场景概述
线上数据库性能问题，80%都源于糟糕的SQL。本节聚焦实际开发中最常见的慢查询、锁等待、CPU飙高场景，给出可直接落地的编写规范和优化套路。
一、基础编写规范（先别写烂SQL）
1. SELECT * 是万恶之源
反面教材：
-- 不要这样写
SELECT * FROM user WHERE status = 1;正确姿势：
-- 只拿需要的字段，减少网络IO和内存占用
SELECT id, username, email
FROM user
WHERE status = 1
LIMIT 100;2. 批量操作分批做
一次性插入10万条数据会导致binlog暴增、主从延迟。
正确姿势：
// Java伪代码：分批提交，每批1000条
for (int i = 0; i < total; i += 1000) {
insertBatch(list.subList(i, Math.min(i + 1000, total)));
}二、索引使用实战（效率提升10倍的关键）
1. 索引失效的坑
最频繁的翻车现场：
| 错误写法 | 为什么失效 | 修正方案 |
|---------|-----------|---------|
| WHERE LEFT(phone, 3) = '138' | 对字段做函数运算 | WHERE phone LIKE '138%' |
| WHERE order_id + 1 = 100 | 索引列参与计算 | WHERE order_id = 99 |
| WHERE status IN (1,2,3,4,5,6) | IN过多导致全表扫描 | 用 BETWEEN 或拆多次查询 |
| WHERE order_no IS NULL | NULL值不走索引（MyISAM） | 默认值设为0或空字符串 |
2. 联合索引最左前缀
建了 (a,b,c) 索引，只有以下情况能用上：
WHERE a=1✓WHERE a=1 AND b=2✓WHERE a=1 AND b=2 AND c=3✓WHERE b=2✗（跳过了a）
实战建议： 把区分度最高的字段放最左边。
三、常见优化技巧
1. 分页优化（深分页问题）
问题SQL：
-- 100万页之后慢如狗
SELECT * FROM orders
WHERE status=1
ORDER BY create_time DESC
LIMIT 1000000, 10;优化方案：
-- 利用覆盖索引+子查询
SELECT * FROM orders o
JOIN (
SELECT id FROM orders
WHERE status=1
ORDER BY create_time DESC
LIMIT 1000000, 10
) tmp ON o.id = tmp.id;2. 关联查询 vs 多次单表查询
数据量小时（< 1万条）： JOIN 更方便，注意关联字段加索引。
数据量大时： 拆成多次单表查询，在业务层组装（避免临时表过大）。
3. 写操作优化
- UPDATE/DELETE 一定要带WHERE条件（血泪教训）
- UPDATE避免批量锁表：
-- 分批更新，每次500条
UPDATE orders
SET status = 2
WHERE status = 1
LIMIT 500;四、真实案例：从3秒到30毫秒
背景： 电商系统订单查询慢，高峰期数据库CPU 90%。
原SQL：
SELECT o.*, u.username, p.product_name
FROM orders o
LEFT JOIN user u ON o.user_id = u.id
LEFT JOIN product p ON o.product_id = p.id
WHERE o.create_time > '2024-01-01'
AND o.status IN (1,2,3,4,5)
ORDER BY o.id DESC
LIMIT 20;问题诊断：
create_time和status单独有索引，但MySQL选择了全表扫描- 大表JOIN产生临时表
SELECT *返回字段过多
优化后：
-- 1. 建立联合索引 (create_time, status, id)
-- 2. 先查主键，再回表取详情
SELECT o.*, u.username, p.product_name
FROM (
SELECT id FROM orders
WHERE create_time > '2024-01-01'
AND status = 1 -- 去掉IN，业务分多次查或改为范围查询
ORDER BY id DESC
LIMIT 20
) tmp
JOIN orders o ON tmp.id = o.id
LEFT JOIN user u ON o.user_id = u.id
LEFT JOIN product p ON o.product_id = p.id;结果： 查询时间从3.2秒降至28毫秒，CPU降到15%。
五、Checklist：上线前必查
- [ ]
EXPLAIN检查是否走了索引（type列至少range以上） - [ ] 表数据量>10万时，是否有合适的索引
- [ ] 是否存在隐式类型转换（如字符串ID传了数字）
- [ ] 批量操作是否有上限控制
- [ ] 是否用了
FOR UPDATE却没走索引（会锁全表！）
工具推荐：
- MySQL：使用
EXPLAIN FORMAT=JSON看执行代价 - 阿里云/腾讯云：开启慢查询日志（>1秒），定期用pt-query-digest分析
小结： SQL优化不是炫技，而是减少数据扫描量和避免额外排序。先让SQL跑起来，再让它跑得快，记住：好的索引设计胜过一切调优技巧。