基于真实慢 SQL 的 MySQL 优化实践从索引排查到增量架构本文基于某电商商品中心一次真实慢 SQL 治理经验整理覆盖「高频短查询」「稀疏条件扫描」「UNION ALL 重复访问」「选品池全表聚合」等高发问题。文中表名、字段名、路径与业务标识均已脱敏数值为治理过程中的量级参考上线前请以真实环境EXPLAIN ANALYZE为准。一、背景与问题画像在一次 RDS 慢 SQL 专项排查中我们发现慢查询并不总是「缺一个索引」那么简单。有的表明明已经有索引却仍累计扫描数亿行有的 SQL 单次扫描行数不高却因为一天执行近十万次把数据库打满有的统计任务看似“简单 COUNT”却周期性扫完整张历史大表。本次治理涉及两类业务业务域典型问题量级特征脱敏后商品/订单中心稀疏条件清理、补货关联查询、在途数量统计、SKU 映射查询单次扫描可达千万级部分 SQL 日调用约 9 万 次选品中心任务入池统计、已上品关联统计、池内分页与全量反选相关 SQL 累计扫描约亿级单次统计扫描约数十万行如果只盯着「加索引」很容易踩三个坑重复建索引线上已有同名字段索引DDL 只会增加写入成本。治标不治本索引无法消灭「周期性全历史聚合」。口径被改坏为了性能把UNION ALL改成互斥CASE统计结果悄悄变了。因此我们把优化原则先定死再谈具体改法。二、优化原则先于技巧不因慢 SQL 记录直接重复加索引先SHOW INDEX/SHOW CREATE TABLE再用真实参数跑EXPLAIN ANALYZE。优先减少重复扫描、宽字段回表、无边界批处理很多“慢”不是算不出来而是算得太频繁、取了太多无用字段。游标必须按“已扫描位置”推进不能按“过滤结果”推进否则稀疏命中时任务会卡死在原地。条件聚合改写必须保持原叠加语义UNION ALL允许同一行命中多个分支互斥CASE会改口径。一对多关联先预聚合再回连主表否则金额/数量会被包裹行放大。可增量维护的指标不要长期依赖全表 GROUP BY短期靠索引止血中长期靠事件驱动计数 低频对账。三、案例一稀疏条件清理 —— 别迷信「WHERE 非空 主键」3.1 现象某清理任务按主键分批删除「存在错误信息」的记录SQL 类似SELECTidFROMai_image_match_resultWHEREid?ANDerror_messageISNOTNULLORDERBYidASCLIMIT1000;慢日志显示单次扫描约2475 万行只返回 1000 行。原因是error_message非空记录非常稀疏优化器沿主键一路跳过大量正常行才能凑够一批。3.2 错误思路第一反应往往是给error_message建索引。但大字段/文本类索引成本高且清理是低频任务不值得给正常写入路径增加索引维护开销。3.3 正确思路主键范围扫描 应用层过滤 持久化游标数据库只做稳定、可预期的主键范围读取SELECTid,error_messageFROMai_image_match_resultWHEREid#{scanCursor}ORDERBYidASCLIMIT#{scanBatchSize};Java 侧关键规则scanCursor表示「已经扫描到的位置」不是「最后一条错误记录」。每批查完后先用本批最后一条记录的 id 更新游标。在内存中过滤errorMessage ! null或业务等价判断。只删除过滤后的 ID本批没有可删数据时仍然继续下一批。返回行数 scanBatchSize时结束本轮。游标必须持久化任务参数 / Redis / 状态表禁止每次从 0 重扫全表。参考伪代码longscanCursorloadCheckpoint();// 持久化检查点intscanBatchSize5000;longmaxScanRows500_000L;// 单次任务上限longscanned0L;while(scannedmaxScanRows){ListRowrowsmapper.selectScanBatch(scanCursor,scanBatchSize);if(rows.isEmpty()){break;}// 关键游标按“已扫描记录”推进scanCursorrows.get(rows.size()-1).getId();saveCheckpoint(scanCursor);ListLongdeleteIdsrows.stream().filter(r-r.getErrorMessage()!null).map(Row::getId).collect(Collectors.toList());// 删除再按 500~1000 分批避免超长 IN / 大事务for(ListLongpart:Lists.partition(deleteIds,800)){mapper.deleteBatchIds(part);}scannedrows.size();if(rows.size()scanBatchSize){break;}}3.4 收益与代价收益每批扫描量有明确上限执行时间可预期。不新增大字段索引不影响正常写入。方便叠加「保留期」等业务规则例如只删 N 天前的失败数据。代价失败记录极少时完整清理仍需读大量正常行。若不限制单次任务工作量、不持久化游标只是把一个超长 SQL 拆成大量小 SQL整体压力未必下降。四、案例二索引已存在仍很慢 —— 先证明索引有没有被用上4.1 现象补货明细表上按replenish_ref_id查询的 SQL 日执行约两千次累计扫描约14 亿行平均每次返回却很少。排查后确认该字段线上已有索引因此「再加一个同名单列索引」是错误动作。4.2 排查清单用线上真实参数执行EXPLAINANALYZESELECT/* 实际业务字段 */...FROMorder_item_overseasWHEREreplenish_ref_idIN(...);重点核对检查项为什么重要字段类型 vs Java 参数类型BIGINT对VARCHAR等隐式转换会导致索引失效字符集 / 排序规则关联或比较时发生转换同样可能走不了索引IN集合规模过大时优化器可能改走全表扫描是否含重复值 / 空值放大解析与探测成本统计信息是否过期ANALYZE TABLE后再看计划是否循环重复查同一批 ID调用层缓存 / 批量合并往往比改 SQL 更有效返回字段是否过宽先查主键再按主键取明细建议先确认事实SHOWINDEXFROMorder_item_overseas;ANALYZETABLEorder_item_overseas;4.3 处理策略若执行计划未使用索引优先修类型一致性、拆分超大IN、刷新统计信息。若执行计划已使用索引但回表仍贵收缩 SELECT 字段调用方只需少量 ID 时采用「先 ID、后明细」两段式。若业务侧在循环里重复打相同条件先消重复调用再谈 SQL。经验慢 SQL 面板上的「扫描行数巨大」既可能是缺索引也可能是「索引在但没用上」还可能是「用了但仍被超高频调用放大」。三者解法完全不同。五、案例三高频 UNION ALL —— 把四次主表访问收敛成条件聚合5.1 业务结构在途数量统计对同一组条件供应商 SKU 采购类型连续访问主表四次典型形态是四段UNION ALL指定状态上限下累计下单量指定状态集合下累计「下单 - 缺货 - 入库 - 问题」从包裹表累计在途包裹量累计某类单据的入库量作为扣减项。单次扫描行数未必夸张但日调用约9.6 万次问题变成主表被重复访问四次SQL 反复解析/优化调用方往往在 SKU 循环中逐条 RPC / 逐条查库。5.2 推荐改写主表条件聚合 包裹预聚合核心写法是多个独立的SUM(CASE ...)再相加而不是一个互斥CASESELECTCOALESCE(SUM(CASEWHENa.is_special#{normalFlag}ANDa.settle_flag#{normalSettle}ANDa.abnormal_qty#{normalAbnormal}ANDa.status#{statusUpper}THENa.order_qtyELSE0END),0)COALESCE(SUM(CASEWHENa.is_special#{normalFlag}ANDa.settle_flag#{normalSettle}ANDa.abnormal_qty#{normalAbnormal}ANDa.statusIN(/* statusList */)THENa.order_qty-COALESCE(a.shortage_qty,0)-COALESCE(a.store_qty,0)-COALESCE(a.problem_qty,0)ELSE0END),0)COALESCE(SUM(CASEWHENa.settle_flag#{packageSettle}THENCOALESCE(pkg.transit_qty,0)ELSE0END),0)-COALESCE(SUM(CASEWHENa.is_special#{specialFlag}ANDa.settle_flag#{specialSettle}THENCOALESCE(a.store_qty,0)ELSE0END),0)AStransit_qtyFROMfactory_order aLEFTJOIN(SELECTb.order_id,SUM(COALESCE(b.order_qty,0)-COALESCE(b.shortage_qty,0)-COALESCE(b.problem_qty,0))AStransit_qtyFROMfactory_order_package bINNERJOINfactory_order faONfa.idb.order_idWHEREfa.supplier_code#{supplierCode}ANDfa.sku#{sku}ANDfa.purchase_typeIN(/* types */)ANDfa.settle_flag#{packageSettle}GROUPBYb.order_id)pkgONpkg.order_ida.idWHEREa.supplier_code#{supplierCode}ANDa.sku#{sku}ANDa.purchase_typeIN(/* types */);5.3 为什么不能写成一个 CASE-- 错误示范互斥 CASE 会改变口径CASEWHEN条件1THEN...WHEN条件2THEN...END原UNION ALL允许同一条订单同时命中多个分支并累加。互斥CASE只命中第一个条件金额口径会被悄悄改掉。这一点在 code review 里必须当成正确性缺陷而不是风格问题。5.4 包裹表为什么要先聚合一个订单对应多个包裹时如果直接JOIN再SUM(主表字段)主表数量会被放大。必须包裹表按 order_id 预聚合 - 再 LEFT JOIN 回主表 - 对主表字段做条件求和并且尽量把主表筛选条件下推到包裹子查询避免对整个包裹表做无条件聚合。5.5 比 SQL 改写更大的收益批量接口把supplier sku - transitQty升级为supplier skuList - Mapsku, transitQtySQL 按supplier_code, sku分组一次算一批禁止在 SKU 循环中逐条查库。对「单次不慢、一天九万次」这类问题批量化往往比微优化单条 SQL 更有效。索引侧优先核对存在则不重复建factory_order(supplier_code, sku, purchase_type) factory_order_package(order_id)低基数字段状态、结算标记等是否进联合索引以执行计划选择性为准不要凭感觉堆索引。六、案例四宽表按业务键查询 —— 两段式「先 ID后明细」6.1 方案当业务键如来源编码已有索引时保留两段式即可不必再叠加重复索引-- 第一段只取主键尽量走业务键索引减少回表SELECTidFROMproduct_skuWHEREsource_codeIN(...);-- 第二段按主键批量取真正需要的字段SELECTid,source_code,sku_codeFROMproduct_skuWHEREidIN(...);6.2 落地注意点第一段只查 ID不要SELECT *。入参集合先去重建议每批 5001000最终以压测为准。Java 类型、字段类型、字符集必须一致避免隐式转换。第二段返回顺序不稳定需要映射后按输入顺序重排。一对多关系不要用MapsourceCode, Entity覆盖应按业务键分组。若两段式仍扫描数千万行优先怀疑线上索引与认知不一致参数隐式转换统计信息异常慢日志采集时间早于索引上线时间IN过大导致优化器放弃索引。七、案例五选品池统计 —— 全表 GROUP BY 的结构性陷阱前面几个案例偏「单条 SQL / 单次调用」优化选品模块则是另一类更危险的问题用全历史聚合支撑实时看板指标。7.1 三条核心统计链路1当日入池数量—— 相对健康有日期条件SELECTtask_type,task_id,COUNT(*)AScntFROMselection_pool_itemWHEREis_deleted0ANDtask_idISNOTNULLANDpool_date?GROUPBYtask_type,task_id;2历史入池总数—— 典型慢查询温床SELECTtask_type,task_id,COUNT(*)AScntFROMselection_pool_itemWHEREis_deleted0ANDtask_idISNOTNULLGROUPBYtask_type,task_id;这类 SQL每次都扫全部有效历史。索引只能减少回表无法消除全量扫描数据越大越慢而且会随业务增长线性恶化。3已上品数量—— 大表实时关联SELECTa.task_type,a.task_id,COUNT(*)ASpublished_cntFROMselection_pool_item aINNERJOINsource_product bONb.source_data_ida.offer_idANDb.status3WHEREa.status2ANDa.is_deleted0ANDa.task_idISNOTNULLGROUPBYa.task_type,a.task_id;风险点若一个source_data_id对应多条有效商品选品池记录会被重复计数若池内status2已能代表上品成功关联可能是冗余的两张增长型大表实时 JOIN GROUP BY成本会持续上升。7.2 为什么「仓库 DDL」不能代表线上初始建表索引往往服务于写入去重例如PRIMARYKEY(id),UNIQUEKEYuk_task_offer_date(task_type,task_id,offer_id,pool_date),KEYidx_batch_id(batch_id)但 Mapper 早已使用大量后续新增字段删除标记、池类型、销量、发布状态、自动上品规则等。必须以线上SHOW CREATE TABLE/SHOW INDEX为准禁止按仓库旧脚本直接下 DDL。八、选品治理短期止血 中长期增量8.1 短期索引与 SQL 形态修正在确认线上无等价索引后可考虑-- 今日统计把日期放最前尽量覆盖 where group byALTERTABLEselection_pool_itemADDINDEXidx_pool_date_del_task(pool_date,is_deleted,task_type,task_id);-- 历史总数仅过渡方案降低回表不能消灭全扫ALTERTABLEselection_pool_itemADDINDEXidx_del_task(is_deleted,task_type,task_id);-- 已上品统计两侧都要核对ALTERTABLEselection_pool_itemADDINDEXidx_status_del_task_offer(status,is_deleted,task_type,task_id,offer_id);ALTERTABLEsource_productADDINDEXidx_source_data_status(source_data_id,status);已上品计数建议先排查一对多SELECTsource_data_id,COUNT(*)FROMsource_productWHEREstatus3ANDsource_data_idISNOTNULLGROUPBYsource_data_idHAVINGCOUNT(*)1LIMIT100;若业务口径是「存在一条有效商品即算已发布」把JOIN改成EXISTS避免重复计数SELECTa.task_type,a.task_id,COUNT(*)ASpublished_cntFROMselection_pool_item aWHEREa.status2ANDa.is_deleted0ANDa.task_idISNOTNULLANDEXISTS(SELECT1FROMsource_product bWHEREb.source_data_ida.offer_idANDb.status3)GROUPBYa.task_type,a.task_id;如果业务确认「池状态已上品」与「源商品状态已生效」强一致也可以直接按池表状态统计——但这是业务决策不能仅为性能擅自删关联。8.2 列表与全量反选别再SELECT *选品池表通常包含标题、图片 URL、JSON 快照、销售趋势等宽字段。列表页显式字段只返回展示与操作所需列。全量反选 / 批量上品先只查 ID再分批处理SELECTp.idFROMselection_pool_item pWHERE/* 动态条件 */ORDERBYp.idLIMIT?;禁止一次把全部实体加载进 JVM。价格排序若依赖相关子查询反复算MIN(price)高频场景应冗余最低价字段或独立价格汇总表。8.3 终局方案事件驱动增量计数维护三类指标指标含义today_count任务当天新增的有效入池数total_count任务累计有效入池数published_count任务累计成功上品数入池成功以affectedRows1为准插入 selection_pool_item - affectedRows 1 ? - total_count 1 - pool_date 为业务当天则 today_count 1 - 同事务提交跨库则 Outbox/MQ 幂等命中唯一键的重复数据不得重复计数。上品成功仅首次状态迁移加一UPDATEselection_pool_itemSETstatus2,onboard_time?WHEREid?ANDstatus2;仅当affectedRows1时published_count 1抵御 MQ 重复消费、重试和并发回调。若存在「按 offer 批量联动其他池记录」的逻辑必须找出真正发生状态迁移的行再按task_type task_id分组更新不能只给当前任务 1。删除 / 回退逻辑删除、已上品回退要产生负增量忽略状态是否扣减必须与现网统计口径对齐后再改。幂等键建议eventId 或 poolItemId eventType statusVersion不要只靠带过期时间的 Redis 锁做幂等。8.4 全量 SQL 降级为对账增量上线后全量GROUP BY只保留为每日低峰对账按 ID / 日期分片扫描按任务汇总真实计数与任务表计数对比只修正有差异的任务记录修正前值、真实值、差异与时间保证可审计、可重跑。高峰期禁止无边界全表聚合。九、推荐实施路线阶段一无需/少 DDL先止血稀疏清理改为主键批扫 Java 过滤 持久化游标。对「已有索引仍慢」的 SQL 用真实参数做EXPLAIN ANALYZE。宽表业务键查询保持两段式并给IN分批与监控。确认高频在途查询是否存在 SKU 循环调用优先提供批量接口。拉取选品相关表的线上完整 DDL / 索引核实统计任务调度频率。阶段二SQL 改写与低风险修正UNION ALL改为条件聚合 包裹预聚合并用脱敏生产参数对比结果口径。覆盖状态重叠、空值、无包裹、多包裹、特殊单据、异常结算等场景。选品已上品统计视情况改为EXISTS列表去*全量反选改只查 ID。按执行计划谨慎补齐统计索引禁止重复建设。阶段三结构治理与容量验证上线选品增量计数与持久化幂等。全量统计降级为对账。全量目标 SQL 再跑EXPLAIN ANALYZE。灰度观察 CPU、IOPS、活跃连接、临时表、主从延迟、慢 SQL 次数与计数差异。十、验收标准可直接写进方案/测试单清理类任务单批读取行数不超过配置的scanBatchSize。某批无命中时游标仍能推进。任务中断后可从检查点续跑。不误删正常记录。索引类查询EXPLAIN ANALYZE明确使用目标索引。排除隐式转换与失控的超大IN。相同业务请求不再重复打库。条件聚合类统计新旧 SQL 在全部样本上结果完全一致。主表重复访问从 N 次收敛到 12 次以执行计划为准。一对多关联不放大主表金额。批量接口上线后日执行次数显著下降。选品计数今日统计只扫描目标日期。已上品统计无一对多重复计数。常规链路不再做历史全表聚合。增量计数与对账一致重复消息不加重复数。删除/回退后计数符合确认口径。十一、可复用的排查套路建议收藏遇到慢 SQL建议按下面顺序推进而不是一上来改表1. 这是「单次太慢」还是「单次还行但调用太猛」 2. 线上真实索引是什么SHOW INDEX / SHOW CREATE TABLE 3. 真实参数下的计划是什么EXPLAIN ANALYZE 4. 有没有隐式转换、超大 IN、SELECT *、循环查库 5. 是缺索引还是缺批量还是缺缓存/增量模型 6. 改写后口径是否与旧逻辑逐项对齐 7. 灰度看的是扫描行数、耗时还是 CPU/IOPS/延迟再对应到解法选型问题类型优先解法稀疏条件沿主键猛扫主键范围扫 应用过滤 游标持久化有索引却不走修类型/字符集/统计信息/IN 规模同表被 UNION 多次扫条件聚合一对多先预聚合日调用十万级短查询批量接口 / 减少 RPC宽字段回表贵两段式先 ID 后明细禁止*周期性全历史 COUNT短期索引止血长期增量计数 对账JOIN 一对多放大计数EXISTS或先去重/预聚合十二、总结这次治理里最有价值的不是某条「神级 SQL」而是几条可迁移的判断慢 SQL 的根因经常在调用形态而不仅在 SQL 文本。一天九万次的短查询批量化优先于微优化。索引是工具不是条件反射。先证明「有没有、用没用、该不该」再决定 DDL。性能改写必须带着口径回归。UNION ALL改SUM(CASE)、JOIN改EXISTS都可能改变业务结果。统计类指标最终要离开全表扫描。索引只能买时间增量事件 低频对账才是终局。所有上线都以真实参数的EXPLAIN ANALYZE和灰度指标说话。文档里的执行计划截图替代不了生产参数下的那一次验证。如果你也在做中台/商品/选品这类「表持续增长 统计看板实时刷新」的系统建议把「可增量指标」从全表聚合名单里抠出来——往往比再加三五个索引更划算。附录上线前检查清单已用线上账号执行SHOW CREATE TABLE/SHOW INDEX确认无重复索引已用脱敏后的真实参数跑通新旧 SQL结果一致已对比EXPLAIN ANALYZE的扫描行数、回表、临时表、耗时批量IN已分批删除/更新事务大小可控清理类任务具备检查点与单次扫描上限增量计数具备幂等键与对账任务灰度观察 CPU / IOPS / 连接数 / 主从延迟 / 慢 SQL 次数完文章内容来自真实慢 SQL 专项治理经验的脱敏整理欢迎讨论你在业务里遇到的「有索引仍然慢」案例。 基于真实慢 SQL 的 MySQL 优化实践：从索引排查到增量架构
基于真实慢 SQL 的 MySQL 优化实践从索引排查到增量架构本文基于某电商商品中心一次真实慢 SQL 治理经验整理覆盖「高频短查询」「稀疏条件扫描」「UNION ALL 重复访问」「选品池全表聚合」等高发问题。文中表名、字段名、路径与业务标识均已脱敏数值为治理过程中的量级参考上线前请以真实环境EXPLAIN ANALYZE为准。一、背景与问题画像在一次 RDS 慢 SQL 专项排查中我们发现慢查询并不总是「缺一个索引」那么简单。有的表明明已经有索引却仍累计扫描数亿行有的 SQL 单次扫描行数不高却因为一天执行近十万次把数据库打满有的统计任务看似“简单 COUNT”却周期性扫完整张历史大表。本次治理涉及两类业务业务域典型问题量级特征脱敏后商品/订单中心稀疏条件清理、补货关联查询、在途数量统计、SKU 映射查询单次扫描可达千万级部分 SQL 日调用约 9 万 次选品中心任务入池统计、已上品关联统计、池内分页与全量反选相关 SQL 累计扫描约亿级单次统计扫描约数十万行如果只盯着「加索引」很容易踩三个坑重复建索引线上已有同名字段索引DDL 只会增加写入成本。治标不治本索引无法消灭「周期性全历史聚合」。口径被改坏为了性能把UNION ALL改成互斥CASE统计结果悄悄变了。因此我们把优化原则先定死再谈具体改法。二、优化原则先于技巧不因慢 SQL 记录直接重复加索引先SHOW INDEX/SHOW CREATE TABLE再用真实参数跑EXPLAIN ANALYZE。优先减少重复扫描、宽字段回表、无边界批处理很多“慢”不是算不出来而是算得太频繁、取了太多无用字段。游标必须按“已扫描位置”推进不能按“过滤结果”推进否则稀疏命中时任务会卡死在原地。条件聚合改写必须保持原叠加语义UNION ALL允许同一行命中多个分支互斥CASE会改口径。一对多关联先预聚合再回连主表否则金额/数量会被包裹行放大。可增量维护的指标不要长期依赖全表 GROUP BY短期靠索引止血中长期靠事件驱动计数 低频对账。三、案例一稀疏条件清理 —— 别迷信「WHERE 非空 主键」3.1 现象某清理任务按主键分批删除「存在错误信息」的记录SQL 类似SELECTidFROMai_image_match_resultWHEREid?ANDerror_messageISNOTNULLORDERBYidASCLIMIT1000;慢日志显示单次扫描约2475 万行只返回 1000 行。原因是error_message非空记录非常稀疏优化器沿主键一路跳过大量正常行才能凑够一批。3.2 错误思路第一反应往往是给error_message建索引。但大字段/文本类索引成本高且清理是低频任务不值得给正常写入路径增加索引维护开销。3.3 正确思路主键范围扫描 应用层过滤 持久化游标数据库只做稳定、可预期的主键范围读取SELECTid,error_messageFROMai_image_match_resultWHEREid#{scanCursor}ORDERBYidASCLIMIT#{scanBatchSize};Java 侧关键规则scanCursor表示「已经扫描到的位置」不是「最后一条错误记录」。每批查完后先用本批最后一条记录的 id 更新游标。在内存中过滤errorMessage ! null或业务等价判断。只删除过滤后的 ID本批没有可删数据时仍然继续下一批。返回行数 scanBatchSize时结束本轮。游标必须持久化任务参数 / Redis / 状态表禁止每次从 0 重扫全表。参考伪代码longscanCursorloadCheckpoint();// 持久化检查点intscanBatchSize5000;longmaxScanRows500_000L;// 单次任务上限longscanned0L;while(scannedmaxScanRows){ListRowrowsmapper.selectScanBatch(scanCursor,scanBatchSize);if(rows.isEmpty()){break;}// 关键游标按“已扫描记录”推进scanCursorrows.get(rows.size()-1).getId();saveCheckpoint(scanCursor);ListLongdeleteIdsrows.stream().filter(r-r.getErrorMessage()!null).map(Row::getId).collect(Collectors.toList());// 删除再按 500~1000 分批避免超长 IN / 大事务for(ListLongpart:Lists.partition(deleteIds,800)){mapper.deleteBatchIds(part);}scannedrows.size();if(rows.size()scanBatchSize){break;}}3.4 收益与代价收益每批扫描量有明确上限执行时间可预期。不新增大字段索引不影响正常写入。方便叠加「保留期」等业务规则例如只删 N 天前的失败数据。代价失败记录极少时完整清理仍需读大量正常行。若不限制单次任务工作量、不持久化游标只是把一个超长 SQL 拆成大量小 SQL整体压力未必下降。四、案例二索引已存在仍很慢 —— 先证明索引有没有被用上4.1 现象补货明细表上按replenish_ref_id查询的 SQL 日执行约两千次累计扫描约14 亿行平均每次返回却很少。排查后确认该字段线上已有索引因此「再加一个同名单列索引」是错误动作。4.2 排查清单用线上真实参数执行EXPLAINANALYZESELECT/* 实际业务字段 */...FROMorder_item_overseasWHEREreplenish_ref_idIN(...);重点核对检查项为什么重要字段类型 vs Java 参数类型BIGINT对VARCHAR等隐式转换会导致索引失效字符集 / 排序规则关联或比较时发生转换同样可能走不了索引IN集合规模过大时优化器可能改走全表扫描是否含重复值 / 空值放大解析与探测成本统计信息是否过期ANALYZE TABLE后再看计划是否循环重复查同一批 ID调用层缓存 / 批量合并往往比改 SQL 更有效返回字段是否过宽先查主键再按主键取明细建议先确认事实SHOWINDEXFROMorder_item_overseas;ANALYZETABLEorder_item_overseas;4.3 处理策略若执行计划未使用索引优先修类型一致性、拆分超大IN、刷新统计信息。若执行计划已使用索引但回表仍贵收缩 SELECT 字段调用方只需少量 ID 时采用「先 ID、后明细」两段式。若业务侧在循环里重复打相同条件先消重复调用再谈 SQL。经验慢 SQL 面板上的「扫描行数巨大」既可能是缺索引也可能是「索引在但没用上」还可能是「用了但仍被超高频调用放大」。三者解法完全不同。五、案例三高频 UNION ALL —— 把四次主表访问收敛成条件聚合5.1 业务结构在途数量统计对同一组条件供应商 SKU 采购类型连续访问主表四次典型形态是四段UNION ALL指定状态上限下累计下单量指定状态集合下累计「下单 - 缺货 - 入库 - 问题」从包裹表累计在途包裹量累计某类单据的入库量作为扣减项。单次扫描行数未必夸张但日调用约9.6 万次问题变成主表被重复访问四次SQL 反复解析/优化调用方往往在 SKU 循环中逐条 RPC / 逐条查库。5.2 推荐改写主表条件聚合 包裹预聚合核心写法是多个独立的SUM(CASE ...)再相加而不是一个互斥CASESELECTCOALESCE(SUM(CASEWHENa.is_special#{normalFlag}ANDa.settle_flag#{normalSettle}ANDa.abnormal_qty#{normalAbnormal}ANDa.status#{statusUpper}THENa.order_qtyELSE0END),0)COALESCE(SUM(CASEWHENa.is_special#{normalFlag}ANDa.settle_flag#{normalSettle}ANDa.abnormal_qty#{normalAbnormal}ANDa.statusIN(/* statusList */)THENa.order_qty-COALESCE(a.shortage_qty,0)-COALESCE(a.store_qty,0)-COALESCE(a.problem_qty,0)ELSE0END),0)COALESCE(SUM(CASEWHENa.settle_flag#{packageSettle}THENCOALESCE(pkg.transit_qty,0)ELSE0END),0)-COALESCE(SUM(CASEWHENa.is_special#{specialFlag}ANDa.settle_flag#{specialSettle}THENCOALESCE(a.store_qty,0)ELSE0END),0)AStransit_qtyFROMfactory_order aLEFTJOIN(SELECTb.order_id,SUM(COALESCE(b.order_qty,0)-COALESCE(b.shortage_qty,0)-COALESCE(b.problem_qty,0))AStransit_qtyFROMfactory_order_package bINNERJOINfactory_order faONfa.idb.order_idWHEREfa.supplier_code#{supplierCode}ANDfa.sku#{sku}ANDfa.purchase_typeIN(/* types */)ANDfa.settle_flag#{packageSettle}GROUPBYb.order_id)pkgONpkg.order_ida.idWHEREa.supplier_code#{supplierCode}ANDa.sku#{sku}ANDa.purchase_typeIN(/* types */);5.3 为什么不能写成一个 CASE-- 错误示范互斥 CASE 会改变口径CASEWHEN条件1THEN...WHEN条件2THEN...END原UNION ALL允许同一条订单同时命中多个分支并累加。互斥CASE只命中第一个条件金额口径会被悄悄改掉。这一点在 code review 里必须当成正确性缺陷而不是风格问题。5.4 包裹表为什么要先聚合一个订单对应多个包裹时如果直接JOIN再SUM(主表字段)主表数量会被放大。必须包裹表按 order_id 预聚合 - 再 LEFT JOIN 回主表 - 对主表字段做条件求和并且尽量把主表筛选条件下推到包裹子查询避免对整个包裹表做无条件聚合。5.5 比 SQL 改写更大的收益批量接口把supplier sku - transitQty升级为supplier skuList - Mapsku, transitQtySQL 按supplier_code, sku分组一次算一批禁止在 SKU 循环中逐条查库。对「单次不慢、一天九万次」这类问题批量化往往比微优化单条 SQL 更有效。索引侧优先核对存在则不重复建factory_order(supplier_code, sku, purchase_type) factory_order_package(order_id)低基数字段状态、结算标记等是否进联合索引以执行计划选择性为准不要凭感觉堆索引。六、案例四宽表按业务键查询 —— 两段式「先 ID后明细」6.1 方案当业务键如来源编码已有索引时保留两段式即可不必再叠加重复索引-- 第一段只取主键尽量走业务键索引减少回表SELECTidFROMproduct_skuWHEREsource_codeIN(...);-- 第二段按主键批量取真正需要的字段SELECTid,source_code,sku_codeFROMproduct_skuWHEREidIN(...);6.2 落地注意点第一段只查 ID不要SELECT *。入参集合先去重建议每批 5001000最终以压测为准。Java 类型、字段类型、字符集必须一致避免隐式转换。第二段返回顺序不稳定需要映射后按输入顺序重排。一对多关系不要用MapsourceCode, Entity覆盖应按业务键分组。若两段式仍扫描数千万行优先怀疑线上索引与认知不一致参数隐式转换统计信息异常慢日志采集时间早于索引上线时间IN过大导致优化器放弃索引。七、案例五选品池统计 —— 全表 GROUP BY 的结构性陷阱前面几个案例偏「单条 SQL / 单次调用」优化选品模块则是另一类更危险的问题用全历史聚合支撑实时看板指标。7.1 三条核心统计链路1当日入池数量—— 相对健康有日期条件SELECTtask_type,task_id,COUNT(*)AScntFROMselection_pool_itemWHEREis_deleted0ANDtask_idISNOTNULLANDpool_date?GROUPBYtask_type,task_id;2历史入池总数—— 典型慢查询温床SELECTtask_type,task_id,COUNT(*)AScntFROMselection_pool_itemWHEREis_deleted0ANDtask_idISNOTNULLGROUPBYtask_type,task_id;这类 SQL每次都扫全部有效历史。索引只能减少回表无法消除全量扫描数据越大越慢而且会随业务增长线性恶化。3已上品数量—— 大表实时关联SELECTa.task_type,a.task_id,COUNT(*)ASpublished_cntFROMselection_pool_item aINNERJOINsource_product bONb.source_data_ida.offer_idANDb.status3WHEREa.status2ANDa.is_deleted0ANDa.task_idISNOTNULLGROUPBYa.task_type,a.task_id;风险点若一个source_data_id对应多条有效商品选品池记录会被重复计数若池内status2已能代表上品成功关联可能是冗余的两张增长型大表实时 JOIN GROUP BY成本会持续上升。7.2 为什么「仓库 DDL」不能代表线上初始建表索引往往服务于写入去重例如PRIMARYKEY(id),UNIQUEKEYuk_task_offer_date(task_type,task_id,offer_id,pool_date),KEYidx_batch_id(batch_id)但 Mapper 早已使用大量后续新增字段删除标记、池类型、销量、发布状态、自动上品规则等。必须以线上SHOW CREATE TABLE/SHOW INDEX为准禁止按仓库旧脚本直接下 DDL。八、选品治理短期止血 中长期增量8.1 短期索引与 SQL 形态修正在确认线上无等价索引后可考虑-- 今日统计把日期放最前尽量覆盖 where group byALTERTABLEselection_pool_itemADDINDEXidx_pool_date_del_task(pool_date,is_deleted,task_type,task_id);-- 历史总数仅过渡方案降低回表不能消灭全扫ALTERTABLEselection_pool_itemADDINDEXidx_del_task(is_deleted,task_type,task_id);-- 已上品统计两侧都要核对ALTERTABLEselection_pool_itemADDINDEXidx_status_del_task_offer(status,is_deleted,task_type,task_id,offer_id);ALTERTABLEsource_productADDINDEXidx_source_data_status(source_data_id,status);已上品计数建议先排查一对多SELECTsource_data_id,COUNT(*)FROMsource_productWHEREstatus3ANDsource_data_idISNOTNULLGROUPBYsource_data_idHAVINGCOUNT(*)1LIMIT100;若业务口径是「存在一条有效商品即算已发布」把JOIN改成EXISTS避免重复计数SELECTa.task_type,a.task_id,COUNT(*)ASpublished_cntFROMselection_pool_item aWHEREa.status2ANDa.is_deleted0ANDa.task_idISNOTNULLANDEXISTS(SELECT1FROMsource_product bWHEREb.source_data_ida.offer_idANDb.status3)GROUPBYa.task_type,a.task_id;如果业务确认「池状态已上品」与「源商品状态已生效」强一致也可以直接按池表状态统计——但这是业务决策不能仅为性能擅自删关联。8.2 列表与全量反选别再SELECT *选品池表通常包含标题、图片 URL、JSON 快照、销售趋势等宽字段。列表页显式字段只返回展示与操作所需列。全量反选 / 批量上品先只查 ID再分批处理SELECTp.idFROMselection_pool_item pWHERE/* 动态条件 */ORDERBYp.idLIMIT?;禁止一次把全部实体加载进 JVM。价格排序若依赖相关子查询反复算MIN(price)高频场景应冗余最低价字段或独立价格汇总表。8.3 终局方案事件驱动增量计数维护三类指标指标含义today_count任务当天新增的有效入池数total_count任务累计有效入池数published_count任务累计成功上品数入池成功以affectedRows1为准插入 selection_pool_item - affectedRows 1 ? - total_count 1 - pool_date 为业务当天则 today_count 1 - 同事务提交跨库则 Outbox/MQ 幂等命中唯一键的重复数据不得重复计数。上品成功仅首次状态迁移加一UPDATEselection_pool_itemSETstatus2,onboard_time?WHEREid?ANDstatus2;仅当affectedRows1时published_count 1抵御 MQ 重复消费、重试和并发回调。若存在「按 offer 批量联动其他池记录」的逻辑必须找出真正发生状态迁移的行再按task_type task_id分组更新不能只给当前任务 1。删除 / 回退逻辑删除、已上品回退要产生负增量忽略状态是否扣减必须与现网统计口径对齐后再改。幂等键建议eventId 或 poolItemId eventType statusVersion不要只靠带过期时间的 Redis 锁做幂等。8.4 全量 SQL 降级为对账增量上线后全量GROUP BY只保留为每日低峰对账按 ID / 日期分片扫描按任务汇总真实计数与任务表计数对比只修正有差异的任务记录修正前值、真实值、差异与时间保证可审计、可重跑。高峰期禁止无边界全表聚合。九、推荐实施路线阶段一无需/少 DDL先止血稀疏清理改为主键批扫 Java 过滤 持久化游标。对「已有索引仍慢」的 SQL 用真实参数做EXPLAIN ANALYZE。宽表业务键查询保持两段式并给IN分批与监控。确认高频在途查询是否存在 SKU 循环调用优先提供批量接口。拉取选品相关表的线上完整 DDL / 索引核实统计任务调度频率。阶段二SQL 改写与低风险修正UNION ALL改为条件聚合 包裹预聚合并用脱敏生产参数对比结果口径。覆盖状态重叠、空值、无包裹、多包裹、特殊单据、异常结算等场景。选品已上品统计视情况改为EXISTS列表去*全量反选改只查 ID。按执行计划谨慎补齐统计索引禁止重复建设。阶段三结构治理与容量验证上线选品增量计数与持久化幂等。全量统计降级为对账。全量目标 SQL 再跑EXPLAIN ANALYZE。灰度观察 CPU、IOPS、活跃连接、临时表、主从延迟、慢 SQL 次数与计数差异。十、验收标准可直接写进方案/测试单清理类任务单批读取行数不超过配置的scanBatchSize。某批无命中时游标仍能推进。任务中断后可从检查点续跑。不误删正常记录。索引类查询EXPLAIN ANALYZE明确使用目标索引。排除隐式转换与失控的超大IN。相同业务请求不再重复打库。条件聚合类统计新旧 SQL 在全部样本上结果完全一致。主表重复访问从 N 次收敛到 12 次以执行计划为准。一对多关联不放大主表金额。批量接口上线后日执行次数显著下降。选品计数今日统计只扫描目标日期。已上品统计无一对多重复计数。常规链路不再做历史全表聚合。增量计数与对账一致重复消息不加重复数。删除/回退后计数符合确认口径。十一、可复用的排查套路建议收藏遇到慢 SQL建议按下面顺序推进而不是一上来改表1. 这是「单次太慢」还是「单次还行但调用太猛」 2. 线上真实索引是什么SHOW INDEX / SHOW CREATE TABLE 3. 真实参数下的计划是什么EXPLAIN ANALYZE 4. 有没有隐式转换、超大 IN、SELECT *、循环查库 5. 是缺索引还是缺批量还是缺缓存/增量模型 6. 改写后口径是否与旧逻辑逐项对齐 7. 灰度看的是扫描行数、耗时还是 CPU/IOPS/延迟再对应到解法选型问题类型优先解法稀疏条件沿主键猛扫主键范围扫 应用过滤 游标持久化有索引却不走修类型/字符集/统计信息/IN 规模同表被 UNION 多次扫条件聚合一对多先预聚合日调用十万级短查询批量接口 / 减少 RPC宽字段回表贵两段式先 ID 后明细禁止*周期性全历史 COUNT短期索引止血长期增量计数 对账JOIN 一对多放大计数EXISTS或先去重/预聚合十二、总结这次治理里最有价值的不是某条「神级 SQL」而是几条可迁移的判断慢 SQL 的根因经常在调用形态而不仅在 SQL 文本。一天九万次的短查询批量化优先于微优化。索引是工具不是条件反射。先证明「有没有、用没用、该不该」再决定 DDL。性能改写必须带着口径回归。UNION ALL改SUM(CASE)、JOIN改EXISTS都可能改变业务结果。统计类指标最终要离开全表扫描。索引只能买时间增量事件 低频对账才是终局。所有上线都以真实参数的EXPLAIN ANALYZE和灰度指标说话。文档里的执行计划截图替代不了生产参数下的那一次验证。如果你也在做中台/商品/选品这类「表持续增长 统计看板实时刷新」的系统建议把「可增量指标」从全表聚合名单里抠出来——往往比再加三五个索引更划算。附录上线前检查清单已用线上账号执行SHOW CREATE TABLE/SHOW INDEX确认无重复索引已用脱敏后的真实参数跑通新旧 SQL结果一致已对比EXPLAIN ANALYZE的扫描行数、回表、临时表、耗时批量IN已分批删除/更新事务大小可控清理类任务具备检查点与单次扫描上限增量计数具备幂等键与对账任务灰度观察 CPU / IOPS / 连接数 / 主从延迟 / 慢 SQL 次数完文章内容来自真实慢 SQL 专项治理经验的脱敏整理欢迎讨论你在业务里遇到的「有索引仍然慢」案例。