[[339409]]

Explain簡介

Explain關(guān)鍵字是Mysql中sql優(yōu)化的常用「關(guān)鍵字」，通常都會使用Explain來「查看sql的執(zhí)行計(jì)劃，而不用執(zhí)行sql」，從而快速的找出sql的問題所在。

在講解Explain之前首先創(chuàng)建需要的「用戶表user、角色表role、以及用戶角色關(guān)系表role_user」作為測試用的表： 

// 用戶表  
DROP TABLE IF EXISTS `user`;  
CREATE TABLE `user` (  
  `id` int(11) NOT NULL,  
  `name` varchar(25) DEFAULT NULL,  
  `age` int(11)  NOT NULL DEFAULT 0,  
  `update_time` datetime DEFAULT NULL,  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
INSERT INTO `user` (`id`, `name`, `age`,`update_time`) VALUES (1,'張三',23,'2020-12-22 15:27:18'), (2,'李四',24,'2020-06-21 15:27:18'), (3,'王五',25,'2020-07-20 15:27:18'); 
 DROP TABLE IF EXISTS `role`;  
CREATE TABLE `role` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` varchar(10) DEFAULT NULL,  
  PRIMARY KEY (`id`),  
  KEY `index_name` (`name`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
INSERT INTO `role` (`id`, `name`) VALUES (1,'產(chǎn)品經(jīng)理'),(2,'技術(shù)經(jīng)理'),(3,'項(xiàng)目總監(jiān)');  
DROP TABLE IF EXISTS `role_user`;  
CREATE TABLE `role_user` (  
  `id` int(11) NOT NULL,  
  `role_id` int(11) NOT NULL,  
  `user_id` int(11) NOT NULL,  
  PRIMARY KEY (`id`),  
  KEY `index_role_user_id` (`role_id`,`user_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
INSERT INTO `role_user` (`id`, `role_id`, `user_id`) VALUES (1,2,1),(2,1,2),(3,3,3);  

我們首先執(zhí)行一條sql：explain select * from user where id =2;，執(zhí)行后可以看到執(zhí)行的結(jié)果如下：

可以看到這里有12個(gè)字段那個(gè)且都有對應(yīng)的值，這就是explain的執(zhí)行計(jì)劃，能看懂這個(gè)執(zhí)行計(jì)劃，你離精通sql優(yōu)化就不遠(yuǎn)了，下面就來詳細(xì)的介紹這12個(gè)字段分別表示什么意思。

id字段

id表示執(zhí)行select查詢語句的序號，它是sql執(zhí)行的順序的標(biāo)識，sql按照id從大到小執(zhí)行，id相同的為一組，從上到下執(zhí)行。

什么意思呢？例如執(zhí)行這條sql：explain select * from user where id in (select user_id from role_user); 

+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+ 
| id | select_type | table     | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra                                                                             | 
+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+ 
|  1 | SIMPLE      | user      | NULL       | ALL   | PRIMARY       | NULL               | NULL    | NULL |    3 |   100.00 | NULL                                                                              | 
|  1 | SIMPLE      | role_user | NULL       | index | NULL          | index_role_user_id | 8       | NULL |    3 |    33.33 | Using where; Using index; FirstMatch(user); Using join buffer (Block Nested Loop) | 
+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+ 

 顯示出的兩者的id都相同，便表示sql的執(zhí)行從上往下執(zhí)行，第一條記錄對應(yīng)的是user表，然后第二條記錄對應(yīng)的是role_user表，這種是id相同的情況。

若是id不同，例如執(zhí)行下面的sql：explain select (select 1 from user limit 1) from role;： 

+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+  
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |  
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+  
|  1 | PRIMARY     | role  | NULL       | index | NULL          | index_name | 33      | NULL |    3 |   100.00 | Using index |  
|  2 | SUBQUERY    | user  | NULL       | index | NULL          | PRIMARY    | 4       | NULL |    3 |   100.00 | Using index |  
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ 

就會看到有兩條記錄，并且兩條記錄的id會不一樣，id越大的就越先執(zhí)行，可以看到id=2的執(zhí)行的是user表，也就是子查詢部分，最后執(zhí)行最外層的部分。

「結(jié)論：」 這個(gè)就是id標(biāo)識sql的執(zhí)行順序，一般在復(fù)雜查詢中會有多條記錄，簡單查詢只有一條記錄，復(fù)雜查詢中id相同的為一組，執(zhí)行的順序是從上往下，而id越大的越先執(zhí)行；Mysql 8中會存在對子查詢進(jìn)行優(yōu)化，所以有時(shí)候即使是復(fù)雜查詢，也只有一條記錄。

select_type字段

select_type表示查詢的類型，也就是對應(yīng)的是簡單查詢還是復(fù)雜查詢，若是復(fù)雜查詢又包含：「簡單的子查詢、from子句的子查詢、union查詢」。下面就分別來看看select_type中的所有查詢類型。

simple

simple表示簡單查詢，不含有任何的復(fù)雜查詢。

PRIMARY

復(fù)雜查詢中「最外層的select語句的查詢類型就是PRIMARY」，例如執(zhí)行下面的sql：explain select * from role where id = (select id from role_user where role_id = (select id from user where id = 2));

最外層的select，也就是select * from role where id =？會被標(biāo)記為PRIMARY類型。

SUBQUERY

在「select或者where中包含的子查詢」會被表示為SUBQUERY類型，例如上一句執(zhí)行的sql中就有兩次的子查詢?yōu)镾UBQUERY。

DERIVED

「DERIVED表示的是派生表或者衍生表的意思，在from包含的子查詢中會被表示為DERIVED類型」，Mysql會遞歸執(zhí)行這些子查詢，并且把結(jié)果放在臨時(shí)表中。執(zhí)行sql：explain select * from (select name from user union select name from role) a where a.name = '張三';

在Mysql 5.7以上的版本中對其做了優(yōu)化，新增了derived_merge(派生合并)，可以加快查詢效率。

UNION

在出現(xiàn)「UNION查詢語句中，第二個(gè)select的查詢語句就會被表示為UNION」：

UNION RESULT

「UNION查詢語句的結(jié)果被標(biāo)記為UNION RESULT」，如上面執(zhí)行的sql：explain select * from (select name from user union select name from role) a where a.name = '張三';

第四行記錄中從table字段中可以看出，第四行的記錄來源于第二行和第三行<union2,3>，因此一個(gè)UNION查詢語句的結(jié)果就會被標(biāo)記為UNION RESULT

其它

上面的七個(gè)select_type都是比較常見的，還有一些不常見的，作為了解就好：

1.  DEPENDENT UNION：也表示UNION查詢語句中第二個(gè)或者后面的語句，但是取決于外面的查詢。
2.  DEPENDENT SUBQUERY：子查詢中的第一個(gè)select語句，也是依賴于外部的查詢。
3.  UNCACHEABLE SUBQUERY：子查詢的結(jié)果不能被緩存，必須重新評估外連接的第一行。

table字段

這個(gè)很容易看出「table字段表示的是查詢的是哪個(gè)表」，一個(gè)是已經(jīng)存在的表，比如上面的user、role都是我們自己創(chuàng)建的表，也可以表示衍生表。

比如：UNION RESULT的table字段表示為<union2,3>，也就是查詢的是第二行和第三行的結(jié)果記錄。

type字段

「type字段表示的sql關(guān)聯(lián)的類型或者說是訪問的類型」。從這個(gè)字段中我們可以確定這條sql查找數(shù)據(jù)庫表的時(shí)候，查找記錄的大概范圍是怎么樣的，直接就能體現(xiàn)sql的效率問題。

type字段的類型也是有比較多，主要常見掌握的有以下幾個(gè)：system、const 、eq_ref 、ref 、range 、index 、ALL。它的性能體現(xiàn)是從高到低，即system > const > eq_ref > ref > range > index > ALL，下面就來詳細(xì)的說一說這屬性。

system

system是const的特例，「表示表中只有一行記錄」，這個(gè)幾乎不會出現(xiàn)，也作為了解。

const

const表示通過索引一次就查找到了數(shù)據(jù)，一般const出現(xiàn)在「唯一索引或者主鍵索引中使用等值查詢」，因?yàn)楸碇兄挥幸粭l數(shù)據(jù)匹配，所以查找的速度很快。例子：explain select * from user where id =2;

eq_ref

eq_ref表示使用唯一索引或者主鍵索引掃描作為表鏈接匹配條件，對于每一個(gè)索引鍵，表中只有一條記錄與之匹配。例如：explain select * from user left join role_user on user.id = role_user.user_id left join role on role_user.role_id=role.id;

ref

ref性能比eq_ref差，也表示表的鏈接匹配條件，也就是使用哪些表字段作為查詢索引列上的值，ref與eq_ref的區(qū)別就是eq_ref使用的是唯一索引或者主鍵索引。

ref掃描后的結(jié)果可能會找到多條符合條件的行數(shù)據(jù)，本質(zhì)上是一種索引訪問，返回匹配的行。例如：explain select * from user where name = '張三';

range

「range使用索引來檢索給定范圍的行數(shù)據(jù)，一般是在where后面使用between、<>、in等查詢語句就會出現(xiàn)range」：explain select * from user where id > 2;

index

index表示會遍歷索引樹，index回避ALL速度快一些，但是出現(xiàn)index說明需要檢查自己的索引是否使用正確：explain select id from user;

ALL

「ALL與index的區(qū)別就是ALL是從硬盤中讀取，而index是從索引文件中讀取」，ALL全表掃描意味著Mysql會從表的頭到尾進(jìn)行掃描，這時(shí)候表示通常需要增加索引來進(jìn)行優(yōu)化了，或者說是查詢中并沒有使用索引作為條件進(jìn)行查詢：explain select * from user;

possible_keys字段

possible_keys表示這一列查詢語句可能使用到的索引，僅僅只是可能，列出來的索引并不一定真正的使用到。

當(dāng)沒有使用索引為NULL時(shí)，說明需要增加索引來優(yōu)化查詢了，若是表的數(shù)據(jù)比較少的話，數(shù)據(jù)庫覺得全表掃描更快，也可能為NULL。

key字段

key字段與possible_keys的區(qū)別就是，表示的真正使用到的索引，即possible_keys中包含key的值。

若是想Mysql使用或者忽視possible_keys中的索引，可以使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len字段

表示sql查詢語句中索引使用到的字節(jié)數(shù)，這個(gè)字節(jié)數(shù)并不是實(shí)際的長度，而是通過計(jì)算查詢中使用到的索引中的長度得出來的，顯示的是索引字段最大的可能長度。

一般來說在不損失精度的前提下，key_len是越小越好，比如上面的測試表的id為int類型，int類型由4個(gè)字節(jié)組成：explain select * from user where id =2;

key_len對于不同的類型有自己的計(jì)算規(guī)則，具體的計(jì)算規(guī)則如下所示：

數(shù)據(jù)類型	所占字節(jié)數(shù)
字符串	char(n)：n字節(jié)長度 varchar(n)：2字節(jié)存儲字符串長度，如果是utf-8，則長度 3n + 2
數(shù)值類型	tinyint：1字節(jié) smallint：2字節(jié) int：4字節(jié) bigint：8字節(jié)
時(shí)間類型	date：3字節(jié) timestamp：4字節(jié) datetime：8字節(jié)

若是索引為字符串類型的時(shí)候，實(shí)際存儲的字符串非常長，已經(jīng)超出了字符串類型的存儲最大長度（768字節(jié)），mysql，就會使用類似左前綴索引來處理。

ref字段

ref表示列與索引的比較，表連接的匹配條件，表示哪些列或者常量被用于查詢索引列上的值。

rows字段

rows表示估算的要掃描的行數(shù)，一般Mysql會根據(jù)統(tǒng)計(jì)表信息和索引的選用情況，估算出查找記錄所要掃描的行數(shù)，注意這個(gè)并不是實(shí)際結(jié)果集的行數(shù)。

partitions、filtered字段

partitions表示所匹配的分區(qū)；filtered表示的是查詢表行所占表的百分比。

Extra字段

該字段顯示的是sql查詢的額外信息，主要有以下幾種情況：

Using index

表示查詢的列被索引覆蓋，這個(gè)是查詢性能比較高的體現(xiàn)，即所要查詢的信息搜在索引里面可以得到，不用回表，索引被正確的使用：explain select id from user where id =2;

假如同時(shí)出現(xiàn)了using where，表示索引用于執(zhí)行索引鍵值的查找；若是沒有出現(xiàn)using where，則表示索引用于讀取數(shù)據(jù)，而非執(zhí)行查詢的動(dòng)作。

Using where

該屬性與Using index相反，查詢的列并沒有被索引覆蓋，where條件后面使用的是非索引的前導(dǎo)列，它僅僅是使用了where條件而已：explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id;

Using temporary

「Using temporary表示使用了臨時(shí)表存儲中間的結(jié)果，一般在對結(jié)果排序的時(shí)候會使用臨時(shí)表」，例如：排序order by 和分組查詢group by。例子：explain select * from (select name from user union select name from role) a where a.name = '張三';

Using filesort

Using filesort表示文件排序，說明Mysql對數(shù)據(jù)使用了外部的索引進(jìn)行排序，并沒有使用表中的索引進(jìn)行排序：explain select * from user order by name;

Using join buffer

Using join buffer表示使用連接緩存：explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id;

它強(qiáng)調(diào)在獲取連接條件時(shí)，并沒有使用索引，而是使用連接緩沖區(qū)來存儲中間結(jié)果，若是出現(xiàn)該值，一般說明需要添加索引來進(jìn)行優(yōu)化了。

Impossible where

Impossible where會出現(xiàn)在where后的條件一直為false的情況下，這種可以忽視，比較少出現(xiàn)：explain select * from user where name = 'hah' and name = 'sfsd';

Select tables optimized away

表示select語句沒有遍歷表或者索引就返回?cái)?shù)據(jù)了，比如：explain select min(id) from user;

在Extra字段中還有其它的屬性，但是幾乎都沒見過的，不出現(xiàn)，所以哪些就講解，有興趣的可以自己去了解，這里只列出這些常見的。

說了那么多理論總是要實(shí)踐一下的，下面以user測試表為例進(jìn)行測試實(shí)踐。

實(shí)踐

（1）通過查詢 explain select * from user where name ='張三';name字段并沒有創(chuàng)建索引。

我們可以通過創(chuàng)建一個(gè)聯(lián)合索引index_name_age_time，來解決： 

alter table user add index index_name_age_time (name,age,update_time) ; 

當(dāng)再次查詢的時(shí)候，就會使用上了索引：

（2）使用聯(lián)合索引要遵循「最左前綴法則」，關(guān)于最左前綴法則原則的使用，之前我寫過一篇詳細(xì)介紹的文章，可以參考[]。

（3）在使用索引進(jìn)行查詢的時(shí)候，不要做任何的函數(shù)操作，不然會導(dǎo)致索引失效：例子：EXPLAIN SELECT * FROM user WHERE name = '張三';

但是你在使用的時(shí)候，使用了left()函數(shù)，如：EXPLAIN SELECT * FROM employees WHERE left(name,2) = '張三';，會導(dǎo)致索引失效。

（4）在數(shù)據(jù)庫的查詢中不要使用（！=或者<>）等判條件和is null,is not null、like關(guān)鍵詞中以%開頭來判斷，不然也會使索引失效：