3. SQL查询性能调优：慢SQL的定位与优化
说实话，在MES系统里，SQL慢查询是我最常遇到的性能杀手。你想想看，一条SQL慢个几秒，在产线上可能就是几十个工位同时卡住。我见过最夸张的一次，一个批次查询跑了40多秒，整个车间都在骂娘。
这一章，我就把压箱底的经验掏出来。从定位慢SQL开始，到索引优化、分页优化、N+1查询的坑，再到Explain执行计划的解读。全是实战干货。
3.1 慢SQL的定位：先找到敌人
优化之前，你得先知道哪些SQL慢。我个人习惯用两种方式：
- 开启慢查询日志：MySQL里设置
long_query_time = 1，超过1秒的SQL全记录下来 - 实时监控：用
SHOW FULL PROCESSLIST看当前正在执行的SQL
实战命令：
-- 查看慢查询是否开启
SHOW VARIABLES LIKE 'slow_query_log';
-- 设置慢查询阈值（单位：秒）
SET GLOBAL long_query_time = 1;
-- 查看当前正在运行的SQL
SHOW FULL PROCESSLIST;我在项目中遇到过一个问题：生产环境突然变慢，但慢查询日志里啥也没有。后来发现是阈值设成了10秒，而实际慢SQL只跑了3秒。嗯，这个坑我踩过，你注意一下。
3.2 索引优化：用对索引，事半功倍
索引这东西，用好了是神器，用错了是累赘。我见过太多人给每个字段都加索引，结果写入慢得要死。
3.2.1 索引的基本原则
- 最左前缀原则：联合索引 (a, b, c)，查询条件必须从a开始
- 区分度高的字段放前面：比如订单号比状态字段更适合做索引
- 避免索引失效：不要在索引列上做函数操作
我的经验：MES系统里，工单号、批次号、设备ID这些字段，区分度极高，优先建索引。状态字段（如'已完成'、'进行中'）区分度低，索引效果很差。
3.2.2 索引优化的实战案例
有一次，一个查询工单列表的接口特别慢。原始SQL是这样的：
-- 原始SQL：跑了8秒
SELECT * FROM work_order
WHERE status = 'completed'
AND create_time BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY create_time DESC
LIMIT 20;我一看，status字段区分度太低，索引基本没用。优化方案是建一个联合索引：
-- 创建联合索引
ALTER TABLE work_order ADD INDEX idx_status_time (status, create_time);
-- 优化后：跑了0.02秒
SELECT * FROM work_order
WHERE status = 'completed'
AND create_time BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY create_time DESC
LIMIT 20;为什么会这样？因为联合索引把status和create_time绑在一起，MySQL可以直接定位到目标数据，不用全表扫描。
3.3 分页查询优化：别让LIMIT拖垮你
MES系统里分页查询太常见了。但很多人不知道，LIMIT 100000, 20 这种写法，其实MySQL要扫描10万行数据，然后扔掉前99980行。
我曾经优化过一个报表查询，翻到第100页时，响应时间从3秒飙升到30秒。原因就是深分页。
3.3.1 优化方案一：子查询分页
-- 优化前（深分页）
SELECT * FROM work_order
ORDER BY id
LIMIT 100000, 20;
-- 优化后（子查询）
SELECT * FROM work_order
WHERE id > (
SELECT id FROM work_order
ORDER BY id
LIMIT 100000, 1
)
ORDER BY id
LIMIT 20;这个技巧的核心是：先用子查询找到第100001条记录的ID，然后直接从那个ID开始取20条。避免了扫描大量无用数据。
3.3.2 优化方案二：游标分页
如果业务允许，我建议用游标分页。就是记住上一页最后一条记录的ID，下一页直接查比它大的：
-- 第一页
SELECT * FROM work_order
ORDER BY id
LIMIT 20;
-- 第二页（传入上一页最后一个ID：100）
SELECT * FROM work_order
WHERE id > 100
ORDER BY id
LIMIT 20;注意：游标分页只适用于按唯一递增字段排序的场景。如果按时间排序，且时间有重复值，需要额外处理。
3.4 避免N+1查询：ORM的隐形杀手
N+1查询，说白了就是：先查一次主表拿到N条记录，然后循环N次查关联表。在MES系统里，这问题特别常见。
我记得有一次排查一个工单详情页的慢查询，发现一个页面加载了200多次SQL。原因是ORM框架的懒加载，每次访问关联对象都发一次查询。
3.4.1 问题复现
-- 伪代码：N+1查询
List<WorkOrder> orders = workOrderMapper.findAll(); // 1次查询
for (WorkOrder order : orders) {
List<OperationLog> logs = order.getOperationLogs(); // N次查询
}3.4.2 优化方案：批量查询
-- 优化后：2次查询搞定
List<WorkOrder> orders = workOrderMapper.findAll(); // 1次
List<Long> orderIds = orders.stream().map(WorkOrder::getId).collect(toList());
List<OperationLog> logs = operationLogMapper.findByOrderIds(orderIds); // 1次
// 然后在内存中组装避坑指南：我曾经在MyBatis的@BatchSize注解。
3.5 使用Explain分析执行计划
Explain是MySQL的"照妖镜"。一条SQL慢不慢，为什么慢，用它一看就明白。
3.5.1 Explain的关键字段
| 字段 | 含义 | 重点关注 |
|---|---|---|
| type | 访问类型 | ALL（全表扫描）要避免，至少到range或ref |
| key | 使用的索引 | NULL表示没用到索引 |
| rows | 扫描的行数 | 越小越好，超过10万就要警惕 |
| Extra | 额外信息 | Using filesort、Using temporary 是性能杀手 |
3.5.2 实战分析
-- 分析一条慢SQL
EXPLAIN SELECT * FROM work_order
WHERE status = 'pending'
ORDER BY create_time DESC
LIMIT 20;
-- 结果解读
-- type: ALL（全表扫描！）
-- key: NULL（没用到索引）
-- rows: 500000（扫描了50万行）
-- Extra: Using filesort（文件排序，性能差）看到这个结果，我立马知道问题在哪：status字段没索引，而且排序也没走索引。优化方案就是前面说的，建一个 (status, create_time) 联合索引。
我的习惯：每次上线前，我会把核心SQL都跑一遍Explain。如果看到type=ALL或者Extra里有Using filesort，我会直接打回重写。这个习惯帮我避免了很多线上事故。
3.6 本章小结
SQL优化其实没那么玄乎。记住几个要点：
- 慢SQL定位：慢查询日志 + SHOW PROCESSLIST
- 索引优化：联合索引、最左前缀、避免索引失效
- 分页优化：子查询分页或游标分页，别用深分页
- N+1查询：用批量查询替代循环查询
- Explain分析：重点关注type、key、rows、Extra
这些技巧，我在MES系统里反复用过。说实话，80%的慢SQL问题，用这几招就能解决。剩下的20%，要么是数据量太大需要分库分表，要么是业务逻辑设计不合理。那些就是更高阶的话题了。