MySQL 慢查询治理：3 秒到 45ms 的完整过程

一个订单列表接口，从上线第 3 个月开始变慢：响应时间从 80ms 涨到 1.2 秒，再涨到 3 秒。数据量从 10 万涨到 400 万。开发的第一反应是「加机器」，加了两台只把 3 秒变成 2.8 秒——因为瓶颈在数据库单条 SQL 上。

最后定位到一个 ORDER BY create_time DESC LIMIT 20 的分页查询，EXPLAIN 显示 type: ALL, rows: 4128836, Extra: Using filesort。

这篇文章是那次治理的完整记录：从开启慢日志到最终把 P99 压到 45ms，中间做了哪 7 件事，每件带来多少收益。

一、先让慢查询可见

没有数据不要优化。第一步永远是开慢日志。

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;                    -- 先抓 1 秒以上的
SET GLOBAL log_queries_not_using_indexes = ON;     -- 顺便抓没走索引的
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';

log_queries_not_using_indexes 有一个副作用：小表全扫也会被记录，日志会暴涨。建议只在治理期开，或者配合 min_examined_row_limit 过滤：

SET GLOBAL min_examined_row_limit = 1000;   -- 扫描少于 1000 行的不记

线上更推荐用 performance_schema 做聚合统计，不用解析日志文件：

-- 总耗时最高的 10 条 SQL
SELECT
    DIGEST_TEXT                                   AS sql_text,
    COUNT_STAR                                    AS exec_count,
    ROUND(SUM_TIMER_WAIT/1e12, 2)                 AS total_sec,
    ROUND(AVG_TIMER_WAIT/1e9, 2)                  AS avg_ms,
    ROUND(MAX_TIMER_WAIT/1e9, 2)                  AS max_ms,
    SUM_ROWS_EXAMINED                             AS rows_examined,
    SUM_ROWS_SENT                                 AS rows_sent,
    SUM_NO_INDEX_USED                             AS no_index
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'shop'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

这个视图的价值在于按总耗时排序，而不是按单次耗时。一条 10ms 但每秒跑 500 次的 SQL，总消耗远超一条 2 秒但一天跑一次的报表。优化前者收益大得多。

判读两个关键比值：

rows_examined / rows_sent  → 扫描放大倍数，> 100 就说明索引有问题
total_sec                  → 总耗时，决定优化优先级

二、EXPLAIN 只看五列

EXPLAIN 输出十几列，实际只需要盯这五个：

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1
ORDER BY create_time DESC LIMIT 20;

type 的优劣顺序：

system > const > eq_ref > ref > range > index > ALL

index 看着比 ALL 好，其实只是「扫描索引树」而不是「扫描数据页」，对于大表依然是全量扫描。index 和 ALL 在优化优先级上是同一档。

Extra 里两个词要特别警惕：

• Using filesort：需要额外排序。数据量大时会写临时文件，磁盘 IO 飙升。
• Using temporary：用了临时表。GROUP BY、DISTINCT、子查询常见，临时表可能落盘。

三、七步优化过程

第 1 步：给 ORDER BY + WHERE 建联合索引（收益最大）

原始 SQL：

SELECT * FROM orders
WHERE status = 1 AND shop_id = 88
ORDER BY create_time DESC
LIMIT 20;

EXPLAIN 输出：type: ALL, rows: 4128836, Extra: Using where; Using filesort

问题：没有索引，全表扫描 + 内存排序。

索引设计原则（最左前缀 + 排序连续）：

等值条件（WHERE a = ? AND b = ?）  →  放前面，顺序可换
范围条件（> < BETWEEN IN）         →  放等值之后
排序字段（ORDER BY）               →  紧跟等值条件，保证索引有序

对应的索引：

ALTER TABLE orders ADD INDEX idx_shop_status_time (shop_id, status, create_time);

这条索引让 shop_id = 88 AND status = 1 定位到一段，而这段内部已经按 create_time 有序，所以 ORDER BY create_time DESC 不需要额外排序。

结果：type: ref, key: idx_shop_status_time, rows: 1200, Extra: Using where

耗时：1.2 秒 → 38ms。这是全篇收益最大的一步。

为什么 create_time 要放在最后？ 因为排序字段必须在等值条件之后才能保证有序。如果放成 (create_time, shop_id, status)，索引先按时间排序，用 shop_id 过滤就退化成过滤而非定位，ORDER BY 也用不上索引有序性。

第 2 步：覆盖索引，消除回表

加了索引后 Extra 里还有 Using where，说明还要回表。如果查询只需要几个字段：

SELECT id, order_no, amount, create_time FROM orders
WHERE shop_id = 88 AND status = 1
ORDER BY create_time DESC LIMIT 20;

把查询字段都放索引进：

ALTER TABLE orders ADD INDEX idx_shop_status_time_cover
    (shop_id, status, create_time, order_no, amount);

这样查询所需数据全在索引里，不需要回表，Extra 变成 Using index。

结果：38ms → 12ms。

代价是索引变大。二级索引的叶子节点存了更多列，写入开销上升。所以覆盖索引只给「高频 + 字段少」的查询用。

第 3 步：深分页改用游标（延迟关联不够好）

LIMIT 100000, 20 这种深分页是另一个大坑：

-- 需要先扫过 100000 行再丢弃
SELECT * FROM orders WHERE shop_id = 88 ORDER BY id LIMIT 100000, 20;

两种改法：

改法 A：延迟关联（子查询先取 ID）

SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders WHERE shop_id = 88 ORDER BY id LIMIT 100000, 20
) t ON o.id = t.id;

子查询走覆盖索引（只取 id），不用回表扫 10 万行的大字段。实测 800ms → 120ms。

改法 B：游标分页（推荐）

SELECT * FROM orders
WHERE shop_id = 88 AND id > 100000
ORDER BY id LIMIT 20;

用上一页最后一条的 id 做起点，索引直接定位，无论翻到第几页都是 5ms。

缺点是不能跳页。但绝大多数列表场景（滚动加载、无限下拉）本来就不需要跳页。前端改成「加载更多」比后端硬撑深分页划算得多。

第 4 步：LIKE 查询改成前缀匹配或全文索引

-- 前导模糊，索引必失效
SELECT * FROM orders WHERE order_no LIKE '%202611%';

% 在前，B+ 树无法定位起点，必然全扫。

三种改法：

A. 后缀匹配（LIKE '202611%'），能用索引。

B. 反向存储 + 后缀匹配。 建一个 order_no_reverse 字段存反转字符串，查 LIKE REVERSE('%202611') 就变成前缀匹配。

C. 全文索引（字段多、要分词时）

ALTER TABLE orders ADD FULLTEXT INDEX ft_remark (remark) WITH PARSER ngram;
SELECT * FROM orders WHERE MATCH(remark) AGAINST('加急' IN BOOLEAN MODE);

ngram 分词器是中文场景的关键，默认分词器对中文无效。注意全文索引对短词（少于 ngram_token_size，默认 2）不生效。

第 5 步：COUNT(*) 从实时改成近似或缓存

SELECT COUNT(*) FROM orders WHERE shop_id = 88 AND status = 1;

400 万行、status 区分度低，这个 count 要扫几十万行，耗时 400ms。

而它通常只用于「共 N 条」的展示，用户并不要求精确。

改法 A：近似值

SHOW TABLE STATUS LIKE 'orders';   -- 看 Rows 字段，这是估算值

或者用 EXPLAIN 的 rows 估算。误差 5% 以内，用户完全无法感知。

改法 B：维护计数表

-- 写入时同步更新
UPDATE order_stats SET cnt = cnt + 1 WHERE shop_id = 88 AND status = 1;

代价是写入多一次操作，且要考虑一致性。

改法 C：只统计前 N 页

-- 超过 1000 条就显示 "1000+"
SELECT COUNT(*) FROM (
    SELECT 1 FROM orders WHERE shop_id = 88 AND status = 1 LIMIT 1000
) t;

这是最实用的折中：扫描行数被 LIMIT 封顶，耗时恒定在 8ms，用户体验上「1000+」也够用。

第 6 步：避免函数和隐式转换

-- 索引失效：索引列上用了函数
SELECT * FROM orders WHERE DATE(create_time) = '2026-11-05';

-- 改写为范围查询，能用索引
SELECT * FROM orders
WHERE create_time >= '2026-11-05 00:00:00'
  AND create_time <  '2026-11-06 00:00:00';

隐式转换同理：

-- user_id 是 varchar，传了数字 → 索引失效
SELECT * FROM orders WHERE user_id = 10086;
-- 改成
SELECT * FROM orders WHERE user_id = '10086';

这类问题在 EXPLAIN 里表现为「建了索引但 key 是 NULL」，或者在 SHOW WARNINGS 里能看到 Implicit conversion 提示。

第 7 步：读写分离 + 从库分担统计查询

报表、导出、后台列表这类查询走从库，避免和线上交易抢资源。

# Spring Boot + ShardingSphere 示例
spring:
  shardingsphere:
    rules:
      readwrite-splitting:
        data-sources:
          rw:
            write-data-source-name: master
            read-data-source-names: slave0,slave1
            load-balancer-name: round_robin

配合 @Transactional(readOnly = true) 生效（ShardingSphere 靠这个提示路由到从库）。

注意主从延迟：写后立即读会读到旧数据。对一致性要求高的查询强制走主库：

@Transactional(readOnly = false)   // 强制主库
public Order getAfterCreate(Long id) { ... }

四、优化结果

五、索引不是越多越好

优化的诱惑是「每列都加索引」。这会带来三个问题：

1. 写入变慢。 每个 INSERT 要维护所有索引的 B+ 树。一张表 10 个索引，写入开销是 1 个索引的 3～5 倍。

2. 优化器选择困难。 索引太多时，MySQL 优化器可能选错索引。经典现象是「加了索引反而更慢」：

-- 强制用指定索引来验证
SELECT * FROM orders FORCE INDEX (idx_shop_status_time) WHERE ...;

3. 空间浪费。 400 万行的表，一个联合索引可能就是 200 MB。

判断标准：用 information_schema 找从未使用过的索引。

SELECT
    object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'shop'
ORDER BY object_name;

count_star = 0 说明这个索引自 MySQL 启动以来一次都没被用过。重启后统计会清零，所以要在跑了足够久的实例上看。

六、上线前的检查清单

把上面的经验固化成发布前的检查项：

[ ] 所有新 SQL 都跑过 EXPLAIN，type != ALL / index
[ ] 没有 Using filesort / Using temporary（大表）
[ ] rows 和 LIMIT 同一数量级
[ ] WHERE 条件列类型和索引列一致（无隐式转换）
[ ] 索引列上没有函数包裹
[ ] LIKE 没有前导 %
[ ] 分页用游标或延迟关联，LIMIT offset < 10000
[ ] COUNT(*) 有 LIMIT 封顶或走缓存
[ ] 联合索引顺序符合「等值 → 范围 → 排序」
[ ] 事务里没有网络调用

小结

慢查询治理的收益遵循帕累托分布：一条 SQL 的索引，往往抵得上十台服务器。

按收益排序的经验是：

1. 加对联合索引（等值 → 范围 → 排序）— 通常 20～50 倍提升
2. 消除函数/隐式转换 — 10～30 倍
3. 深分页改游标 — 常数级优化，与页码无关
4. 覆盖索引减少回表 — 2～4 倍
5. COUNT 限流 — 50 倍但只针对特定查询
6. 读写分离 — 分散压力，不解决单查询慢

前三条做完，绝大部分接口都能进 100ms。剩下的靠业务侧改造（缓存、异步、预计算），已经不是 SQL 层面的问题了。