解析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_name
    ON 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语句在建表时创建函