天天看點

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

來來來!一次搞定各種資料庫 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)                                                           |