explain的作用

我們使用explain命令來查看mysql語句的執(zhí)行計劃（execution plan）,解釋mysql是如何執(zhí)行一條sql語句的；解釋的內容主要包括表的連接方式和順序，以及索引的使用情況。使用explain，可以分析出需要在哪里加上索引，以及調整表的連接，以達到優(yōu)化查詢的目的；explain命令之后不僅可以跟select語句，也可以跟delete,insert,update,replace語句。

用法

只需要在sql語句前加上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命令的結果有很多列，通常情況下，我們最關心的指標是select_type和type。

概念

進一步解釋

id

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

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

select_type

table

顯示這行的數據是關于哪張表的，也可能是表的別名。

type

可能的取值

null > system > const > eq_ref > ref > range > index > all,從左到右，性能遞減，null最好，all最差，一般的，最好能優(yōu)化查詢到const到range之間。

含義

ALL：Full Table Scan，遍歷全表。

index: Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹，不遍歷數據行，所以比all的速度要快。

range: 只檢索給定范圍的行，這個范圍必須應用在一個有索引的列上。

ref: 使用了非唯一索引作為where或join條件，是一個確定的值。

eq_ref: 同ref，但索引是唯一索引。

const、system: 將確定的值應用在索引（unique and not unique）上，type將會是const,當結果只有一行時，type是system。

NULL: MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨索引查找完成。

例子

每一個type都給出了一個例子:

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:不用讀取表中所有信息，僅通過索引就可以獲取所需數據，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候，表示mysql服務器將在存儲引擎檢索行后再進行過濾。

Using temporary：表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢，常見 group by ; order by。

Using filesort：當Query中包含 order by 操作，而且無法利用索引完成的排序操作稱為“文件排序”。

Using join buffer：該值強調了在獲取連接條件時沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結果。如果出現(xiàn)了這個值，那應該注意，根據查詢的具體情況可能需要添加索引來改進能。

Impossible where：這個值強調了where語句會導致沒有符合條件的行（通過收集統(tǒng)計信息不可能存在結果）。

Select tables optimized away：這個值意味著僅通過使用索引，優(yōu)化器可能僅從聚合函數結果中返回一行

No tables used：Query語句中使用from dual 或不含任何from子句。

應用

在很多博客上，都有關于between,>,<,in,not in,like是否會使用索引，如果使用，那么type是什么的文章，其實這個問題的本質是對explain命令的使用，我們只需要寫幾個包含以上where條件的語句，就可以找到問題的答案了。我們新建一個叫user的表，并且插入10萬條隨機的英文姓名。測試的結果如下。

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相當于等號
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

從以上的測試結果，我們可以得出結論：
between and, >,<,in,not in,or,like都是會使用索引的，但是between and, >,<必須用在數值類型的列上；in，not in,or可以用到數值和字符串的列上；而like只能用到字符串類型的列上，而且必須是左邊不能以通配符開頭。