共計 8543 個字符，預計需要花費 22 分鐘才能閱讀完成。

這篇文章主要講解了“explain 都不會用，你還好意思說精通 MySQL 查詢優化？”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“explain 都不會用，你還好意思說精通 MySQL 查詢優化？”吧！

Explain 簡介

Explain 關鍵字是 Mysql 中 sql 優化的常用「關鍵字」，通常都會使用 Explain 來「查看 sql 的執行計劃，而不用執行 sql」，從而快速的找出 sql 的問題所在。

在講解 Explain 之前首先創建需要的「用戶表 user、角色表 role、以及用戶角色關系表 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, 產品經理),(2, 技術經理),(3, 項目總監  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);

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

可以看到這里有 12 個字段那個且都有對應的值，這就是 explain 的執行計劃，能看懂這個執行計劃，你離精通 sql 優化就不遠了，下面就來詳細的介紹這 12 個字段分別表示什么意思。

id 字段

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

什么意思呢？例如執行這條 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 的執行從上往下執行，第一條記錄對應的是 user 表，然后第二條記錄對應的是 role_user 表，這種是 id 相同的情況。

若是 id 不同，例如執行下面的 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 越大的就越先執行，可以看到 id= 2 的執行的是 user 表，也就是子查詢部分，最后執行最外層的部分。

「結論：」這個就是 id 標識 sql 的執行順序，一般在復雜查詢中會有多條記錄，簡單查詢只有一條記錄，復雜查詢中 id 相同的為一組，執行的順序是從上往下，而 id 越大的越先執行；Mysql 8 中會存在對子查詢進行優化，所以有時候即使是復雜查詢，也只有一條記錄。

select_type 字段

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

simple

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

PRIMARY

復雜查詢中「最外層的 select 語句的查詢類型就是 PRIMARY」，例如執行下面的 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 =？會被標記為 PRIMARY 類型。

SUBQUERY

在「select 或者 where 中包含的子查詢」會被表示為 SUBQUERY 類型，例如上一句執行的 sql 中就有兩次的子查詢為 SUBQUERY。

DERIVED

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

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

UNION

在出現「UNION 查詢語句中，第二個 select 的查詢語句就會被表示為 UNION」：

UNION RESULT

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

第四行記錄中從 table 字段中可以看出，第四行的記錄來源于第二行和第三行 union2,3，因此一個 UNION 查詢語句的結果就會被標記為 UNION RESULT

其它

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

鴻蒙官方戰略合作共建——HarmonyOS 技術社區

 DEPENDENT UNION：也表示 UNION 查詢語句中第二個或者后面的語句，但是取決于外面的查詢。

 DEPENDENT SUBQUERY：子查詢中的第一個 select 語句，也是依賴于外部的查詢。

 UNCACHEABLE SUBQUERY：子查詢的結果不能被緩存，必須重新評估外連接的第一行。

table 字段

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

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

type 字段

「type 字段表示的 sql 關聯的類型或者說是訪問的類型」。從這個字段中我們可以確定這條 sql 查找數據庫表的時候，查找記錄的大概范圍是怎么樣的，直接就能體現 sql 的效率問題。

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

system

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

const

const 表示通過索引一次就查找到了數據，一般 const 出現在「唯一索引或者主鍵索引中使用等值查詢」，因為表中只有一條數據匹配，所以查找的速度很快。例子：explain select * from user where id =2;

eq_ref

eq_ref 表示使用唯一索引或者主鍵索引掃描作為表鏈接匹配條件，對于每一個索引鍵，表中只有一條記錄與之匹配。例如：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 的區別就是 eq_ref 使用的是唯一索引或者主鍵索引。

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

range

「range 使用索引來檢索給定范圍的行數據，一般是在 where 后面使用 between、、in 等查詢語句就會出現 range」：explain select * from user where id

index

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

ALL

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

possible_keys 字段

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

當沒有使用索引為 NULL 時，說明需要增加索引來優化查詢了，若是表的數據比較少的話，數據庫覺得全表掃描更快，也可能為 NULL。

key 字段

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

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

key_len 字段

表示 sql 查詢語句中索引使用到的字節數，這個字節數并不是實際的長度，而是通過計算查詢中使用到的索引中的長度得出來的，顯示的是索引字段最大的可能長度。

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

key_len 對于不同的類型有自己的計算規則，具體的計算規則如下所示：

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

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

ref 字段

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

rows 字段

rows 表示估算的要掃描的行數，一般 Mysql 會根據統計表信息和索引的選用情況，估算出查找記錄所要掃描的行數，注意這個并不是實際結果集的行數。

partitions、filtered 字段

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

Extra 字段

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

Using index

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

假如同時出現了 using where，表示索引用于執行索引鍵值的查找；若是沒有出現 using where，則表示索引用于讀取數據，而非執行查詢的動作。

Using where

該屬性與 Using index 相反，查詢的列并沒有被索引覆蓋，where 條件后面使用的是非索引的前導列，它僅僅是使用了 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 表示使用了臨時表存儲中間的結果，一般在對結果排序的時候會使用臨時表」，例如：排序 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 對數據使用了外部的索引進行排序，并沒有使用表中的索引進行排序：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;

它強調在獲取連接條件時，并沒有使用索引，而是使用連接緩沖區來存儲中間結果，若是出現該值，一般說明需要添加索引來進行優化了。

Impossible where

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

Select tables optimized away

表示 select 語句沒有遍歷表或者索引就返回數據了，比如：explain select min(id) from user;

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

說了那么多理論總是要實踐一下的，下面以 user 測試表為例進行測試實踐。

實踐

（1）通過查詢 explain select * from user where name = 張三 name 字段并沒有創建索引。

我們可以通過創建一個聯合索引 index_name_age_time，來解決：

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

當再次查詢的時候，就會使用上了索引：

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

（3）在使用索引進行查詢的時候，不要做任何的函數操作，不然會導致索引失效：例子：EXPLAIN SELECT * FROM user WHERE name = 張三

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

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

感謝各位的閱讀，以上就是“explain 都不會用，你還好意思說精通 MySQL 查詢優化？”的內容了，經過本文的學習后，相信大家對 explain 都不會用，你還好意思說精通 MySQL 查詢優化？這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！