各種數據庫的SQL執行計劃是怎么樣的

各種數據庫的SQL執行計劃是怎么樣的，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

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

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

我們先給出在各種數據庫中查看執行計劃的一個簡單匯總：

MySQL 執行計劃

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

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

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

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

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

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

Oracle 執行計劃

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

• 使用EXPLAIN PLAN FOR命令生成并保存執行計劃;

• 顯示保存的執行計劃。

首先，生成執行計劃：

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

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

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

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

• Plan hash value 是該語句的哈希值。SQL 語句和執行計劃會存儲在庫緩存中，哈希值相同的語句可以重用已有的執行計劃，也就是軟解析;

• Id 是一個序號，但不代表執行的順序。執行的順序按照縮進來判斷，縮進越多的越先執行，同樣縮進的從上至下執行。Id 前面的星號表示使用了謂詞判斷，參考下面的  Predicate Information;

• Operation 表示當前的操作，也就是如何訪問表的數據、如何實現表的連接、如何進行排序操作等;

• Name 顯示了訪問的表名、索引名或者子查詢等，前提是當前操作涉及到了這些對象;

• Rows 是 Oracle 估計的當前操作返回的行數，也叫基數(Cardinality);

• Bytes 是 Oracle 估計的當前操作涉及的數據量

• Cost (%CPU) 是 Oracle 計算執行該操作所需的代價;

• Time 是 Oracle 估計執行該操作所需的時間;

• Predicate Information 顯示與 Id 相關的謂詞信息。access  是訪問條件，影響到數據的訪問方式(掃描表還是通過索引);filter 是過濾條件，獲取數據后根據該條件進行過濾。

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

關于 Oracle 執行計劃和 SQL 調優，可以參考 Oracle 官方文檔《SQL Tuning Guide》。

SQL Server 執行計劃

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

SET STATISTICS PROFILE ON

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

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

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

• Rows 表示該步驟實際產生的記錄數;

• Executes 表示該步驟實際被執行的次數;

• StmtText 包含了每個步驟的具體描述，也就是如何訪問和過濾表的數據、如何實現表的連接、如何進行排序操作等;

• StmtId，該語句的編號;

• NodeId，當前操作步驟的節點號，不代表執行順序;

• Parent，當前操作步驟的父節點，先執行子節點，再執行父節點;

• PhysicalOp，物理操作，例如連接操作的嵌套循環實現;

• LogicalOp，邏輯操作，例如內連接操作;

• Argument，操作使用的參數;

• DefinedValues，定義的變量值;

• EstimateRows，估計返回的行數;

• EstimateIO，估計的 IO 成本;

• EstimateCPU，估計的 CPU 成本;

• AvgRowSize，平均返回的行大小;

• TotalSubtreeCost，當前節點累計的成本;

• OutputList，當前節點輸出的字段列表;

• Warnings，預估得到的警告信息;

• Type，當前操作步驟的類型;

• Parallel，是否并行執行;

• EstimateExecutions，該步驟預計被執行的次數;

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

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

SET STATISTICS PROFILE OFF

關于 SQL Server 執行計劃和 SQL 調優，可以參考 SQL Server 官方文檔執行計劃。

PostgreSQL 執行計劃

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

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

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

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

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

關于 PostgreSQL 的執行計劃和性能優化，可以參考 PostgreSQL 官方文檔性能提示。

SQLite 執行計劃

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

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

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

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

看完上述內容，你們掌握各種數據庫的SQL執行計劃是怎么樣的的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！