数据库的查询执行计划中的索引跳跃扫描优化

1. 问题描述

索引跳跃扫描（Index Skip Scan）是一种数据库查询优化技术，用于在复合索引（多列索引）中跳过前导列（leading column），直接利用非前导列进行数据检索。传统索引查询要求前导列必须出现在查询条件中（最左前缀原则），而跳跃扫描打破了这一限制，在某些场景下显著提升查询效率。

2. 适用场景与限制

• 场景：
  • 复合索引包含列 (A, B, C)，但查询条件仅包含非前导列（如 WHERE B=value）。
  • 前导列的基数（不同值的数量）较低时（例如性别、状态等枚举值），优化器可能选择跳跃扫描。
• 限制：
  • 并非所有数据库支持（如 Oracle 支持，MySQL 8.0 后部分支持，PostgreSQL 通过条件索引间接实现）。
  • 前导列基数过高时，效率可能低于全表扫描。

3. 工作原理

假设复合索引为 (A, B)，数据存储逻辑如下：

A=1 → B=1, B=2, B=3  
A=2 → B=1, B=4, B=5  

查询条件为 WHERE B=1，传统索引无法直接使用 (A, B) 索引（因 A 未指定）。跳跃扫描的步骤如下：

步骤 1：提取前导列所有可能值

优化器先扫描索引，获取前导列 A 的所有不同值（如 A=1, A=2）。

步骤 2：逐值执行子查询

将原查询重写为多个子查询的联合：

WHERE (A=1 AND B=1) OR (A=2 AND B=1)

相当于对每个 A 的取值，单独利用索引查询 B=1 的数据。

步骤 3：合并结果

将各子查询的结果合并，返回最终数据。

4. 性能优化逻辑

• 减少扫描范围：
  跳跃扫描仅需遍历索引中前导列的每个不同值对应的索引子树，避免全索引扫描。
• 对比全表扫描：
  若表数据量巨大且符合条件的数据较少，跳跃扫描的 I/O 成本远低于全表扫描。

5. 实际案例

表结构与数据：

CREATE TABLE sales (
    region VARCHAR(10),  -- 地区（低基数，如东/西/南/北）
    product_id INT,
    sale_date DATE,
    INDEX idx_region_product (region, product_id)
);

查询：

SELECT * FROM sales WHERE product_id = 100;

• 无跳跃扫描：优化器可能选择全表扫描。
• 有跳跃扫描：
  1. 获取 region 的所有值（如 '东','西','南','北'）。
  2. 依次查询 (region='东' AND product_id=100)、(region='西' AND product_id=100) 等。
  3. 合并结果，直接通过索引定位数据。

6. 数据库实现差异

• Oracle：
  使用 INDEX SKIP SCAN 提示，通过统计信息判断前导列基数是否适合跳跃扫描。
• MySQL 8.0：
  类似功能通过多值范围查询（Multi-Range Read）间接实现。
• PostgreSQL：
  需创建额外索引（如单独索引 B 列）或使用部分索引（Partial Index）模拟。

7. 优化器决策因素

• 前导列基数：基数越低，跳跃扫描成本越小。
• 数据分布：若前导列某些值对应的非前导列数据集中，效率更高。
• 索引选择度：非前导列的条件过滤性越强，跳跃扫描收益越大。

8. 使用建议

• 在低基数列作为前导列的复合索引中，跳跃扫描效果显著。
• 可通过数据库统计信息（如 ANALYZE TABLE）帮助优化器决策。
• 若不支持跳跃扫描，可考虑为非前导列创建独立索引或调整索引顺序。

通过以上步骤，跳跃扫描在特定场景下高效利用复合索引，弥补了最左前缀原则的局限性，是数据库高级优化的重要技术之一。