Mysql：SQL优化常用手段及explain使用（一）

当面对一条慢SQL时，可以按此清单排查：

1. 能用缓存吗？​ -> 避免查询。

2. 用EXPLAIN分析了吗？​ -> 定位问题根源。

3. 用上索引了吗？​ -> 检查type和key列。

4. 索引失效了吗？​ -> 检查写法，避免函数、左模糊、类型转换。

5. 能用覆盖索引吗？​ -> 检查Extra列是否有Using index。

6. 有Using filesort或Using temporary吗？​ -> 优化ORDER BY/GROUP BY。

7. 是SELECT *吗？​ -> 改为指定字段。

8. 分页查询慢吗？​ -> 优化LIMIT。

9. 子查询能变JOIN吗？​ -> 改写SQL。

10. 单机瓶颈到了吗？​ -> 考虑读写分离、分库分表。

EXPLAIN 输出结果详解

先看一个例子：

EXPLAIN SELECT * FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE o.status = 'shipped' 
ORDER BY o.created_at DESC 
LIMIT 10;

结果：

下面逐列详解每个字段的含义和调优要点：

1. id（执行顺序）

• 表示 SELECT 的序号

• id 相同：执行顺序从上到下

• id 不同：id 值越大，越先执行

• id 为 NULL：表示是结果集，如 UNION 结果

-- 示例：子查询 id 不同
EXPLAIN 
SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 1000
);
-- 会看到两个不同的 id

2. select_type（查询类型）

表示查询的类型，常见的有：

重点关注 DERIVED：

-- 差：使用派生表
EXPLAIN SELECT * FROM (
    SELECT * FROM users WHERE status = 1
) AS t WHERE t.age > 20;
-- select_type: DERIVED

-- 好：改写为普通查询
EXPLAIN SELECT * FROM users 
WHERE status = 1 AND age > 20;

3. table（访问的表）

• 显示正在访问的表名

• 可能是：表名、别名、<derivedN>（派生表）、<unionM,N>（UNION 结果）

4. type（访问类型）

这是最重要的列！​ 表示 MySQL 如何查找表中的行，从好到坏：

实战判断标准：

-- 1. const/eq_ref（最优）
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const

-- 2. ref（良好）
-- 假设在 email 上有普通索引
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- type: ref

-- 3. range（可接受）
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- type: range

-- 4. index（较差）
-- 查询的列都在索引中，但需要扫描整个索引
EXPLAIN SELECT id FROM users;  -- id 是主键
-- type: index

-- 5. ALL（最差，必须优化！）
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';
-- 如果 phone 没有索引，type: ALL

调优目标：至少达到 range 级别，避免 index 和 ALL。

5. possible_keys（可能用到的索引）

• MySQL 分析后可能使用的索引

• 如果为 NULL，表示没有可用的索引

6. key（实际使用的索引）

• MySQL 实际决定使用的索引

• 如果为 NULL，表示没有使用索引，需要优化

• 可能不在 possible_keys 中，优化器会选择它认为最优的

-- 强制使用索引
EXPLAIN SELECT * FROM users USE INDEX(idx_email) WHERE email LIKE 'a%';

-- 忽略索引
EXPLAIN SELECT * FROM users IGNORE INDEX(idx_email) WHERE email = 'test@example.com';

7. key_len（索引使用长度）

• 索引中使用的字节数

• 判断索引使用情况的重要指标：

  • 值越大，使用的索引部分越多

  • 可判断是否使用了复合索引的所有部分

-- 示例：复合索引 (name, age, city)
CREATE INDEX idx_multi ON users(name(10), age, city(20));

EXPLAIN SELECT * FROM users WHERE name = '张三';
-- key_len: 31 (10 * 3 + 1) 使用了 name 部分

EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25;
-- key_len: 35 (31 + 4) 使用了 name 和 age 部分

计算规则：

• 字符串：字符数 × 字符集字节数 + 2（变长字段）

• 整数：TINYINT 1, SMALLINT 2, INT 4, BIGINT 8

• 时间：DATE 3, TIMESTAMP 4, DATETIME 8

• NULL 标记：允许 NULL 时 +1

8. ref（索引等值匹配的值）

• 显示索引的哪一列被使用了

• 常见值：const（常量）、字段名、NULL

EXPLAIN SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id;
-- 对于 users 表：ref: db.o.user_id

9. rows（预估扫描行数）

• MySQL 预估需要扫描的行数

• 值越小越好

• 基于统计信息估算，可能不准确

调优目标：尽可能减少 rows 值

10. filtered（过滤百分比）

• 表示存储引擎返回的数据在 server 层过滤后，剩下多少百分比

• 值越大越好，100 表示全部返回

• 8.0 之前版本没有此列

11. Extra（额外信息）

包含非常重要的执行细节：

重点关注的问题：

1. Using filesort（文件排序）

-- 没有合适的索引排序
EXPLAIN SELECT * FROM users ORDER BY created_at;
-- Extra: Using filesort

-- 优化：添加索引
ALTER TABLE users ADD INDEX idx_created(created_at);
EXPLAIN SELECT * FROM users ORDER BY created_at;
-- Extra: Using index  (如果只查 created_at)

2. Using temporary（临时表）

-- GROUP BY 无索引
EXPLAIN SELECT city, COUNT(*) FROM users GROUP BY city;
-- Extra: Using temporary; Using filesort

-- 优化：添加索引
ALTER TABLE users ADD INDEX idx_city(city);
-- 或使用覆盖索引
EXPLAIN SELECT city, COUNT(*) FROM users GROUP BY city;
-- Extra: Using index

3. Using index（覆盖索引）

-- 理想情况：查询的列都在索引中
EXPLAIN SELECT id, name FROM users WHERE name LIKE '张%';
-- 假设有索引 (name, id) 或 (id, name)
-- Extra: Using index

EXPLAIN 调优检查清单

 第一优先级：必须优化的

1. type 是否为 ALL？​ → 添加合适的索引

2. key 是否为 NULL？​ → 检查索引是否失效

3. rows 是否过大？​ → 尝试减少扫描行数

4. 是否有 Using temporary？​ → 优化 GROUP BY/DISTINCT

5. 是否有 Using filesort？​ → 优化 ORDER BY

第二优先级：应该优化的

1. type 是否为 index？​ → 考虑使用覆盖索引

2. Extra 是否有 Using join buffer？​ → 检查 JOIN 条件索引

3. filtered 是否很低？​ → 索引选择性可能不好

4. 是否有 DERIVED 类型？​ → 考虑改写子查询

高级优化

1. 能否使用覆盖索引？​ → 添加包含所有查询列的索引

2. 索引选择性如何？​ → 高选择性的列放索引前面

3. 符合最左前缀吗？​ → 调整 WHERE 条件顺序

4. 是否需要索引下推？​ → MySQL 5.6+ 特性

下一篇接着写一些可供参考的explain案例。