EXPLAIN Output Interpretation

本文涉及的产品
云数据库 Tair(兼容Redis),内存型 2GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 通过分析`EXPLAIN`输出的行列乘积,可评估MySQL连接效率,估算查询所需检查的行数。该乘积还影响`max-join-size`系统变量对多表SELECT语句的执行与中止决策。示例展示了如何逐步优化多表连接,通过调整列类型和大小来减少行乘积,从而提高查询性能。最终,结合索引分析,实现更优的连接效果。

EXPLAIN Output Format

image.png

EXPLAIN Output Interpretation

通过获取EXPLAIN输出的行列中的值的乘积,可以很好地指示连接有多好。这应该大致告诉您MySQL必须检查多少行才能执行查询。
如果使用max-join_size系统变量限制查询,则此行乘积还用于确定执行哪些多表SELECT语句以及中止哪些语句。请参阅第5.1.1节“配置服务器”。
以下示例显示了如何根据EXPLAIN提供的信息逐步优化多表连接。
假设您有此处显示的SELECT语句,并且您计划使用EXPLAIN对其进行检查:

image.png

For this example, make the following assumptions:

The columns being compared have been declared as follows.

image.png

最初,在执行任何优化之前,EXPLAIN语句会生成以下信息:

image.png

因为每个表的类型都是ALL,所以此输出表示MySQL正在生成所有表的笛卡尔积;即每一行的组合。这需要相当长的时间,因为必须检查每个表中行数的乘积。对于手头的案例,该产品为74×2135×74×3872=45268558720行。如果桌子更大,你只能想象需要多长时间。
这里的一个问题是,如果列被声明为相同的类型和大小,MySQL可以更有效地在列上使用索引。在这种情况下,如果VARCHAR和CHAR被声明为相同的大小,则它们被认为是相同的。tt.Actical PC被声明为CHAR(10),et.EMPLOYID为CHAR,因此存在长度不匹配。
要修复列长度之间的差异,请使用ALTER TABLE将ActualPC从10个字符延长到15个字符:

image.png

现在tt.AactualPC和et.MPLOYID都是VARCHAR(15)。再次执行EXPLAIN语句会产生以下结果:

image.png

这并不完美,但要好得多:行值的乘积减少了74倍。此版本将在几秒钟内执行。
可以进行第二次更改,以消除tt.AssignedPC=et_1.EMPLOYID和tt.ClientID=do.CUSTNMBR比较的列长度不匹配:
image.png

After that modification, EXPLAIN produces the output shown here:

image.png

此时,查询几乎尽可能地优化了。剩下的问题是,默认情况下,MySQL假设tt.ActualPC列中的值是均匀分布的,而tt表则不是这样。幸运的是,告诉MySQL分析密钥分布很容易:
image.png

With the additional index information, the join is perfect and EXPLAIN produces this result:

image.png

EXPLAIN输出中的行列是MySQL连接优化器的一个有根据的猜测。通过将行乘积与查询返回的实际行数进行比较,检查这些数字是否接近真实值。如果数字相差很大,在SELECT语句中使用STRIGHT_JOIN并尝试在FROM子句中以不同的顺序列出表,可能会获得更好的性能。(但是,STRIGHT_JOIN可能会阻止使用索引,因为它禁用了半连接转换。请参阅第8.2.2.1节“使用半连接转换优化子查询、派生表和视图引用”。)
在某些情况下,当EXPLAIN SELECT与子查询一起使用时,可以执行修改数据的语句;有关更多信息,请参阅第13.2.10.8节“衍生表”。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
24天前
|
JSON 关系型数据库 MySQL
EXPLAIN Output Format
`EXPLAIN`语句提供MySQL执行详情,适用于SELECT、DELETE、INSERT等语句,为每个表返回一行信息,按处理顺序列出。MySQL使用嵌套循环连接处理连接,输出含分区信息及扩展信息。旧版中分区和扩展信息需用特定语法生成,现已默认启用。EXPLAIN输出包括id、select_type、table等列,展示查询执行细节。最新版本中,可视化工具进一步帮助理解查询性能。
|
24天前
|
JSON 关系型数据库 MySQL
EXPLAIN Join Types
`EXPLAIN` 输出的 `type` 列描述了表连接方式,从最优到最差包括:`system`(单行系统表)、`const`(最多一行,视为常量)、`eq_ref`(最佳连接类型,用于主键或唯一索引)、`ref`(基于索引的部分匹配)、`fulltext`(全文索引)、`ref_or_null`(包含 NULL 值的行)、`index_merge`(索引合并优化)、`unique_subquery` 和 `index_subquery`(索引查找替代子查询)、`range`(索引范围内检索)、`index`(索引扫描)、`ALL`(全表扫描,通常最差)。
|
23天前
|
JSON 关系型数据库 MySQL
EXPLAIN Extra Information
`EXPLAIN` 输出的 `Extra` 列提供了 MySQL 解析查询的附加信息。此列可能的值及其对应的 JSON 属性如下: - **Using filesort / using_filesort**:需额外排序。 - **Using temporary / using_temporary_table**:需创建临时表。 - **Deleting all rows**:删除所有行。 - **Distinct / distinct**:寻找不同值。 - **FirstMatch(tbl_name)**:使用半连接策略。
|
存储 SQL 关系型数据库
Optimizing Queries with EXPLAIN(用explain来优化查询语句)
Optimizing Queries with EXPLAIN(用explain来优化查询语句)
35 0
SAMPLE 子句
SAMPLE 子句
144 1
1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause
1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause
195 0
1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause
|
SQL 关系型数据库 MySQL
Explain 之 type 介绍|学习笔记
快速学习 Explain 之 type 介绍
128 0
Explain 之 type 介绍|学习笔记
|
关系型数据库 MySQL 开发者
explain 之 select-type 和 table 介绍|学习笔记
快速学习 explain 之 select-type 和 table 介绍
797 0
|
存储 关系型数据库