MySQL Explain 结果解读与实践

Explain 结果解读与实践

基于 MySQL 5.0.67 ，存储引擎 MyISAM 。

注：单独一行的"%%"及"`"表示分隔内容，就象分开“第一章”“第二章”。

explain 可以分析 select 语句的执行，即 MySQL 的“执行计划”：

mysql> explain select 1;

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

用"\G"代替分号可得到竖排的格式：

mysql> explain select 1\G
*************************** 1
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used

`

可以用 desc 代替 explain ：

desc select 1;

%%

id 列

建表：

create table a(a_id int);

create table b(b_id int);

create table c(c_id int);

mysql> explain select * from a join b on a_id=b_id where b_id in (select c_id from c);
+----+--------------------+-------+...
| id | select_type        | table |...
+----+--------------------+-------+...
|  1 | PRIMARY            | a     |...
|  1 | PRIMARY            | b     |...
|  2 | DEPENDENT SUBQUERY | c     |...
+----+--------------------+-------+...

从 3 个表中查询，对应输出 3 行，每行对应一个表， id 列表示执行顺序，id 越大，越先执行，id 相同，由上至下执行。此处的执行顺序为（以 table 列表示）：c -> a -> b

%%

select_type 列

MySQL 把 SELECT 查询分成简单和复杂两种类型，复杂类型又可以分成三个大类：简单子查询、所谓的衍生表（子查询在 FROM 子句里）和 UNION 。

SIMPLE：查询中不包含子查询或者UNION

mysql> explain select * from a;
+----+-------------+-------+...
| id | select_type | table |...
+----+-------------+-------+...
|  1 | SIMPLE      | a     |...
+----+-------------+-------+...

SUBQUERY：子查询

PRIMARY:子查询上层

mysql> explain select * from a where a_id in (select b_id from b);
+----+--------------------+-------+...
| id | select_type        | table |...
+----+--------------------+-------+...
|  1 | PRIMARY            | a     |...
|  2 | DEPENDENT SUBQUERY | b     |...
+----+--------------------+-------+...

DERIVED：在FROM列表中包含子查询， MySQL 会递归执行这些子查询，把结果放在临时表里。

mysql> explain select count(*) from (select * from a) as der;
+----+-------------+-------+...
| id | select_type | table |...
+----+-------------+-------+...
|  1 | PRIMARY     | NULL  |...
|  2 | DERIVED     | a     |...
+----+-------------+-------+...

%%

table 列

显示每行对应的表名。若在 SELECT 语句中为表起了别名，则会显示表的别名。

一个很复杂的示例及解释可参考《高性能 MySQL 》（第二版）中文版 P467(pdf.491) 〈附录 B.2.3 table 列〉

%%

type 列

MySQL 在表里找到所需行的方式。包括（由左至右，由最差到最好）：

| All | index | range | ref | eq_ref | const,system | null |

ALL

全表扫描，MySQL 从头到尾扫描整张表查找行。

mysql> explain select * from a\G

...

         type: ALL

如果加上 limit 如 select * from a limit 10 MySQL 会扫描 10 行，但扫描方式不会变，还是从头到尾扫描。

`

index

按索引次序扫描表，就是先读索引，再读实际的行，其实还是全表扫描。主要优点是避免了排序，因为索引是排好序的。

建表：

create table a(a_id int not null, key(a_id));

insert into a value(1),(2)

mysql> explain select * from a\G
...
         type: index

`

range

以范围的形式扫描索引

建表：

create table a(a_id int not null, key(a_id));

insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

mysql> explain select * from a where a_id > 1\G
...
         type: range

...

IN 比较符也会用 range