在日常編程中,我們需要經(jīng)常與數(shù)據(jù)庫打交道,所以本文給大家?guī)硪恍╆P(guān)于Explain
的總結(jié),讓大家更好的處理SQL
。
在 select
語句之前增加 explain
關(guān)鍵字,MySQL
會在查詢上設(shè)置一個標(biāo)記,執(zhí)行查詢時,會返回執(zhí)行計(jì)劃的信息,而不是執(zhí)行這條SQL
(如果 from
中包含子查詢,仍會執(zhí)行該子查詢,將結(jié)果放入臨時表中)
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;
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;
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
兩個變種
explain extended
會在 explain
的基礎(chǔ)上額外提供一些查詢優(yōu)化的信息。緊隨其后通過 show warnings
命令可以 得到優(yōu)化后的查詢語句,從而看出優(yōu)化器優(yōu)化了什么。額外還有 filtered
列,是一個半分比的值,rows * filtered/100
可以估算出將要和 explain
中前一個表進(jìn)行連接的行數(shù)(前一個表指 explain 中的 id 值比當(dāng)前表 id 值小的表)
mysql> explain extended select * from film where id =
mysql> show warnings;
explain partitions
相比 explain
多了個 partitions
字段,如果查詢是基于分區(qū)表的話,會顯示查詢將訪問的分區(qū)。
(推薦課程:SQL教程)
id列
id
列的編號是 select
的序列號,有幾個 select
就有幾個id
,并且id
的順序是按 select
出現(xiàn)的順序增長的。 MySQL
將 select
查詢分為簡單查詢(SIMPLE)和復(fù)雜查詢(PRIMARY)。 復(fù)雜查詢分為三類:簡單子查詢、派生表(from語句中的子查詢)、union
查詢。 id
列越大執(zhí)行優(yōu)先級越高,id
相同則從上往下執(zhí)行,id
為NULL
最后執(zhí)行**
select_type列
select_type
表示對應(yīng)行是簡單還是復(fù)雜的查詢,如果是復(fù)雜的查詢,又是上述三種復(fù)雜查詢中的哪一種。
1)simple 簡單查詢:查詢不包含子查詢和union
mysql> explain select * from film where id = 2;
2)primary:復(fù)雜查詢中最外層的 select
3)subquery:包含在 select
中的子查詢(不在 from 子句中)
4)derived:包含在 from
子句中的子查詢。MySQL
會將結(jié)果存放在一個臨時表中,也稱為派生表(derived的英文含義) 用這個例子來了解 primary
、subquery
和 derived
類型
mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
5)union:在 union
中的第二個和隨后的 select
6)union result:從 union
臨時表檢索結(jié)果的 select
用這個例子來了解 union
和 union result
類型:
mysql> explain select 1 union all select 1;
table列
這一列表示 explain
的一行正在訪問哪個表。 當(dāng) from
子句中有子查詢時,table
列是 格式,表示當(dāng)前查詢依賴 id=N
的查詢,于是先執(zhí)行 id=N
的查詢。 當(dāng)有 union
時,UNION RESULT
的table
列的值為<union1,2>
,1 和 2 表示參與 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í)行階段用不著再訪問表或索引。例如:在索引列中選取最小值,可以單獨(dú)查找索引來完成,不需要在執(zhí)行時訪問表
mysql> explain select min(id) from film;
const, system
mysql
能對查詢的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)化成一個常量(可以看show warnings 的結(jié)果)。用于 primary key
或 unique key
的所有列與常數(shù)比較時,所以表最多有一個匹配行,讀取 1 次,速度比較快。system
是const
的特例,表里只有一條元組匹配時為system
mysql> explain extended select * from (select * from film where id = 1) tmp;
eq_ref
primary key
或 unique key
索引的所有部分被連接使用 ,最多只會返回一條符合條件的記錄。這可能是在 const
之外最好的聯(lián)接類型了,簡單的 select
查詢不會出現(xiàn)這種 type
。
mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
ref
相比 eq_ref
,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前綴,索引要和某個值相比較,可能會找到多個符合條件的行。
1.簡單 select
查詢,name
是普通索引(非唯一索引)
mysql> explain select * from film where name = "film1";
2.關(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
掃描全表索引,這通常比ALL
快一些。( index 是從索引中讀取的,而 all 是從硬盤中讀?。?/p>
mysql> explain select * from film;
ALL
即全表掃描,意味著mysql
需要從頭到尾去查找所需要的行。通常情況下這需要增加索引來進(jìn)行優(yōu)化了
mysql> explain select * from actor;
(推薦課程:MySQL教程)
possible_keys列
這一列顯示查詢可能使用哪些索引來查找。 explain
時可能出現(xiàn) possible_keys
有列,而 key
顯示 NULL
的情況,這種情況是因?yàn)楸碇袛?shù)據(jù)不多,mysql
認(rèn)為索引對此查詢幫助不大,選擇了全表查詢。 如果該列是NULL
,則沒有相關(guān)的索引。在這種情況下,可以通過檢查 where
子句看是否可以創(chuàng)造一個適當(dāng)?shù)乃饕齺硖岣卟樵冃阅?,然后?explain
查看效果。
key列
這一列顯示mysql
實(shí)際采用哪個索引來優(yōu)化對該表的訪問。 如果沒有使用索引,則該列是 NULL
。如果想強(qiáng)制mysql
使用或忽視possible_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í)行索引查找。
mysql> explain select * from film_actor where film_id = 2;
“key_len計(jì)算規(guī)則如下: 字符串 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é) 時間類型 date:3字節(jié) timestamp:4字節(jié) datetime:8字節(jié)”
如果字段允許為 NULL
,需要 1字節(jié)記錄是否為 NULL
索引最大長度是 768字節(jié),當(dāng)字符串過長時,mysql
會做一個類似左前綴索引的處理,將前半部分的字符提取出來做索引。
ref列
這一列顯示了在key
列記錄的索引中,表查找值所用到的列或常量,常見的有:const
(常量),字段名(例:film.id)
rows列
這一列是mysql
估計(jì)要讀取并檢測的行數(shù),注意這個不是結(jié)果集里的行數(shù)。
Extra列
這一列展示的是額外信息。常見的重要值如下:
Using index
查詢的列被索引覆蓋,并且where
篩選條件是索引的前導(dǎo)列,是性能高的表現(xiàn)。一般是使用了覆蓋索引(索引包含了所有查詢的字段)。對于innodb
來說,如果是輔助索引性能會有不少提高
mysql> explain select film_id from film_actor where film_id = 1;
Using where
查詢的列未被索引覆蓋,where
篩選條件非索引的前導(dǎo)列
mysql> explain select * from actor where name = 'a';
Using where Using index
查詢的列被索引覆蓋,并且where
篩選條件是索引列之一但是不是索引的前導(dǎo)列,意味著無法直接通過索引查找來查詢到符合條件的數(shù)據(jù)
mysql> explain select film_id from film_actor where actor_id = 1;
NULL
查詢的列未被索引覆蓋,并且where
篩選條件是索引的前導(dǎo)列,意味著用到了索引,但是部分字段未被索引覆蓋,必須通過“回表”來實(shí)現(xiàn),不是純粹地用到了索引,也不是完全沒用到索引
mysql>explain select * from film_actor where film_id = 1;
Using index condition
與Using where
類似,查詢的列不完全被索引覆蓋,where
條件中是一個前導(dǎo)列的范圍;
mysql> explain select * from film_actor where film_id > 1;
Using temporary
mysql
需要創(chuàng)建一張臨時表來處理查詢。出現(xiàn)這種情況一般是要進(jìn)行優(yōu)化的,首先是想到用索引來優(yōu)化。
1.actor.name
沒有索引,此時創(chuàng)建了張臨時表來distinct
mysql> explain select distinct name from actor;
2.film.name
建立了idx_name
索引,此時查詢時extra
是using index
,沒有用臨時表
mysql> explain select distinct name from film;
(推薦微課:MySQL微課)
Using filesort
mysql
會對結(jié)果使用一個外部索引排序,而不是按索引次序從表里讀取行。此時mysql
會根據(jù)聯(lián)接類型瀏覽所有符合條件的記錄,并保存排序關(guān)鍵字和行指針,然后排序關(guān)鍵字并按順序檢索行信息。這種情況下一般也是要考慮使用索引來優(yōu)化的。
1.actor.name
未創(chuàng)建索引,會瀏覽actor
整個表,保存排序關(guān)鍵字name
和對應(yīng)的id
,然后排序name
并檢索行記錄
mysql> explain select * from actor order by name;
2.film.name
建立了idx_name
索引,此時查詢時extra
是using index
mysql> explain select * from film order by name;
文章來源:juejin.im/post/6863832433062739981
以上就是W3Cschool編程獅
關(guān)于完整的Explain寶典,SQL優(yōu)化再也不用擔(dān)心了的相關(guān)介紹了,希望對大家有所幫助。