攻克慢SQL：24小时解决30条慢SQL的实现方法
一、核心优化思想
- 减少数据扫描量
数据库查询的物理 I/O 和内存消耗与扫描的数据量正相关。通过索引、分区等手段缩小扫描范围，可直接降低磁盘读取和内存占用。
- 降低计算复杂度
复杂操作（如全表扫描、多表 JOIN）会占用大量 CPU 资源。简化计算逻辑可减少查询时间，提升并发处理能力。
- 分步处理复杂查询
单次复杂查询容易导致锁竞争和资源独占。分步操作利用应用层灵活性，降低数据库单次负载。
二、索引优化策略
2.1 索引设计原则
2.1.1 最左匹配原则
（1）索引生效的查询
-- 联合索引 (a, b, c)
-- 有效：按索引顺序使用 a 和 b
WHERE a = 1 AND b = 2;
-- 无效：跳过 a，索引无法使用
WHERE b = 2;（2）范围查询影响索引
-- `b` 是范围查询，`c` 无法使用索引
WHERE a = 100 AND b > 200 AND c = 300;
-- 同样的问题
WHERE a = 100 AND b BETWEEN 200 AND 300;索引使用情况如下：
| 字段 | 是否使用索引 | 说明 |
|---|---|---|
| a | ✅ 是 | 等值查询，索引可用 |
| b | ✅ 是 | 范围查询，索引可用 |
| c | ❌ 否 | 受范围查询影响，索引失效 |
解决方案：尽量将范围查询放在 WHERE 末尾，避免影响后续索引。
（3）IN 查询
WHERE a IN (100, 101) AND b IN (200, 201) AND c = 300;索引使用情况如下：
| 字段 | 是否使用索引 | 说明 |
|---|---|---|
| a | ✅ 是 | 等值查询 |
| b | ✅ 是 | IN 属于等值查询 |
| c | ✅ 是 | 仍然是等值查询 |
（4）LIKE 非前缀匹配导致索引失效
WHERE a = 100 AND b LIKE '%abc%' AND c = 300;索引使用情况如下：
| 字段 | 是否使用索引 | 说明 |
|---|---|---|
| a | ✅ 是 | 等值查询 |
| b | ❌ 否 | LIKE '%abc%' 无法使用索引 |
| c | ❌ 否 | 受 b 影响，索引被截断 |
解决方案：使用 LIKE 'abc%' 进行前缀匹配，可保持索引可用。
小结
- **尽量使用等值查询 (
=或IN)**，避免范围查询影响索引。 - 范围查询 (
> < BETWEEN LIKE '%abc%') 应尽量放在WHERE末尾，减少索引失效风险。 - **
LIKE查询应使用前缀匹配 (LIKE 'abc%')**，避免索引失效。
2.1.2 覆盖索引
若查询字段全部包含在索引中（即覆盖索引），数据库可直接从索引树获取数据，无需回表查询主键索引。
回表代价：若未覆盖字段，需通过主键 ID 回主索引读取完整数据，增加磁盘 I/O。
-- 覆盖索引示例
CREATE INDEX idx_cover ON users(city, age);
SELECT city, age FROM users WHERE city = 'Shanghai'; -- ✅ 无需回表
SELECT name FROM users WHERE city = 'Shanghai'; -- ❌ 需回表获取name2.2 索引失效场景
2.2.1 隐式类型转换
若索引字段类型与查询条件类型不一致（如字符串字段匹配数字），数据库需隐式转换类型，导致无法使用索引。
-- ❌ 索引失效（supplier_id为VARCHAR）
SELECT * FROM orders WHERE supplier_id = 7067;
-- ✅ 正确写法
SELECT * FROM orders WHERE supplier_id = '7067';2.2.2 函数操作索引列
对索引列使用函数（如 YEAR(date_column)）会破坏 B+树的有序性，导致索引失效。
-- ❌ 索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2023;
-- ✅ 优化写法
SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';三、查询语句优化
3.1 分页优化
LIMIT offset, N需要扫描前offset + N行数据，偏移量越大性能越差。- 游标分页：利用索引有序性，直接定位起始位置，减少扫描行数。
-- ❌ 低效分页（扫描100万+20行）
SELECT * FROM orders LIMIT 1000000, 20;
-- ✅ 高效分页（基于主键游标）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;3.2 用 JOIN 替代子查询
- 关联子查询（Correlated Subquery）对外层查询的每一行执行一次，时间复杂度为 O(N²)。
- JOIN 优化：将嵌套查询扁平化，利用哈希连接（Hash Join）或嵌套循环连接（Nested Loop Join）降低复杂度。
-- ❌ 时间复杂度 O(N²)
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id)
FROM users u;
-- ✅ 优化为 JOIN（时间复杂度 O(N)）
SELECT u.name, COUNT(o.id)
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;3.3 减少结果集
- 在查询优化阶段，尽早将过滤条件（WHERE、JOIN ON）应用到数据源，减少中间结果集大小。
-- ❌ 先JOIN后过滤（处理全量数据）
SELECT u.name, o.amount
FROM users u LEFT JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Shanghai';
-- ✅ 先过滤后JOIN（减少JOIN数据量）
SELECT u.name, o.amount
FROM (SELECT * FROM users WHERE city = 'Shanghai') u
LEFT JOIN orders o ON u.id = o.user_id;3.4 减少关联查询
分步查询 + 应用层组装
本文系作者在时代Java发表，未经许可，不得转载。
如有侵权，请联系nowjava@qq.com删除。
编辑于
关注时代Java