『Mysql进阶』Mysql explain详解(五)

目录

Explain 介绍

Explain分析示例

explain中的列

1. id 列

2. select_type 列

3. table 列

4. partitions 列

5. type 列

6. possible_keys 列

7. key 列

8. key_len 列

9. ref 列

10. rows 列

11. filtered 列

12. Extra 列

---

Explain 介绍

     EXPLAIN 语句提供有关 MySQL 如何执行语句的信息，使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 。

        我们根据 MySQL 的慢查询去记录一些执行时间比较久的SQL语句, 然后使用explain命令来查看这些SQL语句的执行计划, 根据执行计划内容，判断该SQL语句有没有使用上索引, 有没有做全表扫描等, 进而排查出SQL执行慢的原因。

官网介绍

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

Explain分析示例

• CREATE TABLE `actor` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(45) NOT NULL,`create_time` datetime DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `uk_name` (`name`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;INSERT INTO `actor` (`id`, `name`, `create_time`) VALUES (1,'a',NOW()), (2,'b',NOW()), (3,'c',NOW());CREATE TABLE `film` (`id` int(11) NOT NULL AUTO_INCREMENT,`actor_id` int(11) NOT NULL,`name` varchar(10) DEFAULT NULL,`create_time` datetime DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_actorid` (`actor_id`) USING BTREE
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;INSERT INTO `film` (`id`, `actor_id`, `name`, `create_time`) VALUES (1,1,'一路向东',NOW());

explain中的列

 explain select * from actor;

1. id 列

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行。

• id 相同，按table列由上至下顺序执行
• id 不同，如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行。
• id 想通不同同时存在，id如果相同，可以认为是一组，（本组内）从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。
• 当使用union时，id会出现NULL值，同时table列会出现<unionM,N>的值，其表示id的值为M和N的行的联合。

2. select_type 列

表示对应行是简单还是复杂的查询。

• simple：简单查询。查询不包含子查询和union

• primary：复杂查询中最外层的 select

• subquery：包含在 select 中的子查询（不在 from 子句中）

• derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表(derived的英文含义)

• union：在 union 中的第二个和随后的 select

•  union result: 从union表获取结果的select, UNION的结果集
• dependent union： UNION UNIONUNION中的第二个或以后的SELECT的SELECT的SELECT语句，取决于外部查询

•  dependent subquery:  子查询中的第一个SELECT，依赖于外部查询，表示这个subquery的查询要受到外部表查询的影响

3. table 列

这一列表示 explain 的一行正在访问哪个表。

它也可以是以下值之一：

• <unionM，N>：行引用ID值为M和N的行的并集。
• <derivedN>：该行引用ID值为N的行的派生表结果。派生表可以由例如FROM子句中的子查询产生。
• <subqueryN>：该行引用ID值为N的行的具体化子查询的结果。

4. partitions 列

使用的哪些分区（对于非分区表值为null）。

5. type 列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：

system > const > eq_ref > ref >fulltext >ref_or_null >unique_subquery>index_subquery> range > index_merge>index > ALL

一般来说，得保证查询达到range级别，最好达到ref

explain select min(id) from actor

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表.

• system: 表中只有一行数据或者是空表。等于系统表，这是const类型的特列，平时不会出现，可以忽略不计 .

• const : 使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type 是const。其他数据库也叫做唯一索引扫描。

explain select * from actor where id=1;

• eq_ref: primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。在连接表，被驱动表会出现.

explain select * from  actor  a  JOIN film b on a.id=b.actor_id

• ref : 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

explain select * from film where actor_id=1

•  fulltext : 全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时， mysql不管代价，优先选择使用全文索引 .
• ref_or_null : 与ref方法类似，只是增加了null值的比较。实际用的不多。
• unique_subquery: 用于where中的in形式子查询，子查询返回不重复值唯一值
• index_subquery: 用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复
  值，可以使用索引将子查询去重。
• range : 范围扫描通常出现在 in(), between ,> ,= 等操作中。使用一个索引来检索给定范围的行。

explain select * from actor where id >=1 and id <=3;

• index_merge: 表示查询使用了两个以上的索引，最后取交集或者并集，常见 and ， or 的条件使 用了不同的索引.
• index : 扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。(全叶子索引扫描，扫描的是二级索引)

explain select actor_id from film;

• ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

  explain select * from film;

  

6. possible_keys 列

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

7. key 列

查询真正使用到的索引

8. key_len 列

显示MySQL决定使用的索引size

key_len 计算规则:

1. 索引字段，非NOT NULL，加1个字节。
2. 定长字段：tinyint占1个字节、smallint占2个字节、int占4个字节、bitint占8个字节、date占3个字节、timestamp占4个字节，datetime占8个字节，char(n)占n个字符。
3. 变长字段：varchar(n)占n个字符+2个字节。
4. 不同的字符集，一个字符占用的字节数不同：
   • latin1编码，每个字符占用一个字节
   • gbk编码，每个字符占用两个字节
   • utf8编码，每个字符占用三个字节
   • utf8mb4编码，每个字符占用四个字节

例子

字段：phone varchar(20) DEFAULT NULL COMMENT ‘手机号’

条件：where phone=‘xxx’

通过explain查看key_len

utf8mb4编码下，key_len=83，即20*4+2+1

utf8编码下，key_len=63，即20*3+2+1

9. ref 列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

10. rows 列

显示此查询一共扫描了多少行，这个是一个估计值，不是精确的值。

11. filtered 列

表示此查询条件所过滤的数据的百分比

12. Extra 列

这一列展示的是额外信息。常见的重要值如下：

• Using where：使用 where 语句来处理结果，表示MySQL将对InnoDB提取的结果在SQL Layer层进行过滤，过滤条件字段无索引；

explain select * from film where name='一路向东';

• Using index：使用覆盖索引-----不会回表

覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

explain select actor_id from film where actor_id=1;

•  Using index condition: 用不了索引, 但用了索引下推优化,使之可以使用到索引.

explain select * from film where actor_id>=1;

• Using temporary: mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。(先扫描出来放在临时表，然后再去重)

explain select DISTINCT name from film;

• Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

explain select * from film ORDER BY name;

• Using join buffer (Block Nested Loop)：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度 .