mysql- explain 分析 -06

MySQL EXPLAIN 详解：SQL性能分析与优化指南

EXPLAIN是MySQL中用于分析SQL查询性能的关键工具，它能够显示MySQL如何执行查询，包括使用的索引、表连接顺序等重要信息。下面我将全面介绍EXPLAIN的使用方法、各字段含义及分析方法。

一、EXPLAIN基本用法

1. 基本语法

EXPLAIN SELECT * FROM users WHERE id = 100;

-- 或更详细的格式（MySQL 8.0+）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 100;

　　查询出的字段信息下面有详细解释

 

2. 分析UPDATE/DELETE/INSERT语句

EXPLAIN UPDATE users SET status = 'active' WHERE age > 18;
EXPLAIN DELETE FROM users WHERE last_login < '2023-01-01';

　　

3. 分析连接查询

EXPLAIN SELECT u.*, o.order_date 
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Beijing';

　　

二、EXPLAIN输出字段详解

以下是EXPLAIN输出的主要列及其含义：

列名	说明	重要程度	优化建议	典型场景	取值示例
id	SELECT查询的序列号， id相同，执行顺序从上到下， id不同，从大到小执行， id为null最后执行	★★	关注复杂查询中id的分布，子查询或UNION可能导致多id	嵌套子查询、UNION查	1 (简单查询) 1,2 (子查询)
select_type	查询类型，分别如下这些： SIMPLE ：简单SELECT查询(不含子查询或UNION) PRIMARY ：最外层查询 SUBQUERY ：子查询中的第一个SELECT DERIVED ：派生表(FROM子句中的子查询) UNION： UNION中的第二个或后续查询 UNION RESULT： UNION的结果	★★★	避免DEPENDENT SUBQUERY（依赖外层查询的子查询，性能差）	SIMPLE: 简单查询 DERIVED: FROM子句中的子查询	SIMPLE, PRIMARY, SUBQUERY, DERIVED
table	访问的表名或别名	★★	表名较长时建议使用别名	多表连接时显示表的访问顺序	users, u (别名), <derived2> (派生表)
partitions	匹配的分区	★	分区表优化时关注	分区表查询	p0, p1 (具体分区名)
type	访问类型（关键指标） 1.  system：表只有一行记录(系统表) 2. const：通过主键或唯一索引一次就找到(eg:  EXPLAIN SELECT * FROM users WHERE id = 1; ) 3. eq_ref：唯一索引关联查询 (eg: EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id; ) 4. ref：非唯一索引查找(eg: EXPLAIN SELECT * FROM users WHERE age = 25; -- age有普通索引 ) 5. range：索引范围扫描(eg: EXPLAIN SELECT * FROM users WHERE id > 100; ) 6. index：全索引扫描(比全表扫描好)(eg: EXPLAIN SELECT id FROM users; -- id是主键 ) 7. ALL：全表扫描(需优化)	★★★★★	至少达到range级别，优先优化ALL和index	const: 主键查询 ref: 非唯一索引查询 ALL: 全表扫描	访问类型，性能从好到坏排序: system > const > eq_ref > ref > range > index > ALL
possible_keys	可能使用的索引	★★★	检查列出的索引是否合理	显示优化器考虑的索引选项	idx_name, PRIMARY
key	实际使用的索引	★★★★★	若为NULL则未用索引，需创建	显示实际选择的索引	idx_age (使用age索引)
key_len	使用的索引字节数	★★★	与字段定义长度对比，判断是否充分利用索引	varchar(100) utf8mb4索引，前30字符: 30*4+2=122	4 (int) 122 (varchar部分使用)
ref	与索引比较的列或常量	★★	若为func可能使用了函数导致索引失效	显示索引匹配方式	const (常量) db1.users.id (列引用)
rows	预估检查行数	★★★★	数值越大性能越差，结合filtered评估	全表扫描时等于表总行数	1 (精确匹配) 1000 (范围扫描)
filtered	存储引擎层过滤后剩余数据的百分比	★★★	大于20%可能需优化	连接查询中前表filtered影响后表扫描次数	10.00 (过滤掉90%)
Extra	额外执行信息（关键提示）	★★★★★	重点优化Using filesort/temporary	Using index: 覆盖索引 Using where: 服务层过滤	Using index, Using where, Using filesort

 

三、EXPLAIN分析方法

1. 性能分析步骤

1. 查看type列：确认访问类型，避免ALL全表扫描

2. 检查key列：确认是否使用了合适的索引

3. 分析rows列：预估行数是否合理

4. 研究Extra列：查看额外执行信息

5. 评估key_len：确认索引使用是否充分

2. 实际案例分析

案例1：未使用索引

sql

EXPLAIN SELECT * FROM users WHERE phone = '13800138000';

• 如果type=ALL且key=NULL，说明是全表扫描

• 解决方案：为phone字段添加索引

案例2：索引使用不充分

sql

EXPLAIN SELECT * FROM users WHERE name LIKE '%张%';

• type=range但rows值很大

• 解决方案：避免前导通配符，考虑全文索引

案例3：多表连接优化

sql

EXPLAIN SELECT u.name, o.order_no 
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Shanghai';

• 检查每张表的type和key

• 确保连接字段有索引