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

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

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

文章目錄

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

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

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

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

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

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

 

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

舉例來說：

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í)行計劃包含了 12 列信息，這些字段的含義我們在下文中進(jìn)行解讀。

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

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

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

type 被稱為連接類型（join type）或者訪問類型（access type），它顯示了 MySQL 如何訪問表中的數(shù)據(jù)。

訪問類型會直接影響到查詢語句的性能，性能從好到差依次為：

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

const 和 eq_ref 都意味著著通過 PRIMARY KEY 或者 UNIQUE 索引查找唯一值；它們的區(qū)別在于 const 對于整個查詢只返回一條數(shù)據(jù)，eq_ref 對于前面的結(jié)果集中的每條記錄只返回一條數(shù)據(jù)。例如以下查詢通過主鍵（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問方式，所以相當(dāng)于一個常量（constant）。

以下語句通過主鍵等值連接兩個表：

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

 

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

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

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

 

以上兩個查詢語句都是通過索引 idx_emp_dept 返回 employee 表中的數(shù)據(jù)。

ref_or_null 和 ref 的區(qū)別在于查詢中包含了 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，意味著通過索引訪問表中的數(shù)據(jù)之前，直接通過 WHERE 語句中出現(xiàn)的索引字段條件過濾數(shù)據(jù)。這是 MySQL 5.6 之后引入了一種優(yōu)化，叫做索引條件下推（Index Condition Pushdown）。

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

alter table employee modify column dept_id int not null;

 

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

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)查詢通過多個索引 range 訪問方式返回數(shù)據(jù)時，MySQL 可以先對這些索引掃描結(jié)果合并成一個，然后通過這個索引獲取表中的數(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) 是索引合并的算法，這里采用了并集算法（查詢條件使用了 or 運算符）。

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

value IN (SELECT primary_key FROM single_table WHERE some_expr)

 

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

value IN (SELECT key_column FROM single_table WHERE some_expr)

 

index表示掃描整個索引，以下兩種情況會使用這種訪問方式：

    查詢可以直接通過索引返回所需的字段信息，也就是 index-only scan。此時 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|
 

    查詢所需的 dept_id 字段通過掃描索引 idx_emp_dept 即可獲得，所以采用了 index 訪問類型。

    通過掃描索引執(zhí)行全表掃描，從而按照索引的順序返回數(shù)據(jù)。此時 Extra 字段不會出現(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 訪問方式，我們使用了強制索引（force index）；否則，MySQL 選擇使用全表掃描（ALL）。

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

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

 

因為 employee 表本身不大，而且我們查詢了所有的數(shù)據(jù)，這種情況下全表掃描反而是一個很好的訪問方法。但是，以下查詢顯然需要進(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|

 

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

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

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

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

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

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

    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 增加一個 hire_date 字段來消除示例中的排序。

    Using temporary，意味著需要創(chuàng)建臨時表保存中間結(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|
 

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

訪問謂詞與過濾謂詞

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

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

一般來說，對于相同的查詢語句，訪問謂詞的性能好于索引過濾謂詞，索引過濾謂詞的性能好于表級過濾謂詞。

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

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;

 

以下語句使用 col1 和 col2 作為查詢條件：

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 兩個字段的長度（可空字段長度加 1）；ref = const,const 表示使用了索引中的兩個字段和常量進(jìn)行比較，從而判斷是否返回數(shù)據(jù)行。因此，該語句中的 WHERE 條件是一個訪問謂詞。

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

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，它是一個 index-only scan，因為所有的查詢結(jié)果都可以通過索引直接返回（包括 id）；其他字段的信息和上面的示例相同。因此，該語句中的 WHERE 條件也是一個訪問謂詞。

然后使用 col1 進(jì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 字段的長度（可空字段長度加 1）；ref 為空表示沒有訪問謂詞。因此，該語句中的 WHERE 條件是一個索引過濾謂詞，查詢需要遍歷整個索引并且通過索引判斷是否訪問表中的數(shù)據(jù)。

最后使用 col1 和 col3 作為查詢條件：

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，表示訪問表中的數(shù)據(jù)然后再應(yīng)用查詢條件 col3=1；key = test_idx 表示使用索引進(jìn)行查找，key_len = 5 就是 col1 字段的長度（可空字段長度加 1）；ref = const 表示常量等值比較；filtered = 33.33 意味著經(jīng)過查詢條件比較之后只保留三分之一的數(shù)據(jù)。因此，該語句中的 WHERE 條件是一個表級過濾謂詞，意味著數(shù)據(jù)庫需要訪問表中的數(shù)據(jù)然后再應(yīng)用該條件。
完整字段信息

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

 

格式化參數(shù)

MySQL EXPLAIN 語句支持使用 FORMAT 選項指定不同的輸出格式：

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

 

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

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

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é)點信息和表格形式的字段能夠?qū)?yīng)；但是也返回了一些額外的信息，尤其是各種操作的成本信息 cost_info，可以幫助我們了解不同執(zhí)行計劃之間的成本差異。

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

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í)行計劃使用了 Inner hash join 實現(xiàn)兩個表的連接查詢。
執(zhí)行計劃中的分區(qū)表信息

如果 SELECT 語句使用了分區(qū)表，可以通過 EXPLAIN 命令查看涉及的具體分區(qū)。執(zhí)行計劃輸出的 partitions 字段顯示了數(shù)據(jù)行所在的表分區(qū)。首先創(chuàng)建一個分區(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)行范圍查詢時的執(zhí)行計劃：

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é)果顯示查詢訪問了分區(qū) p0 和 p1。
獲取額外的執(zhí)行計劃信息

除了直接輸出的執(zhí)行計劃之外，EXPLAIN 命令還會產(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)化器如何限定查詢語句中的表名和列名、應(yīng)用了重寫和優(yōu)化規(guī)則后的查詢語句以及優(yōu)化過程的其他信息。

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

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

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

 

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

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

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

 

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

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

 

因為只有 SELECT、UPDATE、INSERT、DELETE、REPLACE 語句支持執(zhí)行計劃，當(dāng)前正在執(zhí)行的是 EXPLAIN 語句。

在當(dāng)前會話中執(zhí)行一個大表查詢：

mysql> select * from large_table;

 

然后在另一個會話中執(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|     |

 

如果指定會話沒有正在運行的語句，EXPLAIN 命令將會返回空結(jié)果。
獲取實際運行的執(zhí)行計劃

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

{EXPLAIN | DESCRIBE | DESC} ANALYZE select_statement

 

例如，以下 EXPLAIN 語句返回了查詢計劃和成本估算：

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)

 

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

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)

 

對于每個迭代器，EXPLAIN ANALYZE 輸出了以下信息：

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

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

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

 

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

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

    -> 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)用過濾迭代器返回第一行數(shù)據(jù)的時間為 0.218 ms，包括索引掃描的 0.214 ms；返回所有數(shù)據(jù)的平均時間為 0.233 ms，包括索引掃描的 0.228 ms；絕大部分時間都消耗在了索引掃描，總共返回了 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 表示這個迭代器需要執(zhí)行 2 次；每次返回 1 行數(shù)據(jù)，所以兩個實際時間都是 0.009 ms。

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

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 字段作為過濾條件，該字段沒有索引。執(zhí)行計劃中的最大問題在于估計返回的行數(shù)是 3，而實際返回的行數(shù)是 1；這是由于缺少字段的直方圖統(tǒng)計信息。

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

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)

 

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

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

寫作不易，需要鼓勵！歡迎關(guān)注??、評論??、點贊??