天天看點
傳回

Mysql 查詢優化 - Explain 詳解（下）

Extra

顧名思義，

Extra

列是用來說明一些額外資訊的，我們可以通過這些額外資訊來更準确的了解

MySQL

到底将如何執行給定的查詢語句。

MySQL

提供的額外資訊有好幾十個，我們就不一個一個介紹了（都介紹了感覺我們的文章就跟文檔差不多了～），是以我們隻挑一些平時常見的或者比較重要的額外資訊介紹給大家哈。

  • No tables used

     當查詢語句的沒有

    FROM

     子句時将會提示該額外資訊，比如： 
    mysql> EXPLAIN SELECT 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

  • Impossible WHERE

     查詢語句的

    WHERE

     子句永遠為

    FALSE

     時将會提示該額外資訊，比方說： 
    mysql> EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.01 sec)

  • No matching min/max row

     當查詢清單處有

    MIN

     或者

    MAX

     聚集函數，但是并沒有符合

    WHERE

     子句中的搜尋條件的記錄時，将會提示該額外資訊，比方說： 
    mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                   |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No matching min/max row |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
1 row in set, 1 warning (0.00 sec)

  • Using index

     當我們的查詢清單以及搜尋條件中隻包含屬于某個索引的列，也就是在可以使用索引覆寫的情況下，在

    Extra

     列将會提示該額外資訊。比方說下邊這個查詢中隻需要用到

    idx_key1

     而不需要回表操作： 
    mysql> EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

  • Using index condition

     有些搜尋條件中雖然出現了索引列，但卻不能使用到索引，比如下邊這個查詢： 
    SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
    其中的

    key1 > 'z'

     可以使用到索引，但是

    key1 LIKE '%a'

     卻無法使用到索引，在以前版本的

    MySQL

     中，是按照下邊步驟來執行這個查詢的：
    • 先根據

      key1 > 'z'

       這個條件，從二級索引

      idx_key1

       中擷取到對應的二級索引記錄。
    • 根據上一步驟得到的二級索引記錄中的主鍵值進行回表，找到完整的使用者記錄再檢測該記錄是否符合

      key1 LIKE '%a'

       這個條件，将符合條件的記錄加入到最後的結果集。
    但是雖然

    key1 LIKE '%a'

     不能組成範圍區間參與

    range

     通路方法的執行，但這個條件畢竟隻涉及到了

    key1

     列，是以設計

    MySQL

     的大叔把上邊的步驟改進了一下：
    • 先根據

      key1 > 'z'

       這個條件，定位到二級索引

      idx_key1

       中對應的二級索引記錄。
    • 對于指定的二級索引記錄，先不着急回表，而是先檢測一下該記錄是否滿足

      key1 LIKE '%a'

       這個條件，如果這個條件不滿足，則該二級索引記錄壓根兒就沒必要回表。
    • 對于滿足

      key1 LIKE '%a'

       這個條件的二級索引記錄執行回表操作。
    我們說回表操作其實是一個随機

    IO

     ，比較耗時，是以上述修改雖然隻改進了一點點，但是可以省去好多回表操作的成本。設計

    MySQL

     的大叔們把他們的這個改進稱之為

    索引條件下推

     （英文名：

    Index Condition Pushdown

    ）。

    如果在查詢語句的執行過程中将要使用

    索引條件下推

     這個特性，在

    Extra

     列中将會顯示

    Using index condition

     ，比如這樣： 
    mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';
  +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
  +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  |  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |  266 |   100.00 | Using index condition |
  +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  1 row in set, 1 warning (0.01 sec)

  • Using where

     當我們使用全表掃描來執行對某個表的查詢，并且該語句的

    WHERE

     子句中有針對該表的搜尋條件時，在

    Extra

     列中會提示上述額外資訊。比如下邊這個查詢： 
    mysql> EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
    當使用索引通路來執行對某個表的查詢，并且該語句的

    WHERE

     子句中有除了該索引包含的列之外的其他搜尋條件時，在

    Extra

     列中也會提示上述額外資訊。比如下邊這個查詢雖然使用

    idx_key1

     索引執行查詢，但是搜尋條件中除了包含

    key1

     的搜尋條件

    key1 = 'a'

     ，還有包含

    common_field

     的搜尋條件，是以

    Extra

     列會顯示

    Using where

     的提示： 
    mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

  • Using join buffer (Block Nested Loop)

     在連接配接查詢執行過程過，當被驅動表不能有效的利用索引加快通路速度，

    MySQL

     一般會為其配置設定一塊名叫

    join buffer

     的記憶體塊來加快查詢速度，也就是我們所講的

    基于塊的嵌套循環算法

     ，比如下邊這個查詢語句： 
    mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL                                               |
|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.03 sec)
    可以在對

    s2

     表的執行計劃的

    Extra

     列顯示了兩個提示：

    • Using join buffer (Block Nested Loop)

       ：這是因為對表

      s2

       的通路不能有效利用索引，隻好退而求其次，使用

      join buffer

       來減少對

      s2

       表的通路次數，進而提高性能。

    • Using where

       ：可以看到查詢語句中有一個

      s1.common_field = s2.common_field

       條件，因為

      s1

       是驅動表，

      s2

       是被驅動表，是以在通路

      s2

       表時，

      s1.common_field

       的值已經确定下來了，是以實際上查詢

      s2

       表的條件就是

      s2.common_field = 一個常數

       ，是以提示了

      Using where

       額外資訊。

  • Not exists

     當我們使用左（外）連接配接時，如果

    WHERE

     子句中包含要求被驅動表的某個列等于

    NULL

     值的搜尋條件，而且那個列又是不允許存儲

    NULL

     值的，那麼在該表的執行計劃的

    Extra

     列就會提示

    Not exists

     額外資訊，比如這樣： 
    mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra                   |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL     | NULL    | NULL              | 9688 |   100.00 | NULL                    |
|  1 | SIMPLE      | s2    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s1.key1 |    1 |    10.00 | Using where; Not exists |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+
2 rows in set, 1 warning (0.00 sec)
    上述查詢中

    s1

     表是驅動表，

    s2

     表是被驅動表，

    s2.id

     列是不允許存儲

    NULL

     值的，而

    WHERE

     子句中又包含

    s2.id IS NULL

     的搜尋條件，這意味着必定是驅動表的記錄在被驅動表中找不到比對

    ON

     子句條件的記錄才會把該驅動表的記錄加入到最終的結果集，是以對于某條驅動表中的記錄來說，如果能在被驅動表中找到1條符合

    ON

     子句條件的記錄，那麼該驅動表的記錄就不會被加入到最終的結果集，也就是說我們沒有必要到被驅動表中找到全部符合ON子句條件的記錄，這樣可以稍微節省一點性能。 
    小貼士：

右（外）連接配接可以被轉換為左（外）連接配接，是以就不提右（外）連接配接的情況了。

  • Using intersect(...)

     、

    Using union(...)

     和

    Using sort_union(...)

     如果執行計劃的

    Extra

     列出現了

    Using intersect(...)

     提示，說明準備使用

    Intersect

     索引合并的方式執行查詢，括号中的

    ...

     表示需要進行索引合并的索引名稱；如果出現了

    Using union(...)

     提示，說明準備使用

    Union

     索引合并的方式執行查詢；出現了

    Using sort_union(...)

     提示，說明準備使用

    Sort-Union

     索引合并的方式執行查詢。比如這個查詢的執行計劃： 
    mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND key3 = 'a';
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+-------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                           |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+-------------------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | index_merge | idx_key1,idx_key3 | idx_key3,idx_key1 | 303,303 | NULL |    1 |   100.00 | Using intersect(idx_key3,idx_key1); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+-------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
    其中

    Extra

     列就顯示了

    Using intersect(idx_key3,idx_key1)

     ，表明

    MySQL

     即将使用

    idx_key3

     和

    idx_key1

     這兩個索引進行

    Intersect

     索引合并的方式執行查詢。 
    小貼士：

剩下兩種類型的索引合并的Extra列資訊就不一一舉例子了，自己寫個查詢瞅瞅呗～

  • Zero limit

     當我們的

    LIMIT

     子句的參數為 時，表示壓根兒不打算從表中讀出任何記錄，将會提示該額外資訊，比如這樣： 
    mysql> EXPLAIN SELECT * FROM s1 LIMIT 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Zero limit |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
1 row in set, 1 warning (0.00 sec)

  • Using filesort

     有一些情況下對結果集中的記錄進行排序是可以使用到索引的，比如下邊這個查詢： 
    mysql> EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | index | NULL          | idx_key1 | 303     | NULL |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)
    這個查詢語句可以利用

    idx_key1

     索引直接取出

    key1

     列的10條記錄，然後再進行回表操作就好了。但是很多情況下排序操作無法使用到索引，隻能在記憶體中（記錄較少的時候）或者磁盤中（記錄較多的時候）進行排序，設計

    MySQL

     的大叔把這種在記憶體中或者磁盤上進行排序的方式統稱為檔案排序（英文名：

    filesort

     ）。如果某個查詢需要使用檔案排序的方式執行查詢，就會在執行計劃的

    Extra

     列中顯示

    Using filesort

     提示，比如這樣： 
    mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
    需要注意的是，如果查詢中需要使用

    filesort

     的方式進行排序的記錄非常多，那麼這個過程是很耗費性能的，我們最好想辦法将使用

    檔案排序

     的執行方式改為使用索引進行排序。

  • Using temporary

     在許多查詢的執行過程中，

    MySQL

     可能會借助臨時表來完成一些功能，比如去重、排序之類的，比如我們在執行許多包含

    DISTINCT

     、

    GROUP BY

     、

    UNION

     等子句的查詢過程中，如果不能有效利用索引來完成查詢，

    MySQL

     很有可能尋求通過建立内部的臨時表來執行查詢。如果查詢中使用到了内部的臨時表，在執行計劃的

    Extra

     列将會顯示

    Using temporary

     提示，比方說這樣： 
    mysql> EXPLAIN SELECT DISTINCT common_field FROM s1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
    再比如： 
    mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
    不知道大家注意到沒有，上述執行計劃的

    Extra

     列不僅僅包含

    Using temporary

     提示，還包含

    Using filesort

     提示，可是我們的查詢語句中明明沒有寫

    ORDER BY

     子句呀？這是因為

    MySQL

     會在包含

    GROUP BY

     子句的查詢中預設添加上

    ORDER BY

     子句，也就是說上述查詢其實和下邊這個查詢等價： 
    EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field ORDER BY common_field;
    如果我們并不想為包含

    GROUP BY

     子句的查詢進行排序，需要我們顯式的寫上

    ORDER BY NULL

     ，就像這樣： 
    mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field ORDER BY NULL;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
    這回執行計劃中就沒有

    Using filesort

    的提示了，也就意味着執行查詢時可以省去對記錄進行檔案排序的成本了。

    另外，執行計劃中出現

    Using temporary

     并不是一個好的征兆，因為建立與維護臨時表要付出很大成本的，是以我們最好能使用索引來替代掉使用臨時表，比方說下邊這個包含

    GROUP BY

     子句的查詢就不需要使用臨時表： 
    mysql> EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9688 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
    從

    Extra

     的

    Using index

     的提示裡我們可以看出，上述查詢隻需要掃描

    idx_key1

     索引就可以搞定了，不再需要臨時表了。

  • Start temporary, End temporary

     我們前邊唠叨子查詢的時候說過，查詢優化器會優先嘗試将

    IN

     子查詢轉換成

    semi-join

     ，而

    semi-join

     又有好多種執行政策，當執行政策為

    DuplicateWeedout

     時，也就是通過建立臨時表來實作為外層查詢中的記錄進行去重操作時，驅動表查詢執行計劃的

    Extra

     列将顯示

    Start temporary

     提示，被驅動表查詢執行計劃的

    Extra

     列将顯示

    End temporary

     提示，就是這樣： 
    mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a');
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+
|  1 | SIMPLE      | s2    | NULL       | ALL  | idx_key3      | NULL     | NULL    | NULL              | 9954 |    10.00 | Using where; Start temporary |
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s2.key3 |    1 |   100.00 | End temporary                |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

  • LooseScan

     在将

    In

     子查詢轉為

    semi-join

     時，如果采用的是

    LooseScan

     執行政策，則在驅動表執行計劃的

    Extra

     列就是顯示

    LooseScan

     提示，比如這樣： 
    mysql> EXPLAIN SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'z');
+----+-------------+-------+------------+-------+---------------+----------+---------+-------------------+------+----------+-------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref               | rows | filtered | Extra                               |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------------------+------+----------+-------------------------------------+
|  1 | SIMPLE      | s2    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL              |  270 |   100.00 | Using where; Using index; LooseScan |
|  1 | SIMPLE      | s1    | NULL       | ref   | idx_key3      | idx_key3 | 303     | xiaohaizi.s2.key1 |    1 |   100.00 | NULL                                |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------------------+------+----------+-------------------------------------+
2 rows in set, 1 warning (0.01 sec)

  • FirstMatch(tbl_name)

     在将

    In

     子查詢轉為

    semi-join

     時，如果采用的是

    FirstMatch

     執行政策，則在被驅動表執行計劃的

    Extra

     列就是顯示

    FirstMatch(tbl_name)

     提示，比如這樣： 
    mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key1 FROM s2 where s1.key3 = s2.key3);
+----+-------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys     | key      | key_len | ref               | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-----------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key3          | NULL     | NULL    | NULL              | 9688 |   100.00 | Using where                 |
|  1 | SIMPLE      | s2    | NULL       | ref  | idx_key1,idx_key3 | idx_key3 | 303     | xiaohaizi.s1.key3 |    1 |     4.87 | Using where; FirstMatch(s1) |
+----+-------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-----------------------------+
2 rows in set, 2 warnings (0.00 sec)

Json格式的執行計劃

我們上邊介紹的

EXPLAIN

語句輸出中缺少了一個衡量執行計劃好壞的重要屬性 —— 成本。不過設計

MySQL

的大叔貼心的為我們提供了一種檢視某個執行計劃花費的成本的方式：

  • 在

    EXPLAIN

     單詞和真正的查詢語句中間加上

    FORMAT=JSON

     。

這樣我們就可以得到一個

json

格式的執行計劃，裡邊兒包含該計劃花費的成本，比如這樣：

mysql> EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G
*************************** 1. row ***************************

EXPLAIN: {
  "query_block": {
    "select_id": 1,     # 整個查詢語句隻有1個SELECT關鍵字，該關鍵字對應的id号為1
    "cost_info": {
      "query_cost": "3197.16"   # 整個查詢的執行成本預計為3197.16
    },
    "nested_loop": [    # 幾個表之間采用嵌套循環連接配接算法執行
    
    # 以下是參與嵌套循環連接配接算法的各個表的資訊
      {
        "table": {
          "table_name": "s1",   # s1表是驅動表
          "access_type": "ALL",     # 通路方法為ALL，意味着使用全表掃描通路
          "possible_keys": [    # 可能使用的索引
            "idx_key1"
          ],
          "rows_examined_per_scan": 9688,   # 查詢一次s1表大緻需要掃描9688條記錄
          "rows_produced_per_join": 968,    # 驅動表s1的扇出是968
          "filtered": "10.00",  # condition filtering代表的百分比
          "cost_info": {
            "read_cost": "1840.84",     # 稍後解釋
            "eval_cost": "193.76",      # 稍後解釋
            "prefix_cost": "2034.60",   # 單次查詢s1表總共的成本
            "data_read_per_join": "1M"  # 讀取的資料量
          },
          "used_columns": [     # 執行查詢中涉及到的列
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ],
          
          # 對s1表通路時針對單表查詢的條件
          "attached_condition": "((`xiaohaizi`.`s1`.`common_field` = 'a') and (`xiaohaizi`.`s1`.`key1` is not null))"
        }
      },
      {
        "table": {
          "table_name": "s2",   # s2表是被驅動表
          "access_type": "ref",     # 通路方法為ref，意味着使用索引等值比對的方式通路
          "possible_keys": [    # 可能使用的索引
            "idx_key2"
          ],
          "key": "idx_key2",    # 實際使用的索引
          "used_key_parts": [   # 使用到的索引列
            "key2"
          ],
          "key_length": "5",    # key_len
          "ref": [      # 與key2列進行等值比對的對象
            "xiaohaizi.s1.key1"
          ],
          "rows_examined_per_scan": 1,  # 查詢一次s2表大緻需要掃描1條記錄
          "rows_produced_per_join": 968,    # 被驅動表s2的扇出是968（由于後邊沒有多餘的表進行連接配接，是以這個值也沒啥用）
          "filtered": "100.00",     # condition filtering代表的百分比
          
          # s2表使用索引進行查詢的搜尋條件
          "index_condition": "(`xiaohaizi`.`s1`.`key1` = `xiaohaizi`.`s2`.`key2`)",
          "cost_info": {
            "read_cost": "968.80",      # 稍後解釋
            "eval_cost": "193.76",      # 稍後解釋
            "prefix_cost": "3197.16",   # 單次查詢s1、多次查詢s2表總共的成本
            "data_read_per_join": "1M"  # 讀取的資料量
          },
          "used_columns": [     # 執行查詢中涉及到的列
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ]
        }
      }
    ]
  }
}
1 row in set, 2 warnings (0.00 sec)

我們使用

#

後邊跟随注釋的形式為大家解釋了

EXPLAIN FORMAT=JSON

語句的輸出内容，但是大家可能有疑問

"cost_info"

裡邊的成本看着怪怪的，它們是怎麼計算出來的？先看

s1

表的

"cost_info"

部分：

"cost_info": {
    "read_cost": "1840.84",
    "eval_cost": "193.76",
    "prefix_cost": "2034.60",
    "data_read_per_join": "1M"
}

  • read_cost

     是由下邊這兩部分組成的：

    • IO

       成本
    • 檢測

      rows × (1 - filter)

       條記錄的

      CPU

       成本 
    小貼士：

rows和filter都是我們前邊介紹執行計劃的輸出列，在JSON格式的執行計劃中，rows相當于rows_examined_per_scan，filtered名稱不變。

  • eval_cost

    是這樣計算的：

    檢測

    rows × filter

     條記錄的成本。

  • prefix_cost

     就是單獨查詢

    s1

     表的成本，也就是：

    read_cost + eval_cost

  • data_read_per_join

     表示在此次查詢中需要讀取的資料量，我們就不多唠叨這個了。 
小貼士：

大家其實沒必要關注MySQL為啥使用這麼古怪的方式計算出read_cost和eval_cost，關注prefix_cost是查詢s1表的成本就好了。

對于

s2

表的

"cost_info"

部分是這樣的：

"cost_info": {
    "read_cost": "968.80",
    "eval_cost": "193.76",
    "prefix_cost": "3197.16",
    "data_read_per_join": "1M"
}

由于

s2

表是被驅動表，是以可能被讀取多次，這裡的

read_cost

和

eval_cost

是通路多次

s2

表後累加起來的值，大家主要關注裡邊兒的

prefix_cost

的值代表的是整個連接配接查詢預計的成本，也就是單次查詢

s1

表和多次查詢

s2

表後的成本的和，也就是：

968.80 + 193.76 + 2034.60 = 3197.16

Extented EXPLAIN

最後，設計

MySQL

的大叔還為我們留了個彩蛋，在我們使用

EXPLAIN

語句檢視了某個查詢的執行計劃後，緊接着還可以使用

SHOW WARNINGS

語句檢視與這個查詢的執行計劃有關的一些擴充資訊，比如這樣：

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | s2    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL              | 9954 |    90.00 | Using where |
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s2.key1 |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `xiaohaizi`.`s1`.`key1` AS `key1`,`xiaohaizi`.`s2`.`key1` AS `key1` from `xiaohaizi`.`s1` join `xiaohaizi`.`s2` where ((`xiaohaizi`.`s1`.`key1` = `xiaohaizi`.`s2`.`key1`) and (`xiaohaizi`.`s2`.`common_field` is not null))
1 row in set (0.00 sec)

大家可以看到

SHOW WARNINGS

展示出來的資訊有三個字段，分别是

Level

、

Code

、

Message

。我們最常見的就是

Code

為

1003

的資訊，當

Code

值為

1003

時，

Message

字段展示的資訊類似于查詢優化器将我們的查詢語句重寫後的語句。比如我們上邊的查詢本來是一個左（外）連接配接查詢，但是有一個

s2.common_field IS NOT NULL

的條件，着就會導緻查詢優化器把左（外）連接配接查詢優化為内連接配接查詢，從

SHOW WARNINGS

的

Message

字段也可以看出來，原本的

LEFT JOIN

已經變成了

JOIN

。

但是大家一定要注意，我們說

Message

字段展示的資訊類似于查詢優化器将我們的查詢語句重寫後的語句，并不是等價于，也就是說

Message

字段展示的資訊并不是标準的查詢語句，在很多情況下并不能直接拿到黑框框中運作，它隻能作為幫助我們了解查

MySQL

将如何執行查詢語句的一個參考依據而已。

mysql 資料庫 explain
上一篇: explain 分析 ：MySQL優化之慢查詢的定位及優化-SQL優化必備
下一篇: mysql的explain執行計劃_MySQL之Explain(執行計劃)

繼續閱讀