当过滤条件不符合最左前缀时，如何有效利用索引？ | OceanBase SQL 优化实践
本文涉及两个知识点：通过索引快速定位数据的规则，以及 OceanBase 中 index skip scan 能力。
问题背景
在《OceanBase DBA 入门教程》里，我们曾阐述过：“索引的一大功能是迅速定位数据，它能将索引列上的过滤条件转化为索引扫描的开始点和结束点。执行扫描时，仅需从开始点扫描至结束点，两点间的数据即满足索引列上的过滤条件。这段扫描范围被称为query range。这里有一个关键点需要注意：索引可以从头开始匹配多个等值谓词，直至与第一个范围谓词匹配为止。”
这个规则的详情可以参见《OceanBase DBA 入门教程》中 “常见的 SQL 调优方式” 小节中的 “索引的几个作用” 部分。这里简单举一个例子，创建一张表，建一个索引 idx，在 c1、c2 列上。
CREATE TABLE t1 (c1 INT,c2 INT,c3 INT);CREATE INDEX idx ON t1 (c1, c2);场景一：过滤条件中的等值谓词是 c1，符合索引前缀，可以利用索引 idx。
EXPLAIN BASIC SELECT * FROM t1 WHERE c1 = 1;
+----------------------------------------------------------------------------------------+
| Query Plan |
+----------------------------------------------------------------------------------------+
| ============================= |
| |ID|OPERATOR |NAME | |
| ----------------------------- |
| |0 |TABLE RANGE SCAN|t1(idx)| |
| ============================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=16 |
| access([t1.__pk_increment], [t1.c1], [t1.c2], [t1.c3]), partitions(p0) |
| is_index_back=true, is_global_index=false, |
| range_key([t1.c1], [t1.c2], [t1.__pk_increment]), range(1,MIN,MIN ; 1,MAX,MAX), |
| range_cond([t1.c1 = 1]) |
+----------------------------------------------------------------------------------------+场景二：等值谓词分别是 c1 和 c2，符合索引前缀，可以利用索引 idx。
EXPLAIN BASIC SELECT * FROM t1 WHERE c1 = 1 AND c2 = 2;
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| ============================= |
| |ID|OPERATOR |NAME | |
| ----------------------------- |
| |0 |TABLE RANGE SCAN|t1(idx)| |
| ============================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=16 |
| access([t1.__pk_increment], [t1.c1], [t1.c2], [t1.c3]), partitions(p0) |
| is_index_back=true, is_global_index=false, |
| range_key([t1.c1], [t1.c2], [t1.__pk_increment]), range(1,2,MIN ; 1,2,MAX), |
| range_cond([t1.c1 = 1], [t1.c2 = 2]) |
+------------------------------------------------------------------------------------+场景三：等值谓词是 c2，不符合索引前缀，不可以利用索引 idx。
EXPLAIN BASIC SELECT * FROM t1 WHERE c2 = 2;
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| ========================= |
| |ID|OPERATOR |NAME| |
| ------------------------- |
| |0 |TABLE FULL SCAN|t1 | |
| ========================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 = 2]), rowset=16 |
| access([t1.c2], [t1.c1], [t1.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+优化思路
问题背景和《入门教程》中的说法在一般情况下并没有错。但是，OceanBase 从 4.1 版本开始，支持了一个叫 Index Skip Scan 的能力，咱们先通过 Hint 使用这个能力，展示下当过滤条件不是最左前缀时，也能走上索引的效果：
EXPLAIN BASIC SELECT /*+ index_ss(t1 idx) */* FROM t1 WHERE c2 = 2;
+-----------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------+
| ============================ |
| |ID|OPERATOR |NAME | |
| ---------------------------- |
| |0 |TABLE SKIP SCAN|t1(idx)| |
| ============================ |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 = 2]), rowset=16 |
| access([t1.__pk_increment], [t1.c2], [t1.c1], [t1.c3]), partitions(p0) |
| is_index_back=true, is_global_index=false, filter_before_indexback[true], |
| range_key([t1.c1], [t1.c2], [t1.__pk_increment]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true |
| prefix_columns_cnt = 1 , skip_scan_range(2,MIN ; 2,MAX) |
+-----------------------------------------------------------------------------------------------------+计划里的这个算子比较奇怪，名字和之前索引范围扫描时的 TABLE RANGE SCAN 不同，变成了 TABLE SKIP SCAN。TABLE SKIP SCAN 虽然属于范围扫描，但是会跳跃执行，可以在满足一定条件的时候，利用已有的索引来加速扫描。
如果读者希望通过这个 index_ss 这个 Hint 来使用这个跳跃范围扫描的优化，需要关注下索引列的 NDV（Number of Distinct Values）。 从索引的实现原理上来讲，当表中存在一个联合索引 idx (c1, c2)，并且过滤条件是 where c2 = ？时，c1 列的 NDV 越小，c2 列的 NDV 越大，就越适合通过这个 Hint 来使用 Index Skip Scan。
What's more ?
当然，什么时候适合用 Index Skip Scan 是由表中的数据分布以及查询条件决定的，OceanBase 的优化器会根据代价决定是否使用 Index Skip Scan，自动选择最适合的查询方式。简单来说，优化器会在满足如下限制条件时会尝试进行 Index Skip Scan 的优化：
- 表上有收集过统计信息。
- 查询条件中包含联合索引的后缀列，并且不是其他索引最左前缀。
- 优化器比较
TABLE SKIP SCAN和全表扫描的代价，发现TABLE SKIP SCAN代价更低。