MySQL 联合索引遇到范围查询，后一列为何失效：扫描行数差 20 倍的对照实验

本文摘要范围条件写在中间列时后一列只能过滤不能定位扫描行数成倍放大。对照实验显示定位在范围列截止ICP 只在索引层过滤。结论适用于 InnoDB B-tree 索引调整列序前须核对查询分布。一、问题与结论线上一张约 200 万行的订单明细表order_items高频查询按shop_id、order_status、created_date三个条件取数索引idx_shop_status_date就按这个顺序建。把order_status 3写成order_status 3后仍命中同一索引EXPLAIN中type从ref变成range估算rows从三位数跳到四位数以上高峰时段耗时明显上升。结论先说清楚索引没有失效是 B-tree 的定位能力在第一个范围列处截止created_date ?仍能过滤但无法再用于跳转到目标条目。Extra: Using index conditionICP把该条件下推到存储引擎的索引扫描阶段省回表不省扫描行数。在 1 万行的缩比实验里两条只差一个操作符的 SQL 索引扫描量约 6000 与 286 条约 21 倍。换列序只是把问题转移给另一类查询是否再建索引取决于查询分布与写入量。实验环境为 MySQL 8.0.18支持EXPLAIN ANALYZE、InnoDB线上 200 万行表的耗时未实测文中不给未经验证的毫秒数。二、排查与选择依据先用EXPLAIN ANALYZE看实际扫描行数确认差距来自能否定位而不是统计信息误判再决定动列序、加覆盖列还是改写 SQL。排查顺序确认引擎与版本InnoDB 标准 B-treeEXPLAIN ANALYZE需 8.0.18更早版本用EXPLAIN FORMATJSON看估算值。执行ANALYZE TABLE order_items;刷新统计信息看估算rows是否接近真实。对比type、ref、rows、Extraref且ref列为const,const,const说明三列都参与定位range且ref为NULL说明只有前缀参与定位。Extra只有Using where时后一列条件要等回表后才过滤比 ICP 更贵。替代方案与取舍方案适用条件代价边界范围列移到索引末尾最高频查询是等值列在前、范围列在后一条ALTER重建索引期间写入变慢另一类范围查询在同一索引上退化覆盖索引纳入查询列查询列少且固定、表不宽索引体积增大、写入维护增加含TEXT/JSON的宽表不适用按范围段拆UNION ALL范围段可枚举如order_status仅 5 个值SQL 变长优化器可能合并计划枚举值多时无法维护按created_date分区日期是主要过滤维度、表足够大改表结构与分区维护成本主键须含分区键唯一约束受限查询模式未统计清楚、或两类范围查询同时高频时不该只调列序写多读少无法接受写入放大时也不该为单一查询再加一个索引。三、关键原理B-tree 按(shop_id, order_status, created_date)排序同一order_status内created_date有序跨order_status时created_date交错排列。范围访问逐列构建定位条件一旦order_status用上扫描起点只能定到该区间开头之后顺序扫过区间内全部条目created_date ?不再参与跳转。ICP 与回表的分工要分清Using index condition表示过滤发生在引擎的索引扫描阶段索引已含该列时不必回表即可判掉不匹配的条目SELECT *命中的行仍要回表取完整行。也就是说ICP 降低的是回表次数不是扫描行数。四、可运行示例环境MySQL 8.0.18、InnoDB、默认optimizer_switch。下面脚本生成 1 万行确定性数据order_status循环 5 个值created_date循环 7 天每个(order_status, created_date)组合 285 或 286 行分布可由生成公式直接推算。DROPTABLEIFEXISTSorder_items;CREATETABLEorder_items(idBIGINTAUTO_INCREMENTPRIMARYKEY,shop_idINTNOTNULL,order_statusTINYINTNOTNULL,created_dateDATENOTNULL,product_idINTNOTNULL,qtyINTNOTNULL,amountDECIMAL(10,2)NOTNULL,INDEXidx_shop_status_date(shop_id,order_status,created_date))ENGINEInnoDB;SETSESSIONcte_max_recursion_depth10001;INSERTINTOorder_items(shop_id,order_status,created_date,product_id,qty,amount)WITHRECURSIVE nAS(SELECT0iUNIONALLSELECTi1FROMnWHEREi9999)SELECT1,1(i%5),DATE_ADD(2024-06-01,INTERVAL((iDIV5)%7)DAY),100(i%1000),1(i%10),1.00FROMn;ANALYZETABLEorder_items;两条对照 SQL-- 查询 A三列等值EXPLAINANALYZESELECT*FROMorder_itemsWHEREshop_id1ANDorder_status3ANDcreated_date2024-06-03;-- 查询 B中间列改范围条件EXPLAINANALYZESELECT*FROMorder_itemsWHEREshop_id1ANDorder_status3ANDcreated_date2024-06-03;预期输出行数由上面的生成公式精确推算可复现毫秒数未实机验证因此不列出。格式随版本略有差异。查询 Atyperefrefconst,const,const估算 rows≈286actual rows≈286 查询 BtyperangeExtraUsing index condition估算 rows≈6000ICP 过滤后 actual rows≈858 两条返回行数同量级索引扫描量 6000 : 286 ≈ 21 : 1实际输出在你的实例执行上述语句核对索引扫描迭代器的估算rows与actual rows是否落在上述区间再用计数语句交叉验证分布。SELECTCOUNT(*)FROMorder_itemsWHEREshop_id1ANDorder_status3ANDcreated_date2024-06-03;SELECTCOUNT(*)FROMorder_itemsWHEREshop_id1ANDorder_status3;SELECTCOUNT(*)FROMorder_itemsWHEREshop_id1ANDorder_status3ANDcreated_date2024-06-03;三条计数分别应为 286、6000、858 行由生成公式推出查询 B 的估算rows应与第二条计数同量级actual rows应与第三条同量级。常见失败与修复递归 CTE 超出cte_max_recursion_depth默认 1000报错先执行SET SESSION cte_max_recursion_depth 10001;。Extra只有Using where确认版本不低于 5.6、optimizer_switch中index_condition_pushdownon并检查条件列上没有函数或隐式类型转换。估算rows与实际差很多先执行ANALYZE TABLE order_items;估算值只作参考以EXPLAIN ANALYZE的实际行数为准。五、验证结果与边界对照结果两条 SQL 返回 286 与 858 行属同一量级索引扫描量却相差约 21 倍差距来自能否定位不来自能否过滤。收益递减把范围列换到末尾后查询 B 的扫描量回落但order_status 3 AND created_date ?这类查询在同一索引上又退化成range。固定列序无法兼顾两个方向的范围查询必要时两个索引共存由写入量买单。回表与写入代价SELECT *命中行要回表覆盖索引省回表但宽索引挤占 buffer pool、加宽每条写入。每多一个索引插入、更新、删除都要多维护一棵 B-tree。不该用的场景查询分布未统计、范围列选择性极低如状态只有 2 个值且均匀、写多读少无法接受写入放大大范围命中率低时按主键顺序扫或分区裁剪可能更划算。边界200 万行的线上表只会放大扫描量级机制不变行数与耗时必须在你的数据分布上实测。思考同一张表既要order_status范围又要created_date范围时你会用两个索引承担写入放大还是按日期分区换裁剪覆盖索引省回表却会加宽 B-tree、挤占 buffer pool你的表宽与写入量能接受到什么程度参考资料Range Optimization — MySQL 8.0 Reference ManualMultiple-Column Indexes — MySQL 8.0 Reference ManualIndex Condition Pushdown Optimization — MySQL 8.0 Reference ManualEXPLAIN Output Format — MySQL 8.0 Reference Manual