线上慢SQL排查完整实战案例

线上慢SQL排查完整实战案例面试完整版含原理、踩坑、追问应答直接背诵一、业务背景负责电商订单查询模块线上高峰期大促时段出现问题用户「我的订单」查询接口响应超时RT 从正常 20ms 飙升至 1~3s大量请求报错监控面板观测到MySQL 数据库 CPU 使用率持续 90%负载居高不下应用服务本身CPU、内存正常排除Java代码问题锁定数据库瓶颈。涉及核心表t_order订单表数据量约800w条。问题SQL用户查询个人历史订单SELECTid,order_no,user_id,order_status,create_time,pay_amountFROMt_orderWHEREuser_id?ANDcreate_time?ANDorder_statusIN(1,2,3,4)ORDERBYcreate_timeDESCLIMIT0,10;二、完整排查步骤按线上标准流程步骤1全局定位瓶颈查看应用监控接口耗时全部卡在DAO层确定是数据库查询慢导致登录MySQL查看慢查询日志该SQL短时间内累计上万条慢记录是核心元凶执行EXPLAIN分析执行计划得到关键信息typeALL全表扫描rows扫描行数120wExtraUsing where; Using filesort索引列仅使用了user_id单字段索引步骤2分析现有索引与问题根因原表仅建有单字段索引idx_user_id(user_id)。结合SQL和索引规则拆解三大问题大量回表操作通过user_id索引找到当前用户的所有订单单用户几百条但查询字段不全在索引中必须回主键索引读取完整数据随机IO开销极大。出现Using filesortcreate_time是范围查询根据联合索引规则范围字段后无法利用索引排序MySQL会在MySQL服务端内存sort_buffer中做内存排序数据量多时还可能落地磁盘进一步拖慢性能。注意排序、过滤均为MySQL内存操作和JVM无关。范围查询打断索引查找create_time ?属于范围条件按照最左前缀原则索引的查找匹配在该字段处中断后续order_status无法参与索引查找只能取出数据后在MySQL内存中做条件过滤。补充单用户仅几百条数据但大促期间并发请求极高回表排序内存过滤叠加最终打满数据库CPU。步骤3制定优化方案并落地方案新建联合索引遵循「等值在前、范围次之、过滤字段在后」原则创建联合索引CREATEINDEXidx_uid_ct_statusONt_order(user_id,create_time,order_status);索引原理拆解面试核心加分点最左前缀匹配user_id ?等值查询精准匹配索引首列create_time ?范围查询索引查找逻辑到此中断order_status不再参与索引查找索引本身按create_time有序ORDER BY create_time DESC无需额外排序彻底消除filesort。索引下推ICP生效order_status包含在联合索引内MySQL开启索引下推优化在索引节点层直接过滤不符合order_status条件的数据不用回表大幅减少IO。覆盖索引生效SQL查询的所有字段id,order_no,user_id,order_status,create_time,pay_amount均包含在索引中直接从索引返回数据完全消除回表操作。步骤4验证优化效果再次执行EXPLAINtyperef索引精准匹配rows仅扫描十几行ExtraUsing where; Using index无filesort、无全表扫描覆盖索引索引下推生效线上观测SQL执行耗时从1.5~3s 降至 10~15msMySQL CPU 从90%回落至15%左右接口超时问题彻底解决业务恢复正常。三、面试口述完整版分精简版/详细版按需选用版本1一面精简版1分钟之前在电商项目中大促期间订单查询接口大面积超时数据库CPU居高不下。我先通过监控和慢查询日志定位到问题SQL执行explain发现是全表扫描文件排序。原表只有user_id单字段索引查询存在回表、内存排序、内存过滤等问题。我根据业务条件创建(user_id, create_time, order_status)联合索引利用最左前缀、覆盖索引和索引下推优化。优化后SQL耗时大幅下降数据库负载恢复正常接口超时问题解决。版本2二面/技术面详细版2~3分钟推荐我分享一个线上订单模块的慢SQL排查案例。大促高峰期用户查询个人订单的接口频繁超时监控显示应用本身正常但MySQL CPU占用超过90%。我先查看慢查询日志锁定了核心查询SQL这条语句根据用户ID、订单创建时间、订单状态做条件查询同时按创建时间倒序分页。执行explain分析后发现语句走了全表扫描还出现了Using filesort。根因是表上只有user_id单字段索引一方面查询需要回表取数据另一方面create_time是范围查询无法利用索引排序只能在MySQL内存中做排序和条件过滤高并发下直接压垮数据库。针对这个问题我设计并创建了(user_id, create_time, order_status)联合索引。这里有几个关键点第一user_id等值查询放最左侧create_time范围查询居中符合索引设计规范第二索引本身有序规避了文件排序第三order_status在索引列中触发索引下推在索引层完成条件过滤同时该索引也满足覆盖索引彻底消除回表。这里补充一点create_time作为范围字段会打断索引的查找匹配所以order_status无法参与索引检索但依靠索引下推依然能做过滤不影响优化效果。优化完成后重新校验执行计划全表扫描、文件排序都消失了单条SQL耗时从秒级降到十几毫秒数据库CPU恢复正常线上问题彻底解决。四、高频追问标准应答面试官必问问单个用户只有几百条订单为什么单字段索引还会慢答数据量本身不大但高并发场景下大量请求同时触发回表、内存排序、内存过滤三类耗时操作叠加后数据库压力陡增。慢SQL不一定由大结果集导致不合理的索引结构才是核心。问create_time是范围查询不会让后面的order_status索引失效吗答会打断索引查找逻辑MySQL无法通过order_status去检索数据但因为该字段在联合索引内MySQL触发索引下推(ICP)可以在索引节点直接过滤数据不需要回表依然能提升性能。简单说查找失效过滤依然生效。问filesort是在JVM内存排序吗答不是。filesort、条件过滤都是MySQL服务端内存sort_buffer完成和Java应用的JVM内存没有关系。问为什么不把order_status放到create_time前面答遵循索引设计原则等值条件优先于范围条件。create_time是范围查询放在范围字段前会导致create_time无法使用索引排序反而得不偿失。