当过滤条件不符合最左前缀时,如何有效利用索引? | 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
代价更低。