从全表扫描到覆盖索引我是怎么干掉慢查询的生产环境一次慢查询拖垮整条业务线查了三天最后发现问题竟然出在一个JOIN上——这种事我见过太多了。SQL优化不是玄学它有方法论、有套路、有可复制的路径。今天我就拿一个真实案例把从发现问题到解决问题的全过程拆开讲透看完你就能直接上手用。一、一次真实的慢查询事故去年我们团队接手了一个电商后台项目用户反馈订单列表页加载特别慢。我登录数据库一看果然有问题。这条查询语句大概长这样sqlSELECT o.order_id, o.create_time, u.nickname, p.product_name,oi.quantity, oi.priceFROM orders oLEFT JOIN users u ON o.user_id u.user_idLEFT JOIN order_items oi ON o.order_id oi.order_idLEFT JOIN products p ON oi.product_id p.product_idWHERE o.create_time 2025-01-01AND o.status paidORDER BY o.create_time DESCLIMIT 20;这条语句在测试环境没什么问题但到了生产环境数据量表膨胀到几百万行之后执行时间直接飙到了8秒以上。用户等不了投诉不断。我用 EXPLAIN 看了一下执行计划结果触目惊心id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o ALL idx_create_time NULL NULL NULL 1200000 Using where; Using filesort1 SIMPLE u eq_ref PRIMARY PRIMARY 4 o.user_id 1 NULL1 SIMPLE oi ref idx_order_id idx_order_id 4 o.order_id 3 NULL1 SIMPLE p eq_ref PRIMARY PRIMARY 4 oi.product_id 1 NULL问题一目了然orders 表走了全表扫描扫描了120万行数据。typeALL 意味着没有用到任何索引Extra 里的 Using filesort 说明连排序都在内存里硬排没有利用索引的有序性。二、问题拆解这条SQL到底错在哪很多人看到慢查询第一反应是加索引。但加索引之前得先搞清楚问题的根源。1、WHERE 条件没命中索引WHERE o.create_time 2025-01-01 AND o.status paid 这两个条件create_time 字段有索引但 status 没有。数据库优化器在选择执行路径时发现 status 的区分度不高paid 订单占了总量的60%以上所以干脆放弃了 idx_create_time直接全表扫描。2、复合索引缺失导致优化器做了最差选择如果 (status, create_time) 上有一个复合索引优化器大概率会走这个索引。因为 status 是等值条件create_time 是范围条件复合索引的最左匹配原则刚好能用上。3、ORDER BY 无法利用索引虽然 create_time 上有单列索引但因为查询走了全表扫描ORDER BY o.create_time DESC 根本用不上这个索引的有序性导致额外的 filesort 操作。4、SELECT 列有不必要的字段o.order_id, o.create_time, u.nickname, p.product_name, oi.quantity, oi.price 这些字段全部需要这个倒没什么问题。但如果有大字段比如 TEXT 类型就会造成回表开销。三、优化方案三步走从8秒到0.3秒找到了病根接下来就是对症下药。第一步加复合索引sqlALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);加完之后再看 EXPLAINid select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o range idx_status_create_time idx_status_create_time 13 NULL 48000 Using index condition; Using filesort扫描行数从120万降到了4.8万已经是质的飞跃。但还有 Using filesort说明排序还是有问题。第二步调整索引顺序让排序也能用上索引把复合索引改成 (status, create_time DESC)sqlALTER TABLE orders DROP INDEX idx_status_create_time;ALTER TABLE orders ADD INDEX idx_status_create_time_desc (status, create_time DESC);再次 EXPLAINid select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o range idx_status_create_time_desc idx_status_create_time_desc 13 NULL 48000 Using index conditionUsing filesort 消失了因为索引本身就是按 create_time DESC 排序的数据库可以直接按索引顺序取数据不需要额外排序。第三步用覆盖索引减少回表观察一下 SELECT 的字段order_id、create_time、status 都在索引里但 user_id 不在。而 JOIN users 需要 user_id。如果把 user_id 也加进索引sqlALTER TABLE orders DROP INDEX idx_status_create_time_desc;ALTER TABLE orders ADD INDEX idx_status_time_user (status, create_time, user_id);优化后的执行计划id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o range idx_status_time_user idx_status_time_user 17 NULL 48000 Using index condition执行时间从8秒降到了0.3秒。从全表扫描120万行到索引范围扫描4.8万行再到利用覆盖索引避免回表每一步都有明确的收益。四、几个容易踩的坑优化SQL这件事不是加个索引就完了。有几个坑我必须提一下1、索引不是越多越好每加一个索引INSERT、UPDATE、DELETE 都要维护这个索引写操作的开销会增加。我见过一个项目一张表加了十几个索引结果查询快了但写入慢了三倍。索引要加在刀刃上优先考虑 WHERE、JOIN、ORDER BY 涉及的字段。2、区分度低的字段别单独建索引比如 status 字段如果只有 paid、unpaid、refund 三个值区分度极低。单独给 status 建索引优化器大概率不会用。这种字段适合放在复合索引的最左边配合高区分度字段一起用。3、LIKE %xxx% 几乎无法用索引左模糊查询会导致索引失效这是硬伤。如果业务上必须支持模糊搜索建议走 Elasticsearch别硬扛。4、函数操作会让索引失效sql-- 索引失效WHERE YEAR(create_time) 2025-- 走索引WHERE create_time 2025-01-01 AND create_time 2026-01-01把函数从字段上移到等号右边这个小改动能让索引从失效变成生效。5、EXPLAIN 不是看一次就够的很多人优化完看一眼 EXPLAIN 就觉得完事了。但数据量变化之后执行计划可能完全不同。我的习惯是在上线前、上线后、数据量翻倍后各看一次 EXPLAIN确保优化效果在不同数据规模下都稳定。五、总结SQL优化的核心思维回过头来看这次优化的本质是什么不是某个神奇的技巧而是三个基本动作1、让 WHERE 条件能命中索引复合索引 最左匹配2、让 ORDER BY 能利用索引有序性索引列顺序与排序方向一致3、让 SELECT 尽量走覆盖索引减少回表这三条做到了大部分慢查询都能解决。剩下的那些极端情况再考虑 EXPLAIN 分析、执行计划劫持、SQL重写等进阶手段。SQL优化这件事说难也难说简单也简单。难的是没有标准答案每条SQL的数据分布、业务场景都不一样。简单的是方法论就那些EXPLAIN 一跑问题基本就藏不住了。别迷信银弹也别一上来就加索引。先看执行计划再动手改改完再验证。这个习惯养成了你会发现大部分性能问题都能在半小时内定位并解决。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围
从全表扫描到覆盖索引：我是怎么干掉慢查询的
从全表扫描到覆盖索引我是怎么干掉慢查询的生产环境一次慢查询拖垮整条业务线查了三天最后发现问题竟然出在一个JOIN上——这种事我见过太多了。SQL优化不是玄学它有方法论、有套路、有可复制的路径。今天我就拿一个真实案例把从发现问题到解决问题的全过程拆开讲透看完你就能直接上手用。一、一次真实的慢查询事故去年我们团队接手了一个电商后台项目用户反馈订单列表页加载特别慢。我登录数据库一看果然有问题。这条查询语句大概长这样sqlSELECT o.order_id, o.create_time, u.nickname, p.product_name,oi.quantity, oi.priceFROM orders oLEFT JOIN users u ON o.user_id u.user_idLEFT JOIN order_items oi ON o.order_id oi.order_idLEFT JOIN products p ON oi.product_id p.product_idWHERE o.create_time 2025-01-01AND o.status paidORDER BY o.create_time DESCLIMIT 20;这条语句在测试环境没什么问题但到了生产环境数据量表膨胀到几百万行之后执行时间直接飙到了8秒以上。用户等不了投诉不断。我用 EXPLAIN 看了一下执行计划结果触目惊心id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o ALL idx_create_time NULL NULL NULL 1200000 Using where; Using filesort1 SIMPLE u eq_ref PRIMARY PRIMARY 4 o.user_id 1 NULL1 SIMPLE oi ref idx_order_id idx_order_id 4 o.order_id 3 NULL1 SIMPLE p eq_ref PRIMARY PRIMARY 4 oi.product_id 1 NULL问题一目了然orders 表走了全表扫描扫描了120万行数据。typeALL 意味着没有用到任何索引Extra 里的 Using filesort 说明连排序都在内存里硬排没有利用索引的有序性。二、问题拆解这条SQL到底错在哪很多人看到慢查询第一反应是加索引。但加索引之前得先搞清楚问题的根源。1、WHERE 条件没命中索引WHERE o.create_time 2025-01-01 AND o.status paid 这两个条件create_time 字段有索引但 status 没有。数据库优化器在选择执行路径时发现 status 的区分度不高paid 订单占了总量的60%以上所以干脆放弃了 idx_create_time直接全表扫描。2、复合索引缺失导致优化器做了最差选择如果 (status, create_time) 上有一个复合索引优化器大概率会走这个索引。因为 status 是等值条件create_time 是范围条件复合索引的最左匹配原则刚好能用上。3、ORDER BY 无法利用索引虽然 create_time 上有单列索引但因为查询走了全表扫描ORDER BY o.create_time DESC 根本用不上这个索引的有序性导致额外的 filesort 操作。4、SELECT 列有不必要的字段o.order_id, o.create_time, u.nickname, p.product_name, oi.quantity, oi.price 这些字段全部需要这个倒没什么问题。但如果有大字段比如 TEXT 类型就会造成回表开销。三、优化方案三步走从8秒到0.3秒找到了病根接下来就是对症下药。第一步加复合索引sqlALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);加完之后再看 EXPLAINid select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o range idx_status_create_time idx_status_create_time 13 NULL 48000 Using index condition; Using filesort扫描行数从120万降到了4.8万已经是质的飞跃。但还有 Using filesort说明排序还是有问题。第二步调整索引顺序让排序也能用上索引把复合索引改成 (status, create_time DESC)sqlALTER TABLE orders DROP INDEX idx_status_create_time;ALTER TABLE orders ADD INDEX idx_status_create_time_desc (status, create_time DESC);再次 EXPLAINid select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o range idx_status_create_time_desc idx_status_create_time_desc 13 NULL 48000 Using index conditionUsing filesort 消失了因为索引本身就是按 create_time DESC 排序的数据库可以直接按索引顺序取数据不需要额外排序。第三步用覆盖索引减少回表观察一下 SELECT 的字段order_id、create_time、status 都在索引里但 user_id 不在。而 JOIN users 需要 user_id。如果把 user_id 也加进索引sqlALTER TABLE orders DROP INDEX idx_status_create_time_desc;ALTER TABLE orders ADD INDEX idx_status_time_user (status, create_time, user_id);优化后的执行计划id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o range idx_status_time_user idx_status_time_user 17 NULL 48000 Using index condition执行时间从8秒降到了0.3秒。从全表扫描120万行到索引范围扫描4.8万行再到利用覆盖索引避免回表每一步都有明确的收益。四、几个容易踩的坑优化SQL这件事不是加个索引就完了。有几个坑我必须提一下1、索引不是越多越好每加一个索引INSERT、UPDATE、DELETE 都要维护这个索引写操作的开销会增加。我见过一个项目一张表加了十几个索引结果查询快了但写入慢了三倍。索引要加在刀刃上优先考虑 WHERE、JOIN、ORDER BY 涉及的字段。2、区分度低的字段别单独建索引比如 status 字段如果只有 paid、unpaid、refund 三个值区分度极低。单独给 status 建索引优化器大概率不会用。这种字段适合放在复合索引的最左边配合高区分度字段一起用。3、LIKE %xxx% 几乎无法用索引左模糊查询会导致索引失效这是硬伤。如果业务上必须支持模糊搜索建议走 Elasticsearch别硬扛。4、函数操作会让索引失效sql-- 索引失效WHERE YEAR(create_time) 2025-- 走索引WHERE create_time 2025-01-01 AND create_time 2026-01-01把函数从字段上移到等号右边这个小改动能让索引从失效变成生效。5、EXPLAIN 不是看一次就够的很多人优化完看一眼 EXPLAIN 就觉得完事了。但数据量变化之后执行计划可能完全不同。我的习惯是在上线前、上线后、数据量翻倍后各看一次 EXPLAIN确保优化效果在不同数据规模下都稳定。五、总结SQL优化的核心思维回过头来看这次优化的本质是什么不是某个神奇的技巧而是三个基本动作1、让 WHERE 条件能命中索引复合索引 最左匹配2、让 ORDER BY 能利用索引有序性索引列顺序与排序方向一致3、让 SELECT 尽量走覆盖索引减少回表这三条做到了大部分慢查询都能解决。剩下的那些极端情况再考虑 EXPLAIN 分析、执行计划劫持、SQL重写等进阶手段。SQL优化这件事说难也难说简单也简单。难的是没有标准答案每条SQL的数据分布、业务场景都不一样。简单的是方法论就那些EXPLAIN 一跑问题基本就藏不住了。别迷信银弹也别一上来就加索引。先看执行计划再动手改改完再验证。这个习惯养成了你会发现大部分性能问题都能在半小时内定位并解决。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围
