explain的作用
我們使用explain命令來(lái)查看mysql語(yǔ)句的執(zhí)行計(jì)劃(execution plan),解釋mysql是如何執(zhí)行一條sql語(yǔ)句的亥宿;解釋的內(nèi)容主要包括表的連接方式和順序传趾,以及索引的使用情況秉氧。使用explain驻襟,可以分析出需要在哪里加上索引,以及調(diào)整表的連接腺占,以達(dá)到優(yōu)化查詢(xún)的目的赃承;explain命令之后不僅可以跟select語(yǔ)句,也可以跟delete,insert,update,replace語(yǔ)句赘被。
用法
只需要在sql語(yǔ)句前加上explain就可以了是整,比如:
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 100310 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
如何分析
我們看到explain命令的結(jié)果有很多列,通常情況下民假,我們最關(guān)心的指標(biāo)是select_type
和type
浮入。
概念
Column | JSON Name | Meaning |
---|---|---|
id |
select_id |
The SELECT identifier(選擇標(biāo)識(shí)符) |
select_type |
None | The SELECT type(查詢(xún)類(lèi)型) |
table |
table_name |
The table for the output row(結(jié)果集使用的表) |
partitions |
partitions |
The matching partitions(匹配的分區(qū)) |
type |
access_type |
The join type(連接類(lèi)型) |
possible_keys |
possible_keys |
The possible indexes to choose(可能使用到的索引) |
key |
key |
The index actually chosen(實(shí)際使用的索引) |
key_len |
key_length |
The length of the chosen key (使用索引的長(zhǎng)度) |
ref |
ref |
The columns compared to the index(和索引比較的列) |
rows |
rows |
Estimate of rows to be examined(掃描的行數(shù)) |
filtered |
filtered |
Percentage of rows filtered by table condition(表?xiàng)l件過(guò)濾行數(shù)的百分比) |
Extra |
None | Additional information(附加的信息) |
進(jìn)一步解釋
id
id的值越大,執(zhí)行的優(yōu)先級(jí)越高羊异,id相同事秀,則從上往下順序執(zhí)行。所以以下例子:
id | placeholder |
---|---|
1 | A |
1 | B |
2 | C |
執(zhí)行的順序是:C野舶,A易迹,B。
select_type
select_type Value |
JSON Name | Meaning(含義) |
---|---|---|
SIMPLE |
None | Simple SELECT (not using UNION or subqueries)(查詢(xún)查詢(xún)平道,不使用union或子查詢(xún)) |
PRIMARY |
None | Outermost SELECT (最外層的select查詢(xún)) |
UNION |
None | Second or later SELECT statement in a UNION (在union中排在第二位甚至更靠后的select語(yǔ)句) |
DEPENDENT UNION |
dependent (true ) |
Second or later SELECT statement in a UNION , dependent on outer query (在union中排在第二位甚至更靠后的select語(yǔ)句睹欲,取決于外面的查詢(xún)) |
UNION RESULT |
union_result |
Result of a UNION .(union結(jié)果集) |
SUBQUERY |
None | First SELECT in subquery(子查詢(xún)中的第一個(gè)select) |
DEPENDENT SUBQUERY |
dependent (true ) |
First SELECT in subquery, dependent on outer query(子查詢(xún)中的第一個(gè)select,取決于外面的查詢(xún)) |
DERIVED |
None | Derived table(派生表) |
DEPENDENT DERIVED |
dependent (true ) |
Derived table dependent on another table (派生表巢掺,依賴(lài)其他表) |
MATERIALIZED |
materialized_from_subquery |
Materialized subquery (實(shí)現(xiàn)子查詢(xún)) |
UNCACHEABLE SUBQUERY |
cacheable (false ) |
A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query(結(jié)果不能被緩存并且外部查詢(xún)的每一行都必須被重新評(píng)估的子查詢(xún)) |
UNCACHEABLE UNION |
cacheable (false ) |
The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY )(在union中排位第二甚至更靠后屬于不能緩存的子查詢(xún)) |
table
顯示這行的數(shù)據(jù)是關(guān)于哪張表的句伶,也可能是表的別名劲蜻。
type
可能的取值
null > system > const > eq_ref > ref > range > index > all,從左到右,性能遞減考余,null最好先嬉,all最差,一般的楚堤,最好能優(yōu)化查詢(xún)到const到range之間疫蔓。
含義
ALL:Full Table Scan,遍歷全表身冬。
index: Full Index Scan衅胀,index與ALL區(qū)別為index類(lèi)型只遍歷索引樹(shù),不遍歷數(shù)據(jù)行酥筝,所以比all的速度要快滚躯。
range: 只檢索給定范圍的行,這個(gè)范圍必須應(yīng)用在一個(gè)有索引的列上嘿歌。
ref: 使用了非唯一索引作為where或join條件掸掏,是一個(gè)確定的值。
eq_ref: 同ref宙帝,但索引是唯一索引丧凤。
const、system: 將確定的值應(yīng)用在索引(unique and not unique)上步脓,type
將會(huì)是const
,當(dāng)結(jié)果只有一行時(shí)愿待,type
是system
。
NULL: MySQL在優(yōu)化過(guò)程中分解語(yǔ)句靴患,執(zhí)行時(shí)甚至不用訪問(wèn)表或索引仍侥,例如從一個(gè)索引列里選取最小值可以通過(guò)單獨(dú)索引查找完成。
例子
每一個(gè)type
都給出了一個(gè)例子:
drop table t1, t2, t3;
create table t1 (
id int(11) auto_increment,
name varchar(50),
address varchar(100),
primary key (id),
key index_name(name)
);
create table t2 (
id int(11) auto_increment,
name varchar(50),
address varchar(100),
primary key (id),
key index_name(name)
);
create table t3 (
id int(11) auto_increment,
name varchar(50),
address varchar(100),
primary key (id),
key index_name(name)
);
insert into t1 (name, address)
values ('tom', 'downtown'),
('jack', 'country yard');
insert into t2 (name, address)
values ('tom', 'downtown'),
('jack', 'country yard');
insert into t3 (name, address)
values ('tom', 'downtown'),
('jack', 'country yard');
explain select *
from t1
where id = 1; -- const
explain select *
from t1
limit 10; -- all
explain select *
from t1
where name = 'tom'; -- ref
explain select t1.id
from t1,
t2
where t1.id = t2.id; -- index, eq_ref
explain select t1.*
from t1,
t2
where t1.id = t2.id; -- index, all
explain select *
from t1
where id between 1 and 10; -- range
explain select t1.name
from t1,
t2
where t1.name = t2.name; -- index, ref
explain select *
from t2,
(select id from t1 where t1.id = 1) t
where t2.id = t.id;
explain select *
from (select * from t1 where t1.address = 'downtown') t;
explain select *
from t1
where id = 1
union
select *
from t2
where id = 2; -- const,const,all
explain select *
from (select * from t1 limit 1) a1; -- system, all
Extra
Using where:不用讀取表中所有信息蚁廓,僅通過(guò)索引就可以獲取所需數(shù)據(jù)访圃,這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候,表示mysql服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾相嵌。
Using temporary:表示MySQL需要使用臨時(shí)表來(lái)存儲(chǔ)結(jié)果集腿时,常見(jiàn)于排序和分組查詢(xún),常見(jiàn) group by ; order by。
Using filesort:當(dāng)Query中包含 order by 操作,而且無(wú)法利用索引完成的排序操作稱(chēng)為“文件排序”庶柿。
Using join buffer:該值強(qiáng)調(diào)了在獲取連接條件時(shí)沒(méi)有使用索引,并且需要連接緩沖區(qū)來(lái)存儲(chǔ)中間結(jié)果徽鼎。如果出現(xiàn)了這個(gè)值,那應(yīng)該注意,根據(jù)查詢(xún)的具體情況可能需要添加索引來(lái)改進(jìn)能否淤。
Impossible where:這個(gè)值強(qiáng)調(diào)了where語(yǔ)句會(huì)導(dǎo)致沒(méi)有符合條件的行(通過(guò)收集統(tǒng)計(jì)信息不可能存在結(jié)果)悄但。
Select tables optimized away:這個(gè)值意味著僅通過(guò)使用索引,優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行
No tables used:Query語(yǔ)句中使用from dual 或不含任何from子句石抡。
應(yīng)用
在很多博客上檐嚣,都有關(guān)于between,>,<,in,not in,like是否會(huì)使用索引,如果使用啰扛,那么type
是什么的文章嚎京,其實(shí)這個(gè)問(wèn)題的本質(zhì)是對(duì)explain
命令的使用,我們只需要寫(xiě)幾個(gè)包含以上where
條件的語(yǔ)句隐解,就可以找到問(wèn)題的答案了鞍帝。我們新建一個(gè)叫user
的表,并且插入10萬(wàn)條隨機(jī)的英文姓名煞茫。測(cè)試的結(jié)果如下帕涌。
create table user (
id int(11) auto_increment,
name varchar(100),
age int(11),
primary key (id),
key index_age(age),
key index_name(name)
);
explain select * from user where name in ('tom'); -- use
explain select * from user where name = 'tom'; -- use
explain select * from user where name between 'tom' and 'jerry'; -- not use
explain select * from user where name <> 'tom'; -- not use
explain select * from user where name = 'tom' or name = 'jerry'; -- use
explain select * from user where name like 'om%'; -- use
explain select * from user where name like 'tom'; -- use, 這里的like相當(dāng)于等號(hào)
explain select * from user where name like '%to'; -- not use
explain select * from user where age between 0 and 1; -- use
explain select * from user where age not between 0 and 1; -- not use
explain select * from user where age > 50; -- not use
explain select * from user where age < 50; -- not use
explain select * from user where age != 3; -- not use
explain select * from user where age in (1, 99); -- use
explain select * from user where age = 1 or age = 3; -- use
explain select * from user where age like '1%'; -- not use
explain select * from user where age like '%1'; -- not use
從以上的測(cè)試結(jié)果,我們可以得出結(jié)論:
between and, >,<,in,not in,or,like
都是會(huì)使用索引的溜嗜,但是between and, >,<
必須用在數(shù)值類(lèi)型的列上宵膨;in,not in,or
可以用到數(shù)值和字符串的列上炸宵;而like
只能用到字符串類(lèi)型的列上,而且必須是左邊不能以通配符開(kāi)頭谷扣。