15个SQL优化技巧，让你的查询性能飙升300%！
在系统性能优化中，SQL调优往往是投入产出比最高的环节。相比代码重构，SQL优化通常改动小、见效快。本文将用最精炼的方式，分享15个实战验证的SQL优化技巧，助你轻松应对高并发场景。
1 避免SELECT * 陷阱
1.1 问题本质
- 资源浪费：查询无用字段消耗额外IO、内存和CPU
- 索引失效：
SELECT * 无法利用覆盖索引，强制回表查询 - 网络瓶颈：传输冗余数据增加网络延迟
1.2 优化方案
-- 反例
SELECT * FROM user WHERE id = 100;
-- 正例（只取必要字段）
SELECT name, phone FROM user WHERE id = 100;
关键点：永远只查询业务必需的字段，覆盖索引可提升3-5倍性能
2 UNION ALL 替代 UNION
2.1 性能差异
| 操作 | 是否去重 | CPU消耗 | 适用场景 |
|---|
UNION | 是 | 高 | 严格去重要求 |
UNION ALL | 否 | 低 | 90%常规场景 |
2.2 优化实践
-- 反例（不必要的去重）
(SELECT id, name FROM user WHERE dept_id = 1)
UNION
(SELECT id, name FROM user WHERE dept_id = 2);
-- 正例（直接合并结果集）
(SELECT id, name FROM user WHERE dept_id = 1)
UNION ALL
(SELECT id, name FROM user WHERE dept_id = 2);
3 小表驱动大表原则
3.1 驱动表选择
| 场景 | 推荐写法 | 原理说明 |
|---|
| 左表大 + 右表小 | IN | 先执行小表查询作为过滤条件 |
| 左表小 + 右表大 | EXISTS | 用小表逐行匹配大表 |
3.2 优化示例
-- 场景：10000条订单(大表) + 100个有效用户(小表)
-- 正确写法（小表user驱动大表order）：
SELECT * FROM order
WHERE user_id IN (SELECT id FROM user WHERE status = 1);
-- 错误写法（大表驱动小表）：
SELECT * FROM order o
WHERE EXISTS (
SELECT 1 FROM user u
WHERE u.id = o.user_id AND status = 1
);
4 批量操作代替循环
4.1 性能对比
| 操作方式 | 1000条数据耗时 | 网络往返次数 |
|---|
| 循环单条插入 | 3200ms | 1000次 |
| 批量插入 | 45ms | 1次 |
4.2 优化方案
// 反例：逐条插入
for(Order order : list) {
orderMapper.insert(order); // 1000次数据库交互
}
// 正例：批量插入（MyBatis示例）
orderMapper.batchInsert(list); // 1次交互
-- 生成SQL
INSERT INTO order(id, code, user_id)
VALUES
(123,'001',100),
(124,'002',100),
(125,'003',101); -- 建议单批次≤500条
5 善用LIMIT限制
5.1 查询场景优化
-- 反例：取首单（查询全部再截取）
SELECT id, create_time FROM order
WHERE user_id = 123
ORDER BY create_time ASC;
-- 正例：精准定位
SELECT id, create_time FROM order
WHERE user_id = 123
ORDER BY create_time ASC
LIMIT 1; -- 性能提升10倍+
5.2 数据安全防护
-- 防误操作更新（限制影响行数）
UPDATE order
SET status = 0, edit_time = NOW(3)
WHERE create_time < '2023-01-01'
LIMIT 100; -- 即使条件错误，最多影响100行
6 限制IN列表大小
6.1 风险控制
- 超长IN列表：10万+ ID导致查询超时、内存溢出
- 网络瓶颈：返回巨量数据拖垮应用
6.2 优化方案
// 业务层双重防护
public List<User> getUsers(List<Long> ids) {
if(ids.size() > 500) { // 阈值控制
throw new RuntimeException("单次查询不超过500条");
}
return userMapper.batchQuery(ids); // SQL中仍加LIMIT 500
}
进阶方案：超大查询走异步分页 + 消息队列
7 增量查询替代全量
7.1 同步场景优化
-- 反例：全量拉取
SELECT * FROM user;
-- 正例：基于游标的增量同步
SELECT * FROM user
WHERE id > #{last_max_id}
AND update_time >= #{last_sync_time}
ORDER BY id
LIMIT 1000; -- 每次只取1000条
关键点：记录每次同步的最大ID和时间戳，建立更新时间索引
8 高效分页方案
8.1 深度分页问题
-- 传统分页（100万数据时极慢）
SELECT * FROM orders LIMIT 1000000, 20;
-- 优化方案1：主键范围查询
SELECT * FROM orders
WHERE id > 1000000
ORDER BY id
LIMIT 20;
-- 优化方案2：BETWEEN（需唯一索引）
SELECT * FROM orders
WHERE id BETWEEN 1000000 AND 1000020;
注意：方案1要求ID连续，方案2需避免重复ID
9 连接查询替代子查询
9.1 性能对比
| 查询方式 | 临时表 | 执行速度 | 适用场景 |
|---|
| 子查询 | 需要 | 慢 | 简单查询 |
| JOIN | 不需要 | 快30%+ | 多表关联场景 |
9.2 优化示例
-- 反例子查询
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM user WHERE city = 'Shanghai');
-- 正例JOIN改写
SELECT o.*
FROM orders o
JOIN user u ON o.user_id = u.id
WHERE u.city = 'Shanghai';
10 控制JOIN表数量
10.1 阿里规范
- 硬性要求：单查询JOIN表≤3张
- 性能拐点：每增加1张JOIN表，执行时间指数级增长
10.2 优化技巧
-- 反例（7表JOIN）
SELECT ... FROM a
JOIN b ON ...
JOIN c ON ...
... -- 性能灾难
-- 正例：
-- 1. 冗余必要字段（如a表增加d_name）
-- 2. 拆分复杂查询
-- 3. 用ES/HBase处理多源数据
11 JOIN驱动表选择
11.1 驱动规则
| JOIN类型 | 驱动表选择 | 优化建议 |
|---|
INNER JOIN | MySQL自动选 | 无需干预 |
LEFT JOIN | 强制左表 | 左表必须是小表 |
11.2 优化示例
-- 错误（大表order驱动小表user）
SELECT o.*
FROM orders o -- 100万行
LEFT JOIN user u ON o.user_id = u.id -- 1万行
-- 正确（小表驱动）
SELECT o.*
FROM user u -- 1万行（筛选后更少）
JOIN orders o ON u.id = o.user_id
WHERE u.level = 'VIP';
12 精简索引数量
12.1 索引代价
- 写性能损耗：每增1个索引，INSERT速度下降15%
- 存储成本：索引大小 ≈ 数据大小的1.2-1.5倍
12.2 优化策略
1. **单表索引≤5个**（阿里强制规范）
2. **优先联合索引**：
`(user_id, status)` 优于两个单列索引
3. **清理僵尸索引**：
SELECT * FROM sys.schema_unused_indexes;
4. **冷热分离**：历史数据归档到ES/HBase
13 合理设计字段类型
13.1 类型选择原则
| 场景 | 推荐类型 | 节省空间 | 性能提升 |
|---|
| 布尔值 | TINYINT(1) | 75% | 20% |
| 枚举值（<255种） | TINYINT | 90% | 35% |
| 固定长度字符串 | CHAR(11) | 50%* | 15% |
| 可变长度字符串 | VARCHAR(255) | 动态 | 25% |
| 金额 | DECIMAL(10,2) | 避免精度 | 关键 |
*注：手机号等固定11位字段用CHAR(11)比VARCHAR更高效
13.2 避坑指南
-- 错误：企业名称用CHAR
ALTER TABLE company ADD COLUMN name CHAR(200); -- 浪费150字节/行
-- 正确：动态长度
ALTER TABLE company ADD COLUMN name VARCHAR(200);
14 优化GROUP BY
14.1 执行顺序陷阱
-- 反例（先分组后过滤）
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
HAVING MAX(create_time) > '2023-01-01'; -- 全表分组
-- 正例（先过滤后分组）
SELECT user_id, COUNT(*)
FROM orders
WHERE create_time > '2023-01-01' -- 先缩小50%数据
GROUP BY user_id;
核心原则：在耗时操作（JOIN/GROUP BY/SORT）前，用WHERE过滤90%数据
15 索引深度优化
15.1 执行计划分析
EXPLAIN SELECT * FROM orders WHERE order_no = '20230517001';
关键列解读：
| 列名 | 优秀值 | 警示值 | 说明 |
|---|
type | const/ref | ALL/index | 访问类型 |
key | 有值 | NULL | 实际使用索引 |
rows | <100 | >10000 | 扫描行数 |
Extra | Using index | Using filesort | 避免磁盘排序 |
15.2 索引失效急救包
| 失效场景 | 修复方案 |
|---|
| 函数操作 | WHERE create_time > NOW()-INTERVAL 7 DAY |
| 隐式类型转换 | 确保字段与值类型一致 |
| 最左前缀破坏 | 调整条件顺序或重建索引 |
| OR条件 | 改为UNION ALL |
| 负向查询 | status IN (0,1) 代替 status != 2 |
15.3 强制索引技巧
-- 当MySQL选错索引时
SELECT * FROM orders FORCE INDEX(idx_user_id)
WHERE user_id = 100
AND status = 1;
终极建议：高并发系统需建立SQL审核流程，对慢查询自动拦截。持续优化比单次优化更重要，建议每周Review慢日志TOP 10！
通过这15个技巧，我们曾将某电商平台的订单查询从2.1s优化到78ms。记住：好的SQL是设计出来的，不是调出来的。立即检查你的核心SQL，让数据库为你加速！
Good
精华