Daily Study
更新: 9/1/2025 字数: 0 字 时长: 0 分钟
Daily Plan
#todo
- [ ]
MySQL 索引下推
索引下推（Index Condition Pushdown，简称 ICP）是 MySQL（5.6 起）的一个查询优化：把原本由 SQL 层（Server 层）做的部分 WHERE 过滤，下推给存储引擎在“索引扫描阶段”就用索引记录里的列进行判断，从而尽量减少“回表”（按主键再去读整行）的次数，降低随机 I/O 和行访问数量。
它解决的痛点
- 没有 ICP 时的流程（以 InnoDB、二级索引为例）：
- 根据可用索引做“范围扫描”（range/ref），把落在索引范围内的所有索引项取出来。
- 对每个索引项都“回表”读取整行数据。
- SQL 层再用 WHERE 的其余条件过滤。 这样如果范围条件较宽，会产生大量无效回表。
- 有 ICP 时：
- 存储引擎扫描索引范围。
- 在“仍停留在索引层”的阶段，先用可在索引记录中就能取到的列来评估更多 WHERE 条件。
- 只有通过这些条件的索引项才回表读取整行，再交给 SQL 层做剩余过滤。 这样能显著减少回表次数，尤其在二级索引+随机 I/O 显著的场景。
典型示例
- 表 t(a, b, c, d, …)，复合二级索引 idx(a, b, c)。
- 查询：where a = 10 and c = 5 and d = 'X'
- 按“最左前缀”规则，能用于“定义索引范围”的只有 a=10（b、c 无法同时用于范围，c 也不是紧邻 a 的下一列）。
- 无 ICP 时：先扫描 a=10 的全部索引项，对每个索引项都回表，然后由 SQL 层判断 c=5、d='X'。
- 有 ICP 时：引擎在索引项（已包含 a、b、c 和主键）阶段就先判断 c=5，不满足的直接丢弃，不回表；仅对满足 c=5 的行回表再判断 d='X'。
- 回表量显著下降。
何时会用到 ICP
- 访问类型通常为 range、ref、eq_ref、ref_or_null 等对索引的范围/等值访问。
- 存储引擎常见为 InnoDB、MyISAM（InnoDB 最常见）。
- 主要收益在“二级索引 + 需要回表”的场景。
- EXPLAIN 的 Extra 字段出现“Using index condition”，表示启用了索引下推。
和其他 EXPLAIN 提示的区别
- Using where：WHERE 过滤发生在 SQL 层（常见）。
- Using index：覆盖索引（Index Only Scan），查询所需列都在索引里，不必回表。此时没有“回表”可省，ICP 没有意义，一般不会显示 Using index condition。
- Using index condition：发生了索引下推，即在索引扫描时已用索引中的列进行额外过滤，用以减少后续回表。
能被“下推”的条件大致规则
- 条件只引用“当前正在扫描的同一个索引记录里可读到的列”（即该索引包含的列）。对不在该索引内的列无法下推（只能回表后再判断）。
- 条件需是可在存储引擎层快速评估的“可下推表达式”（大多是确定性的比较，如 =、<、>、<=、>=、BETWEEN、IN、IS NULL 等；函数/表达式是否可下推视版本与函数类型而定，复杂或非确定性的表达式通常不下推）。
- 对复合索引，即使某些列不是“定义范围”的左前缀列，也仍可能被 ICP 用来进一步过滤（这正是 ICP 的价值所在）。
何时没有或收益很小
- 覆盖索引（Using index）：本来就不回表，ICP无额外收益。
- 主键访问（InnoDB 聚簇索引）：读取聚簇索引记录时本就拿到整行，避免回表的收益有限或无意义。
- 条件基本不具选择性（几乎都通过），下推后仍需回表几乎所有行，收益不大。
- 全表/全索引扫描（type=ALL 或 index）且条件不能利用索引；或数据已在缓冲中、回表代价很小，收益可能有限。
如何验证是否生效
- EXPLAIN 看 Extra 是否出现 “Using index condition”（也可能同时看到 “Using where”）。
- 对比启用/关闭 ICP 的执行情况：
set [session] optimizer_switch='index_condition_pushdown=on/off';- 比较执行时间、Rows_examined、Handler_read_* 计数器（show session status like 'Handler_read%';）、回表次数（InnoDB 随机读）等。
- 观察实际回表量是否下降（可借助 performance_schema 或引擎层统计）。
与“最左前缀”“覆盖索引”的关系
- 复合索引的“最左前缀”仍决定“索引范围”的构造；ICP并不会改变范围本身。
- 但 ICP 能在范围内继续利用该索引中“非前缀列”的条件做进一步过滤，减少回表。
- 如果能通过“覆盖索引”直接满足查询（选择列与过滤列都在索引里），通常优先考虑覆盖索引，收益常常高于 ICP。
常见限制与注意事项
- 仅能下推到“正在使用的那个索引”的列条件；跨表条件、需要整行数据的条件、子查询、非确定性函数、部分复杂表达式通常无法下推。
- 并非所有存储引擎或所有版本都支持；MySQL 5.6 引入，后续版本完善；MariaDB 也支持但实现细节略有差异。
- 不会改变优化器选择的索引，只是在选定索引后减少不必要的回表。
- 如果条件一开始就能被改写成更窄的索引范围（SARGable），应优先让优化器直接构造更窄的 range，而不是寄望于 ICP 事后过滤。
实践建议
- 尽量让过滤条件“可利用索引”（SARGable），避免对索引列施加函数、表达式或隐式类型转换。
- 设计复合索引时，将选择性高、常作为等值/范围前导的列放前面；对经常在后续列上过滤但无法成为范围的一些条件，ICP 仍能提供额外收益。
- 能覆盖就覆盖（覆盖索引），覆盖不了时 ICP 是降低回表的“第二选择”。
- 用 EXPLAIN 和实际压测验证：观察 “Using index condition”、Rows_examined、查询时延与 I/O。
总结
索引下推不会改变“能不能用上索引”，而是在“已经在用索引扫描”的前提下，把更多 WHERE 条件提前在索引层过滤，减少回表，通常显著降低 I/O 与行访问数，是 MySQL 查询优化中的重要一环。