本文摘要：范围条件写在中间列时，后一列只能过滤不能定位，扫描行数成倍放大。对照实验显示定位在范围列截止，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 condition（ICP）把该条件下推到存储引擎的索引扫描阶段，省回表，不省扫描行数。- 在 1 万行的缩比实验里，两条只差一个操作符的 SQL 索引扫描量约 6000 与 286 条，约 21 倍。
- 换列序只是把问题转移给另一类查询，是否再建索引取决于查询分布与写入量。
实验环境为 MySQL 8.0.18+（支持EXPLAIN ANALYZE）、InnoDB；线上 200 万行表的耗时未实测，文中不给未经验证的毫秒数。
二、排查与选择依据
先用EXPLAIN ANALYZE看实际扫描行数，确认差距来自能否定位而不是统计信息误判，再决定动列序、加覆盖列还是改写 SQL。排查顺序：
- 确认引擎与版本：InnoDB 标准 B-tree；
EXPLAIN ANALYZE需 8.0.18+，更早版本用EXPLAIN FORMAT=JSON看估算值。 - 执行
ANALYZE TABLE order_items;刷新统计信息，看估算rows是否接近真实。 - 对比
type、ref、rows、Extra：ref且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))ENGINE=InnoDB;SETSESSIONcte_max_recursion_depth=10001;INSERTINTOorder_items(shop_id,order_status,created_date,product_id,qty,amount)WITHRECURSIVE nAS(SELECT0iUNIONALLSELECTi+1FROMnWHEREi<9999)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_id=1ANDorder_status=3ANDcreated_date='2024-06-03';-- 查询 B：中间列改范围条件EXPLAINANALYZESELECT*FROMorder_itemsWHEREshop_id=1ANDorder_status>=3ANDcreated_date='2024-06-03';预期输出：行数由上面的生成公式精确推算，可复现；毫秒数未实机验证，因此不列出。格式随版本略有差异。
查询 A：type=ref，ref=const,const,const，估算 rows≈286，actual rows≈286 查询 B：type=range，Extra=Using index condition，估算 rows≈6000，ICP 过滤后 actual rows≈858 两条返回行数同量级，索引扫描量 6000 : 286 ≈ 21 : 1实际输出：在你的实例执行上述语句，核对索引扫描迭代器的估算rows与actual rows是否落在上述区间，再用计数语句交叉验证分布。
SELECTCOUNT(*)FROMorder_itemsWHEREshop_id=1ANDorder_status=3ANDcreated_date='2024-06-03';SELECTCOUNT(*)FROMorder_itemsWHEREshop_id=1ANDorder_status>=3;SELECTCOUNT(*)FROMorder_itemsWHEREshop_id=1ANDorder_status>=3ANDcreated_date='2024-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_pushdown=on，并检查条件列上没有函数或隐式类型转换。- 估算
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 Manual
- Multiple-Column Indexes — MySQL 8.0 Reference Manual
- Index Condition Pushdown Optimization — MySQL 8.0 Reference Manual
- EXPLAIN Output Format — MySQL 8.0 Reference Manual
