MySQL 索引优化与慢查询排查

dbaai

MySQL 索引优化与慢查询排查实战

一、具体的问题

很多开发在线上遇到"列表页越翻越慢""导出报表要等半分钟"时，第一反应就是加个索引，但加完要么没效果、要么写入变慢。真正要解决的问题是：慢查询到底慢在哪一步，是该加索引、改写法，还是该重写排序分页逻辑？本文用一个会拖垮体验的分页场景，把定位与优化的完整路径走一遍。

二、核心原理

1. 慢查询的两条主因

MySQL 的慢，绝大多数逃不出两类：一是没走索引，全表扫描（type=ALL）逐行比对；二是走了索引但回表次数太多，或者排序、分页阶段在临时文件里折腾。看一条 SQL 慢不慢，先看执行计划的 type 列（const > ref > range > index > ALL，越靠右越差）和 rows 估算扫描行数，再看 Extra 里有没有 Using filesort、Using temporary——这两个是性能杀手。

2. 慢查询日志是定位入口

MySQL 自带慢查询日志，把超过 long_query_time 秒的语句记下来，是排查的第一手材料。打开后不要全量开（I/O 有开销），一般把阈值设 1 秒，配合 log_queries_not_using_indexes 顺带抓"全表扫描"的语句。真正分析时，直接用 mysqldumpslow 按总耗时、平均耗时、出现次数排序，快速锁定最该优化的那几条：

2. # 按平均耗时倒序，取前 10 条
3. mysqldumpslow -s at -t 10 /var/lib/mysql/slow.log
5. # 按总耗时，只看某张表的
6. mysqldumpslow -s t -g "FROM orders" /var/lib/mysql/slow.log

复制代码

注意：long_query_time 默认是 10 秒，很多"肉眼觉得慢"的语句根本不会被记录，上线排查前先把它调到 1 或 0.5。

3. 索引为什么能救分页，又为什么救不了深翻页

在 WHERE status=? ORDER BY created_at DESC LIMIT 20 这种查询里，如果在 (status, created_at) 上建联合索引，MySQL 能直接用索引定位到有序的数据，避免 filesort。但传统 LIMIT 100000, 20 的深翻页，即使有索引也要先顺着索引跳过前 10 万行再取 20 行，跳过动作本身很贵。深翻页的正确解法是"游标分页"（seek method）：记住上一页最后一条的排序值，下次用 WHERE created_at < ? ORDER BY created_at DESC LIMIT 20 直接接着取，索引可以精确定位起点，不用再跳。

三、实例参考（动手步骤）

下面用一张订单表 orders（约 200 万行）复现"列表分页慢"，并给出可照做的优化步骤。

1) 先确认慢日志是否开启，并定位问题 SQL：

2. SHOW VARIABLES LIKE 'slow_query_log';
3. SHOW VARIABLES LIKE 'long_query_time';
4. -- 若未开启：SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1;

复制代码

2) 用 EXPLAIN 看原始分页语句的执行计划：

2. EXPLAIN
3. SELECT id, user_id, amount, created_at
4. FROM orders
5. WHERE status = 'PAID'
6. ORDER BY created_at DESC
7. LIMIT 100000, 20;

复制代码

通常会看到 type=index 或 ALL、Extra 里有 Using filesort，rows 估算接近全表——这就是慢的根因：要先扫过 10 万行再丢弃。

3) 建联合索引，覆盖过滤+排序：

2. ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

复制代码

安全提示：这里用 ALTER TABLE ... ADD INDEX 在 MySQL 8.0 默认走 Online DDL，不会长时间锁表，但大表仍建议在低峰执行，并用 ALGORITHM=INPLACE 显式确认。

4) 重建语句为游标分页，避免深翻页跳跃：

2. -- 上一页最后一条的 created_at 记为 @last_time
3. SELECT id, user_id, amount, created_at
4. FROM orders
5. WHERE status = 'PAID' AND created_at < @last_time
6. ORDER BY created_at DESC
7. LIMIT 20;

复制代码

5) 前后对比验证：原语句 LIMIT 100000,20 执行约 1.8 秒、扫描 100020 行；改用游标分页后约 8 毫秒、扫描 20 行，EXPLAIN 的 type 变为 range、Extra 不再有 filesort。差距来自"跳过"动作被消除，索引直接定位起点。

四、实操检查清单

• 排查前先把 long_query_time 调到 1 秒或更低，否则慢语句根本不进日志。
  
• 拿到慢 SQL 第一件事是 EXPLAIN，重点盯 type（越靠右越差）和 Extra 里的 Using filesort / Using temporary。
  
• 用 mysqldumpslow -s at -t 10 先找"平均最慢"的 Top 10，别凭感觉盲优化。
  
• 过滤+排序的语句优先考虑联合索引，顺序要贴合 WHERE 等值列在前、排序列在后。
  
• 深翻页（LIMIT 大 offset）一律改成游标分页，用上一页边界值接着取，不要硬跳。
  
• 大表加索引挑低峰，确认走 Online DDL（ALGORITHM=INPLACE），避免锁表拖垮业务。
  
• 优化后用同一句 EXPLAIN 复核 rows 扫描行数与 Extra，并实测前后耗时，确认真的变快而不是"感觉变快"。
  
• 索引不是免费午餐：每张表索引越多，写入与备份成本越高，只为极少查询服务的大索引要权衡是否值得。