MySQL EXPLAIN详解

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL数据库是许多Web应用程序的底层支持,而查询性能的优化是确保系统高效运行的关键。在MySQL中,`EXPLAIN`是一项强大的工具,可帮助开发者深入了解查询语句的执行计划,从而更好地优化查询性能。本文将详细解析MySQL的`EXPLAIN`关键字,以揭开查询执行计划的面纱。

MySQL数据库是许多Web应用程序的底层支持,而查询性能的优化是确保系统高效运行的关键。在MySQL中,EXPLAIN是一项强大的工具,可帮助开发者深入了解查询语句的执行计划,从而更好地优化查询性能。本文将详细解析MySQL的EXPLAIN关键字,以揭开查询执行计划的面纱。

mysql_explain.jpg

什么是EXPLAIN?

mysql官网文档:https://dev.mysql.com/doc/refman/8.0/en/explain.html

EXPLAIN是MySQL提供的一种查询优化工具,通过分析查询语句的执行计划,我们可以深入了解数据库引擎是如何处理查询的。执行EXPLAIN语句后,MySQL将返回一组关于查询执行的信息,其中包括访问表的方式、使用的索引、估计的行数等关键信息。

EXPLAIN示例

我们有四张表,bus_work_order_info工单表T1,sys_user用户表T2,bus_work_order_range发布省份表T3,bus_work_order_channel发布渠道表T5,T3,T5表的serial_id关联T1表的serial_id,T2表的user_name关联T1表的create_by,T1,T3,T5的字段serial_id都创建了索引,T2表的user_name创建了索引。执行以下sql:

EXPLAIN SELECT
    T1.serial_id,
    T1.top_flag,
    T1.top_time,
    T1.create_time,
    T2.nick_name,
    group_concat( T3.province_code ),
    group_concat( T5.channel_type ) 
FROM
    bus_work_order_info T1
    LEFT JOIN sys_user T2 ON T1.create_by = T2.user_name
    LEFT JOIN bus_work_order_range T3 ON T1.serial_id = T3.serial_id
    LEFT JOIN bus_work_order_channel T5 ON T1.serial_id = T5.serial_id 
WHERE
    T1.del_flag = '0' 
GROUP BY
    T1.serial_id 
ORDER BY
    T1.top_flag,
    T1.top_time DESC,
    T1.create_time DESC

输出结果为:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE T1 NULL ALL index_serial_id NULL NULL NULL 1403 10.00 Using where; Using temporary; Using filesort
1 SIMPLE T2 NULL ref index_user_name index_user_name 122 cnpctest.T1.create_by 1 100.00 Using where
1 SIMPLE T3 NULL ref index_serial_id index_serial_id 259 cnpctest.T1.serial_id 1 100.00 NULL
1 SIMPLE T5 NULL ref index_serial_id index_serial_id 259 cnpctest.T1.serial_id 1 100.00 NULL

我们逐行解读下这个信息

  • 第一个行(T1表)

type: ALL 表示全表扫描,没有使用索引。

rows: 1403 表示MySQL估计需要检索1403行。

filtered: 10.00 表示过滤效果较差。

Extra: Using where; Using temporary; Using filesort 表示使用了 WHERE 子句过滤,需要使用临时表进行排序。

  • 第二个行(T2表)

type: ref 表示使用了索引进行等值比较。

rows: 1 表示MySQL估计需要检索1行。

filtered: 100.00 表示过滤效果好。

Extra: Using where 表示使用了 WHERE 子句过滤。

  • 第三个行(T3表)

type: ref 表示使用了索引进行等值比较。

rows: 1 表示MySQL估计需要检索1行。

filtered: 100.00 表示过滤效果好。

Extra: NULL 表示无额外信息。

  • 第四个行(T5表)

type: ref 表示使用了索引进行等值比较。

rows: 1 表示MySQL估计需要检索1行。

filtered: 100.00 表示过滤效果好。

Extra: NULL 表示无额外信息。

EXPLAIN输出的字段解析

执行EXPLAIN语句后,输出结果中的字段提供了关于查询执行计划的详细信息。以下是一些常见的输出字段及其含义:

id

查询的唯一标识符。对于复杂查询,可能会有多个id值,表示查询的执行顺序。

  • id的取值

单一查询:如果查询是简单的SELECT语句,id将为一个整数值,表示查询的顺序。

复合查询:对于包含子查询或UNION的复杂查询,id可能包含多个值,每个值代表查询计划中的一个步骤。

  • id值的排序

从1开始递增:对于单一查询,id值从1开始递增,依次标识查询中的每个步骤。

子查询和UNION:对于复合查询,id值的排序取决于执行计划中步骤的执行顺序。

  • id的含义

主查询的id为1:在复合查询中,主查询的id通常为1,表示最外层的查询。

子查询的id大于1:对于子查询,id的值大于1,用于表示子查询的执行顺序。

  • id的关联性

相同id值的关联子查询:在复合查询中,如果多个查询步骤有相同的id值,它们可能是关联的,表示它们在同一级别执行。

父子查询关系:id值较小的步骤通常是id值较大的步骤的父查询。

  • 分析查询计划

顺序分析:观察id值的顺序,可以了解查询的执行顺序。

关联关系:通过id值的关联关系,可以理解查询计划中不同步骤之间的父子关系。

select_type

查询的类型,常见的有:

SIMPLE: 简单的SELECT查询,不包含子查询或UNION。
PRIMARY: 最外层的SELECT查询。
SUBQUERY: 子查询。
DERIVED: 衍生表,通过FROM子句中的子查询获得的表。

  • SIMPLE:简单查询

表示查询不包含子查询或UNION操作。

这是最基本的查询类型,不涉及复杂的操作。

  • PRIMARY:最外层查询

表示查询中的最外层SELECT语句。

在复杂查询中,最外层的SELECT语句的select_type值为PRIMARY。

  • SUBQUERY:子查询

表示在查询中包含的子查询。

子查询可以是嵌套在主查询中的查询操作。

  • DERIVED:衍生表

表示从FROM子句中的子查询获取的衍生表。

在查询中创建了一个临时表,通常发生在包含GROUP BY或DISTINCT的查询中。

  • UNION:UNION操作

表示在查询中使用了UNION或UNION ALL操作。

多个查询结果集合并的情况。

  • UNION RESULT:UNION结果

表示合并了多个查询结果的最终结果。

在包含UNION的查询中,表示最终的合并步骤。

  • SUBQUERY N:带编号的子查询

表示查询中的第N个子查询,N为正整数。

在包含多个子查询的查询中,通过编号区分不同的子查询。

  • DEPENDENT SUBQUERY:依赖的子查询

表示子查询的执行依赖于外部查询的结果。

子查询的每次执行都依赖于外部查询的结果。

  • UNCACHEABLE SUBQUERY:不可缓存的子查询

表示子查询的结果不能被缓存。

子查询包含了不可缓存的元素,例如用户变量或RAND()函数。

  • DEPENDENT UNION:依赖的UNION

表示UNION中的某个查询依赖于外部查询的结果。

与DEPENDENT SUBQUERY类似,但用于UNION操作。

  • UNCACHEABLE UNION:不可缓存的UNION

表示UNION中的某个查询结果不能被缓存。

与UNCACHEABLE SUBQUERY类似,但用于UNION操作。

table

涉及的表,包括表名或子查询的别名。

  • 表名

如果查询操作直接涉及表,table字段将显示表的名称。

  • 子查询别名

如果查询中包含子查询,table字段可能显示子查询的别名。

  • 子查询的表别名

在包含子查询的情况下,如果子查询中使用了表别名,table字段将显示子查询的表别名。

  • 衍生表的别名

如果查询中使用了DERIVED,表示创建了一个衍生表(通常是从FROM子句中的子查询中获取的),table字段将显示衍生表的别名。

partitions

查询中涉及的分区(如果有)的信息。分区是MySQL中一种对表进行水平分割的技术,可以根据某个列的值将表数据划分为不同的分区。

  • 分区名称

如果查询涉及分区表,并且分区列上有索引,partitions字段将显示查询中涉及的分区的名称。

  • ALL

如果partitions字段的取值是"ALL",表示查询操作涉及表的所有分区。这可能是因为查询条件没有涉及到分区列,或者分区列上没有索引。

  • NULL

如果partitions字段的取值是NULL,表示表不是分区表,或者查询不涉及分区表。针对非分区表或者没有涉及分区列的查询,partitions字段通常是NULL。

type

访问表的方式,可能是const、eq_ref、range、index、all等。

  • const:常量

表示通过索引一次就找到了,常用于等值比较。例如,在主键或唯一索引上进行等值查询。

  • eq_ref:唯一索引查找

类似于const,区别在于使用的是非唯一索引。例如,在使用主键或唯一索引进行连接查询时。

  • ref:非唯一索引查找

表示使用非唯一索引进行等值比较。例如,通过普通索引查找某个值。

  • range:范围查找

表示使用索引进行范围查找,通常出现在带有BETWEEN、IN、>,<等条件的查询中。例如,WHERE column_name BETWEEN 10 AND 20。

  • index:全索引扫描

表示查询会遍历整个索引,而不是表中的实际行数。这可能是因为查询的列没有被索引覆盖,或者查询不使用索引而进行全表扫描。

  • ALL:全表扫描

表示查询将对整个表进行扫描,而不使用索引。这是效率最低的情况,尽量避免在大表上出现type为ALL的情况。

  • system:系统表

表示查询将对系统表进行扫描,通常是一些固定的小表。

  • NULL:无效的或不可用的索引

表示没有使用索引,通常是因为相关的索引不可用或不适用。

possible_keys

在查询中可能用于加速数据检索的索引。

  • 多个索引

如果查询中有多个可以使用的索引,它们将以逗号分隔列在possible_keys字段中。

  • NULL值

如果possible_keys字段的值为NULL,表示在查询中没有找到可以使用的索引。这可能导致查询执行时需要全表扫描,影响性能。

  • 覆盖索引

如果查询的列在某个索引中全部包含,这个索引可能成为覆盖索引。覆盖索引可以提高性能,因为它不需要回表查找实际的行数据。

key

在查询执行中实际使用的索引。

  • NULL值

如果key字段的值是NULL,表示在查询中没有使用索引。这可能导致查询执行时需要全表扫描,影响性能。

  • 具体索引名称

如果key字段的值是一个具体的索引名称,表示在查询中使用了这个索引。索引名称将与表的索引名称一致。

  • 覆盖索引

如果key字段使用了索引,并且在Extra字段中显示了Using index,表示使用了覆盖索引。覆盖索引指的是查询所需的数据都包含在索引中,无需回表查找实际的行数据,通常提高性能。

  • 多个索引

如果查询中有多个可以使用的索引,它们将以逗号分隔列在key字段中。这表示MySQL优化器可能在多个索引中选择一个用于查询执行。

key_len

使用的索引的长度。

  • key_len的单位

key_len表示索引的长度,以字节为单位。

  • key_len值为1的情况

如果key_len的值是1,表示使用的是单列索引,索引列的长度为1个字节。

  • 多列索引的情况

如果使用的是多列索引,key_len将显示这些列长度之和。例如,如果有一个包含两个INT类型列的索引,且这两列的长度分别为4个字节,那么key_len将显示8。

  • 字符串类型的索引

如果索引包含字符串类型的列,key_len将根据字符集和存储引擎的不同而有所不同。例如,UTF-8字符集的字符串索引可能需要多个字节来表示一个字符。

ref

关于索引的使用方式和关联条件的信息。

  • ref值的含义

ref字段的值指示了连接表时所使用的索引,通常与关联条件中的列有关。如果没有连接操作,ref字段可能显示NULL。

  • 多个ref值

如果查询中有多个连接条件,ref字段的值将以逗号分隔,分别对应不同的连接条件。

  • const值

如果ref的值为const,表示使用的是常量值进行连接,通常出现在使用常量进行等值连接的情况下。例如,在连接时使用了类似WHERE column_name = 10的条件。

  • 复杂条件的情况

如果连接条件比较复杂,ref字段可能显示多个值,反映了不同连接条件的情况。这可能涉及到多个索引和多个关联条件。

rows

查询执行时估计的检索行数。

  • rows的含义

rows字段表示在执行查询时,MySQL估计需要检索的行数。这是一个估计值,不是实际的查询结果行数。

  • 单表查询

在单表查询中,rows表示预计从表中检索的行数。

  • 多表查询

在多表连接查询中,rows表示联接操作后预计返回的行数。

对于联接操作,rows的值可能会受到连接条件、索引的影响。

  • 索引使用:

当type字段显示为const、eq_ref、ref、range等时,rows通常是相对准确的估计。

当type字段显示为ALL时,rows可能是一个较大的值,表示将对整个表进行扫描。

  • 优化器统计信息:

MySQL优化器根据表的统计信息和索引信息来估计rows的值。

通过ANALYZE TABLE命令可以更新表的统计信息,有助于优化器更准确地估计行数。

filtered

在查询过程中过滤的行的百分比

  • filtered的取值范围

filtered的值是一个百分比,范围从0到100。0表示没有任何过滤,100表示所有的行都被过滤。

  • filtered的含义

filtered表示通过使用索引和条件进行过滤后,最终返回的行数相对于扫描行数的百分比。这个值越高,表示查询条件和索引更有效地过滤了不符合条件的行。

  • 单表查询

在单表查询中,filtered表示使用索引和条件过滤掉的行的百分比。

  • 多表查询

在多表连接查询中,filtered表示在连接操作后使用索引和条件过滤掉的行的百分比。

  • NULL值

如果filtered的值是NULL,表示MySQL无法估计或优化器没有使用统计信息。

  • 统计信息的重要性

filtered的值受到表统计信息的影响,通过ANALYZE TABLE命令可以更新表的统计信息,有助于优化器更准确地估计过滤效果。

Extra

查询执行计划的一些额外信息,如Using index、Using where、Using temporary等。这个字段可以包含多个标志,每个标志之间以分号(;)分隔。

  • Using where

表示MySQL将在存储引擎层使用WHERE条件来过滤行。这意味着在检索出的结果集中,可能会有额外的条件过滤。

  • Using index

表示MySQL将使用覆盖索引,即查询可以通过索引的信息满足WHERE条件和选择列表,而无需回表查找实际的行数据。

  • Using temporary

表示MySQL在查询过程中需要创建一个临时表,通常发生在排序或分组操作时。

  • Using filesort

表示MySQL将对结果进行文件排序,通常发生在没有使用索引的ORDER BY操作中。

  • Range checked for each record

表示MySQL在索引范围扫描时需要对每一条记录进行额外的检查,通常出现在范围查询时。

  • Full scan on NULL key

表示MySQL执行了全表扫描,但只考虑了索引列为NULL的行。

  • Backward index scan

表示MySQL将反向扫描索引,通常发生在ORDER BY DESC操作中。

  • Distinct

表示MySQL将对结果进行DISTINCT操作,去除重复的行。

  • Fulltext index

表示MySQL使用了全文索引。

  • Filesort on disk

表示MySQL执行文件排序,并且结果集大到不能在内存中完成排序,因此需要使用磁盘临时文件。

如何利用EXPLAIN优化查询

  • 查看访问类型

通过type字段了解访问表的方式,避免全表扫描,优先选择索引访问。

  • 检查索引使用情况

确保查询中涉及的字段有适当的索引,避免不必要的索引扫描。

  • 关注Extra字段

注意额外信息,如Using where、Using temporary等,有助于理解查询执行的额外开销。

  • 避免不必要的数据检索

    通过分析rows字段,可以了解估计的数据行数,避免不必要的大量数据检索。

总结

通过深入了解MySQL的EXPLAIN工具,开发者可以更好地理解查询语句的执行计划,从而有针对性地进行优化。通过选择合适的索引、避免全表扫描、关注查询执行的额外开销,我们可以提高数据库查询性能,确保系统的高效运行。

EXPLAIN是MySQL查询优化的得力助手,通过善于利用这一工具,我们能够更好地挖掘MySQL数据库的潜力,为应用程序提供更出色的性能。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
4月前
|
SQL 存储 关系型数据库
【MYSQL高级】Mysql的SQL性能分析【借助EXPLAIN分析】
【MYSQL高级】Mysql的SQL性能分析【借助EXPLAIN分析】
59 0
|
21天前
|
存储 关系型数据库 MySQL
MySQL技能完整学习列表6、查询优化——1、EXPLAIN命令的使用——2、索引优化
MySQL技能完整学习列表6、查询优化——1、EXPLAIN命令的使用——2、索引优化
12 0
|
23天前
|
SQL 存储 关系型数据库
MySQL - Explain详解
MySQL - Explain详解
|
1月前
|
SQL 关系型数据库 MySQL
MySQL SQL性能分析 慢查询日志、explain使用
MySQL SQL性能分析 慢查询日志、explain使用
33 0
|
1月前
|
SQL 机器学习/深度学习 关系型数据库
MySQL - Explain深度剖析
MySQL - Explain深度剖析
39 0
|
2月前
|
SQL 关系型数据库 MySQL
mysql explain 详解及sql优化指南
mysql explain 详解及sql优化指南
24 0
|
2月前
|
SQL 存储 关系型数据库
【MySQL】MySQL Explain性能调优详解
【MySQL】MySQL Explain性能调优详解
52 0
【MySQL】MySQL Explain性能调优详解
|
2月前
|
SQL 存储 关系型数据库
谈谈MYSQL中的Explain
谈谈MYSQL中的Explain
|
2月前
|
SQL 关系型数据库 MySQL
MySQL EXPLAIN
MySQL EXPLAIN
24 0
|
2月前
|
存储 关系型数据库 MySQL
MySQL查询执行计划详解(EXPLAIN)
一、单表查询 访问方法/访问类型: • const:通过主键值或唯一二级索引与一个常熟进行等值查询(不包括NULL),只会生成一条记录 • ref:普通二级索引与一个常数进行等值比较,可能生成多条记录 • ref_or_null:ref的前提下可以加上or key is null • range:对应的扫描区间为若干个单点扫描区间或范围扫描区间(不包括负无穷到正无穷的范围) • index:扫描区间为全表,但是可以在二级索引中扫描(因为二级索引每条记录占用空间更小,所以需要读的页更少) • all:直接扫描全部的聚集索引记录