MySQL 的 EXPLAIN 命令可以查看 SELECT 语句的执行的计划, 是 MySQL 查询优化的必备工具.
通过执行计划可以了解查询方式, 索引使用情况, 需要扫描的数据量以及是否需要临时表或排序操作等信息.
我们需要分析执行计划对查询进行有的放矢的优化.
需要注意:
EXPLAIN 不考虑触发器, 存储过程或用户自定义函数对查询的影响
EXPLAIN 不考虑缓存
EXPLAIN 只能分析执行计划, 不能显示存储引擎在执行查询过程中进行的操作
部分统计信息是估算的, 并非精确值
本文基于 MySQL 5.6 版本.
- EXPLAIN SELECT * FROM `user`
- JOIN `post` ON `user`.id = `post`.uid
- WHERE user.`created_at` <'2018-10-01 00:00:00' AND `post`.status = 1;
结果:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | user | range | PRIMARY,idx_created_at | idx_created_at | 7 | null | 19440 | Using index condition; Using where; Using temporary; Using filesort |
1 | SIMPLE | post | ref | idx_uid,idx_status | idx_uid | 8 | user.id | 1 | Using where |
EXPLAIN 的行数为查询涉及的表数, 结果各列的含义为:
id: 查询的唯一标识
select_type: 查询的类型
table: 查询的表, 可能是数据库中的表 / 视图, 也可能是 FROM 中的子查询
type: 搜索数据的方法
possible_keys: 可能使用的索引
key: 最终决定要使用的 key
key_len: 查询索引使用的字节数. 通常越少越好
ref: 查询的列或常量
rows: 需要扫描的行数, 估计值. 通常越少越好
extra: 额外的信息
select type
select_type 可能的值有:
SIMPLE: 简单查询, 不包含子查询和 union
PRIMRARY: 包含子查询时的最外层查询; 使用 union 时的第一个查询
UNION: 包含 union 的查询中非第一个查询
DEPENDENT UNION: 与 UNION 相同, 但依赖外层查询的结果
SUBQUERY: 子查询
DEPENDENT SUBQUERY: 依赖外层查询的子查询
DERIVED: 用于 FROM 中的子查询
下面给出几个示例:
- EXPLAIN SELECT * FROM post WHERE uid = (
- SELECT id FROM user WHERE name = "finley"
- );
id | select_type | table |
---|---|---|
1 | PRIMARY | post |
2 | SUBQUERY | user |
- DEPENDENT SUBQUERY:
- EXPLAIN SELECT * FROM post WHERE uid = (
- SELECT id FROM user WHERE name = "finley" AND post.uid=user.id
- );
id | select_type | table |
---|---|---|
1 | PRIMARY | post |
2 | DEPENDENT SUBQUERY | user |
type
type 字段描述了查询的方式, 从好到坏为:
null: 不需要访问索引和表即可完成, 示例: SELECT 1;
const: 表中仅有一行匹配, 在分解查询计划时直接将其读出作为常量使用. system 是 const 类型的特例.
示例: SELECT id FROM user WHERE name = "hdt3213";
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | user | const | uni_name | uni_name | 258 | const | 1 | Using index |
UNIQUE KEY uni_name (name) ON user
eq_ref: 使用 PRIMARY KEY 或 UNIQUE KEY 进行关联查询.
示例: SELECT * FROM post JOIN user ON post.uid = user.id WHERE user.gender = 'M';
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | post | ALL | idx_uid | 0 | 0 | 0 | 57796 | null |
1 | SIMPLE | user | eq_ref | PRIMARY | PRIMARY | 8 | post.uid | 1 | Using where |
ref: 使用允许重复的索引进行查询
示例: SELECT * FROM user WHERE phone='12345678901';
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | user | ref | idx_phone | idx_phone | 259 | const | 1 | Using index condition |
range: 使用索引进行范围查询:
示例: SELECT * FROM user WHERE age>18;
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | user | ref | idx_age | idx_age | 259 | const | 1 | null |
index: 在索引上进行顺序扫描. 常见于在多列索引中未使用最左列进行查询.
示例: SELECT * FROM user WHERE last_name='smith'
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | user | ref | idx_full_name | idx_full_name | 259 | const | 1 | Using where |
all: 扫描全表, 最坏的情况
extra
extra 列显示了查询过程中需要执行的其它操作, 有些情况应尽力避免.
using filesort: 查询时执行了排序操作而无法使用索引排序. 虽然名称为'file'但操作可能是在内存中执行的, 取决是否有足够的内存进行排序.
应尽量避免这种 filesort 出现.
using temporary: 使用临时表存储中间结果, 常见于 ORDER BY 和 GROUP BY 语句中. 临时表可能在内存中也可能在硬盘中, 应尽量避免这种操作出现.
using index: 索引中包含查询的所有列 (覆盖索引) 不需要查询数据表. 可以加快查询速度.
using index condition: 索引条件推送(MySQL 5.6 新特性), 服务器层将不能直接使用索引的查询条件推送给存储引擎, 从而避免在服务器层进行过滤.
using where: 服务器层对存储引擎返回的数据进行了过滤
distinct: 优化 distinct 操作, 查询到匹配的数据后停止继续搜索
来源: https://www.cnblogs.com/Finley/p/9866606.html