MySQL执行计划EXPLAIN

EXPLAIN 执行计划

我们先创建三张表。一张课程表，一张老师表，一张老师联系方式表（没有任何索引）。

DROP TABLE IF EXISTS course;

CREATE TABLE `course` (

`cid` int(3) DEFAULT NULL,

`cname` varchar(20) DEFAULT NULL,

`tid` int(3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS teacher;

CREATE TABLE `teacher` (

`tid` int(3) DEFAULT NULL,

`tname` varchar(20) DEFAULT NULL,

`tcid` int(3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS teacher_contact;

CREATE TABLE `teacher_contact` (

`tcid` int(3) DEFAULT NULL,

`phone` varchar(200) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `course` VALUES ('1', 'mysql', '1');

INSERT INTO `course` VALUES ('2', 'jvm', '1');

INSERT INTO `course` VALUES ('3', 'juc', '2');

INSERT INTO `course` VALUES ('4', 'spring', '3');

INSERT INTO `teacher` VALUES ('1', 'qingshan', '1');

INSERT INTO `teacher` VALUES ('2', 'jack', '2');

INSERT INTO `teacher` VALUES ('3', 'mic', '3');

INSERT INTO `teacher_contact` VALUES ('1', '13688888888');

INSERT INTO `teacher_contact` VALUES ('2', '18166669999');

INSERT INTO `teacher_contact` VALUES ('3', '17722225555');

id

id 是查询序列编号，每张表都是单独访问的，一个 SELECT 就会有一个序号。

id 值不同的时候，先查询 id 值大的（先大后小）。

-- 查询 mysql 课程的老师手机号

EXPLAIN SELECT tc.phone

FROM teacher_contact tc

WHERE tcid = (

    SELECT tcid

    FROM teacher t

    WHERE t.tid = (

    SELECT c.tid

    FROM course c

    WHERE c.cname = 'mysql'
    )

);

查询顺序：course c——teacher t——teacher_contact tc。

先查课程表，再查老师表，最后查老师联系方式表。子查询只能以这种方式进行，只有拿到内层的结果之后才能进行外层的查询。

id 值相同（从上往下）

-- 查询课程 ID 为 2，或者联系表 ID 为 3 的老师 
EXPLAIN
SELECT t.tname, c.cname, tc.phone
FROM teacher t,
     course c,
     teacher_contact tc
WHERE t.tid = c.tid
  AND t.tcid = tc.tcid
  AND (c.cid = 2 OR tc.tcid = 3);

id 值相同时，表的查询顺序是从上往下顺序执行。例如这次查询的 id 都是 1（说明 子查询被优化器转换成了连接查询），查询的顺序是 teacher t（3 条）——course c（4 条）——teacher_contact tc（3 条）。

在连接查询中，先查询的叫做驱动表，后查询的叫做被驱动表，我们肯定要把小表放在前面查询，因为它的中间结果最少。

既有相同也有不同

如果 ID 有相同也有不同，就是 ID 不同的先大后小，ID 相同的从上往下。

select type 查询类型

这里并没有列举全部（其它：DEPENDENT UNION、DEPENDENT SUBQUERY、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION）。

下面列举了一些常见的查询类型：

• SIMPLE

简单查询，不包含子查询和关联查询 union。

EXPLAIN SELECT * FROM teacher;

再看一个包含子查询的案例：

-- 查询 mysql 课程的老师手机号
EXPLAIN
SELECT tc.phone
FROM teacher_contact tc
WHERE tcid = (SELECT tcid FROM teacher t WHERE t.tid = (SELECT c.tid FROM course c WHERE c.cname = 'mysql'));

• PRIMARY

子查询 SQL 语句中的主查询，也就是最外面的那层查询。

• SUBQUERY

子查询中所有的内层查询都是 SUBQUERY 类型的。

• DERIVED

派生查询，表示在得到最终查询结果之前会用到临时表。例如：

-- 查询 ID 为 1 或 2 的老师教授的课程
EXPLAIN
SELECT cr.cname
FROM (SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2) cr;

对于关联查询，先执行右边的 table（UNION），再执行左边的 table，类型是DERIVED。

• UNION

用到了 UNION 查询（UNION 会用到内部的临时表）。同上例。

UNION ALL 不需要去重，因此不用临时表。

• UNION RESULT

主要是显示哪些表之间存在 UNION 查询。<union2,3>代表 id=2 和 id=3 的查询存在 UNION。同上例。

EXPLAIN
SELECT cr.cname
FROM (SELECT * FROM course WHERE tid = 1 UNION ALL SELECT * FROM course WHERE tid = 2) cr;

• type 访问方法

所有的连接类型中，上面的最好，越往下越差。

在常用的链接类型中：system > const > eq_ref > ref > range > index > all

这 里 并 没 有 列 举 全 部 （ 其 他 ： fulltext 、 ref_or_null 、 index_merger 、unique_subquery、index_subquery）。

以上访问类型除了 all，都能用到索引。

• const

主键索引或者唯一索引与常数进行等值匹配，只能查到一条数据的 SQL。

DROP TABLE IF EXISTS single_data;
CREATE TABLE single_data
(
    id      int(3) PRIMARY KEY,
    content varchar(20)
);
insert into single_data
values (1, 'a');

EXPLAIN
SELECT *
FROM single_data a
where id = 1;

• system

system 是 const 的一种特例，只有一行满足条件，对于 MyISAM、Memory 的表，只查询到一条记录，也是 system。

例如：只有一条数据的系统表。

EXPLAIN SELECT * FROM mysql.proxies_priv;

• eq_ref

通常出现在多表的 join 查询，被驱动表通过唯一性索引（UNIQUE 或 PRIMARY KEY）进行访问，此时被驱动表的访问方式就是 eq_ref。

eq_ref 是除 const 之外最好的访问类型。

先删除 teacher 表中多余的数据，teacher_contact 有 3 条数据，teacher 表有 3条数据

DELETE
FROM teacher
where tid in (4, 5, 6);
commit;

-- 备份
INSERT INTO `teacher` VALUES (4, 'james', 4);
INSERT INTO `teacher` VALUES (5, 'tom', 5);
INSERT INTO `teacher` VALUES (6, 'seven', 6);
commit;

为 teacher_contact 表的 tcid（第一个字段）创建主键索引。

-- ALTER TABLE teacher_contact DROP PRIMARY KEY;
ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);

为 teacher 表的 tcid（第三个字段）创建普通索引。

-- ALTER TABLE teacher DROP INDEX idx_tcid;
ALTER TABLE teacher ADD INDEX idx_tcid (tcid);

执行以下 SQL 语句：

select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;

此时的执行计划（teacher_contact 表是 eq_ref）：

explain select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;

小结：

以上三种 system，const，eq_ref，都是可遇而不可求的，基本上很难优化到这个状态。

ref：查询用到了非唯一性索引。

例如：使用 tcid 上的普通索引查询：

explain SELECT * FROM teacher where tcid = 3;

• range

对索引进行范围扫描。

如果 where 后面是 between and 或 <或 > 或 >= 或 <=或 in 这些，type 类型就为 range。

不走索引一定是全表扫描（ALL），所以先加上普通索引。

执行范围查询（字段上有普通索引）：

EXPLAIN SELECT * FROM teacher t WHERE t.tid <3;
-- 或
EXPLAIN SELECT * FROM teacher t WHERE tid BETWEEN 1 AND 2;

IN 查询也是 range（字段有主键索引）

EXPLAIN SELECT * FROM teacher_contact t WHERE tcid in (1,2,3);

Version:0.9 StartHTML:0000000105 EndHTML:0000000504 StartFragment:0000000141 EndFragment:0000000464

• index

Full Index Scan，查询全部索引中的数据（比不走索引要快）。

EXPLAIN SELECT tid FROM teacher;

• ALL

Full Table Scan，如果没有索引或者没有用到索引，type 就是 ALL。代表全表扫描。

小结：

一般来说，需要保证查询至少达到 range 级别，最好能达到 ref。

ALL（全表扫描）和 index（查询全部索引）都是需要优化的。

possible_key、key

可能用到的索引和实际用到的索引。如果是 NULL 就代表没有用到索引。

possible_key 可以有一个或者多个，比如查询多个字段上都有索引，或者一个字段同时有单列索引和联合索引。

能用到的索引并不是越多越好。可能用到索引不代表一定用到索引。

如果通过分析发现没有用到索引，就要检查 SQL 或者创建索引。

key_len

索引的长度（使用的字节数）。跟索引字段的类型、长度有关。

表上有联合索引：KEY comidx_name_phone (name,phone)

explain select * from user_innodb where name ='青山';

key_len =1023，为什么不是 255+11=266 呢？

这里的索引只用到了 name 字段，utf8mb4 编码 1 个字符 4 个字节。所以是255*4=1020。使用变长字段 varchar 需要额外增加 2 个字节，允许 NULL 需要额外增加 1 个字节。一共是 1023。

rows

MySQL 认为扫描多少行（数据或者索引）才能返回请求的数据，是一个预估值。一般来说行数越少越好。

filtered

这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比。

如果比例很低，说明存储引擎层返回的数据需要经过大量过滤，这个是会消耗性能的，需要关注。

ref

使用哪个列或者常数和索引一起从表中筛选数据，可以参考一下。

Extra

执行计划给出的额外的信息说明。

• using index

属于覆盖索引的情况，不需要回表。

EXPLAIN SELECT tid FROM teacher ;

• using where

使用了 where 过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在 server 层进行过滤（跟是否使用索引没有关系）。

EXPLAIN select * from user_innodb where phone ='13866667777';

• using Index Condition

索引下推，昨天的课已经讲过了。

• using filesort

不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）。需要优化。

（复合索引的前提）

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;

ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

执行 SQL：

EXPLAIN select * from user_innodb where name ='青山' order by id;

（order by id 引起）

• using temporary

在查询的时候，需要做去重、排序之类的工作的时候，可能会用到临时表。

举几个例子：

1、distinct 非索引列

EXPLAIN select DISTINCT(tid) from teacher t;

2、group by 非索引列

EXPLAIN select tname from teacher group by tname;

3、使用 join 的时候，group 任意列

EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid;

Using Temporary 需要优化，例如创建复合索引。

总结一下：

模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是怎么处理一条 SQL 语句的。

通过这种方式我们可以分析语句或者表的性能瓶颈。

如果需要具体的 cost 信息，可以用：EXPLAIN FORMAT=JSON。

如果觉得 EXPLAIN 还不够详细，可以用开启 optimizer trace。

SQL 与索引优化

SQL 语句的优化的目标，大部分时候都是用到索引。