解析OceanBase v4.2函数索引进行查询优化

2024-04-15 19:12
文章标签 进行 函数 查询 优化 索引 解析 oceanbase v4.2

本文主要是介绍解析OceanBase v4.2函数索引进行查询优化，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一、如何通过函数索引来进行查询优化

函数索引是一种优化查询的技术，其主要作用在于提升包含函数调用的查询语句的执行速度。当查询语句中包含函数调用时，数据库系统需要逐行执行函数计算，这无疑会增加查询的复杂性，导致查询速度下降。然而，通过创建函数索引，我们可以在查询时直接定位到匹配的函数值，从而避免重复计算，提高查询的速度。下面，我们将通过一个具体示例来展示如何利用函数索引来优化查询性能。

假设有一张表t1，t1中有一个数据类型为date的列date_col：

create table t1(date_col date, sales_col int, name_col varchar(10));

如果需要获取3月份的数据，那么可以使用Month()函数来查询：

select * from t1 where month(date_col) = 3;

这种场景下，数据库需要为表中每行计算month(date_col)，过滤掉不符合"month(date_col) = 3"的行。如果需要频繁使用月份信息来过滤数据，每次都需要重新计算month(date_col)，就会造成大量开销。因此可以在date_col上建一个函数索引，将month(date_col)存储到了索引表中，来加速查询，创建函数索引的语句如下：

create index i1 on t1((month(date_col)));

建立索引后，查询时就可以直接使用该索引，避免了对每个行进行函数计算，提高了查询效率。

explain select * from t1 where month(date_col) = 3;
+-------------------------------------------------------------------------------------------------+
| Query Plan                                                                                      |
+-------------------------------------------------------------------------------------------------+
| ==================================================                                              |
| |ID|OPERATOR        |NAME  |EST.ROWS|EST.TIME(us)|                                              |
| --------------------------------------------------                                              |
| |0 |TABLE RANGE SCAN|t1(i1)|1       |7           |                                              |
| ==================================================                                              |
| Outputs & filters:                                                                              |
| -------------------------------------                                                           |
|   0 - output([t1.date_col], [t1.sales_col], [t1.name_col]), filter(nil), rowset=16              |
|       access([t1.__pk_increment], [t1.date_col], [t1.sales_col], [t1.name_col]), partitions(p0) |
|       is_index_back=true, is_global_index=false,                                                |
|       range_key([t1.SYS_NC19$], [t1.__pk_increment]), range(3,MIN ; 3,MAX),                     |
|       range_cond([t1.SYS_NC19$ = 3])                                                            |
+-------------------------------------------------------------------------------------------------+

二、OceanBase 4.2在MySQL模式下支持函数索引功能

OceanBase 4.1 以及之前的版本中，已在Oracle模式下支持了函数索引功能。OceanBase 4.2 在MySQL模式支持函数索引功能，兼容MySQL 8.0。

示例1：使用create index语句创建函数索引。

语法如下：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_nameON tbl_name (expr,...)[index_option] ...key_part: (expr) [ASC | DESC]

expr是一个合法的函数索引表达式，且允许是布尔表达式，例如"c1=c1"。与MySQL不同的是，OceanBase禁止在函数索引的定义中引用生成列。

例如以下语句在t1_func表上创建了一个索引定义是c1+c2 < 1的函数索引i1。

create table t1_func(c1 int, c2 int);
create index i1 on t1_func ((c1+c2 < 1));

示例2：使用alter table语句创建函数索引。

语法如下：

ALTER TABLE tbl_name[alter_option [, alter_option] ...][partition_options]alter_option: {table_options| ADD {INDEX | KEY} [index_name][index_type] (key_part,...) [index_option] ...| ADD SPATIAL [INDEX | KEY] [index_name](key_part,...) [index_option] ...| ...key_part: (expr) [ASC | DESC]

例如以下语句在t1_func上添加了3个函数索引，其中一个名字是i2，另外两个由系统自动生成的名称，格式为‘functional_index’前缀加编号。

alter table t1_func add index ((concat(c1,'a')));
alter table t1_func add index ((c1+1));
alter table t1_func add index i2 ((concat(c1,'a')));

示例3：使用create table语句在建表时创建函数索引。

语法如下：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name(create_definition,...)[table_options][partition_options]create_definition: {col_name column_definition| {INDEX | KEY} [index_name] [index_type] (key_part,...)[index_option] ...| SPATIAL [INDEX | KEY] [index_name] (key_part,...)[index_option] ...| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY][index_name] [index_type] (key_part,...)[index_option] ......
}key_part: (expr) [ASC | DESC]

例如以下语句在创建表t2_func时，创建了一个函数索引i1。

create table t2_func (c1 int, c2 int, index i1 ((c1+1)), unique key ((c1+c2)));

可以使用show create table语句查看创建的函数索引：

show create table t1_func;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1_func | CREATE TABLE `t1_func` (`c1` int(11) DEFAULT NULL,`c2` int(11) DEFAULT NULL,KEY `i1` (((`c1` + `c2`) < 1)) BLOCK_SIZE 16384 LOCAL,KEY `functional_index` (concat(`c1`,'a')) BLOCK_SIZE 16384 LOCAL,KEY `functional_index_2` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL,KEY `i2` (concat(`c1`,'a')) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'lz4_1.0' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+show create table t2_func;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                       |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2_func | CREATE TABLE `t2_func` (`c1` int(11) DEFAULT NULL,`c2` int(11) DEFAULT NULL,UNIQUE KEY `functional_index` ((`c1` + `c2`)) BLOCK_SIZE 16384 LOCAL,KEY `i1` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'lz4_1.0' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

也可以使用show index命令来查看已创建的函数索引，例如：

show index from t1_func;
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------------+
| Table   | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression          |
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------------+
| t1_func |          1 | i1                 |            1 | SYS_NC18$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | ((`c1` + `c2`) < 1) |
| t1_func |          1 | functional_index   |            1 | SYS_NC19$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | concat(`c1`,'a')    |
| t1_func |          1 | functional_index_2 |            1 | SYS_NC20$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | (`c1` + 1)          |
| t1_func |          1 | i2                 |            1 | SYS_NC19$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | concat(`c1`,'a')    |
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------------+show index from t2_func;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression    |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------+
| t2_func |          1 | i1               |            1 | SYS_NC18$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | (`c1` + 1)    |
| t2_func |          0 | functional_index |            1 | SYS_NC19$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | (`c1` + `c2`) |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------+

三、OceanBase 4.2禁止非确定性函数用于函数索引

OceanBase 4.2版本禁止了一些非确定性的系统函数被用于创建函数索引和生成列，以提升稳定性。这些系统函数的结果会随着系统或用户环境的变化而变化的函数，例如：

select current_time();
+----------------+
| current_time() |
+----------------+
| 20:44:22       |
+----------------+select current_time();
+----------------+
| current_time() |
+----------------+
| 20:44:24       |
+----------------+

MySQL模式

以下函数被禁止用于生成列和函数索引：

aes_decryptfrom_unixtimestatement_digest
aes_encrypthost_ipsys_privilege_check
benchmarkicu_versionsysdate
connection_idis_serving_tenanttime_to_usec
current_datelast_excution_idunix_timestamp
current_timelast_insert_idusec_to_time
current_timestamplnnvluser
current_userlocaltimestamputc_date
current_user_privmysql_portutc_time
databasename_constutc_timestamp
des_decryptob_versionuuid
des_encryptranduuid_short
des_hex_strrandom_bytesvalidate_password_strength
dumprow_countversion
encryptrpc_portweight_string
found_rowssleep

一些时间相关的系统函数禁止在参数为TIME类型时用于函数索引，例如：

create table time_func(c1 time, c2 date);
create index i1 on time_func((date(c1)));
ERROR 3758 (HY000): Expression of functional index contains a disallowed function.

涉及以下函数：

adddatedayofyeartimestampdiff
datelast_dayto_days
date_submonthweek
datediffmonthnameweekday
dayquarterweekofyear
daynametimestampyear
dayofmonthtimestamp_nvlyearweek
dayofweektimestampadd

此外，cast函数将time类型的参数转换成非time的其他时间类型，或者将timestamp类型的参数转换为非timestamp的其他数据类型时，也被禁止用于生成列和函数索引，例如：

create index i1 on time_func((cast(c1 as DATE)));
ERROR 3758 (HY000): Expression of functional index contains a disallowed function.

Oracle模式

与之前的版本相比，4.2版本在Oracle模式下新增禁止了以下函数用于生成列和函数索引：

dumpob_versionrpc_port
host_ipregexp_countscn_to_timestamp
is_serving_tenantregexp_instrtimestamp_to_scn
last_trace_idregexp_substr

以下系统函数在参数是字符串类型时禁止用于函数索引：

add_monthsnext_day
last_daytimestamp_nvl
months_between

例如：

create table t1(c1 date, c2 varchar(40), c3 timestamp);
create index i1 on t1(ADD_MONTHS(c2, 1));
ORA-01743: only pure functions can be indexed

此外，cast函数在将字符串转为时间类型、将时间类型转为字符串、将不带timezone信息的时间类型转为带timezone信息的时间类型的情况下禁止用于函数索引，例如：

create table t2(c1 varchar(20), c2 date, c3 TIMESTAMP WITH TIME ZONE, c4 number);
create index i1 on t2(cast(c1 as DATE));
ORA-01743: only pure functions can be indexedcreate index i1 on t2(cast(c2 as TIMESTAMP WITH TIME ZONE));
ORA-01743: only pure functions can be indexedcreate index i1 on t2(cast(c3 as VARCHAR(10)));
ORA-01743: only pure functions can be indexed

四、总结

Oceanbase 4.2版本支持了在MySQL模式下创建和使用函数索引，并且禁止了部分非确定性函数用于创建函数索引和生成列以提升稳定性。但是其中部分函数在MySQL和Oracle中是允许用于函数索引的，例如：

(Mysql 8.0.31)
mysql> create table time_func(c1 timestamp, c2 date);
Query OK, 0 rows affected (0.04 sec)mysql> create index i1 on time_func((date(c1)));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Oceanbase后续版本将会放开对这些函数的限制，进一步提升兼容性。

这篇关于解析OceanBase v4.2函数索引进行查询优化的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/906674。

相关文章

MyBatis模糊查询报错：ParserException: not supported.pos 问题解决

MyBatis模糊查询报错：ParserException: not supported.pos 问题解决

《MyBatis模糊查询报错：ParserException:notsupported.pos问题解决》本文主要介绍了MyBatis模糊查询报错：ParserException:notsuppo... 目录问题描述问题根源错误SQL解析逻辑深层原因分析三种解决方案方案一：使用CONCAT函数（推荐）方案二：
阅读更多...
Qt实现网络数据解析的方法总结

Qt实现网络数据解析的方法总结

《Qt实现网络数据解析的方法总结》在Qt中解析网络数据通常涉及接收原始字节流,并将其转换为有意义的应用层数据,这篇文章为大家介绍了详细步骤和示例,感兴趣的小伙伴可以了解下... 目录1. 网络数据接收2. 缓冲区管理（处理粘包/拆包）3. 常见数据格式解析3.1 jsON解析3.2 XML解析3.3 自定义
阅读更多...
利用python实现对excel文件进行加密

利用python实现对excel文件进行加密

《利用python实现对excel文件进行加密》由于文件内容的私密性,需要对Excel文件进行加密,保护文件以免给第三方看到,本文将以Python语言为例,和大家讲讲如何对Excel文件进行加密,感兴... 目录前言方法一：使用pywin32库（仅限Windows）方法二：使用msoffcrypto-too
阅读更多...
MySQL 中的 JSON 查询案例详解

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》：本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J
阅读更多...
Golang HashMap实现原理解析

Golang HashMap实现原理解析

《GolangHashMap实现原理解析》HashMap是一种基于哈希表实现的键值对存储结构,它通过哈希函数将键映射到数组的索引位置,支持高效的插入、查找和删除操作,：本文主要介绍GolangH... 目录HashMap是一种基于哈希表实现的键值对存储结构，它通过哈希函数将键映射到数组的索引位置，支持
阅读更多...
Pandas使用AdaBoost进行分类的实现

Pandas使用AdaBoost进行分类的实现

《Pandas使用AdaBoost进行分类的实现》Pandas和AdaBoost分类算法,可以高效地进行数据预处理和分类任务,本文主要介绍了Pandas使用AdaBoost进行分类的实现,具有一定的参... 目录什么是 AdaBoost？使用 AdaBoost 的步骤安装必要的库步骤一：数据准备步骤二：模型
阅读更多...
使用Pandas进行均值填充的实现

使用Pandas进行均值填充的实现

《使用Pandas进行均值填充的实现》缺失数据（NaN值）是一个常见的问题,我们可以通过多种方法来处理缺失数据,其中一种常用的方法是均值填充,本文主要介绍了使用Pandas进行均值填充的实现,感兴趣的... 目录什么是均值填充？为什么选择均值填充？均值填充的步骤实际代码示例总结在数据分析和处理过程中，缺失数
阅读更多...
Go语言开发实现查询IP信息的MCP服务器

Go语言开发实现查询IP信息的MCP服务器

《Go语言开发实现查询IP信息的MCP服务器》随着MCP的快速普及和广泛应用,MCP服务器也层出不穷,本文将详细介绍如何在Go语言中使用go-mcp库来开发一个查询IP信息的MCP... 目录前言mcp-ip-geo 服务器目录结构说明查询 IP 信息功能实现工具实现工具管理查询单个 IP 信息工具的实现服
阅读更多...
Python的time模块一些常用功能(各种与时间相关的函数)

Python的time模块一些常用功能(各种与时间相关的函数)

《Python的time模块一些常用功能(各种与时间相关的函数)》Python的time模块提供了各种与时间相关的函数,包括获取当前时间、处理时间间隔、执行时间测量等,：本文主要介绍Python的... 目录1. 获取当前时间2. 时间格式化3. 延时执行4. 时间戳运算5. 计算代码执行时间6. 转换为指
阅读更多...
Python正则表达式语法及re模块中的常用函数详解

Python正则表达式语法及re模块中的常用函数详解

《Python正则表达式语法及re模块中的常用函数详解》这篇文章主要给大家介绍了关于Python正则表达式语法及re模块中常用函数的相关资料,正则表达式是一种强大的字符串处理工具,可以用于匹配、切分、... 目录概念、作用和步骤语法re模块中的常用函数总结 概念、作用和步骤概念： 本身也是一个字符串，其中
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2