😇MySQLExplain使用教程

61%

​Full scan on NULL key：子查询中的一种优化方式，在无法通过索引访问null值的时候使用

​Using Index：仅使用索引树中的信息从表中检索列信息，不用进行其他查找就可以读取实际行。(​覆盖索引：查询列都是索引列)

​No tables used：当此查询没有FROM子句或拥有FROM DUAL子句时出现。

​Impossible where：where子句始没有满足条件的目标数据，不会命中任何行

Using index condition：表示先按条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。（​使用索引范围查找，因查询列未被索引全覆盖，最终会回表查询。）

​Using join buffer：使用join buffer降低对被驱动表的扫描次数。

​Using where：不是读取表的所有数据或者不通过索引就可以获取所有需要的数据。（​未使用索引检索数据）

​Select tables optimized away：使用某些聚合函数（min,max）来访问某个索引值。（​无需查找表、索引就可以返回数据，效率非常高）

​Using filesort：当查询中包含 order by 操作而且无法利用索引完成的排序操作，数据较少时从内存排序，如果数据较多需要在磁盘中排序，需优化成索引排序。（​未使用索引进行排序导致在内存中或硬盘中排序）

​Using temporary：表示MySQL需要使用临时表来存储结果集；如果查询包含不同列的GROUP BY和 ORDER BY子句，通常会发生这种情况。（​使用临时表存储数据进行数据去重等操作）

可以通过添加索引的方式，优化成using index​

​Extra：SQL执行查询的一些额外信息。

​NULL：最后执行。且表示结果集，不需要使用它来进行查询

​id相同：执行顺序从上往下

​同时存在：先执行序号大，在从上往下

​id不同：序号大先执行

id：每个select都对应一个id，从1开始递增。如果该查询有子查询，将显示多个id值。

​filtered：表示符合查询条件的数据百分比。可以使用rows \* filtered/100计算出与explain前一个表进行连接的行数。

​rows：全表扫描时表示需要扫描表的行数估计值；索引扫描时表示扫描索引的行数估计值；值越小越好。

​UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，而是需要每次查询时重新计算。

​SIMPLE：简单select，不包括union与子查询

​UNION RESULT：如果两个查询中有相同的列，则会对这些列进行重复删除，只保留一个表中的列。

​PRIMARY：复杂查询中最外层查询，比如使用union或union all时，id为1的记录select\_type通常是primary

​DEPENDENT UNION：当union作为子查询时，其中第一个union为dependent subquery，第二个union为dependent union。

SUBQUERY：指在 select 语句中出现的查询语句，结果不依赖于外部查询

​UNION：分union与union all两种，id大于1的select被标记为union；如果union被from子句的子查询包含，那么第一个select会被标记为derived；union会针对相同的结果集进行去重，union all不会进行去重处理。

​DEPENDENT SUBQUERY：指在 select 语句中出现的查询语句，结果依赖于外部查询

​DERIVED：派生表，在FROM子句的查询语句，表示从外部数据源中推导出来的，而不是从 SELECT 语句中的其他列中选择出来的。

select\_type：查询类型。

​ref：表示将哪个字段或常量和key列所使用的字段进行比较。

​如果有别名，展示别名。

​如果有多个表，将显示多行记录。

table：查询所涉及的表名。

​partitions：表分区情况。

​字段如果为NULL，需要1个字节记录是否为NULL（空值字段：1字节）

​varchar(n)：如果是uft-8：3n+2字节，加的2个字节存储字符串长度。如果是utf8mb4：4n+2 字节（变长字段：2字节）

​char(n)：n个字节

​字符串

​datetime：8字节

​date：3字节

​ timestamp：4字节 

​时间类型

​ bigint：8字节

​ tinyint：1字节

​ int：4字节

​smaillint：2字节

​数值类型

​计算规则

​作用：使用联合索引的时候可以知道使用了哪几列

​key\_len：表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度。

​key：表示实际在查询中使用的索引，如果没有使用索引，显示NULL。

​possible\_keys：表示在查询中可能使用到某个索引或多个索引；如果没有选择索引，显示NULL。

all：扫描整个表进行匹配

避免使用

很少出现

​NULL：MySQL在优化过程中分解语句就已经可以获取到结果，执行时甚至不用访问表或索引，效率高

index：扫描整个索引进行匹配

​system：const类型的一种特殊场景，查询的表只有一行的情况

range：使用非唯一索引扫描部分索引，比如使用索引获取某些范围区间的记录

​index\_subquery：查询使用子查询的索引，当查询需要过滤大量数据时，使用子查询可以提高查询效率，而使用索引可以提高查询性能。

const：基于主键或唯一索引查看一行，当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问转换成常量查询，效率高

eq\_ref：基于主键或唯一索引连接两个表，对于每个索引键值，只有一条匹配记录

​unique\_subquery：查询使用合并索引来执行，当查询需要过滤大量数据时，使用子查询可以避免重复行，从而提高查询效率。

ref：基于非唯一索引连接两个表，通过二级索引列与常量进行等值匹配，可能会存在多条匹配记录

​index\_merge：表示使用了索引合并的优化方法，当查询需要扫描大量的数据时，使用合并索引可以提高查询效率。

​fulltext：全文索引

​ref\_or\_null：基于非唯一索引连接两个表，通过二级索引进行等值匹配询，该索引列的值可以是NULL值。

推荐使用​

​type：查询所使用的访问类型。（查询效率从上往下依次递减）

​Explain返回列详解

​Explain含义：Explain是 SQL 分析工具中非常重要的一个功能，它可以模拟优化器执行查询语句，帮助我们理解查询是如何执行的；分析查询执行计划可以帮助我们发现查询瓶颈，优化查询性能。

与​show warnings搭配使用查看执行器优化后的SQL：

Explain select \* from student;

show warnings;

​MySQL5.7版本之前使用Explain Extended关键字：Explain Extended select \* from student;

​MySQL5.7版本开始使用Explain关键字：Explain select \* from student;

​Explain使用方法

Explain使用与详解​

Explain使用与详解​

​Explain含义：Explain是 SQL 分析工具中非常重要的一个功能，它可以模拟优化器执行查询语句，帮助我们理解查询是如何执行的；分析查询执行计划可以帮助我们发现查询瓶颈，优化查询性能。

​Explain使用方法

​MySQL5.7版本之前使用Explain Extended关键字：Explain Extended select \* from student;

​MySQL5.7版本开始使用Explain关键字：Explain select \* from student;

与​show warnings搭配使用查看执行器优化后的SQL：

Explain select \* from student;

show warnings;

​Explain返回列详解

id：每个select都对应一个id，从1开始递增。如果该查询有子查询，将显示多个id值。

​id相同：执行顺序从上往下

​id不同：序号大先执行

​同时存在：先执行序号大，在从上往下

​NULL：最后执行。且表示结果集，不需要使用它来进行查询

select\_type：查询类型。

​SIMPLE：简单select，不包括union与子查询

​PRIMARY：复杂查询中最外层查询，比如使用union或union all时，id为1的记录select\_type通常是primary

SUBQUERY：指在 select 语句中出现的查询语句，结果不依赖于外部查询

​DEPENDENT SUBQUERY：指在 select 语句中出现的查询语句，结果依赖于外部查询

​DERIVED：派生表，在FROM子句的查询语句，表示从外部数据源中推导出来的，而不是从 SELECT 语句中的其他列中选择出来的。

​UNION：分union与union all两种，id大于1的select被标记为union；如果union被from子句的子查询包含，那么第一个select会被标记为derived；union会针对相同的结果集进行去重，union all不会进行去重处理。

​DEPENDENT UNION：当union作为子查询时，其中第一个union为dependent subquery，第二个union为dependent union。

​UNION RESULT：如果两个查询中有相同的列，则会对这些列进行重复删除，只保留一个表中的列。

​UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，而是需要每次查询时重新计算。

table：查询所涉及的表名。

​如果有多个表，将显示多行记录。

​如果有别名，展示别名。

​partitions：表分区情况。

​type：查询所使用的访问类型。（查询效率从上往下依次递减）

​NULL：MySQL在优化过程中分解语句就已经可以获取到结果，执行时甚至不用访问表或索引，效率高

​system：const类型的一种特殊场景，查询的表只有一行的情况

const：基于主键或唯一索引查看一行，当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问转换成常量查询，效率高

eq\_ref：基于主键或唯一索引连接两个表，对于每个索引键值，只有一条匹配记录

ref：基于非唯一索引连接两个表，通过二级索引列与常量进行等值匹配，可能会存在多条匹配记录

​fulltext：全文索引

​ref\_or\_null：基于非唯一索引连接两个表，通过二级索引进行等值匹配询，该索引列的值可以是NULL值。

​index\_merge：表示使用了索引合并的优化方法，当查询需要扫描大量的数据时，使用合并索引可以提高查询效率。

​unique\_subquery：查询使用合并索引来执行，当查询需要过滤大量数据时，使用子查询可以避免重复行，从而提高查询效率。

​index\_subquery：查询使用子查询的索引，当查询需要过滤大量数据时，使用子查询可以提高查询效率，而使用索引可以提高查询性能。

range：使用非唯一索引扫描部分索引，比如使用索引获取某些范围区间的记录

index：扫描整个索引进行匹配

all：扫描整个表进行匹配

很少出现

推荐使用​

避免使用

​possible\_keys：表示在查询中可能使用到某个索引或多个索引；如果没有选择索引，显示NULL。

​key：表示实际在查询中使用的索引，如果没有使用索引，显示NULL。

​key\_len：表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度。

​作用：使用联合索引的时候可以知道使用了哪几列

​计算规则

​字符串

​char(n)：n个字节

​varchar(n)：如果是uft-8：3n+2字节，加的2个字节存储字符串长度。如果是utf8mb4：4n+2 字节（变长字段：2字节）

​数值类型

​ tinyint：1字节

​smaillint：2字节

​ int：4字节

​ bigint：8字节

​时间类型

​date：3字节

​ timestamp：4字节 

​datetime：8字节

​字段如果为NULL，需要1个字节记录是否为NULL（空值字段：1字节）

​ref：表示将哪个字段或常量和key列所使用的字段进行比较。

​rows：全表扫描时表示需要扫描表的行数估计值；索引扫描时表示扫描索引的行数估计值；值越小越好。

​filtered：表示符合查询条件的数据百分比。可以使用rows \* filtered/100计算出与explain前一个表进行连接的行数。

​Extra：SQL执行查询的一些额外信息。

​Using Index：仅使用索引树中的信息从表中检索列信息，不用进行其他查找就可以读取实际行。(​覆盖索引：查询列都是索引列)

Using index condition：表示先按条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。（​使用索引范围查找，因查询列未被索引全覆盖，最终会回表查询。）

​Using where：不是读取表的所有数据或者不通过索引就可以获取所有需要的数据。（​未使用索引检索数据）

​Using temporary：表示MySQL需要使用临时表来存储结果集；如果查询包含不同列的GROUP BY和 ORDER BY子句，通常会发生这种情况。（​使用临时表存储数据进行数据去重等操作）

​Using filesort：当查询中包含 order by 操作而且无法利用索引完成的排序操作，数据较少时从内存排序，如果数据较多需要在磁盘中排序，需优化成索引排序。（​未使用索引进行排序导致在内存中或硬盘中排序）

​Select tables optimized away：使用某些聚合函数（min,max）来访问某个索引值。（​无需查找表、索引就可以返回数据，效率非常高）

​Using join buffer：使用join buffer降低对被驱动表的扫描次数。

​Impossible where：where子句始没有满足条件的目标数据，不会命中任何行

​No tables used：当此查询没有FROM子句或拥有FROM DUAL子句时出现。

​Full scan on NULL key：子查询中的一种优化方式，在无法通过索引访问null值的时候使用

可以通过添加索引的方式，优化成using index​

1.Explain含义

Explain是 SQL 分析工具中非常重要的一个功能，它可以模拟优化器执行查询语句，帮助我们理解查询是如何执行的；分析查询执行计划可以帮助我们发现查询瓶颈，优化查询性能。

2.Explain作用

●表的读取顺序
●SQL执行时查询操作类型
●可以使用哪些索引
●实际使用哪些索引
●每张表有多少行记录被扫描
●SQL语句性能分析

3.Explain用法

​

MySQL5.7版本之前，使用Explain Extended在Explain的基础上额外多返回filtered列与extra列；

​

Explain Extended select \* from users;

MySQL5.7版本之前，使用Explain Partitions在Explain的基础上额外多返回partitions列；

​

Explain Partitions select \* from users;

MySQL5.7版本引入了这两个特性，直接使用Explain关键字可以将partitions列、filtered列、extra列直接查询出来。

​

Explain select \* from users;

Explain语句返回列的各列含义：

列名

含义

id

每个select都有一个对应的id号，并且是从1开始自增的

select_type

查询语句执行的查询操作类型

table

表名

partitions

表分区情况

type

查询所用的访问类型

possible_keys

可能用到的索引

key

实际查询用到的索引

key_len

所使用到的索引长度

ref

使用到索引时，与索引进行等值匹配的列或者常量

rows

预计扫描的行数（索引行数或者表记录行数）

filtered

表示符合查询条件的数据百分比

Extra

SQL执行的额外信息

这些查询列大家先留一个印象，后续会详细讲解。

4.Explain返回列详解

接下来我们将展示Explain中每个列的信息

1. id列：每个select都有一个对应的id号，并且是从1开始自增的。

●如果id序号相同，从上往下执行。
●如果id序号不同，序号大先执行。
●如果两种都存在，先执行序号大，在同级从上往下执行。
●如果显示NULL，最后执行。表示结果集，并且不需要使用它来进行查询。

优化器会针对子查询进行一定的优化重写SQL：

2.select_type列：表示查询语句执行的查询操作类型

2.1.simple：简单select，不包括union与子查询

2.2.primary：复杂查询中最外层查询，比如使用union或union all时，id为1的记录select_type通常是primary

2.3.subquery：指在 select 语句中出现的子查询语句,结果不依赖于外部查询（不在from语句中）

2.4.dependent subquery：指在 select 语句中出现的查询语句，结果依赖于外部查询

2.5.derived：派生表，在FROM子句的查询语句，表示从外部数据源中推导出来的，而不是从 SELECT 语句中的其他列中选择出来的。

2.6.union：分union与union all两种，若第二个select出现在union之后，则被标记为union；如果union被from子句的子查询包含，那么第一个select会被标记为derived；union会针对相同的结果集进行去重，union all不会进行去重处理。

2.7.dependent union：当union作为子查询时，其中第一个union为dependent subquery，第二个union为dependent union。

2.8.union result：如果两个查询中有相同的列，则会对这些列进行重复删除，只保留一个表中的列。

3.table列：查询所涉及的表名。如果有多个表，将显示多行记录

4.partitions列：表分区情况

查询语句所涉及的表的分区情况。具体来说，它会显示出查询语句在哪些分区上执行，以及是否使用了分区裁剪等信息。如果没有分区，该项为NULL。

5.type列：查询所使用的访问类型

效率从高到低分别为：system > const > eq_ref > ref > fulltext > ref_or_null > range > index > ALL，一般来说保证range级别，最好能达到ref级别。

5.1.system：const类型的一种特殊场景，查询的表只有一行记录的情况，并且该表使用的存储引擎的统计数据是精确的

InnoDb存储引擎的统计数据不是精确的，虽然只有一条数据但是type类型为ALL；

Memory存储引擎的统计数据是精确的，所以当只有一条记录的时候type类型为system。

5.2.const：基于主键或唯一索引查看一行，当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问转换成常量查询，效率高

5.3.eq_ref：基于主键或唯一索引连接两个表，对于每个索引键值，只有一条匹配记录，被驱动表的类型为’eq_ref’

5.4.ref：基于非唯一索引连接两个表或通过二级索引列与常量进行等值匹配，可能会存在多条匹配记录

1.关联查询，使用非唯一索引进行匹配。
2.简单查询，使用二级索引列匹配。

5.5.range：使用非唯一索引扫描部分索引，比如使用索引获取某些范围区间的记录

5.6.index：扫描整个索引就能拿到结果，一般是二级索引，这种查询一般为使用覆盖索引（需优化，缩小数据范围）

5.7.all：扫描整个表进行匹配，即扫描聚簇索引树（需优化，添加索引优化）

5.8.NULL：MySQL在优化过程中分解语句就已经可以获取到结果，执行时甚至不用访问表或索引。

6.possible_keys列：表示在查询中可能使用到某个索引或多个索引；如果没有选择索引，显示NULL

7.key列：表示在查询中实际使用的索引，如果没有使用索引，显示NULL。

8.key_len列：表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度（主要使用在联合索引）

联合索引可以通过这个值算出具体使用了索引中的哪些列。
使用单例索引：

使用联合索引：

计算规则：
●字符串：
char(n)：n个字节 varchar(n)：如果是uft-8：3n+2字节，加的2个字节存储字符串长度。如果是utf8mb4：4n+2字节。
●数值类型：
tinyint：1字节
smaillint：2字节
int：4字节
bigint：8字节
●时间类型：
date：3字节
timestamp：4字节
datetime：8字节 字段如果为NULL，需要1个字节记录是否为NULL

9.ref列：表示将哪个字段或常量和key列所使用的字段进行比较。

当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
1.常量：

2.字段：

3.函数

10.rows列：全表扫描时表示需要扫描表的行数估计值；索引扫描时表示扫描索引的行数估计值；值越小越好（不是结果集中的行数）

1.全表扫描

2.索引扫描

11.filtered列：表示符合查询条件的数据百分比。可以使用rows * filtered/100计算出与explain前一个表进行连接的行数。

前一个表指 explain 中的id值比当前表id值小的表，id相同的时候指后执行的表。

12.Extra列：SQL执行查询的一些额外信息

12.1.Using Index：使用非主键索引树就可以查询所需要的数据。一般是覆盖索引，即查询列都包含在辅助索引树叶子节点中，不需要回表查询。

12.2.Using where：不通过索引查询所需要的数据

12.3.Using index condition：表示查询列不被索引覆盖，where 条件中是一个索引范围查找，过滤完索引后回表找到所有符合条件的数据行。

12.4.Using temporary：表示需要使用临时表来处理查询；

1.total_price列无索引，需要创建一张临时表进行去重

2.name列有联合索引

12.5.Using filesort：当查询中包含 order by 操作而且无法利用索引完成的排序操作，数据较少时从内存排序，如果数据较多需要在磁盘中排序。 需优化成索引排序。

1.total_price列无索引，无法通过索引进行排序。需要先保存total_price与对应的主键id，然后在排序total_price查找数据。

2.name列有索引，因索引已经是排好序的所以直接读取就可以了。

12.6.Select tables optimized away：使用某些聚合函数（min,max）来访问某个索引值。

5. 总结

正确合理使用 MySQL explain 可以帮助我们更好地理解查询执行计划，并确定如何最好地优化查询SQL，提升SQL性能，增加系统稳定性。

​