查询优化案例复盘从线上故障到长效保障做数据库开发和运维的人几乎都遇到过这样的惊魂时刻大促前压测一切正常零点刚过流量峰值一到核心订单查询接口突然大面积超时数据库CPU直接冲到100%几十分钟都降不下来最后只能靠限流降级勉强保住核心链路事后复盘翻遍慢查询日志才发现是一条之前被忽略的低频查询突然拖垮了整个库。很多团队做SQL优化都是“兵来将挡水来土掩”出一个慢SQL就临时改一个从来没有把线上真实故障沉淀成可复用的优化方法论最后同样的坑反复踩同样的故障反复发生。其实查询优化从来不是靠零散的技巧堆出来的它是一套从问题定位、根因分析到落地验证、长效保障的完整工程体系。今天我就把过去7年在电商平台经历的6个真实线上查询优化案例完整复盘出来每一个案例都包含故障现场、排查过程、优化方案和事后长效机制帮你把别人踩过的坑变成自己的优化经验库以后遇到同类问题能快速定位解决再也不会在大促高峰期手忙脚乱。一、千万级订单表深分页查询超时故障这个案例发生在2023年618大促前的最后一轮压测当时运营后台的订单导出接口在翻到第100页之后直接超时单条SQL的执行耗时超过30秒直接把从库的磁盘IO打满影响了所有依赖从库的查询接口。故障发生的时候我们第一时间从慢查询日志里捞出来这条问题SQL它的逻辑非常简单按创建时间倒序分页查询订单列表用的是最常见的LIMIT 20000,20写法也就是跳过前20000条数据返回第20001到20020条订单。一开始我们以为是没有给create_time建索引但是用Explain一看这条SQL明明已经走了create_time的二级索引type字段是range看起来完全没问题但是实际扫描的行数超过了20020行性能完全达不到预期。我们顺着执行逻辑往下深挖才发现问题的根因MySQL执行LIMIT 20000,20的时候并不是直接跳到第20000行的位置取20条数据而是要先从头到尾扫描前20000条完全无用的数据直接把它们全部丢弃之后才会返回后面的20条结果。当分页深度到第1000页的时候就要扫描20万行无关数据哪怕走了索引大量的随机IO也会把磁盘性能拖垮。我们最终落地的优化方案是用“子查询主键定位法”改造SQL先在覆盖索引里快速定位到第20000条数据的主键ID再通过这个ID往后取20条订单数据全程不需要扫描前面的20000行冗余数据。优化前后的SQL对比如下sql-- 优化前的深分页写法SELECT * FROM order_infoWHERE create_time 2023-06-01ORDER BY create_time DESCLIMIT 20000,20;-- 优化后的深分页写法SELECT a.* FROM order_info aINNER JOIN (SELECT id FROM order_infoWHERE create_time 2023-06-01ORDER BY create_time DESCLIMIT 20000,1) b ON a.id b.idORDER BY a.create_time DESCLIMIT 20;优化之后这条SQL的执行耗时直接从30秒降到了15毫秒哪怕翻到第1000页性能也能保持稳定。事后我们做了长效保障给所有运营后台的分页接口统一增加最大页码限制不允许超过100页同时全量扫描所有深分页SQL全部改成主键定位的优化写法从根源上避免同类问题再次发生。二、隐式类型转换引发的全表扫描雪崩这个案例发生在2024年年初的一次日常版本上线上线之后10分钟数据库的CPU使用率突然从平时的20%直接冲到95%大量用户的支付查询接口超时线上告警直接炸了几百条。我们第一时间登上数据库服务器用show processlist看当前的活跃线程发现有几百条完全一样的查询卡在执行状态这条SQL的逻辑是根据支付流水号查询支付记录开发人员明明给pay_flow_no字段建了唯一索引但是实际执行的时候完全没有走索引。我们立刻用Explain分析这条SQL发现type字段是ALLkey字段为空预估扫描行数是800万完全是全表扫描的状态。排查了十几分钟才找到根因pay_flow_no字段在数据库里定义的是varchar字符串类型但是代码里传入的参数是Long长整型MySQL在执行的时候触发了隐式类型转换自动把每一行的pay_flow_no字段转成数字和传入的参数做比较导致索引完全失效所有请求都在做全表扫描瞬间把CPU打满。我们的应急方案非常简单立刻在代码里把传入的参数改成字符串类型重新发布之后所有请求立刻恢复走唯一索引单条SQL耗时从原来的2秒降到了1毫秒数据库CPU在1分钟之内就恢复到了正常水平。事后我们做了两层长效保障第一层是在公司的代码扫描工具里增加规则只要出现查询条件参数类型和数据库字段类型不匹配的情况直接拦截不让上线第二层是全量扫描所有历史SQL把所有存在隐式类型转换风险的语句全部整改彻底杜绝同类故障再次发生。三、多表关联驱动表选择错误引发的慢查询风暴这个案例发生在2022年的双11大促当天用户中心的订单关联查询接口突然大面积变慢原本几毫秒就能返回的接口耗时突然涨到了几百毫秒直接拖慢了整个用户中心的响应速度。我们拿到问题SQL之后发现这条SQL要关联三张表千万级的订单表order_info、10万级的用户表user_info、50万级的商品表goods_info逻辑是查询某个用户最近的10条订单同时关联查询对应的用户昵称和商品名称。一开始我们以为是关联字段没有建索引但是检查之后发现所有关联字段都已经建了索引用Explain看执行计划才发现MySQL的优化器错误地选择了千万级的订单表作为驱动表先扫描了整个订单表的所有数据再关联用户表和商品表总扫描行数超过了1000万性能自然极差。根因是优化器基于统计信息做判断的时候错误估算了订单表的符合条件的行数最终做出了完全错误的驱动表选择。我们的优化方案是用STRAIGHT_JOIN语法强制指定小表作为驱动表让数据量最小的用户表先执行拿到用户的信息之后再去关联订单表和商品表这样每次关联都能通过索引快速定位数据总扫描行数直接降到了几十行。优化之后的SQL耗时从原来的800毫秒降到了3毫秒接口的吞吐量直接提升了200多倍。事后我们做了长效保障所有超过两张表关联的核心SQL都不允许让优化器自由选择驱动表必须手动指定驱动顺序避免优化器因为统计信息偏差做出错误判断同时每季度定期更新所有核心表的统计信息保证优化器拿到的数据分布是准确的。四、低区分度字段索引失效引发的批量慢查询这个案例发生在2023年的一次日常运营活动运营人员临时加了一个查询条件要筛选所有“待发货”状态的订单结果这条SQL一上线就直接把从库的磁盘IO打满大量订单查询接口超时。我们分析这条SQL的执行计划发现order_status字段只有3个枚举值区分度不到万分之一开发人员给这个字段单独建了一个普通二级索引但是优化器判断走索引的开销比全表扫描还大直接放弃了索引选择全表扫描每次查询都要扫描整个订单表的1000万行数据。当时同时有几百个这样的请求在执行磁盘IO直接被打满。我们的优化方案不是删掉这个索引而是把user_id字段和order_status字段组合起来做成(user_id,order_status)的联合索引这样索引的整体区分度变得非常高优化器就会愿意选择这个索引查询某个用户的待发货订单的时候直接通过索引就能快速定位数据完全不需要全表扫描。优化之后这条SQL的耗时从原来的5秒降到了2毫秒磁盘IO使用率直接从100%降到了10%以下。事后我们做了长效保障制定索引设计规范明确禁止给区分度低于1%的字段单独建二级索引所有低区分度字段必须和高区分度字段组合成联合索引使用同时全量扫描所有历史索引把所有不符合规范的低区分度单列索引全部整改。五、大表加索引引发的锁表故障这个案例发生在2022年的一次版本迭代开发人员为了优化一个慢SQL直接在千万级的订单表上执行ALTER TABLE语句加索引结果执行命令之后整个订单表直接被锁住所有写入请求全部卡住订单提交接口大面积超时持续了整整40分钟造成了不小的资损。故障发生的时候我们才反应过来开发人员完全忘记了MySQL的DDL操作在旧版本里会锁全表加索引的过程中整个表的所有读写请求都会被阻塞千万级的大表加索引DDL执行时间要几十分钟这段时间整个业务完全不可用。我们的应急方案是立刻终止正在执行的ALTER TABLE命令然后改用pt-online-schema-change工具做无锁的索引添加这个工具会创建一张和原表结构一样的新表在新表上建好索引然后把原表的数据分批拷贝到新表里最后通过原子性的rename操作替换表整个过程几乎不会锁表对线上业务的影响几乎可以忽略不计。最终我们用这个工具花了15分钟就完成了索引的添加全程没有阻塞任何线上写入请求。事后我们做了非常严格的DDL规范所有超过100万行的大表绝对不允许直接在线执行ALTER TABLE做DDL操作必须使用pt-online-schema-change或者Online DDL的方式执行而且所有大表DDL必须安排在凌晨业务低峰期执行提前提交变更申请经过DBA审核通过之后才能上线从根源上杜绝大表锁表故障再次发生。六、分组查询临时表溢出引发的数据库OOM这个案例发生在2024年的一次月度报表生成任务运营人员跑了一个按天统计全平台订单金额的SQL结果这条SQL一执行数据库的内存使用率直接冲到100%没过多久数据库进程就被操作系统OOM Killer杀掉整个数据库直接宕机。我们事后分析这条SQL的执行计划发现它没有给create_time字段建合适的索引执行GROUP BY create_time的时候MySQL无法利用索引的有序性完成分组只能在内存里创建临时表来存储分组的中间结果但是内存临时表的大小是有限制的当数据量超过配置的阈值之后临时表就会转成磁盘临时表大量的磁盘IO和内存占用最终耗尽了数据库的所有内存引发OOM宕机。我们的优化方案非常简单给create_time字段创建普通二级索引这样索引本身就是按时间有序排列的MySQL遍历索引的时候相同日期的数据已经连续排列在一起完全不需要创建临时表直接顺序遍历就能完成分组操作内存占用几乎可以忽略不计。优化之后这条报表SQL的执行耗时从原来的2分钟降到了2秒再也不会出现内存溢出的问题。事后我们做了长效保障把所有报表类的离线查询全部迁移到专门的分析型从库不允许在主库上跑任何大流量的报表查询同时配置数据库的内存使用告警当内存使用率超过80%的时候立刻发出告警DBA可以提前介入处理避免出现OOM宕机的严重故障。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围
查询优化案例复盘从线上故障到长效保障做数据库开发和运维的人几乎都遇到过这样的惊魂时刻大促前压测一切正常零点刚过流量峰值一到核心订单查询接口突然大面积超时数据库CPU直接冲到100%几十分钟都降不下来最后只能靠限流降级勉强保住核心链路事后复盘翻遍慢查询日志才发现是一条之前被忽略的低频查询突然拖垮了整个库。很多团队做SQL优化都是“兵来将挡水来土掩”出一个慢SQL就临时改一个从来没有把线上真实故障沉淀成可复用的优化方法论最后同样的坑反复踩同样的故障反复发生。其实查询优化从来不是靠零散的技巧堆出来的它是一套从问题定位、根因分析到落地验证、长效保障的完整工程体系。今天我就把过去7年在电商平台经历的6个真实线上查询优化案例完整复盘出来每一个案例都包含故障现场、排查过程、优化方案和事后长效机制帮你把别人踩过的坑变成自己的优化经验库以后遇到同类问题能快速定位解决再也不会在大促高峰期手忙脚乱。一、千万级订单表深分页查询超时故障这个案例发生在2023年618大促前的最后一轮压测当时运营后台的订单导出接口在翻到第100页之后直接超时单条SQL的执行耗时超过30秒直接把从库的磁盘IO打满影响了所有依赖从库的查询接口。故障发生的时候我们第一时间从慢查询日志里捞出来这条问题SQL它的逻辑非常简单按创建时间倒序分页查询订单列表用的是最常见的LIMIT 20000,20写法也就是跳过前20000条数据返回第20001到20020条订单。一开始我们以为是没有给create_time建索引但是用Explain一看这条SQL明明已经走了create_time的二级索引type字段是range看起来完全没问题但是实际扫描的行数超过了20020行性能完全达不到预期。我们顺着执行逻辑往下深挖才发现问题的根因MySQL执行LIMIT 20000,20的时候并不是直接跳到第20000行的位置取20条数据而是要先从头到尾扫描前20000条完全无用的数据直接把它们全部丢弃之后才会返回后面的20条结果。当分页深度到第1000页的时候就要扫描20万行无关数据哪怕走了索引大量的随机IO也会把磁盘性能拖垮。我们最终落地的优化方案是用“子查询主键定位法”改造SQL先在覆盖索引里快速定位到第20000条数据的主键ID再通过这个ID往后取20条订单数据全程不需要扫描前面的20000行冗余数据。优化前后的SQL对比如下sql-- 优化前的深分页写法SELECT * FROM order_infoWHERE create_time 2023-06-01ORDER BY create_time DESCLIMIT 20000,20;-- 优化后的深分页写法SELECT a.* FROM order_info aINNER JOIN (SELECT id FROM order_infoWHERE create_time 2023-06-01ORDER BY create_time DESCLIMIT 20000,1) b ON a.id b.idORDER BY a.create_time DESCLIMIT 20;优化之后这条SQL的执行耗时直接从30秒降到了15毫秒哪怕翻到第1000页性能也能保持稳定。事后我们做了长效保障给所有运营后台的分页接口统一增加最大页码限制不允许超过100页同时全量扫描所有深分页SQL全部改成主键定位的优化写法从根源上避免同类问题再次发生。二、隐式类型转换引发的全表扫描雪崩这个案例发生在2024年年初的一次日常版本上线上线之后10分钟数据库的CPU使用率突然从平时的20%直接冲到95%大量用户的支付查询接口超时线上告警直接炸了几百条。我们第一时间登上数据库服务器用show processlist看当前的活跃线程发现有几百条完全一样的查询卡在执行状态这条SQL的逻辑是根据支付流水号查询支付记录开发人员明明给pay_flow_no字段建了唯一索引但是实际执行的时候完全没有走索引。我们立刻用Explain分析这条SQL发现type字段是ALLkey字段为空预估扫描行数是800万完全是全表扫描的状态。排查了十几分钟才找到根因pay_flow_no字段在数据库里定义的是varchar字符串类型但是代码里传入的参数是Long长整型MySQL在执行的时候触发了隐式类型转换自动把每一行的pay_flow_no字段转成数字和传入的参数做比较导致索引完全失效所有请求都在做全表扫描瞬间把CPU打满。我们的应急方案非常简单立刻在代码里把传入的参数改成字符串类型重新发布之后所有请求立刻恢复走唯一索引单条SQL耗时从原来的2秒降到了1毫秒数据库CPU在1分钟之内就恢复到了正常水平。事后我们做了两层长效保障第一层是在公司的代码扫描工具里增加规则只要出现查询条件参数类型和数据库字段类型不匹配的情况直接拦截不让上线第二层是全量扫描所有历史SQL把所有存在隐式类型转换风险的语句全部整改彻底杜绝同类故障再次发生。三、多表关联驱动表选择错误引发的慢查询风暴这个案例发生在2022年的双11大促当天用户中心的订单关联查询接口突然大面积变慢原本几毫秒就能返回的接口耗时突然涨到了几百毫秒直接拖慢了整个用户中心的响应速度。我们拿到问题SQL之后发现这条SQL要关联三张表千万级的订单表order_info、10万级的用户表user_info、50万级的商品表goods_info逻辑是查询某个用户最近的10条订单同时关联查询对应的用户昵称和商品名称。一开始我们以为是关联字段没有建索引但是检查之后发现所有关联字段都已经建了索引用Explain看执行计划才发现MySQL的优化器错误地选择了千万级的订单表作为驱动表先扫描了整个订单表的所有数据再关联用户表和商品表总扫描行数超过了1000万性能自然极差。根因是优化器基于统计信息做判断的时候错误估算了订单表的符合条件的行数最终做出了完全错误的驱动表选择。我们的优化方案是用STRAIGHT_JOIN语法强制指定小表作为驱动表让数据量最小的用户表先执行拿到用户的信息之后再去关联订单表和商品表这样每次关联都能通过索引快速定位数据总扫描行数直接降到了几十行。优化之后的SQL耗时从原来的800毫秒降到了3毫秒接口的吞吐量直接提升了200多倍。事后我们做了长效保障所有超过两张表关联的核心SQL都不允许让优化器自由选择驱动表必须手动指定驱动顺序避免优化器因为统计信息偏差做出错误判断同时每季度定期更新所有核心表的统计信息保证优化器拿到的数据分布是准确的。四、低区分度字段索引失效引发的批量慢查询这个案例发生在2023年的一次日常运营活动运营人员临时加了一个查询条件要筛选所有“待发货”状态的订单结果这条SQL一上线就直接把从库的磁盘IO打满大量订单查询接口超时。我们分析这条SQL的执行计划发现order_status字段只有3个枚举值区分度不到万分之一开发人员给这个字段单独建了一个普通二级索引但是优化器判断走索引的开销比全表扫描还大直接放弃了索引选择全表扫描每次查询都要扫描整个订单表的1000万行数据。当时同时有几百个这样的请求在执行磁盘IO直接被打满。我们的优化方案不是删掉这个索引而是把user_id字段和order_status字段组合起来做成(user_id,order_status)的联合索引这样索引的整体区分度变得非常高优化器就会愿意选择这个索引查询某个用户的待发货订单的时候直接通过索引就能快速定位数据完全不需要全表扫描。优化之后这条SQL的耗时从原来的5秒降到了2毫秒磁盘IO使用率直接从100%降到了10%以下。事后我们做了长效保障制定索引设计规范明确禁止给区分度低于1%的字段单独建二级索引所有低区分度字段必须和高区分度字段组合成联合索引使用同时全量扫描所有历史索引把所有不符合规范的低区分度单列索引全部整改。五、大表加索引引发的锁表故障这个案例发生在2022年的一次版本迭代开发人员为了优化一个慢SQL直接在千万级的订单表上执行ALTER TABLE语句加索引结果执行命令之后整个订单表直接被锁住所有写入请求全部卡住订单提交接口大面积超时持续了整整40分钟造成了不小的资损。故障发生的时候我们才反应过来开发人员完全忘记了MySQL的DDL操作在旧版本里会锁全表加索引的过程中整个表的所有读写请求都会被阻塞千万级的大表加索引DDL执行时间要几十分钟这段时间整个业务完全不可用。我们的应急方案是立刻终止正在执行的ALTER TABLE命令然后改用pt-online-schema-change工具做无锁的索引添加这个工具会创建一张和原表结构一样的新表在新表上建好索引然后把原表的数据分批拷贝到新表里最后通过原子性的rename操作替换表整个过程几乎不会锁表对线上业务的影响几乎可以忽略不计。最终我们用这个工具花了15分钟就完成了索引的添加全程没有阻塞任何线上写入请求。事后我们做了非常严格的DDL规范所有超过100万行的大表绝对不允许直接在线执行ALTER TABLE做DDL操作必须使用pt-online-schema-change或者Online DDL的方式执行而且所有大表DDL必须安排在凌晨业务低峰期执行提前提交变更申请经过DBA审核通过之后才能上线从根源上杜绝大表锁表故障再次发生。六、分组查询临时表溢出引发的数据库OOM这个案例发生在2024年的一次月度报表生成任务运营人员跑了一个按天统计全平台订单金额的SQL结果这条SQL一执行数据库的内存使用率直接冲到100%没过多久数据库进程就被操作系统OOM Killer杀掉整个数据库直接宕机。我们事后分析这条SQL的执行计划发现它没有给create_time字段建合适的索引执行GROUP BY create_time的时候MySQL无法利用索引的有序性完成分组只能在内存里创建临时表来存储分组的中间结果但是内存临时表的大小是有限制的当数据量超过配置的阈值之后临时表就会转成磁盘临时表大量的磁盘IO和内存占用最终耗尽了数据库的所有内存引发OOM宕机。我们的优化方案非常简单给create_time字段创建普通二级索引这样索引本身就是按时间有序排列的MySQL遍历索引的时候相同日期的数据已经连续排列在一起完全不需要创建临时表直接顺序遍历就能完成分组操作内存占用几乎可以忽略不计。优化之后这条报表SQL的执行耗时从原来的2分钟降到了2秒再也不会出现内存溢出的问题。事后我们做了长效保障把所有报表类的离线查询全部迁移到专门的分析型从库不允许在主库上跑任何大流量的报表查询同时配置数据库的内存使用告警当内存使用率超过80%的时候立刻发出告警DBA可以提前介入处理避免出现OOM宕机的严重故障。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围
