在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

一次搞定各種數(shù)據(jù)庫 SQL 執(zhí)行計(jì)劃：MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        MySQL 執(zhí)行計(jì)劃
        Oracle 執(zhí)行計(jì)劃
        SQL Server 執(zhí)行計(jì)劃
        PostgreSQL 執(zhí)行計(jì)劃
        SQLite 執(zhí)行計(jì)劃

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

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

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


本文使用的示例表和數(shù)據(jù)可以點(diǎn)擊鏈接《SQL 入門教程》示例數(shù)據(jù)庫。
MySQL 執(zhí)行計(jì)劃

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

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;

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

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

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


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

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

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 表示使用嵌套循環(huán)連接的方式連接兩個(gè)表，employees 為驅(qū)動(dòng)表。cost 表示估算的代價(jià)，rows 表示估計(jì)返回的行數(shù)；actual time 顯示了返回第一行和所有數(shù)據(jù)行花費(fèi)的實(shí)際時(shí)間，后面的 rows 表示迭代器返回的行數(shù)，loops 表示迭代器循環(huán)的次數(shù)。

關(guān)于 MySQL EXPLAIN 命令的使用和參數(shù)，可以參考 MySQL 官方文檔 EXPLAIN 語句。

關(guān)于 MySQL 執(zhí)行計(jì)劃的輸出信息，可以參考 MySQL 官方文檔理解查詢執(zhí)行計(jì)劃。
Oracle 執(zhí)行計(jì)劃

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

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

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

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命令不會(huì)運(yùn)行 SQL 語句，因此創(chuàng)建的執(zhí)行計(jì)劃不一定與執(zhí)行該語句時(shí)的實(shí)際計(jì)劃相同。

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

SELECT * FROM TABLE(DBMS_XPLAN.display);

--------------------------------------------------------------------------------------------|
| 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 ||
--------------------------------------------------------------------------------------------|
|

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 語句。

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

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

在上面的示例中，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 基于部門編號(hào)進(jìn)行排序和過濾；最后 Id = 1 執(zhí)行合并連接。顯然，此處 Oracle 選擇了排序合并連接的方式實(shí)現(xiàn)兩個(gè)表的連接。

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

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

SET STATISTICS PROFILE ON

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

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;

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

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

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

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

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

SET STATISTICS PROFILE OFF

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

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

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;

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

PostgreSQL 中的EXPLAIN也可以使用 ANALYZE 選項(xiàng)顯示語句的實(shí)際運(yùn)行時(shí)間和更多信息：

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;

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

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

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

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

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

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

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