数据库的查询执行计划中的索引范围扫描与跳跃扫描联合优化

描述
在数据库查询优化中，索引范围扫描是一种高效的索引访问方法，它利用索引的有序性，快速定位到满足某个范围条件（如 WHERE col BETWEEN 10 AND 20）的数据行。索引跳跃扫描则允许查询在复合索引（多列索引）中跳过前导列的限制，在特定场景下高效利用索引，即使查询条件未包含索引的第一列。本知识点讲解如何将这两种索引扫描技术结合使用，在复杂查询条件下实现更优的性能，核心在于优化器如何智能地选择索引访问路径，减少不必要的I/O和计算开销。

解题过程循序渐进讲解

步骤1：理解两种扫描技术的基本原理

1. 索引范围扫描：

   • 适用场景：查询条件包含索引列的范围比较（如 >, <, BETWEEN, LIKE 'prefix%'）。
   • 工作原理：利用B+树索引的排序特性，从索引中定位到范围的起始键值，然后沿叶子节点链表顺序扫描，直到范围的结束键值。这个过程避免了全表扫描，I/O开销较小。
   • 示例：对索引 INDEX(a)，查询 WHERE a BETWEEN 5 AND 10，会直接定位到 a=5 的索引条目，顺序读取出 a=5 到 a=10 的所有索引记录。

2. 索引跳跃扫描：

   • 适用场景：查询条件缺失复合索引的前导列，但包含后续列的条件，且前导列的不同值数量较少。
   • 工作原理：优化器会"跳跃"地遍历前导列的每个不同值，对每个值单独执行后续列的索引扫描，然后将结果合并。这本质上是将一次查询分解为多个子查询，每个子查询利用索引的有序性。
   • 示例：对复合索引 INDEX(gender, age)，查询 WHERE age > 30 且未指定 gender。如果 gender 只有 'M' 和 'F' 两个值，优化器会先获取所有 gender 值，然后分别执行两次扫描：一次对 (gender='M', age>30)，一次对 (gender='F', age>30)，最后合并结果。

步骤2：分析联合优化的触发场景
联合优化通常在以下复杂条件下被考虑：

• 查询条件同时包含：
  a. 复合索引中前导列的范围条件（触发范围扫描）。
  b. 复合索引中后续列的等值或范围条件（可能触发跳跃扫描的逻辑扩展）。
• 核心目标：在范围扫描限定的"大范围"内，利用跳跃扫描思想进一步减少扫描的数据量。

示例查询：
表 employees 有复合索引 INDEX(department_id, salary)，查询如下：

SELECT * FROM employees 
WHERE department_id BETWEEN 10 AND 20 
  AND salary > 5000;

这里：

• department_id BETWEEN 10 AND 20 是前导列的范围条件。
• salary > 5000 是后续列的范围条件。

步骤3：优化器的决策与扫描过程
优化器会评估两种执行路径：

1. 路径A：纯索引范围扫描

   • 沿着索引定位到 (department_id=10, salary=最小值)，然后顺序扫描所有 department_id 在 10 到 20 之间的索引条目。
   • 对每个扫描到的索引条目，检查 salary > 5000 的条件，过滤掉不满足的行。
   • 缺点：如果大多数记录的 salary 不满足条件，会扫描许多无用的索引条目。

2. 路径B：范围扫描 + 跳跃扫描联合优化

   • 优化器识别到 department_id 的范围内有多个不同的 department_id 值（比如 10, 11, ..., 20）。
   • 对每个具体的 department_id 值（如 10），在索引中定位到 (department_id=10, salary=5000) 的起始点（因为 salary 索引部分有序），然后仅扫描该 department_id 下满足 salary > 5000 的索引条目，再"跳跃"到下一个 department_id 值（如 11）重复此过程。
   • 本质：将一次大范围扫描分解为多个小范围扫描，每个小范围扫描中利用 salary 列的有序性快速定位，跳过不满足 salary 条件的索引条目。

步骤4：联合优化的实现机制
数据库（如 Oracle、MySQL 8.0）通过以下技术实现联合优化：

1. 索引跳跃扫描条件检测：优化器检查前导列的不同值数量（通过统计信息），如果数量较少且后续列有条件，则考虑跳跃。
2. 范围扫描的迭代分解：将前导列的范围条件拆解为多个等值条件（如 department_id=10、department_id=11...），对每个等值条件执行后续列的范围扫描，通过索引的有序性快速定位。
3. 结果合并：将多个子扫描的结果通过 UNION ALL 或流式方式合并，避免重复排序。

在示例中，优化器可能生成如下等效逻辑：

SELECT * FROM employees WHERE department_id=10 AND salary>5000
UNION ALL
SELECT * FROM employees WHERE department_id=11 AND salary>5000
...
UNION ALL
SELECT * FROM employees WHERE department_id=20 AND salary>5000;

但实际执行中不会产生 UNION ALL 操作，而是通过一次扫描动态跳跃完成。

步骤5：性能收益与限制

• 收益：
  • 减少I/O：跳过了前导列范围内不满足后续列条件的数据块。
  • 提高CPU缓存效率：每次子扫描的数据更局部。
  • 特别适合：前导列范围较大但实际匹配行较少，且后续列条件选择性高的场景。
• 限制：
  • 前导列的不同值数量需较少，否则跳跃开销过大。
  • 依赖准确的统计信息（如前导列基数、数据分布）。
  • 不适用于前导列条件为等值查询的简单情况（此时直接用范围扫描即可）。

步骤6：实际应用与调优建议

1. 索引设计：将区分度低但常作为范围查询的列放在复合索引前导位置，将区分度高或常作为等值查询的列放在后面，便于联合优化。
2. 查询写法：尽量将范围条件列放在索引前导列，后续列条件明确，以提示优化器。
3. 统计信息更新：确保前导列的基数（不同值数量）和分布直方图最新，否则优化器可能错误估算成本。
4. 执行计划检查：在数据库中使用 EXPLAIN 查看是否出现 Index Range Scan 结合 Skip Scan 的提示（如 Oracle 的 INDEX SKIP SCAN，MySQL 8.0 的 skip scan 访问方法）。

通过以上联合优化，数据库能在复杂范围查询中最大限度地利用复合索引的有序性，减少不必要的扫描，提升查询性能。