mysql執行計劃定義及解讀

本文主要給大家介紹mysql執行計劃定義及解讀，文章內容都是筆者用心摘選和編輯的，mysql執行計劃定義及解讀具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下主題內容吧。

首先我們先簡單看一下執行計劃是什么東西。
關聯一下簡單的訂單和訂單商品join得到的結果：

ok，執行計劃表總共有12列，每一列的含義，我們一一道來。
1.id，表示每一個子句的操作順序，id越大，優先級越高。
對于每一個平級查詢，id是一致的，表示操作的優先級查詢；

對于有子查詢的sql，可以看到子查詢的優先級是比外層查詢要高的。也比較符合我們的主觀意識，先查子表，才能查主表。
但是注意，并不是所有帶有子查詢語句的sql一定會是子查詢，例如如下語句：

關于sql優化器的優化規則，我不再多講解，一是本章的主要內容不是講這方面的內容，二是我自己領悟的也不夠。

2.select_type,查詢類型
1）SIMPLE，簡單查詢，表示不包含子查詢或者UNION子句

2）PRIMARY，表示查詢語句包含子查詢或者UNION子句，PRIMARY表示外層的語句
3）SUBQUEY，子查詢語句
4）UNION  union  位于union中第二個及其以后的子查詢被標記為union

5）DERIVED  在from列表中包含的子查詢被標記為derived（衍生）
6）DEPENDENT UNION   UNION中的第二個或后面的SELECT語句，取決于外面的查詢
7）UNION RESULT  UNION的結果
6和7我本人并不是十分理解，不再加以展示

3.table,需要查詢表，這個有可能是數據庫表或臨時表

4.partitions,匹配到的分區，出現于分表查詢中的情況

5.type,訪問類型，這個指標是sql查詢優化中很重要的一個指標
常會出現的幾個枚舉值如下：
system：表中只有一行記錄，相當于系統表
const：通過索引一次命中，匹配一行數據   
eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配，常用語主鍵或唯一索引掃描   
ref：非唯一性索引掃描，返回匹配某個單獨值的所有行，用于=、<或>操作符帶索引的列   
range：只檢索給定范圍的行，使用一個索引來選擇行，一般用于between、<、>；   
index：只遍歷索引樹；   
all：全表掃描；
從上到下的執行效率是依次降低的，前5種情況都是理想的索引的情況。通常優化至少到range級別，最好能優化到ref。
列舉一些例子：
system是const的一個特例，表中只有一行數據時使用
我在訂單編號上建立了唯一索引，通過下圖（呃呃，不知道為啥傳不了圖片了，直接粘結果吧）可以看出，當主鍵或者唯一索引位于where條件時，那么執行的類型為const

    mysql> explain select * from `order` where title = 'C1086407110000019';
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | order | NULL       | const | 訂單          | 訂單   | 1023    | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from `order` where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | order | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

而eq_ref的區別為，eq_ref使用在關聯查詢上，如下：

mysql> explain select * from `order` left join order_item on `order`.id = order_item.order_id where `order`.id = 1;
+----+-------------+------------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | order      | NULL       | const | PRIMARY       | PRIMARY   | 8       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | order_item | NULL       | ref   | 訂單號        | 訂單號    | 8       | const |    2 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

對于非唯一性索引，比如我們的訂單中的用戶id，當我們查詢時，有可能會使用ref或者range結果如下：

mysql> explain select * from `order` where customer_id = 55029;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | order | NULL       | ref  | customer_id   | customer_id | 8       | const |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from `order` where customer_id > 55029 and customer_id < 55129;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | order | NULL       | range | customer_id   | customer_id | 8       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

當然如果沒有索引的字段，那么執行方式只能是ALL了。
但是值得注意的是，并不是有了索引就一定不會走索引的。如果字段的差異性太小，例如性別字段，即使建了索引，那么也不會走索引的。這里我舉一個例子，我拿來做測試的所有訂單表中只有一個賣家，這個字段上是有索引的，但是我們來查詢一下：

mysql> explain select * from `order` where seller_id = 19;
+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys    | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | order | NULL       | ALL  | 商家,seller_id   | NULL | NULL    | NULL | 2197 |   100.00 | Using where |
+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

發現實際上innodb并沒有選擇走索引，因為在這種情況下，走索引比不走索引的開銷還要大。關于開銷，Innodb是基于CBO進行判斷的（更古老的判斷方式是RBO，這些內容具體已經記得不是很清楚了，想要了解的需要再查找一些相關資料）。

上述幾個值并不是全部的枚舉值，例如還有fulltext、 index_merge、 unique_subquery等等，不過出現的頻次沒有什么上述的幾個枚舉值高而已。
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

看完以上關于mysql執行計劃定義及解讀，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業知識信息 ，可以持續關注我們的行業資訊欄目的。