文章目录
一、前言
最近在读《MySQL 是怎样运行的》、《MySQL技术内幕 InnoDB存储引擎 》，后续会随机将书中部分内容记录下来作为学习笔记，部分内容经过个人删改，因此可能存在错误，如想详细了解相关内容强烈推荐阅读相关书籍
系列文章内容目录：
- 【MySQL00】【 杂七杂八】
- 【MySQL01】【 Explain 命令详解】
- 【MySQL02】【 InnoDB 记录存储结构】
- 【MySQL03】【 Buffer Pool】
- 【MySQL04】【 redo 日志】
- 【MySQL05】【 undo 日志】
- 【MySQL06】【MVCC】
- 【MySQL07】【锁】
- 【MySQL08】【死锁】
本文 Mysql 版本为 5.7.16，
涉及表结构来源两处：
-
数据库 基于 Mysql 官方提供的 sakila 内容基于 下载地址：https://dev.mysql.com/doc/index-other.html
-
如下：
CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_a` (`a`), KEY `idx_b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `t2` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
二、Explain 概览
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 通过explain我们可以获得以下信息：
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
使用方法：explain + sql语句。 如下
可以看到下面我们来分析每一列的内容
| 列名 | 描述 |
|---|---|
| id | 选择标识符。数字越大，越先执行；数字相同，从上往下依次执行。需要注意在连接查询（join ）中由于每个表都会对应一条记录，因此会存在多条 ID 相同的记录，此时出现在前面的表示驱动表，出现在后面的表示被驱动表。 |
| select_type | 表示查询的类型 |
| table | 输出结果集的表。EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，table 即为该表的表名 |
| partitions | 匹配的分区 |
| type | 表示表的连接类型 |
| possible_keys | 表示查询时，可能使用的索引 |
| key | 表示实际使用的索引 |
| key_len | 索引字段的长度 |
| ref | 列与索引的比较 |
| rows | 扫描出的行数(估算的行数) |
| filtered | 按表条件过滤的行百分比 |
| Extra | 执行情况的描述和说明 |
三、Explain 详解
1. id
选择标识符。数字越大，越先执行；数字相同，从上往下依次执行。需要注意在连接查询（join ）中由于每个表都会对应一条记录，因此会存在多条 ID 相同的记录，此时出现在前面的表示驱动表，出现在后面的表示被驱动表。
-
id 不同 : ID不同，数字越大，越先执行。 如下, 先执行SUBQUERY，再执行 PRIMARY 。
mysql> explain select * from film where film_id = (select max(film_id) from film); +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+ | 1 | PRIMARY | film | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | NULL | | 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+ 2 rows in set (0.08 sec) -
id 相同 :按照从上到下的顺序依次执行
mysql> explain select f.* from film f left join film_actor fa on fa.film_id = f.film_id; +----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+ | 1 | SIMPLE | f | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL | | 1 | SIMPLE | fa | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | sakila.f.film_id | 5 | 100.00 | Using index | +----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+ 2 rows in set (0.06 sec) -
id为null : 表示一个结果集，并不需要使用它来进行查询。
UNION 会将多个查询的结果集合合并起来并对结果集中的记录去重，去重的实现是通过 MySQL 的 内部临时表，如下即使将 id 为 1 和 2 的查询结果集合并起来去重，在内部创建了一个名为 <union1,2> 的零时表，id 为 null 说明这个临时表是为了合并两个查询的结果集而创建的 。额外的，由于 union all 不需要对结果集去重，他只是单纯的将结果集合并后返回给用户也就不需要临时表，因此就没有 id = NULL 的记录。
-- 如下 UNION RESULT 作为一个合并结果集的操作的执行id为null。 mysql> explain select film_id from film union select film_id from film_actor; +------+--------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | film | NULL | index | NULL | idx_fk_language_id | 1 | NULL | 1000 | 100.00 | Using index | | 2 | UNION | film_actor | NULL | index | NULL | idx_fk_film_id | 2 | NULL | 5462 | 100.00 | Using index | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------+ 3 rows in set (0.03 sec) -- 额外的，由于 union all 不需要对结果集去重，他只是单纯的将结果集合并后返回给用户也就不需要临时表，因此就没有 id = NULL 的记录。 mysql> explain select * from t1 union all select * from t2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL | | 2 | UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set (0.04 sec)
需要注意的是：
-
在连接查询（join ）中由于每个表都会对应一条记录，因此会存在多条 ID 相同的记录，此时出现在前面的表示驱动表，出现在后面的表示被驱动表。
-- 如下：t1在t2 之前，所以 t1 表将作为驱动表，t2 表将作为被驱动表 mysql> explain select * from t1 inner join t2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ 2 rows in set (0.02 sec) -
查询优化器可能对涉及子查询的查询语句重写，从而转换为连接查询，如果想知道查询优化器是否对某个包含子查询的语句进行了重写，可以直接看执行计划。如下：
-- 这一句由于包含 or id = 1 的部分，因此无法转换为 连接查询，所以存在 DEPENDENT SUBQUERY, id 列的值不同，说明存在执行优先级 mysql> explain select * from t1 where id in (select id from t2) or id = 1; +----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+ | 1 | PRIMARY | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t2 | NULL | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | +----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+ 2 rows in set (0.03 sec) -- 这一句可以直接转换为连接查询，所以id 列相同。 mysql> explain select * from t1 where id in (select id from t2); +----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.t1.id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+ 2 rows in set (0.04 sec)
2. select_type
select_type 代表查询的类型, 各种取值如下：
-
SIMPLE: 即简单查询，查询语句中不包含 UNION 或者子查询的查询都算做 SIMPLE 类型。
mysql> explain select * from film; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set (0.05 sec) mysql> explain select f.* from film f left JOIN film_actor fa on fa.film_id = f.film_id; +----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+ | 1 | SIMPLE | f | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL | | 1 | SIMPLE | fa | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | sakila.f.film_id | 5 | 100.00 | Using index | +----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+ 2 rows in set (0.03 sec) -
PRIMARY: 对于包含 UNION、UNION ALL 或者子查询的大查询来说，他是由几个小查询组成的。
mysql> explain select film_id from film union all select film_id from film_actor; +----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ | 1 | PRIMARY | film | NULL | index | NULL | idx_fk_language_id | 1 | NULL | 1000 | 100.00 | Using index | | 2 | UNION | film_actor | NULL | index | NULL | idx_fk_film_id | 2 | NULL | 5462 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ 2 rows in set (0.03 sec) mysql> explain select film_id from film union all select film_id from film_actor where film_id > (select min(film_id) from film_actor); +----+-------------+------------+------------+-------+----------------+--------------------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+----------------+--------------------+---------+------+------+----------+------------------------------+ | 1 | PRIMARY | film | NULL | index | NULL | idx_fk_language_id | 1 | NULL | 1000 | 100.00 | Using index | | 2 | UNION | film_actor | NULL | range | idx_fk_film_id | idx_fk_film_id | 2 | NULL | 5452 | 100.00 | Using where; Using index | | 3 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+------------+------------+-------+----------------+--------------------+---------+------+------+----------+------------------------------+ 3 rows in set (0.02 sec) -
UNION：对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的；其中除了最左边的那个小查询外，其余小查询的 select_type 的值就是 UNUION。
mysql> explain select film_id from film union all select film_id from film_actor UNION all select film_id from film_text; +----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ | 1 | PRIMARY | film | NULL | index | NULL | idx_fk_language_id | 1 | NULL | 1000 | 100.00 | Using index | | 2 | UNION | film_actor | NULL | index | NULL | idx_fk_film_id | 2 | NULL | 5462 | 100.00 | Using index | | 3 | UNION | film_text | NULL | index | NULL | PRIMARY | 2 | NULL | 1000 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ 3 rows in set (0.04 sec) -
UNION RESULT : MySQL 选择用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT
mysql> explain select * from t1 union select * from t2; +------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL | | 2 | UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set (0.06 sec) -
SUBQUERY : 如果包含子查询的查询语句不能转换为对应的半连接形式，并且该子查询是不相关子查询，而且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查询就是 SUBQUERY。(需要注意： 查询条件是 SUBQUERY 的子查询会被物化，所以该子查询只需要执行一遍)
mysql> explain SELECT * FROM film_category WHERE film_id = (SELECT max(film_id) FROM film_actor); +----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+------------------------------+ | 1 | PRIMARY | film_category | NULL | ref | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | Using where | | 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+------------------------------+ 2 rows in set (0.03 sec) mysql> explain SELECT (select 1 from film_category) from film; +----+-------------+---------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+ | 1 | PRIMARY | film | NULL | index | NULL | idx_fk_language_id | 1 | NULL | 1000 | 100.00 | Using index | | 2 | SUBQUERY | film_category | NULL | index | NULL | fk_film_category_category | 1 | NULL | 1000 | 100.00 | Using index | +----+-------------+---------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+ 2 rows in set (0.05 sec) -
DEPENDENT SUBQUERY : 如果包含子查询的查询语句不能够转为对应的半连接形式，并且该子查询被查询优化器转换为相关子查询的形式，则该子查询第一个 SELECT 关键字代表的那个查询就是 DEPENDENT SUBQUERY。需要注意 DEPENDENT SUBQUERY 的子查询可能会被执行多次。
如下SQL，大查询中包含一个子查询，子查询中又包含由 UNION 连接起来的两个小查询。从执行计划可以看到，
select id from t2 where a = '1'是子查询的第一个查询，所以他的类型是 DEPENDENT SUBQUERY，而select id from t1 where a = '1'这个查询就是 DEPENDENT UNION 类型。mysql> explain select * from t1 where id in (select id from t2 where a = '1' union select id from t1 where a = '1'); +------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t2 | NULL | eq_ref | PRIMARY,a | PRIMARY | 4 | func | 1 | 5.00 | Using where | | 3 | DEPENDENT UNION | t1 | NULL | eq_ref | PRIMARY,a | PRIMARY | 4 | func | 1 | 5.00 | Using where | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ 4 rows in set (0.07 sec) -
DEPENDENT UNION : 在包含 UNION 或者 UNION ALL的大查询中，如果各个子查询都依赖于外层查询，除了最左边的那个小查询之外，其余小查询就是 DEPENDENT UNION 。
如下SQL，大查询中包含一个子查询，子查询中又包含由 UNION 连接起来的两个小查询。从执行计划可以看到，
select id from t2 where a = '1'是子查询的第一个查询，所以他的类型是 DEPENDENT SUBQUERY，而select id from t1 where a = '1'这个查询就是 DEPENDENT UNION 类型。mysql> explain select * from t1 where id in (select id from t2 where a = '1' union select id from t1 where a = '1'); +------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t2 | NULL | eq_ref | PRIMARY,a | PRIMARY | 4 | func | 1 | 5.00 | Using where | | 3 | DEPENDENT UNION | t1 | NULL | eq_ref | PRIMARY,a | PRIMARY | 4 | func | 1 | 5.00 | Using where | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ 4 rows in set (0.07 sec) -
DERIVED : 在包含派生表的查询中，如果是以物化派生表的方式执行查询，则派生表对应的子查询是 DERIVED。
mysql> explain SELECT ff.* FROM ( SELECT film_id FROM film UNION ALL SELECT film_id FROM film_actor ) ff; +----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 6462 | 100.00 | NULL | | 2 | DERIVED | film | NULL | index | NULL | idx_fk_language_id | 1 | NULL | 1000 | 100.00 | Using index | | 3 | UNION | film_actor | NULL | index | NULL | idx_fk_film_id | 2 | NULL | 5462 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ 3 rows in set (0.02 sec) -
MATERIALIZED : 当查询优化器在执行包含子查询的语句时，选择将子查询物化后与外层查询进行连接查询，该子查询对应的类型即为 MATERIALIZED 。
mysql> explain select * from t1 where b in (select b from t2); +----+--------------+-------------+------------+--------+---------------+------------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------+------------+---------+-----------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using where | | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 5 | demo.t1.b | 1 | 100.00 | NULL | | 2 | MATERIALIZED | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL | +----+--------------+-------------+------------+--------+---------------+------------+---------+-----------+------+----------+-------------+ 3 rows in set (0.06 sec)
3. table
显式的查询显式查询的表名，如果查询使用了别名，这里显示的则是别名。如果不涉及对数据库的操作，那么这里显示的就是null 。如果显示为 <derived N > 则表明这个是临时表，N为执行计划id，表示这个结果来源于这个查询自身，如果显示为 <union M, N>，其也是一个临时表，表示这个结果来自于 union 查询的 id为 M，N的结果集。
不涉及表操作 ：
mysql> explain select sleep(1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.06 sec)
4. type
type列很重要，是用来说明表与表之间是如何进行关联操作的，有没有使用索引。MySQL中“关联”一词比一般意义上的要宽泛，MySQL认为任何一次查询都是一次“关联”，并不仅仅是一个查询需要两张表才叫关联，所以也可以理解MySQL是如何访问表的。主要有下面几种类别。
type 依次性能从好到差 ： system，const，eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range,index,all。除了all之外，其他type都能用到索引，除了 index_merge 之外，其他的type只可以用到一个索引，一般来说，好的 sql查询至少达到 range 级别，最好能达到 ref
-
system : 当表中只有一条记录并且该表使用的存储引擎（如 MyISAM、MEMORY）的统计数据是精确的，那么该表的访问方法就是 system。如果是 Innodb 引擎表，type 在这个情况下通常都是 all 或者 index。
-
const : 当根据主键或者唯一二级索引列与常数进行等值匹配的时候，对单表的访问方法就是 const。即查询是通过主键或唯一二级索引来与常量做比较，最终结果只能有一条记录。
mysql> explain select * from film where film_id = 1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | film | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set (0.01 sec) -
eq_ref : 执行连接查询时，如果被驱动表是通过主键或者不允许存储 NULL 值的唯一二级索引列等值匹配的方式进行访问的（如果该主键或者不允许存储 NULL 值的唯一二级索引是联合索引，则所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref。即查询是通过主键或唯一二级索引来来作为查询条件，最终结果只能有一条记录。
如下，t1 将作为驱动表，t2 作为被驱动表。在访问 t2 表时，可以通过主键等值匹配来访问：
mysql> explain select * from t1 inner join t2 on t1.id = t2.id; +----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.t1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+ 2 rows in set (0.07 sec) -
ref : 当通过普通索引列与常量进行等值匹配的方式来查询某个表时，对该表的访问方法就可能是 ref。另外，如果是执行连接查询，被驱动表中的某个普通的二级索引列与驱动表中的某个列进行等值匹配，那么对被驱动表也可能使用 ref。
相较于 eq_ref。 ref 不要求记录唯一性，也就是不要求索引是主键或者唯一索引。ref在连接查询时，只要求是索引即可，返回记录也可出现多条。总之，返回数据可能不唯一的等值查找就可能出现。
mysql> explain select * from film f left join film_actor fa on fa.film_id = f.film_id; +----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | fa | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL | | 1 | SIMPLE | f | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | sakila.fa.film_id | 5 | 100.00 | NULL | +----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+ 2 rows in set (0.06 sec) mysql> explain select * from film where title = 'film1'; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | film | NULL | ref | idx_title | idx_title | 514 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set (0.07 sec) -
fulltext : 全文索引检索，全文检索的优先级很高，若全文索引和普通索引同时存在，mysql不管代价，优先选择全文索引。
-
ref_or_null : 当对普通二级索引列进行等值匹配且该索引列的值也可以是 NULL 值时，对该表的访问方法就可能是 ref_or_null。简单来说类似ref，但是可以搜索值为NULL的行。
mysql> explain select * from t1 where a = 'a' or a is null; +----+-------------+-------+------------+-------------+---------------+-----+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+-----+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | t1 | NULL | ref_or_null | a | a | 5 | const | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------------+---------------+-----+---------+-------+------+----------+-----------------------+ 1 row in set (0.06 sec) -
index_merge : 一般清理下只会为单个索引生成扫描区间，但是 InnoDB 存在Intersection (交集)索引合并、Union （并集）索引合并、Sort-Union 索引合并 三种索引合并的方式来执行查询。(关于这三种索引合并方式，本文末有补充)。
如下，type 为 index_mege， 即MySQL 打算使用索引合并的方式来执行对 t1 表的查询。
mysql> explain select * from t1 where a = 'a' or b = 'b'; +----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | t1 | NULL | index_merge | idx_a,idx_b | idx_a,idx_b | 5,5 | NULL | 2 | 100.00 | Using union(idx_a,idx_b); Using where | +----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+ 1 row in set (0.06 sec) -
unique_subquery : 类似于两表连接中被驱动表的 eq_ref 访问方法， unique_subquery 针对的事一些包含 in 子查询的查询语句。如果查询优化器决定将 in 子查询转换为 exists 子查询，而且子查询在转换之后可以使用主键或者不允许存储 NULL 值的唯一二级索引进行等值匹配，那么该子查询执行计划的 type 列的值就是 unique_subquery 。
mysql> explain select * from t1 where a in (select id from t2 where t1.id = t2.id) or b = 'a'; +----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------------------------+ | 1 | PRIMARY | t1 | NULL | ALL | idx_b | NULL | NULL | NULL | 100 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.t1.id | 1 | 100.00 | Using where; Using index | +----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------------------------+ 2 rows in set (0.06 sec) -
index_subquery : 与 unique_subquery 类似，只不过在访问子查询中表时使用的是普通索引。
-
range : 如果使用索引获取某些单点扫描区间的记录，那么就可能使用到 range 访问方法，即索引范围扫描，常见于使用 >， <, is null, between, in, like 等范围运算符查询中。
mysql> explain select * from t1 where id > 0; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 100 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set (0.06 sec) -
index : 当可以使用索引覆盖，但需要扫描全部的索引时，该表的访问方法就是 index。 额外的，当我们需要执行全表扫描，并且需求对主键进行排序时，此时的 type 也是 index。
mysql> explain select id from t1; +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | index | NULL | idx_a | 5 | NULL | 100 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ 1 row in set (0.04 sec) -
all : 全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。效率最差
mysql> explain select * from t1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set (0.04 sec)
5. possible_keys
表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，这里可能出现多个的。如下：对于 t1 表 字段a 和 b 都是普通索引，如下语句在执行前就可能会使用 idx_a 或 idx_b 为索引。最终通过查询优化器计算后认为执行全表扫描成本更低（因为表里就一条记录）
mysql> explain select * from t1 where a > 'a' and b > 'b';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | idx_a,idx_b | NULL | NULL | NULL | 100 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.04 sec)
6. key
表示在某个查询语句中，对某个表执行单表查询时真正用到的索引有哪些。之所以出现 possible_keys 与 key 的不同是因为MySQL 经过查询优化器计算不同所以的使用成本后，会选择成本较少的索引使用。需要注意的是 ：当select_type 为 index_merge 时，这里可能出现两个以上的索引，其他的 select_type 这里只会出现一个
7. key_len
用于处理查询的索引长度，如果是单列索引，那就是整个索引长度，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少列的索引，这里就会计算出去，没有使用到的列不会计算进去。留意下整个列的值，算一下你的多列索引使用长度就知道有没有使用到所有的列。另外 key_len 只计算where 条件用到的索引长度，而 排序和分组就算用到了索引，也不会计算到 key_len 中。
具体的长度计算逻辑在《MySQL是怎样运行的》中有具体示例，如有需要可以看书。
8. ref
当访问方法时 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery 中的其中一个时，ref 列展示的就是与索引列进行等值匹配的东西，比如只是一常数或者某一列
-- 如下查询：以 t1 表作为驱动表，查询条件是 const 级别， 以 t2 表作为被驱动表，对应的 ref列是 demo.t1.id
mysql> explain select * from t1 inner join t2 on t1.id = t2.id where t1.a = 'a';
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | PRIMARY,idx_a | idx_a | 5 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.t1.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
2 rows in set (0.05 sec)
需要注意的是有时与索引列做匹配的是一个函数，此时 ref 列展示的是 func，如下：
mysql> explain select * from t1 inner join t2 on t1.id = upper(t2.id);
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL |
| 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set (0.05 sec)
9. rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数。
10. filtered
5.7 版本之前，使用 explain extended 命令才会出现此字段，5.7 版本之后默认有此字段，这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，这里是百分比，并非具体记录数。在单表查询中这一列没有什么意义，更应该在连接查询中关注驱动表对应的执行计划的 filtered 值。
11. extra 列
-
No table used : 不带 from字句的查询或者 from dual 查询, 如下：
mysql> explain select 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 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 | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set (0.05 sec) -
Impossible WHERE : 查询语句的 where 字句永远为 false，如下：
mysql> explain select MAX(id) from t1 where 1 != 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 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 row in set (0.05 sec) -
No matching min/max row : 当查询列表有 MIN 或者 MAX 函数，但是并没有记录符合 WHERE 字句中的搜索条件，如下：
mysql> explain select MAX(id) from t1 where a = 'asda'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | 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 | No matching min/max row | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ 1 row in set (0.04 sec) -
Using index : 不需要回表，直接通过索引查询 (覆盖索引) ，如下：
mysql> explain select a from t1; +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | index | NULL | idx_a | 5 | NULL | 100 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ 1 row in set (0.05 sec) -
Using index condition : 有些搜索条件中虽然出现了索引列，但是不能充当边界条件来形成扫描区间，即不能用来减少需要扫描的记录数量，将会提示该额外信息; 出现了该信息提示，也可以说明该查询使用了索引下推 （Index Condition Pushdown ICP）。如下：
mysql> explain select * from t1 where a > '1'; +----+-------------+----------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t1 | NULL | range | a | a | 5 | NULL | 1 | 100.00 | Using index condition | +----+-------------+----------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+ 1 row in set (0.05 sec) -
Using where : 当某个查询条件需要在 server 层进行判断时，会提示该信息。如下：这里虽然 a 列是索引，但是MySQL 判断全表扫描效率更高，因此没有使用索引（key 为 null）；全表扫描后将数据返回给 Server 层，在 Server 层过滤 a > ‘a’ 并且 c = ‘c’ 的条件.
mysql> explain select * from t1 where a > 'a' and c = 'c'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | idx_a | NULL | NULL | NULL | 100 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set (0.04 sec) -
Using join buffer(Block Nested Loop) : 在连接查询的执行过程中，当被驱动表不能有效利用索引加快访问速度时，MySQL一般会为其分配一块名为连接缓冲区（Join Buffer）的内存卡来加快查询速度；也就是使用基于块的嵌套循环算法来执行连接查询。如下：
sql> explain select * from t1 inner join t2 on t1.c = t2.c; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 10.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 2 rows in set (0.06 sec)在 Extra 中显示了两个提示：
- Using join buffer (Block Nested Loop) : 因为对于 t2 表的访问不能有效利用索引，所以只能退而求其次，使用 Join Buffer 来减少 t2 表的访问次数，提高性能。
- Using where : 查询语句中的 t2 on t1.c = t2.c条件，因此 t1 表是驱动表，t2 表是被驱动表，所以在访问 t2 表的时候 t1.c 的值已经确定下来了，所以提示了 Using where 信息。
-
Using intersect(…)、Using union(…)、Using sort_union(…) : 说明查询使用了 Intersect、Union 或 Sort-Union 索引合并。
-
Zero limit ：当 Limit 参数为0时，表示不打算从表中读取出任何记录。
mysql> explain select * from t1 limit 0; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ | 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 | Zero limit | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ 1 row in set (0.04 sec) -
Using filesort : 在有些情况下， 对结果集总的记录进行排序时，是可以使用到索引的。但是很多情况下排序操作是无法使用到索引的，只能在内存或磁盘中进行排序，而这种在内存或磁盘中进行排序的方式称为文件排序（filesort）。如果某个查询使用到了文件排序，则会显示该提示，如下：
mysql> explain select * from t1 order by c; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set (0.04 sec) -
Using temporary ：在许多查询的执行过程中，MySQL会借助临时表来完成一些去重、排序等功能。如果不能有效利用索引来完成查询，MySQL很有可能通过建立内部临时表执行查询。如果查询中使用了内部临时表则会展示该信息。
mysql> explain select distinct(c) from t1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set (0.04 sec) -
Loosescan : 在将 IN 子查询转为半连接时，如果采用的是 LooseScan 执行策略，则驱动表的 Extra 列会显示该提示。
-
Firstmatch ：在将 IN 子查询转为半连接时，如果采用的是 FirstMatch 执行策略，则被驱动表的 Extra 列会显示该提示。
-
NULL : 查询的列未被索引覆盖，并且where 筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过 “回表” 来实现，不是纯粹地用到了索引，也不是完全没用到索引。（需要回表操作完成的查询）,如下：
mysql> explain select * from t1 where a = 'a'; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ref | idx_a | idx_a | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
四、补充
1. EXPLAIN 扩展
1.1 Extend EXPLAIN
在使用 Explain 语句查看 SQL 的执行计划后，紧接着还可以使用 show warnings 语句来查看与这个查询计划有关的扩展信息, 如下：
mysql> explain select * from t1 where a = 'a' and c = 'c' ;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_a | idx_a | 5 | const | 1 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set (0.04 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `demo`.`t1`.`id` AS `id`,`demo`.`t1`.`a` AS `a`,`demo`.`t1`.`b` AS `b`,`demo`.`t1`.`c` AS `c` from `demo`.`t1` where ((`demo`.`t1`.`a` = 'a') and (`demo`.`t1`.`c` = 'c')) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
show warnings 展示了三个字段：Level、Code、 Message。其中当 Code =1003 时，Message 字段展示的信息类似于查询优化器将查询语句重写后的语句。
1.2 JSON 格式的执行计划
可以在 Explain 命令后追加 format=json 命令以得到一个 JSON 格式的执行计划，里面除了常规的信息外海包含该计划花费的成本。
mysql> explain format=json select * from t1 where a = 'a' and c = 'c';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1, # 整个查询语句只有一个 Select 关键字，该关键字对应的id号为 1
"cost_info": {
"query_cost": "1.20" # 整个查询语句预计执行成本
},
"table": {
"table_name": "t1",
"access_type": "ref",
"possible_keys": [
"idx_a"
],
"key": "idx_a",
"used_key_parts": [
"a"
],
"key_length": "5",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 0,
"filtered": "10.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.02",
"prefix_cost": "1.20",
"data_read_per_join": "78"
},
# 执行查询中设计的列
"used_columns": [
"id",
"a",
"b",
"c"
],
# 针对单表的查询条件
"attached_condition": "(`demo`.`t1`.`c` = 'c')"
}
}
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)
2. Intersection、Union、Sort-Union 索引合并
一般情况下 MySQL 只会为单个索引生成扫描空间，但是特殊情况下MySQL 也会为多个索引生成扫描区间。
2.1 Intersection
select from t1 where k1='a' and k2 = 'b'
其中 k1，k2 都是索引，此处存在三种方案：
- 扫描 k1 辅助索引，回表查询 k2 = ‘b’ 的记录
- 扫描 k2 辅助索引，回表查询 k1 = 'a’的记录
- 同时扫描 k1 和 k2 的辅助索引，然后从二者的操作结果中找出 id 列值相同的记录。此方案，即为 Intersection (交集)索引合并
如果要使用 Intersection (交集)索引合并 ，并且每个使用到的索引都是二级索引，那么则要求从每个索引中获取到的二级索引记录都是按照主键值排序的。比如上面的sql， 查询 k1 = ‘a’ 的辅助索引记录，因为其辅助索引列都是 a，又因为在索引列相同时按照主键排序，所以这里查询出来的二级索引记录是按照主键排序的，k2 = ‘b’ 同理，因此 上述SQL 可以使用 Intersection索引合并。
为什么需要主键有序？
- 从两个有序集合中取交集更为容易
- 如果获取到的id 值是有序的，则回表时就不是随机 IO了（相当于实现了MMR），从而提高效率。
2.2 Union
基本与 Intersection (交集)索引合并 类似，只不过这里取的是并集。
如：select from t1 where k1='a' or k2 = 'b'
2.3 Sort-Union
Union 需要保证扫描出来的二级索引的主键值是有序的，而 Sort-Union 则会将从各个索引中扫描到的记录的主键值进行排序，在按照 Union 索引合并的方式执行查询。相较于 Union， Sort-Union 多了一步将查询出来的记录按照主键排序的过程。
需要注意的是并不存在 Sort-Intersection 索引合并，在《MySQL是怎样运行的》一书中说是 Sort-Intersection 索引合并 查询出的记录太多，导致回表代价太大。
3. 基于块的嵌套循环连接
在连接查询过程中，分为驱动表和被驱动表，驱动表只需要访问一次，被驱动表可能需要访问多次。两表连接查询的过程并不是将所有满足条件的驱动表记录先查询出来放到一个地方然后再去被驱动表中查询（因为可能驱动表记录太大，无法存储），而是每获取到一条驱动表记录，就立即到被驱动表中寻找匹配的记录，如果得到了匹配的记录，就把组合后的记录发送给客户端，然后再到驱动表中获取下一条记录，这个过程将重复进行。
但上述这种方案需要频繁访问 被驱动表，因此InnoDB 存在一个 Join Buffer (连接缓冲区) 的概念，Join Buffer 在执行连接查询前申请的一块大小固定的内存。先把若干条驱动表结果集中的记录记录在 Join Buffer 中，然后开始扫描被驱动表，每一条被动表的记录一次性与 Join Buffer 中的多条驱动表记录进行匹配。由于匹配的过程是在内存中完成的，因此可以显著减少被驱动表的 IO 代价。此为基于块的嵌套循环连接（Block Nested-Loop Join）算法。
需要注意的是 : Join Buffer 中并不会存放驱动表记录的所有列，只有查询列表中的列和过滤条件中的列才会被放到 Join Buffer 中。
Join Buffer 示意图如下：
五、参考内容
书籍：《MySQL是怎样运行的——从根儿上理解MySQL》、《MySQL技术内幕 InnoDB存储引擎 》
全技术栈企业级性能调优万花筒
一张图彻底搞懂MySQL的 explain
MySQL优化—工欲善其事，必先利其器之EXPLAIN*
https://coding.imooc.com/class/442.html
https://segmentfault.com/a/1190000021458117?utm_source=tag-newest
https://www.cnblogs.com/tufujie/p/9413852.html
https://blog.csdn.net/qq_27399407/article/details/93741225
https://smartan123.github.io/book/?file=home-%E9%A6%96%E9%A1%B5
https://www.cnblogs.com/zhanjindong/archive/2013/11/23/3439042.html
如有侵扰，联系删除。 内容仅用于自我记录学习使用。如有错误，欢迎指正
扫一扫
775
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
