当前位置: 首页 > news >正文

当过滤条件不符合最左前缀时,如何有效利用索引? | 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 SCANTABLE 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 代价更低。
http://www.lryc.cn/news/606131.html

相关文章:

  • 0731 IO进程基础
  • FATFS文件系统
  • 从“救火”到“先知”:润建曲尺运维大模型如何重构网络运维价值链
  • 科研快报 |无人机+AI:广东防控基孔热背后的技术革命
  • 大疆无人机开发:MQTT 赋能机场系统集成的Java实战之旅
  • 九识智能与星逻智能达成战略合作,共推“无人车 + 无人机”空地一体巡检升级
  • 5G 单兵终端 + 无人机:消防应急场景的 “空 - 地” 救援协同体系
  • 无人机光伏巡检缺陷检出率↑32%:陌讯多模态融合算法实战解析
  • Lombok 字段魔法:用 @FieldDefaults 解锁“隐身+锁死”双重特效
  • php session 和 jwt 区别和使用场景
  • Java试题-选择题(2)
  • sqli-labs:Less-13关卡详细解析
  • 数据大集网:引领精准获客新时代的优质平台
  • 智慧医院导航系统:基于GPS+蓝牙ibeacon多源融合定位与deepseek•AI导诊问答的设计与实现
  • Linux 时钟同步配置:基础管理与 chrony 工具应用
  • 多架构镜像整合全攻略:在Docker中实现单一镜像支持同时支持amd64和arm64架构
  • hive新增列之后插入新数据时,新列为NULL的解决办法
  • CentOS 7 编译 Redis 6.x 完整教程(解决 GCC 版本不支持 C11)
  • 告别物业思维:科技正重构产业园区的价值坐标系
  • AR智能巡检:工业4.0时代的降本增效利器
  • [人工智能-综述-17]:AI革命:重塑职业版图,开启文明新篇
  • 数据集归一化
  • 机器学习之逻辑回归(Logistic Regression)
  • 视觉图像处理中级篇 [2]—— 外观检查 / 伤痕模式的原理与优化设置方法
  • 【支持Ubuntu22】Ambari3.0.0+Bigtop3.2.0——Step5—Nginx安装
  • Qt 常用控件 - 3
  • vue-seamless-scroll 与 echarts 三联水球图循环滚动的渲染难题-出现短暂空白
  • iOS高级开发工程师面试——其他
  • Linux大页内存导致服务内存不足
  • Java——方法