查询优化案例集：从线上故障到性能跃升的实战复盘‌

查询优化案例集从线上故障到性能跃升的实战复盘‌你有没有过那种凌晨三点被电话惊醒的经历电话那头运维同事急得声音都在抖说线上数据库CPU直接冲到百分之百所有业务接口全部超时整个系统彻底瘫痪。你迷迷糊糊爬起来打开监控看到慢查询日志里几十条一模一样的SQL在疯狂执行每条都要跑几十秒瞬间睡意全无。我做数据库优化这几年这种场景遇到过不下十次。很多时候线上的性能灾难根本不是什么高深的分布式架构问题就是一条写得很糟糕的SQL引发的连锁反应。而大部分糟糕的SQL都不是凭空出现的背后藏着开发同学对数据库执行逻辑的误解、业务迭代留下的历史包袱还有早期数据量小的时候埋下的隐患。今天这篇文章我就把三个印象最深的线上查询优化案例完整复盘出来没有空泛的理论全是踩过坑、流过汗的真实经历看完你下次遇到类似问题至少能少走三天弯路。一、千万级表分页优化从三十秒到两百毫秒的逆袭第一个案例发生在去年的电商大促期间凌晨两点我接到紧急告警订单后台的所有查询接口全部超时数据库CPU使用率直接拉满到百分之百。我登上服务器一看慢查询日志里全是同一条分页SQL执行时间最长的达到了三十七秒。这条SQL的业务逻辑很简单运营同事要在后台分页查询所有订单按创建时间倒序排列支持按订单状态、用户手机号、支付方式筛选。原始的SQL写法是非常经典的OFFSET大分页sqlSELECT order_id, order_sn, user_id, order_amount, create_time, order_statusFROM ordersWHERE order_status 已支付ORDER BY create_time DESCLIMIT 100000, 20;当时订单表里已经有一千二百万条数据这条SQL要跳过十万条记录再取后面的二十条。很多人都知道OFFSET大分页慢但很少有人能说清楚它到底为什么慢。我用EXPLAIN看执行计划发现虽然create_time字段上建了索引但MySQL还是选择了全表扫描扫描行数达到了一千二百万行。为什么放着索引不用我仔细分析了优化器的选择逻辑。如果直接走create_time的索引因为是倒序排列MySQL需要从最新的订单开始往前扫扫到十万零二十条的时候才能拿到最后要返回的二十条数据。但这十万条数据里大部分订单状态都不是“已支付”MySQL要不断地回表查询判断订单状态这个过程的随机IO开销非常大。优化器评估下来觉得全表扫描反而更快于是直接放弃了索引选择了全表扫描。一千二百万行的全表扫描再加上排序操作CPU直接被打满就不奇怪了。很多人遇到这个问题第一反应是把SQL改成子查询的写法先在子查询里用索引查出order_id再关联主表拿数据。但我试了之后发现这种写法在有筛选条件的场景下性能提升非常有限。因为子查询里还是要先扫十万条索引记录开销依然很大。真正的优化思路必须跳出“OFFSET跳过数据”的逻辑。既然我们是按create_time倒序分页那么每一页的最后一条记录都有一个最小的create_time值我们完全可以利用这个值作为下一页的查询条件避免跳过前面的十万条数据。这种优化方式业内叫“书签分页”或者“键集分页”。我先给表建了一个符合筛选条件的联合覆盖索引把order_status、create_time、order_id三个字段组合起来sqlALTER TABLE orders ADD INDEX idx_status_time_id (order_status, create_time DESC, order_id);这个索引的好处是所有order_status为“已支付”的记录在索引里已经按create_time倒序排好了MySQL可以直接从索引的头部开始取数据完全不需要排序也不需要回表。然后我把原来的分页SQL改写成基于上一页最后一条记录的查询方式sqlSELECT order_id, order_sn, user_id, order_amount, create_time, order_statusFROM ordersWHERE order_status 已支付AND create_time 上一页最后一条记录的create_timeORDER BY create_time DESCLIMIT 20;改完之后我再用EXPLAIN看执行计划type变成了range扫描行数直接降到了二十行Extra里显示Using index完全走覆盖索引不需要回表。原来三十多秒的查询现在两百毫秒以内就能返回结果。但这里还有一个细节要处理如果同一秒内有多个订单create_time的值完全相同直接用create_time作为书签会出现重复或者漏数据的问题。这时候可以把order_id也加进去作为第二排序条件保证排序的绝对唯一性。最终优化后的SQL是这样的sqlSELECT order_id, order_sn, user_id, order_amount, create_time, order_statusFROM ordersWHERE order_status 已支付AND (create_time 上一页最后一条记录的create_timeOR (create_time 上一页最后一条记录的create_timeAND order_id 上一页最后一条记录的order_id))ORDER BY create_time DESC, order_id DESCLIMIT 20;这个优化方案上线之后大促期间的订单后台分页接口再也没有出现过超时数据库CPU使用率直接降到了百分之二十以下。后来我把这个方案推广到了所有后台分页场景整个系统的慢查询数量直接减少了百分之六十。很多人觉得大分页优化只能靠分库分表但实际上在千万级数据量下只要跳出OFFSET的思维定式用书签分页的方式配合精心设计的联合覆盖索引完全可以把分页性能提升两个数量级根本不需要急着上复杂的分库分表方案。二、多表关联优化从二十秒到一秒的JOIN逻辑重构第二个案例来自一个用户画像分析系统业务方反馈用户消费统计报表加载特别慢每次导出数据都要等二十多秒运营同事经常要加班等报表生成。我拿到对应的SQL一看里面有五张表做关联逻辑非常复杂开发同学为了图省事直接把所有表用LEFT JOIN连在一起没有做任何关联顺序的控制。原始的SQL大概是这样的sqlSELECT u.user_id, u.nickname,COUNT(o.order_id) AS total_order,SUM(o.order_amount) AS total_consume,COUNT(c.coupon_id) AS used_coupon,SUM(p.points) AS total_pointsFROM users uLEFT JOIN orders o ON u.user_id o.user_idLEFT JOIN coupons c ON u.user_id c.user_id AND c.status 已使用LEFT JOIN points p ON u.user_id p.user_id AND p.type 消费获得WHERE u.register_time 2024-01-01GROUP BY u.user_id;当时users表有两百万数据orders表有一千五百万数据coupons表有八百万数据points表有一千万数据。我用EXPLAIN看执行计划发现MySQL的优化器选择了users表作为驱动表然后依次关联orders、coupons、points三张表。但问题出在orders表上的user_id索引区分度非常低很多用户有几十上百条订单记录关联之后中间结果集膨胀到了几千万行后续的GROUP BY操作不得不创建临时表排序开销大得惊人。很多开发同学不知道MySQL的嵌套循环连接算法本质上是“驱动表拿一行数据去被驱动表里找匹配的记录”。驱动表的选择直接决定了关联的总开销驱动表的行数越少被驱动表上的关联字段有索引整个关联的性能就越高。如果驱动表选得不对中间结果集疯狂膨胀性能直接就崩了。我仔细分析了业务逻辑发现这个报表的核心需求是统计2024年之后注册的新用户的消费数据这些新用户总共只有二十万只占users表总数据量的十分之一。原来的SQL写法是先把所有用户关联所有订单、优惠券、积分记录最后再做GROUP BY统计相当于做了大量无用的计算。正确的优化思路是把大表的聚合操作下推到各自的子查询里先在子查询里按user_id完成聚合把每个用户的统计结果先算出来得到一个只有二十万行的小结果集再用这个小结果集去做关联。这样关联的时候每张表的中间结果集都只有二十万行完全不会出现膨胀的问题。我把SQL彻底重构把每个大表的统计逻辑都拆成独立的子查询sqlSELECT u.user_id, u.nickname,IFNULL(o.total_order, 0) AS total_order,IFNULL(o.total_consume, 0) AS total_consume,IFNULL(c.used_coupon, 0) AS used_coupon,IFNULL(p.total_points, 0) AS total_pointsFROM users uLEFT JOIN (SELECT user_id, COUNT(order_id) AS total_order, SUM(order_amount) AS total_consumeFROM ordersWHERE create_time 2024-01-01GROUP BY user_id) o ON u.user_id o.user_idLEFT JOIN (SELECT user_id, COUNT(coupon_id) AS used_couponFROM couponsWHERE status 已使用 AND use_time 2024-01-01GROUP BY user_id) c ON u.user_id c.user_idLEFT JOIN (SELECT user_id, SUM(points) AS total_pointsFROM pointsWHERE type 消费获得 AND create_time 2024-01-01GROUP BY user_id) p ON u.user_id p.user_idWHERE u.register_time 2024-01-01;同时我给每个子查询的表都建了对应的联合覆盖索引orders表建(user_id, create_time, order_amount)coupons表建(user_id, status, use_time)points表建(user_id, type, create_time, points)。所有子查询的聚合操作都可以直接在覆盖索引里完成不需要回表也不需要额外排序。优化之后报表的执行时间从二十多秒直接降到了一秒以内运营同事导出报表再也不用加班等待。这个案例让我深刻意识到多表关联优化的核心从来不是加几个索引那么简单而是要控制中间结果集的大小把大的聚合操作下推让每一步关联的结果集都尽可能小。很多复杂的慢查询只要把逻辑拆解开把大操作拆成小操作性能就能得到质的提升。三、IN子查询优化从十五秒到三百毫秒的逻辑改写第三个案例来自一个社交平台的消息通知系统用户反馈消息列表加载很慢经常要等十几秒才能看到新消息。我查到对应的SQL发现里面有一个IN子查询里面嵌套了另一个子查询开发同学本来是想简化代码结果写出了一个性能灾难。原始的SQL是这样的sqlSELECT msg_id, sender_id, content, create_timeFROM messagesWHERE receiver_id 12345AND is_deleted 0AND sender_id IN (SELECT user_id FROM friendsWHERE owner_id 12345 AND status 已通过)ORDER BY create_time DESCLIMIT 50;当时messages表有八百万条数据friends表有五百万条数据。我用EXPLAIN看执行计划发现MySQL把这个IN子查询做了半连接优化但优化的方式非常糟糕它先把friends表里符合条件的所有用户ID查出来生成一个临时表然后遍历messages表的所有记录去和这个临时表做匹配。相当于messages表做了全表扫描扫描行数达到了八百万行查询时间超过了十五秒。很多开发同学都遇到过IN子查询慢的问题网上很多教程会告诉你把IN改成JOIN就好了但不是所有场景下直接改JOIN都能解决问题。我一开始直接把IN改成了INNER JOIN结果发现性能并没有明显提升因为关联之后还是要扫描大量数据。我仔细分析了业务逻辑发现这个查询的本质是查询当前用户收到的、来自已通过好友的最新五十条未删除消息。原来的SQL写法是先筛选好友再去消息表里匹配相当于把筛选条件的顺序搞反了。消息表里receiver_id等于当前用户的记录总共只有三万条远小于好友表的总数据量。如果我们先从消息表里把这三万条记录查出来再去和好友表做关联扫描的行数会少得多。我重新调整了查询逻辑先在消息表里用覆盖索引快速拿到当前用户的最新五十条消息再去好友表里判断发送者是不是好友这样完全不需要扫描整个消息表。最终改写后的SQL是这样的sqlSELECT m.msg_id, m.sender_id, m.content, m.create_timeFROM (SELECT msg_id, sender_id, content, create_timeFROM messagesWHERE receiver_id 12345 AND is_deleted 0ORDER BY create_time DESCLIMIT 100) mWHERE EXISTS (SELECT 1 FROM friendsWHERE owner_id 12345AND user_id m.sender_idAND status 已通过)ORDER BY m.create_time DESCLIMIT 50;同时我给messages表建了联合覆盖索引(receiver_id, is_deleted, create_time DESC, msg_id, sender_id, content)给friends表建了联合索引(owner_id, status, user_id)。改写之后子查询m只需要从消息表里取最新的一百条记录然后用这一百条记录去好友表里做存在性判断整个查询的扫描行数不到两百行执行时间直接降到了三百毫秒以内。这里我特意把子查询里的LIMIT设成了100而不是50是因为这一百条消息里可能有一部分发送者不是好友多取一些可以保证最终筛选出来的有效好友消息能拿到五十条。这个小细节既保证了业务逻辑正确又把性能开销控制到了最低。这个案例给我的启发是很多子查询慢的根本原因不是IN关键字本身有问题而是优化器选择了错误的驱动顺序把大表当成了驱动表。这时候不要迷信优化器的自动选择手动调整查询逻辑把小结果集作为驱动表往往能得到远超预期的性能提升。这三个案例都是我在不同项目里真实遇到的线上故障它们有一个共同的特点没有用到任何高深的技术没有上昂贵的硬件升级也没有做复杂的分库分表只是通过读懂执行计划理清数据之间的关联逻辑调整SQL的执行顺序加上几个精心设计的索引就把查询性能提升了几十甚至上百倍。很多开发同学遇到慢查询第一反应是加缓存、上中间件、做分库分表却忽略了最基础的SQL本身的优化。实际上大部分线上的性能问题根源都出在SQL写得不合理上。掌握查询优化的思路不是为了应付面试而是真的能在关键时刻帮你保住系统的稳定性帮你在凌晨三点不用熬夜加班处理故障。我希望这些真实的复盘案例能让你下次遇到慢查询的时候不再手足无措能冷静地一步步定位问题最终找到最优的解决方案。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围发现夸克网盘链接×是否打开打开No关闭