SQL 优化实战从执行计划到索引10 个让查询变快的技巧引言写过业务的同学都有这种经历功能上线时好好的数据量一上来接口就卡成狗。查日志一看一条 SQL 跑了 5 秒。SQL 慢80% 的问题出在没走索引、走了错误的执行计划、或者写法本身低效。这三件事恰恰是本文要讲的。本文以 MySQL 为主要示例绝大多数优化思路对 PostgreSQL、SQL Server 同样适用带你掌握一套发现慢 SQL → 分析 → 优化的完整方法。一、先学会看执行计划EXPLAIN优化第一步不是改 SQL而是先知道数据库打算怎么执行这条 SQL。EXPLAINSELECT*FROMusersWHEREname张三;重点关注几个字段type访问类型从好到差依次是consteq_refrefrangeindexALL。看到ALL全表扫描就要警惕了。key实际用到的索引NULL表示没走索引。rows预估扫描的行数越小越好。ExtraUsing index覆盖索引很好、Using filesort额外排序尽量消除、Using temporary临时表尽量消除。记住这个口诀先 EXPLAIN再动手。改完 SQL 再 EXPLAIN 一次对比type和rows的变化才算验证到位。二、索引优化的第一杠杆索引是 SQL 优化里性价比最高的手段但也是最容易用错的地方。2.1 该给哪些列建索引WHERE、JOIN、ORDER BY、GROUP BY里频繁出现的列。区分度高唯一值多的列如用户 ID、订单号别给性别这种只有两三个值的列建。外键列、经常参与关联的列。-- 高频查询场景SELECT*FROMordersWHEREuser_id100ANDstatuspaidORDERBYcreated_atDESC;-- 联合索引顺序要和查询匹配user_id - status - created_atCREATEINDEXidx_user_status_timeONorders(user_id,status,created_at);2.2 联合索引的最左前缀原则联合索引(a, b, c)相当于建了(a)、(a,b)、(a,b,c)三个索引但单独查b或c用不上。-- 能走索引WHEREa1;WHEREa1ANDb2;-- 用不上跳过了 aWHEREb2;WHEREc3;2.3 覆盖索引连回表都省了如果查询的列正好都在索引里就无需回表查主键索引这是最快的查询-- 索引 (user_id, status, created_at) 直接覆盖了要查的列SELECTuser_id,statusFROMordersWHEREuser_id100;-- Extra 显示 Using index2.4 索引失效的 5 个高频场景这些写法会让索引哑火务必记牢索引列上做运算或函数-- ❌ 失效SELECT*FROMusersWHEREYEAR(birthday)1990;SELECT*FROMusersWHEREage120;-- ✅ 改写SELECT*FROMusersWHEREbirthday1990-01-01ANDbirthday1991-01-01;SELECT*FROMusersWHEREage19;隐式类型转换-- ❌ phone 是字符串传数字会触发转换索引失效SELECT*FROMusersWHEREphone13800138000;-- ✅ 保持一致SELECT*FROMusersWHEREphone13800138000;前置通配符 LIKE-- ❌ 前置 % 索引失效SELECT*FROMusersWHEREnameLIKE%三;-- ✅ 后置 % 可以走索引SELECT*FROMusersWHEREnameLIKE张%;OR 条件中部分列无索引可用 UNION 改写-- ❌ 若 age 无索引整条可能全表扫描SELECT*FROMusersWHEREid1ORage20;-- ✅ 拆分SELECT*FROMusersWHEREid1UNIONALLSELECT*FROMusersWHEREage20;对索引列使用!、NOT IN、IS NULL这些通常难走索引能改范围查询就改。注以上是通常情况。MySQL 5.6 有index_merge优化某些场景如 OR也能用上多个索引但以 EXPLAIN 结果为准别背结论。三、SQL 写法优化3.1 别用 SELECT *SELECT *会返回所有列白白增大网络传输和内存开销也浪费了覆盖索引的机会。-- ❌SELECT*FROMusersWHEREid1;-- ✅ 只取需要的列SELECTid,name,emailFROMusersWHEREid1;3.2 深分页优化LIMIT分页越往后越慢因为数据库要先扫过前面所有行-- ❌ 第 100 万页会扫描并丢弃前 100 万行SELECT*FROMordersORDERBYidLIMIT1000000,20;用延迟关联Deferred Join改写先取主键再回表-- ✅ 先在索引上定位到 20 条主键再回表取数据SELECTo.*FROMorders oINNERJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,20)tmpONo.idtmp.id;如果分页能传上一页最后一条的 ID用游标式分页更快SELECT*FROMordersWHEREid1000000ORDERBYidLIMIT20;3.3 子查询改写为 JOIN某些子查询尤其IN子查询执行效率低改写为JOIN通常更好-- ❌SELECT*FROMusersWHEREidIN(SELECTuser_idFROMordersWHEREstatuspaid);-- ✅SELECTDISTINCTu.*FROMusers uJOINorders oONu.ido.user_idWHEREo.statuspaid;3.4 避免 ORDER BY RAND()ORDER BY RAND()会对每行生成随机数再全表排序极慢。取随机数据的正确姿势-- ❌SELECT*FROMarticlesORDERBYRAND()LIMIT10;-- ✅ 先算出随机 ID 范围SELECT*FROMarticlesWHEREid(SELECTFLOOR(RAND()*(SELECTMAX(id)FROMarticles)))ORDERBYidLIMIT10;3.5 UNION ALL 优先于 UNIONUNION会去重隐式排序去重UNION ALL不去重。确定无重复时用UNION ALL-- ✅ 更快SELECTidFROMaUNIONALLSELECTidFROMb;四、JOIN 优化小表驱动大表让结果集小的一方作为驱动表通常是 WHERE 过滤后行数少的表减少被驱动表的扫描次数。关联字段必须建索引ON条件里的列没有索引每次关联都可能全表扫描。-- orders.user_id 建了索引users 过滤后行数少则 users 驱动 ordersSELECTu.name,o.amountFROMusers uJOINorders oONu.ido.user_idWHEREu.city上海;五、事务与锁事务要小把耗时的、与数据无关的操作网络请求、文件 IO移出事务缩短持锁时间。控制锁范围只锁必要的行能用行级锁就别锁整表SELECT ... FOR UPDATE慎用。注意锁的顺序多个事务访问多张表时保持一致的加锁顺序避免死锁。-- 避免长事务事务里只包数据库操作STARTTRANSACTION;UPDATEaccountsSETbalancebalance-100WHEREid1;UPDATEaccountsSETbalancebalance100WHEREid2;COMMIT;六、其他实用技巧批量插入逐条INSERT慢用批量INSERT或LOAD DATAINSERTINTOusers(name,email)VALUES(张三,ax.com),(李四,bx.com),(王五,cx.com);选择合适的数据类型能用INT/BIGINT就别用VARCHAR存数字能用DATE就别用字符串存日期。字段越小索引越紧凑、越快。COUNT的选择COUNT(*)和COUNT(1)性能几乎一致现代优化器已优化COUNT(列名)会忽略 NULL 且可能更慢别混用。合理用缓存高频且变化少的查询结果可以放到 Redis 缓存减轻数据库压力。总结SQL 优化没有银弹但有一条黄金方法论先EXPLAIN看执行计划定位typeALL、Using filesort等信号。检查索引该建的建、该改的改避开失效场景。优化写法去掉SELECT *、解决深分页、子查询改 JOIN。优化 JOIN 和事务小表驱动大表、缩小锁范围。改完再EXPLAIN验证用数据说话。记住先分析、后优化、再验证。盲改 SQL 往往南辕北辙而看懂执行计划的人才能在数据量暴涨时稳如老狗。如果你在项目里遇到过EXPLAIN 显示正常但还是很慢的诡异场景欢迎评论区讨论我下一篇可以专门写执行计划背后的存储引擎与锁。本文为原创转载注明出处。觉得有用就点赞、收藏、关注感谢支持 SQL 优化实战：从执行计划到索引，10 个让查询变快的技巧
SQL 优化实战从执行计划到索引10 个让查询变快的技巧引言写过业务的同学都有这种经历功能上线时好好的数据量一上来接口就卡成狗。查日志一看一条 SQL 跑了 5 秒。SQL 慢80% 的问题出在没走索引、走了错误的执行计划、或者写法本身低效。这三件事恰恰是本文要讲的。本文以 MySQL 为主要示例绝大多数优化思路对 PostgreSQL、SQL Server 同样适用带你掌握一套发现慢 SQL → 分析 → 优化的完整方法。一、先学会看执行计划EXPLAIN优化第一步不是改 SQL而是先知道数据库打算怎么执行这条 SQL。EXPLAINSELECT*FROMusersWHEREname张三;重点关注几个字段type访问类型从好到差依次是consteq_refrefrangeindexALL。看到ALL全表扫描就要警惕了。key实际用到的索引NULL表示没走索引。rows预估扫描的行数越小越好。ExtraUsing index覆盖索引很好、Using filesort额外排序尽量消除、Using temporary临时表尽量消除。记住这个口诀先 EXPLAIN再动手。改完 SQL 再 EXPLAIN 一次对比type和rows的变化才算验证到位。二、索引优化的第一杠杆索引是 SQL 优化里性价比最高的手段但也是最容易用错的地方。2.1 该给哪些列建索引WHERE、JOIN、ORDER BY、GROUP BY里频繁出现的列。区分度高唯一值多的列如用户 ID、订单号别给性别这种只有两三个值的列建。外键列、经常参与关联的列。-- 高频查询场景SELECT*FROMordersWHEREuser_id100ANDstatuspaidORDERBYcreated_atDESC;-- 联合索引顺序要和查询匹配user_id - status - created_atCREATEINDEXidx_user_status_timeONorders(user_id,status,created_at);2.2 联合索引的最左前缀原则联合索引(a, b, c)相当于建了(a)、(a,b)、(a,b,c)三个索引但单独查b或c用不上。-- 能走索引WHEREa1;WHEREa1ANDb2;-- 用不上跳过了 aWHEREb2;WHEREc3;2.3 覆盖索引连回表都省了如果查询的列正好都在索引里就无需回表查主键索引这是最快的查询-- 索引 (user_id, status, created_at) 直接覆盖了要查的列SELECTuser_id,statusFROMordersWHEREuser_id100;-- Extra 显示 Using index2.4 索引失效的 5 个高频场景这些写法会让索引哑火务必记牢索引列上做运算或函数-- ❌ 失效SELECT*FROMusersWHEREYEAR(birthday)1990;SELECT*FROMusersWHEREage120;-- ✅ 改写SELECT*FROMusersWHEREbirthday1990-01-01ANDbirthday1991-01-01;SELECT*FROMusersWHEREage19;隐式类型转换-- ❌ phone 是字符串传数字会触发转换索引失效SELECT*FROMusersWHEREphone13800138000;-- ✅ 保持一致SELECT*FROMusersWHEREphone13800138000;前置通配符 LIKE-- ❌ 前置 % 索引失效SELECT*FROMusersWHEREnameLIKE%三;-- ✅ 后置 % 可以走索引SELECT*FROMusersWHEREnameLIKE张%;OR 条件中部分列无索引可用 UNION 改写-- ❌ 若 age 无索引整条可能全表扫描SELECT*FROMusersWHEREid1ORage20;-- ✅ 拆分SELECT*FROMusersWHEREid1UNIONALLSELECT*FROMusersWHEREage20;对索引列使用!、NOT IN、IS NULL这些通常难走索引能改范围查询就改。注以上是通常情况。MySQL 5.6 有index_merge优化某些场景如 OR也能用上多个索引但以 EXPLAIN 结果为准别背结论。三、SQL 写法优化3.1 别用 SELECT *SELECT *会返回所有列白白增大网络传输和内存开销也浪费了覆盖索引的机会。-- ❌SELECT*FROMusersWHEREid1;-- ✅ 只取需要的列SELECTid,name,emailFROMusersWHEREid1;3.2 深分页优化LIMIT分页越往后越慢因为数据库要先扫过前面所有行-- ❌ 第 100 万页会扫描并丢弃前 100 万行SELECT*FROMordersORDERBYidLIMIT1000000,20;用延迟关联Deferred Join改写先取主键再回表-- ✅ 先在索引上定位到 20 条主键再回表取数据SELECTo.*FROMorders oINNERJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,20)tmpONo.idtmp.id;如果分页能传上一页最后一条的 ID用游标式分页更快SELECT*FROMordersWHEREid1000000ORDERBYidLIMIT20;3.3 子查询改写为 JOIN某些子查询尤其IN子查询执行效率低改写为JOIN通常更好-- ❌SELECT*FROMusersWHEREidIN(SELECTuser_idFROMordersWHEREstatuspaid);-- ✅SELECTDISTINCTu.*FROMusers uJOINorders oONu.ido.user_idWHEREo.statuspaid;3.4 避免 ORDER BY RAND()ORDER BY RAND()会对每行生成随机数再全表排序极慢。取随机数据的正确姿势-- ❌SELECT*FROMarticlesORDERBYRAND()LIMIT10;-- ✅ 先算出随机 ID 范围SELECT*FROMarticlesWHEREid(SELECTFLOOR(RAND()*(SELECTMAX(id)FROMarticles)))ORDERBYidLIMIT10;3.5 UNION ALL 优先于 UNIONUNION会去重隐式排序去重UNION ALL不去重。确定无重复时用UNION ALL-- ✅ 更快SELECTidFROMaUNIONALLSELECTidFROMb;四、JOIN 优化小表驱动大表让结果集小的一方作为驱动表通常是 WHERE 过滤后行数少的表减少被驱动表的扫描次数。关联字段必须建索引ON条件里的列没有索引每次关联都可能全表扫描。-- orders.user_id 建了索引users 过滤后行数少则 users 驱动 ordersSELECTu.name,o.amountFROMusers uJOINorders oONu.ido.user_idWHEREu.city上海;五、事务与锁事务要小把耗时的、与数据无关的操作网络请求、文件 IO移出事务缩短持锁时间。控制锁范围只锁必要的行能用行级锁就别锁整表SELECT ... FOR UPDATE慎用。注意锁的顺序多个事务访问多张表时保持一致的加锁顺序避免死锁。-- 避免长事务事务里只包数据库操作STARTTRANSACTION;UPDATEaccountsSETbalancebalance-100WHEREid1;UPDATEaccountsSETbalancebalance100WHEREid2;COMMIT;六、其他实用技巧批量插入逐条INSERT慢用批量INSERT或LOAD DATAINSERTINTOusers(name,email)VALUES(张三,ax.com),(李四,bx.com),(王五,cx.com);选择合适的数据类型能用INT/BIGINT就别用VARCHAR存数字能用DATE就别用字符串存日期。字段越小索引越紧凑、越快。COUNT的选择COUNT(*)和COUNT(1)性能几乎一致现代优化器已优化COUNT(列名)会忽略 NULL 且可能更慢别混用。合理用缓存高频且变化少的查询结果可以放到 Redis 缓存减轻数据库压力。总结SQL 优化没有银弹但有一条黄金方法论先EXPLAIN看执行计划定位typeALL、Using filesort等信号。检查索引该建的建、该改的改避开失效场景。优化写法去掉SELECT *、解决深分页、子查询改 JOIN。优化 JOIN 和事务小表驱动大表、缩小锁范围。改完再EXPLAIN验证用数据说话。记住先分析、后优化、再验证。盲改 SQL 往往南辕北辙而看懂执行计划的人才能在数据量暴涨时稳如老狗。如果你在项目里遇到过EXPLAIN 显示正常但还是很慢的诡异场景欢迎评论区讨论我下一篇可以专门写执行计划背后的存储引擎与锁。本文为原创转载注明出处。觉得有用就点赞、收藏、关注感谢支持