SQL优化案例分析：十个常见性能问题-平芜编程栈

纸上谈兵没意思，直接上案例。

这篇整理了十个我遇到过的SQL性能问题，每个都是真实场景，看看你踩过几个。

案例1：SELECT * 的代价

问题SQL：

SELECT*FROMordersWHEREuser_id=123;

问题：orders表有30个字段，但业务只需要3个字段。

优化后：

SELECTorder_id,amount,statusFROMordersWHEREuser_id=123;

效果：查询时间从120ms降到35ms。

原因：

减少了数据传输量
可能用上覆盖索引，避免回表

案例2：隐式类型转换

问题SQL：

SELECT*FROMusersWHEREphone=13800138000;

phone字段是varchar类型，传入数字。

EXPLAIN结果：type: ALL，全表扫描。

优化后：

SELECT*FROMusersWHEREphone='13800138000';

EXPLAIN结果：type: ref，走索引。

原因：类型不匹配时，MySQL会把字符串转成数字比较，导致索引失效。

案例3：函数导致索引失效

问题SQL：

SELECT*FROMordersWHEREDATE(create_time)='2024-01-15';

create_time上有索引，但没用上。

优化后：

SELECT*FROMordersWHEREcreate_time>='2024-01-15 00:00:00'ANDcreate_time<'2024-01-16 00:00:00';

效果：从全表扫描变成范围扫描，快了100倍。

原因：对索引列使用函数，优化器无法使用索引。

案例4：深分页问题

问题SQL：

SELECT*FROMordersORDERBYidLIMIT500000,20;

问题：要扫描50万+20行，然后丢掉前50万行。

优化方案一：用上一页的ID

SELECT*FROMordersWHEREid>500000ORDERBYidLIMIT20;

优化方案二：延迟关联

SELECTo.*FROMorders oINNERJOIN(SELECTidFROMordersORDERBYidLIMIT500000,20)tONo.id=t.id;

子查询只查主键，速度快。

效果：从5秒降到50毫秒。

案例5：OR条件优化

问题SQL：

SELECT*FROMordersWHEREuser_id=123ORorder_no='ABC123';

user_id有索引，order_no也有索引，但MySQL只能用一个。

优化后：

SELECT*FROMordersWHEREuser_id=123UNIONSELECT*FROMordersWHEREorder_no='ABC123';

效果：两个查询分别走各自的索引，然后合并。

注意：如果确定没有重复数据，用UNION ALL更快。

案例6：EXISTS vs IN

问题SQL：

-- 查询有订单的用户SELECT*FROMusersWHEREidIN(SELECTuser_idFROMorders);

orders表很大，子查询返回大量数据。

优化后：

SELECT*FROMusers uWHEREEXISTS(SELECT1FROMorders oWHEREo.user_id=u.id);

什么时候用什么：

子查询结果集小 → 用IN
子查询结果集大，外层表小 → 用EXISTS
现代MySQL优化器通常能自动转换，但复杂SQL还是要注意

案例7：JOIN顺序优化

问题SQL：

SELECT*FROMorders oLEFTJOINusers uONo.user_id=u.idWHEREu.status=1;

orders表100万，users表10万。

问题：大表驱动小表，效率低。

优化后：

SELECT*FROMusers uINNERJOINorders oONu.id=o.user_idWHEREu.status=1;

小表users先过滤，再关联大表orders。

原则：

小表驱动大表
把过滤条件尽量放在驱动表
LEFT JOIN改成INNER JOIN（如果业务允许）

案例8：COUNT优化

问题SQL：

SELECTCOUNT(*)FROMordersWHEREstatus=1;

orders表1000万，status=1的有800万，每次统计要扫描800万行。

优化方案：

方案一：加索引

CREATEINDEXidx_statusONorders(status);-- 走索引扫描，但还是要扫描800万个索引项

方案二：汇总表

-- 创建汇总表CREATETABLEorder_stats(statusINTPRIMARYKEY,cntINT,updated_atDATETIME);-- 定时任务更新（或触发器）UPDATEorder_statsSETcnt=(SELECTCOUNT(*)FROMordersWHEREstatus=1),updated_at=NOW()WHEREstatus=1;-- 查询直接读汇总表SELECTcntFROMorder_statsWHEREstatus=1;

方案三：近似值

-- 如果不需要精确值，用EXPLAIN的rows估算EXPLAINSELECT*FROMordersWHEREstatus=1;-- rows字段就是估算值

案例9：ORDER BY优化

问题SQL：

SELECT*FROMordersWHEREuser_id=123ORDERBYcreate_timeDESC;

有idx_user_id索引，但排序还是用了filesort。

EXPLAIN：Extra: Using filesort

优化后：

CREATEINDEXidx_user_timeONorders(user_id,create_time);

EXPLAIN：Extra: Using index，不再filesort。

原因：联合索引里包含了排序字段，数据已经有序。

案例10：UPDATE优化

问题SQL：

-- 批量更新状态UPDATEordersSETstatus=2WHEREstatus=1ANDcreate_time<'2024-01-01';

符合条件的有10万条，一次性更新锁表时间长。

优化后：分批更新

-- 每次更新1000条UPDATEordersSETstatus=2WHEREstatus=1ANDcreate_time<'2024-01-01'LIMIT1000;-- 循环执行直到影响行数为0

或者用存储过程：

DELIMITER//CREATEPROCEDUREbatch_update()BEGINDECLAREaffected_rowsINTDEFAULT1;WHILEaffected_rows>0DOUPDATEordersSETstatus=2WHEREstatus=1ANDcreate_time<'2024-01-01'LIMIT1000;SETaffected_rows=ROW_COUNT();-- 稍微等一下，让其他事务有机会执行DOSLEEP(0.1);ENDWHILE;END//DELIMITER;CALLbatch_update();

效果：避免长时间锁表，其他业务可以正常执行。

优化检查清单

每次写SQL前，过一遍这个清单：

□ 是否用了SELECT *？改成只查需要的列 □ 有没有隐式类型转换？字符串加引号 □ 索引列上有没有函数？改写成范围查询 □ 有没有深分页？用ID游标或延迟关联 □ OR条件能否改成UNION？ □ IN子查询是否可以用EXISTS或JOIN替代？ □ JOIN顺序对不对？小表驱动大表 □ ORDER BY能否利用索引？ □ 大批量UPDATE/DELETE是否要分批？ □ 最后：跑一遍EXPLAIN确认