数据库查询优化中的索引跳跃扫描优化技术

一、知识点描述
索引跳跃扫描（Index Skip Scan）是一种数据库查询优化技术，用于在复合索引（多列索引）中，当查询条件只包含后置列而缺失前置列时，仍然能够利用该索引进行数据访问。传统索引查找需要遵循最左前缀原则，而索引跳跃扫描通过逻辑上"跳过"缺失的前置列，将查询分解为多个子查询来利用索引，从而避免全表扫描。

二、背景与问题

1. 复合索引的最左前缀原则：对于索引(A,B,C)，查询条件必须包含A列才能有效使用该索引
2. 现实查询模式：实际业务中经常出现只使用B列或C列作为查询条件的情况
3. 传统解决方案的局限性：
   • 全表扫描：性能差，尤其对大表
   • 创建新索引：增加存储和维护成本
   • 查询重写：需要应用层修改

三、技术原理详解

步骤1：索引结构分析

• 复合索引在物理上按所有列组合排序
• 例如索引(A,B)的存储结构：

A=1,B=1 → 行指针1
A=1,B=2 → 行指针2
A=2,B=1 → 行指针3
A=2,B=2 → 行指针4

步骤2：跳跃扫描的核心思想

• 将WHERE B=2的查询逻辑上重写为：

-- 原查询
SELECT * FROM table WHERE B=2;

-- 逻辑等价重写
SELECT * FROM table WHERE A=1 AND B=2
UNION ALL
SELECT * FROM table WHERE A=2 AND B=2;

• 数据库优化器自动识别索引中A列的所有不同值
• 对每个不同的A值，执行一次索引范围扫描查找B=2的记录

步骤3：执行过程分解

1. 提取前缀列不同值：首先扫描索引，收集A列的所有不同值（1,2）
2. 生成子查询计划：为每个A值生成一个索引范围扫描：
   • 扫描[A=1,B=2]范围的索引条目
   • 扫描[A=2,B=2]范围的索引条目
3. 合并结果：将各次扫描结果合并返回

四、适用条件与限制

适用场景：

• 复合索引的前置列基数（不同值数量）较低
• 查询条件包含索引后置列但不含前置列
• 前置列的选择性较差（重复值多）

技术限制：

• 前置列基数过高时效率下降（需要执行太多次子扫描）
• 不是所有数据库都支持该优化
• 对索引维护要求较高，需要统计信息准确

五、实际示例说明

表结构和索引：

CREATE TABLE sales (
    region VARCHAR(10),    -- 地区，基数低
    product_id INT,        -- 产品ID，基数高  
    sale_date DATE,
    amount DECIMAL(10,2)
);

CREATE INDEX idx_region_product ON sales(region, product_id);

查询分析：

-- 传统情况：能使用索引（满足最左前缀）
SELECT * FROM sales WHERE region='北京' AND product_id=1001;

-- 跳跃扫描场景：缺失region条件
SELECT * FROM sales WHERE product_id=1001;

跳跃扫描执行过程：

1. 识别region的所有不同值：['北京','上海','广州','深圳']
2. 分别执行4次索引查找：
   • 查找(region='北京', product_id=1001)
   • 查找(region='上海', product_id=1001)
   • 查找(region='广州', product_id=1001)
   • 查找(region='深圳', product_id=1001)
3. 合并4次查找的结果

六、性能考量

优势：

• 避免全表扫描，尤其对大表性能显著提升
• 减少额外索引的创建和维护成本
• 自动优化，对应用透明

代价因素：

• 前置列基数：基数越低，子扫描次数越少，效率越高
• 索引聚类因子：数据物理分布影响IO成本
• 统计信息准确性：影响优化器是否选择该计划

七、数据库支持情况

• Oracle：支持跳跃扫描（Index Skip Scan）
• MySQL 8.0：支持类似技术（Loose Index Scan）
• PostgreSQL：通过BITMAP索引扫描实现类似效果
• SQL Server：通过索引交集等技术间接实现

该技术通过在逻辑层面智能地分解查询，突破了传统最左前缀原则的限制，是复合索引优化的重要补充手段。