MySQL中explain命令詳解

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_typetype浮入。

概念

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í)愿待,typesystem

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)頭谷扣。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末土全,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子会涎,更是在濱河造成了極大的恐慌裹匙,老刑警劉巖,帶你破解...
    沈念sama閱讀 210,978評(píng)論 6 490
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件末秃,死亡現(xiàn)場(chǎng)離奇詭異概页,居然都是意外死亡,警方通過(guò)查閱死者的電腦和手機(jī)练慕,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 89,954評(píng)論 2 384
  • 文/潘曉璐 我一進(jìn)店門(mén)惰匙,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái),“玉大人铃将,你說(shuō)我怎么就攤上這事项鬼。” “怎么了劲阎?”我有些...
    開(kāi)封第一講書(shū)人閱讀 156,623評(píng)論 0 345
  • 文/不壞的土叔 我叫張陵绘盟,是天一觀的道長(zhǎng)。 經(jīng)常有香客問(wèn)我,道長(zhǎng)龄毡,這世上最難降的妖魔是什么吠卷? 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 56,324評(píng)論 1 282
  • 正文 為了忘掉前任,我火速辦了婚禮沦零,結(jié)果婚禮上撤嫩,老公的妹妹穿的比我還像新娘。我一直安慰自己蠢终,他們只是感情好序攘,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,390評(píng)論 5 384
  • 文/花漫 我一把揭開(kāi)白布。 她就那樣靜靜地躺著寻拂,像睡著了一般程奠。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上祭钉,一...
    開(kāi)封第一講書(shū)人閱讀 49,741評(píng)論 1 289
  • 那天瞄沙,我揣著相機(jī)與錄音,去河邊找鬼慌核。 笑死距境,一個(gè)胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的垮卓。 我是一名探鬼主播垫桂,決...
    沈念sama閱讀 38,892評(píng)論 3 405
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼粟按!你這毒婦竟也來(lái)了诬滩?” 一聲冷哼從身側(cè)響起,我...
    開(kāi)封第一講書(shū)人閱讀 37,655評(píng)論 0 266
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤灭将,失蹤者是張志新(化名)和其女友劉穎疼鸟,沒(méi)想到半個(gè)月后,有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體庙曙,經(jīng)...
    沈念sama閱讀 44,104評(píng)論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡空镜,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,451評(píng)論 2 325
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了捌朴。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片吴攒。...
    茶點(diǎn)故事閱讀 38,569評(píng)論 1 340
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖男旗,靈堂內(nèi)的尸體忽然破棺而出舶斧,到底是詐尸還是另有隱情,我是刑警寧澤察皇,帶...
    沈念sama閱讀 34,254評(píng)論 4 328
  • 正文 年R本政府宣布茴厉,位于F島的核電站泽台,受9級(jí)特大地震影響,放射性物質(zhì)發(fā)生泄漏矾缓。R本人自食惡果不足惜怀酷,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,834評(píng)論 3 312
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望嗜闻。 院中可真熱鬧蜕依,春花似錦、人聲如沸琉雳。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 30,725評(píng)論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)翠肘。三九已至檐束,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間束倍,已是汗流浹背被丧。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 31,950評(píng)論 1 264
  • 我被黑心中介騙來(lái)泰國(guó)打工, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留绪妹,地道東北人甥桂。 一個(gè)月前我還...
    沈念sama閱讀 46,260評(píng)論 2 360
  • 正文 我出身青樓,卻偏偏與公主長(zhǎng)得像邮旷,于是被迫代替她去往敵國(guó)和親黄选。 傳聞我的和親對(duì)象是個(gè)殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,446評(píng)論 2 348

推薦閱讀更多精彩內(nèi)容