MySQL之Explain怎么使用

本篇內(nèi)容介紹了“MySQL之Explain怎么使用”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

Explain工具介紹

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

示例代碼

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;

在查詢中的每個(gè)表都會(huì)輸出一行，如果有兩個(gè)表通過(guò)join連接查詢，那么會(huì)輸出兩行

explain兩個(gè)變種 explain extended

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

explain partitions

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

explain中的列

接下來(lái)我們將展示 explain 中每個(gè)列的信息。

id列

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

select_type列

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

1.simple：簡(jiǎn)單查詢，不包含子查詢和union

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

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

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

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

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

id = 1) der;

5.union：在union中的第二個(gè)和隨后的select

table列

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

type列

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

一般來(lái)說(shuō)得保證查詢達(dá)到range級(jí)別，最大達(dá)到ref

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

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

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

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

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

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

1. 簡(jiǎn)單 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,>,<,>=等操作中，使用一個(gè)索引來(lái)檢索給定范圍的行。

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

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

explain select * from film;

為什么這里是走的idx_name這個(gè)索引？沒(méi)有走主鍵索引？
因?yàn)镸ySQL底層有一套判斷使用哪個(gè)索引的機(jī)制，這里是因?yàn)檫@張表一共就2個(gè)字段，而這inx_name索引樹就已經(jīng)包含了id和name，這時(shí)候選擇用主鍵和二級(jí)索引，它會(huì)優(yōu)先選擇二級(jí)索引因?yàn)樗乃饕龢浯笮?huì)小很多。
如果有很多其他字段，可能會(huì)選擇主鍵索引，因?yàn)槟氵x擇了二級(jí)索引還得做回表操作

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

 explain select * from actor;

possible_keys列

這一列顯示查詢可能使用哪些索引來(lái)查找。
explain時(shí)可能出現(xiàn)possible_keys有列，而key顯示NULL的情況，這種情況是因?yàn)楸碇袛?shù)據(jù)不多，mysql認(rèn)為索引對(duì)此查詢幫助不大，所以選擇全表掃描。
如果該列是NULL，則沒(méi)有相關(guān)的索引。在這種情況下可以通過(guò)檢查where子句看是否可以創(chuàng)建一個(gè)適當(dāng)?shù)乃饕齺?lái)提高查詢性能，然后用explain查看效果。

key列

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

key_len列

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

explain select * from film_actor where film_id = 2;

ref列

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

rows列

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

Extra列

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

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

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

explain select film_id from film_actor where film_id = 1;

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

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

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

explain select * from film_actor where film_id > 1;

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

4.1 actor.name沒(méi)有索引，此時(shí)創(chuàng)建一張臨時(shí)表來(lái)distinct

explain select distinct name from actor;

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

explain select distinct name from film;

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

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

explain select * from actor order by name;

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

explain select * from film order by name;

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

explain select min(id) from film;

“MySQL之Explain怎么使用”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！