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

概念

進(jìn)一步解釋

id

id的值越大，執(zhí)行的優(yōu)先級(jí)越高羊异，id相同事秀，則從上往下順序執(zhí)行。所以以下例子：

執(zhí)行的順序是：C野舶，A易迹，B。

select_type

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