# 基于代价估算选择索引条件下推 #### 操作场景 社区MySQL中，索引条件下推（Index Condition Pushdown，ICP）是一种将 WHERE 条件下推至存储引擎层检查的优化技术。但是 ICP 决策时机滞后于执行计划的规划阶段，仅在执行计划确定后才判断是否启用 ICP。这种基于规则的延迟决策可能导致以下两类问题： - 等值访问路径选择遗漏：当多个索引共享相同起始列时（如idx_a(a)、idx_ab(a,b)、idx_abc(a,b,c)），优化器在等值访问路径选择时主要按扫描行数比较候选索引。对于查询条件 WHERE a = 4 AND c = 9，三个索引在 a = 4 上扫描行数相同，最窄的 idx_a 获胜，而只有 idx_abc 能通过ICP过滤 c = 9，这份收益被完全忽略。 - 范围访问路径选择遗漏：范围扫描候选在代价比较时按存储引擎上报的原始代价与全表扫描基线比较。存储引擎的代价仅反映原始行读取开销，对后续可下推的条件一无所知。对于 WHERE a BETWEEN 2 AND 2.5 AND c = 7，所有范围扫描候选的原始代价可能高于全表扫描，导致优化器放弃索引扫描，ICP无从参与。 TaurusDB支持基于代价估算选择ICP功能。当该功能开启时，优化器在等值访问路径选择和范围访问路径选择阶段提前预估ICP收益，将ICP过滤效果纳入代价估算，使能利用ICP过滤的更宽索引在候选比较中获得公平竞争力，从而选择更优的执行计划。 #### 原理介绍 基于代价估算选择ICP是一种在访问路径选择阶段预估ICP过滤收益的优化策略。优化器在保证结果集不变的前提下，将ICP对代价的影响前置到计划生成阶段。 #### ICP性能收益原理 在二级索引扫描过程中，如果查询需要访问索引以外的列（即非覆盖索引场景），InnoDB 存储引擎需要对索引中匹配的每一行执行回表操作，从聚簇索引读取完整行数据，再由SQL层逐行检查WHERE条件。 当WHERE条件涉及索引尾部键列时（例如idx_abc(a, b, c)上的c = 9），这些列的值已经存在于索引记录中，在回表之前就可以在存储引擎层直接判断条件是否满足。 ICP（Index Condition Pushdown，索引条件下推） 正是利用了这一特性：将 WHERE 条件下推至存储引擎层，在索引扫描时直接检查，跳过不满足条件的行的回表操作。 ICP的性能收益来源于两个方面： - 减少回表IO：被ICP过滤的行无需进行聚簇索引回表，节省了磁盘IO和缓冲池访问开销。对于选择性较高的过滤条件，回表次数可大幅减少。 - 降低CPU开销：引擎侧直接在索引记录的键值上判断条件，无需构造完整的表达式树、调用虚函数和执行类型转换，相比SQL层的条件检查开销更低。 例如，对于 WHERE a = 4 AND c = 9，在 idx_abc 上执行索引扫描时：引擎先按 a = 4 定位索引记录，然后在索引记录中直接检查 c 列的值是否等于9；不满足的行直接跳过，无需进行聚簇索引回表；满足的行才执行回表。相比无ICP时每行都回表再由SQL层检查 c = 9，ICP将回表次数从 a = 4 匹配的行数降低到 a = 4 AND c = 9 匹配的行数。 #### 适用场景 该功能在优化器规划阶段执行，位于等值访问路径选择和范围访问路径选择的候选比较环节。优化器会先检查索引是否支持 ICP、是否存在可下推的尾部键列条件、过滤阈值是否满足，满足条件时将ICP收益从原始代价中扣除，使调整后的代价参与候选比较。因此，开启功能后，可能观察到执行计划选择了更宽的索引，且 EXPLAIN 输出的 Extra 列中出现 Using index condition。 - 等值访问场景 对于如下查询： ``` SELECT * FROM t WHERE a = 4 AND c = 9; ``` 假设表上存在 idx_a(a) 和 idx_abc(a, b, c) 两个索引。在社区 MySQL 中，两个索引在 a = 4 上扫描行数相同，idx_a 因更窄而胜出。但 idx_abc 可以通过 ICP 将 c = 9 下推至引擎层检查，减少回表次数。开启该功能后，优化器会预估 ICP 对 idx_abc 的代价收益，使 idx_abc 在候选比较中胜出。 - 范围扫描场景 对于如下查询： ``` SELECT * FROM t WHERE a BETWEEN 2 AND 2.5 AND c = 7; ``` 在社区 MySQL 中，范围扫描候选按原始代价比较，各索引代价相近时最窄索引胜出。但更宽的 idx_abc 可以通过 ICP 过滤 c = 7，减少回表和行检查开销。开启该功能后，优化器会预估 ICP 过滤收益，将调整后的代价参与候选比较，使 idx_abc 胜出。 #### 代价模型与防护机制 - 代价模型 ICP 的性能收益来源于 IO 和 CPU 两个方面的节省，与 MySQL 现有公式保持一致： - IO节省：被 ICP 过滤的行无需回表读取数据页，直接节省了回表 IO 开销。 - CPU节省：引擎侧条件检查开销低于 SQL 层，被过滤的行无需经过 SQL 层的完整表达式求值。 - 收益上限：ICP 收益不超过其可影响的代价比例，避免调整后代价失真。 - 防护机制 为防止 ICP 收益预估过度乐观导致索引选择偏移，该功能设计了以下防护机制： - 过滤阈值：仅当 ICP 可下推条件的选择率足够低时才启用代价调整，确保ICP能过滤足够比例的行。该阈值排除了选择率估算过于粗糙的条件（如不等式、多个 OR 组合），避免这些不可靠的估算用于影响索引选择而导致更差的计划。 - 收益上限：ICP 收益不超过其可影响的代价比例，确保调整后代价非负且合理。 - 引擎侧代价因子：引擎侧 ICP 条件检查代价建模为低于 SQL 层，避免高估 CPU 节省。 #### 前提条件 - TaurusDB内核版本为2.0.78.260602或以上的版本。内核版本的查询方法请参见[如何查看云数据库 TaurusDB实例的版本号](https://support.huaweicloud.com/taurusdb_faq/taurusdb_faq_0141.html)。 - optimizer_switch 中 icp_cost_based 参数为 ON。 #### 支持的查询语句 - SELECT - INSERT ... SELECT（单表查询） - REPLACE ... SELECT（单表查询） - CREATE TABLE ... SELECT（单表查询） - UNION各分支中的符合条件单表查询 #### 约束限制 - 仅支持单表查询，不支持多表JOIN查询和子查询。多表JOIN的跨表条件可能导致过滤率估算不准确，引入回归风险。 - 不支持多表UPDATE、多表DELETE语句。 - 不支持全文索引（FULLTEXT），全文索引使用不同的ICP机制。 - 不支持向量索引（HA_VECTOR），向量索引使用不同的访问方式。 - 不支持聚簇主键，聚簇主键的ICP收益有限。 - 不支持虚拟生成列索引。 - 不支持覆盖索引（covering index）的仅索引扫描，覆盖索引无需ICP。 - 不支持索引合并和按行ID有序扫描。 - 不支持反向索引扫描，反向扫描时执行层会跳过ICP。 - 不支持BLOB、TEXT、GEOMETRY类型字段的ICP代价估算。 - 不支持前缀索引部分的ICP代价估算。 - 由于WHERE条件的选择率估算在无直方图时使用固定估值，可能与实际数据分布偏差较大，因此少数场景下可能出现因ICP代价调整导致索引选择变化而引起的性能劣化。如遇到此类情况，可通过SET optimizer_switch='icp_cost_based=off'关闭该功能恢复原有行为。 #### 使用方法 您可以通过optimizer_switch参数设置基于代价估算选择ICP功能。 表1参数说明 | 参数名称 | 级别 | 描述 | |:---|:---|:---| | optimizer_switch | Global、Session | 通过 icp_cost_based=on 开启基于代价估算选择索引条件下推功能，通过 icp_cost_based=off 关闭该功能。默认关闭。 | 开启功能： ``` SET optimizer_switch='icp_cost_based=ON'; ``` 关闭功能： ``` SET optimizer_switch='icp_cost_based=OFF'; ``` 用户可以通过 optimizer_trace 查看基于代价估算选择 ICP 是否生效。 - 当 ICP 有收益时，trace 中会显示icp_filter_effect和icp_cost等字段信息； - 当 ICP 无收益时，trace 中会显示icp_skip_reason字段说明跳过原因，常见的取值包括： - no_icp_candidates：该索引没有可下推的尾部键列条件。 - filter_above_threshold：可下推条件的选择率过高，ICP收益不明显。 - can_not_consider_icp：索引本身不支持ICP（如全文索引、聚簇主键等）。 - all_keyparts_bound：索引的所有键列都已被访问方法绑定，无需ICP。 #### 使用示例 创建测试表： ``` CREATE TABLE t ( id INT NOT NULL AUTO_INCREMENT, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT, payload CHAR(32), PRIMARY KEY(id), INDEX idx_a(a), INDEX idx_ab(a, b), INDEX idx_abc(a, b, c), INDEX idx_abcd(a, b, c, d) ); INSERT INTO t (a, b, c, d, payload) WITH RECURSIVE number_series AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM number_series WHERE n < 1000 ) SELECT n % 5 + 1, n % 10 + 1, n % 100 + 1, n % 1000 + 1, LPAD(n, 32, 'x') FROM number_series; ANALYZE TABLE t; ``` - [等值访问路径：ICP选择更宽索引] 先关闭基于代价估算选择ICP： ``` SET optimizer_switch='icp_cost_based=off'; EXPLAIN SELECT COUNT(payload) FROM t WHERE a = 4 AND c = 9\G ``` 关闭该功能后，优化器选择最窄的idx_a，无法利用ICP过滤 c = 9： ``` mysql> EXPLAIN SELECT COUNT(payload) FROM t WHERE a = 4 AND c = 9\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref possible_keys: idx_a,idx_ab,idx_abc,idx_abcd key: idx_a key_len: 4 ref: const rows: 200 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) ``` 再开启基于代价估算选择ICP： ``` SET optimizer_switch='icp_cost_based=on'; EXPLAIN SELECT COUNT(payload) FROM t WHERE a = 4 AND c = 9\G ``` 开启该功能后，优化器选择 idx_abc，通过 ICP 将 c = 9 下推至引擎层检查，减少回表次数： ``` mysql> EXPLAIN SELECT COUNT(payload) FROM t WHERE a = 4 AND c = 9\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref possible_keys: idx_a,idx_ab,idx_abc,idx_abcd key: idx_abc key_len: 4 ref: const rows: 200 filtered: 10.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec) ``` 通过 optimizer_trace 可以看到 ICP 对各候选索引代价的具体影响： ``` SET optimizer_switch='icp_cost_based=on'; SET optimizer_trace='enabled=on'; SELECT COUNT(payload) FROM t WHERE a = 4 AND c = 9; SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G ``` optimizer_trace 结果的 considered_access_paths 步骤中，可以看到各候选索引的 ICP 信息： ``` { "access_type": "ref", "index": "idx_a", "icp_skip_reason": "no_icp_candidates", "rows": 200, "cost": 25.25, "chosen": true }, { "access_type": "ref", "index": "idx_abc", "icp_original_cost": 25.25, "icp_filter_effect": 0.1, "icp_cost": 11.5, "rows": 200, "cost": 11.5, "chosen": true } ``` idx_a 没有可下推的尾部键列条件，显示 icp_skip_reason: no_icp_candidates；idx_abc 可以通过 ICP 下推 c = 9，代价从 25.25 降至11.5，最终在候选比较中胜出。 - [范围访问路径：ICP选择更宽索引] 先关闭基于代价估算选择ICP： ``` SET optimizer_switch='icp_cost_based=off'; EXPLAIN SELECT COUNT(payload) FROM t WHERE a BETWEEN 2 AND 2.5 AND c = 7\G ``` 关闭该功能后，优化器选择 idx_a，ICP 仅在执行阶段对 idx_a 生效，无法利用 idx_abc 的更宽覆盖： ``` mysql> EXPLAIN SELECT COUNT(payload) FROM t WHERE a BETWEEN 2 AND 2.5 AND c = 7\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: range possible_keys: idx_a,idx_ab,idx_abc,idx_abcd key: idx_a key_len: 4 ref: NULL rows: 200 filtered: 10.00 Extra: Using index condition; Using where 1 row in set, 1 warning (0.00 sec) ``` 再开启基于代价估算选择ICP： ``` SET optimizer_switch='icp_cost_based=on'; EXPLAIN SELECT COUNT(payload) FROM t WHERE a BETWEEN 2 AND 2.5 AND c = 7\G ``` 开启该功能后，优化器选择 idx_abc，通过 ICP 将 c = 7 下推至引擎层检查： ``` mysql> EXPLAIN SELECT COUNT(payload) FROM t WHERE a BETWEEN 2 AND 2.5 AND c = 7\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: range possible_keys: idx_a,idx_ab,idx_abc,idx_abcd key: idx_abc key_len: 4 ref: NULL rows: 200 filtered: 10.00 Extra: Using index condition 1 row in set, 1 warning (0.01 sec) ``` 通过 optimizer_trace 可以看到范围扫描候选中 ICP 对代价的具体调整： ``` SET optimizer_switch='icp_cost_based=on'; SET optimizer_trace='enabled=on'; SELECT COUNT(payload) FROM t WHERE a BETWEEN 2 AND 2.5 AND c = 7; SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G ``` optimizer_trace 结果的 range_scan_alternatives 步骤中，可以看到各候选索引的 ICP 信息： ``` { "index": "idx_a", "icp_skip_reason": "no_icp_candidates", "cost": 70.26, "chosen": true }, { "index": "idx_abc", "icp_original_cost": 70.26, "icp_filter_effect": 0.1, "icp_cost": 11.76, "chosen": true } ``` idx_a 没有可下推的尾部键列条件，显示 icp_skip_reason: no_icp_candidates；idx_abc 可以通过 ICP 下推 c = 7，代价从 70.26 降至11.76，最终在候选比较中胜出。