這篇文章主要講解了“explain都不會(huì)用，你還好意思說(shuō)精通MySQL查詢優(yōu)化？”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“explain都不會(huì)用，你還好意思說(shuō)精通MySQL查詢優(yōu)化？”吧！

創(chuàng)新互聯(lián)建站堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站建設(shè)、網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的科爾沁右翼中網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

Explain簡(jiǎn)介

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

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

// 用戶表  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è)且都有對(duì)應(yīng)的值，這就是explain的執(zhí)行計(jì)劃，能看懂這個(gè)執(zhí)行計(jì)劃，你離精通sql優(yōu)化就不遠(yuǎn)了，下面就來(lái)詳細(xì)的介紹這12個(gè)字段分別表示什么意思。

id字段

id表示執(zhí)行select查詢語(yǔ)句的序號(hào)，它是sql執(zhí)行的順序的標(biāo)識(shí)，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í)行，第一條記錄對(duì)應(yīng)的是user表，然后第二條記錄對(duì)應(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 |  +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+

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

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

select_type字段

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

simple

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

PRIMARY

復(fù)雜查詢中「最外層的select語(yǔ)句的查詢類(lèi)型就是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 =？會(huì)被標(biāo)記為PRIMARY類(lèi)型。

SUBQUERY

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

DERIVED

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

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

UNION

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

UNION RESULT

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

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

其它

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

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2.  DEPENDENT UNION：也表示UNION查詢語(yǔ)句中第二個(gè)或者后面的語(yǔ)句，但是取決于外面的查詢。

3.  DEPENDENT SUBQUERY：子查詢中的第一個(gè)select語(yǔ)句，也是依賴于外部的查詢。

4.  UNCACHEABLE SUBQUERY：子查詢的結(jié)果不能被緩存，必須重新評(píng)估外連接的第一行。

table字段

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

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

type字段

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

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

system

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

const

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

eq_ref

eq_ref表示使用唯一索引或者主鍵索引掃描作為表鏈接匹配條件，對(duì)于每一個(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é)果可能會(huì)找到多條符合條件的行數(shù)據(jù)，本質(zhì)上是一種索引訪問(wèn)，返回匹配的行。例如：explain select * from user where name = '張三';

range

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

index

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

ALL

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

possible_keys字段

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

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

key字段

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

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

key_len字段

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

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

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

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

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

ref字段

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

rows字段

rows表示估算的要掃描的行數(shù)，一般Mysql會(huì)根據(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í)行索引鍵值的查找；若是沒(méi)有出現(xiàn)using where，則表示索引用于讀取數(shù)據(jù)，而非執(zhí)行查詢的動(dòng)作。

Using where

該屬性與Using index相反，查詢的列并沒(méi)有被索引覆蓋，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í)表存儲(chǔ)中間的結(jié)果，一般在對(duì)結(jié)果排序的時(shí)候會(huì)使用臨時(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表示文件排序，說(shuō)明Mysql對(duì)數(shù)據(jù)使用了外部的索引進(jìn)行排序，并沒(méi)有使用表中的索引進(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í)，并沒(méi)有使用索引，而是使用連接緩沖區(qū)來(lái)存儲(chǔ)中間結(jié)果，若是出現(xiàn)該值，一般說(shuō)明需要添加索引來(lái)進(jìn)行優(yōu)化了。

Impossible where

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

Select tables optimized away

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

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

說(shuō)了那么多理論總是要實(shí)踐一下的，下面以u(píng)ser測(cè)試表為例進(jìn)行測(cè)試實(shí)踐。

實(shí)踐

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

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

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

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

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

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

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

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

感謝各位的閱讀，以上就是“explain都不會(huì)用，你還好意思說(shuō)精通MySQL查詢優(yōu)化？”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)explain都不會(huì)用，你還好意思說(shuō)精通MySQL查詢優(yōu)化？這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

當(dāng)前題目：explain都不會(huì)用，你還好意思說(shuō)精通MySQL查詢優(yōu)化？
本文地址：http://jinyejixie.com/article4/pgggoe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供用戶體驗(yàn)、品牌網(wǎng)站設(shè)計(jì)、小程序開(kāi)發(fā)、、微信小程序、移動(dòng)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)