500万数据的慢SQL我只改了一行索引就起飞了你有没有经历过这样的场景一条SQL语句在测试环境跑得飞快上线之后却把整个数据库拖垮了这种水土不服的背后往往藏着你根本没看过的执行计划。今天这篇文章我会用真实案例带你从Explain分析入手一步步拆解SQL优化的核心套路看完你会发现所谓的调优高手不过是把这些细节做得更扎实而已。一、SQL优化为什么总被忽视很多开发者在写SQL的时候第一反应是能跑就行。尤其是业务初期数据量不大随便写条语句也能秒出结果。但随着业务增长数据量从几万涨到几百万甚至上千万原本能跑的SQL就变成了能跑但要命。☆ 数据库层面的性能瓶颈80%以上都跟SQL写法有关☆ 索引不是越多越好用错了反而比没索引还慢☆ Explain是最被低估的调优工具大部分人只看了一眼就关掉了我见过太多团队花大价钱升级服务器、加内存结果问题出在一条没加索引的关联查询上。说白了SQL优化的性价比远高于堆硬件。接下来我们就从最基础的Explain说起聊聊怎么把一条慢SQL一步步优化到极致。二、用Explain看透SQL的执行真相Explain是MySQL提供的执行计划分析工具它不会真正执行你的SQL而是告诉你数据库打算怎么执行这条语句。很多人用Explain只看type字段其实每个字段都有它的意义。我们先看一个实际案例。假设有一张订单表orders大概有500万条数据sqlCREATE TABLE orders (id BIGINT PRIMARY KEY AUTO_INCREMENT,user_id BIGINT NOT NULL,status TINYINT NOT NULL,create_time DATETIME NOT NULL,amount DECIMAL(10,2) NOT NULL,INDEX idx_user_status (user_id, status),INDEX idx_create_time (create_time));现在有这样一条查询sqlEXPLAIN SELECT * FROM ordersWHERE user_id 12345 AND status 1ORDER BY create_time DESCLIMIT 10;执行之后你会得到类似下面的结果id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 SIMPLE orders NULL ref idx_user_status idx_user_status 17 const,const 320 100.00 Using where; Using index; Using filesort重点看这几个字段1、type显示为ref说明用到了非唯一索引进行查找这是一个还不错的结果。如果是ALL那就意味着全表扫描直接报警。2、key实际使用的索引是idx_user_status说明优化器选择了联合索引这步没问题。3、rows预估扫描320行。对于500万的表来说这个数字还算可以接受。4、Extra这里出现了Using filesort这是个危险信号。说明虽然用了索引找数据但排序操作没有利用索引完成额外触发了文件排序。这就是性能隐患所在。三、索引策略示例联合索引的顺序到底怎么排上面那个案例里索引idx_user_status是(user_id, status)的顺序。很多人会问为什么不是(status, user_id)这里涉及到一个核心原则联合索引遵循最左前缀匹配原则索引列的顺序直接决定了查询能否命中索引。我们来做个对比sql-- 场景一按user_id查询status作为过滤条件SELECT * FROM ordersWHERE user_id 12345 AND status 1;-- 索引(user_id, status) → 完美命中-- 索引(status, user_id) → 只能用到status部分user_id走不了索引-- 场景二只按status查询SELECT * FROM orders WHERE status 1;-- 索引(user_id, status) → 走不了索引因为最左列user_id没出现-- 索引(status, user_id) → 可以命中所以索引列的排列顺序必须根据实际业务查询的频率来决定。一般的排布思路是1、等值查询的列放在最前面比如user_id xxx这种2、范围查询的列放在后面比如create_time 2024-01-013、排序用的列尽量放在索引的尾部这样可以避免filesort回到刚才的案例我们的查询条件是user_id和status都是等值但还有一个ORDER BY create_time。这意味着现有的联合索引无法覆盖排序需求。优化方案就是把索引改成sqlALTER TABLE orders DROP INDEX idx_user_status;ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);改完之后再看Explainid select_type table type key rows Extra1 SIMPLE orders ref idx_user_status_time 320 Using where; Using index注意看Extra字段Using filesort消失了变成了Using index。这说明排序也走了索引整个查询变成了覆盖索引扫描性能提升非常明显。四、查询优化案例从3秒到30毫秒的真实调优过程下面这个案例是我之前在一个电商项目中遇到的真实问题。业务方反馈用户中心的我的订单页面加载特别慢有时候要等三四秒。我拿到SQL一看sqlSELECT o.id, o.amount, o.create_time, u.nicknameFROM orders oLEFT JOIN users u ON o.user_id u.idWHERE o.status IN (1, 2, 3)AND o.create_time 2024-01-01ORDER BY o.create_time DESCLIMIT 20;先跑一下Explain看看问题出在哪id select_type table type possible_keys key rows Extra1 SIMPLE o ALL idx_create_time NULL 4800000 Using where; Using filesort1 SIMPLE u eq_ref PRIMARY PRIMARY 1 NULL问题很明显1、orders表走了全表扫描480万行全部扫了一遍2、status用的是IN条件但索引idx_create_time只包含create_timestatus完全没用上索引3、排序也触发了filesort优化步骤如下1、首先给orders表加一个更合适的联合索引把status和create_time都放进去sqlALTER TABLE orders ADD INDEX idx_status_time (status, create_time);2、把IN查询改成等价的写法让优化器更容易选择索引sqlSELECT o.id, o.amount, o.create_time, u.nicknameFROM orders oLEFT JOIN users u ON o.user_id u.idWHERE (o.status 1 OR o.status 2 OR o.status 3)AND o.create_time 2024-01-01ORDER BY o.create_time DESCLIMIT 20;3、进一步优化考虑到status只有三个值且create_time是范围查询我们把索引顺序调整为(create_time, status)因为范围查询放在联合索引中间会导致后面的列失效sqlALTER TABLE orders DROP INDEX idx_status_time;ALTER TABLE orders ADD INDEX idx_time_status (create_time, status);改完之后再看Explainid select_type table type possible_keys key rows Extra1 SIMPLE o range idx_time_status idx_time_status 15000 Using where; Using index1 SIMPLE u eq_ref PRIMARY PRIMARY 1 NULLrows从480万降到了1.5万Extra里的filesort也没了。实际执行时间从3.2秒降到了28毫秒。五、Explain对比优化前后的差距有多大为了让大家更直观地感受到优化效果我把前后两次Explain的关键指标做了一个对比表对比项 优化前 优化后 变化扫描行数 4800000 15000 降低99.7%访问类型 ALL全表扫描 range范围扫描 质的提升是否用到索引 否 是 从无到有是否触发filesort 是 否 消除排序开销执行时间 3.2秒 28毫秒 提升约114倍这个案例说明一个道理很多时候性能问题不是数据量的问题而是你的SQL和索引没有配合好。六、几个容易踩坑的SQL优化细节1、不要在索引列上做函数运算。比如WHERE YEAR(create_time) 2024这会让索引直接失效。应该改成create_time 2024-01-01 AND create_time 2025-01-01。2、LIKE查询的百分号不要放在最前面。WHERE name LIKE %张%是没法走索引的但WHERE name LIKE 张%可以走。3、尽量避免SELECT *。只查需要的字段尤其是有TEXT或BLOB字段的表SELECT *会把这些大字段也拉出来严重影响IO性能。4、JOIN的时候被驱动表右边那张表一定要有索引。比如A JOIN BB表的关联字段必须有索引否则会触发NLJ嵌套循环连接的全表扫描。5、LIMIT分页在大偏移量时也会变慢。比如LIMIT 1000000, 20数据库要先扫描1000020行再丢弃前1000000行。可以用WHERE id 上一页最后一条ID的方式来优化。七、总结SQL优化是一种思维方式SQL优化不是背几条规则就够了它更像是一种思维方式。你需要习惯在写完SQL之后看一眼Explain需要理解索引的底层原理需要知道优化器是怎么做选择的。☆ 工具是死的思路是活的。Explain给你的是数据怎么解读靠的是经验☆ 优化没有银弹每个案例都要具体分析但核心逻辑就那么几条☆ 养成好习惯比记住多少技巧都管用当你能从Explain的一行结果里快速定位问题、给出方案的时候你就已经超越了大部分开发者。希望这篇文章能帮你在SQL调优的路上少走一些弯路。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口https://blog.csdn.net/Start_mswin 复制到【浏览器】打开即可,宝贝入口https://pan.quark.cn/s/b42958e1c3c0 宝贝https://pan.quark.cn/s/1eb92d021d17作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围