來來來！一次搞定各種資料庫 SQL 執行計劃：MySQL、Oracle

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

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

我們先給出在各種資料庫中檢視執行計劃的一個簡單彙總：

資料庫	執行計劃
MySQL	EXPLAIN sql_statement;
Oracle	EXPLAIN PLAN FOR sql_statement; SELECT * FROM TABLE(DBMS_XPLAN.display);
SQL Server	SET STATISTICS PROFILE ON; sql_statement; SET STATISTICS PROFILE OFF;
PostgreSQL	EXPLAIN sql_statement;
SQLite	EXPLAIN QUERY PLAN sql_statement;

MySQL 執行計劃

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

EXPLAIN
SELECT e.first_name,e.last_name,e.salary,d.department_name
  FROM employees e
  JOIN departments d ON (e.department_id = d.department_id)
 WHERE e.salary > 15000;
      

執行該語句将會傳回一個表格形式的執行計劃，包含了 12 列資訊：

id|select_type|table|partitions|type  |possible_keys    |key    |key_len|ref                 |rows|filtered|Extra      |
--|-----------|-----|----------|------|-----------------|-------|-------|--------------------|----|--------|-----------|
 1|SIMPLE     |e    |          |ALL   |emp_department_ix|       |       |                    | 107|   33.33|Using where|
 1|SIMPLE     |d    |          |eq_ref|PRIMARY          |PRIMARY|4      |hrdb.e.department_id|   1|     100|           |
      

MySQL 中的EXPLAIN支援 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 語句。

Oracle 執行計劃

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

使用​

​EXPLAIN PLAN FOR​

​​指令生成并儲存執行計劃；

顯示儲存的執行計劃。

首先，生成執行計劃：

EXPLAIN PLAN FOR
SELECT e.first_name,e.last_name,e.salary,d.department_name
  FROM employees e
  JOIN departments d ON (e.department_id = d.department_id)
 WHERE e.salary > 15000;      

EXPLAIN PLAN FOR指令不會運作 SQL 語句，是以建立的執行計劃不一定與執行該語句時的實際計劃相同。

SELECT * FROM TABLE(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT                                                                           |
--------------------------------------------------------------------------------------------|
Plan hash value: 1343509718                                                                 |
                                                                                            |
--------------------------------------------------------------------------------------------|
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     ||
--------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT             |             |    44 |  1672 |     6  (17)| 00:00:01 ||
|   1 |  MERGE JOIN                  |             |    44 |  1672 |     6  (17)| 00:00:01 ||
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 ||
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 ||
|*  4 |   SORT JOIN                  |             |    44 |   968 |     4  (25)| 00:00:01 ||
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |    44 |   968 |     3   (0)| 00:00:01 ||
--------------------------------------------------------------------------------------------|
                                                                                            |
Predicate Information (identified by operation id):                                         |
---------------------------------------------------                                         |
                                                                                            |
   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")                                      |
       filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")                                      |
   5 - filter("E"."SALARY">15000)                                                           |