SQL优化没那么难，这些技巧我帮你踩过坑了地的实战技巧-北京尧图网络

SQL优化没那么难这些技巧我帮你踩过坑了地的实战技巧去年双11前一周我们电商平台的订单库突然触发P0级CPU告警峰值直接冲到98%后台堆了三千多条慢查询订单创建接口超时率一度超过15%运营那边催着说用户付不了钱要投诉。整个技术组熬了一整夜排查最后只改了3条SQL加了2个联合索引就把库的QPS从200拉到了3200CPU直接降到了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_timeFROM order_info oINNER 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_timeFROM order_infoWHERE create_time 上一页最后一条的create_time AND id 上一页最后一条的idORDER 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# 记录没有走索引的SQLlog_queries_not_using_indexes ON2、第二步用EXPLAIN分析执行计划揪出问题点找到慢SQL之后不要急着改先用EXPLAIN看一下执行计划重点看这几个字段我给你整理了个表格每个字段的含义和优化要点都写清楚了表格字段名含义说明优化要点type 访问类型性能从好到坏依次是systemconsteq_refrefrangeindexALL 保证查询至少达到range级别最好能到ref避免出现ALL全表扫描key 实际使用的索引如果是NULL说明没走索引需要检查是不是索引失效或者没加索引rows 预估要扫描的行数这个值越小越好如果值特别大说明扫描的数据太多要优化Extra 额外信息比如Using index、Using filesort、Using temporary等出现Using filesort或者Using temporary说明需要额外优化尽量用覆盖索引之前我优化过一个SQLEXPLAIN看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博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围

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

相关推荐

高速ADC系统SYSREF校准原理与实战：从JESD204B同步到抗辐射设计

DP83826以太网PHY芯片LED与GPIO配置实战指南

DRA71x SoC电气特性与电源时序设计实战指南

更多资讯

宜春铜鼓考电工证怎么考内部消息告诉你怎么破学历门槛

办理考低压电工证的试卷需要什么材料

潍坊考低压电工证在哪考?实操考试心里没底怕挂科

低压电工证分几科通过率揭秘：刚需证书怎么考最稳妥

工地太忙根本没时间复习考试？敦煌电工证培训学校通过率揭秘来了

别踩坑！学历不高也能考电工焊工证，抖音推广电工证焊工证选对机构太关键

今日更新

西昌市电工作业查询怎么查？郑州报考避坑指南帮你少走弯路

西昌市电工作业查询通过率揭秘：怕考不过白交培训费？这几点要记牢

临夏电工证在哪里审官方报名入口怎么找

本周精选

安监局电工证分数查询怎么查才能知道含金量高不高

新疆电工作业干活累吗良心建议助你避开考不过的坑

中牟县电工证怎么报名外省证能转吗

本月热门

面向复杂光照与角度的宠物面部识别鲁棒性增强策略

基于DRV8818与STM32的双极步进电机驱动方案实战解析

DRV8818PWPR+PIC18F45K80双极步进电机控制方案实战

读完文章，想聊聊您的网站？