4、慢SQL排查与优化：慢查询日志分析、执行计划解读、索引优化策略、分库分表场景

各位同学，咱们今天聊点实在的。

慢SQL，说白了就是数据库里的「慢性病」。平时不疼不痒，流量一上来就让你系统直接瘫痪。我见过太多团队，上线前没查过慢查询，结果大促当天数据库CPU飙到100%，那叫一个酸爽。

今天我就把排查和优化的完整套路，掰开了讲给你听。

4.1 慢查询日志分析：先找到「病根」

你想想看，连问题在哪儿都不知道，怎么优化？

第一步，打开慢查询日志。MySQL默认是关的，得手动开。

-- 查看当前是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 开启慢查询日志（临时生效）
SET GLOBAL slow_query_log = ON;

-- 设置阈值，超过2秒就算慢
SET GLOBAL long_query_time = 2;

-- 指定日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

我个人习惯把阈值设成1秒。为什么？因为线上业务，超过1秒的查询已经算「事故」了。

日志长什么样？我截一段给你看：

# Time: 2025-03-15T10:23:45.123456Z
# User@Host: root[root] @ localhost []
# Query_time: 3.456789  Lock_time: 0.000123 Rows_sent: 1000  Rows_examined: 500000
SET timestamp=1742029425;
SELECT * FROM orders WHERE status = 'PENDING' ORDER BY create_time DESC;

这里有几个关键字段：

  • Query_time：查询耗时。3.45秒，妥妥的慢查询。
  • Rows_examined：扫描了多少行。50万行！
  • Rows_sent：实际返回了多少行。才1000行。

核心指标：Rows_examined / Rows_sent 的比值。比值越大，说明扫描了大量无用数据，索引大概率有问题。

我曾经接手过一个项目，慢查询日志里全是同一个SQL，扫描了200万行只返回10条。结果呢？加了个索引，查询时间从8秒降到了0.01秒。老板当场给我加了鸡腿。

4.2 执行计划解读：看懂数据库的「内心戏」

找到慢SQL了，下一步就是分析它为什么慢。

用 EXPLAIN 命令，让数据库告诉你它是怎么执行这条SQL的。

EXPLAIN SELECT * FROM orders WHERE status = 'PENDING' ORDER BY create_time DESC;

输出结果长这样：

id select_type table type possible_keys key rows Extra
1 SIMPLE orders ALL NULL NULL 500000 Using filesort

我来给你翻译翻译：

  • type = ALL：全表扫描。这是最差的情况，没有之一。
  • possible_keys = NULL：没有可用的索引。
  • rows = 500000：预估扫描50万行。
  • Extra = Using filesort：排序没用到索引，在磁盘上排序，慢上加慢。

避坑指南：我曾经见过有人只看 type 字段，忽略了 Extra 里的 Using filesort。结果加了索引后 type 变成了 ref，但排序还是慢。记住，Extra 里的信息同样致命。

type 字段的优劣排序（从好到差）：

  • system/const：最多匹配一行，神仙速度。
  • eq_ref：主键或唯一索引查找，非常快。
  • ref：普通索引查找，还不错。
  • range：索引范围扫描，可以接受。
  • index：索引全扫描，比全表好一点。
  • ALL：全表扫描，赶紧优化！

4.3 索引优化策略：给数据建「目录」

说白了，索引就是书的目录。没有目录，你只能一页一页翻。

策略一：复合索引的「最左前缀」原则

假设我们建了这样一个索引：

ALTER TABLE orders ADD INDEX idx_status_create (status, create_time);

那么以下查询能用到索引：

-- 能用，因为 status 在最左边
SELECT * FROM orders WHERE status = 'PENDING';

-- 能用，因为 status 和 create_time 都在索引中
SELECT * FROM orders WHERE status = 'PENDING' ORDER BY create_time DESC;

-- 不能用！因为跳过了 status，直接查 create_time
SELECT * FROM orders WHERE create_time > '2025-01-01';

嗯，这里要注意：复合索引的顺序很重要。把区分度高的列放前面，把经常用于排序的列放后面。

策略二：覆盖索引

如果查询的字段都在索引里，数据库就不需要回表查数据行。这叫「覆盖索引」，性能极佳。

-- 假设索引是 idx_status_create (status, create_time)
-- 这个查询只需要扫描索引，不需要回表
SELECT status, create_time FROM orders WHERE status = 'PENDING';

个人经验：我优化过一个报表查询，原来要查20个字段，回表次数多到爆炸。后来我把查询改成只查索引里的5个字段，速度提升了10倍。当然，业务逻辑也得配合调整。

策略三：避免索引失效

这些操作会让索引失效，你想想看是不是踩过坑：

  • 对索引列使用函数：WHERE DATE(create_time) = '2025-01-01' → 改成 WHERE create_time >= '2025-01-01' AND create_time < '2025-01-02'
  • 隐式类型转换：WHERE user_id = '123'（user_id 是数字类型）
  • LIKE 以通配符开头：WHERE name LIKE '%张三'

4.4 分库分表场景：当索引也救不了你的时候

说实话，索引不是万能的。当单表数据量超过千万甚至亿级，索引的 B+ 树深度会增加，磁盘IO也会变多。这时候，就得考虑分库分表了。

什么时候该分？

  • 单表数据量超过 500万 ~ 1000万行，且查询性能明显下降。
  • 索引优化后，慢查询依然存在。
  • 写入压力太大，单库扛不住。

分表策略

我常用的两种方式：

  • 水平分表：按某个字段（如 user_id）取模，把数据分散到多个表中。比如 user_id % 16，分成 16 张表。
  • 垂直分表：把大字段（如 text、blob）拆到单独的表中，减少单行数据量。
-- 水平分表示例：按 user_id 分 16 张表
-- 查询时计算表名
String tableName = "orders_" + (userId % 16);
SELECT * FROM orders_3 WHERE user_id = 12345;

我曾经踩过的坑：分表后，跨表查询成了噩梦。比如要查某个时间段的订单，得遍历所有分表。后来我引入了 Elasticsearch 做二级索引，专门处理这种跨表查询。记住，分表不是银弹，它只是把问题转移了。

分库策略

分库解决的是写入压力。把不同的业务数据放到不同的数据库实例上。比如订单库、用户库、商品库分开。

但分库后，事务就成了大问题。分布式事务的解决方案有：

  • 两阶段提交（2PC）：强一致性，但性能差。
  • TCC（Try-Confirm-Cancel）：业务侵入性强。
  • 最终一致性（消息队列）：最常用，性能好。

我个人推荐的做法是：能不分的尽量不分。先做索引优化，再做缓存，最后才考虑分库分表。因为分库分表带来的复杂度，有时候比慢查询本身更让人头疼。

总结一下今天的核心思路：

  1. 打开慢查询日志，找到「病根」SQL。
  2. 用 EXPLAIN 分析执行计划，看 type、rows、Extra。
  3. 根据分析结果，优化索引（复合索引、覆盖索引、避免失效）。
  4. 索引搞不定，再考虑分库分表，但要做好复杂度评估。

好了，这一章的内容就到这儿。下一章咱们聊聊「连接池与线程池调优」，那又是另一个有意思的话题。

← 上一章 📖 返回目录 下一章 →