如何看懂MySQL执行计划
MySQL执行计划是查询性能优化的重要工具,通过执行计划,我们可以了解数据库是如何处理查询语句的。理解执行计划的各个组成部分,帮助我们定位性能瓶颈,进行有效的索引优化,提升查询效率。本文将详细解析MySQL执行计划的各个部分,并通过实例演示如何看懂和分析执行计划。
1. 执行计划的基础组成
当使用EXPLAIN
关键字查看一个查询的执行计划时,我们通常会遇到以下几个关键部分:
- id: 查询标识符,表示查询的顺序。
- select_type: 查询类型,如SIMPLE(简单查询)、PRIMARY(主查询)、SUBQUERY(子查询)等。
- table: 查询涉及到的表。
- type: 访问表的方式,如ALL、index、range、ref、eq_ref等,代表了查询的成本和效率。
- possible_keys: 可能使用的索引。
- key: 实际使用的索引。
- key_len: 使用的索引的长度。
- ref: 索引参考列。
- rows: 扫描的行数估算。
- Extra: 额外信息,如排序方式、是否使用了临时表等。
2. 查询类型(select_type)
查询类型指的是查询的种类,这可以帮助我们理解查询的复杂性和执行顺序。有几种常见的类型:
- SIMPLE: 简单查询,不包含子查询或UNION。
- PRIMARY: 复杂查询中最外层的查询。
- SUBQUERY: 子查询,可以嵌套在其他查询中。
- DERIVED: 用于FROM子句中的子查询。
3. 访问类型(type)
访问类型是执行计划中非常重要的部分,它直接影响查询的性能。以下是从优到劣的常见访问类型:
- system: 表只有一行 = 系统表。
- const: 通过主键或唯一索引访问,最多只有一行匹配。
- eq_ref: 使用主键或唯一索引的等值比较扫描。
- ref: 非唯一索引扫描,返回匹配的所有行。
- range: 索引范围扫描。
- index: 全索引扫描,与ALL类似,但只扫索引,不扫数据。
- ALL: 全表扫描。
4. 索引使用情况
- possible_keys: 列出了对于该查询可以考虑使用的所有索引。
- key: 实际选择使用的索引。
- key_len: 使用的索引的长度。这个长度越短,索引的选择性就越高,通常性能越好。
- ref: 显示了哪些列或常量被用作索引查找的参考。
5. 扫描行数和额外信息
- rows: 预估的需要检测的行数,数值越小,说明查询越高效。
- Extra: 提供额外的查询信息,如是否使用了临时表(Using temporary),是否进行了文件排序(Using filesort)等。
6. 分析示例
让我们以一个实例来分析:
假设有一个users
表,有name
(姓名),age
(年龄),和city
(城市)三个字段,我们要找出年龄大于30的用户。
查询语句为:
SELECT * FROM users WHERE age > 30;
使用EXPLAIN
分析查询:
EXPLAIN SELECT * FROM users WHERE age > 30;
得到的执行计划可能如下:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users range idx_users_age idx_users_age 4 NULL 100 Using where
7. 执行计划分析
从以上执行计划可以看出:
- id为1,表示这是唯一的查询。
- select_type为SIMPLE,说明这是一个简单查询。
- table为
users
,表明查询的是用户表。 - type为
range
,表示使用了范围扫描,性能较好。 - possible_keys提示了可能使用的索引
idx_users_age
。 - key确认了这个查询使用了
idx_users_age
索引。 - key_len为4,表明索引长度为4字节。
- ref为NULL,表示没有使用其他引用。
- rows估计需扫描100行,相对于表的总行数,这是一个较小的数字,意味着效率较高。
- Extra中的
Using where
表示在存储引擎收到记录后,通过服务器端的where条件进行了过滤。
8. 总结
通过上述分析,我们可以理解执行计划的每一部分,从而对查询性能进行评估和调优。例如,如果发现type
为ALL
,则可能需要添加索引以优化查询。如果Extra
中有Using temporary
或Using filesort
,可能需要调整查询逻辑或优化索引。
在实际应用中,应不断地使用EXPLAIN
来分析复杂查询,并根据执行计划适当地调整索引策略,改善查询语句,以达到最佳的数据库性能。