从Explain到SQL优化一次生产环境慢查询的完整调优复盘线上接口突然变慢用户投诉如潮水般涌来DBA紧急排查发现竟然是一条看起来没问题的SQL在搞鬼——这种场景你一定不陌生。本文将用一个真实的生产案例带你从头到尾走一遍SQL优化的全流程从Explain分析到索引重构每一步都踩在实战的点子上。一、问题背景一条SQL拖垮了整个接口事情发生在去年双十一大促的前两天我们的订单查询接口响应时间从平时的200ms飙到了8秒多部分请求甚至直接超时。运维拉出慢查询日志一看矛头直指一条关联了四张表的订单列表查询SQL。这条SQL说起来也不算复杂核心逻辑就是根据用户ID和订单状态分页拉取订单列表同时关联商品表和收货地址表拼接出前端需要展示的字段。开发写的时候觉得逻辑挺清晰谁能想到上了生产环境就成了定时炸弹。先把这条SQL贴出来大家感受一下sqlSELECTo.id, o.order_no, o.create_time, o.status,g.goods_name, g.price, g.image_url,a.receiver_name, a.receiver_phone, a.receiver_addressFROM orders oLEFT JOIN order_goods og ON o.id og.order_idLEFT JOIN goods g ON og.goods_id g.idLEFT JOIN address a ON o.address_id a.idWHERE o.user_id 123456AND o.status IN (1, 2, 3)ORDER BY o.create_time DESCLIMIT 10 OFFSET 0;看着确实不算太离谱对吧但就是这条SQL在数据量只有50万订单的时候已经让数据库喘不过气了。接下来我们就一步步拆解它到底烂在哪里。二、Explain分析数据不会说谎拿到慢SQL之后第一件事就是丢进Explain里跑一遍。以下是优化前的Explain结果id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o ALL idx_user_id NULL NULL NULL 487362 Using where; Using filesort1 SIMPLE og ref idx_order_id idx_order_id 8 db.o.id 2 NULL1 SIMPLE g eq_ref PRIMARY PRIMARY 4 db.og.goods_id 1 NULL1 SIMPLE a eq_ref PRIMARY PRIMARY 4 db.o.address_id 1 NULL看到第一行没有type是ALL这意味着orders表做了全表扫描。possible_keys显示idx_user_id这个索引其实是存在的但key列是NULL说明MySQL压根没走这个索引。再看rows直接扫了48万多行然后Extra里还写着Using filesort说明排序也没用上索引。这下问题就很清晰了1、orders表的user_id字段虽然建了索引但由于WHERE条件里还带了status IN (1,2,3)MySQL优化器评估之后觉得走索引不如全表扫描划算于是直接放弃了索引。2、ORDER BY create_time DESC也没有命中任何索引导致每次查询都要额外做一次文件排序。3、LIMIT虽然只要10条但因为前面扫描和排序的代价太大LIMIT根本救不了场。三、优化思路先想清楚再动手在动手改SQL之前我习惯先在脑子里过一遍优化的几个方向而不是上来就加索引或者改语句。这次的优化思路主要有三条1、让索引真正被用上既然user_id有索引但没被选中那就需要调整索引结构或者调整查询条件让优化器愿意走索引。2、消除文件排序ORDER BY的字段必须和索引的排序方向一致否则filesort永远消不掉。3、减少回表次数当前查询需要回表取goods_name、price等字段如果能用覆盖索引把常用字段都包含进去可以大幅减少随机IO。思路理清楚了接下来就是实操。四、第一步重构联合索引原来的idx_user_id是一个单字段索引在面对多条件查询时覆盖能力不够。我的做法是直接建一个联合索引把user_id、status、create_time三个字段组合在一起sqlALTER TABLE ordersDROP INDEX idx_user_id,ADD INDEX idx_user_status_time (user_id, status, create_time);为什么这么建因为联合索引遵循最左前缀原则user_id在最前面可以快速定位到某个用户的订单status放在第二位可以在user_id确定的范围内进一步过滤create_time放在最后刚好可以支撑ORDER BY的排序需求而且是DESC方向和索引的排列一致。建完索引之后再跑一遍Explainid select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o ref idx_user_status_time idx_user_status_time 12 const,const 36 Using index condition1 SIMPLE og ref idx_order_id idx_order_id 8 db.o.id 2 NULL1 SIMPLE g eq_ref PRIMARY PRIMARY 4 db.og.goods_id 1 NULL1 SIMPLE a eq_ref PRIMARY PRIMARY 4 db.o.address_id 1 NULL对比一下优化前的结果type从ALL变成了ref扫描行数从48万多降到了36行Extra里的Using filesort也消失了。这一波操作下来查询时间直接从8秒降到了50毫秒以内。五、第二步用覆盖索引减少回表虽然第一步已经解决了大部分问题但我在review的时候发现SELECT里还拉了o.order_no和o.status这两个字段而当前的联合索引只包含了user_id、status、create_time并没有包含order_no。这意味着即使走了索引回表的时候还是要多查一次。既然都已经在建索引了不如一步到位把order_no也加进去做成一个覆盖索引sqlALTER TABLE ordersDROP INDEX idx_user_status_time,ADD INDEX idx_user_status_time_no (user_id, status, create_time, order_no);加完之后orders表的所有查询字段user_id、status、create_time、order_no、id全都能从索引里拿到完全不需要回表。再看Explainid select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o ref idx_user_status_time_no idx_user_status_time_no 28 const,const 36 Using index condition; Using index1 SIMPLE og ref idx_order_id idx_order_id 8 db.o.id 2 NULL1 SIMPLE g eq_ref PRIMARY PRIMARY 4 db.og.goods_id 1 NULL1 SIMPLE a eq_ref PRIMARY PRIMARY 4 db.o.address_id 1 NULLExtra里多了一个Using index这就是覆盖索引生效的标志。此时查询已经完全不需要访问orders表的数据行了所有需要的信息都在索引B树上就能拿到。六、第三步SQL语句层面的小调整索引优化完了其实还有一个小细节可以再抠一下。原SQL里用的是status IN (1, 2, 3)在某些MySQL版本中IN列表如果值比较多优化器可能会选择全表扫描而不是走索引。虽然这里只有三个值但我还是习惯把它改写成等价的OR条件有时候能让优化器的选择更明确sqlWHERE o.user_id 123456AND (o.status 1 OR o.status 2 OR o.status 3)ORDER BY o.create_time DESCLIMIT 10 OFFSET 0;实际测试下来这两种写法在新版本MySQL中性能几乎没有差别但在老版本上OR写法有时反而更稳定。另外我还把LIMIT 10 OFFSET 0改成了LIMIT 10因为OFFSET 0本身是多此一举的去掉之后语句更干净也能让优化器少做一步计算。七、优化效果对比数据说话最后把优化前后的关键指标拉出来做个对比这样更直观指标 优化前 优化后 提升幅度查询耗时 8000ms 48ms 约99.4%扫描行数 487362 36 约99.99%是否使用索引 否 是覆盖索引 —是否文件排序 是 否 —是否回表 是 否 —从8秒到不到50毫秒这个提升幅度说实话连我自己都有点意外。但回头想想其实也不意外——原来那条SQL相当于在50万行数据里大海捞针还顺便排了个序优化之后相当于直接翻到了对应的那一页拿起来就走能不快吗八、几点实战中的经验总结通过这次调优我总结了几条在日常工作中特别实用的经验分享给大家1、不要迷信单字段索引很多开发习惯给每个WHERE条件的字段单独建一个索引但面对多条件组合查询时联合索引的效果往往远好于多个单字段索引。索引不是越多越好而是越精准越好。2、Explain一定要看全不要只看type和keyExtra列里的信息同样关键。Using filesort、Using temporary这两个标志一旦出现基本就意味着还有优化空间。3、覆盖索引是大杀器如果一个查询的所有字段都能从索引里拿到那这个查询的性能几乎可以达到理论极限。建索引的时候多想一步把常用的查询字段也加进去收益非常大。4、LIMIT不是万能的很多人以为加了LIMIT就万事大吉但如果前面的扫描和排序代价太大LIMIT 10和LIMIT 10000的区别其实没那么大。治本的方法还是让索引把扫描范围缩小。5、改完一定要验证每次优化完都要跑一遍Explain确认执行计划确实变了同时在测试环境用真实数据量压测一下不要只在开发环境的几条数据上自嗨。九、写在最后SQL优化这件事说难也难说简单也简单。难的是面对复杂业务场景时要能快速定位问题简单的是只要你掌握了Explain的分析方法注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口https://blog.csdn.net/Start_mswin 复制到【浏览器】打开即可,宝贝入口https://pan.quark.cn/s/b42958e1c3c0 宝贝https://pan.quark.cn/s/1eb92d021d17作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围
从Explain到SQL优化：一次生产环境慢查询的完整调优复盘
从Explain到SQL优化一次生产环境慢查询的完整调优复盘线上接口突然变慢用户投诉如潮水般涌来DBA紧急排查发现竟然是一条看起来没问题的SQL在搞鬼——这种场景你一定不陌生。本文将用一个真实的生产案例带你从头到尾走一遍SQL优化的全流程从Explain分析到索引重构每一步都踩在实战的点子上。一、问题背景一条SQL拖垮了整个接口事情发生在去年双十一大促的前两天我们的订单查询接口响应时间从平时的200ms飙到了8秒多部分请求甚至直接超时。运维拉出慢查询日志一看矛头直指一条关联了四张表的订单列表查询SQL。这条SQL说起来也不算复杂核心逻辑就是根据用户ID和订单状态分页拉取订单列表同时关联商品表和收货地址表拼接出前端需要展示的字段。开发写的时候觉得逻辑挺清晰谁能想到上了生产环境就成了定时炸弹。先把这条SQL贴出来大家感受一下sqlSELECTo.id, o.order_no, o.create_time, o.status,g.goods_name, g.price, g.image_url,a.receiver_name, a.receiver_phone, a.receiver_addressFROM orders oLEFT JOIN order_goods og ON o.id og.order_idLEFT JOIN goods g ON og.goods_id g.idLEFT JOIN address a ON o.address_id a.idWHERE o.user_id 123456AND o.status IN (1, 2, 3)ORDER BY o.create_time DESCLIMIT 10 OFFSET 0;看着确实不算太离谱对吧但就是这条SQL在数据量只有50万订单的时候已经让数据库喘不过气了。接下来我们就一步步拆解它到底烂在哪里。二、Explain分析数据不会说谎拿到慢SQL之后第一件事就是丢进Explain里跑一遍。以下是优化前的Explain结果id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o ALL idx_user_id NULL NULL NULL 487362 Using where; Using filesort1 SIMPLE og ref idx_order_id idx_order_id 8 db.o.id 2 NULL1 SIMPLE g eq_ref PRIMARY PRIMARY 4 db.og.goods_id 1 NULL1 SIMPLE a eq_ref PRIMARY PRIMARY 4 db.o.address_id 1 NULL看到第一行没有type是ALL这意味着orders表做了全表扫描。possible_keys显示idx_user_id这个索引其实是存在的但key列是NULL说明MySQL压根没走这个索引。再看rows直接扫了48万多行然后Extra里还写着Using filesort说明排序也没用上索引。这下问题就很清晰了1、orders表的user_id字段虽然建了索引但由于WHERE条件里还带了status IN (1,2,3)MySQL优化器评估之后觉得走索引不如全表扫描划算于是直接放弃了索引。2、ORDER BY create_time DESC也没有命中任何索引导致每次查询都要额外做一次文件排序。3、LIMIT虽然只要10条但因为前面扫描和排序的代价太大LIMIT根本救不了场。三、优化思路先想清楚再动手在动手改SQL之前我习惯先在脑子里过一遍优化的几个方向而不是上来就加索引或者改语句。这次的优化思路主要有三条1、让索引真正被用上既然user_id有索引但没被选中那就需要调整索引结构或者调整查询条件让优化器愿意走索引。2、消除文件排序ORDER BY的字段必须和索引的排序方向一致否则filesort永远消不掉。3、减少回表次数当前查询需要回表取goods_name、price等字段如果能用覆盖索引把常用字段都包含进去可以大幅减少随机IO。思路理清楚了接下来就是实操。四、第一步重构联合索引原来的idx_user_id是一个单字段索引在面对多条件查询时覆盖能力不够。我的做法是直接建一个联合索引把user_id、status、create_time三个字段组合在一起sqlALTER TABLE ordersDROP INDEX idx_user_id,ADD INDEX idx_user_status_time (user_id, status, create_time);为什么这么建因为联合索引遵循最左前缀原则user_id在最前面可以快速定位到某个用户的订单status放在第二位可以在user_id确定的范围内进一步过滤create_time放在最后刚好可以支撑ORDER BY的排序需求而且是DESC方向和索引的排列一致。建完索引之后再跑一遍Explainid select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o ref idx_user_status_time idx_user_status_time 12 const,const 36 Using index condition1 SIMPLE og ref idx_order_id idx_order_id 8 db.o.id 2 NULL1 SIMPLE g eq_ref PRIMARY PRIMARY 4 db.og.goods_id 1 NULL1 SIMPLE a eq_ref PRIMARY PRIMARY 4 db.o.address_id 1 NULL对比一下优化前的结果type从ALL变成了ref扫描行数从48万多降到了36行Extra里的Using filesort也消失了。这一波操作下来查询时间直接从8秒降到了50毫秒以内。五、第二步用覆盖索引减少回表虽然第一步已经解决了大部分问题但我在review的时候发现SELECT里还拉了o.order_no和o.status这两个字段而当前的联合索引只包含了user_id、status、create_time并没有包含order_no。这意味着即使走了索引回表的时候还是要多查一次。既然都已经在建索引了不如一步到位把order_no也加进去做成一个覆盖索引sqlALTER TABLE ordersDROP INDEX idx_user_status_time,ADD INDEX idx_user_status_time_no (user_id, status, create_time, order_no);加完之后orders表的所有查询字段user_id、status、create_time、order_no、id全都能从索引里拿到完全不需要回表。再看Explainid select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o ref idx_user_status_time_no idx_user_status_time_no 28 const,const 36 Using index condition; Using index1 SIMPLE og ref idx_order_id idx_order_id 8 db.o.id 2 NULL1 SIMPLE g eq_ref PRIMARY PRIMARY 4 db.og.goods_id 1 NULL1 SIMPLE a eq_ref PRIMARY PRIMARY 4 db.o.address_id 1 NULLExtra里多了一个Using index这就是覆盖索引生效的标志。此时查询已经完全不需要访问orders表的数据行了所有需要的信息都在索引B树上就能拿到。六、第三步SQL语句层面的小调整索引优化完了其实还有一个小细节可以再抠一下。原SQL里用的是status IN (1, 2, 3)在某些MySQL版本中IN列表如果值比较多优化器可能会选择全表扫描而不是走索引。虽然这里只有三个值但我还是习惯把它改写成等价的OR条件有时候能让优化器的选择更明确sqlWHERE o.user_id 123456AND (o.status 1 OR o.status 2 OR o.status 3)ORDER BY o.create_time DESCLIMIT 10 OFFSET 0;实际测试下来这两种写法在新版本MySQL中性能几乎没有差别但在老版本上OR写法有时反而更稳定。另外我还把LIMIT 10 OFFSET 0改成了LIMIT 10因为OFFSET 0本身是多此一举的去掉之后语句更干净也能让优化器少做一步计算。七、优化效果对比数据说话最后把优化前后的关键指标拉出来做个对比这样更直观指标 优化前 优化后 提升幅度查询耗时 8000ms 48ms 约99.4%扫描行数 487362 36 约99.99%是否使用索引 否 是覆盖索引 —是否文件排序 是 否 —是否回表 是 否 —从8秒到不到50毫秒这个提升幅度说实话连我自己都有点意外。但回头想想其实也不意外——原来那条SQL相当于在50万行数据里大海捞针还顺便排了个序优化之后相当于直接翻到了对应的那一页拿起来就走能不快吗八、几点实战中的经验总结通过这次调优我总结了几条在日常工作中特别实用的经验分享给大家1、不要迷信单字段索引很多开发习惯给每个WHERE条件的字段单独建一个索引但面对多条件组合查询时联合索引的效果往往远好于多个单字段索引。索引不是越多越好而是越精准越好。2、Explain一定要看全不要只看type和keyExtra列里的信息同样关键。Using filesort、Using temporary这两个标志一旦出现基本就意味着还有优化空间。3、覆盖索引是大杀器如果一个查询的所有字段都能从索引里拿到那这个查询的性能几乎可以达到理论极限。建索引的时候多想一步把常用的查询字段也加进去收益非常大。4、LIMIT不是万能的很多人以为加了LIMIT就万事大吉但如果前面的扫描和排序代价太大LIMIT 10和LIMIT 10000的区别其实没那么大。治本的方法还是让索引把扫描范围缩小。5、改完一定要验证每次优化完都要跑一遍Explain确认执行计划确实变了同时在测试环境用真实数据量压测一下不要只在开发环境的几条数据上自嗨。九、写在最后SQL优化这件事说难也难说简单也简单。难的是面对复杂业务场景时要能快速定位问题简单的是只要你掌握了Explain的分析方法注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口https://blog.csdn.net/Start_mswin 复制到【浏览器】打开即可,宝贝入口https://pan.quark.cn/s/b42958e1c3c0 宝贝https://pan.quark.cn/s/1eb92d021d17作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围
