各種數(shù)據(jù)庫(kù)的SQL執(zhí)行計(jì)劃是怎么樣的

各種數(shù)據(jù)庫(kù)的SQL執(zhí)行計(jì)劃是怎么樣的，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

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

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

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

MySQL 執(zhí)行計(jì)劃

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

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

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

對(duì)于上面的示例，只有一個(gè) SELECT 子句，id 都為 1;首先對(duì) employees 表執(zhí)行全表掃描(type = ALL)，處理了 107  行數(shù)據(jù)，使用 WHERE 條件過(guò)濾后預(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í)間等額外的信息：

其中，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ù)。

Oracle 執(zhí)行計(jì)劃

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

• 使用EXPLAIN PLAN FOR命令生成并保存執(zhí)行計(jì)劃;

• 顯示保存的執(zhí)行計(jì)劃。

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

EXPLAIN PLAN FOR命令不會(huì)運(yùn)行 SQL 語(yǔ)句，因此創(chuàng)建的執(zhí)行計(jì)劃不一定與執(zhí)行該語(yǔ)句時(shí)的實(shí)際計(jì)劃相同。

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

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

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

• Plan hash value 是該語(yǔ)句的哈希值。SQL 語(yǔ)句和執(zhí)行計(jì)劃會(huì)存儲(chǔ)在庫(kù)緩存中，哈希值相同的語(yǔ)句可以重用已有的執(zhí)行計(jì)劃，也就是軟解析;

• Id 是一個(gè)序號(hào)，但不代表執(zhí)行的順序。執(zhí)行的順序按照縮進(jìn)來(lái)判斷，縮進(jìn)越多的越先執(zhí)行，同樣縮進(jìn)的從上至下執(zhí)行。Id 前面的星號(hào)表示使用了謂詞判斷，參考下面的  Predicate Information;

• Operation 表示當(dāng)前的操作，也就是如何訪問(wèn)表的數(shù)據(jù)、如何實(shí)現(xiàn)表的連接、如何進(jìn)行排序操作等;

• Name 顯示了訪問(wèn)的表名、索引名或者子查詢(xún)等，前提是當(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  是訪問(wèn)條件，影響到數(shù)據(jù)的訪問(wèn)方式(掃描表還是通過(guò)索引);filter 是過(guò)濾條件，獲取數(shù)據(jù)后根據(jù)該條件進(jìn)行過(guò)濾。

在上面的示例中，Id 的執(zhí)行順序依次為 3 -> 2 -> 5 -> 4- >1。首先，Id = 3 掃描主鍵索引  DEPT_ID_PK，Id = 2 按主鍵 ROWID 訪問(wèn)表 DEPARTMENTS，結(jié)果已經(jīng)排序;其次，Id = 5 全表掃描訪問(wèn) EMPLOYEES  并且利用 filter 過(guò)濾數(shù)據(jù)，Id = 4 基于部門(mén)編號(hào)進(jìn)行排序和過(guò)濾;最后 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)單方法，這里我們介紹一種通過(guò)命令查看的方法：

SET STATISTICS PROFILE ON

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

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

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

• Rows 表示該步驟實(shí)際產(chǎn)生的記錄數(shù);

• Executes 表示該步驟實(shí)際被執(zhí)行的次數(shù);

• StmtText 包含了每個(gè)步驟的具體描述，也就是如何訪問(wèn)和過(guò)濾表的數(shù)據(jù)、如何實(shí)現(xiàn)表的連接、如何進(jìn)行排序操作等;

• StmtId，該語(yǔ)句的編號(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)前操作步驟的類(lèi)型;

• Parallel，是否并行執(zhí)行;

• EstimateExecutions，該步驟預(yù)計(jì)被執(zhí)行的次數(shù);

對(duì)于上面的語(yǔ)句，節(jié)點(diǎn)執(zhí)行的順序?yàn)?3 -> 4 -> 2 -> 1。首先執(zhí)行第 3 行，通過(guò)聚集索引(主鍵)掃描 employees  表加過(guò)濾的方式返回了 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]))說(shuō)明驅(qū)動(dòng)表為 employees ;第 1  行代表了整個(gè)查詢(xún)，不執(zhí)行實(shí)際操作。

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

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 類(lèi)似，也就是在 SQL 語(yǔ)句的前面加上EXPLAIN關(guān)鍵字：

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

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

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

EXPLAIN ANALYZE通過(guò)執(zhí)行語(yǔ)句獲得了更多的信息。其中，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  意味著需要使用到磁盤(pán)的臨時(shí)存儲(chǔ);Planning Time 是生成執(zhí)行計(jì)劃的時(shí)間;Execution Time 是執(zhí)行語(yǔ)句的實(shí)際時(shí)間，不包括 Planning  Time。

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

SQLite 執(zhí)行計(jì)劃

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

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

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

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

看完上述內(nèi)容，你們掌握各種數(shù)據(jù)庫(kù)的SQL執(zhí)行計(jì)劃是怎么樣的的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！