第72课：SQL 优化

🎯 学习目标

• 理解 SQL 优化的核心是减少扫描行数、减少排序临时表、减少回表和减少不必要查询。
• 掌握索引设计、执行计划、慢查询日志和常见索引失效场景。
• 能识别深分页、N+1 查询、隐式类型转换、函数条件、低选择性索引等问题。
• 能从 Java 应用角度优化 ORM 查询、事务范围和连接池使用。
• 建立“慢查询发现 -> EXPLAIN 分析 -> 改写验证”的优化流程。

---

📖 一、SQL 优化先看什么

看到接口慢，不要直接改 SQL。先确认：

慢的是哪条 SQL？
执行频率是多少？
扫描行数是多少？
是否走索引？
是否排序、临时表、回表？
是否因为锁等待而慢？
Java 侧是否 N+1 查询？

SQL 优化目标通常是：

减少扫描行数
减少返回列
减少 JOIN 数据量
减少排序和临时表
减少事务持锁时间
减少应用层重复查询

---

📖 二、慢查询日志

MySQL 中可以开启慢查询：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

查看配置：

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

分析慢日志：

mysqldumpslow -s t -t 10 slow.log

更现代的做法是接入数据库监控平台，持续观察：

平均耗时
P95/P99
执行次数
扫描行数
返回行数
锁等待

一条执行 5 秒但一天一次的 SQL，优先级可能低于一条执行 100ms 但每秒 500 次的 SQL。

---

📖 三、EXPLAIN 执行计划

EXPLAIN SELECT id, name FROM users WHERE email = 'alice@example.com';

重点字段：

字段	含义	关注点
type	访问类型	ALL 通常表示全表扫描
possible_keys	可能使用的索引	有索引不代表会用
key	实际使用的索引	是否符合预期
rows	预估扫描行数	越少越好
filtered	过滤比例	判断条件筛选效果
Extra	附加信息	filesort、temporary、using index

常见 type 从好到差：

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

ALL 不是绝对错误，小表全表扫描可能没问题；但大表高频 SQL 出现 ALL 就要重点看。

---

📖 四、索引设计

1. 单列索引

CREATE INDEX idx_users_email ON users(email);

适合高选择性字段，例如邮箱、手机号、订单号。

2. 联合索引

CREATE INDEX idx_orders_user_status_time ON orders(user_id, status, created_at);

适合：

SELECT id, amount
FROM orders
WHERE user_id = 1001
  AND status = 'PAID'
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC;

联合索引遵循最左前缀原则。索引顺序要根据查询条件设计，而不是随便排列。

3. 覆盖索引

如果查询字段都在索引里，可以减少回表：

CREATE INDEX idx_users_email_name ON users(email, name);

SELECT name FROM users WHERE email = 'alice@example.com';

执行计划 Extra 中可能出现 Using index。

---

📖 五、索引失效场景

1. 对字段使用函数

错误：

SELECT * FROM orders WHERE DATE(created_at) = '2026-06-30';

优化：

SELECT * FROM orders
WHERE created_at >= '2026-06-30 00:00:00'
  AND created_at <  '2026-07-01 00:00:00';

2. 隐式类型转换

如果 phone 是字符串：

SELECT * FROM users WHERE phone = 13800138000;

应写成：

SELECT * FROM users WHERE phone = '13800138000';

3. 左模糊 LIKE

WHERE name LIKE '%张'

普通 B+Tree 索引通常无法有效利用。可以考虑搜索引擎、倒排索引或业务上改为前缀匹配。

4. 低选择性字段

性别、状态这种字段取值少，单独建索引不一定有效。通常要和其他字段组成联合索引。

---

📖 六、查询改写

1. 避免 SELECT *

SELECT id, name, email FROM users WHERE id = 1;

少查列能减少 IO、网络传输和对象映射成本。

2. 深分页优化

低效：

SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

优化一：基于游标：

SELECT * FROM orders
WHERE id > 1000000
ORDER BY id
LIMIT 20;

优化二：延迟关联：

SELECT o.*
FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;

3. 批量查询替代循环查询

SELECT * FROM orders WHERE user_id IN (1, 2, 3, 4);

不要在 Java 循环里一条一条查。

---

📖 七、N+1 查询

错误示例：

List<User> users = userRepository.findAll();
for (User user : users) {
    List<Order> orders = orderRepository.findByUserId(user.getId());
    user.setOrders(orders);
}

如果有 100 个用户，就会执行 1 + 100 次查询。

优化：

List<User> users = userRepository.findAll();
List<Long> userIds = users.stream().map(User::getId).toList();
List<Order> orders = orderRepository.findByUserIdIn(userIds);

Map<Long, List<Order>> ordersByUserId = orders.stream()
    .collect(Collectors.groupingBy(Order::getUserId));

JPA 中也可以使用：

@Query("select u from User u left join fetch u.orders where u.id in :ids")
List<User> findWithOrders(@Param("ids") List<Long> ids);

---

📖 八、事务和锁

慢 SQL 不一定是执行慢，也可能是等锁。

常见问题：

事务太大
事务中包含远程调用
更新语句没有命中索引导致锁范围扩大
热点行频繁更新
批量任务和在线请求争抢数据

错误示例：

@Transactional
public void pay(Order order) {
    orderRepository.updateStatus(order.getId(), "PAYING");
    paymentClient.pay(order); // 远程调用放在事务里
    orderRepository.updateStatus(order.getId(), "PAID");
}

远程调用慢会延长事务持锁时间。应重新设计事务边界，必要时使用状态机、消息队列和补偿机制。

---

⚠️ 九、常见陷阱

1. 索引越多越好

索引会占空间，也会降低写入速度。每次 insert/update/delete 都可能维护索引。

2. 只看是否走索引

走了索引不代表快。如果扫描大量索引记录再回表，仍然可能慢。

3. ORM 隐藏 SQL

JPA/MyBatis 写起来像对象操作，但最终执行的是 SQL。必须打开 SQL 日志或使用监控查看真实 SQL。

4. 在大事务里做复杂查询

事务时间越长，锁持有越久，冲突概率越高。

---

🆚 十、Java vs C 对比

维度	C 服务	Java 后端
数据访问	可能直接调用数据库 C API	JDBC、MyBatis、JPA
SQL 可见性	通常显式 SQL	ORM 可能隐藏 SQL
对象映射	手动映射结构体	自动映射为对象
性能风险	SQL 本身	SQL + ORM + 连接池 + 事务代理

Java 后端优化 SQL 时，不能只看数据库，也要看 ORM、连接池和事务边界。

---

💡 十一、最佳实践

• 慢查询必须结合执行次数和扫描行数判断优先级。
• 高频查询优先建立合适联合索引，而不是堆单列索引。
• 查询只返回必要列，避免 SELECT *。
• 深分页优先使用游标分页。
• Java 侧避免 N+1 查询，能批量就批量。
• 事务内不要做远程调用和耗时计算。
• 每次优化都用 EXPLAIN 和压测验证。
• 索引变更要评估写入成本和磁盘成本。

---

🎓 小结

SQL 优化的关键是减少数据库无效工作：少扫行、少排序、少回表、少等待锁、少重复查询。Java 后端工程师必须同时理解数据库执行计划和应用层访问模式，才能真正解决接口慢的问题。

---

🧭 十二、SQL 优化检查清单

分析一条慢 SQL 时，按顺序检查：

1. 是否真的是这条 SQL 慢，而不是网络或锁等待？
2. 执行频率是多少？
3. 是否有合适索引？
4. EXPLAIN 中 type、key、rows、Extra 是否符合预期？
5. WHERE 条件是否让索引失效？
6. ORDER BY/GROUP BY 是否产生 filesort 或 temporary？
7. 是否 SELECT * 返回了过多列？
8. 是否深分页？
9. Java 侧是否 N+1 查询？
10. 事务是否持锁太久？

一个可复用的优化记录模板：

SQL：
执行频率：
优化前耗时：
优化前 EXPLAIN：
问题判断：
优化方式：
优化后耗时：
优化后 EXPLAIN：
风险：
回滚方案：

这样团队可以积累可复盘的 SQL 优化经验，而不是只靠口头结论。

---

📌 十三、索引设计经验

联合索引顺序通常参考：

等值条件字段
范围条件字段
排序字段
覆盖查询需要的字段

示例：

WHERE tenant_id = ?
  AND status = ?
  AND created_at >= ?
ORDER BY created_at DESC

可以考虑：

CREATE INDEX idx_order_tenant_status_time
ON orders(tenant_id, status, created_at);

但索引设计必须结合真实查询，不要为了“可能会用”提前建一堆索引。

---

🔍 十四、自测问题

EXPLAIN 中 type=ALL 一定是坏事吗？
最左前缀原则是什么意思？
为什么对索引字段使用函数可能导致索引失效？
为什么 SELECT * 会影响性能？
深分页为什么慢？
N+1 查询在 Java 代码中通常长什么样？
为什么事务中远程调用会放大锁问题？
为什么索引不是越多越好？