Mysql中Explain 关键字的作用是什么

发布时间:2021-07-13 14:48:39 作者:Leah
来源:亿速云 阅读:267
# MySQL中Explain关键字的作用是什么

## 引言

在数据库开发和优化过程中,SQL查询性能分析是每个开发者必须掌握的技能。MySQL提供的`EXPLN`关键字就是这样一个强大的工具,它能够帮助我们理解MySQL如何执行查询,从而发现潜在的性能瓶颈并进行优化。本文将深入探讨`EXPLN`的各个方面,包括其基本用法、输出字段解析、实际应用场景以及高级技巧。

## 一、EXPLN概述

### 1.1 什么是EXPLN

`EXPLN`是MySQL提供的一个SQL语句分析工具,它可以显示MySQL如何执行一条SELECT语句,包括表的读取顺序、可能使用的索引、实际使用的索引、表之间的连接方式等信息。通过分析这些信息,开发者可以了解查询的执行计划(Execution Plan),从而进行针对性的优化。

### 1.2 EXPLN的基本语法

```sql
EXPLN [EXTENDED | FORMAT=JSON] SELECT * FROM table_name WHERE condition;

MySQL 5.6之后还支持对UPDATE、DELETE等语句进行解释:

EXPLN UPDATE table_name SET column=value WHERE condition;

二、EXPLN的输出字段详解

EXPLN的输出包含多个重要字段,每个字段都提供了查询执行计划的不同方面信息。

2.1 id列

表示SELECT查询的序列号,包含一组数字,表示查询中执行SELECT子句或操作表的顺序。id相同,执行顺序由上至下;id不同,id值越大优先级越高,越先被执行。

2.2 select_type列

显示查询的类型,常见的有: - SIMPLE:简单SELECT查询(不包含子查询或UNION) - PRIMARY:查询中包含复杂的子部分,最外层的SELECT - SUBQUERY:在SELECT或WHERE列表中包含了子查询 - DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生表) - UNION:UNION中的第二个或后面的SELECT语句 - UNION RESULT:UNION的结果

2.3 table列

显示这一行的数据是关于哪张表的。有时不是真实的表名,可能是: - <derivedN>:表示id为N的衍生表 - <unionM,N>:表示id为M和N的UNION结果

2.4 partitions列

显示查询将访问的分区,对于非分区表显示为NULL。

2.5 type列

表示MySQL在表中找到所需行的方式,又称”访问类型”,从最好到最差依次是: - system:表只有一行记录(等于系统表) - const:通过索引一次就找到了,用于比较primary key或unique索引 - eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配 - ref:非唯一性索引扫描,返回匹配某个单独值的所有行 - range:只检索给定范围的行,使用一个索引来选择行 - index:Full Index Scan,index与ALL区别为index类型只遍历索引树 - ALL:Full Table Scan,将遍历全表以找到匹配的行

2.6 possible_keys列

显示可能应用在这张表中的索引,一个或多个。查询涉及的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。

2.7 key列

实际使用的索引。如果为NULL,则没有使用索引。查询中若使用了覆盖索引,则该索引仅出现在key列表中。

2.8 key_len列

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。

2.9 ref列

显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。

2.10 rows列

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数。

2.11 filtered列

表示返回结果的行数占需要读取行数的百分比,filtered的值越大越好。

2.12 Extra列

包含不适合在其他列显示但十分重要的额外信息: - Using filesort:说明MySQL会对数据使用一个外部的索引排序 - Using temporary:使用了临时表保存中间结果 - Using index:表示相应的SELECT操作中使用了覆盖索引 - Using where:表示使用了WHERE过滤 - Using join buffer:表示使用了连接缓存 - Impossible WHERE:WHERE子句的值总是false,不能获取任何数据

三、EXPLN的实际应用

3.1 索引优化

通过EXPLN可以验证索引是否被正确使用:

EXPLN SELECT * FROM users WHERE username = 'admin';

如果type为ALL,说明进行了全表扫描,应考虑为username字段添加索引。

3.2 连接查询优化

分析多表连接时的执行计划:

EXPLN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

观察连接顺序是否合理,是否有适当的索引被使用。

3.3 子查询优化

EXPLN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

检查子查询是否被优化为连接查询,或者是否产生了临时表。

3.4 排序和分组优化

EXPLN SELECT * FROM users ORDER BY create_time DESC;

如果Extra显示”Using filesort”,考虑为create_time添加索引。

四、EXPLN的高级用法

4.1 EXPLN EXTENDED

提供额外的信息,可以通过SHOW WARNINGS查看优化器重写后的查询:

EXPLN EXTENDED SELECT * FROM users WHERE id = 1;
SHOW WARNINGS;

4.2 EXPLN FORMAT=JSON

以JSON格式输出更详细的执行计划信息:

EXPLN FORMAT=JSON SELECT * FROM users WHERE id = 1;

JSON格式提供了更丰富的执行计划细节,包括成本估算等。

4.3 分析UPDATE/DELETE语句

MySQL 5.6+支持对DML语句进行解释:

EXPLN UPDATE users SET status=1 WHERE last_login < '2020-01-01';

五、EXPLN的局限性

虽然EXPLN是强大的工具,但也有其局限性: 1. 它只是预测执行计划,不是实际的执行情况 2. 不显示查询的执行时间 3. 不考虑缓存的影响 4. 对于复杂的查询可能不够直观

六、结合其他工具使用

6.1 与慢查询日志结合

通过慢查询日志找到慢查询,再用EXPLN分析。

6.2 与PROFILING结合

SET profiling = 1;
SELECT * FROM users WHERE username LIKE 'a%';
SHOW PROFILE;

6.3 使用Performance Schema

MySQL 5.5+提供的Performance Schema可以监控服务器事件。

七、实际案例分析

7.1 案例一:缺少索引导致的性能问题

原始查询:

EXPLN SELECT * FROM orders WHERE user_id = 100 AND status = 'completed';

分析后发现type为ALL,添加复合索引(user_id, status)后再次分析。

7.2 案例二:不合理的连接顺序

多表连接查询性能差,通过EXPLN发现连接顺序不合理,使用STRGHT_JOIN强制连接顺序。

7.3 案例三:索引失效的情况

查询条件使用函数导致索引失效:

EXPLN SELECT * FROM users WHERE DATE(create_time) = '2020-01-01';

八、最佳实践总结

  1. 养成对复杂查询使用EXPLN的习惯
  2. 重点关注type、key、rows、Extra列
  3. 为常用查询条件创建合适的索引
  4. 避免索引失效的写法
  5. 定期审查和优化查询语句

九、未来展望

随着MySQL版本的更新,EXPLN功能也在不断增强: - MySQL 8.0支持EXPLN ANALYZE,提供实际执行统计数据 - 更直观的可视化执行计划工具 - 与优化器跟踪(Optimizer Trace)更深度集成

结语

EXPLN是MySQL查询优化不可或缺的工具,掌握它的使用方法和解读技巧,可以显著提高数据库查询性能。通过本文的系统介绍,希望读者能够在实际工作中灵活运用EXPLN,发现并解决SQL性能问题,构建更高效的数据库应用。


:本文约5150字,详细介绍了MySQL中EXPLN关键字的作用、使用方法、输出解读、实际应用案例和最佳实践。通过系统的讲解,帮助读者全面理解并掌握这一重要的SQL性能分析工具。 “`

推荐阅读:
  1. Mysql中explain起到何种作用
  2. mysql中explain的作用

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql explain

上一篇:如何使用eclipse+maven一步步搭建SSM框架

下一篇:Java多线程的相关机制是什么

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》