# explain学习笔记
# 基础说明
# 介绍
使用 explain 关键字可以模拟优化器执行 SQL 查询语句，从而之道 MySQL 是如何处理你的 SQL 语句的。分析查询语句或是表结构的性能瓶颈。
# explain能干嘛
1、表的读取顺序
2、数据读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、没张表有多少行被优化器查询
# 用法
explain + SQL语句
mysql> explain select * from tbl_emp;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tbl_emp | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1
2
3
4
5
6
2
3
4
5
6
# 各字段解释
# id
select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序
三种情况
1、id 相同，执行顺序由上至下
2、id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
3、id 相同不同都存在时：id 如果相关，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
# select_type
查询的类型，主要用于区别普通查询、联合查询、子查询等各种复杂查询。
主要有以下类型
1、SIMPLE：简单的 select 查询，查询中不包含子查询或者 union
mysql> explain select * from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 200 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1
2
3
4
5
6
2
3
4
5
6
2、PRIMARY：查询中包含任何复杂的子部分，最外层查询被标记为 primary
3、SUBQUERY：在 select 或者 where 列表中包含的子查询
4、DERIVED：在 from 列表中包含的子查询被标记为 derived(衍生)，MySQL 会递归执行这些子查询，把结果放在临时表里。
5、UNION：若第二个 select 出现在 union 之后，则被标记为 union；若 union 包含在 from 子句的子查询中，外层 select 被标记为 derived。
6、UNION RESULT：从 union 表获取结果的 select。
mysql> explain select * from tbl_dept a left join tbl_emp b on a.id = b.dept_id
-> union
-> select * from tbl_dept a right join tbl_emp b on a.id = b.dept_id;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
| 1 | PRIMARY | b | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 2 | UNION | b | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL |
| 2 | UNION | a | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# table
显示了对应行正在访问的表。
# partitions
分区表的时候表示落到哪个分区了。
# type
显示查询使用了何种类型，主要有以下几种，从好到坏依次是：
system > const > eq_ref > ref > range > index > all
1、system：表只有一行记录(等于系统表)，这是 const 类型的特例，平时不会出现。
mysql> explain select * from version;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
1
2
3
4
5
6
7
2
3
4
5
6
7
2、const：表示通过索引一次就能找到了，const 用于比较 primary key或者 unique 索引。因为只匹配一行数据，所以很快。
如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。
mysql> explain select * from tbl_dept where id = 1;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tbl_dept | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1
2
3
4
5
6
2
3
4
5
6
3、eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。
4、ref：非唯一索引扫描，返回匹配某个单独值的所在行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到
多个符合条件的行，所以它应该属于查找和扫描的混合体。
5、range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的 where 语句中出现了 between、
<、>、in 等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，结束于另一点，不用扫描全部索引。
mysql> explain select * from tbl_emp where id > 2;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_emp | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 6 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1
2
3
4
5
6
2
3
4
5
6
6、index：full index scan，index 与 all 区别为 index 类型只遍历索引树。这通常比 all 快，因为索引文件通常比数据文件小。
其实 index 和 all 都是读全表，但是 index 是从索引中读取的，all 是从硬盘中读的。
mysql> explain select id from tbl_emp;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_emp | NULL | index | NULL | PRIMARY | 4 | NULL | 8 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1
2
3
4
5
6
2
3
4
5
6
7、all：full table scan，遍历全表来找到匹配的行。
mysql> explain select * from tbl_emp;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tbl_emp | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1
2
3
4
5
6
2
3
4
5
6
# possible_keys
显示可能应用到这张表中的索引，一个或者多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定查询实际使用。
# key
实际使用的索引，如果为 null，则没有使用索引。 查询中若使用了覆盖索引，则该索引仅出现在 key 列表中。
mysql> explain select id from tbl_emp;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_emp | NULL | index | NULL | PRIMARY | 4 | NULL | 8 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1
2
3
4
5
6
2
3
4
5
6
# key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的。
# ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
mysql> explain select id from tbl_emp where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | tbl_emp | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1
2
3
4
5
6
2
3
4
5
6
mysql> explain select * from tbl_dept a,tbl_emp b where a.id = b.dept_id and b.name='z3';
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+--------------------------+
| 1 | SIMPLE | b | NULL | ref | idx_n_d | idx_n_d | 63 | const | 1 | 100.00 | Using where; Using index |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | study.b.dept_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+--------------------------+
1
2
3
4
5
6
7
2
3
4
5
6
7
# rows
根据表统计信息及索引选用情况，大概估算出找到所需的记录需要读取的行数。
# filtered
显示的是针对表里符合某个条件(where 子句或者联接条件)的记录数的百分比所做的一个悲观估算。 如果把 rows 列和这个百分比相乘，就能看到 MySQL 估算它将和查询计划里前一个表关联的行数。
# Extra
这一列包含的是不适合在其他列显示的额外信息。常见的重要值如下：
1、Using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引进行顺序读取。
MySQL 中无法利用索引完成的排序操作称为"文件排序"。
mysql> explain select * from tbl_dept order by loc_add;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | tbl_dept | NULL | index | NULL | idx_d_l | 216 | NULL | 5 | 100.00 | Using index; Using filesort |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1
2
3
4
5
6
2
3
4
5
6
2、Using temporary：使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见排序
order by 和分组查询 group by。
mysql> explain select dept_id from tbl_emp group by dept_id;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
| 1 | SIMPLE | tbl_emp | NULL | index | idx_n_d | idx_n_d | 68 | NULL | 8 | 100.00 | Using index; Using temporary; Using filesort |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
1
2
3
4
5
6
2
3
4
5
6
3、Using index：表示相应的 select 操作中使用了覆盖索引，避免访问了表的数据行，效率不错。
如果同时出现 using where，表明索引被用来执行索引键值的查找。如果没有同时出现 using where，表明索引用来读取
数据而非执行查找操作。
覆盖索引：就是 select 的数据列只需要从索引中就能取到，不必读取数据行，MySQL 可以利用索引返回 select 列表中的字段，
不必根据索引再次读取数据文件，其实就是查询列要被建立的索引覆盖。
4、Using where：使用了 where 过滤。
5、Using join buffer：使用了连接缓存。
6、impossible where：where 子句的值总是 false，不能用来获取任何元组。
mysql> explain select * from tbl_emp where name = 's7' and name ='s8';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1
2
3
4
5
6
2
3
4
5
6
7、select table optimized away：在没有 group by子句的情况下，基于索引优化 min/max 操作或者对于 myisam 存储引擎
优化 count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
mysql> explain select max(id) from tbl_emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1
2
3
4
5
6
2
3
4
5
6
8、distinct：优化 distinct 操作，在找到第一匹配的元组后即停止找同样值的动作。
# 使用案例
# 单表优化案例
# 用到的表结构及数据
create table if not exists `article` (
`id` int(11) unsigned not null primary key auto_increment,
`author_id` int(11) unsigned not null ,
`category_id` int(11) unsigned not null,
`views` int(11) unsigned not null ,
`comments` int(11) unsigned not null ,
`title` varchar(255) not null ,
`content` text not null
);
insert into `article`(`author_id`, `category_id`,`views`, `comments`, `title`, `content`) values
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
# 案例
1、category_id为1，comments大于1，views最多
未建索引时的效果：
mysql> explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1
2
3
4
5
6
2
3
4
5
6
建立索引：
mysql> create index idx_ccv on article(category_id,comments,views);
1
mysql> explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | article | NULL | range | idx_ccv | idx_ccv | 8 | NULL | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+
1
2
3
4
5
6
2
3
4
5
6
由于中间有范围查询，依然产生了文件排序。取消范围查询的效果如下：
mysql> explain select id,author_id from article where category_id = 1 and comments = 1 order by views desc limit 1;
+----+-------------+---------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | article | NULL | ref | idx_ccv | idx_ccv | 8 | const,const | 1 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
1
2
3
4
5
6
2
3
4
5
6
删除索引：
mysql> drop index idx_ccv on article;
1
重新建立索引：
mysql> create index idc_cv on article(category_id,views);
1
再次执行，查看效果：
mysql> explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | article | NULL | ref | idc_cv | idc_cv | 4 | const | 2 | 33.33 | Using where |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1
2
3
4
5
6
2
3
4
5
6
此时没有了文件排序，并且 type 变成了 ref。
# 两表优化案例
# 用到的表结构及数据
create table if not exists `class` (
`id` int(11) unsigned not null auto_increment,
`card` int(11) unsigned not null ,
primary key (`id`)
);
create table if not exists `book` (
`bookid` int(11) unsigned not null auto_increment,
`card` int(11) unsigned not null,
primary key (`bookid`)
);
# 多重复几次，随机生成一批数据就行
insert into class(`card`) values(floor(1+(rand() * 20)));
insert into book(`card`) values(floor(1+(rand() * 20)));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 案例
1、左连接
没有建立索引的时候：
mysql> explain select * from class left join book on book.card = class.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 36 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 157 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
1
2
3
4
5
6
7
2
3
4
5
6
7
在 book 表建立索引：
mysql> create index idx_card on book(card);
1
此时的效果：
mysql> explain select * from class left join book on book.card = class.card;
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 36 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | idx_card | idx_card | 4 | study.class.card | 7 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
1
2
3
4
5
6
7
2
3
4
5
6
7
删掉 book 表的索引，在 class 表建立索引：
mysql> drop index idx_card on book;
mysql> create index idx_card on class(card);
1
2
2
此时的效果：
mysql> explain select * from class left join book on book.card = class.card;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | index | NULL | idx_card | 4 | NULL | 36 | 100.00 | Using index |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 157 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
1
2
3
4
5
6
7
2
3
4
5
6
7
对比可见：左连接要在右边的表建立索引效果更好。
2、右链接
删除之前建立的索引：
mysql> drop index idx_card on class;
1
此时的效果：
mysql> explain select * from class right join book on book.card = class.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 157 | 100.00 | NULL |
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 36 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
1
2
3
4
5
6
7
2
3
4
5
6
7
在 class 表建立索引：
mysql> create index idx_card on class(card);
1
此时的效果：
mysql> explain select * from class right join book on book.card = class.card;
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 157 | 100.00 | NULL |
| 1 | SIMPLE | class | NULL | ref | idx_card | idx_card | 4 | study.book.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+
1
2
3
4
5
6
7
2
3
4
5
6
7
重复之前的操作：
mysql> drop index idx_card on class;
mysql> create index idx_card on book(card);
1
2
2
此时的效果：
mysql> explain select * from class right join book on book.card = class.card;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | book | NULL | index | NULL | idx_card | 4 | NULL | 157 | 100.00 | Using index |
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 36 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
1
2
3
4
5
6
7
2
3
4
5
6
7
对比可见：右连接要在左边的表建立索引效果更好。
# 三表优化案例
# 用到的表结构及数据
create table if not exists `phone` (
`phoneid` int(11) unsigned not null auto_increment,
`card` int(11) unsigned not null,
primary key (`phoneid`)
);
# 多重复几次，随机生成一批数据就行
insert into phone(`card`) values(floor(1+(rand() * 20)));
1
2
3
4
5
6
7
2
3
4
5
6
7
# 三表 left join
没有建立索引的效果：
mysql> explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 36 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 157 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | phone | NULL | ALL | NULL | NULL | NULL | NULL | 62 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
按照之前的经验，左连接在右边的表建立索引：
mysql> create index idx_card on book(card);
mysql> create index idx_card on phone(card);
1
2
2
看下现在的效果：
mysql> explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 36 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | idx_card | idx_card | 4 | study.class.card | 7 | 100.00 | Using index |
| 1 | SIMPLE | phone | NULL | ref | idx_card | idx_card | 4 | study.book.card | 3 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
很明显，book 和 phone 表 从全表扫描变成了 ref，效果更好了。
【结论】
1、尽量减少 join 语句中的 Nested Loop 的循环总次数："永远用小结果集驱动大的结果集"。
2、优先优化 Nested Loop 的内层循环。
3、保证 join 语句中被驱动表上 join 条件字段已经被索引。
4、当无法保证被驱动表的 join 条件字段被索引且内存资源充足的前提下，可以调整 joinBuffer 的设置。
# 三表 right join
没有建立索引时的效果：
mysql> explain select * from class right join book on class.card = book.card right join phone on book.card = phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | phone | NULL | ALL | NULL | NULL | NULL | NULL | 62 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 157 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 36 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
给右连接左边的表建立索引：
mysql> create index idx_card on book(card);
mysql> create index idx_card on class(card);
1
2
2
此时的效果：
mysql> explain select * from class right join book on class.card = book.card right join phone on book.card = phone.card;
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+--------------------------+
| 1 | SIMPLE | phone | NULL | ALL | NULL | NULL | NULL | NULL | 62 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | idx_card | idx_card | 4 | study.phone.card | 7 | 100.00 | Using index |
| 1 | SIMPLE | class | NULL | ref | idx_card | idx_card | 4 | study.book.card | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+--------------------------+
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
由此可见，确实使用到了索引。
# left 和 right 都有的时候
没有建立索引时的效果：
mysql> explain select * from class right join book on class.card = book.card left join phone on book.card = phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 157 | 100.00 | NULL |
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 36 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | phone | NULL | ALL | NULL | NULL | NULL | NULL | 62 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
根据左连接右边的表建立索引，右连接左边的表建立索引的规则建立相关的索引：
mysql> create index idx_card on class(card);
mysql> create index idx_card on phone(card);
1
2
2
此时的效果：
mysql> explain select * from class right join book on class.card = book.card left join phone on book.card = phone.card;
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 157 | 100.00 | NULL |
| 1 | SIMPLE | class | NULL | ref | idx_card | idx_card | 4 | study.book.card | 1 | 100.00 | Using index |
| 1 | SIMPLE | phone | NULL | ref | idx_card | idx_card | 4 | study.book.card | 3 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8