Detailed explanation of MySQL 5.7 optimization: Explain execution plan

mysql video tutorialColumn introductionExplain execution plan

##Recommended (free ): mysql video tutorial

Table of contents

1. Introduction
• 2. Explain the result column Detailed explanation
• 2.1 id
  • 2.2 select_type
  • 2.3 table
  • 2.4 partitions
  • 2.5 type (very important )
  • 2.6 possible_keys
  • 2.7 key
  • 2.8 key_len
  • 2.9 ref
  • 3.10 rows
  • 2.11 filtered
  • 2.12 Extra
##[Note]

Current system environment:

MySQL 5.7
• , other versions are omitted If there are differences, I will take time to explain them separately later. Only common scenarios are introduced. Other rare scenarios will not be studied for the time being. If necessary, you can find them in the official documents.
• Not for beginners, you need to have a certain understanding of
MySQL
• ’s underlying data structure B tree.

Document reference:

MySQL official Explain document

1. Introduction

Usage

The EXPLAIN

keyword can simulate the optimizer's execution of the SQL statement and analyze the performance bottleneck of the query statement.

2. Explain the detailed explanation of the result column

2.1 id

id
• column The serial number is select. Generally, there are as many select as there are id (Joint table query will have duplicate id ), and the order of id increases in the order in which select appears.
id
• The larger the value, the higher the priority of execution. If id is the same (usually appears in joint table query), the execution will be from top to bottom. id is NULL and is executed last.

2.2 select_type

select_type

Indicates whether the corresponding row is a simple or complex query. Common values ​​are:

• simple

  : simple query, the query does not include subqueries and unions.

• primary

  : The outermost select in a complex query.

• subquery

  : subquery contained in select (not in the from clause)

• derived

  : For subqueries contained in the form clause, MySQL will place the results in a temporary table, also called a derived table.

• union

  : The second or subsequent select in the union.

[Note] In

MySQL 5.7

, derived tables will be merged and optimized. If you want to visually view the value of select_type, you need to Temporarily turn off this function (it is turned on by default). This operation is required for all derivative tables in the following introduction.

# 关闭衍生表的合并优化（只对该会话有效）set session optimizer_switch='derived_merge=off'; # 打开衍生表的合并优化（只对该会话有效）set session optimizer_switch='derived_merge=on';

2.3 table

The table corresponding to the row query.

【Note】

When there is a subquery in the

from clause
• , the table column is in the format of <derivenN> , indicating that this row executes the query of the id = N row. When there is
union
• , the table data is in the format of <union M,N>, M and N represent participation##select row id of #union.
2.4 partitions

To be continued. . .

2.5 type (very important)

type

Indicates the association type (access type, or query type) of this row of queries, This value can be used to understand the approximate range of query data records for this row.
• The common values ​​from best to worst are: system > const > eq_ref > ref > range > index > ALL
; Generally, we need to ensure efficiency If so, we need to optimize our statements to at least reach the
• range level, and if possible optimize to ref; range is generally used for range searches, so change In other words, in addition to range search, we'd better optimize other query statements to the ref level.
Common value description

:

NULL
• : Indicates that

  MySQL can be optimized The query statement is decomposed in stages, and tables and indexes do not need to be accessed during the execution stage.

system / const
• : MySQL can optimize a certain query part and convert it into a constant (you can view it through

  show warnings Optimized results), mainly query the records corresponding to the primary key (Primary Key) or the unique key index (Unique Key). Because there are no duplications, only one record can be queried at most. , so the speed is relatively fast. system is a special case of const, which is system when there is only one record in the temporary table.

  # 表里有一个主键id为1的记录 - constexplain select * from student where id = 1# 派生表里面只有一条记录 - systemexplain select * from (select * from student where id = 1) tmp# 注： 如果查询的列中有 text 类型，那么在这里 type 会变为 ALL ，# 因为无法使用内存临时表，只能在磁盘上创建临时表，所以性能上会有所损耗，效果等同于全表查询 ALL。

• req_ref：当主键或唯一键索引的相关列并联接使用时（联表查询），最多匹配一条符合条件的记录。这是除了 const之外的最好的联接类型，简单的 select 查询不会出现 req_ref，更多出现在联表查询。

  # 虽然返回结果中有多条记录，但是在查询中一个学生id只对应一个班级，所以查询班级的时候为 req_ref，# 但是查询 student 的时候是 ALL，全表查询explain select * from student left join banji on student.id = banji.student_id

【注】在查询的过程中的返回结果如下：

当联接表查询时候会看作是一条查询 SQL，所以它们对应的 id 是一样的，当 id 都是一样的时候，按照从上到下的顺序依次执行，这里是先查询班级所有的学生（全表查询 ALL），然后根据学生id查找出学生对应的班级信息（req_ref）。

• ref：当使用普通索引（Normal）或者是联合索引的部分前缀时，索引要和某个值进行比较，可能会找到多个符合条件的记录行，从辅助索引的根节点开始对比并找到相应的记录。

  # 简单的 select 查询，name 是普通索引（Normal Index）explain select * from student where name = '张三';# 简单 select 查询，banji_id (第一个) 和 student_id (第二个) 的联合索引EXPLAIN SELECT * FROM banji_student WHERE banji_student.banji_id = 3# 关联表查询# 包含 banji 表，banji_student 是班级与学生的关系表# 关系表中有 banji_id (第一个) 和 student_id (第二个) 的联合索引 idx_banji_stu_id 索引，# 以下查询只用到了联合索引的 banji_id (第一个)explain select * from banji_id from banji left join banji_student 
  	on banji.id = banji_student.banji_id

• range：范围扫描，通常出现在 in，between，>，<，>= 等操作中，使用一个索引来检索给定范围的行。

  # 查询 id 大于 1 的学生信息explain select * from student where id > 2;

• index：

  • 扫描全索引就能拿到结果，一般是扫描某个二级索引（辅助索引，除了主键之外的索引）。这种索引不会从主键索引树根节点开始查找，而是直接对二级索引的叶子节点遍历和扫描，从而查找出相应的记录行，速度比较慢；
  • 这种查询方式一般为使用覆盖索引，查询所需的所有结果集在二级索引与主键索引中都有的情况下，由于二级索引一般比较小（因为二级索引是非聚集的，其叶子节点是存放的主键索引相应的地址，而主键索引是聚集的，其叶子节点存放的是完整的数据集），所以优先走二级索引，这种情况通常比 ALL 快一些。
  • 在某些情况下，如果表的列数特别多，这个时候通过辅助索引查询的性能就不如直接使用主键索引效率高（如果查询了辅助索引的话，还会返回到主键索引中进行查找更多的字段，也就是回表查询，当然在某些情况下使用回表查询的性能也会比只使用主键索引的性能高），这个时候会走主键索引，这种情况也比 ALL 快。

  # student 表只有id主键，name 普通索引select * from student;# 这个时候会走 name 索引# 因为 name 是普通索引，所以如果加 where 的话可以达到 ref 级别select * from student where name = 'Ana'

  覆盖索引定义：覆盖索引一般针对于辅助索引，并不是真正的索引，只是索引查找的一种方式。如果 select 查询的字段都在辅助索引树中全部拿到，这种情况一般是使用了覆盖索引，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。

• ALL：全表扫描，扫描主键（聚簇、聚集）索引树的所有叶子节点，通常这种情况下要根据业务场景来增加其他索引进行优化。

  # id 为主键的 student 表，没有其他索引，该查询为 ALL.select * from student

2.6 possible_keys

possible_keys 主要显示查询可能用到哪些索引来查找，只是可能会使用，并不代表一定会使用。

常见值说明：

• NULL: 没有相关索引，如果是 NULL 的话，可以考虑在 where 子句中创建一个适当的索引来提高查询性能，然后继续用 explain 查看其效果；也有可能出现 possible_keys 为 NULL，但是 key 有值，实际走了索引。
• 有列值：如果显示表中的某列，则表示可能会走这一列对应列值的索引；如果 possible_keys 有值，但是 key 显示 NULL，这种情况一般存在于表中数据量不大的情况，因为 MySQL 语句优化器认为索引对此查询的帮助不大，从而选择了全表查询。

2.7 key

• key 表示 MySQL 实际采用哪个索引来优化对该表的查询。
• 如果没有使用索引，则该列为 NULL，如果想强制 MySQL 使用或忽略 possible_keys 列中的索引，可以在查询中使用 force index 或 ignore index.

2.8 key_len

显示了 MySQL 索引所使用的字节数，通过这个数值可以计算具体使用了索引中的哪些列（主要用于联合索引的优化）。

【注】索引最大长度是 768 字节，当字符串过长时，MySQL 会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

示例：一个学生与班级的关系表：banji_student，存在使用 banji_id 与 student_id 两个列组合的联合索引，并且每个索引 int 都是 4 字节，通过 key_len 值为 4 可以知道只使用了联合索引的第一列：banji_id 来执行索引查找。

# 只使用了联合索引的第一列select * from banji_student where banji_id = 2

key_len 的计算规则如下：

• 字符串：常见的是 char(n) 和 varchar(n)，从 MySQL 5.0.3 之后，n 均表示字符数，而不是字节数，如果是 UTF-8，一个数字或字母占1个字节，一个汉字占3个字节。

  	描述
  char(n)	非汉字长度为 n，如果存放汉字长度为 3n 字节
  varchar(n)	非汉字长度为 n+2，如果存放汉字长度为 3n+2 字节；因为 varchar 是可变长字符串，需要 2 字节来存储字符串长度

• 数值类型：

  	描述
  tinyint	长度为 1 字节
  smallint	长度为 2 字节
  int	长度为 4 字节
  bigint	长度为 8 字节

• 时间类型：

  	描述
  date	长度为 3 字节
  timestamp	长度为 4 字节
  datetime	长度为 8 字节

• NULL

  如果字段允许设置为 NULL，则需要 1 字节来记录是否为 NULL； Not NULL 的列则不需要。

2.9 ref

显示了在使用 key 列中实际的索引时，表查找时所用到的列名和常量；常见的为 const 常量或索引关联查询的字段（列）名。

# 使用了常量 2，所以在查询的时候 ref 为 constselect * from student where id = 2# 关联表查询# 包含 banji 表，banji_student 是班级与学生的关系表# 关系表中有 banji_id (第一个) 和 student_id (第二个) 的联合索引 idx_banji_stu_id 索引# 这里的 ref 为 test.id ，也就是指的是 banji.idexplain select * from banji_id from banji left join banji_student 		on banji.id = banji_student.banji_id

3.10 rows

显示预计查询的结果数，并不是真正的结果集中的记录（行）数，仅供参考。

2.11 filtered

未完待续。。。

2.12 Extra

这一列展示的是额外的信息，存在很多值，且在不同的场景下以及不同版本的 MySQL 所表示的意思也不同，只能是表示大概的意思并且仅做优化参考，这里只介绍常见的值。

• Using index：使用覆盖索引，在 type 相同的情况下， Extra 的值为 Using index 要比为 NULL 性能高。

  比如 banji 表，存在 id，name，create_time 列，存在 id 主键与 name 普通索引。

  # 覆盖索引，直接查询 name 对应的索引树就可以满足 select 后面的查询列select id,name from banji# 非覆盖索引，虽然也走了索引，但是进行了回表查询，以查询出 create_time 字段。select * from banji where name = '二年级'

• Using where：使用 where 关键字来查询，并且对应的列没有设置索引，对应的 key 为 NULL。

  这种情况一般要对查询的列添加相对应的索引来进行优化。

• Using index condition：非覆盖索引查询并进行了回表，并且辅助索引使用了条件查询语句（where 或其他）。

  比如 banji_student 关系表，存在 id，banji_id，student_id，create_time 列，存在 id 主键 和 banji_id 与 student_id 的组合（联合）索引。

  # 进行了回表查询，以查询出 create_time 列，并且组合索引进行了范围查找select * from banji_student where banji_id > 3

• Using temporary：MySQL 需要创建创建一个临时表来处理查询，出现这种情况一般要添加索引进行优化处理。

  # 如果 name 没有添加普通索引的话，则需要创建一个临时表来进行去重，Extra 值为 Using temporary# 如果添加了索引，则会走 name 对应的索引树，并且是覆盖索引，Extra 值为 Using indexexplain select distinct name from student

• Using filesort：使用外部排序而不是索引排序，当数据较小的时候采用的是内存排序，当数据量较大的时候会频繁的访问磁盘，并将排序后的数据写入磁盘。

  # 如果 name 没有添加普通索引的话，则需要创建一个临时表来进行去重，Extra 值为 Using filesort# 如果添加了索引，则会走 name 对应的索引树，并且是覆盖索引，Extra 值为 Using indexexplain select name from student order by name

• Select tables optimized away：使用聚合函数（例如 max、min等）来访问存在索引的字段时，只访问索引树中已排好序的叶子，节点性能很高。

  # 比如使用聚合函数 min 查询最小的学生 id(主键)explain select min(id) from student

  The above is the detailed content of Detailed explanation of MySQL 5.7 optimization: Explain execution plan. For more information, please follow other related articles on the PHP Chinese website!