7.1 查询性能那些事

7.1.1 查看系统性能参数

在 MySQL 中，可以使用 SHOW STATUS语句查询一些数据库服务器的性能参数、执行频率：

SHOW [GLOBAL | SESSION] STATUS LIKE '参数';

一些常用的性能参数如下：

• Connections：连接 MySQL 服务器的次数。
• Uptime：MySQL 服务器的上线时间。
• Slow_queries：慢查询的次数。
• Innodb_rows_read：Select 查询返回的行数。
• Innodb_rows_inserted：执行 INSERT 操作插入的行数。
• Innodb_rows_updated：执行 UPDATE 操作更新的行数。
• Innodb_rows_deleted：执行 DELETE 操作删除的行数。
• Com_select：查询操作的次数。
• Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
• Com_update：更新操作的次数。
• Com_delete：删除操作的次数。

查询 MySQL 服务器的连接次数，可以执行语句：

SHOW STATUS LIKE 'Connrctions';

查询服务器的工作时间，可以执行语句：

SHOW STATUS LIKE 'Uptime';

查询慢查询次数，可以执行语句：

SHOW STATUS LIKE 'Slow_queries';

慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。

7.1.2 统计 SQL 的查询成本

一条 SQL 查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MySQL 会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。

如果我们想要查看某条 SQL 语句的查询成本，可以在执行完这条 SQL 语句之后，通过查看当前会话中的 last_query_cost 变量值来得到当前查询的成本。它通常也是评价一个查询的执行效率的一个常用指标。这个查询成本对应的是 SQL 语句所需要读取的页的数量。

仍然以 student_info 表为例，如果想要查询 id = 900001 的记录，然后再查询一下此语句的查询成本：

SELECT student_id, class_id, name, create_time FROM student_info WHERE id = 900001;

# 查询上面语句的查询成本
SHOW STATUS LIKE 'last_query_cost';

由结果可知，此语句只需要检索一个页即可。

再查询 id 在 900001 到 900100 之间的记录：

SELECT student_id, class_id, name, create_time FROM student_info WHERE id BETWEEN 900001 AND 900100;

# 查询上面语句的查询成本
SHOW STATUS LIKE 'last_query_cost';

由结果可知，此语句大概需要进行 20 个页的查询。

虽然两个语句查询页的数量不一样，但是查询的效率并没有明显的变化。实际上，这两个 SQL 语句的查询时间基本一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量（last_query_cost）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。

SQL 查询是一个动态的过程，从页加载的角度看，可以得到两点结论：

1. 位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
2. 批量决定效率。如果我们从磁盘中对单一页进行随机读取，那么效率是很低的，而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

所以，遇到 I/O 并不用担心，首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中；其次可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

7.1.3 定位执行慢的 SQL：慢查询日志

MySQL 的慢查询日志用来记录在 MySQL 中响应时间超过阈值的语句。具体是指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。long_query_time 的默认值是 0，也就是指运行 10 秒以上（不含 10 秒）的语句，则认为是超出了我们的最大忍耐时间值。

它的主要作用是，帮助我们发现那些执行时间特别长的 SQL 查询，并且有针对性地进行优化，从而提高系统的整体效率。当数据库服务器发生阻塞、运行慢的时候，检查一下慢查询日志，找到那些慢查询，会对解决问题很有帮助。

默认情况下，MySQL 数据库没有开启慢查询日志，需要手动来设置这个参数。因为开启慢查询日志或多或少会带来一定的性能影响，所以如果不是调优的话，一般不建议启动该参数。

01、开启慢查询日志参数

• 开启 slow_query_log

  SET GLOBAL slow_query_log = 'ON';
  # 查看慢查询日志
  SHOW VARIABLES LIKE '%slow_query_log%';
  

  

• 修改 long_query_time

  SHOW VARIABLES LIKE '%long_query_time%';
  

  

  # 这里设置的是session级别
  # 设置为 global 级别时，对当前 session 失效
  SET long_query_time = 1;
  SHOW VARIABLES LIKE '%long_query_time%';
  

  
  这里需要注意，global 方式对当前 session 失效，对新连接的客户端有效。不设置 GLOBAL 关键字就默认是 session 级别。

  补充：也可以在配置文件中设置参数，这种方式是永久设置的方式。

02、关闭慢查询日志

MySQL 服务器停止慢查询日志功能有两种方法：永久性关闭、临时性关闭。

• 永久性方式

  修改 my.cnf 或 my.ini 文件，把 [mysqld] 组下的 slow_query_log 值设置为 OFF，修改保存后再重启 MySQL 服务即可生效：

  [mysqld]
  slow_query_log=OFF
  

  或者，把 slow_query_log 注释掉或删除：

  [mysqld]
  #slow_query_log=OFF
  

• 临时性方式

  使用 SET 语句来设置：

  SET GLOBAL slow_query_log = 'OFF';
  

03、删除慢查询日志

使用 SHOW 语句查询日志信息，其中包含了慢查询日志的默认目录，在该目录下手动删除查询日志文件即可。

删除后使用命令 mysqladmin flush-logs 来重新生成查询日志文件，会在数据目录下重新生成慢查询日志文件。

需要注意的是，使用命令来删除重建时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，必须事先做好备份。

7.1.4 查看 SQL 执行成本：SHOW PROFILE

show profile 是 MySQL 提供的可以用来分析当前会话中 SQL 都做了什么、执行的资源消耗情况的工具，可以用于 SQL 调优的测量。默认情况下处于关闭状态，并保存最近 15 次的运行结果。

通过设置 profiling = ‘ON’ 来开启 show profile：

SET SESSION profiling = 'ON';

# 查看当前会话都有哪些 profiles
SHOW PROFILES;

# 查看当前会话最近一次查询的开销
SHOW PROFILE;

show profile 的常用查询参数如下：

• ① ALL：显示所有的开销信息。
• ② BLOCK IO：显示块 IO 开销。
• ③ CONTEXT SWITCHES：上下文切换开销。
• ④ CPU：显示 CPU 开销信息。
• ⑤ IPC：显示发送和接收开销信息。
• ⑥ MEMORY：显示内存开销信息。
• ⑦ PAGE FAULTS：显示页面错误开销信息。
• ⑧ SOURCE：显示和 Source_function，Source_file，Source_line 相关的开销信息。
• ⑨ SWAPS：显示交换次数开销信息。

日常开发需要注意的结论：

• ① converting HEAP to MyISAM：查询结果太大， 内存不够，数据往磁盘上搬了。
• ② Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
• ③ Copying to tmp table on disk：把内存中临时表复制到磁盘上，需警惕。
• ④ locked。

如果在 show profile 诊断结果中出现了以上 4 条结果中的任何一条，则 SQL 语句需要优化。

但是，show profile 命令将被弃用，可以从 information_schema 中的 profiling 数据表进行查看。

7.2 EXPLAIN 语句输出中各列详解

MySQL 查询优化器在基于成本和规则对一条查询语句进行优化后，会生成一个执行计划。这个执行计划展示了接下来执行查询的方式，比如多表连接的顺序是什么、采用什么访问方法来具体查询每个表等。设计 MySQL 的大叔贴心地提供了 EXPLAIN 语句，可以让我们查看某个查询语句地具体执行计划。 ——摘自《MySQL 是怎样运行的》

在这个 EXPLAIN 输出的结果中，我们能看到什么？

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些所以可以使用
• 哪些所以被实际使用
• 表之间是怎么引用的
• 每张表有多少行被优化器查询
• …

执行计划的基本语法：

EXPLAIN SELECT select_options;
# 或
DESCRIBE SELECT select_options;

输出各个列的作用如下：

列名	描述
id	在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id
select_type	SELECT 关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

注：下面的例子中已经提前创建了两张表：s1 和 s2，两张表里分别插入了 1w 条记录。

## 创建表
CREATE TABLE s1 (
	id INT AUTO_INCREMENT,
	key1 VARCHAR ( 100 ),
	key2 INT,
	key3 VARCHAR ( 100 ),
	key_part1 VARCHAR ( 100 ),
	key_part2 VARCHAR ( 100 ),
	key_part3 VARCHAR ( 100 ),
	common_field VARCHAR ( 100 ),
	PRIMARY KEY ( id ),
	INDEX idx_key1 ( key1 ),
	UNIQUE INDEX idx_key2 ( key2 ),
	INDEX idx_key3 ( key3 ),
	INDEX idx_key_part ( key_part1, key_part2, key_part3 ) 
) ENGINE = INNODB CHARSET = utf8;

CREATE TABLE s2 (
	id INT AUTO_INCREMENT,
	key1 VARCHAR ( 100 ),
	key2 INT,
	key3 VARCHAR ( 100 ),
	key_part1 VARCHAR ( 100 ),
	key_part2 VARCHAR ( 100 ),
	key_part3 VARCHAR ( 100 ),
	common_field VARCHAR ( 100 ),
	PRIMARY KEY ( id ),
	INDEX idx_key1 ( key1 ),
	UNIQUE INDEX idx_key2 ( key2 ),
	INDEX idx_key3 ( key3 ),
	INDEX idx_key_part ( key_part1, key_part2, key_part3 ) 
) ENGINE = INNODB CHARSET = utf8;

7.2.1 table

无论查询的语句有多复杂，里面包含了多少张表，到最后都是对每张表进行单表访问的。所以 MySQL 规定 EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，该条记录的 table 列代表着该表的表名。

EXPLAIN SELECT * FROM s1;

# s1:驱动表  s2:被驱动表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

7.2.2 id

一般情况下，查询语句中每出现一个 SELECT 关键字，就会为它分配一个 id，这个 id 值就是 EXPLAIN 输出的第一列。下面分几种情况讨论。

01、简单查询

简单查询语句，只包含一个 SELECT 关键字的查询：

EXPLAIN SELECT * FROM s1 WHERE key1 = 'aBJBMi';

02、连接查询

# 连接查询
EXPLAIN SELECT
	* 
FROM
	s1
	INNER JOIN s2 ON s1.key1 = s2.key1 
WHERE
	s1.common_field = 'aBJBMi';

在连接查询中，s1 和 s2 表分别对应一条记录，但是这两条记录对应的 id 值都是 1。这里需要知道的是：在连接查询的执行计划中，每个表都会对应一条记录，这些记录的 id 列都是相同的。出现在前面的表表示驱动表，出现在后面的表表示被驱动表。而决定谁作为驱动表，谁作为被驱动表是由优化器决定的，而并非开发。

03、包含子查询

# 包含子查询
EXPLAIN SELECT
	* 
FROM
	s1 
WHERE
	key1 IN ( SELECT key1 FROM s2 ) 
	OR key3 = 'a';

从查询结果可以看出，s1 表在外层查询中，外层查询有一个独立的 SELECT 关键字，所以第一条记录的 id 值就是 1；s2 表在子查询中，子查询有一个独立的 SELECT 关键字，所以第二条记录的 id 值就是 2。

但是这里需要特别注意的是：查询优化器可能对设计子查询的查询语句进行重写，从而转换为连接查询。两者的查询效率可以对比出来，优化器必定会选择执行效率高的方式执行。

当然，如果想知道查询优化器对某个子查询语句是否进行了重写，可以查看执行计划，比如：

######查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作########
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');

两条记录的 id 值都为 1，说明优化器将子查询转换为了连接查询。

04、包含 union 子句

# Union 去重
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

这里 id 为 3 的记录表示是一个临时的结果（按照书上介绍的，此处 id 应该为 null），为了合并两个查询的结果集而创建的。

# Union ALL无需去重
EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;

这里需要注意的是， UNION ALL 不需要对最终的结果集进行去重，它只是单纯地把多个查询结果集中的记录合并成一个并返回，所以无需用到临时表，所以在包含 UNION ALL 子句的查询的执行计划中，没有那个 id 为 null 的记录。

小贴士：MySQL 5.6 及之前的版本中，执行 UNION ALL 语句可能也会用到临时表。MySQL 8.0 之后 UNION 会用到临时表，UNION ALL 不会用到临时表。

05、总结

1. id 如果相同，可以认为是一组，自上往下顺序执行。
2. 在所有组中，id 值越大，优先级越高，越先执行。
3. id 的每个值都代表一趟独立的查询，一个 sql 的查询趟数越少越好。

7.2.3 select_type

由前文可知，一条大的查询语句里可以包含若干个 SELECT 关键字，每个 SELECT 关键字代表着一个小的查询语句。而每个 SELECT 语句的 FROM 子句中都可以包含若干张表（这些表用来进行连接查询），每张表都对应着执行计划输出中的一条记录。设计 MySQL 的大叔为每个 SELECT 关键字代表的小查询都定义了一个名为 select_type 的属性。只要知道了某个小查询的 select_type 属性，也就知道了这个小查询在整个大查询中扮演一个什么角色。

select_type 表示：select 关键字对应的那个查询的类型，确定小查询在大查询中扮演了一个什么角色。

select_type 的值如下：

名称	描述
SIMPLE	Simple SELECT (not using UNION or subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
UNION RESULT	Result of a UNION
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
DERIVED	Derived table
MATERIALIZED	Materialized subquery
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery(see UNCACHEABLE SUBQUERY)

01、SIPMLE

查询语句中不包含 UNION 或子查询的查询都算作 SIMPLE 类型。连接查询的 select_type 值也是 SIMPLE。

# 查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型
EXPLAIN SELECT * FROM s1;

# 连接查询也算是`SIMPLE`类型
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

02、PRIMARY

对于包含 UNION、UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边那个查询的 select_type 值就是 PRIMARY。

# 对于包含`UNION`或者`UNION ALL`或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的`select_type`值就是`PRIMARY`
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

03、UNION

对于包含 UNION、UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询外，其余小查询的 select_type 值就是 UNION。

# 对于包含`UNION`或者`UNION ALL`的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的`select_type`值就是`UNION`
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

04、UNION RESULT

MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT。

# `MySQL`选择使用临时表来完成`UNION`查询的去重工作，针对该临时表的查询的`select_type`就是`UNION RESULT`
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

05、SUBQUERY

如果包含子查询的查询语句不能够转为对应的半连接形式，并且该子查询是不相关子查询，而且优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 SUBQUERY。

相关子查询：在子查询中使用到了外表。
物化策略：简单来说，就是通过生成子查询结果作为临时表，通常在内存中，实现加速查询执行。具体介绍可参考文章：使用物化策略优化子查询。

# 子查询：
# 如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是不相关子查询。该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`SUBQUERY`
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

06、DEPENDENT SUBQUERY

如果包含子查询的查询语句不能够转为对应的半连接形式，并且该子查询被查询优化器转换为相关子查询的形式，则该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 DEPENDENT SUBQUERY。

# 如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是相关子查询，则该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`DEPENDENT SUBQUERY`
EXPLAIN SELECT * FROM s1 
WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';

# 注意的是，select_type为`DEPENDENT SUBQUERY`的查询可能会被执行多次。

这里需要注意的是，select_type 为 `DEPENDENT SUBQUERY` 的查询可能会被执行多次。

07、DEPENDENT UNION

在包含 UNION 或 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询，则除了最左边的那个小查询外，其余小查询的 select_type 的值就是 DEPENDENT UNION。

# 在包含`UNION`或者`UNION ALL`的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的`select_type`的值就是`DEPENDENT UNION`。
EXPLAIN SELECT * FROM s1 
WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');

08、DERIVED

在包含派生表的查询中，如果是以物化派生表的方式执行查询，则派生表对应的子查询的 select_type 就是 DERIVED。

# 对于包含`派生表`的查询，该派生表对应的子查询的`select_type`就是`DERIVED`
EXPLAIN SELECT * 
FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;

id 为 2 的记录就代表子查询的执行方式，它的 select_type 是 DERIVED，说明该子查询是以物化的方式执行的。

派生表：把查询结果作为一个表。

09、MATERIALLZED

当查询优化器在执行包含子查询的语句时，选择将子查询物化后与外层查询进行连接查询（将结果集物化后供外层查询使用），该子查询对应的 select_type 属性就是 MATERIALLZED（物化视图）。

# 当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的`select_type`属性就是`MATERIALIZED`
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); # 子查询被转为了物化表

SUBQUERY（子查询）和MATERIALLZED（物化视图）在数据库操作和查询优化中各自扮演着不同的角色，它们主要区别在于：

• SUBQUERY 是一种查询技术，用于在 SQL 语句中嵌套其他查询以过滤或计算数据；而 MATERIALLZED 是一种数据库对象，用于存储查询结果或数据的预计算汇总。
• SUBQUERY 的主要作用是简化 SQL 语句和优化查询性能；而 MATERIALLZED 的主要作用是提高复杂查询的性能和加速对历史数据的访问。

select_type 的值具体会是什么，都是由优化器选择决定的，我们了解即可。

7.2.4 partitions

代表分区表中的命中情况，非分区表，该值为 NULL。一般情况下查询语句的执行计划里 partitions 列的值都是 NULL。

7.2.5 ⭐type

执行计划的一条记录代表着 MySQL 对某个表执行查询时的 "访问方法" 或 "访问类型"。同一个查询语句可以使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是不同的执行方式花费的时间成本可能差距非常大。

其中的 type 列就表明了这个访问方法是什么。访问方法的效率：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 const 级别。

01、system

当表中只有一条记录并且该表使用的存储引擎（比如 MyISAM、MEMORY）的统计数据是精确的，那么对该表的访问方法就是 system。

# 当表中`只有一条记录`并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是`system`。
CREATE TABLE t ( i INT ) ENGINE = MYISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;

# 换成InnoDB
CREATE TABLE tt ( i INT ) ENGINE = INNODB;
INSERT INTO tt VALUES(1);
EXPLAIN SELECT * FROM tt;

MyISAM 中有一个变量专门用来统计记录行数（维护行记录数）。
InnoDB 需要将数据页加载到缓冲区后再全表扫描、统计。

关键点：只有一条数据，且存储引擎是 MyISAM 或 MEMORY。

02、const

通过主键或者唯一二级索引列来定位一条记录的访问方法定义为 const（意思时常数级别的，代价是可以忽略不计的）。

这里需要注意：如果主键或者唯一二级索引的索引列由多个列构成，那么只有再索引列中的每一个列都与常数进行等值比较时，这个 const 访问方法才有效。

# 当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是`const`
EXPLAIN SELECT * FROM s1 WHERE id = 10005;

# 根据二级索引列等值匹配
EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

关键点：主键、唯一二级索引列与常数等值比较。

03、eq_ref

在连接查询时，如果被驱动表是通过主键或者不允许存储 NULL 值的唯一二级索引列等值匹配的方式进行访问的，则该被驱动表的访问方法就是 eq_ref。

注意：如果该主键或者不允许存储 NULL 值的唯一二级索引是联合索引，则所有的索引列都必须进行等值比较。

# 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是`eq_ref`
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

关键点：连接查询，且主键或非空的二级索引等值匹配。

04、ref

通过普通的二级索引列与常量进行等值匹配的方式来查询某个表时，对该表的访问方法就可能是 ref。

搜索条件为二级索引列与常数进行等值比较，形成的扫描区间为单点扫描区间，采用二级索引来执行查询。

# 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref`
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

注意点：在查询时，如果赋值类型与字段类型不一致时，内部会进行隐式转换，从而导致索引失效。

# key3的类型是varchar类型，但我们赋值时给的是int，内部会进行隐式转换，索引失效
EXPLAIN SELECT * FROM s1 WHERE key3 = 10066; -- ALL
EXPLAIN SELECT * FROM s1 WHERE key3 = '10066'; -- ref

关键点：普通二级索引与常数等值比较。

05、fulltext

指的是全文索引。

06、ref_or_null

对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值，那么对该表的访问方法就可能是 ref_or_null。

注意点：ref_or_null 访问方法只是比 ref 多扫描了一些值为 NULL 的二级索引记录，值为 NULL 的记录会被放在非聚簇索引的最左边。

# 当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法就可能是`ref_or_null`
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

关键点：对可以为 NULL 的普通二级索引等值查询。

07、index_merge

一般情况下对单个表的查询只能使用到一个索引，但是单表访问方法时在某些场景下可以使用 Interseation、Union、Sort-Union 三种索引合并的方式来执行查询。

# 单表访问方法时在某些场景下可以使用`Intersection`、`Union`、`Sort-Union`这三种索引合并的方式来执行查询
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

索引合并：使用多个索引来完成一次查询的执行方法称为 index merge。具体的索引合并方法有三种：

1. Intersection 索引合并

   对从不同索引中扫描到的记录的 id 取交集，只为这些 id 值执行回表操作。如果使用 Intersection 索引合并的方式执行查询，并且每个使用到的索引都是二级索引的话，则要求从每个索引中获取到的二级索引记录都是按照主键值排序的。

2. Union 索引合并

   对从不同索引中扫描到的记录的 id 值取并集，为这些 id 值执行回表操作。如果使用 Union 索引合并的方式执行查询，并且每个使用到的索引都是二级索引的话，则要求从每个索引中获取到的二级索引都是按照主键值排序的。

3. Sort-Union 索引合并

   先将从各个索引中扫描到的记录的主键值进行排序，再按照执行 Union 索引合并的方式执行查询的方式称为 Sort-Union 索引合并。Union 索引合并的使用条件太苛刻，它必须保证从各个索引中扫描到的记录的主键值都是有序的。

08、unique_subquery

类似于两表连接中被驱动表的 eq_ref 方法，unique_subquery 针对的是一些包含 IN 子查询的查询语句。如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询在转换之后可以使用主键或者不允许存储 NULL 值的唯一二级索引进行等值匹配，那么该子查询执行计划的 type 列的值就是 unique_subquery。

# `unique_subquery`是针对在一些包含`IN`子查询的查询语句中，如果查询优化器决定将`IN`子查询转换为`EXISTS`子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的`type`列的值就是`unique_subquery`
EXPLAIN SELECT
	* 
FROM
	s1 
WHERE
	common_field IN ( SELECT id FROM s2 WHERE s1.common_field = s2.common_field ) 
	OR key3 = 'a';

说明在执行子查询时会使用到 id 列的聚簇索引。

关键点：执行子查询可以使用主键或不为空的唯一二级索引等值匹配。

09、index_subquery

index_subquery 与 unique_subquery 类似，不过在访问子查询时使用的是普通索引。

# index_subquery 与 unique_subquery 类似，不过在访问子查询时使用的是普通索引	
EXPLAIN SELECT
	* 
FROM
	s1 
WHERE
	common_field IN ( SELECT key1 FROM s2 WHERE s1.common_field = s2.common_field ) 
	OR key3 = 'a';

关键点：执行子查询可以使用普通索引等值匹配。

10、range

如果使用索引获取 "范围区间" 或若干个单点 "扫描区间" 的记录，那么可能使用到 range 访问方法。

注意点：一个单点扫描区间，(-∞，+∞) 都不能称为 range。

# 如果使用索引获取某些`范围区间`的记录，那么就可能使用到`range`访问方法
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

# 同上
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

关键点：“范围区间” 或若干个单点 “扫描区间”。

11、index

当可以使用覆盖索引，但需要扫描全部的所有记录时，该表的访问方法就是 index。

覆盖索引就是指：不用回表便可找到要查询的数据。

# 当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index，key_part2,key_part3 是联合索引idx_key_part中的字段
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'; -- index
EXPLAIN SELECT key1,key_part2 FROM s1 WHERE key_part3 = 'a'; -- ALL

小贴士：对于使用 InnoDB 存储引擎的表来说，二级索引叶子节点的记录只包含索引列和主键列的值，而聚簇索引叶子节点中包含了用户自定义的全部列以及一些隐藏列。索引扫描全部二级索引记录的代价比扫描全部聚簇索引记录的代价更低一些。

对于 InnoDB 存储引擎来说，需要执行全部扫描、并且需要对主键进行排序时，此时的 type 列的值也是 index。

# 对于InnoDB存储引擎来说，需要执行全部扫描、并且需要对主键进行排序时，此时的type列的值也是index
EXPLAIN SELECT * FROM s1 ORDER BY id;

12、ALL

ALL 代表全表扫描。

# 最熟悉的全表扫描
EXPLAIN SELECT * FROM s1;

7.2.6 possible_keys 和 key

possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些；key 列表示实际用到的索引有哪些，如果为 NULL，则表示没有使用索引

一般查询涉及到的字段上如果存在索引，则该索引将被列出，但不一定被查询使用。

# 6. possible_keys和key：可能用到的索引 和  实际上使用的索引
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

7.2.7 key_len

key_len 表示实际用到的索引长度（字节数），以帮忙检查是否充分利用上了索引，值越大越好。该值由三部分组成：

1. 该列的实际数据最多占用的存储空间长度。
2. 如果该列可以存储 NULL 值，则 key_len 值在该列的实际数据最多占用的存储空间长度的基础上再加 1 字节。
3. 对于使用变长类型的列来说，都会有 2 字节的空间来存储该变列的实际数据占用的存储空间长度，key_len 值还要在原先的基础上加 2 字节。

# key_len：实际使用到的索引长度(即：字节数)
# 1. 该列的实际数据最多占用的存储空间长度
EXPLAIN SELECT * FROM s1 WHERE id = 10005; -- 主键唯一索引，int类型占用 4 字节

# 2. 如果该列可以存储NULL值，则key_len值在该列的实际数据最大占用的存储空间长度的基础上再加1字节
EXPLAIN SELECT * FROM s1 WHERE key2 = 10126; -- 5, key2是int类型，但是可能非空，所以4+1=5

# utf8中一个字符占3个字节（100*3=300），key1也可能是空的所以需要加1，另外，key1是可变长的，需要有两个字节来存储该变列的实际数据占用的存储空间长度所以需要加2字节
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; -- 303 
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b'; -- 606
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'; -- 909

key_len 长度计算公式：

1. varchar(10) 变长字段且允许 NULL = 10 * ( character set：utf8 = 3，gbk = 2，latin1 = 1) + 1(NULL) + 2(变长字段)
2. varchar(10) 变长字段且不允许 NULL = 10 * ( character set：utf8 = 3，gbk = 2，latin1 = 1) + 2(变长字段)
3. char(10) 固定字段且允许 NULL = 10 * ( character set：utf8 = 3，gbk = 2，latin1 = 1) + 1(NULL)
4. char(10) 固定字段且不允许 NULL = 10 * ( character set：utf8 = 3，gbk = 2，latin1 = 1)

7.2.8 ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息。也就是在访问方法是 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery 其中之一时，ref 列展示的就是与索引列作等值匹配的结构是什么，比如只是一个常数或者是某个列。

# 8. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。比如只是一个常数或者是某个列。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

这里的 ref 值是 const，表明在使用 idx_key1 索引执行查询时，与 key1 列作等值匹配的对象是一个常数。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

这里的 ref 值是 study01.s1.id，表明针对被驱动表 s2 的访问方法是 eq_ref，在对 s2 表进行访问时，与 s2 表的 id 列进行等值匹配的对象就是 study01.s1.id 列（这里的 study01 是数据库名）。

# 使用了函数
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

7.2.9 rows

查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表该表的估计行数。如果使用索引来执行查询，执行计划的 rows 列就代表预计扫描的索引记录行数。

# 9. rows：预估的需要读取的记录条数，`值越小越好`
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

这里的值表示查询优化器在分析完使用 idx_key1 执行查询的成本后，满足条件的记录有 429 条。

7.2.10 filtered

某表经过过滤条件过滤后得到的记录数占预估读取记录数的百分比。

# 10. filtered: 某表经过过滤条件过滤后得到的记录数占预估读取记录数的百分比
# 如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';

这里的结果表示：查询优化器预测出这 429 条记录中有 10% 的记录满足条件。

# 对于单表查询来说，这个filtered列的值没什么意义，我们`更关注在连接查询中驱动表对应的执行计划记录的filtered值`，它决定了被驱动表要执行的次数(即：rows * filtered)
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

7.2.11 ⭐Extra

Extra 列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确地理解 MySQL 到底该如何执行给定的查询语句。其中，MySQL 提高的额外信息有好几十个，这里只挑一些常见的或者比较重要的额外信息来进行介绍。

01、No table used

当查询语句中没有 FROM 子句时将会提示该额外信息。

# 当查询语句的没有`FROM`子句时将会提示该额外信息
EXPLAIN SELECT 1;

02、Impossible WHERE

查询语句的 WHERE 子句永远为 FALSE 时（永远不成立）将会提示该额外信息。

# Impossible WHERE
# 查询语句的`WHERE`子句永远为`FALSE`时将会提示该额外信息
EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

03、No matching min/max row

当查询列表处有 MIN 或 MAX 聚合函数，但是并没有记录符合 WHERE 子句中的搜索条件时，将会提示该额外信息。

# No matching min/max row
# 当查询列表处有`MIN`或者`MAX`聚合函数，但是并没有符合`WHERE`子句中的搜索条件的记录时，将会提示该额外信息
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';

04、Using index

使用覆盖索引执行查询时，Extra 列表将会提示该额外信息。

# Using index
# 当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用覆盖索引的情况下，在`Extra`列将会提示该额外信息。
# 比方说下边这个查询中只需要用到`idx_key1`而不需要回表操作：
EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a';

05、Using index condition

有些搜索条件中虽然出现了索引列，但是却不能充当边界条件来形成扫描区间，也就是不能用来减少需要扫描的记录数量，将会提示该额外信息。简单来说就是：在查询语句的执行过程中虽然出现了索引列，但是却不能减少查询的次数，但是使用了索引条件下推特性，在 Extra 列中将会显示该额外信息。

# Using index condition
# 有些搜索条件中虽然出现了索引列，但却不能使用到索引
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'VxDwxYyyYw' AND key_part3 = 'gTauZMNDDh';

如上面例子所示，在没有索引条件下推特性之前，查询步骤是这样的：

1. server 层首先调用存储引擎的接口定位到满足 key1 > 'z' 条件的第一条二级索引记录。
2. 存储引擎根据 B+Tree 索引快速定位到这条二级索引记录后，根据该二级索引记录的主键值进行回表操作，将完整的用户记录返回给 server 层。
3. server 判断其他的搜索条件是否成立（比如这里的 key1 LIKE ‘%a’），如果成立则将其发送给客户端；否则就会跳过该记录，然后向存储引擎层要下一条记录。
4. 由于每条记录都有一个 next_record 属性，根据该属性可以快速定位到符合 key1 > ‘z’ 条件的下一条二级索引记录，然后再执行回表操作，将完整的用户记录返回给 server 层。然后重复步骤 3，直到将该索引 idx_key1 的扫描区间 (‘z’，+∞) 内的所有记录都扫描过为止。

这就引出一个问题：虽然 key1 LIKE ‘%a’ 不能用于充当边界条件来减少需要扫描的二级索引记录的数量，但是这个搜索条件也只涉及到 key1 列，而 key1 列是包含在索引 idx_key1中的，所以，尝试对上面的执行步骤进行了改进：

1. server 层首先调用存储引擎的接口定位到满足 key1 > 'z' 条件的第一条二级索引记录。
2. 存储引擎根据 B+Tree 索引快速定位到这条二级索引记录后，不着急执行回表操作，而是先判断一下所有关于 idx_key1 索引中包含的列的条件是否成立，也就是判断 key1 > ‘z’ AND key1 LIKE ‘%a’ 是否成立。如果这些条件不成立，则直接跳过该二级索引记录去找下一条二级索引记录；如果这些条件成立，则执行回表操作，将完整的用户记录返回给 server 层。
3. server 判断其他的搜索条件是否成立（这里就没有其他搜索条件了），如果成立则将其发送给客户端；否则就会跳过该记录，然后向存储引擎层要下一条记录。
4. 由于每条记录都有一个 next_record 属性，根据该属性可以快速定位到符合 key1 > ‘z’ 条件的下一条二级索引记录，还是不着急进行回表操作，先判断一下所有关于 idx_key1 索引中包含的列的条件是否成立。如果这些条件不成立，则直接跳过该二级索引记录，然后去找下一条二级索引记录。如果这些条件成立，则执行回表操作，将完整的用户记录返回给 server 层。然后重复步骤 3，直到将该索引 idx_key1 的扫描区间 (‘z’，+∞) 内的所有记录都扫描过为止。

每次执行回表操作时，都需要将一个聚簇索引页面加载到内存中，这比较耗时。所以，尽管上述两个方案只有一点点差别，但是可以省区很多回表操作的成本，这个改进就称为索引条件下推。

简单来说，索引条件下推和非索引条件下推有两点区别：

1. 非索引条件下推：先回表，再匹配其他搜索条件。也就是有几条记录就回表几次。
2. 索引条件下推：先匹配其他搜索条件，再回表。也就是尽可能地减少回表次数。

小贴士：索引条件下推是由 MySQL 服务器计算出执行成本后才决定是否使用该策略，而非开发控制的。

06、Using where

当某个搜索条件需要在 server 层进行判断时，在 Extra 列中会提示 Using where。

有两种情况：

1. 不使用索引，有 where 条件。
2. 使用索引，但 where 条件中有非索引字段的搜索条件。

# Using where
# 当我们使用全表扫描来执行对某个表的查询，并且该语句的`WHERE`子句中有针对该表的搜索条件时，在`Extra`列中会提示上述额外信息。
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';

对于聚簇索引来说，是用不到索引条件下推特性的（将过滤条件下推到索引层，从而减少回表次数）。这个例子中的 common_field = ‘a’ 条件是在 server 层进行判断的。

# 当使用索引访问来执行对某个表的查询，并且该语句的`WHERE`子句中有除了该索引包含的列之外的其他搜索条件时，在`Extra`列中也会提示上述额外信息。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';

该例子中使用到了二级索引 idx_key1，但是由于该索引并不包含 common_field 列。所以，存储引擎层要根据二级索引记录执行回表操作，并将完整的用户记录返回给 server 层之后，再在 server 层判断这个条件是否成立，所以 Extra 列的值是 Using where。

07、Using join buffer（Block Nested Loop）

在连接查询的执行过程中，当被驱动表不能有效地利用索引加快访问速度时，MySQL 一般会为其分配一块名为连接缓冲区的内存块来加快查询速度，也就是基于块的嵌套循环算法来执行连接查询。

# Using join buffer
# 在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫`join buffer`的内存块来加快查询速度，也就是基于块的嵌套循环算法来执行连接查询。
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

在针对 s2 表的执行计划中，Extra 列显示了两个提示：

1. Using join buffer (hash join)：因为对表 s2 的访问不能有效利用索引，只好退而求其次，使用 join buffer 来减少对 s2 表的访问次数，从而提高性能。
2. Using where：查询语句中有 s1.common_field = s2.common_field 条件，因为 s1 是驱动表，s2 是被驱动表，所以在访问 s2 表时，s1.common_field 的值已经确定好了。所以，实际上查询 s2 表的条件就是 “s2.common_field = 一个常数”，所以提示了 Using where。

08、Not exists

当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 NULL 值的，那么在该表的执行计划的 Extra 列就会提示 Not exists 额外信息。

# 当我们使用左（外）连接时，如果`WHERE`子句中包含要求被驱动表的某个列等于`NULL`值的搜索条件，而且那个列又是不允许存储`NULL`值的，那么在该表的执行计划的Extra列就会提示`Not exists`额外信息
EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

s1 是驱动表，s2 是被驱动表，s2.id 列是不允许存储 NULL 值的，而 where 子句中又包含 s2.id IS NULL 的搜索条件，这意味着必定是驱动表的记录在被驱动表中找不到匹配 ON 子句条件的记录才会把该驱动表的记录加入到最终结果集，所以对于某条驱动表中的记录来说，如果能在被驱动表中找到一条符合 ON 子句条件的记录，那么该驱动表的记录就不会被加入到最终的结果集，也就是说没有必要到被驱动表中找到全部符合 ON 子句条件的记录，这样可以稍微节省一点性能。

09、Using intersect(…) 、 Using union(…) 和 Using sort_union(…)

1. 如果执行计划的 Extra 列出现了 Using intersect(…) 提示，说明准备使用 Intersect 索引合并的方式执行查询，括号中的 … 表示需要进行索引合并的索引名称；
2. 如果出现了 Using union(…) 提示，说明准备使用 Union 索引合并的方式执行查询；
3. 如果出现了 Using sort_union(…) 提示，说明准备使用 Sort-Union 索引合并的方式执行查询。

# 如果执行计划的`Extra`列出现了`Using intersect(...)`提示，说明准备使用`Intersect`索引合并的方式执行查询，括号中的`...`表示需要进行索引合并的索引名称；
# 如果出现了`Using union(...)`提示，说明准备使用`Union`索引合并的方式执行查询；
# 如果出现了`Using sort_union(...)`提示，说明准备使用`Sort-Union`索引合并的方式执行查询。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'; -- Using union(idx_key1,idx_key3); Using where
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND id > 9000; -- Using intersect(idx_key1,PRIMARY); Using where
EXPLAIN SELECT * FROM s1 WHERE key1 < 'a' OR key3 > 'z'; -- Using sort_union(idx_key1,idx_key3); Using where

10、Zero limit

当 LIMIT 子句的参数为 0 时，表示压根儿不打算从表中读出任何记录，此时将会提示该额外记录。

# 当我们的`LIMIT`子句的参数为`0`时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息
EXPLAIN SELECT * FROM s1 LIMIT 0;

11、Using filesort

在有些情况下，当对结果集中的记录进行排序时，是可以使用到索引的。

# 有一些情况下对结果集中的记录进行排序是可以使用到索引的。
EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

这个查询语句利用 idx_key1 索引直接取出 key1 列的 10 条记录，然后针对每一条二级索引记录进行回表操作。但是在很多种情况下，排序无法使用到索引，只能在内存中（记录较少时）或磁盘中（记录较多时）进行排序，这种在内存中或磁盘中进行排序的方式统称为文件排序。

# 很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：`filesort`）。
# 如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的`Extra`列中显示`Using filesort`提示
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

12、Using temporary

在许多查询的执行过程中，MySQL 可能会借助临时表来完成一些功能，比如去重、排序之类的。

比如，在执行 distinct、group by、union 等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL 很有可能通过建立内部的临时表来执行查询。如果查询中用到了内部的临时表，在执行计划的 Extra 列将会显示 Using temporary。

# 在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。
# 如果查询中使用到了内部的临时表，在执行计划的`Extra`列将会显示`Using temporary`提示
EXPLAIN SELECT DISTINCT common_field FROM s1;

注意：执行计划中出现 Using temporary 并不是一个好的征兆，因为建立与维护临时表需要付出很大的成本，所以最好能使用索引来替代临时表。

13、Start temporary、End temporary

查询优化器会优先尝试将 IN子句查询转换成半连接，而半连接又有好多种执行策略。当执行策略为 Duplicate Weedout 时，也就是通过建立临时表来为外层查询中的记录进行去重操作时，驱动表查询执行计划的 Extra 列将显示 Start temporary，被驱动表查询计划的 Extra 列将显示 End temporary。

14、LooseScan

在将 IN 子查询转为半连接时，如果采用的是 LooseScan 执行策略，则驱动表执行计划的 Extra 列就显示 LooseScan。

15、FirstMatch（tbl_name）

在将 IN 子查询转为半连接时，如果采用的是 FirstMatch 执行策略，则被驱动表执行计划的 Extra 列就显示 FirstMatch（tbl_name）。

7.2.12 小结

1. EXPLAIN 不考虑各种 Cache
2. EXPLAIN 不能显示 MySQL 在执行查询时所做的优化工作
3. EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
4. 部分统计信息是估算的，并非精确值

7.3 执行计划的输出格式

EXPLAIN 可以输出四种格式：传统格式、JSON 格式、TREE 格式、可视化输出。用户可以根据需要选择适用于自己的格式。

这部分了解即可。

7.3.1 传统格式

传统格式简单明了，输出是一个表格形式，概要说明查询计划。

7.3.2 JSON 格式

传统 EXPLAIN 语句输出中缺少了一个衡量执行计划好坏的重要属性-成本。而 JSON 格式是四种格式里输出信息最详尽的格式，里面包含了执行的成本信息。

JSON 格式：在 EXPLAIN 单词和真正的查询语句中间加上 FORMAT=JSON：

EXPLAIN FORMAT = JSON SELECT ....

# json格式的explain
EXPLAIN FORMAT = JSON SELECT
* 
FROM
	s1
	INNER JOIN s2 ON s1.key1 = s2.key2 
WHERE
	s1.common_field = 'a';

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1394.77"
    } /* cost_info */,
    "nested_loop": [
      {
        "table": {
          "table_name": "s1",
          "access_type": "ALL",
          "possible_keys": [
            "idx_key1"
          ] /* possible_keys */,
          "rows_examined_per_scan": 10152,
          "rows_produced_per_join": 1015,
          "filtered": "10.00",
          "cost_info": {
            "read_cost": "937.93",
            "eval_cost": "101.52",
            "prefix_cost": "1039.45",
            "data_read_per_join": "1M"
          } /* cost_info */,
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ] /* used_columns */,
          "attached_condition": "((`study01`.`s1`.`common_field` = 'a') and (`study01`.`s1`.`key1` is not null))"
        } /* table */
      },
      {
        "table": {
          "table_name": "s2",
          "access_type": "eq_ref",
          "possible_keys": [
            "idx_key2"
          ] /* possible_keys */,
          "key": "idx_key2",
          "used_key_parts": [
            "key2"
          ] /* used_key_parts */,
          "key_length": "5",
          "ref": [
            "study01.s1.key1"
          ] /* ref */,
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1015,
          "filtered": "100.00",
          "index_condition": "(cast(`study01`.`s1`.`key1` as double) = cast(`study01`.`s2`.`key2` as double))",
          "cost_info": {
            "read_cost": "253.80",
            "eval_cost": "101.52",
            "prefix_cost": "1394.77",
            "data_read_per_join": "1M"
          } /* cost_info */,
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ] /* used_columns */
        } /* table */
      }
    ] /* nested_loop */
  } /* query_block */
}

7.3.3 TREE 格式

TREE 格式是 8.0.16 版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。

# TREE格式
EXPLAIN FORMAT = tree SELECT
* 
FROM
	s1
	INNER JOIN s2 ON s1.key1 = s2.key2 
WHERE
	s1.common_field = 'a';

7.3.4 可视化输出

可视化输出，可以通过 MySQL Workbench 可视化查看 MySQL 的执行计划。通过点击 Workbench 的放大镜图标，即可生成可视化的查询计划。