4、SQL查询优化：慢查询分析、索引使用、避免N+1问题

SQL 查询优化这件事，说起来不复杂，但真到了线上环境，你才会发现一个慢查询能把整个接口拖垮。我自己的经验是：80% 的性能问题，根源都在 SQL 上。这一节我们就一步步拆解——怎么找到慢查询、怎么用好索引、怎么干掉 N+1 这种“经典坑”。

4.1 慢查询分析——先找到敌人，再开枪

你想想看，如果不知道哪个查询慢，后面所有优化都是瞎忙活。所以第一步永远是：定位慢查询。

4.1.1 开启慢查询日志

MySQL 里可以用以下配置来捕获执行时间超过阈值的 SQL：

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
-- 设定阈值（单位：秒），我习惯设 1 秒
SET GLOBAL long_query_time = 1;
-- 日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

拿到日志后，用 mysqldumpslow 工具聚合分析，重点关注那些出现频率高、总耗时长的 SQL。我个人还会配合 Performance Schema 去查锁等待和 IO 开销。

4.1.2 用 EXPLAIN 解读执行计划

找到疑似慢的 SQL 后，贴到 EXPLAIN 里看执行计划。其实只要盯住这几个字段就够了：

字段关注点典型问题
type访问方式。ALL 最差，index 次之，ref/eq_ref/const 好出现 ALL 说明没用到索引，全表扫
rows预估扫描行数。越大越慢和实际返回行数差太多，说明统计信息不准
ExtraUsing filesort、Using temporary 都是“危险信号”排序或分组没用到索引，触发了磁盘临时表
key实际使用的索引。NULL 代表无索引明明有索引却没用？可能索引失效了
我的习惯： 每次写完一个新 SQL，我都顺手跑一遍 EXPLAIN。养成肌肉记忆，后面省大功夫。

4.2 索引使用——用好它，而不是滥用它

索引就像书的目录——没有它你翻到死，但页页都加目录你也受不了。下面几个原则是我在实际项目中反复验证过的。

4.2.1 索引类型与适用场景

  • B+树索引：MySQL 默认，最适合范围查询、排序、等值匹配。我记得有一次线上死锁，就是因为唯一索引和普通索引的间隙锁冲突，后来改成了非唯一索引才解决。
  • 哈希索引：Memory 引擎专用，等值查询极快，但不支持范围。审计接口里很少用。
  • 全文索引：适合大文本模糊搜索，比如查日志内容。不要对 varchar 建普通索引然后 LIKE '%xxx%'——那肯定慢。
  • 空间索引：GIS 场景专用，普通业务用不上。

4.2.2 最左前缀法则——联合索引的灵魂

建联合索引时，最常用的条件放最左边。假设表 audit_log 有联合索引 (user_id, action, created_at)：

-- ✅ 能命中
SELECT * FROM audit_log WHERE user_id = 123 AND action = 'login';
-- ✅ 也能命中（最左前缀匹配到 user_id）
SELECT * FROM audit_log WHERE user_id = 123;
-- ❌ 不走索引（跳过了最左列）
SELECT * FROM audit_log WHERE action = 'login';
-- ❌ 范围查询后面的列失效（created_at 也不能走索引过滤）
SELECT * FROM audit_log WHERE user_id = 123 AND action LIKE 'log%' AND created_at > '2024-01-01';

嗯，这里要注意：范围查询（>, <, BETWEEN, LIKE 前缀模糊）会阻断后续列走索引。所以设计联合索引时，要把范围条件放在最后面。

4.2.3 覆盖索引——少回表就是快

当查询需要的所有列都在索引中时，MySQL 直接通过索引返回结果，不用回表查聚簇索引。这就是覆盖索引。

-- 假设有 (user_id, action, created_at) 联合索引
-- 如果只查这三列，就是覆盖索引查询
EXPLAIN SELECT user_id, action FROM audit_log WHERE user_id = 123;
-- Extra 字段会显示 "Using index"，表示用到了覆盖索引

我在优化报表接口时，经常故意把查询字段塞进索引里，避免回表 IO。代价是索引体积会变大，需要权衡写入性能与查询性能。

我曾经踩过的坑： 给超长字符串字段（如 TEXT）建前缀索引，以为能加速。结果因为区分度不够，rows 还是很大，查询反而更慢。后来改成只对前 10 个字符做前缀索引，才真正有效。记住：高选择性的列才适合建索引。

4.3 避免 N+1 问题——ORM 的隐形杀手

N+1 问题太常见了，尤其是用 ORM 框架（MyBatis、Hibernate、JPA）的时候。说白了就是：做一次查询拿到 N 条主记录，然后循环里又发 N 次查询拿关联记录。总共 1+N 次查询，网络延迟和数据库压力全上去了。

4.3.1 典型场景

-- 假设有两个表：用户表 user，操作日志表 audit_log
-- 最初写法（伪代码）：
List<User> users = userDao.findAll();               // 1 次查询
for (User user : users) {
    List<AuditLog> logs = auditLogDao.findByUserId(user.getId());  // N 次查询
    // 处理...
}
-- 如果 users 有 1000 条，数据库就要执行 1001 次查询

4.3.2 解决方案

  • 批量查询 + IN 条件：先查出所有用户 ID，再一次性查关联表。
  • 子查询 / JOIN：直接用一条 SQL 搞定。但要注意 JOIN 可能导致结果集膨胀，且要对大表谨慎。
  • ORM 懒加载改急加载：Hibernate 的 @ManyToOne(fetch = FetchType.EAGER) 或 MyBatis 的 collection 标签里用嵌套查询+延迟加载时，考虑改成关联查询。
  • 数据冗余 / 缓存：对频繁读取的关联数据，比如用户名字，可以冗余到审计日志表中，避免连表查询。
我个人推荐的做法： 审计接口一般对实时性要求高、但写少读多。所以优先采用 批量查询 + 缓存 的方式。先用 IN 查出所有关联记录，然后按 userId 分组放到 Map 里，循环时直接从内存拿，避免多次数据库交互。

4.4 本章知识体系全貌

下面的 SVG 图把核心流程串起来了：从定位慢查询开始，到优化索引、最后根治 N+1。你可以把它当一张“检查清单”贴在工作台上。

SQL查询优化核心流程 慢查询分析 日志+EXPLAIN 索引优化 类型+最左前缀+覆盖索引 避免N+1问题 批量查询/JOIN/缓存 B+树/BTree索引 覆盖索引 最左前缀法则 范围条件放最后 索引选择性 高区分度列建索引 批量查询 IN条件+Map内存 JOIN替代循环 注意结果集膨胀 数据冗余/缓存 常用字段冗余存储 慢日志配置 long_query_time=1s EXPLAIN分析 type/rows/Extra Performance Schema IO/锁等待分析 优化建议： 先定位后动手

总结起来很简单：先分析，再索引，最后治理 N+1。我个人在给团队做审计接口优化时，就是用这张图当检查清单，一个节点一个节点过，效果非常稳。你对着它做一次全链路扫描，大部分 SQL 性能问题都能暴露出来。

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