MySQL中Explain執(zhí)行計劃的案例

這篇文章給大家分享的是有關MySQL中Explain執(zhí)行計劃的案例的內容。小編覺得挺實用的，因此分享給大家做個參考。一起跟隨小編過來看看吧。

1. Explain 簡述

Explain 語句可以查看 MySQL 是如何執(zhí)行這條 SQL 語句的，包括使用索引情況、掃描行數(shù)等，這些信息對于 SQL 調優(yōu)來說十分重要，所以首先得看懂執(zhí)行計劃。

mysql> explain select * from user where name='one';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | user  | NULL       | ref  | a             | a    | 13      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)復制代碼

以上是一條簡單查詢語句的執(zhí)行計劃，這張表一共有12個字段，分別代表不同的含義，下面一一敘述。

• id: 表示 SQL 執(zhí)行的順序，值越大，優(yōu)先級越高。若值相同，執(zhí)行順序由優(yōu)化器決定。
• select_type: 表示 select 查詢語句的類型
• table: SQL 語句查詢的表名（或該表的別名），也可能是臨時表等不存在的表
• partitions: 查詢語句涉及的分區(qū)信息
• type: 關聯(lián)類型（訪問類型），決定了 MySQL 是如何查找表中行的。性能從最差到最優(yōu)依次是 ALL, index, range, index_merge, ref, eq_ref, const, system, NULL
• possible_keys: 展示了查詢語句可以使用的所有索引
• key: 展示了優(yōu)化器決定采用的索引名稱
• key_len: 展示了 MySQL 使用索引長度的字節(jié)數(shù)
• ref: 在 key 列記錄的索引中查找值所使用的列或常量
• rows: 掃描行數(shù)的估值
• filtered: 最終滿足查詢語句行數(shù)占存儲引擎返回總行數(shù)的百分比
• Extra: 其他執(zhí)行信息

以上只是對執(zhí)行計劃表各個字段的名詞解釋，接下來我會通過實際的例子來幫助大家（我自己）更好地理解其中 select_type, type, key_len, rows, Extra 這些重要的字段。

2. Explain 詳述

2.1 示例表結構

首先介紹本文中將用到的示例表表結構以及數(shù)據(jù)行：

CREATE TABLE `user`  (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',  `name` varchar(36) DEFAULT NULL COMMENT '姓名',  `age` int(11) NULL DEFAULT NULL COMMENT '年齡',  `email` varchar(36) DEFAULT NULL COMMENT '郵箱',
  PRIMARY KEY (`id`) USING BTREE,  INDEX `idx_age_name`(`age`, `name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;復制代碼

通過函數(shù)向表中插入1000000條測試數(shù)據(jù)。

CREATE DEFINER=`root`@`localhost` PROCEDURE `idata`()begin 
  declare i int; 
  set i=1; 
  while(i<=1000000)do 
    insert into user(id,name,age,email) values(i, CONCAT('name',i), i % 50, concat(i,'name@email.cn'));    set i=i+1; 
  end while;end復制代碼

2.2 select_type in Explain

執(zhí)行計劃中 select_type 字段表示 select 查詢語句的類型，常見類型有：

• SIMPLE: 簡單的查詢語句，不包括子查詢和關聯(lián)，如：

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

2.2.1 PRIMARY

若查詢語句中包含任何復雜的子部分，那么最外層部分會被標記為 PRIMARY，如：

mysql> explain select * from user where id=(select id from user where id=1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+|  1 | PRIMARY     | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)復制代碼

在這條 SQL 語句的執(zhí)行計劃中，第一條執(zhí)行的 SQL，即 select * from yser where id = (...) 就被標記為 PRIMARY

2.2.2 SUBQUERY

包含在 select 或 where 內容中的子查詢會被標記為 SUBQUERY，如上一條示例 SQL 的執(zhí)行計劃中第二條語句，即 select id from user where id=1 的 select_type 就被標記為了SUBQUERY。

2.2.3 DERIVED

包含在 FROM 關鍵字后的子查詢（即將子查詢的結果視為「表」），被視為「表」的子查詢會被標記為 DERIVED，其結果將被存放在臨時表中，如：

mysql> explain select * from (select id,name,count(*) from user where id=1) as user_1 where id=1;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | user       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)復制代碼

從執(zhí)行計劃中可以看到，第二條執(zhí)行的 SQL，即 select id,name,count(*) from user where id=1 的查詢類型是 DERIVED。

select_type 一共有12中查詢類型，具體釋義可以看官方文檔-explain_select_type

2.3 type in Explain

type 字段是執(zhí)行計劃中衡量 SQL 非常重要的依據(jù)，它展示了 SQL 語句的關聯(lián)類型（訪問類型），決定了 MySQL 是如何查找表中行的。

type 字段的值性能從最差到最優(yōu)依次是 ALL, index, range, index_merge, ref, eq_ref, const, system。

為了能更好地理解各個類型的含義，我對上述每一種類型都舉出了相應的示例。

并未全部列出，完整的解釋可以看官方文檔-EXPLAIN Join Types

2.3.1 ALL

ALL 表示全表掃描，意味著存儲引擎查找記錄時未走索引，所以它是性能最差的一種訪問類型，如

mysql> explain select * from user where age+2=20;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1002301 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec)復制代碼

可以看到 rows 行的值為1002301，即掃描了全表的所有數(shù)據(jù)（掃描行數(shù)的值實際為估算），如果在生產環(huán)境有這樣的 SQL，絕對是要優(yōu)化的。

我們知道在 where 查詢條件中，不應該對查詢字段使用函數(shù)或表達式（應該寫在等號不等號右側），不了解此內容的可以看看我的上一篇博客 —— 我所理解的MySQL(二)索引。

這條查詢語句在優(yōu)化后應該是: select * from user where age=18，去掉等號左側的表達式，優(yōu)化后的執(zhí)行計劃如下:

mysql> explain select * from user where age=18;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+|  1 | SIMPLE      | user  | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 39360 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)復制代碼

2.3.2 index

index 表示全索引樹掃描，由于掃描的是索引樹，所以比 ALL 形式的全表掃描性能要好。

同時，由于索引樹本身就是有序的，可以避免排序。

mysql> explain select id,age from user where name='name1';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+|  1 | SIMPLE      | user  | NULL       | index | NULL          | idx_age_name | 116     | NULL | 1002301 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)復制代碼

示例查詢語句如上所述，當查詢條件存在于聯(lián)合索引 idx_age_name 中，但又無法直接使用該索引（由于最左前綴原則），同時查詢列 id,age 也存在于聯(lián)合索引中，無須通過回表來獲取時，執(zhí)行計劃中的訪問類型 type 列就會是 index。

2.3.3 range

range 表示范圍掃描，準確的說是基于索引樹的范圍掃描，掃描的是部分索引樹，所以性能比 index 稍好。

需要注意的是，若使用 in 或者 or 時，也可以使用范圍掃描。

mysql> explain select * from user where age>18 and age<20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+|  1 | SIMPLE      | user  | NULL       | range | idx_age_name  | idx_age_name | 5       | NULL | 36690 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.01 sec)

mysql> explain select * from user where age=18 or age=20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+|  1 | SIMPLE      | user  | NULL       | range | idx_age_name  | idx_age_name | 5       | NULL | 78720 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)復制代碼

2.3.4 index_merge

index_merge 即索引合并，它表示在查詢時 MySQL 會使用多個索引。

MySQL 在 where 語句中存在多個查詢條件，并且其中存在多個字段可以分別使用到多個不同的索引，在這種情況下 MySQL 可以對多個索引樹同時進行掃描，最后將它們的結果進行合并，如：

mysql> explain select * from user where id=1 or age=18;
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+| id | select_type | table | partitions | type        | possible_keys        | key                  | key_len | ref  | rows  | filtered | Extra                                               |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+|  1 | SIMPLE      | user  | NULL       | index_merge | PRIMARY,idx_age_name | idx_age_name,PRIMARY | 5,4     | NULL | 39361 |   100.00 | Using sort_union(idx_age_name,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+1 row in set, 1 warning (0.00 sec)復制代碼

上面這條查詢語句中的 id=1 和 age=18 分別使用到了 PRIMARY 主鍵索引和 idx_age_name 聯(lián)合索引，最后再將滿足這兩個條件的記錄進行合并。

2.3.5 ref

ref 表示索引訪問（索引查找），這種訪問類型會出現(xiàn)在查詢條件中以非聚簇索引列的常量值進行查詢的情況。

比如在介紹全表掃描中優(yōu)化后 SQL 的訪問類型就是 ref。

2.3.6 eq_ref

eq_ref 這種訪問類型會出現(xiàn)在連接查詢時，通過聚簇索引進行連接的情況，此類型最多只返回一條符合條件的記錄。若表的聚簇索引為聯(lián)合索引，所有的索引列必須是等值查詢，如：

mysql> explain select * from user user1 inner join user user2 where user1.id=user2.id limit 10;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows    | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+|  1 | SIMPLE      | user1 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                | 1002301 |   100.00 | NULL  |
|  1 | SIMPLE      | user2 | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | all_in_one.user1.id |       1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+2 rows in set, 1 warning (0.00 sec)復制代碼

2.3.7 const

const 這種訪問類型會出現(xiàn)在通過聚簇索引進行常量等值查詢的情況，如：

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

2.4 key_len in Explain

在上一篇博客 —— 我所理解的MySQL(二)索引 中 5.2 部分字段匹配 中已經提到過關于索引長度的計算方式，這里再來總結一下。

2.4.1 字符類型

字符類型的字段若作為索引列，它的索引長度 = 字段定義長度 字符長度 + 是否默認NULL + 是否是變長字段*，其中：

• 字段定義長度 就是定義表結構時跟在字段類型后括號中的數(shù)字
• 字符長度 是常數(shù)，utf8=3, gbk=2, latin1=1
• 是否默認NULL 也是常數(shù)，若字段默認值為 NULL，該值為1，因為 NULL 需要額外的一個字節(jié)來表示；否則該值為0
• 是否是變長字段 也是常數(shù)，若該字段為變長字段，該值為2；否則該值為0

所謂的變長字段就是 varchar，它所占用的就是字段實際內容的長度而非定義字段時的長度。而定長字段，也就是 char 類型，它所占用的空間就是自定字段時的長度，若超過會被截取。

舉個例子，為上述實例表中添加一個字符類型字段的索引。

alter table user add index idx_name(`name`);復制代碼

然后通過 name 字段去做查詢，查看執(zhí)行計劃。

mysql> explain select * from user where name='name1';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+|  1 | SIMPLE      | user  | NULL       | ref  | idx_name      | idx_name | 111     | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)復制代碼

可以看到，執(zhí)行計劃中 key_len 一列的值為 111。

根據(jù)上述索引長度的計算公式，name 列字段定義長度為36，字符集類型為默認的 utf8，該字段默認允許 NULL，同時該字段是可變長字段 varchar。

所以 idx_name 索引的索引長度=36*3+1+2=111，恰如執(zhí)行計劃中顯示的值。

2.4.2 其他定長類型

對于定長類型的字段，其索引長度與它的數(shù)據(jù)類型長度是一致的。

需要注意的是，若該字段允許默認值為 NULL，與字符類型一樣，其索引長度也需要加上1。

mysql> explain select * from user where age=1;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+|  1 | SIMPLE      | user  | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 39366 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)復制代碼

如上面這個示例（本示例中索引只用到了 age 字段），age 字段為 int 類型，其索引長度本應為 4，但由于 age 字段默認允許為 NULL，所以它的索引長度就變成了5。

2.5 rows in Explain

掃描行數(shù)在執(zhí)行計劃中其實是一個估值，MySQL 會選擇 N 個不同的索引數(shù)據(jù)頁，計算平均值得到單頁索引基數(shù)，然后再乘以索引頁面數(shù)，就得到了掃描行數(shù)的估值。

掃描行數(shù)就是優(yōu)化器考量索引執(zhí)行效率的因素之一，一般而言掃描行數(shù)越少，執(zhí)行效率越高。

2.6 Extra in Explain

執(zhí)行計劃中 Extra 字段的常見類型有：

• Using index: 使用了覆蓋索引，以避免回表
• Using index condition: 使用了索引下推，具體可以看我的上一篇博客 —— 我所理解的MySQL(二)索引
• Using where: 表示MySQL 會通過 where 條件過濾記錄
  • 全表掃描：where 中有該表字段作為搜索條件
  • 掃描索引樹：where 中包含索引字段之外的其他字段作為搜索條件
• Using temporary: MySQL 在對查詢結果排序時會使用臨時表
• Using filesort: 對結果進行外部索引排序（文件排序），排序不走索引
  • 數(shù)據(jù)較少時在內存中排序，數(shù)據(jù)較多時在磁盤中排序
  • 盡量避免該信息出現(xiàn)在執(zhí)行計劃中

感謝各位的閱讀！關于MySQL中Explain執(zhí)行計劃的案例就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！