我用Explain揪出了藏了半年的性能杀手我用Explain揪出了藏了半年的性能杀手同样一条SQL语句有人写完跑了30秒还在转圈有人花5分钟调优后0.02秒就出结果。这中间差的不是运气是方法。今天这篇文章我把这些年踩过的坑、总结出的SQL优化思路一次性全掏给你。不讲虚的全是实战案例和可直接复用的代码看完就能上手。一、SQL优化核心思路与Explain对比分析1、为什么要学Explain很多人写完SQL就觉得完事了至于为什么慢、慢在哪儿完全靠猜。其实MySQL早就给我们准备好了一把透视镜——EXPLAIN命令。它能让你看到查询执行计划的每一个细节全表扫描还是走了索引、扫描了多少行、有没有用到临时表。说白了不会看Explain优化SQL就跟闭着眼开车一样。下面这张表是我整理的Explain关键字段含义对照建议收藏字段名 含义说明 关注重点id 查询的序列号相同id表示从属关系 复杂查询时注意执行顺序select_type 查询类型SIMPLE、PRIMARY、SUBQUERY等 判断是否有子查询table 当前操作的表名 —type 访问类型性能从好到差system const eq_ref ref range index ALL 最核心字段至少要达到range级别possible_keys 可能用到的索引 看有没有命中预期索引key 实际使用的索引 和possible_keys对比看是否选对了key_len 索引使用的字节数 判断联合索引是否完全命中rows 预估需要扫描的行数 越小越好Extra 额外信息 出现Using filesort、Using temporary时要警惕2、一个真实的Explain对比案例我之前接手过一个订单查询接口原始SQL是这样的sqlSELECT o.order_id, o.user_id, o.amount, u.nickname, u.levelFROM orders oLEFT JOIN users u ON o.user_id u.user_idWHERE o.status 1AND o.create_time 2025-01-01ORDER BY o.create_time DESCLIMIT 20;这条SQL在数据量达到200万行时查询耗时超过8秒。我用EXPLAIN看了一下执行计划id select_type table type possible_keys key key_len rows Extra1 SIMPLE o ALL idx_status NULL NULL 1850000 Using where; Using filesort1 SIMPLE u eq_ref PRIMARY PRIMARY 4 1 —问题一目了然orders表走了全表扫描typeALL扫描了185万行还用了filesort排序。这能不慢吗优化之后的SQLsqlSELECT o.order_id, o.user_id, o.amount, u.nickname, u.levelFROM orders oLEFT JOIN users u ON o.user_id u.user_idWHERE o.status 1AND o.create_time 2025-01-01ORDER BY o.create_time DESCLIMIT 20;我做了两件事第一在orders表上建了联合索引idx_status_createtime (status, create_time)第二SQL本身没有改因为逻辑是对的问题出在索引上。再次EXPLAINid select_type table type possible_keys key key_len rows Extra1 SIMPLE o range idx_status_createtime idx_status_createtime 9 3200 Using index condition1 SIMPLE u eq_ref PRIMARY PRIMARY 4 1 —type从ALL变成了range扫描行数从185万降到3200查询时间直接从8秒掉到0.03秒。这就是索引策略带来的降维打击。二、索引策略示例联合索引到底怎么建1、最左前缀原则是铁律联合索引(a, b, c)查询条件必须从最左边开始匹配才能生效。WHERE a 1 AND b 2能用到索引但WHERE b 2 AND c 3就用不上。很多人建索引喜欢把常用字段全塞进去结果索引又大又慢因为MySQL只能从最左边开始用。举个例子我见过一个同学在用户表上建了(age, city, gender, name)四列联合索引结果他的查询是sqlSELECT * FROM users WHERE city Beijing AND gender 1;这条SQL压根用不上这个索引因为最左边的age没有出现。后来我建议他把city放到最左边改成(city, gender, age, name)问题立刻解决。2、覆盖索引能省掉回表操作回表这个概念很多人知道但不重视。简单说如果查询需要的字段都在索引里MySQL就不需要再去查主键索引也就是不需要回表这叫覆盖索引。比如上面的订单查询我把索引改成(status, create_time, order_id, user_id, amount)把SELECT要的字段都包进去EXPLAIN的Extra里就会出现Using index说明完全不需要回表id select_type table type possible_keys key key_len rows Extra1 SIMPLE o range idx_status_createtime idx_status_createtime 9 3200 Using index condition; Using indexExtra里同时出现Using index condition和Using index这就是覆盖索引的标志。3、索引不是越多越好这个坑我自己也踩过。曾经有张表我建了6个索引以为查什么都快。结果写入性能暴跌因为每插入一行数据MySQL要维护6棵B树。后来我用SHOW INDEX FROM table_name把所有索引列出来发现有3个索引的使用频率极低直接删掉之后写入速度提升了40%查询性能几乎没受影响。三、查询优化案例那些年我遇到的奇葩慢查询1、LIKE左模糊是索引杀手sqlSELECT * FROM users WHERE name LIKE %张%;这条SQL如果name上有普通索引基本等于没用。因为B树是按前缀排序的你让它从中间开始找它只能全表扫描。解决办法有两个要么用全文索引FULLTEXT要么改业务逻辑比如让用户输入姓名的前几个字来搜。2、OR条件可能导致索引失效sqlSELECT * FROM orders WHERE status 1 OR status 3;很多人以为status上有索引就能用但实际上当OR两边的字段不一致时MySQL可能会放弃索引。更好的写法是用UNION ALLsqlSELECT * FROM orders WHERE status 1UNION ALLSELECT * FROM orders WHERE status 3;这样每条分支都能走索引实际测试下来比OR快了将近3倍。3、子查询改写成JOIN这个案例我印象特别深。有条SQL是这样的sqlSELECT * FROM ordersWHERE user_id IN (SELECT user_id FROM users WHERE level 5);当users表有10万行、orders表有200万行时这条SQL跑了15秒。把它改成JOINsqlSELECT o.*FROM orders oINNER JOIN users u ON o.user_id u.user_idWHERE u.level 5;同样的逻辑改完之后只要0.8秒。原因是MySQL对子查询的优化在老版本里很差JOIN的执行计划通常更优。四、Explain实战对比优化前后差距有多大我把前面几个案例的优化前后数据汇总成表格这样对比更直观案例 优化前type 优化前rows 优化后type 优化后rows 耗时对比订单查询 ALL 1850000 range 3200 8s → 0.03s姓名模糊搜索 ALL 500000 fulltext 1 4s → 0.01sOR条件查询 ALL 320000 ref 160000 2.5s → 0.8s子查询改写 ALL 2000000 ref 48000 15s → 0.8s你看同样是SQL调优思路对了性能提升不是百分之几十的事是几十倍甚至上千倍的事。五、日常优化的几个实用习惯☆ 1、慢查询日志一定要开。在my.cnf里设置slow_query_log 1和long_query_time 1把超过1秒的查询都记下来定期分析。☆ 2、建索引之前先用EXPLAIN验证。不要凭感觉建索引先写好SQL看执行计划确认索引能被用到再建。☆ 3、定期用ANALYZE TABLE table_name更新统计信息。MySQL的查询优化器依赖统计信息来选执行计划数据变动大了但统计信息没更新就可能选错索引。☆ 4、能用EXISTS就别用IN。当子查询结果集很大时EXISTS一旦找到匹配就会停止扫描而IN要把所有结果都拿出来比对。六、写在最后SQL优化这件事说难也难说简单也简单。难的是每个业务场景都不一样需要具体问题具体分析简单的是方法论就那么几条看Explain、建对索引、少用函数、避免全表扫描。把这几条刻进脑子里遇到慢查询的时候就不会慌。希望这篇文章能帮你少走一些弯路。如果你手里也有经典的SQL优化案例欢迎在评论区一起交流。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口https://blog.csdn.net/Start_mswin 复制到【浏览器】打开即可,宝贝入口https://pan.quark.cn/s/b42958e1c3c0 宝贝https://pan.quark.cn/s/1eb92d021d17作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围
我用Explain揪出了藏了半年的性能杀手
我用Explain揪出了藏了半年的性能杀手我用Explain揪出了藏了半年的性能杀手同样一条SQL语句有人写完跑了30秒还在转圈有人花5分钟调优后0.02秒就出结果。这中间差的不是运气是方法。今天这篇文章我把这些年踩过的坑、总结出的SQL优化思路一次性全掏给你。不讲虚的全是实战案例和可直接复用的代码看完就能上手。一、SQL优化核心思路与Explain对比分析1、为什么要学Explain很多人写完SQL就觉得完事了至于为什么慢、慢在哪儿完全靠猜。其实MySQL早就给我们准备好了一把透视镜——EXPLAIN命令。它能让你看到查询执行计划的每一个细节全表扫描还是走了索引、扫描了多少行、有没有用到临时表。说白了不会看Explain优化SQL就跟闭着眼开车一样。下面这张表是我整理的Explain关键字段含义对照建议收藏字段名 含义说明 关注重点id 查询的序列号相同id表示从属关系 复杂查询时注意执行顺序select_type 查询类型SIMPLE、PRIMARY、SUBQUERY等 判断是否有子查询table 当前操作的表名 —type 访问类型性能从好到差system const eq_ref ref range index ALL 最核心字段至少要达到range级别possible_keys 可能用到的索引 看有没有命中预期索引key 实际使用的索引 和possible_keys对比看是否选对了key_len 索引使用的字节数 判断联合索引是否完全命中rows 预估需要扫描的行数 越小越好Extra 额外信息 出现Using filesort、Using temporary时要警惕2、一个真实的Explain对比案例我之前接手过一个订单查询接口原始SQL是这样的sqlSELECT o.order_id, o.user_id, o.amount, u.nickname, u.levelFROM orders oLEFT JOIN users u ON o.user_id u.user_idWHERE o.status 1AND o.create_time 2025-01-01ORDER BY o.create_time DESCLIMIT 20;这条SQL在数据量达到200万行时查询耗时超过8秒。我用EXPLAIN看了一下执行计划id select_type table type possible_keys key key_len rows Extra1 SIMPLE o ALL idx_status NULL NULL 1850000 Using where; Using filesort1 SIMPLE u eq_ref PRIMARY PRIMARY 4 1 —问题一目了然orders表走了全表扫描typeALL扫描了185万行还用了filesort排序。这能不慢吗优化之后的SQLsqlSELECT o.order_id, o.user_id, o.amount, u.nickname, u.levelFROM orders oLEFT JOIN users u ON o.user_id u.user_idWHERE o.status 1AND o.create_time 2025-01-01ORDER BY o.create_time DESCLIMIT 20;我做了两件事第一在orders表上建了联合索引idx_status_createtime (status, create_time)第二SQL本身没有改因为逻辑是对的问题出在索引上。再次EXPLAINid select_type table type possible_keys key key_len rows Extra1 SIMPLE o range idx_status_createtime idx_status_createtime 9 3200 Using index condition1 SIMPLE u eq_ref PRIMARY PRIMARY 4 1 —type从ALL变成了range扫描行数从185万降到3200查询时间直接从8秒掉到0.03秒。这就是索引策略带来的降维打击。二、索引策略示例联合索引到底怎么建1、最左前缀原则是铁律联合索引(a, b, c)查询条件必须从最左边开始匹配才能生效。WHERE a 1 AND b 2能用到索引但WHERE b 2 AND c 3就用不上。很多人建索引喜欢把常用字段全塞进去结果索引又大又慢因为MySQL只能从最左边开始用。举个例子我见过一个同学在用户表上建了(age, city, gender, name)四列联合索引结果他的查询是sqlSELECT * FROM users WHERE city Beijing AND gender 1;这条SQL压根用不上这个索引因为最左边的age没有出现。后来我建议他把city放到最左边改成(city, gender, age, name)问题立刻解决。2、覆盖索引能省掉回表操作回表这个概念很多人知道但不重视。简单说如果查询需要的字段都在索引里MySQL就不需要再去查主键索引也就是不需要回表这叫覆盖索引。比如上面的订单查询我把索引改成(status, create_time, order_id, user_id, amount)把SELECT要的字段都包进去EXPLAIN的Extra里就会出现Using index说明完全不需要回表id select_type table type possible_keys key key_len rows Extra1 SIMPLE o range idx_status_createtime idx_status_createtime 9 3200 Using index condition; Using indexExtra里同时出现Using index condition和Using index这就是覆盖索引的标志。3、索引不是越多越好这个坑我自己也踩过。曾经有张表我建了6个索引以为查什么都快。结果写入性能暴跌因为每插入一行数据MySQL要维护6棵B树。后来我用SHOW INDEX FROM table_name把所有索引列出来发现有3个索引的使用频率极低直接删掉之后写入速度提升了40%查询性能几乎没受影响。三、查询优化案例那些年我遇到的奇葩慢查询1、LIKE左模糊是索引杀手sqlSELECT * FROM users WHERE name LIKE %张%;这条SQL如果name上有普通索引基本等于没用。因为B树是按前缀排序的你让它从中间开始找它只能全表扫描。解决办法有两个要么用全文索引FULLTEXT要么改业务逻辑比如让用户输入姓名的前几个字来搜。2、OR条件可能导致索引失效sqlSELECT * FROM orders WHERE status 1 OR status 3;很多人以为status上有索引就能用但实际上当OR两边的字段不一致时MySQL可能会放弃索引。更好的写法是用UNION ALLsqlSELECT * FROM orders WHERE status 1UNION ALLSELECT * FROM orders WHERE status 3;这样每条分支都能走索引实际测试下来比OR快了将近3倍。3、子查询改写成JOIN这个案例我印象特别深。有条SQL是这样的sqlSELECT * FROM ordersWHERE user_id IN (SELECT user_id FROM users WHERE level 5);当users表有10万行、orders表有200万行时这条SQL跑了15秒。把它改成JOINsqlSELECT o.*FROM orders oINNER JOIN users u ON o.user_id u.user_idWHERE u.level 5;同样的逻辑改完之后只要0.8秒。原因是MySQL对子查询的优化在老版本里很差JOIN的执行计划通常更优。四、Explain实战对比优化前后差距有多大我把前面几个案例的优化前后数据汇总成表格这样对比更直观案例 优化前type 优化前rows 优化后type 优化后rows 耗时对比订单查询 ALL 1850000 range 3200 8s → 0.03s姓名模糊搜索 ALL 500000 fulltext 1 4s → 0.01sOR条件查询 ALL 320000 ref 160000 2.5s → 0.8s子查询改写 ALL 2000000 ref 48000 15s → 0.8s你看同样是SQL调优思路对了性能提升不是百分之几十的事是几十倍甚至上千倍的事。五、日常优化的几个实用习惯☆ 1、慢查询日志一定要开。在my.cnf里设置slow_query_log 1和long_query_time 1把超过1秒的查询都记下来定期分析。☆ 2、建索引之前先用EXPLAIN验证。不要凭感觉建索引先写好SQL看执行计划确认索引能被用到再建。☆ 3、定期用ANALYZE TABLE table_name更新统计信息。MySQL的查询优化器依赖统计信息来选执行计划数据变动大了但统计信息没更新就可能选错索引。☆ 4、能用EXISTS就别用IN。当子查询结果集很大时EXISTS一旦找到匹配就会停止扫描而IN要把所有结果都拿出来比对。六、写在最后SQL优化这件事说难也难说简单也简单。难的是每个业务场景都不一样需要具体问题具体分析简单的是方法论就那么几条看Explain、建对索引、少用函数、避免全表扫描。把这几条刻进脑子里遇到慢查询的时候就不会慌。希望这篇文章能帮你少走一些弯路。如果你手里也有经典的SQL优化案例欢迎在评论区一起交流。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口https://blog.csdn.net/Start_mswin 复制到【浏览器】打开即可,宝贝入口https://pan.quark.cn/s/b42958e1c3c0 宝贝https://pan.quark.cn/s/1eb92d021d17作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围
