纸上谈兵没意思，直接上案例。
这篇整理了十个我遇到过的SQL性能问题，每个都是真实场景，看看你踩过几个。
案例1：SELECT * 的代价
问题SQL：
SELECT * FROM orders WHERE user_id = 123;
问题：orders表有30个字段，但业务只需要3个字段。
优化后：
SELECT order_id, amount, status FROM orders WHERE user_id = 123;
效果：查询时间从120ms降到35ms。
原因：
- 减少了数据传输量
- 可能用上覆盖索引，避免回表
案例2：隐式类型转换
问题SQL：
SELECT * FROM users WHERE phone = 13800138000;
phone字段是varchar类型，传入数字。
EXPLAIN结果：type: ALL，全表扫描。
优化后：
SELECT * FROM users WHERE phone = '13800138000';
EXPLAIN结果：type: ref，走索引。
原因：类型不匹配时，MySQL会把字符串转成数字比较，导致索引失效。
案例3：函数导致索引失效
问题SQL：
SELECT * FROM orders
WHERE DATE(create_time) = '2024-01-15';
create_time上有索引，但没用上。
优化后：
SELECT * FROM orders
WHERE create_time >= '2024-01-15 00:00:00'
AND create_time < '2024-01-16 00:00:00';
效果：从全表扫描变成范围扫描，快了100倍。
原因：对索引列使用函数，优化器无法使用索引。
案例4：深分页问题
问题SQL：
SELECT * FROM orders ORDER BY id LIMIT 500000, 20;
问题：要扫描50万+20行，然后丢掉前50万行。
优化方案一：用上一页的ID
SELECT * FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;
优化方案二：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY id LIMIT 500000, 20
) t ON o.id = t.id;
子查询只查主键，速度快。
效果：从5秒降到50毫秒。
案例5：OR条件优化
问题SQL：
SELECT * FROM orders WHERE user_id = 123 OR order_no = 'ABC123';
user_id有索引，order_no也有索引，但MySQL只能用一个。
优化后：
SELECT * FROM orders WHERE user_id = 123
UNION
SELECT * FROM orders WHERE order_no = 'ABC123';
效果：两个查询分别走各自的索引，然后合并。
注意：如果确定没有重复数据，用UNION ALL更快。
案例6：EXISTS vs IN
问题SQL：
-- 查询有订单的用户
SELECT * FROM users WHERE id IN (
SELECT user_id FROM orders
);
orders表很大，子查询返回大量数据。
优化后：
SELECT * FROM users u WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
什么时候用什么：
- 子查询结果集小 → 用IN
- 子查询结果集大，外层表小 → 用EXISTS
- 现代MySQL优化器通常能自动转换，但复杂SQL还是要注意
案例7：JOIN顺序优化
问题SQL：
SELECT * FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.status = 1;
orders表100万，users表10万。
问题：大表驱动小表，效率低。
优化后：
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;
小表users先过滤，再关联大表orders。
原则：
- 小表驱动大表
- 把过滤条件尽量放在驱动表
- LEFT JOIN改成INNER JOIN（如果业务允许）
案例8：COUNT优化
问题SQL：
SELECT COUNT(*) FROM orders WHERE status = 1;
orders表1000万，status=1的有800万，每次统计要扫描800万行。
优化方案：
方案一：加索引
CREATE INDEX idx_status ON orders(status);
-- 走索引扫描，但还是要扫描800万个索引项
方案二：汇总表
-- 创建汇总表
CREATE TABLE order_stats (
status INT PRIMARY KEY,
cnt INT,
updated_at DATETIME
);
-- 定时任务更新（或触发器）
UPDATE order_stats SET cnt = (
SELECT COUNT(*) FROM orders WHERE status = 1
), updated_at = NOW() WHERE status = 1;
-- 查询直接读汇总表
SELECT cnt FROM order_stats WHERE status = 1;
方案三：近似值
-- 如果不需要精确值，用EXPLAIN的rows估算
EXPLAIN SELECT * FROM orders WHERE status = 1;
-- rows字段就是估算值
案例9：ORDER BY优化
问题SQL：
SELECT * FROM orders WHERE user_id = 123 ORDER BY create_time DESC;
有idx_user_id索引，但排序还是用了filesort。
EXPLAIN：Extra: Using filesort
优化后：
CREATE INDEX idx_user_time ON orders(user_id, create_time);
EXPLAIN：Extra: Using index，不再filesort。
原因：联合索引里包含了排序字段，数据已经有序。
案例10：UPDATE优化
问题SQL：
-- 批量更新状态
UPDATE orders SET status = 2 WHERE status = 1 AND create_time < '2024-01-01';
符合条件的有10万条，一次性更新锁表时间长。
优化后：分批更新
-- 每次更新1000条
UPDATE orders SET status = 2
WHERE status = 1 AND create_time < '2024-01-01'
LIMIT 1000;
-- 循环执行直到影响行数为0
或者用存储过程：
DELIMITER //
CREATE PROCEDURE batch_update()
BEGIN
DECLARE affected_rows INT DEFAULT 1;
WHILE affected_rows > 0 DO
UPDATE orders SET status = 2
WHERE status = 1 AND create_time < '2024-01-01'
LIMIT 1000;
SET affected_rows = ROW_COUNT();
-- 稍微等一下，让其他事务有机会执行
DO SLEEP(0.1);
END WHILE;
END //
DELIMITER ;
CALL batch_update();
效果：避免长时间锁表，其他业务可以正常执行。
优化检查清单
每次写SQL前，过一遍这个清单：
□ 是否用了SELECT *？改成只查需要的列
□ 有没有隐式类型转换？字符串加引号
□ 索引列上有没有函数？改写成范围查询
□ 有没有深分页？用ID游标或延迟关联
□ OR条件能否改成UNION？
□ IN子查询是否可以用EXISTS或JOIN替代？
□ JOIN顺序对不对？小表驱动大表
□ ORDER BY能否利用索引？
□ 大批量UPDATE/DELETE是否要分批？
□ 最后：跑一遍EXPLAIN确认
总结
SQL优化说到底就几个原则：
- 减少扫描行数：用好索引
- 减少回表：覆盖索引
- 减少排序：索引里包含排序字段
- 减少锁冲突：批量操作分批执行
- 减少数据传输：只查需要的列
遇到慢SQL，先EXPLAIN看执行计划，找到问题再针对性优化。
有问题评论区聊。
