雲栖号: https://yqh.aliyun.com 第一手的上雲資訊,不同行業精選的上雲企業案例庫,基于衆多成功案例萃取而成的最佳實踐,助力您上雲決策!
作者 | 董旭陽TonyDong
出品 | CSDN 部落格
執行計劃(execution plan,也叫查詢計劃或者解釋計劃)是資料庫執行 SQL 語句的具體步驟,例如通過索引還是全表掃描通路表中的資料,連接配接查詢的實作方式和連接配接的順序等。如果 SQL 語句性能不夠理想,我們首先應該檢視它的執行計劃。本文主要介紹如何在各種資料庫中擷取和了解執行計劃,并給出進一步深入分析的參考文檔。
現在許多管理和開發工具都提供了檢視圖形化執行計劃的功能,例如 MySQL Workbench、Oracle SQL Developer、SQL Server Management Studio、DBeaver 等;不過我們不打算使用這類工具,而是介紹利用資料庫提供的指令檢視執行計劃。
我們先給出在各種資料庫中檢視執行計劃的一個簡單彙總:
本文使用的示例表和資料可以點選連結《SQL 入門教程》示例資料庫(
https://tonydong.blog.csdn.net/article/details/86518676)。
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 語句。
接下來,我們要做的就是了解執行計劃中這些字段的含義。下表列出了 MySQL 執行計劃中的各個字段的作用:
對于上面的示例,隻有一個 SELECT 子句,id 都為 1;首先對 employees 表執行全表掃描(type = ALL),處理了 107 行資料,使用 WHERE 條件過濾後預計剩下 33.33% 的資料(估計不準确);然後針對這些資料,依次使用 departments 表的主鍵(key = PRIMARY)查找一行比對的資料(type = eq_ref、rows = 1)。
使用 MySQL 8.0 新增的 ANALYZE 選項可以顯示實際執行時間等額外的資訊:
EXPLAIN ANALYZE
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;
-> Nested loop inner join (cost=23.43 rows=36) (actual time=0.325..1.287 rows=3 loops=1)
-> Filter: ((e.salary > 15000.00) and (e.department_id is not null)) (cost=10.95 rows=36) (actual time=0.281..1.194 rows=3 loops=1)
-> Table scan on e (cost=10.95 rows=107) (actual time=0.266..0.716 rows=107 loops=1)
-> Single-row index lookup on d using PRIMARY (department_id=e.department_id) (cost=0.25 rows=1) (actual time=0.013..0.015 rows=1 loops=3)
其中,Nested loop inner join 表示使用嵌套循環連接配接的方式連接配接兩個表,employees 為驅動表。cost 表示估算的代價,rows 表示估計傳回的行數;actual time 顯示了傳回第一行和所有資料行花費的實際時間,後面的 rows 表示疊代器傳回的行數,loops 表示疊代器循環的次數。
關于 MySQL EXPLAIN 指令的使用和參數,可以參考 MySQL 官方文檔 EXPLAIN 語句(
https://dev.mysql.com/doc/refman/8.0/en/explain.html關于 MySQL 執行計劃的輸出資訊,可以參考 MySQL 官方文檔了解查詢執行計劃(
https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.htmlOracle 執行計劃
Oracle 中提供了多種檢視執行計劃的方法,本文使用以下方式:
1.使用EXPLAIN PLAN FOR指令生成并儲存執行計劃;
2.顯示儲存的執行計劃。
首先,生成執行計劃:
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 語句,是以建立的執行計劃不一定與執行該語句時的實際計劃相同。
該指令會将生成的執行計劃儲存到全局的臨時表 PLAN_TABLE 中,然後使用系統包 DBMS_XPLAN 中的存儲過程格式化顯示該表中的執行計劃。以下語句可以檢視目前會話中的最後一個執行計劃:
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) |
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》(
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/SQL Server 執行計劃
SQL Server Management Studio 提供了檢視圖形化執行計劃的簡單方法,這裡我們介紹一種通過指令檢視的方法:
SET STATISTICS PROFILE ON
以上指令可以打開 SQL Server 語句的分析功能,打開之後執行的語句會額外傳回相應的執行計劃:
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;
first_name|last_name|salary |department_name|
----------|---------|--------|---------------|
Steven |King |24000.00|Executive |
Neena |Kochhar |17000.00|Executive |
Lex |De Haan |17000.00|Executive |
Rows|Executes|StmtText |StmtId|NodeId|Parent|PhysicalOp |LogicalOp |Argument |DefinedValues |EstimateRows|EstimateIO |EstimateCPU|AvgRowSize|TotalSubtreeCost|OutputList |Warnings|Type |Parallel|EstimateExecutions|
----|--------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------|------|------|--------------------|--------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------|------------|------------|-----------|----------|----------------|----------------------------------------------------------------------|--------|--------|--------|------------------|
3| 1|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 | 1| 1| 0| | | | | 2.9719627| | | | 0.007803641| | |SELECT | 0| |
3| 1| |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[department_id])) | 1| 2| 1|Nested Loops |Inner Join |OUTER REFERENCES:([e].[department_id]) | | 2.9719627| 0| 0| 57| 0.007803641|[e].[first_name], [e].[last_name], [e].[salary], [d].[department_name]| |PLAN_ROW| 0| 1|
3| 1| |--Clustered Index Scan(OBJECT:([hrdb].[dbo].[employees].[emp_emp_id_pk] AS [e]), WHERE:([hrdb].[dbo].[employees].[salary] as [e].[salary]>(15000.00))) | 1| 3| 2|Clustered Index Scan|Clustered Index Scan|OBJECT:([hrdb].[dbo].[employees].[emp_emp_id_pk] AS [e]), WHERE:([hrdb].[dbo].[employees].[salary] as [e].[salary]>(15000.00)) |[e].[first_name], [e].[last_name], [e].[salary], [e].[department_id]| 3|0.0038657407| 2.747E-4| 44| 0.004140441|[e].[first_name], [e].[last_name], [e].[salary], [e].[department_id] | |PLAN_ROW| 0| 1|
3| 3| |--Clustered Index Seek(OBJECT:([hrdb].[dbo].[departments].[dept_id_pk] AS [d]), SEEK:([d].[department_id]=[hrdb].[dbo].[employees].[department_id] as [e].[department_id]) ORDERED FORWARD)| 1| 4| 2|Clustered Index Seek|Clustered Index Seek|OBJECT:([hrdb].[dbo].[departments].[dept_id_pk] AS [d]), SEEK:([d].[department_id]=[hrdb].[dbo].[employees].[department_id] as [e].[department_id]) ORDERED FORWARD|[d].[department_name] | 1| 0.003125| 1.581E-4| 26| 0.0035993|[d].[department_name] | |PLAN_ROW| 0| 3|
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關鍵字:
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;
QUERY PLAN |
----------------------------------------------------------------------|
Hash Join (cost=3.38..4.84 rows=3 width=29) |
Hash Cond: (d.department_id = e.department_id) |
-> Seq Scan on departments d (cost=0.00..1.27 rows=27 width=15) |
-> Hash (cost=3.34..3.34 rows=3 width=22) |
-> Seq Scan on employees e (cost=0.00..3.34 rows=3 width=22)|
Filter: (salary > '15000'::numeric) |
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
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;
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------|
Hash Join (cost=3.38..4.84 rows=3 width=29) (actual time=0.347..0.382 rows=3 loops=1) |
Hash Cond: (d.department_id = e.department_id) |
-> Seq Scan on departments d (cost=0.00..1.27 rows=27 width=15) (actual time=0.020..0.037 rows=27 loops=1) |
-> Hash (cost=3.34..3.34 rows=3 width=22) (actual time=0.291..0.292 rows=3 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> Seq Scan on employees e (cost=0.00..3.34 rows=3 width=22) (actual time=0.034..0.280 rows=3 loops=1)|
Filter: (salary > '15000'::numeric) |
Rows Removed by Filter: 104 |
Planning Time: 1.053 ms |
Execution Time: 0.553 ms
EXPLAIN ANALYZE通過執行語句獲得了更多的資訊。其中,actual time 是每次疊代實際花費的平均時間(ms),也分為啟動時間和完成時間;loops 表示疊代次數;Hash 操作還會顯示桶數(Buckets)、分批數量(Batches)以及占用的記憶體(Memory Usage),Batches 大于 1 意味着需要使用到磁盤的臨時存儲;Planning Time 是生成執行計劃的時間;Execution Time 是執行語句的實際時間,不包括 Planning Time。
關于 PostgreSQL 的執行計劃和性能優化,可以參考 PostgreSQL 官方文檔性能提示(
https://www.postgresql.org/docs/12/performance-tips.htmlSQLite 執行計劃
SQLite 也提供了EXPLAIN QUERY PLAN指令,用于擷取 SQL 語句的執行計劃:
sqlite> EXPLAIN QUERY PLAN
...> 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;
QUERY PLAN
|--SCAN TABLE employees AS e
`--SEARCH TABLE departments AS d USING INTEGER PRIMARY KEY (rowid=?)
SQLite 中的EXPLAIN QUERY PLAN支援 SELECT、INSERT、UPDATE、DELETE 等語句。
SQLite 執行計劃同樣按照縮進來顯示,縮進越多的越先執行,同樣縮進的從上至下執行。以上示例先掃描 employees 表,然後針對該結果依次通過主鍵查找 departments 中的資料。SQLite 隻支援一種連接配接實作,也就是 nested loops join。
另外,SQLite 中的簡單EXPLAIN也可以用于顯示執行該語句的虛拟機指令序列:
sqlite> 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;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 15 0 00 Start at 15
1 OpenRead 0 5 0 11 00 root=5 iDb=0; employees
2 OpenRead 1 2 0 2 00 root=2 iDb=0; departments
3 Rewind 0 14 0 00
4 Column 0 7 1 00 r[1]=employees.salary
5 Le 2 13 1 (BINARY) 53 if r[1]<=r[2] goto 13
6 Column 0 10 3 00 r[3]=employees.department_id
7 SeekRowid 1 13 3 00 intkey=r[3]
8 Column 0 1 4 00 r[4]=employees.first_name
9 Column 0 2 5 00 r[5]=employees.last_name
10 Column 0 7 6 00 r[6]=employees.salary
11 Column 1 1 7 00 r[7]=departments.department_name
12 ResultRow 4 4 0 00 output=r[4..7]
13 Next 0 4 0 01
14 Halt 0 0 0 00
15 Transaction 0 0 8 0 01 usesStmtJournal=0
16 Integer 15000 2 0 00 r[2]=15000
17 Goto 0 1 0 00
關于 SQLite 的執行計劃和優化器相關資訊,可以參考 SQLite 官方文檔解釋查詢計劃。
版權聲明:本文為CSDN部落客「董旭陽TonyDong」的原創文章。
原文釋出時間:2020-02-04
本文作者:董旭陽TonyDong
本文來自:“
CSDN雲計算”,了解相關資訊可以關注“
”