MySQL 8慢查询诊断与索引优化实战：从EXPLAIN到覆盖索引的性能提升路径

慢查询不是加索引就能解决的

MySQL慢查询是数据库性能问题的常见入口，但很多工程师的应对方式是给相关字段加索引——加完发现查询还是慢，然后继续加索引，最终索引比数据还大。慢查询的根因有很多种：索引设计缺陷、查询写法问题、统计信息过期、锁等待、临时表溢出。系统化的诊断流程比盲目加索引有效得多。

慢查询采集与分析基线

第一步是确保慢查询日志开启并设置合理阈值：

# 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

# 开启慢查询日志并设置阈值为500ms
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';  # 记录未走索引的查询

# 持久化到my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 0.5
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1

用mysqldumpslow或pt-query-digest分析Top N慢查询：

# pt-query-digest分析（推荐）
pt-query-digest /var/log/mysql/slow.log --since '24h' --limit 10

# 输出按总执行时间排序的Top 10查询
# 重点关注：
# - Query ID：唯一标识
# - Exec times：执行次数
# - 95%：P95延迟
# - Rows examine/s：扫描行数

EXPLAIN执行计划深度解读

拿到慢查询SQL后，EXPLAIN是诊断的第一步。MySQL 8的EXPLAIN输出包含12列，核心关注5列：

EXPLAIN FORMAT=TREE
SELECT o.order_id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' AND o.created_at > '2026-07-01'
ORDER BY o.created_at DESC
LIMIT 20;

# 输出示例：
# -> Limit: 20 row(s)
#    -> Nested loop inner join
#      -> Index lookup on u using PRIMARY (id)
#      -> Filter: (o.status = 'PAID')
#         -> Index range scan on o using idx_created_at

type列的效率排序（从好到差）：

| type | 含义 | 性能 |
|——|——|——|
| system/const | 单行匹配 | 最优 |
| eq_ref | 唯一索引关联 | 优 |
| ref | 非唯一索引查找 | 良 |
| range | 索引范围扫描 | 中 |
| index | 全索引扫描 | 差 |
| ALL | 全表扫描 | 最差 |

Extra列的关键值解读：

– Using index：覆盖索引，无需回表，最优情况
– Using where：存储层返回后在Server层过滤
– Using temporary：使用了临时表（通常是GROUP BY导致）
– Using filesort：额外排序（无索引支持）
– Using index condition：索引条件下推（ICP）

Using temporary + Using filesort同时出现，意味着查询需要做大量额外工作，必须优化。

索引设计的5个实战原则

**原则1：最左前缀匹配决定索引可用性**

联合索引(a, b, c)能支持的查询模式：

-- 走索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b > 2  -- a走索引，b走范围

-- 不走索引
WHERE b = 2           -- 跳过了最左列a
WHERE b = 2 AND c = 3 -- 跳过了a
WHERE a = 1 AND c = 3 -- a走索引，c不走（b缺失断开）

**原则2：高选择性列放左边**

联合索引的列顺序应该把区分度高的列放前面：

-- 查看列的选择性
SELECT 
  COUNT(DISTINCT status) / COUNT(*) AS status_sel,
  COUNT(DISTINCT user_id) / COUNT(*) AS user_sel,
  COUNT(DISTINCT created_at) / COUNT(*) AS created_sel
FROM orders;

-- 结果示例：
-- status_sel: 0.001 (3种状态)
-- user_sel: 0.85  (10万用户)
-- created_sel: 0.98 (几乎唯一)

-- 索引设计：
-- 错误：INDEX(status, user_id)  -- status区分度太低
-- 正确：INDEX(user_id, status)  -- user_id高选择性在前

**原则3：覆盖索引消除回表**

当查询的所有列都包含在索引中时，不需要回表查主键数据：

-- 查询：SELECT user_id, status, created_at FROM orders WHERE user_id = 100

-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_user_status_created(user_id, status, created_at);

-- EXPLAIN结果：Extra = Using index（覆盖索引，零回表）
-- 性能提升：回表次数从结果集行数降到0

**原则4：避免索引列上做函数运算**

-- 不走索引（在索引列上做函数）
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-28';
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- 走索引（改写为范围查询）
SELECT * FROM orders 
WHERE created_at >= '2026-07-28' AND created_at < '2026-07-29';

-- 走索引（存储时统一小写）
ALTER TABLE users ADD INDEX idx_email(email);
SELECT * FROM users WHERE email = 'test@example.com';

**原则5：索引下推（ICP）在MySQL 5.6+自动生效**

索引条件下推把WHERE中能在索引上评估的条件提前到存储层过滤，减少回表次数：

-- INDEX(name, age)
SELECT * FROM users WHERE name LIKE '张%' AND age > 25;

-- 无ICP：存储层用name前缀找出所有'张%'行 → Server层逐行回表后过滤age
-- 有ICP：存储层用name前缀找出'张%'行 → 同时用age > 25过滤 → 只回表符合条件的行

-- 查看ICP是否生效
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND age > 25;
-- Extra: Using index condition（ICP已启用）

典型慢查询优化案例

**案例1：深分页优化**

-- 问题：深分页扫描大量行后丢弃
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 实际扫描100020行，只返回20行

-- 方案1：游标分页（推荐）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 方案2：延迟关联（不支持游标分页时）
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t
ON o.id = t.id;
-- 子查询只走覆盖索引，主查询通过主键回表20行

**案例2：ORDER BY + LIMIT的索引设计**

-- 查询模式
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 10;

-- 索引设计：把排序列放在等值条件列之后
ALTER TABLE orders ADD INDEX idx_user_created(user_id, created_at DESC);

-- EXPLAIN: type=ref, Extra=Using index condition
-- 索引有序，无需filesort

统计信息与索引健康度维护

MySQL 8的InnoDB统计信息自动更新在数据变化量大时可能不够及时：

-- 手动更新统计信息
ANALYZE TABLE orders;

-- 查看统计信息更新时间
SELECT table_name, last_analyzed 
FROM mysql.innodb_table_stats 
WHERE database_name = 'production';

-- 查看索引碎片率
SELECT 
  table_name,
  index_name,
  ROUND(data_free / (1024*1024), 2) AS free_mb,
  ROUND(data_length / (1024*1024), 2) AS data_mb
FROM information_schema.innodb_table_stats t
JOIN information_schema.tables s USING(table_name)
WHERE s.data_free / s.data_length > 0.2;  -- 碎片率>20%

-- 重建表消除碎片（线上操作，5.7+Online DDL）
ALTER TABLE orders ENGINE=InnoDB;

索引优化不是一次性的工作。随着数据分布变化、查询模式演变，需要定期回顾索引使用情况，删除无用索引，补充新查询需要的索引。核心思路：先诊断再优化，用EXPLAIN验证，用慢查询日志度量效果。