CREATE INDEX

描述

该语句用来创建索引。索引是创建在表上的，对数据库表中一列或多列的值进行排序的一种结构。其作用主要在于提高查询的速度，降低数据库系统的性能开销。

OceanBase 数据库当前版本支持的索引类型包含唯一索引和非唯一索引，创建索引时可以通过 STORING(column_name,...) 指定索引表上的冗余列。索引表的冗余列除了包含用户指定添加的列外，还会自动添加上主键表的主键列（如果无主键表则包含 Hidden PK），存储额外的列在索引中可以为数据库优化器提供更多的选择。

使用限制及注意事项

如果删除表中的所有索引列，则所创建的索引失效。
全文索引仅支持应用于 CHAR、VARCHAR 和 TEXT 类型的列。
搜索索引支持应用于 JSON、单级数组以及多种标量类型列，完整列表请参见下文搜索索引支持的数据类型。
当前版本只支持创建局部（LOCAL）全文或搜索索引。
创建全文索引时不可以指定 UNIQUE 关键字。
如果要创建涉及多列的全文索引，则必须确保这些列具有相同的字符集。
只有在创建全文索引时，才可以使用 WITH PARSER {SPACE | NGRAM | BENG | IK} 指定分词器。

权限要求

执行 CREATE INDEX 语句创建索引需要当前用户至少拥有对应对象的 INDEX 权限。有关 OceanBase 数据库权限的详细介绍，请参见 MySQL 模式下的权限分类。

语法

通用索引语法

CREATE [hint_options] [index_type] INDEX [IF NOT EXISTS] index_name
    [USING BTREE] ON table_name (sort_column_key [, sort_column_key... ])
    [WITH PARSER tokenizer_option]
    [PARSER_PROPERTIES[=](parser_properties_list)]
    [FTS_INDEX_TYPE [=] {MATCH | PHRASE_MATCH}]
    [index_option...] [partition_option] [index_column_group_option]

index_type:
    SPATIAL
    | UNIQUE
    | FULLTEXT
    | SEARCH -- 具体语法请参见下文搜索索引（Search Index）语法

sort_column_key:
      column_name [(integer)] [ASC] [ID id]
    | (index_expr) [ASC] [ID id]

tokenizer_option:
    SPACE
    | NGRAM
    | BENG
    | IK
    | NGRAM2

parser_properties_list:
    parser_properties, [parser_properties]

parser_properties:
    min_token_size = int_value
    | max_token_size = int_value
    | ngram_token_size = int_value
    | ik_mode = 'char_value'
    | min_ngram_size = int_value
    | max_ngram_size = int_value

index_option:
      GLOBAL
    | LOCAL
    | BLOCK_SIZE integer
    | COMMENT STRING_VALUE
    | STORING (column_name [, column_name...])
    | WITH_ROWID
    | WITH PARSER STRING_VALUE
    | index_using_algorithm
    | visibility_option
    | DATA_TABLE_ID data_table_id
    | INDEX_TABLE_ID index_table_id
    | VIRTUAL_COLUMN_ID virtual_column_id
    | MAX_USED_PART_ID used_part_id
    | STORAGE_CACHE_POLICY(storage_cache_policy_option)

index_column_group_option：
    WITH COLUMN GROUP([all columns, ]each column)

storage_cache_policy_option:
    GLOBAL = {"hot" | "auto" | "cold" | "none"}
    | timeline_strategy_list

timeline_strategy_list:
    BOUNDARY_COLUMN = column_name
    | BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
    | HOT_RETENTION = intnum retention_time_unit
    | MIXED_RETENTION = intnum retention_time_unit
    | GRANULARITY = {"partition" | "block"}

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

搜索索引（Search Index）语法

当 index_type 指定为 SEARCH 时，可使用以下语法创建搜索索引：

CREATE SEARCH INDEX index_name ON table_name (
    search_index_column
    [, search_index_column ...]
) [LOCAL];

search_index_column:
    column_name [WITH (search_col_option_list)]

search_col_option_list:
    [INCLUDE_PATHS = ('path1'[, 'path2']...)]
    [| EXCLUDE_PATHS = ('path1'[, 'path2']...)]
    [| INCLUDE_TYPES = (type1[, type2]...)]

除 CREATE INDEX 外，也支持通过 CREATE TABLE 或 ALTER TABLE 语句创建搜索索引。

上述两种方式中，search_index_column 与 WITH 配置语义与 CREATE SEARCH INDEX 一致。详细语法请参见 CREATE TABLE 和 ALTER TABLE。

参数说明

通用索引参数说明

参数项	描述
hint_options	指定 Hint 选项，为可选项。目前仅支持使用 parallel，如 `/+ parallel(10) /`。
index_type	可选项，指定索引的类型。类型信息介绍可参见下文 index_type。
IF NOT EXISTS	用于指示如果索引已经存在，则不进行创建。创建索引时，如果索引存在且没有指定 `IF NOT EXISTS`，则会报错。
index_name	指定待创建的索引名称。
USING BTREE	可选项，表示创建索引时使用 B-tree 索引结构。说明目前仅支持指定为 `USING BTREE`。
table_name	指定要在其上创建索引的表，可以直接指定表的名字，也可使用 `schema_name.table_name` 格式指定表名和表所属的数据库名。
sort_column_key	指定一个排序列的键，创建索引时可以指定多个排序列，多个排序列使用英文逗号（`,`）分开，详细介绍可参见下文 sort_column_key。
WITH PARSER tokenizer_option	可选项，指定全文索引的分词器。详细介绍可参见下文 tokenizer_option。
PARSER_PROPERTIES[=](parser_properties_list)	可选项，指定分词器的属性。详细介绍可参见下文 parser_properties。
FTS_INDEX_TYPE [=] {MATCH \| PHRASE_MATCH}	可选项，用于设置全文索引的类型。`MATCH` 表示词项匹配索引，支持基于 bm25 算法的匹配和打分，存储内容包含词元（token）、文档ID、词频、文档长度等信息。`PHRASE_MATCH` 表示短语匹配索引，能更高效地支持短语查询，是 `MATCH` 的功能超集，同时还会存储词的位置列表。仅在与 `FULLTEXT` 索引一起使用时生效。
index_option	指定索引选项，创建索引时可以指定多个索引选项，多个索引选项使用英文空格分开，详细介绍可参见下文 index_option。
partition_option	指定创建索引分区的选项，支持指定哈希分区、键分区、范围分区、列表分区以及外部表分区。
index_column_group_option	指定索引选项。详细介绍可参见下文 index_column_group_option。
STORAGE_CACHE_POLICY	可选项，表示共享存储模式下，创建索引时指定索引热点缓存策略。详细介绍请参见下文 storage_cache_policy 。
LOCAL	可选项，表示创建局部索引。

搜索索引参数说明

参数项	描述	取值范围
search_index_column	搜索索引列定义，可指定一个或多个列；每列可通过 `WITH (...)` 指定列级路径与类型选项。
INCLUDE_PATHS / EXCLUDE_PATHS	列级路径白名单/黑名单，二者二选一，用于控制该列参与索引构建的 JSON 路径。默认全路径参与索引构建。路径支持两种配置：简单的确定性路径。比如 `$.key`、`$.a`，这个时候只对 `$.key` 和 `$.a` 下的标量和数组数据生成索引，`$.a.b` 等则不生成索引。前缀匹配路径。比如 `$.a.`，这时对 `$.a` 下的 JSON 数据生成索引，比如 `$.a.b` 等都会有索引。`$.` 表示匹配所有路径。
INCLUDE_TYPES	限制参与索引构建的数据类型集合（类型值以实际版本支持为准）。例如 `INCLUDE_TYPES = (JSON_STRING)` 表示只构建 JSON_STRING 类型的路径索引。默认所有类型都参与索引构建。	JSON_STRING, JSON_NUMBER

搜索索引支持的数据类型

搜索索引可以建立在以下数据类型的列上：

类别	支持的类型
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 及其 UNSIGNED 变体
浮点/定点类型	FLOAT、DOUBLE、DECIMAL、NUMBER
字符串类型	VARCHAR、CHAR
文本类型	TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
二进制类型	BINARY、VARBINARY
二进制大对象	TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
日期时间类型	DATE、TIME、DATETIME、TIMESTAMP、YEAR
JSON 类型	JSON
数组类型	ARRAY(element_type) — 仅支持单层数组（如 ARRAY(INT)、ARRAY(VARCHAR(256))）

不支持的类型：BIT、ENUM、SET 类型不支持创建搜索索引。

index_type

SPATIAL：该关键字用于指定创建空间索引。
UNIQUE 该关键字用于指定创建唯一索引。
FULLTEXT：该关键字用于指定创建全文索引。
SEARCH：该关键字用于指定创建搜索索引（Search Index）。

有关创建不同种类索引的介绍，参见创建索引。

sort_column_key

column_name [(integer)] [ASC] [ID id]：表示指定一个列名作为排序键。
- column_name：表示要排序的列名。
- integer：可选项，表示排序键的长度限制。
- ASC：可选项，表示按升序排序，目前暂不支持降序（DESC）排列。
- ID id：可选项，表示排序键的 ID 号。
例如，在名为 t3 的表上创建一个名为 index3 的索引，并指定该索引在 c1 列上按升序进行排序。
```
CREATE INDEX index3 ON t3 (c1 ASC);
```
(index_expr) [ASC] [ID id]：表示指定一个索引表达式作为排序键。索引表达式可以使用表达式或函数来定义。它可以包含以下选项：
- (index_expr)：表示索引表达式，且允许是布尔表达式，例如 c1=c1。OceanBase 数据库当前版本禁止创建生成列上的函数索引。函数索引所支持的表达式参见函数索引支持的系统函数列表。
- ASC：可选项，表示按升序排序，目前暂不支持降序（DESC）排列。
- ID id：可选项，表示排序键的 ID 号。
例如，在名为 t4 的表上创建一个名为 index4 的索引，指定 c1+c2 作为索引表达式，并按升序进行排序。
```
CREATE INDEX index4 ON t4 ((c1 + c2) ASC);
```

创建索引时可指定多个排序列，多个排序列使用英文逗号(,)分开。例如，在名为 t5 的表上创建一个名为 index5 的索引，并指定使用 c1 列和 c2+c3 表达式作为索引的排序键。

CREATE INDEX index5 ON t5 (c1, (c2+c3));

tokenizer_option

SPACE：默认值，表示按空格进行分词。可以指定以下属性：

属性取值范围

min_token_size [1, 16]

max_token_size [10, 84]
NGRAM：表示基于 N-Gram（中文）的分词方式。可以指定以下属性：

属性取值范围

ngram_token_size [1, 10]
NGRAM2：表示将文本拆成 min_ngram_size～max_ngram_size 范围的连续字符。可以指定以下属性：

属性取值范围

min_ngram_size [1, 16]

max_ngram_size [1, 16]
BENG：基于 Beng （基础英文）的分词方式。可以指定以下属性：

属性取值范围

min_token_size [1, 16]

max_token_size [10, 84]
IK：表示基于 IK（中文）的分词方式。当前仅支持 utf-8 字符集。可以指定以下属性：
属性取值范围
ik_mode

smart

max_word

属性	取值范围
min_token_size	[1, 16]
max_token_size	[10, 84]

属性	取值范围
ngram_token_size	[1, 10]

属性	取值范围
min_ngram_size	[1, 16]
max_ngram_size	[1, 16]

属性	取值范围
min_token_size	[1, 16]
max_token_size	[10, 84]

属性	取值范围
ik_mode	`smart` `max_word`

可以使用 TOKENIZE 函数查看文本按照指定的分词器及 Json 形式参数的分词结果。

parser_properties

min_token_size：表示最小分词长度，默认值为 3，取值范围是 1 到 16。
max_token_size：表示最大分词长度，默认值为 84，取值范围是 10 到 84。
ngram_token_size：表示 NGRAM 的分词长度，只有 NGRAM 分词器有效，默认值为 2，取值范围是 1 到 10。
ik_mode: 表示 IK 分词器的分词模式。取值如下：
- smart：默认值，表示词典中的词汇会被用来提高分词的准确性，词典中的词汇边界会被优先考虑，从而可能减少不必要的扩展。
- max_word：表示在词典中定义的词汇会被识别出来，但不会影响分词的最大化扩展。即使词典中有定义，max_word 模式仍然会尝试将文本切分成更多的词汇。

示例如下：

创建表 tbl1。

CREATE TABLE tbl1 (col1 VARCHAR(200), col2 TEXT);

为表 tbl1 的列 col2 创建一个全文索引，并指定使用 IK 分词器，同时通过 PARSER_PROPERTIES 设置分词器属性。
```
CREATE FULLTEXT INDEX fidx_tbl1 ON tbl1(col2) WITH PARSER IK PARSER_PROPERTIES=(ik_mode='max_word');
```

index_option

GLOBAL：表示创建全局索引。
LOCAL：表示创建局部索引。
BLOCK_SIZE integer：指定索引块的大小，即每个索引块中的字节数。
COMMENT STRING_VALUE：为索引添加注释。
STORING (column_name [, column_name...])：指定要存储在索引中的列，多个列使用英文逗号（,）分开。
WITH_ROWID：创建包含行标识符的索引。
WITH PARSER STRING_VALUE：指定索引所需的解析器。
index_using_algorithm：指定索引所使用的算法。可指定 USING BTREE 或 USING HASH。但不能用 USING HASH 创建多值索引。
visibility_option：指定索引的可见性。
DATA_TABLE_ID data_table_id：指定索引所属的数据表 ID。
INDEX_TABLE_ID index_table_id：指定索引表的 ID。
VIRTUAL_COLUMN_ID virtual_column_id：指定虚拟列的 ID。
MAX_USED_PART_ID used_part_id：指定索引的最大使用分区 ID。

index_column_group_option

WITH COLUMN GROUP(all columns, each column)：指定创建行存列存冗余的索引。
WITH COLUMN GROUP(each column)：指定创建列存索引。

storage_cache_policy

注意

只有在共享存储模式下，才可以在创建索引时指定冷热存储策略（STORAGE_CACHE_POLICY）。

支持对索引表配置冷热存储策略：

对于局部索引，可以指定表级 STORAGE_CACHE_POLICY。如果未指定表级 STORAGE_CACHE_POLICY，则默认为 none，表示使用数据表的 STORAGE_CACHE_POLICY 配置，局部索引分区的冷热存储策略则跟随数据表的分区规则。
对于全局索引索引，除了指定表级的 STORAGE_CACHE_POLICY，如果索引表有分区的话，还可以指定索引表分区级的 STORAGE_CACHE_POLICY。全局索引如果未指定表级 STORAGE_CACHE_POLICY，则默认为 none。

storage_cache_policy_option 中不同属性以 Key-Value 格式定义。各个属性的语意如下：

GLOBAL = {"hot" | "auto" | "cold" | "none"}：用于指定索引的数据的冷热存储策略，取值如下：
- hot：表示指定索引为热存储，在缓存盘空间足够的情况下，索引表下所有数据将会缓存到本地云盘。
- auto：表示指定索引表热数据为系统自动识别。
- cold：表示指定索引表数据为冷数据，不会缓存到本地缓存盘，对于已经持久化到缓存的数据会主动触发缓存淘汰，后续访问的表数据可以存储在宏块内存缓存，不会持久化到宏块缓存和微块缓存。
- none：默认值，表示该索引的策略跟随主表 STORAGE_CACHE_POLICY 取值。
timeline_strategy_list：表示时间轴策略参数列表，各参数间使用英文逗号分隔。时间轴策略支持通过时间来判断分区/宏块数据冷热存储的机制，系统根据配置的策略，自动调整本地缓存盘上的数据。
- BOUNDARY_COLUMN = column_name：用于判定热数据的列。支持整型（BIGINT 或者 INT 类型，格式为 Unix 时间戳）和时间类型（TIMESTAMP、DATE、DATETIME 或者 YEAR 类型）。如果 BOUNDARY_COLUMN 为整型，表的分区方式支持 Range 或 Range Columns 分区类型；如果 BOUNDARY_COLUMN 类型为时间类型时，分区方式只能使用 Range Columns。
- BOUNDARY_COLUMN_UNIT = {"s" | "us" | "ms"}：用于指定参数 BOUNDARY_COLUMN 的时间单位，仅支持 BOUNDARY_COLUMN 为整型时设置此参数，取值可以为：
  - 当分区列为 INT 类型时，BOUNDARY_COLUMN_UNIT 取值只能为 s。
  - 当分区列为 BIGINT 类型时，BOUNDARY_COLUMN_UNIT 取值可以是 s、ms。
- HOT_RETENTION = intnum retention_time_unit：用于配置热数据的时间范围，可以按 MINUTE/ HOUR / DAY / WEEK / MONTH / YEAR 来配置。
- MIXED_RETENTION = intnum retention_time_unit：
  - 如果设置 MIXED_RETENTION，则表示距离 HOT_RETENTION + MIXED_RETENTION 的时间范围后的数据都是 cold 数据。
  - 如果不设置 MIXED_RETENTION，则到达 HOT_RETENTION 之后，就一直为 auto 数据。
  - 如果没有设置 HOT_RETENTION, 则表示 [0, MIXED_RETENTION] 的时间范围为 auto 数据，MIXED_RETENTION 之后的为 cold 数据。
- GRANULARITY = {"partition" | "block"}：可选项，用于指定数据冷热存储的操作粒度，取值如下：
  - partition：表示缓存数据的策略按照分区粒度处理。
  - block：表示缓存数据的策略按照宏块粒度处理。
    
    说明
    
    在没有分区表的情况下，需要手动指定一段时间内数据为热点数据时，可以指定 block。详细使用宏块冷热策略的介绍，参见创建宏块级冷热存储策略。

示例

示例一

使用以下 SQL 语句为表创建列存索引。

使用下面 SQL 语句创建表 test_tbl1。

CREATE TABLE test_tbl1 (col1 INT, col2 VARCHAR(50));

在 test_tbl1 表上创建列存索引 idx1_test_tbl1，引用 col1 列。

CREATE INDEX idx1_test_tbl1 ON test_tbl1 (col1) WITH COLUMN GROUP(each column);

示例二

在共享存储模式下，使用以下 SQL 语句创建索引表的冷热存储策略。

创建索引时，指定索引表 idx1_tbl1 的数据为热数据。

CREATE INDEX idx1_tbl1 ON tbl1 (c1) LOCAL 
  STORAGE_CACHE_POLICY (GLOBAL = "hot");

创建表 tbl2，配置 tbl2 表上的索引表 idx2 的数据为热数据。

CREATE TABLE tbl2 (col1 INT, col2 INT,
  INDEX idx2 ((col1 + 1)) STORAGE_CACHE_POLICY (GLOBAL = "hot"), UNIQUE KEY ((col1 + col2)));

创建全局索引时配置全局索引分区的缓存策略，以及配置时间轴策略。

CREATE INDEX idx_global ON tbl1 (c1) GLOBAL
  PARTITION BY RANGE (c1)
  (PARTITION p1 VALUES LESS THAN (100),
   PARTITION p2 VALUES LESS THAN (200),
   PARTITION p3 VALUES LESS THAN MAXVALUE);

示例三

创建搜索索引（Search Index）的示例如下。

创建表 tbl。

CREATE TABLE tbl (col1 JSON, col2 DECIMAL(10,2), col3 JSON, col4 JSON, col5 JSON, col6 varchar(512));

创建搜索索引 search_idx。

CREATE SEARCH INDEX search_idx ON tbl(
  -- path 需更换为实际路径
  col1 WITH (INCLUDE_PATHS = ('$.path3', '$.path4')),
  col2 WITH (EXCLUDE_PATHS = ('$.path1', '$.path3')),
  col3 WITH (INCLUDE_TYPES = (JSON_STRING)),
  col4
);

描述