SQL优化没那么难，这些技巧我帮你踩过坑了地的实战技巧

SQL优化没那么难，这些技巧我帮你踩过坑了地的实战技巧

去年双11前一周，我们电商平台的订单库突然触发P0级CPU告警，峰值直接冲到98%，后台堆了三千多条慢查询，订单创建接口超时率一度超过15%，运营那边催着说用户付不了钱要投诉。整个技术组熬了一整夜排查，最后只改了3条SQL，加了2个联合索引，就把库的QPS从200拉到了3200，CPU直接降到了20%以下，顺利扛过了双11的流量峰值。很多人觉得SQL优化是DBA的专属工作，和普通业务开发没关系，但实际上我做了6年后端开发，见过无数因为一条慢SQL拖垮整个微服务集群的事故，80%的线上数据库性能问题，本质上都是开发写SQL时不注意细节埋的坑。今天我把这么多年踩坑总结出来的SQL优化经验全部分享给你，没有虚头巴脑的理论，全是能直接复制落地的干货。

SQL优化实战与落地指南‌

一、先搞懂：你的SQL为什么会跑不动

很多人一看到慢SQL就想着加索引，结果加了一堆索引，查询速度没快多少，写入性能反而掉了一半，这就是典型的“头痛医头脚痛医脚”，根本没搞懂SQL慢的根源在哪。

1、SQL执行的全链路到底发生了什么

很多人写SQL只关心能不能查出正确的结果，根本不关心MySQL执行这条SQL的时候到底做了什么。实际上一条SQL从发出去到返回结果，要经过好几个环节：首先是连接器验证权限，然后是查询缓存（MySQL8.0已经删掉了这个模块），接着是分析器做词法语义分析，优化器选择执行计划，最后才是执行器调用存储引擎的接口返回数据。任何一个环节出问题，都可能导致SQL变慢：比如网络延迟、内存不够、锁等待、没走索引、回表次数太多、排序分组用了临时表等等。

我之前见过一个新手开发，写了条关联7张表的查询SQL，每次执行要8秒多，他上来就要给每张表加索引，结果我看了执行计划才发现，优化器直接选了错误的驱动表，导致中间结果集有几十万行，光关联就花了7秒，加索引根本没用，最后改了下连接顺序，加了个STRAIGHT_JOIN强制小表驱动，执行时间直接降到了300毫秒。

2、慢查询的几个常见根源

我总结了线上90%慢SQL的来源，基本逃不出这几个坑：

第一是没走索引，要么是没加索引，要么是索引加了但是因为SQL写法不对导致失效，比如对字段做函数运算、隐式类型转换、like前面加%这些常见的坑。

第二是扫描的数据量太大，比如深分页、查了不需要的字段、关联的时候产生了大的中间结果集，导致需要回表几万甚至几十万次。

第三是排序、分组、去重的时候用了文件排序或者临时表，数据量小的时候没感觉，数据量上百万之后就会非常慢。

第四是锁等待，比如你查询的时候刚好有个大事务在更新表里的数据，行锁或者表锁一直没释放，你的查询就会一直堵着。

二、必学的SQL优化核心手法，附真实代码示例

接下来讲的这些优化手法，都是我在线上摸爬滚打这么多年验证过的，每个手法我都会附上真实的代码例子，你看完就能直接用到自己的项目里。

1、别再写SELECT *了，这个坑真的能踩死人

我见过至少不下10次线上事故，根源都是开发写SQL图方便，直接写SELECT *。很多人觉得不就是查几个字段吗，能有多大影响？我给你算笔账：假设你有张订单表，里面有20个字段，其中有个text类型的字段存的是订单快照，有好几KB大。你本来只需要查订单id、用户id、订单金额这三个字段，结果写了SELECT *，每次查100条数据，就会多传几百KB的无用数据，要是QPS到1000的话，每秒就要多传几百MB的数据，网络带宽都能给你打满。

更要命的是，SELECT * 大概率会导致回表次数变多，覆盖索引直接用不了。我给你举个真实的例子，之前我们有个订单列表的查询SQL，原来写的是：

sql

-- 优化前：全字段查询，执行时间1.2秒

SELECT * FROM order_info WHERE user_id = 12345 AND create_time > '2025-01-01' ORDER BY create_time DESC LIMIT 20;

这个SQL我们已经给user_id和create_time建了联合索引，但是因为用了SELECT *，MySQL拿到索引上的user_id、create_time和主键id之后，还要回主键索引去查其他所有字段，20条数据就要回表20次，而且因为要回表，优化器可能直接选择全表扫描。后来我们把SQL改成了只查需要的字段：

sql

-- 优化后：只查需要的字段，走覆盖索引，执行时间30毫秒

SELECT id, user_id, order_amount, create_time FROM order_info WHERE user_id = 12345 AND create_time > '2025-01-01' ORDER BY create_time DESC LIMIT 20;

改完之后直接走了联合索引的覆盖索引，不需要回表，执行时间直接从1.2秒降到了30毫秒，快了整整40倍。

2、警惕隐式类型转换，隐形的索引杀手

这个坑真的特别隐蔽，很多时候你明明加了索引，但是SQL就是不走索引，查了半天发现是字段类型不匹配导致的隐式转换。我之前遇到过一个案例，用户表的mobile字段是varchar类型，存的是手机号，我们给mobile建了唯一索引，结果有个开发写登录查询的时候，参数传了个数字类型：

sql

-- 错误写法：varchar字段传int，隐式转换，索引失效，执行时间2.8秒

SELECT * FROM user_info WHERE mobile = 13800138000;

这个SQL每次执行要2.8秒，我们用EXPLAIN看了下，type列是ALL，全表扫描，rows列扫了整整200万行。为什么会这样？因为MySQL在遇到字符串和数字比较的时候，会把字符串转换成数字再比较，相当于对mobile字段做了CAST函数运算，而对索引字段做函数运算会直接导致索引失效。后来把参数改成字符串类型：

sql

-- 正确写法：参数类型和字段匹配，走索引，执行时间15毫秒

SELECT * FROM user_info WHERE mobile = '13800138000';

改完之后type直接变成const，执行时间从2.8秒降到15毫秒。类似的坑还有varchar字段的排序规则不匹配，比如一个是utf8mb4_general_ci，一个是utf8mb4_unicode_ci，关联的时候也会导致隐式转换，索引失效。

3、深分页问题的三种解决方案，再也不怕limit翻到几万页

只要你做过后台管理系统，肯定遇到过深分页的问题：limit 100000,10 这种SQL，越往后翻页越慢，到最后翻到几十万页的时候，一次查询要好几秒。为什么会这样？因为MySQL执行limit的时候，会先扫描前100010条数据，然后把前100000条扔掉，只返回最后10条，相当于白扫描了10万条数据，当然慢。

我给你三个亲测有效的解决方案，每个都有适用场景：

第一种是子查询优化法，先通过覆盖索引查到符合条件的主键id，再通过id关联查需要的字段，适合大部分场景：

sql

-- 深分页优化：子查询先查id，再关联，执行时间从2.1秒降到50毫秒

SELECT o.id, o.user_id, o.order_amount, o.create_time

FROM order_info o

INNER JOIN (

SELECT id FROM order_info WHERE create_time > '2025-01-01' ORDER BY create_time DESC LIMIT 100000, 10

) t ON o.id = t.id;

第二种是书签法（也就是游标分页），适合APP端那种上拉加载更多的场景，不需要跳页，只能一页一页往下翻，性能是最好的，不管翻多少页都是毫秒级返回：

sql

-- 书签分页：用上一页最后一条的create_time和id作为条件，永远只查10条

SELECT id, user_id, order_amount, create_time

FROM order_info

WHERE create_time < '上一页最后一条的create_time' AND id < '上一页最后一条的id'

ORDER BY create_time DESC, id DESC LIMIT 10;

第三种是如果你的业务真的需要支持跳转到任意页，而且数据量特别大，可以用Elasticsearch做检索，MySQL只做主键查询，毕竟数据库不是搜索引擎，不要拿数据库做它不擅长的事。

4、JOIN优化的几个黄金法则，别再乱关联表了

很多人写JOIN的时候喜欢随便写，关联个七八张表是常事，结果SQL跑的特别慢，还不知道问题出在哪。我总结了几个JOIN优化的黄金法则，你照着做就不会出大问题：

第一是永远用小表驱动大表，关联的时候MySQL会选择表作为驱动表，遍历驱动表的每一行数据，再去被驱动表里查匹配的数据，所以驱动表的数据量越小，循环的次数就越少，性能就越好。我一般会把过滤之后结果集最小的表作为驱动表，如果你不确定优化器选的对不对，可以用STRAIGHT_JOIN强制指定连接顺序。

第二是关联字段必须建索引，而且两个表的关联字段类型必须完全一致，避免隐式转换导致索引失效。之前我们有个SQL关联两个表，关联字段一个是int，一个是bigint，结果每次关联都要全表扫描，改完字段类型之后速度快了100倍。

第三是尽量不要关联超过3张表，关联的表越多，优化器越容易选错执行计划，而且中间结果集会越来越大，性能会急剧下降。如果真的需要关联很多表，可以拆分SQL，先查主表的数据，再批量查关联表的数据，在代码里做组装，性能很多时候比多表关联要好。

我给你举个反例，之前有个开发写了个关联6张表的查询，执行时间要5秒多，我拆成了3条单表查询，用内存组装，总执行时间才200毫秒，快了25倍。

第四是分清ON和WHERE的区别，LEFT JOIN的时候，ON后面的条件是用来关联两张表的，WHERE后面的条件是过滤关联之后的结果的，如果你把过滤条件写在ON后面，会导致LEFT JOIN变成INNER JOIN，结果不对就算了，还可能会慢。

三、线上SQL优化的标准流程，别直接改线上SQL

很多人优化SQL的时候，抓到一个慢SQL就直接改完上线，结果要么是改完结果不对，要么是上线之后反而更慢，甚至导致线上故障。我平时优化SQL都是按照固定的流程来，从来没出过事故。

1、第一步：先开慢查询日志，精准定位慢SQL

不要凭感觉觉得哪条SQL慢，一定要用数据说话。优化的第一步是开启MySQL的慢查询日志，把执行时间超过阈值（一般线上我会设成1秒）的SQL全部记录下来，然后用mysqldumpslow或者pt-query-digest工具分析，找出执行次数最多、耗时最长的TOP20慢SQL，优先优化这些影响最大的SQL，毕竟20%的慢SQL占用了80%的数据库资源。

给你贴一下开启慢查询日志的配置，直接改my.cnf就行：

ini

# 开启慢查询日志

slow_query_log = ON

# 慢查询日志存放路径

slow_query_log_file = /var/log/mysql/mysql-slow.log

# 慢查询阈值，单位秒，超过这个时间就记录

long_query_time = 1

# 记录没有走索引的SQL

log_queries_not_using_indexes = ON

2、第二步：用EXPLAIN分析执行计划，揪出问题点

找到慢SQL之后，不要急着改，先用EXPLAIN看一下执行计划，重点看这几个字段：

我给你整理了个表格，每个字段的含义和优化要点都写清楚了：

表格

字段名 含义说明 优化要点

type 访问类型，性能从好到坏依次是system>const>eq_ref>ref>range>index>ALL 保证查询至少达到range级别，最好能到ref，避免出现ALL（全表扫描）

key 实际使用的索引 如果是NULL说明没走索引，需要检查是不是索引失效或者没加索引

rows 预估要扫描的行数 这个值越小越好，如果值特别大，说明扫描的数据太多，要优化

Extra 额外信息，比如Using index、Using filesort、Using temporary等 出现Using filesort或者Using temporary说明需要额外优化，尽量用覆盖索引

之前我优化过一个SQL，EXPLAIN看Extra里有Using filesort，因为order by的字段没有在联合索引里，后来调整了联合索引的顺序，把order by的字段加进去，filesort直接消失了，查询速度快了好几倍。

3、第三步：测试环境压测，灰度上线

改完SQL之后，不要直接上线，先在测试环境用生产的等量数据压测，对比优化前后的执行时间、扫描行数，确认结果正确，性能确实有提升。上线的时候先小流量灰度，观察数据库的CPU、QPS、慢查询数量等监控指标，确认没问题再全量上线。我之前见过有人改完SQL没测试，上线之后把数据库查挂了，导致整个服务不可用，这都是血的教训。

四、那些我踩过的SQL优化反直觉坑

优化SQL不是套公式就行，很多时候你以为是对的，实际上反而会导致性能下降，我给你讲几个我踩过的反直觉的坑。

1、不是所有场景加索引都能变快

很多人觉得只要加索引查询就会变快，实际上不是这样的。如果你的字段区分度特别低，比如性别字段，只有0和1两个值，加索引根本没用，因为优化器算下来，走索引要回表一半的数据，还不如直接全表扫描快。而且索引不是越多越好，每个索引都会占用磁盘空间，而且插入、更新、删除数据的时候都要维护索引，索引太多会导致写入性能急剧下降，我一般建议单张表的索引不要超过5个。

2、优化器有时候会选错索引，不要盲目相信它

你以为MySQL的优化器永远是对的？错，我遇到过好多次优化器选错索引的情况，比如有个SQL明明有个联合索引可以走覆盖索引，结果优化器选了另一个普通索引，导致查询慢了十几倍。这个时候你可以用FORCE INDEX强制指定走哪个索引，当然这是兜底方案，一般还是尽量让优化器自己选，真的选错了再强制。

3、不要过度优化，适合业务的才是最好的

我见过很多人为了优化SQL，把SQL写的特别复杂，各种子查询、函数嵌套，结果性能是快了几十毫秒，但是后面的人根本看不懂，维护成本特别高。比如有些场景下，数据量只有几万条，全表扫描也就几毫秒，根本没必要加索引，加了索引反而浪费空间，降低写入性能。优化的前提是不影响业务可读性，不要为了几毫秒的性能提升，把SQL写成没人能看懂的天书。

做了这么多年开发，我越来越觉得，SQL优化不是什么高深的黑科技，本质上就是理解数据库的执行逻辑，避开那些常见的坑，写SQL的时候多注意一点细节，就能避免80%的性能问题。很多人到处找什么SQL优化的武林秘籍，实际上最有用的技巧都是最基础的：别查不需要的字段、别让索引失效、尽量扫描更少的数据、不要让数据库做它不擅长的事。

最后我想说，最好的优化是在写SQL的时候就把这些细节注意到，不要等线上出故障了再去救火，毕竟事故处理的再好，也不如不出事故。

💡注意：本文所介绍的软件及功能均基于公开信息整理，仅供用户参考。在使用任何软件时，请务必遵守相关法律法规及软件使用协议。同时，本文不涉及任何商业推广或引流行为，仅为用户提供一个了解和使用该工具的渠道。

你在生活中时遇到了哪些问题？你是如何解决的？欢迎在评论区分享你的经验和心得！

希望这篇文章能够满足您的需求，如果您有任何修改意见或需要进一步的帮助，请随时告诉我！

感谢各位支持，可以关注我的个人主页，找到你所需要的宝贝。

博文入口：山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口：常用软件宝贝：精品文件

作者郑重声明，本文内容为本人原创文章，纯净无利益纠葛，如有不妥之处，请及时联系修改或删除。诚邀各位读者秉持理性态度交流，共筑和谐讨论氛围～