MySQL慢查询拖垮数据库:从一条31秒SQL说起的索引优化实录-小栋博客

运营活动期间订单查询接口 P99 从 200ms 飙到 8s,数据库 CPU 打满,最慢一条 SQL 跑了 31 秒。一周索引专项治理:慢日志 + pt-query-digest 定位、EXPLAIN 读执行计划、联合索引最左前缀、覆盖索引免回表、修索引失效的 SQL 写法、深分页改游标分页。同接口 P99 稳定 60ms。

2024 年我们的订单查询接口在一次运营活动期间彻底崩了 — P99 从 200ms 飙到 8 秒,数据库 CPU 打满 100%,慢查询日志一秒钟刷出几百条。最夸张的一条 SQL 跑了 31 秒。紧急加只读从库扛住后,投了一周做 MySQL 慢查询与索引专项治理,从慢日志定位、EXPLAIN 分析、联合索引、覆盖索引到深分页优化全部重做,之后同样的接口 P99 稳定在 60ms。本文复盘 MySQL 索引优化的完整实战。

问题背景

业务:订单中心,核心表 t_order 约 4200 万行
数据库:MySQL 8.0,16C32G,InnoDB
事故现象:
- 订单列表接口 P99 从 200ms → 8s
- 数据库 CPU 持续 100%,大量 SQL 排队
- 慢查询日志每秒新增几百条

现场抓的几条典型慢 SQL:

# 慢 SQL 1:跑了 31s
SELECT * FROM t_order
WHERE user_id = 1001 AND status = 2
ORDER BY create_time DESC;
-- user_id 上有索引,但 status + 排序没覆盖,扫了 18 万行再排序

# 慢 SQL 2:深分页,跑了 12s
SELECT * FROM t_order
ORDER BY create_time DESC
LIMIT 2000000, 20;
-- LIMIT 200w 偏移,MySQL 要先扫描丢弃 200w 行

# 慢 SQL 3:索引彻底失效,全表扫描
SELECT * FROM t_order
WHERE DATE(create_time) = '2024-06-01';
-- create_time 上有索引,但被 DATE() 函数包住 → 索引失效

根因:
1. 索引设计随意,只按单列建,没有针对查询的联合索引
2. SELECT * 导致大量回表,无法用覆盖索引
3. 深分页用裸 LIMIT offset,偏移越大越慢
4. SQL 写法让索引失效:函数包裹列、隐式类型转换
5. 没有慢查询监控,问题积累到大促才爆发

修复 1:开启慢查询日志并定位

-- === 1. 开启慢查询日志(运行时动态开,无需重启)===
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;          -- 超过 1s 记入慢日志
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 没走索引的也记

-- 持久化到 my.cnf,重启不丢
-- [mysqld]
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 1
-- log_queries_not_using_indexes = 1

-- === 2. 查看当前正在执行的慢 SQL(现场救火用)===
SELECT id, user, db, time, state,
       LEFT(info, 80) AS sql_text
FROM information_schema.processlist
WHERE command = 'Query' AND time > 2
ORDER BY time DESC;
-- 找到拖死库的长 SQL,必要时 KILL  先止血

-- === 3. 看缓冲池命中率、临时表等全局健康度 ===
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';  -- 磁盘临时表多 = 排序/分组没索引

# === 用 pt-query-digest 聚合分析慢日志(Percona Toolkit)===
# 裸看 slow.log 没意义,要按"SQL 指纹"聚合,找出最该优化的几条

$ pt-query-digest /var/log/mysql/slow.log > digest.txt

# 输出节选:按总耗时排序,第一条就是头号元凶
# Rank Query ID           Response time   Calls  R/Call
# ====================================================
#    1 0x1A2B...         4821s  62.3%    1820   2.65s   SELECT t_order
#    2 0x3C4D...         1203s  15.5%      95   12.66s  SELECT t_order
#    3 0x5E6F...          890s  11.5%   31200   0.028s  SELECT t_order

# 关键解读:
# - Rank 1:单次 2.65s 不算极端,但调用 1820 次,总耗时占 62% → 最该优化
# - Rank 2:单次 12.66s,深分页那条,调用少但每次都慢
# - 优化优先级 = 总响应时间,不是单次最慢的那条

# 实时抓取 TOP SQL 也可以用:
$ pt-query-digest --processlist h=localhost --run-time=60

修复 2:用 EXPLAIN 读懂执行计划

-- 优化索引前,必须先用 EXPLAIN 看 MySQL 到底怎么执行的
EXPLAIN SELECT * FROM t_order
WHERE user_id = 1001 AND status = 2
ORDER BY create_time DESC;

-- 输出(优化前):
-- id  type  key          rows    Extra
-- 1   ref   idx_user_id  183204  Using where; Using filesort
--                        ^^^^^^  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--          扫了 18 万行              还要额外排序(filesort)

-- === EXPLAIN 关键字段怎么看 ===
-- type:访问类型,性能从好到坏
--   system > const > eq_ref > ref > range > index > ALL
--   出现 ALL(全表扫描)或 index(扫整个索引树)基本要优化
-- key:实际用到的索引,NULL = 没走索引
-- rows:预估扫描行数,越小越好
-- Extra:最该关注的列
--   Using filesort     → 排序没用上索引,在内存/磁盘额外排序
--   Using temporary    → 用了临时表(常见于 group by / distinct)
--   Using index        → 覆盖索引,好!不用回表
--   Using where        → server 层还要再过滤,可能索引不够精准

-- === EXPLAIN ANALYZE:实际执行并给出真实耗时(MySQL 8.0)===
EXPLAIN ANALYZE SELECT * FROM t_order WHERE user_id = 1001 AND status = 2;
-- 输出带 actual time=...、loops=...,比预估的 rows 更可信

修复 3:联合索引与最左前缀

-- 慢 SQL 1 的根因:idx_user_id 只覆盖 user_id
-- WHERE 里的 status 和 ORDER BY 的 create_time 都没被索引覆盖
-- → 先按 user_id 取出 18 万行,再在 server 层过滤 status,再 filesort 排序

-- === 解法:建一个"查询定制"的联合索引 ===
-- 顺序原则:等值条件列在前,排序列在后
ALTER TABLE t_order
ADD INDEX idx_user_status_time (user_id, status, create_time);

-- 优化后 EXPLAIN:
-- id  type  key                    rows  Extra
-- 1   ref   idx_user_status_time   42    Using where
--                                  ^^    filesort 消失了!
-- user_id + status 定位到 42 行,create_time 已在索引里有序 → 排序免费

-- === 联合索引的最左前缀原则 ===
-- 索引 (a, b, c) 实际能加速这些前缀组合:
--   WHERE a = ?                    ✓ 用到 a
--   WHERE a = ? AND b = ?          ✓ 用到 a,b
--   WHERE a = ? AND b = ? AND c=?  ✓ 全用到
--   WHERE b = ?                    ✗ 跳过了 a,用不上
--   WHERE a = ? AND c = ?          △ 只用到 a,c 用不上(b 断档)

-- === range 列后面的列会断索引 ===
-- 索引 (user_id, status, create_time)
SELECT * FROM t_order
WHERE user_id = 1001 AND status > 1 AND create_time > '2024-06-01';
-- status 是范围(>),它后面的 create_time 无法再走索引
-- → 把范围列放最后:索引建成 (user_id, create_time, status) 视查询而定

-- 建索引前先看是否已有可复用的:避免冗余索引拖慢写入
SHOW INDEX FROM t_order;

修复 4:覆盖索引避免回表

-- === 什么是回表 ===
-- 二级索引的叶子节点只存:索引列 + 主键
-- 如果 SELECT 的列不在索引里,就要拿主键再去聚簇索引查一次 → 回表
-- 一次查询回表 10 万次 = 10 万次随机 IO,慢的根源之一

-- 慢 SQL:SELECT * 必然回表
SELECT * FROM t_order WHERE user_id = 1001 AND status = 2;
-- EXPLAIN Extra: Using where(回表了)

-- === 覆盖索引:让索引本身就包含查询要的所有列 ===
-- 接口其实只要这几列,不需要 SELECT *
SELECT order_no, amount, create_time
FROM t_order
WHERE user_id = 1001 AND status = 2;

-- 建一个把这些列都带上的索引
ALTER TABLE t_order
ADD INDEX idx_cover (user_id, status, create_time, order_no, amount);

-- 优化后 EXPLAIN Extra: Using index  ← 覆盖索引!全程不回表
-- 数据直接从索引树拿,省掉所有回表的随机 IO

-- === 实测对比(user_id=1001 命中约 12 万行)===
-- SELECT *(回表):      820ms
-- 覆盖索引(不回表):    35ms

-- 注意权衡:
-- 1. 覆盖索引把列塞进索引,索引体积变大,占内存、拖慢写入
-- 2. 只对高频核心查询做覆盖索引,不要无脑把所有列都加进去
-- 3. SELECT * 是覆盖索引的天敌,接口按需取列是好习惯

修复 5:索引失效的常见场景

-- 索引建了却不走,90% 是 SQL 写法的问题。逐个排雷:

-- === 坑 1:对索引列用函数 / 表达式 ===
-- 失效:DATE() 包住了 create_time,索引用不上 → 全表扫
SELECT * FROM t_order WHERE DATE(create_time) = '2024-06-01';
-- 正确:改成范围,让列裸露
SELECT * FROM t_order
WHERE create_time >= '2024-06-01 00:00:00'
  AND create_time <  '2024-06-02 00:00:00';

-- === 坑 2:隐式类型转换 ===
-- order_no 是 varchar,传了数字 → MySQL 把每行的 order_no 转成数字再比
-- 等价于对列做了函数 → 索引失效
SELECT * FROM t_order WHERE order_no = 12345678;     -- 失效
SELECT * FROM t_order WHERE order_no = '12345678';   -- 正确,带引号

-- === 坑 3:LIKE 以通配符开头 ===
SELECT * FROM t_order WHERE order_no LIKE '%888';    -- 失效,前导 %
SELECT * FROM t_order WHERE order_no LIKE '888%';    -- 可用,后缀 %
-- 真要做 %xxx% 模糊搜,该上 Elasticsearch,别硬扛

-- === 坑 4:OR 连接非索引列 ===
-- status 有索引,remark 没有 → 整个 OR 退化成全表扫
SELECT * FROM t_order WHERE status = 2 OR remark = 'x';
-- 改成 UNION,各自走各自的索引
SELECT * FROM t_order WHERE status = 2
UNION
SELECT * FROM t_order WHERE remark = 'x';

-- === 坑 5:不等于 / NOT IN / IS NOT NULL ===
-- != 、<> 、NOT IN 通常无法用索引(选择性差,优化器倾向全表扫)
-- 业务上能转成范围或 IN 的尽量转

-- === 坑 6:索引列参与运算 ===
SELECT * FROM t_order WHERE amount + 100 > 500;      -- 失效
SELECT * FROM t_order WHERE amount > 400;            -- 正确,把运算挪到右边

-- 验证是否真的失效,永远以 EXPLAIN 的 key 列为准,别凭感觉

修复 6:深分页优化

-- 慢 SQL 2:LIMIT 2000000, 20 跑了 12s
-- 原因:MySQL 要先定位并扫描前 200 万行,全部丢弃,只为拿第 200w+1 到 +20 行
SELECT * FROM t_order ORDER BY create_time DESC LIMIT 2000000, 20;

-- === 方案 A:延迟关联(子查询先用覆盖索引拿主键)===
-- 先在索引里翻页拿到 20 个主键(覆盖索引,翻页很快)
-- 再用这 20 个主键回表取完整数据,只回表 20 次
SELECT t.* FROM t_order t
INNER JOIN (
    SELECT id FROM t_order
    ORDER BY create_time DESC
    LIMIT 2000000, 20
) AS page ON t.id = page.id;
-- 子查询 ORDER BY 的列要有索引,扫描的是窄索引,比扫宽表行快得多
-- 实测:12s → 1.1s

-- === 方案 B:游标分页(seek method,最优)===
-- 不用 offset,记住上一页最后一行的位置,WHERE 直接跳过去
-- 第一页
SELECT * FROM t_order ORDER BY id DESC LIMIT 20;
-- 下一页:用上一页最后一条的 id 作为游标
SELECT * FROM t_order
WHERE id < 38291002          -- 上一页最后一行的 id
ORDER BY id DESC
LIMIT 20;
-- 无论翻到第几页都是 O(20),不存在 offset 扫描
-- 实测:任意页稳定 8ms
-- 限制:只能上一页/下一页,不能直接跳到第 N 页

-- === 方案 C:业务上限制翻页深度 ===
-- 真实业务里,几乎没人翻到第 10 万页
-- 列表页限制最多翻 100 页,更深的需求引导用搜索/筛选
-- 这是最朴素也最有效的"优化"

// 游标分页在 Java 接口层的封装
public PageResult<Order> listOrders(Long cursorId, int size) {
    // cursorId 为 null = 第一页;否则查 id < cursorId
    List<Order> rows = orderMapper.listByCursor(cursorId, size + 1);
    // 多查 1 条用于判断"是否还有下一页"
    boolean hasNext = rows.size() > size;
    if (hasNext) {
        rows = rows.subList(0, size);
    }
    Long nextCursor = hasNext ? rows.get(rows.size() - 1).getId() : null;
    return new PageResult<>(rows, nextCursor, hasNext);
}
// 前端拿着 nextCursor 请求下一页,彻底告别 LIMIT offset

修复 7:监控告警

# mysqld_exporter + Prometheus,慢查询与索引健康监控
groups:
- name: mysql-slow-query
  rules:
  # 1. 慢查询速率突增
  - alert: SlowQuerySpike
    expr: rate(mysql_global_status_slow_queries[5m]) > 5
    for: 3m
    annotations:
      summary: "{{ $labels.instance }} 慢查询 > 5 条/s,检查近期上线 SQL"

  # 2. 全表扫描行数过高(没走索引的特征)
  - alert: FullTableScanHigh
    expr: |
      rate(mysql_global_status_select_scan[5m])
      / rate(mysql_global_status_questions[5m]) > 0.1
    for: 5m
    annotations:
      summary: "{{ $labels.instance }} 全表扫描占比 > 10%,排查索引失效"

  # 3. 磁盘临时表过多(排序/分组没索引)
  - alert: TmpDiskTableHigh
    expr: rate(mysql_global_status_created_tmp_disk_tables[5m]) > 2
    for: 5m
    annotations:
      summary: "{{ $labels.instance }} 磁盘临时表过多,排查 filesort/group by"

  # 4. 缓冲池命中率过低
  - alert: BufferPoolHitLow
    expr: |
      1 - rate(mysql_global_status_innodb_buffer_pool_reads[5m])
      / rate(mysql_global_status_innodb_buffer_pool_read_requests[5m]) < 0.98
    for: 10m
    annotations:
      summary: "{{ $labels.instance }} 缓冲池命中率 < 98%,内存不足或扫描过多"

  # 5. 活跃连接数过高(慢 SQL 堆积的连锁反应)
  - alert: ThreadsRunningHigh
    expr: mysql_global_status_threads_running > 50
    for: 2m
    annotations:
      summary: "{{ $labels.instance }} 运行中线程 > 50,可能有慢 SQL 拖垮"

优化效果

指标                      治理前          治理后
=============================================================
订单列表接口 P99          8s              60ms
慢 SQL 1(条件查询)       31s             35ms(覆盖索引)
慢 SQL 2(深分页)         12s             8ms(游标分页)
慢 SQL 3(DATE 函数)      全表扫 4200w    走索引,80ms
数据库 CPU 峰值           100%            38%
慢查询日志               几百条/s         < 1 条/s
全表扫描占比              23%             0.4%
磁盘临时表                频繁             基本为 0

压测(订单查询 8000 QPS):
- 治理前:DB CPU 100%,SQL 排队,接口大面积超时
- 治理后:DB CPU 38%,P99 60ms,无慢查询

排查与改造:
- 慢日志定位 + pt-query-digest 分析:0.5 天
- 设计并新增 6 个联合/覆盖索引:1 天
- 改写 14 处索引失效的 SQL:2 天
- 深分页接口改游标分页(涉及 5 个接口):2 天
- 全链路压测验证:1 天

避坑清单

先开慢查询日志,再用 pt-query-digest 按总耗时聚合,别盯单次最慢的
优化任何 SQL 前必须 EXPLAIN,重点看 type、key、rows、Extra
联合索引按"等值列在前、排序列在后"设计,遵守最左前缀原则
范围条件(> < between)会断掉它后面列的索引,范围列尽量放最后
核心高频查询用覆盖索引,Extra 出现 Using index 才算到位
杜绝 SELECT *,按需取列才能用上覆盖索引、减少回表
索引列不要被函数包裹、不要参与运算、varchar 比较必须带引号
LIKE 前导 % 索引失效,全文模糊搜该上 Elasticsearch
深分页禁用裸 LIMIT offset,优先游标分页,其次延迟关联
慢查询、全表扫描占比、磁盘临时表都要上监控,别等大促才发现

总结

这次数据库被一条 SQL 拖垮的事故,让我对 MySQL 索引有了系统性的重新认识。最大的体会是:索引不是"给某一列加个标记"那么简单,而是要针对具体的查询去设计。我们最初的索引全是单列索引,看起来每个查询条件都"有索引",但真正执行时,联合查询只能用上第一个条件,剩下的过滤和排序全靠 MySQL 在内存里硬扛 —— 一个针对 WHERE 和 ORDER BY 量身定制的联合索引,配合最左前缀原则,才是真正的解药。第二个认知改变是理解了回表的代价:二级索引只存索引列和主键,SELECT * 意味着每行都要拿主键回聚簇索引再查一次,几十万次随机 IO 累积起来就是几百毫秒,而覆盖索引让查询所需的列全部待在索引树里,EXPLAIN 的 Extra 出现 Using index 时,性能往往有十倍以上的提升。第三个最容易被忽视的是 SQL 写法本身就能让索引失效 —— 用 DATE() 包住时间列、varchar 列和数字比较触发隐式转换、LIKE 以 % 开头,这些写法下索引建了也白建,而且 EXPLAIN 不看就根本发现不了。最后是深分页,LIMIT 2000000, 20 这种写法的本质是"扫描并丢弃两百万行",偏移越大越慢,游标分页用 WHERE id < cursor 把"跳过"变成"定位",无论翻到第几页都是恒定开销。归根结底,索引优化没有玄学,EXPLAIN 是唯一的事实来源,把执行计划看懂了,慢 SQL 就无处遁形。

—— 别看了 · 2026

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理邮箱1846861578@qq.com。

{{userData.name}}已认证

MySQL 慢查询拖垮数据库:从一条 31 秒 SQL 说起的索引优化实录