你確定你讀懂了 MySQL 執(zhí)行計(jì)劃嗎？

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


文章目錄

            獲取執(zhí)行計(jì)劃
            解讀執(zhí)行計(jì)劃
                type 字段
                Extra 字段
                訪(fǎng)問(wèn)謂詞與過(guò)濾謂詞
                完整字段信息
            格式化參數(shù)
            執(zhí)行計(jì)劃中的分區(qū)表信息
            獲取額外的執(zhí)行計(jì)劃信息
            獲取指定連接的執(zhí)行計(jì)劃
            獲取實(shí)際運(yùn)行的執(zhí)行計(jì)劃

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天給大家深入分析一下 MySQL 中的執(zhí)行計(jì)劃。

執(zhí)行計(jì)劃（execution plan，也叫查詢(xún)計(jì)劃或者解釋計(jì)劃）是 MySQL 服務(wù)器執(zhí)行 SQL 語(yǔ)句的具體步驟。例如，通過(guò)索引還是全表掃描訪(fǎng)問(wèn)表中的數(shù)據(jù)，連接查詢(xún)的實(shí)現(xiàn)方式和連接的順序，分組和排序操作的實(shí)現(xiàn)方式等。

負(fù)責(zé)生成執(zhí)行計(jì)劃的組件就是優(yōu)化器，優(yōu)化器利用表結(jié)構(gòu)、字段、索引、查詢(xún)條件、數(shù)據(jù)庫(kù)的統(tǒng)計(jì)信息和配置參數(shù)決定 SQL 語(yǔ)句的最佳執(zhí)行方式。如果想要解決慢查詢(xún)的性能問(wèn)題，首先應(yīng)該查看它的執(zhí)行計(jì)劃。
獲取執(zhí)行計(jì)劃

MySQL 提供了 EXPLAIN 語(yǔ)句，用于獲取 SQL 語(yǔ)句的執(zhí)行計(jì)劃。該語(yǔ)句的基本形式如下：

{EXPLAIN | DESCRIBE | DESC}
{
    SELECT statement
  | TABLE statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

 

EXPLAIN和DESCRIBE是同義詞，可以通用。實(shí)際應(yīng)用中，DESCRIBE主要用于查看表的結(jié)構(gòu)，EXPLAIN主要用于獲取執(zhí)行計(jì)劃。MySQL 可以獲取 SELECT、INSERT、DELETE、UPDATE、REPLACE 等語(yǔ)句的執(zhí)行計(jì)劃。從 MySQL 8.0.19 開(kāi)始，支持 TABLE 語(yǔ)句的執(zhí)行計(jì)劃。

舉例來(lái)說(shuō)：

explain
select *
from employee;
id|select_type|table   |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
--|-----------|--------|----------|----|-------------|---|-------|---|----|--------|-----|
 1|SIMPLE     |employee|          |ALL |             |   |       |   |  25|   100.0|     |

 

MySQL 中的執(zhí)行計(jì)劃包含了 12 列信息，這些字段的含義我們?cè)谙挛闹羞M(jìn)行解讀。

除了使用 EXPLAIN 語(yǔ)句之外，很多管理和開(kāi)發(fā)工具都提供了查看圖形化執(zhí)行計(jì)劃的功能，例如 MySQL Workbench 中顯示以上查詢(xún)的執(zhí)行計(jì)劃如下：
 

當(dāng)然，這種方式最終也是執(zhí)行了 EXPLAIN 語(yǔ)句。
解讀執(zhí)行計(jì)劃

理解執(zhí)行計(jì)劃中每個(gè)字段的含義可以幫助我們知悉 MySQL 內(nèi)部的操作過(guò)程，找到性能問(wèn)題的所在并有針對(duì)性地進(jìn)行優(yōu)化。在執(zhí)行計(jì)劃的輸出信息中，最重要的字段就是 type。
type 字段

type 被稱(chēng)為連接類(lèi)型（join type）或者訪(fǎng)問(wèn)類(lèi)型（access type），它顯示了 MySQL 如何訪(fǎng)問(wèn)表中的數(shù)據(jù)。

訪(fǎng)問(wèn)類(lèi)型會(huì)直接影響到查詢(xún)語(yǔ)句的性能，性能從好到差依次為：

    system，表中只有一行數(shù)據(jù)（系統(tǒng)表），這是 const 類(lèi)型的特殊情況；
    const，最多返回一條匹配的數(shù)據(jù)，在查詢(xún)的最開(kāi)始讀??；
    eq_ref，對(duì)于前面的每一行，從該表中讀取一行數(shù)據(jù)；
    ref，對(duì)于前面的每一行，從該表中讀取匹配索引值的所有數(shù)據(jù)行；
    fulltext，通過(guò) FULLTEXT 索引查找數(shù)據(jù)；
    ref_or_null，與 ref 類(lèi)似，額外加上 NULL 值查找；
    index_merge，使用索引合并優(yōu)化技術(shù)，此時(shí) key 列顯示使用的所有索引；
    unique_subquery，替代以下情況時(shí)的 eq_ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)；
    index_subquery，與 unique_subquery 類(lèi)似，用于子查詢(xún)中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)；
    range，使用索引查找范圍值；
    index，與 ALL 類(lèi)型相同，只不過(guò)掃描的是索引；
    ALL，全表掃描，通常表示存在性能問(wèn)題。

const 和 eq_ref 都意味著著通過(guò) PRIMARY KEY 或者 UNIQUE 索引查找唯一值；它們的區(qū)別在于 const 對(duì)于整個(gè)查詢(xún)只返回一條數(shù)據(jù)，eq_ref 對(duì)于前面的結(jié)果集中的每條記錄只返回一條數(shù)據(jù)。例如以下查詢(xún)通過(guò)主鍵（key = PRIMARY）進(jìn)行等值查找：

explain
select *
from employee
where emp_id = 1;
id|select_type|table   |partitions|type |possible_keys|key    |key_len|ref  |rows|filtered|Extra|
--|-----------|--------|----------|-----|-------------|-------|-------|-----|----|--------|-----|
 1|SIMPLE     |employee|          |const|PRIMARY      |PRIMARY|4      |const|   1|   100.0|     |

 

const 只返回一條數(shù)據(jù)，是一種非?？焖俚脑L(fǎng)問(wèn)方式，所以相當(dāng)于一個(gè)常量（constant）。

以下語(yǔ)句通過(guò)主鍵等值連接兩個(gè)表：

explain
select *
from employee e
join department d
on (e.dept_id = d.dept_id )
where e.emp_id in(1, 2);
id|select_type|table|partitions|type  |possible_keys       |key    |key_len|ref           |rows|filtered|Extra      |
--|-----------|-----|----------|------|--------------------|-------|-------|--------------|----|--------|-----------|
 1|SIMPLE     |e    |          |range |PRIMARY,idx_emp_dept|PRIMARY|4      |              |   2|   100.0|Using where|
 1|SIMPLE     |d    |          |eq_ref|PRIMARY             |PRIMARY|4      |hrdb.e.dept_id|   1|   100.0|           |

 

對(duì)于 employee 中返回的每一行（table = e），department 表通過(guò)主鍵（key = PRIMARY）返回且僅返回一條數(shù)據(jù)（type = eq_ref）。Extra 字段中的 Using where 表示將經(jīng)過(guò)條件過(guò)濾后的數(shù)據(jù)傳遞給下個(gè)表或者客戶(hù)端。

ref、ref_or_null 以及 range 表示通過(guò)范圍查找所有匹配的索引項(xiàng)，然后根據(jù)需要再訪(fǎng)問(wèn)表中的數(shù)據(jù)。通常意味著使用了非唯一索引或者唯一索引的前面部分字段進(jìn)行數(shù)據(jù)訪(fǎng)問(wèn)，例如：

explain
select *
from employee e
where e.dept_id = 1;
id|select_type|table|partitions|type|possible_keys|key         |key_len|ref  |rows|filtered|Extra|
--|-----------|-----|----------|----|-------------|------------|-------|-----|----|--------|-----|
 1|SIMPLE     |e    |          |ref |idx_emp_dept |idx_emp_dept|4      |const|   3|   100.0|     |

explain
select *
from employee e
join department d
on (e.dept_id = d.dept_id )
where d.dept_id = 1;
id|select_type|table|partitions|type |possible_keys|key         |key_len|ref  |rows|filtered|Extra|
--|-----------|-----|----------|-----|-------------|------------|-------|-----|----|--------|-----|
 1|SIMPLE     |d    |          |const|PRIMARY      |PRIMARY     |4      |const|   1|   100.0|     |
 1|SIMPLE     |e    |          |ref  |idx_emp_dept |idx_emp_dept|4      |const|   3|   100.0|     |

 

以上兩個(gè)查詢(xún)語(yǔ)句都是通過(guò)索引 idx_emp_dept 返回 employee 表中的數(shù)據(jù)。

ref_or_null 和 ref 的區(qū)別在于查詢(xún)中包含了 IS NULL 條件。例如：

alter table employee modify column dept_id int null;

explain
select *
from employee e
where e.dept_id = 1 or dept_id is null;
id|select_type|table|partitions|type       |possible_keys|key         |key_len|ref  |rows|filtered|Extra                |
--|-----------|-----|----------|-----------|-------------|------------|-------|-----|----|--------|---------------------|
 1|SIMPLE     |e    |          |ref_or_null|idx_emp_dept |idx_emp_dept|5      |const|   4|   100.0|Using index condition|

 

其中，Extra 字段顯示為 Using index condition，意味著通過(guò)索引訪(fǎng)問(wèn)表中的數(shù)據(jù)之前，直接通過(guò) WHERE 語(yǔ)句中出現(xiàn)的索引字段條件過(guò)濾數(shù)據(jù)。這是 MySQL 5.6 之后引入了一種優(yōu)化，叫做索引條件下推（Index Condition Pushdown）。

為了顯示 ref_or_null，我們需要將字段 dept_id 設(shè)置為可空，測(cè)試之后記得重新修改為 NOT NULL：

alter table employee modify column dept_id int not null;

 

range 通常出現(xiàn)在使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或者 IN() 運(yùn)算符和索引字段進(jìn)行比較時(shí)，例如：

explain
select *
from employee e
where e.email like 'zhang%';
id|select_type|table|partitions|type |possible_keys|key         |key_len|ref|rows|filtered|Extra                |
--|-----------|-----|----------|-----|-------------|------------|-------|---|----|--------|---------------------|
 1|SIMPLE     |e    |          |range|uk_emp_email |uk_emp_email|302    |   |   2|   100.0|Using index condition|

 

index_merge 表示索引合并，當(dāng)查詢(xún)通過(guò)多個(gè)索引 range 訪(fǎng)問(wèn)方式返回?cái)?shù)據(jù)時(shí)，MySQL 可以先對(duì)這些索引掃描結(jié)果合并成一個(gè)，然后通過(guò)這個(gè)索引獲取表中的數(shù)據(jù)。例如：

explain
select *
from employee e
where dept_id = 1 or job_id = 1;
id|select_type|table|partitions|type       |possible_keys      |key                |key_len|ref|rows|filtered|Extra                                        |
--|-----------|-----|----------|-----------|-------------------|-------------------|-------|---|----|--------|---------------------------------------------|
 1|SIMPLE     |e    |          |index_merge|PRIMARY,idx_emp_job|PRIMARY,idx_emp_job|4,4    |   |   2|   100.0|Using union(PRIMARY,idx_emp_job); Using where|

 

其中，字段 key 顯示了使用的索引列表；Extra 中的 Using union(PRIMARY,idx_emp_job) 是索引合并的算法，這里采用了并集算法（查詢(xún)條件使用了 or 運(yùn)算符）。

unique_subquery 本質(zhì)上也是 eq_ref 索引查找，用于優(yōu)化以下形式的子查詢(xún)：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

 

index_subquery 本質(zhì)上也是 ref 范圍索引查找，用于優(yōu)化以下形式的子查詢(xún)：

value IN (SELECT key_column FROM single_table WHERE some_expr)

 

index表示掃描整個(gè)索引，以下兩種情況會(huì)使用這種訪(fǎng)問(wèn)方式：

    查詢(xún)可以直接通過(guò)索引返回所需的字段信息，也就是 index-only scan。此時(shí) Extra 字段顯示為 Using index。例如：

    explain
    select dept_id
    from employee;
    id|select_type|table   |partitions|type |possible_keys|key         |key_len|ref|rows|filtered|Extra      |
    --|-----------|--------|----------|-----|-------------|------------|-------|---|----|--------|-----------|
     1|SIMPLE     |employee|          |index|             |idx_emp_dept|4      |   |  25|   100.0|Using index|
 

    查詢(xún)所需的 dept_id 字段通過(guò)掃描索引 idx_emp_dept 即可獲得，所以采用了 index 訪(fǎng)問(wèn)類(lèi)型。

    通過(guò)掃描索引執(zhí)行全表掃描，從而按照索引的順序返回?cái)?shù)據(jù)。此時(shí) Extra 字段不會(huì)出現(xiàn) Using index。

    explain
    select *
    from employee force index (idx_emp_name)
    order by emp_name;
    id|select_type|table   |partitions|type |possible_keys|key         |key_len|ref|rows|filtered|Extra|
    --|-----------|--------|----------|-----|-------------|------------|-------|---|----|--------|-----|
     1|SIMPLE     |employee|          |index|             |idx_emp_name|202    |   |  25|   100.0|     |
 

    為了演示 index 訪(fǎng)問(wèn)方式，我們使用了強(qiáng)制索引（force index）；否則，MySQL 選擇使用全表掃描（ALL）。

ALL表示全表掃描，這是一種 I/O 密集型的操作，通常意味著存在性能問(wèn)題。例如：

explain
select *
from employee;
id|select_type|table   |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
--|-----------|--------|----------|----|-------------|---|-------|---|----|--------|-----|
 1|SIMPLE     |employee|          |ALL |             |   |       |   |  25|   100.0|     |

 

因?yàn)?employee 表本身不大，而且我們查詢(xún)了所有的數(shù)據(jù)，這種情況下全表掃描反而是一個(gè)很好的訪(fǎng)問(wèn)方法。但是，以下查詢(xún)顯然需要進(jìn)行優(yōu)化：

explain
select *
from employee
where salary = 10000;
id|select_type|table   |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra      |
--|-----------|--------|----------|----|-------------|---|-------|---|----|--------|-----------|
 1|SIMPLE     |employee|          |ALL |             |   |       |   |  25|    10.0|Using where|

 

顯然，針對(duì)這種查詢(xún)語(yǔ)句，我們可以通過(guò)為 salary 字段創(chuàng)建一個(gè)索引進(jìn)行優(yōu)化。
Extra 字段

執(zhí)行計(jì)劃輸出中的 Extra 字段通常會(huì)顯示更多的信息，可以幫助我們發(fā)現(xiàn)性能問(wèn)題的所在。上文中我們已經(jīng)介紹了一些 Extra 字段的信息，需要重點(diǎn)關(guān)注的輸出內(nèi)容包括：

    Using where，表示將經(jīng)過(guò) WHERE 條件過(guò)濾后的數(shù)據(jù)傳遞給下個(gè)數(shù)據(jù)表或者返回客戶(hù)端。如果訪(fǎng)問(wèn)類(lèi)型為 ALL 或者 index，而 Extra 字段不是 Using where，意味著查詢(xún)語(yǔ)句可能存在問(wèn)題（除非就是想要獲取全部數(shù)據(jù)）。

    Using index condition，表示通過(guò)索引訪(fǎng)問(wèn)表之前，基于查詢(xún)條件中的索引字段進(jìn)行一次過(guò)濾，只返回必要的索引項(xiàng)。這也就是索引條件下推優(yōu)化。

    Using index，表示直接通過(guò)索引即可返回所需的字段信息（index-only scan），不需要訪(fǎng)問(wèn)表。對(duì)于 InnoDB，如果通過(guò)主鍵獲取數(shù)據(jù)，不會(huì)顯示 Using index，但是仍然是 index-only scan。此時(shí)，訪(fǎng)問(wèn)類(lèi)型為 index，key 字段顯示為 PRIMARY。

    Using filesort，意味著需要執(zhí)行額外的排序操作，通常需要占用大量的內(nèi)存或者磁盤(pán)。例如：

    explain
    select *
    from employee
    where dept_id =3
    order by hire_date;
    id|select_type|table   |partitions|type|possible_keys|key         |key_len|ref  |rows|filtered|Extra         |
    --|-----------|--------|----------|----|-------------|------------|-------|-----|----|--------|--------------|
     1|SIMPLE     |employee|          |ref |idx_emp_dept |idx_emp_dept|4      |const|   2|   100.0|Using filesort|
 

    索引通?？梢杂糜趦?yōu)化排序操作，我們可以為索引 idx_emp_dept 增加一個(gè) hire_date 字段來(lái)消除示例中的排序。

    Using temporary，意味著需要?jiǎng)?chuàng)建臨時(shí)表保存中間結(jié)果。例如：

    explain
    select dept_id,job_id, sum(salary)
    from employee
    group by dept_id, job_id;
    id|select_type|table   |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra          |
    --|-----------|--------|----------|----|-------------|---|-------|---|----|--------|---------------|
     1|SIMPLE     |employee|          |ALL |             |   |       |   |  25|   100.0|Using temporary|
 

    示例中的分組操作需要使用臨時(shí)表，同樣可以通過(guò)增加索引進(jìn)行優(yōu)化。

訪(fǎng)問(wèn)謂詞與過(guò)濾謂詞

在 SQL 中，WHERE 條件也被稱(chēng)為謂詞（predicate）。MySQL 數(shù)據(jù)庫(kù)中的謂詞存在以下三種使用方式：

    訪(fǎng)問(wèn)謂詞（access predicate），在執(zhí)行計(jì)劃的輸出中對(duì)應(yīng)于 key_len 和 ref 字段。訪(fǎng)問(wèn)謂詞代表了索引葉子節(jié)點(diǎn)遍歷的開(kāi)始和結(jié)束條件。
    索引過(guò)濾謂詞（index filter predicate），在執(zhí)行計(jì)劃中對(duì)應(yīng)于 Extra 字段的 Using index condition。索引過(guò)濾謂詞在遍歷索引葉子節(jié)點(diǎn)時(shí)用于判斷是否返回該索引項(xiàng)，但是不會(huì)用于判斷遍歷的開(kāi)始和結(jié)束條件，也就不會(huì)縮小索引掃描的范圍。
    表級(jí)過(guò)濾謂詞（table level filter predicate），在執(zhí)行計(jì)劃中對(duì)應(yīng)于 Extra 字段的 Using where。謂詞中的非索引字段條件在表級(jí)別進(jìn)行判斷，意味著數(shù)據(jù)庫(kù)需要訪(fǎng)問(wèn)表中的數(shù)據(jù)然后再應(yīng)用該條件。

一般來(lái)說(shuō)，對(duì)于相同的查詢(xún)語(yǔ)句，訪(fǎng)問(wèn)謂詞的性能好于索引過(guò)濾謂詞，索引過(guò)濾謂詞的性能好于表級(jí)過(guò)濾謂詞。

MySQL 執(zhí)行計(jì)劃中不會(huì)顯示每個(gè)條件對(duì)應(yīng)的謂詞類(lèi)型，而只是籠統(tǒng)地顯示使用了哪種謂詞類(lèi)型。我們創(chuàng)建一個(gè)示例表：

create table test (
  id int not null auto_increment primary key,
  col1 int,
  col2 int,
  col3 int);

insert into test(col1, col2, col3)
values (1,1,1), (2,4,6), (3,6,9);

create index test_idx on test (col1, col2);

analyze table test;

 

以下語(yǔ)句使用 col1 和 col2 作為查詢(xún)條件：

explain
select *
from test
where col1=1 and col2=1;
id|select_type|table|partitions|type|possible_keys|key     |key_len|ref        |rows|filtered|Extra|
--|-----------|-----|----------|----|-------------|--------|-------|-----------|----|--------|-----|
 1|SIMPLE     |test |          |ref |test_idx     |test_idx|10     |const,const|   1|   100.0|     |

 

其中，Extra 字段為空；key = test_idx 表示使用索引進(jìn)行查找，key_len = 10 就是 col1 和 col2 兩個(gè)字段的長(zhǎng)度（可空字段長(zhǎng)度加 1）；ref = const,const 表示使用了索引中的兩個(gè)字段和常量進(jìn)行比較，從而判斷是否返回?cái)?shù)據(jù)行。因此，該語(yǔ)句中的 WHERE 條件是一個(gè)訪(fǎng)問(wèn)謂詞。

接下來(lái)我們?nèi)匀皇褂?col1 和 col2 作為查詢(xún)條件，但是修改一下返回的字段：

explain
select id, col1, col2
from test
where col1=1 and col2=1;
id|select_type|table|partitions|type|possible_keys|key     |key_len|ref        |rows|filtered|Extra      |
--|-----------|-----|----------|----|-------------|--------|-------|-----------|----|--------|-----------|
 1|SIMPLE     |test |          |ref |test_idx     |test_idx|10     |const,const|   1|   100.0|Using index|

 

其中，Extra 字段中的 Using index 不是 Using index condition，它是一個(gè) index-only scan，因?yàn)樗械牟樵?xún)結(jié)果都可以通過(guò)索引直接返回（包括 id）；其他字段的信息和上面的示例相同。因此，該語(yǔ)句中的 WHERE 條件也是一個(gè)訪(fǎng)問(wèn)謂詞。

然后使用 col1 進(jìn)行范圍查詢(xún)：

explain
select *
from test
where col1 between 1 and 2;
id|select_type|table|partitions|type |possible_keys|key     |key_len|ref|rows|filtered|Extra                |
--|-----------|-----|----------|-----|-------------|--------|-------|---|----|--------|---------------------|
 1|SIMPLE     |test |          |range|test_idx     |test_idx|5      |   |   2|   100.0|Using index condition|

 

其中，Extra 字段中顯示為 Using index condition；key = test_idx 表示使用索引進(jìn)行范圍查找，key_len = 5 就是 col1 字段的長(zhǎng)度（可空字段長(zhǎng)度加 1）；ref 為空表示沒(méi)有訪(fǎng)問(wèn)謂詞。因此，該語(yǔ)句中的 WHERE 條件是一個(gè)索引過(guò)濾謂詞，查詢(xún)需要遍歷整個(gè)索引并且通過(guò)索引判斷是否訪(fǎng)問(wèn)表中的數(shù)據(jù)。

最后使用 col1 和 col3 作為查詢(xún)條件：

explain
select *
from test
where col1=1 and col3=1;
id|select_type|table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra      |
--|-----------|-----|----------|----|-------------|--------|-------|-----|----|--------|-----------|
 1|SIMPLE     |test |          |ref |test_idx     |test_idx|5      |const|   1|   33.33|Using where|

 

其中，Extra 字段中顯示為 Using where，表示訪(fǎng)問(wèn)表中的數(shù)據(jù)然后再應(yīng)用查詢(xún)條件 col3=1；key = test_idx 表示使用索引進(jìn)行查找，key_len = 5 就是 col1 字段的長(zhǎng)度（可空字段長(zhǎng)度加 1）；ref = const 表示常量等值比較；filtered = 33.33 意味著經(jīng)過(guò)查詢(xún)條件比較之后只保留三分之一的數(shù)據(jù)。因此，該語(yǔ)句中的 WHERE 條件是一個(gè)表級(jí)過(guò)濾謂詞，意味著數(shù)據(jù)庫(kù)需要訪(fǎng)問(wèn)表中的數(shù)據(jù)然后再應(yīng)用該條件。
完整字段信息

下表列出了 MySQL 執(zhí)行計(jì)劃中各個(gè)字段的作用：
 

 

格式化參數(shù)

MySQL EXPLAIN 語(yǔ)句支持使用 FORMAT 選項(xiàng)指定不同的輸出格式：

{EXPLAIN | DESCRIBE | DESC}
FORMAT = {TRADITIONAL | JSON | TREE}
explainable_stmt

 

默認(rèn)的格式為 TRADITIONAL，以表格的形式顯示輸出信息；JSON 選項(xiàng)表示以 JSON 格式顯示信息；MySQL 8.0.16 之后支持 TREE 選項(xiàng)，以樹(shù)形結(jié)構(gòu)輸出了比默認(rèn)格式更加詳細(xì)的信息，這也是唯一能夠顯示 hash join 的格式。

例如，以下語(yǔ)句輸出了 JSON 格式的執(zhí)行計(jì)劃：

explain
format=json
select *
from employee
where emp_id = 1;
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.00"
    },
    "table": {
      "table_name": "employee",
      "access_type": "const",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "emp_id"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.00",
        "eval_cost": "0.10",
        "prefix_cost": "0.00",
        "data_read_per_join": "568"
      },
      "used_columns": [
        "emp_id",
        "emp_name",
        "sex",
        "dept_id",
        "manager",
        "hire_date",
        "job_id",
        "salary",
        "bonus",
        "email"
      ]
    }
  }
}

 

其中，大部分的節(jié)點(diǎn)信息和表格形式的字段能夠?qū)?yīng)；但是也返回了一些額外的信息，尤其是各種操作的成本信息 cost_info，可以幫助我們了解不同執(zhí)行計(jì)劃之間的成本差異。

以下語(yǔ)句返回了樹(shù)狀結(jié)構(gòu)的執(zhí)行計(jì)劃：

explain
format=tree
select *
from employee e1
join employee e2
on e1.salary = e2.salary;
-> Inner hash join (e2.salary = e1.salary)  (cost=65.51 rows=63)
    -> Table scan on e2  (cost=0.02 rows=25)
    -> Hash
        -> Table scan on e1  (cost=2.75 rows=25)

 

從結(jié)果可以看出，該執(zhí)行計(jì)劃使用了 Inner hash join 實(shí)現(xiàn)兩個(gè)表的連接查詢(xún)。
執(zhí)行計(jì)劃中的分區(qū)表信息

如果 SELECT 語(yǔ)句使用了分區(qū)表，可以通過(guò) EXPLAIN 命令查看涉及的具體分區(qū)。執(zhí)行計(jì)劃輸出的 partitions 字段顯示了數(shù)據(jù)行所在的表分區(qū)。首先創(chuàng)建一個(gè)分區(qū)表：

create table trb1 (id int primary key, name varchar(50), purchased date)
    partition by range(id)
    (
        partition p0 values less than (3),
        partition p1 values less than (7),
        partition p2 values less than (9),
        partition p3 values less than (11)
    );

insert into trb1 values
    (1, 'desk organiser', '2003-10-15'),
    (2, 'CD player', '1993-11-05'),
    (3, 'TV set', '1996-03-10'),
    (4, 'bookcase', '1982-01-10'),
    (5, 'exercise bike', '2004-05-09'),
    (6, 'sofa', '1987-06-05'),
    (7, 'popcorn maker', '2001-11-22'),
    (8, 'aquarium', '1992-08-04'),
    (9, 'study desk', '1984-09-16'),
    (10, 'lava lamp', '1998-12-25');

 

然后查看使用 id 進(jìn)行范圍查詢(xún)時(shí)的執(zhí)行計(jì)劃：

explain
select * from trb1
where id < 5;
id|select_type|table|partitions|type |possible_keys|key    |key_len|ref|rows|filtered|Extra      |
--|-----------|-----|----------|-----|-------------|-------|-------|---|----|--------|-----------|
 1|SIMPLE     |trb1 |p0,p1     |range|PRIMARY      |PRIMARY|4      |   |   4|   100.0|Using where|

 

結(jié)果顯示查詢(xún)?cè)L問(wèn)了分區(qū) p0 和 p1。
獲取額外的執(zhí)行計(jì)劃信息

除了直接輸出的執(zhí)行計(jì)劃之外，EXPLAIN 命令還會(huì)產(chǎn)生一些額外信息，可以使用SHOW WARNINGS命令進(jìn)行查看。例如：

explain
select *
from department d
where exists (select 1 from employee e where e.dept_id = d.dept_id );
id|select_type|table|partitions|type|possible_keys|key         |key_len|ref           |rows|filtered|Extra                     |
--|-----------|-----|----------|----|-------------|------------|-------|--------------|----|--------|--------------------------|
 1|SIMPLE     |d    |          |ALL |PRIMARY      |            |       |              |   6|   100.0|                          |
 1|SIMPLE     |e    |          |ref |idx_emp_dept |idx_emp_dept|4      |hrdb.d.dept_id|   5|   100.0|Using index; FirstMatch(d)|

show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'hrdb.d.dept_id' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `hrdb`.`d`.`dept_id` AS `dept_id`,`hrdb`.`d`.`dept_name` AS `dept_name` from `hrdb`.`department` `d` semi join (`hrdb`.`employee` `e`) where (`hrdb`.`e`.`dept_id` = `hrdb`.`d`.`dept_id`)
2 rows in set (0.00 sec)

 

SHOW WARNINGS 命令輸出中的 Message 顯示了優(yōu)化器如何限定查詢(xún)語(yǔ)句中的表名和列名、應(yīng)用了重寫(xiě)和優(yōu)化規(guī)則后的查詢(xún)語(yǔ)句以及優(yōu)化過(guò)程的其他信息。

目前只有 SELECT 語(yǔ)句相關(guān)的額外信息可以通過(guò) SHOW WARNINGS 語(yǔ)句進(jìn)行查看，其他語(yǔ)句（DELETE、INSERT、REPLACE 和UPDATE）顯示的信息為空。
獲取指定連接的執(zhí)行計(jì)劃

EXPLAIN 語(yǔ)句也可以用于獲取指定連接中正在執(zhí)行的 SQL 語(yǔ)句的執(zhí)行計(jì)劃，語(yǔ)法如下：

EXPLAIN [FORMAT = {TRADITIONAL | JSON | TREE}] FOR CONNECTION connection_id;

 

其中，connection_id 是連接標(biāo)識(shí)符，可以通過(guò)字典表 INFORMATION_SCHEMA PROCESSLIST 或者 SHOW PROCESSLIST 命令獲取。如果某個(gè)會(huì)話(huà)中存在長(zhǎng)時(shí)間運(yùn)行的慢查詢(xún)語(yǔ)句，在另一個(gè)會(huì)話(huà)中執(zhí)行該命令可以獲得相關(guān)的診斷信息。

首先獲取當(dāng)前連接的會(huì)話(huà)標(biāo)識(shí)符：

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              30 |
+-----------------+
1 row in set (0.00 sec)

 

如果此時(shí)在當(dāng)前會(huì)話(huà)中獲取執(zhí)行計(jì)劃，將會(huì)返回錯(cuò)誤信息：

mysql> EXPLAIN FOR CONNECTION 30;
ERROR 3012 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE

 

因?yàn)橹挥?SELECT、UPDATE、INSERT、DELETE、REPLACE 語(yǔ)句支持執(zhí)行計(jì)劃，當(dāng)前正在執(zhí)行的是 EXPLAIN 語(yǔ)句。

在當(dāng)前會(huì)話(huà)中執(zhí)行一個(gè)大表查詢(xún)：

mysql> select * from large_table;

 

然后在另一個(gè)會(huì)話(huà)中執(zhí)行 EXPLAIN 命令：

explain for connection 30;
id|select_type|table      |partitions|type|possible_keys|key|key_len|ref|rows  |filtered|Extra|
--|-----------|-----------|----------|----|-------------|---|-------|---|------|--------|-----|
 1|SIMPLE     |large_table|          |ALL |             |   |       |   |244296|   100.0|     |

 

如果指定會(huì)話(huà)沒(méi)有正在運(yùn)行的語(yǔ)句，EXPLAIN 命令將會(huì)返回空結(jié)果。
獲取實(shí)際運(yùn)行的執(zhí)行計(jì)劃

MySQL 8.0.18 增加了一個(gè)新的命令：EXPLAIN ANALYZE。該語(yǔ)句用于運(yùn)行一個(gè)語(yǔ)句并且產(chǎn)生 EXPLAIN 結(jié)果，包括執(zhí)行時(shí)間和迭代器（iterator）信息，可以獲取優(yōu)化器的預(yù)期執(zhí)行計(jì)劃和實(shí)際執(zhí)行計(jì)劃之間的差異。

{EXPLAIN | DESCRIBE | DESC} ANALYZE select_statement

 

例如，以下 EXPLAIN 語(yǔ)句返回了查詢(xún)計(jì)劃和成本估算：

explain
format=tree
select *
from employee e
join department d
on (e.dept_id = d.dept_id )
where e.emp_id in(1, 2);
-> Nested loop inner join  (cost=1.61 rows=2)
    -> Filter: (e.emp_id in (1,2))  (cost=0.91 rows=2)
        -> Index range scan on e using PRIMARY  (cost=0.91 rows=2)
    -> Single-row index lookup on d using PRIMARY (dept_id=e.dept_id)  (cost=0.30 rows=1)

 

那么，實(shí)際上的執(zhí)行計(jì)劃和成本消耗情況呢？我們可以使用 EXPLAIN ANALYZE 語(yǔ)句查看：

explain analyze
select *
from employee e
join department d
on (e.dept_id = d.dept_id )
where e.emp_id in(1, 2);
-> Nested loop inner join  (cost=1.61 rows=2) (actual time=0.238..0.258 rows=2 loops=1)
    -> Filter: (e.emp_id in (1,2))  (cost=0.91 rows=2) (actual time=0.218..0.233 rows=2 loops=1)
        -> Index range scan on e using PRIMARY  (cost=0.91 rows=2) (actual time=0.214..0.228 rows=2 loops=1)
    -> Single-row index lookup on d using PRIMARY (dept_id=e.dept_id)  (cost=0.30 rows=1) (actual time=0.009..0.009 rows=1 loops=2)

 

對(duì)于每個(gè)迭代器，EXPLAIN ANALYZE 輸出了以下信息：

    估計(jì)執(zhí)行成本，某些迭代器不計(jì)入成本模型；
    估計(jì)返回行數(shù)；
    返回第一行的實(shí)際時(shí)間（ms）；
    返回所有行的實(shí)際時(shí)間（ms），如果存在多次循環(huán)，顯示平均時(shí)間；
    實(shí)際返回行數(shù)；
    循環(huán)次數(shù)。

在輸出結(jié)果中的每個(gè)節(jié)點(diǎn)包含了下面所有節(jié)點(diǎn)的匯總信息，所以最終的估計(jì)信息和實(shí)際信息如下：

-> Nested loop inner join  (cost=1.61 rows=2) (actual time=0.238..0.258 rows=2 loops=1)

 

查詢(xún)通過(guò)嵌套循環(huán)內(nèi)連接實(shí)現(xiàn)；估計(jì)成本為 1.61，估計(jì)返回 2 行數(shù)據(jù)；實(shí)際返回第一行數(shù)據(jù)的時(shí)間為 0.238 ms，實(shí)際返回所有數(shù)據(jù)的平均時(shí)間為 0.258 ms，實(shí)際返回了 2 行數(shù)據(jù)，嵌套循環(huán)操作執(zhí)行了 1 次。

循環(huán)的實(shí)現(xiàn)過(guò)程是首先通過(guò)主鍵掃描 employee 表并且應(yīng)用過(guò)濾迭代器：

    -> Filter: (e.emp_id in (1,2))  (cost=0.91 rows=2) (actual time=0.218..0.233 rows=2 loops=1)
        -> Index range scan on e using PRIMARY  (cost=0.91 rows=2) (actual time=0.214..0.228 rows=2 loops=1)

 

其中，應(yīng)用過(guò)濾迭代器返回第一行數(shù)據(jù)的時(shí)間為 0.218 ms，包括索引掃描的 0.214 ms；返回所有數(shù)據(jù)的平均時(shí)間為 0.233 ms，包括索引掃描的 0.228 ms；絕大部分時(shí)間都消耗在了索引掃描，總共返回了 2 條數(shù)據(jù)。

然后循環(huán)上一步返回的 2 條數(shù)據(jù)，掃描 department 表的主鍵返回其他數(shù)據(jù)：

    -> Single-row index lookup on d using PRIMARY (dept_id=e.dept_id)  (cost=0.30 rows=1) (actual time=0.009..0.009 rows=1 loops=2)

 

其中，loops=2 表示這個(gè)迭代器需要執(zhí)行 2 次；每次返回 1 行數(shù)據(jù)，所以?xún)蓚€(gè)實(shí)際時(shí)間都是 0.009 ms。

以上示例的預(yù)期執(zhí)行計(jì)劃和實(shí)際執(zhí)行計(jì)劃基本上沒(méi)有什么差異。但有時(shí)候并不一定如此，例如：

explain analyze
select *
from employee e
join department d
on (e.dept_id = d.dept_id )
where e.salary = 10000;
-> Nested loop inner join  (cost=3.63 rows=3) (actual time=0.427..0.444 rows=1 loops=1)
    -> Filter: (e.salary = 10000.00)  (cost=2.75 rows=3) (actual time=0.406..0.423 rows=1 loops=1)
        -> Table scan on e  (cost=2.75 rows=25) (actual time=0.235..0.287 rows=25 loops=1)
    -> Single-row index lookup on d using PRIMARY (dept_id=e.dept_id)  (cost=0.29 rows=1) (actual time=0.018..0.018 rows=1 loops=1)

 

我們使用 salary 字段作為過(guò)濾條件，該字段沒(méi)有索引。執(zhí)行計(jì)劃中的最大問(wèn)題在于估計(jì)返回的行數(shù)是 3，而實(shí)際返回的行數(shù)是 1；這是由于缺少字段的直方圖統(tǒng)計(jì)信息。

我們對(duì) employee 表進(jìn)行分析，收集字段的直方圖統(tǒng)計(jì)之后再查看執(zhí)行計(jì)劃：

analyze table employee update histogram on salary;

explain analyze
select *
from employee e
join department d
on (e.dept_id = d.dept_id )
where e.salary = 10000;
-> Nested loop inner join  (cost=3.10 rows=1) (actual time=0.092..0.105 rows=1 loops=1)
    -> Filter: (e.salary = 10000.00)  (cost=2.75 rows=1) (actual time=0.082..0.093 rows=1 loops=1)
        -> Table scan on e  (cost=2.75 rows=25) (actual time=0.056..0.080 rows=25 loops=1)
    -> Single-row index lookup on d using PRIMARY (dept_id=e.dept_id)  (cost=0.35 rows=1) (actual time=0.009..0.009 rows=1 loops=1)

 

估計(jì)返回的行數(shù)變成了 1，和實(shí)際執(zhí)行結(jié)果相同。

    ??除了本文介紹的各種 EXPLAIN 語(yǔ)句之外，MySQL 還提供了優(yōu)化器跟蹤（optimizer trace）功能，可以獲取關(guān)于優(yōu)化器的更多信息，具體可以參考 MySQL Internals:Tracing the Optimizer。

寫(xiě)作不易，需要鼓勵(lì)！歡迎關(guān)注??、評(píng)論??、點(diǎn)贊??