執(zhí)行計劃(execution plan，也叫查詢計劃或者解釋計劃)是數(shù)據(jù)庫執(zhí)行 SQL 語句的具體步驟，例如通過索引還是全表掃描訪問表中的數(shù)據(jù)，連接查詢的實現(xiàn)方式和連接的順序等。如果 SQL 語句性能不夠理想，我們首先應該查看它的執(zhí)行計劃。本文主要介紹如何在各種數(shù)據(jù)庫中獲取和理解執(zhí)行計劃，并給出進一步深入分析的參考文檔。

現(xiàn)在許多管理和開發(fā)工具都提供了查看圖形化執(zhí)行計劃的功能，例如 MySQL Workbench、Oracle SQL Developer、SQL Server Management Studio、DBeaver 等;不過我們不打算使用這類工具，而是介紹利用數(shù)據(jù)庫提供的命令查看執(zhí)行計劃。

我們先給出在各種數(shù)據(jù)庫中查看執(zhí)行計劃的一個簡單匯總：

MySQL 執(zhí)行計劃

MySQL 中獲取執(zhí)行計劃的方法很簡單，就是在 SQL 語句的前面加上EXPLAIN關(guān)鍵字：

執(zhí)行該語句將會返回一個表格形式的執(zhí)行計劃，包含了 12 列信息：

接下來，我們要做的就是理解執(zhí)行計劃中這些字段的含義。下表列出了 MySQL 執(zhí)行計劃中的各個字段的作用：

對于上面的示例，只有一個 SELECT 子句，id 都為 1;首先對 employees 表執(zhí)行全表掃描(type = ALL)，處理了 107 行數(shù)據(jù)，使用 WHERE 條件過濾后預計剩下 33.33% 的數(shù)據(jù)(估計不準確);然后針對這些數(shù)據(jù)，依次使用 departments 表的主鍵(key = PRIMARY)查找一行匹配的數(shù)據(jù)(type = eq_ref、rows = 1)。

使用 MySQL 8.0 新增的 ANALYZE 選項可以顯示實際執(zhí)行時間等額外的信息：

其中，Nested loop inner join 表示使用嵌套循環(huán)連接的方式連接兩個表，employees 為驅(qū)動表。cost 表示估算的代價，rows 表示估計返回的行數(shù);actual time 顯示了返回第一行和所有數(shù)據(jù)行花費的實際時間，后面的 rows 表示迭代器返回的行數(shù)，loops 表示迭代器循環(huán)的次數(shù)。

Oracle 執(zhí)行計劃

Oracle 中提供了多種查看執(zhí)行計劃的方法，本文使用以下方式：

• 使用EXPLAIN PLAN FOR命令生成并保存執(zhí)行計劃;
• 顯示保存的執(zhí)行計劃。

首先，生成執(zhí)行計劃：

EXPLAIN PLAN FOR命令不會運行 SQL 語句，因此創(chuàng)建的執(zhí)行計劃不一定與執(zhí)行該語句時的實際計劃相同。

該命令會將生成的執(zhí)行計劃保存到全局的臨時表 PLAN_TABLE 中，然后使用系統(tǒng)包 DBMS_XPLAN 中的存儲過程格式化顯示該表中的執(zhí)行計劃。以下語句可以查看當前會話中的最后一個執(zhí)行計劃：

Oracle 中的EXPLAIN PLAN FOR支持 SELECT、UPDATE、INSERT 以及 DELETE 語句。

接下來，我們同樣需要理解執(zhí)行計劃中各種信息的含義：

• Plan hash value 是該語句的哈希值。SQL 語句和執(zhí)行計劃會存儲在庫緩存中，哈希值相同的語句可以重用已有的執(zhí)行計劃，也就是軟解析;
• Id 是一個序號，但不代表執(zhí)行的順序。執(zhí)行的順序按照縮進來判斷，縮進越多的越先執(zhí)行，同樣縮進的從上至下執(zhí)行。Id 前面的星號表示使用了謂詞判斷，參考下面的 Predicate Information;
• Operation 表示當前的操作，也就是如何訪問表的數(shù)據(jù)、如何實現(xiàn)表的連接、如何進行排序操作等;
• Name 顯示了訪問的表名、索引名或者子查詢等，前提是當前操作涉及到了這些對象;
• Rows 是 Oracle 估計的當前操作返回的行數(shù)，也叫基數(shù)(Cardinality);
• Bytes 是 Oracle 估計的當前操作涉及的數(shù)據(jù)量
• Cost (%CPU) 是 Oracle 計算執(zhí)行該操作所需的代價;
• Time 是 Oracle 估計執(zhí)行該操作所需的時間;
• Predicate Information 顯示與 Id 相關(guān)的謂詞信息。access 是訪問條件，影響到數(shù)據(jù)的訪問方式(掃描表還是通過索引);filter 是過濾條件，獲取數(shù)據(jù)后根據(jù)該條件進行過濾。

在上面的示例中，Id 的執(zhí)行順序依次為 3 -> 2 -> 5 -> 4- >1。首先，Id = 3 掃描主鍵索引 DEPT_ID_PK，Id = 2 按主鍵 ROWID 訪問表 DEPARTMENTS，結(jié)果已經(jīng)排序;其次，Id = 5 全表掃描訪問 EMPLOYEES 并且利用 filter 過濾數(shù)據(jù)，Id = 4 基于部門編號進行排序和過濾;最后 Id = 1 執(zhí)行合并連接。顯然，此處 Oracle 選擇了排序合并連接的方式實現(xiàn)兩個表的連接。

關(guān)于 Oracle 執(zhí)行計劃和 SQL 調(diào)優(yōu)，可以參考 Oracle 官方文檔《SQL Tuning Guide》。

SQL Server 執(zhí)行計劃

SQL Server Management Studio 提供了查看圖形化執(zhí)行計劃的簡單方法，這里我們介紹一種通過命令查看的方法：

SET STATISTICS PROFILE ON 

以上命令可以打開 SQL Server 語句的分析功能，打開之后執(zhí)行的語句會額外返回相應的執(zhí)行計劃：

SQL Server 中的執(zhí)行計劃支持 SELECT、INSERT、UPDATE、DELETE 以及 EXECUTE 語句。

SQL Server 執(zhí)行計劃各個步驟的執(zhí)行順序按照縮進來判斷，縮進越多的越先執(zhí)行，同樣縮進的從上至下執(zhí)行。接下來，我們需要理解執(zhí)行計劃中各種信息的含義：

• Rows 表示該步驟實際產(chǎn)生的記錄數(shù);
• Executes 表示該步驟實際被執(zhí)行的次數(shù);
• StmtText 包含了每個步驟的具體描述，也就是如何訪問和過濾表的數(shù)據(jù)、如何實現(xiàn)表的連接、如何進行排序操作等;
• StmtId，該語句的編號;
• NodeId，當前操作步驟的節(jié)點號，不代表執(zhí)行順序;
• Parent，當前操作步驟的父節(jié)點，先執(zhí)行子節(jié)點，再執(zhí)行父節(jié)點;
• PhysicalOp，物理操作，例如連接操作的嵌套循環(huán)實現(xiàn);
• LogicalOp，邏輯操作，例如內(nèi)連接操作;
• Argument，操作使用的參數(shù);
• DefinedValues，定義的變量值;
• EstimateRows，估計返回的行數(shù);
• EstimateIO，估計的 IO 成本;
• EstimateCPU，估計的 CPU 成本;
• AvgRowSize，平均返回的行大小;
• TotalSubtreeCost，當前節(jié)點累計的成本;
• OutputList，當前節(jié)點輸出的字段列表;
• Warnings，預估得到的警告信息;
• Type，當前操作步驟的類型;
• Parallel，是否并行執(zhí)行;
• EstimateExecutions，該步驟預計被執(zhí)行的次數(shù);

對于上面的語句，節(jié)點執(zhí)行的順序為 3 -> 4 -> 2 -> 1。首先執(zhí)行第 3 行，通過聚集索引(主鍵)掃描 employees 表加過濾的方式返回了 3 行數(shù)據(jù)，估計的行數(shù)(3.0841121673583984)與此非常接近;然后執(zhí)行第 4 行，循環(huán)使用聚集索引的方式查找 departments 表，循環(huán) 3 次每次返回 1 行數(shù)據(jù);第 2 行是它們的父節(jié)點，表示使用 Nested Loops 方式實現(xiàn) Inner Join，Argument 列(OUTER REFERENCES:([e].[department_id]))說明驅(qū)動表為 employees ;第 1 行代表了整個查詢，不執(zhí)行實際操作。

最后，可以使用以下命令關(guān)閉語句的分析功能：

SET STATISTICS PROFILE OFF 

關(guān)于 SQL Server 執(zhí)行計劃和 SQL 調(diào)優(yōu)，可以參考 SQL Server 官方文檔執(zhí)行計劃。

PostgreSQL 執(zhí)行計劃

PostgreSQL 中獲取執(zhí)行計劃的方法與 MySQL 類似，也就是在 SQL 語句的前面加上EXPLAIN關(guān)鍵字：

PostgreSQL 中的EXPLAIN支持 SELECT、INSERT、UPDATE、DELETE、VALUES、EXECUTE、DECLARE、CREATE TABLE AS 以及 CREATE MATERIALIZED VIEW AS 語句。

PostgreSQL 執(zhí)行計劃的順序按照縮進來判斷，縮進越多的越先執(zhí)行，同樣縮進的從上至下執(zhí)行。對于以上示例，首先對 employees 表執(zhí)行全表掃描(Seq Scan)，使用 salary > 15000 作為過濾條件;cost 分別顯示了預估的返回第一行的成本(0.00)和返回所有行的成本(3.34);rows 表示預估返回的行數(shù);width 表示預估返回行的大小(單位 Byte)。然后將掃描結(jié)果放入到內(nèi)存哈希表中，兩個 cost 都等于 3.34，因為是在掃描完所有數(shù)據(jù)后一次性計算并存入哈希表。接下來掃描 departments 并且根據(jù) department_id 計算哈希值，然后和前面的哈希表進行匹配(d.department_id = e.department_id)。最上面的一行表明數(shù)據(jù)庫采用的是 Hash Join 實現(xiàn)連接操作。

PostgreSQL 中的EXPLAIN也可以使用 ANALYZE 選項顯示語句的實際運行時間和更多信息：

EXPLAIN ANALYZE通過執(zhí)行語句獲得了更多的信息。其中，actual time 是每次迭代實際花費的平均時間(ms)，也分為啟動時間和完成時間;loops 表示迭代次數(shù);Hash 操作還會顯示桶數(shù)(Buckets)、分批數(shù)量(Batches)以及占用的內(nèi)存(Memory Usage)，Batches 大于 1 意味著需要使用到磁盤的臨時存儲;Planning Time 是生成執(zhí)行計劃的時間;Execution Time 是執(zhí)行語句的實際時間，不包括 Planning Time。

關(guān)于 PostgreSQL 的執(zhí)行計劃和性能優(yōu)化，可以參考 PostgreSQL 官方文檔性能提示。

SQLite 執(zhí)行計劃

SQLite 也提供了EXPLAIN QUERY PLAN命令，用于獲取 SQL 語句的執(zhí)行計劃：

SQLite 中的EXPLAIN QUERY PLAN支持 SELECT、INSERT、UPDATE、DELETE 等語句。

SQLite 執(zhí)行計劃同樣按照縮進來顯示，縮進越多的越先執(zhí)行，同樣縮進的從上至下執(zhí)行。以上示例先掃描 employees 表，然后針對該結(jié)果依次通過主鍵查找 departments 中的數(shù)據(jù)。SQLite 只支持一種連接實現(xiàn)，也就是 nested loops join。

另外，SQLite 中的簡單EXPLAIN也可以用于顯示執(zhí)行該語句的虛擬機指令序列：

關(guān)于 SQLite 的執(zhí)行計劃和優(yōu)化器相關(guān)信息，可以參考 SQLite 官方文檔解釋查詢計劃。