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

描述
索引跳跃扫描优化是一种数据库查询优化技术，用于处理在复合索引（多列索引）上，当查询条件只包含索引的非前导列（即不是索引定义的第一列）时，如何高效地利用该索引。在没有此优化的情况下，此类查询通常无法有效使用该复合索引，可能导致全表扫描。索引跳跃扫描允许数据库引擎“跳过”索引的前导列，直接访问非前导列的数据，从而将全索引扫描的效率提升至接近索引范围扫描的水平。

解题过程

1. 问题背景与挑战

   • 复合索引结构：假设有一个复合索引 INDEX (A, B, C)。该索引的数据是按照列A的值进行排序的，在A列值相同的情况下，再按B列排序，以此类推。这就像一本电话簿，先按姓氏排序，同姓氏下再按名字排序。
   • 查询场景：现在有一个查询，其 WHERE 子句的条件是 B = ?，而没有对A列的任何条件。例如：SELECT * FROM table_name WHERE B = '某个值'。
   • 传统方式的局限：由于索引是按(A, B, C)排序的，直接查找 B='某个值' 就像在一本按姓氏排序的电话簿里直接找某个名字（而不关心姓氏）一样，无法利用排序优势，只能从头到尾扫描整本电话簿（即全索引扫描）。虽然全索引扫描通常比全表扫描快（因为索引更小），但当索引很大时，其开销依然可观。

2. 索引跳跃扫描的基本思想

   • 索引跳跃扫描的核心思路是：将一次对非前导列的全索引扫描，分解为多个小的、对前导列不同取值的范围扫描。
   • 它通过识别出索引前导列（A列）所有不同的值，然后为每一个不同的A值，执行一次在索引上查找 A=特定值 AND B='某个值' 的查询。因为对于每个固定的A值，索引中B列的数据是有序的，所以每次这样的查询都是一个高效的范围扫描。
   • 过程拆解：
     1. 识别前导列不同值：数据库首先快速扫描索引（或使用统计信息）获取列A的所有不同值（Distinct Values），例如得到 A1, A2, A3。
     2. 执行多次“微”查询：对于识别出的每一个A列的值（如 A1），数据库执行一次逻辑上的子查询：SELECT ... WHERE A = A1 AND B = '某个值'。由于索引在 (A, B) 上有序，这次查询可以快速定位到满足 A=A1 且 B='某个值' 的索引条目。
     3. 合并结果：将所有针对不同A值的“微”查询结果合并，作为最终的查询结果。

3. 技术实现的关键点

   • 前导列基数（Cardinality）：此优化的效果高度依赖于前导列A的不同值数量（即基数）。
     • 低基数是关键：如果A列只有少数几个不同的值（例如“性别”列，只有‘男’，‘女’），那么数据库只需要执行少数几次范围扫描。这种情况下，性能提升非常显著，几乎等同于在(B)上有一个单列索引。
     • 高基数效果差：如果A列有非常多的不同值（例如“用户ID”），那么数据库需要执行成千上万次微查询。每次微查询虽然很快，但成千上万次的累加开销可能比直接进行全索引扫描还要大。因此，优化器通常只在判断前导列基数较低时才会选择索引跳跃扫描。
   • 优化器的决策：查询优化器会根据表的统计信息（特别是前导列的不同值数量）来估算全索引扫描的成本和索引跳跃扫描的成本，然后选择成本更低的执行计划。

4. 举例说明

   • 表结构：Employees (EmployeeID, Department, Salary)
   • 索引：在 (Department, Salary) 上创建了一个复合索引。
   • 查询：查找所有薪资为50000的员工。

     SELECT EmployeeID FROM Employees WHERE Salary = 50000;
     

   • 没有跳跃扫描：优化器可能选择全表扫描或全索引扫描，因为查询条件没有用到索引的前导列 Department。
   • 启用跳跃扫描：
     1. 优化器发现前导列 Department 的不同值数量不多（例如，公司只有10个部门）。
     2. 它决定采用跳跃扫描。执行计划大致如下：
        • 获取所有不同的部门值：['HR', 'Engineering', 'Finance', ...]（假设共10个）。
        • 对于每个部门，如 'HR'，在索引中查找 Department = 'HR' AND Salary = 50000。由于索引在(Department, Salary)上排序，这次查找非常高效。
        • 对10个部门依次执行上述操作，然后将10次查找的结果合并。
     • 最终，这个操作只扫描了索引中与10个部门相关的、薪资为50000的少量数据块，而不是扫描整个索引。

5. 数据库支持与提示

   • 数据库支持：并非所有数据库都支持索引跳跃扫描。Oracle数据库对此有明确的支持（称为 Index Skip Scan）。MySQL的InnoDB引擎从8.0.13版本开始也引入了类似的优化（称为 Loose Index Scan 的一种变体）。PostgreSQL等数据库可能通过其他方式（如仅索引扫描）来部分达到类似效果，或者依赖于其他索引类型（如BRIN索引）。
   • 优化器提示：在某些数据库中，可以使用优化器提示（如Oracle的 INDEX_SS）来强制优化器考虑或使用索引跳跃扫描。

总结
索引跳跃扫描是一种巧妙的优化技术，它通过将一次低效的全索引扫描分解为多次高效的范围扫描，解决了复合索引中查询条件不包含前导列时的性能瓶颈。其有效性取决于索引前导列的基数，基数越低，优化效果越明显。理解这一技术有助于数据库开发者和DBA更好地设计索引和解读执行计划。