一条SQL从12秒到80毫秒，我只做了这三件事

一条SQL从12秒到80毫秒我只做了这三件事你有没有经历过这样的场景明明只是查了一张百万级的表接口却愣是跑了十几秒才返回用户在屏幕前干等老板在群里连发三个问号而你对着满屏的SQL语句束手无策。别慌这种事我也经历过而且不止一次。从最初的全表扫描不要钱到后来学会看执行计划、建组合索引、改写子查询我花了将近两年时间踩坑总结。今天这篇文章就把我踩过的坑、填过的土、走过的弯路一次性摊开来讲希望你看完之后下次再遇到慢查询时能少走三年弯路。数据库工程与SQL优化一场与时间赛跑的技术修行‌一、为什么SQL优化值得我们花大力气去研究‌很多刚入行的开发者觉得数据库不就是存数据的地方吗写好业务逻辑才是正道SQL能跑就行。这种想法在数据量小的时候没什么问题但一旦业务起量数据量从几万涨到几百万甚至上亿一条没优化的SQL就能把整个系统拖垮。1、从业务角度看慢查询直接影响用户体验。一个电商平台的商品列表页如果加载超过三秒用户流失率会飙升到百分之四十以上这个数据不是我瞎编的是各大互联网公司反复验证过的结论。2、从成本角度看数据库服务器的资源是有限的。一条慢查询长期霸占连接池和CPU资源其他正常请求就得排队等待甚至触发连接数超限报警。到那时候你要么加机器要么优化SQL而加机器的钱远远比花时间调优贵得多。3、从技术成长角度看SQL优化能力是区分初级开发和中高级开发的一道分水岭。面试的时候面试官很喜欢拿一个慢查询场景让你现场分析你能不能快速定位问题、给出方案直接决定了你能拿到什么级别的offer。所以说SQL优化不是可选项而是必修课。接下来我就从实际案例出发带你一步步拆解慢查询背后的原因和解决思路。二、一个真实的慢查询案例从12秒到80毫秒‌我之前参与过一个在线教育平台的项目里面有一张订单明细表t_order_detail数据量大概在八百万行左右。运营同学提了一个需求要按时间段统计各课程的销售总额和订单数并且支持按课程分类筛选。最初的写法是这样的sqlSELECTc.course_name,COUNT(o.order_id) AS order_count,SUM(o.amount) AS total_amountFROMt_order_detail oLEFT JOIN t_course c ON o.course_id c.idWHEREo.create_time BETWEEN 2024-01-01 AND 2024-06-30AND o.status 1AND c.category_id IN (101, 102, 103)GROUP BYc.course_nameORDER BYtotal_amount DESC;这条SQL在测试环境跑得还行但上了生产环境数据量一上来直接飙到十二秒。运维同学天天找我我也头疼。后来我用EXPLAIN看了一下执行计划发现了几个明显的问题1、t_order_detail表在create_time字段上没有建立索引导致范围查询走了全表扫描。2、LEFT JOIN在这里其实没必要因为WHERE条件里已经限定了c.category_id不匹配的记录本来就会被过滤掉用INNER JOIN语义更准确执行效率也更高。3、GROUP BY c.course_name导致MySQL需要对结果集做临时表排序因为course_name上没有索引且不是覆盖索引。针对这些问题我做了如下优化sql-- 第一步先确认需要的索引ALTER TABLE t_order_detailADD INDEX idx_create_status (create_time, status);-- 第二步改写SQL把过滤条件提前减少JOIN的数据量SELECTc.course_name,COUNT(o.order_id) AS order_count,SUM(o.amount) AS total_amountFROMt_order_detail oINNER JOIN t_course c ON o.course_id c.idWHEREo.create_time BETWEEN 2024-01-01 AND 2024-06-30AND o.status 1AND c.category_id IN (101, 102, 103)GROUP BYc.course_nameORDER BYtotal_amount DESC;光加索引还不够我还把GROUP BY改成了按c.id分组再在外层做一次转换sqlSELECTcourse_name,order_count,total_amountFROM (SELECTc.id,c.course_name,COUNT(o.order_id) AS order_count,SUM(o.amount) AS total_amountFROMt_order_detail oINNER JOIN t_course c ON o.course_id c.idWHEREo.create_time BETWEEN 2024-01-01 AND 2024-06-30AND o.status 1AND c.category_id IN (101, 102, 103)GROUP BYc.id, c.course_name) tmpORDER BYtotal_amount DESC;优化完之后同样的数据量查询时间从十二秒降到了八十毫秒左右。运维同学都惊了说我是不是换了服务器。其实没换就是把该加的索引加上了该改写的SQL改写了。三、Explain执行计划慢查询的体检报告‌很多人知道要用EXPLAIN但看完之后一脸茫然不知道那些字段代表什么意思。我来用最通俗的方式给你拆解一下。当你在SQL前面加上EXPLAIN执行后会得到一张表格核心关注以下几个字段表格字段名 含义 需要关注的点type 访问类型 至少要达到range级别出现ALL就是全表扫描possible_keys 可能用到的索引 如果为NULL说明没有可用索引key 实际使用的索引 和possible_keys对比看优化器选了哪个rows 预估扫描行数 数字越小越好Extra 额外信息 出现Using filesort或Using temporary要警惕拿我上面那个案例来说优化前的EXPLAIN结果大概是这样的表格id select_type table type possible_keys key rows Extra1 SIMPLE o ALL NULL NULL 8000000 Using where; Using temporary; Using filesort1 SIMPLE c eq_ref PRIMARY PRIMARY 1 Using where看到type是ALLrows是八百万Extra里还出现了Using temporary和Using filesort这基本就是慢查询的确诊通知书了。优化之后再看表格id select_type table type possible_keys key rows Extra1 SIMPLE o range idx_create_status idx_create_status 120000 Using index condition1 SIMPLE c eq_ref PRIMARY PRIMARY 1 Using wheretype变成了rangerows从八百万降到了十二万Extra里那些碍眼的东西也没了。这就是优化的直观效果。四、索引策略不是建得越多越好‌说到索引很多新手有一个误区觉得索引建得越多查询越快。实际上索引是一把双刃剑。每多一个索引写入操作就要多维护一棵B树插入、更新、删除的开销都会增加。而且索引占用磁盘空间太多了还会影响缓冲池的命中率。1、优先为WHERE条件、JOIN关联字段、ORDER BY排序字段建立索引这是最基本的原则。2、遵循最左前缀原则。如果你建了一个组合索引(a, b, c)那么查询条件必须从最左边的字段开始匹配才能用到索引。比如WHERE a 1 AND b 2可以用到但WHERE b 2 AND c 3就用不到。3、注意区分度。像性别这种只有两三个值的字段单独建索引意义不大因为区分度太低优化器可能根本不会选它。但如果是和其他字段组合比如(status, create_time)那就很有价值了。4、避免在索引列上做函数运算。比如WHERE YEAR(create_time) 2024这种写法会导致索引失效应该改成范围查询WHERE create_time 2024-01-01 AND create_time 2025-01-01。我再给你举一个反面例子。有个同事曾经在一张用户表上建了十几个索引结果发现写入速度慢得离谱。后来我帮他分析发现其中有五个索引几乎从来没被用到过果断删掉之后写入性能恢复正常查询也没受影响。五、查询优化的几个实用技巧‌除了建索引和看执行计划还有一些立竿见影的小技巧值得掌握。1、能用EXISTS就别用IN。在某些场景下EXISTS的执行效率比IN高很多尤其是子查询结果集比较大的时候。比如sql-- 相对较慢的写法SELECT * FROM t_orderWHERE user_id IN (SELECT id FROM t_user WHERE status 1);-- 更快的写法SELECT * FROM t_order oWHERE EXISTS (SELECT 1 FROM t_user u WHERE u.id o.user_id AND u.status 1);2、避免SELECT *。只查需要的字段不仅减少网络传输还有可能命中覆盖索引避免回表操作。3、大批量插入或更新时分批处理。一次性操作几十万行数据锁表时间长还可能撑爆事务日志。分批次、小事务提交系统会更稳定。4、善用LIMIT。如果业务允许不要一次把所有数据都查出来分页查询是最基本的优化手段。5、定期做慢查询日志分析。MySQL自带的slow_query_log是个好东西开启之后把超过一定阈值的SQL都记录下来定期复盘你会发现很多重复出现的慢查询模式。六、从实战中总结的几点心得‌写了这么多最后分享几条我个人的经验之谈。1、不要迷信任何一条银弹规则。SQL优化没有放之四海而皆准的公式同样的写法在不同的数据分布、不同的MySQL版本下表现可能完全不同。一定要结合实际数据去验证。2、养成看执行计划的习惯。不是出了问题才去看而是写SQL的时候就先想一想这条语句大概会怎么执行提前预判可能的问题。3、关注业务语义。有时候慢查询不是SQL本身的问题而是业务逻辑可以调整。比如有些统计需求没必要实时计算可以做成预聚合的报表或者定时任务从根本上消除压力。4、多和DBA交流。数据库管理员对系统的整体负载、硬件瓶颈、参数配置比开发更了解遇到拿不准的问题多问一句可能省去你半天的折腾。5、保持学习的心态。MySQL每隔几年就有大版本更新新特性、新优化器策略层出不穷。八点零版本的窗口函数、CTE语法都能让很多复杂查询变得简洁高效值得持续关注。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围