Explain工具介紹

使用Explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL語句，分析你的查詢語句或是結(jié)構(gòu)的性能瓶頸。在select語句之前增加explain關(guān)鍵字，Mysql會在查詢上設(shè)置一個標(biāo)記，執(zhí)行查詢會返回執(zhí)行計劃的信息，而不是執(zhí)行這條SQL
注意：如果from中包含子查詢，仍會執(zhí)行該子查詢，將結(jié)構(gòu)放入臨時表中

示例代碼

DROP TABLE IF EXISTS `actor`; 
CREATE TABLE `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

explain select * from actor;

在查詢中的每個表都會輸出一行，如果有兩個表通過join連接查詢，那么會輸出兩行

explain兩個變種 explain extended

會在explain的基礎(chǔ)上額外提供一些查詢優(yōu)化的信息（5.7自動加上了，不需要這個命令了）緊隨其后通過shouw warnings命令可以得到優(yōu)化后的查詢語句，從而看出優(yōu)化器優(yōu)化了什么。額外還有filtered列，是一個半分比的值。rows * filtered/100 可以估算出將要和explain中前一個表進(jìn)行連接的行數(shù)。

explain partitions

相比explain多了個partitions字段(5.7以后，explain默認(rèn)有了)，如果查詢時基于分區(qū)表的話，會顯示查詢將訪問的分區(qū)

explain中的列

接下來我們將展示 explain 中每個列的信息。

id列

id列的編號是select的序列號，有幾個select就有幾個id，并且id的順序是按select出現(xiàn)的順序增長的。
id列值越大優(yōu)先級越高，id相同則是從上往下執(zhí)行，id為NULL最后執(zhí)行

select_type列

select_type表示對應(yīng)行是簡單還是復(fù)雜查詢

1.simple：簡單查詢，不包含子查詢和union

2.primary：復(fù)雜查詢中最外層的select

3.subquery：包含在select中的子查詢（不在from子句中）

4.derived：包含在from自居中的子查詢，MySQL會將結(jié)果存放在一個臨時表中，也稱為派生表

mysql> set session optimizer_switch=‘derived_merge=off’; #關(guān)閉mysql5.7新特性對衍生表的合并優(yōu)化

mysql> explain select (select 1 from actor where id = 1) from (select * from film where

id = 1) der;

5.union：在union中的第二個和隨后的select

table列

這一列表示explain的一行正在訪問哪個表
當(dāng)from子句中有子查詢時，table列時格式，表示當(dāng)前查詢依賴id=N的查詢，于是先執(zhí)行id=N的查詢
當(dāng)有union時，union result的table列的值為<union,1,2> ，1和1表示參與union的select行id。

type列

這一列表示關(guān)聯(lián)類型或訪問類型，即MySQL決定如何查找表中的行，查找數(shù)據(jù)行記錄的大概范圍。
依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index >ALL

一般來說得保證查詢達(dá)到range級別，最大達(dá)到ref

• NULL：mysql能夠在優(yōu)化階段分解查詢語句，在執(zhí)行階段用不著訪問表或索引。例如：在索引列中取最小值，可以單獨查找索引來完成，不需要在執(zhí)行時訪問表

• const,system：mysql能對查詢的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)換成一個常量(可以看show warnings的結(jié)果)。用于primary key 或 unique key的所有列與常數(shù)比較時，所以表最多有一個匹配行，讀取一次，速度比較快。system時const的特例，表里只有一條元組匹配時為system。（意思就是system只有在數(shù)據(jù)找到一條的情況下出現(xiàn)）

 explain extended select * from (select * from film where id =1) tmp;

• eq_ref：primary key或 unique key 索引所在部分被連接使用，最多只會返回一條符合條件的記錄。這可能時在const之外最好的聯(lián)接類型了，簡單的select查詢不會出現(xiàn)這種type

explain select * from film_actor left join film on film_actor.film_id = film.id;

• ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值相比較，可能會找到多個符合條件的行(hang)。

1. 簡單 select 查詢，name是普通索引（非唯一索引）
mysql> explain select * from film where name = 'film1';

關(guān)聯(lián)表查詢，idx_film_actor_id是film_id和actor_id的聯(lián)合索引，這里使用到了film_actor的左邊前綴film_id部分。
mysql> explain select film_id from film left join film_actor on film.id = film_actor.film_id;

• range：范圍掃描通常出現(xiàn)在in(),between,>,<,>=等操作中，使用一個索引來檢索給定范圍的行。

mysql> explain select * from actor where id > 1;

• index：掃描全索引就能拿到結(jié)果，一般是掃描某個二級索引，這種掃描不會從索引樹根節(jié)點開始快速查找，而是直接對二級索引的葉子節(jié)點遍歷和掃描，速度還是比較慢的，這種查詢一般為使用覆蓋索引，二級索引一般比較小，所以這種通常比ALL快一些。

explain select * from film;

為什么這里是走的idx_name這個索引？沒有走主鍵索引？
因為MySQL底層有一套判斷使用哪個索引的機(jī)制，這里是因為這張表一共就2個字段，而這inx_name索引樹就已經(jīng)包含了id和name，這時候選擇用主鍵和二級索引，它會優(yōu)先選擇二級索引因為它的索引樹大小會小很多。
如果有很多其他字段，可能會選擇主鍵索引，因為你選擇了二級索引還得做回表操作

• ALL：即全表掃描，掃描你的聚簇索引的所有葉子節(jié)點。通常情況下這需要增加索引來進(jìn)行優(yōu)化了

 explain select * from actor;

possible_keys列

這一列顯示查詢可能使用哪些索引來查找。
explain時可能出現(xiàn)possible_keys有列，而key顯示NULL的情況，這種情況是因為表中數(shù)據(jù)不多，mysql認(rèn)為索引對此查詢幫助不大，所以選擇全表掃描。
如果該列是NULL，則沒有相關(guān)的索引。在這種情況下可以通過檢查where子句看是否可以創(chuàng)建一個適當(dāng)?shù)乃饕齺硖岣卟樵冃阅?，然后用explain查看效果。

key列

這一列顯示MySQL使用采用那個索引來優(yōu)化對該表的訪問。
如果沒有使用索引，則該列是NULL。如果想強(qiáng)制MySQL使用或忽視possibe_keys列中的索引，在查詢中使用force index、ignore index。

key_len列

這一列顯示了MySQL在索引里使用的字節(jié)數(shù)，通過這個值可以算出具體使用了索引中的哪些列。
舉例來說，film_actor的聯(lián)合索引 idx_film_actor_id 由 film_id 和 actor_id 兩個int列組成，并且每個int是4字節(jié)。通過結(jié)果中的key_len=4可推斷出查詢使用了第一個列：film_id列來執(zhí)行索引查找。

explain select * from film_actor where film_id = 2;

ref列

這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見的有：const（常量），字段名(例：film.id)

rows列

這一列是MySQL估計要讀取并檢測的行數(shù)，注意這個不是結(jié)果集里的行數(shù)

Extra列

這一列展示的是額外信息，常見的重要值如下：

• 1.Using index：使用覆蓋索引 （不會回表）

覆蓋索引定義：MySQL執(zhí)行計劃explain結(jié)果里的key有使用索引，如果select后面查詢的字段都可以從這個索引的樹中獲取，這種情況一般可以說用到了覆蓋索引，extra里一般都有using index；覆蓋索引一般針對的是輔助索引，整個查詢結(jié)構(gòu)只通過輔助索引就能拿到結(jié)構(gòu)，不需要通過輔助所以樹找到主鍵，再通過主鍵去主鍵索引樹里獲取其他字段值

explain select film_id from film_actor where film_id = 1;

• 2.Using where：使用where語句來處理結(jié)果，并且查詢的列未被索引覆蓋

explain select * from actor where name = 'a';

• 3.Using index condition：查詢的列不完全被索引覆蓋，where條件中是一個前導(dǎo)列的范圍

explain select * from film_actor where film_id > 1;

1.Using temporary：MySQL需要創(chuàng)建一張臨時表來處理查詢。出現(xiàn)這種情況一般是要進(jìn)行優(yōu)化的，首先是想導(dǎo)用索引來優(yōu)化。

4.1 actor.name沒有索引，此時創(chuàng)建一張臨時表來distinct

explain select distinct name from actor;

4.2 film.name創(chuàng)建了idx_name索引，此時查詢時extra時using index ，沒有用臨時表

explain select distinct name from film;

5.Using filesort：將用外部排序而不是索引排序，數(shù)據(jù)較小時從內(nèi)存排序，否則需要再磁盤完成排序。這種情況下一般也是要考慮使用索引來優(yōu)化的

5.1 actor.name未創(chuàng)建索引，會瀏覽actor整個表，保存排序關(guān)鍵字name和對應(yīng)的id，然后排序name并檢索行記錄

explain select * from actor order by name;

5.2 film.name建立了idx_name索引,此時查詢時extra是using index

explain select * from film order by name;

6.Select tables optimized away：使用某些聚合函數(shù)（比如 max、min）來訪問存在索引的某個字段

explain select min(id) from film;

到此這篇關(guān)于MySQL之Explain詳解的文章就介紹到這了,更多相關(guān)MySQL Explain詳解內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論