创建索引 - 云原生多模数据库 Lindorm

Lindorm提供了两种高效易用的索引：二级索引和搜索索引，分别适用于非主键匹配场景、多维查询场景。在使用CREATE INDEX语法创建索引时，您可以指定索引类型并添加索引属性。

引擎与版本

CREATE INDEX语法仅适用于宽表引擎。无版本限制。

重要

使用CREATE INDEX语法创建搜索索引、列存索引，要求Lindorm SQL为2.6.1以上版本。如何查看Lindorm SQL的版本，请参见SQL版本说明。

语法

create_index_statement   ::=  CREATE INDEX [IF NOT EXISTS] [ index_identifier ]
                              [ USING index_method_definition ]
                              ON table_identifier '(' index_key_expression ')'
                              [ INCLUDE include_expression]
                              [ PARTITION BY partition_definition ]
                              [ { ASYNC | SYNC} ]
                              [ WITH '(' index_options  ')' ]
index_method_definition  ::=  { KV | SEARCH | COLUMNAR }
index_key_expression     ::=  '('
                                  index_key_definition 
                                  |wildcard_string_literal                                 
                              ')'

index_key_definition     ::= {
                                  column_identifier [ DESC ]
                                  | column_identifier'(' column_options ')'
                                  | function_expression
                             }
                             [ (',' index_key_definition)* ]
column_options    ::=  '(' 
                           option_definition (',' option_definition )* 
                       ')'
function_expression      ::=  function_identifier 
                              '(' 
                                  column_identifer ( ',' column_identifer )* 
                              ')'
option_definition        ::=  option_identifer '=' string_literal
include_expression       ::=  '(' 
                                  column_identifier( ',' column_identifier )*
                              ')'
partition_definition     ::= {
                                {RANGE TIME} 
                                     '(' 
                                          column_identifer 
                                     ')' [ PARTITIONS number_literal ]
                                |
                                HASH '(' 
                                          column_identifer 
                                          ( ',' column_identifer )*
                                     ')' [ PARTITIONS number_literal ]
                                |
                                ENUMERABLE 
                                     '(' 
                                          column_identifer 
                                          ( ',' column_identifer )*
index_options            ::=  '(' 
                                  option_definition (',' option_definition )* 
                              ')'

差异说明

Lindorm宽表引擎支持通过CREATE INDEX语句创建两种索引：二级索引和搜索索引。

不同的索引对语法要素的支持情况不同，具体如下：

语法要素	二级索引	搜索索引
索引类型（index_method_definition）	〇	〇
索引键表达式（index_key_expression）	〇	〇
冗余列（include_expression）	〇	✖️
索引分区（partition_definition）	✖️	〇
索引构建方式（ASYNC\|SYNC）重要仅宽表引擎2.6.3及以上版本支持`SYNC`构建方式。	〇	〇
索引属性（index_options）	〇	〇

使用说明

每张宽表最多可创建3个二级索引、1个搜索索引。

索引类型（index_method_definition）

您可以在创建索引时通过USING关键字指定索引类型，Lindorm宽表引擎支持创建以下三种类型的索引：

参数

索引类型

说明

二级索引

当CREATE INDEX语句中未显式指定索引类型时，默认创建二级索引。二级索引的详细介绍，请参见二级索引。

重要

一个实例中仅允许同时存在8个二级索引的构建任务。如果某一时刻已有8个任务在执行，此时再创建二级索引，创建语句将执行失败。

搜索索引

基于搜索引擎的全文搜索索引。主要面向复杂的多维查询场景，能够覆盖分词、模糊查询、聚合分析、排序翻页等场景。详细介绍，请参见搜索索引介绍。

搜索索引支持除DATE、TIME和DECIMAL外的所有基础数据类型。数据类型的说明，请参见基础数据类型。

重要

创建搜索索引前，请先开通搜索索引功能。开通搜索索引将收取搜索节点和LTS节点费用。如何开通，请参见开通搜索索引。
搜索索引的索引键必须包含一个非主键列。
Lindorm实例对搜索索引的构建任务数无限制。

索引键表达式（index_key_expression）

您可以将一个列或多个列定义为索引键，由多个索引键构成的索引也称为联合索引。

索引键定义（index_key_definition）

如果索引类型为搜索索引，支持为索引键添加属性。如果索引类型为二级索引，支持将索引键指定为一个函数表达式。

搜索索引键属性（option_definition）

通过ALTER INDEX语句增加索引列时，也可以指定索引键的属性。例如c3(type=text,analyzer=ik)表示为c3列创建索引，并指定c3列使用ik分词器。

搜索索引中支持的索引键属性如下所示：

索引键属性	类型	描述
indexed	STRING	是否为索引键中的指定列创建倒排索引。 true：默认值，是。 false：不需要搜索索引。
rowStored	STRING	是否存储原始数据。 true：存储原始数据。 false：默认值，不存储原始数据。
columnStored	STRING	是否设置为列存储，用来加速排序分析。 true：默认值，设置列存储。 false：不设置列存储。
type	STRING	分词场景下，分词字段需要设置type参数为text，其他情况默认与宽表数据类型保持一致。重要 type参数必须与analyzer参数搭配使用。
analyzer	STRING	分词器列表。取值： standard english ik whitespace comma 重要 analyzer参数必须与type参数搭配使用。
mapping	STRING	自定义的索引键属性，以字符串形式表示的JSON对象。兼容Elasticsearch索引键属性语法。重要 mapping参数仅适用于搜索引擎Elasticsearch兼容版本。若使用了mapping参数，该索引键的其它所有参数将失效。

二级索引函数表达式（function_expression）

在创建二级索引时，可以将索引键指定为一个函数表达式。目前支持的函数表达式共五种：Z-ORDER函数、S2函数、CAST函数、MD5函数和SHA256函数。
重要
仅宽表引擎2.6.7.5及以上版本，支持在创建索引时将索引键指定为MD5或SHA256函数表达式。如果您的宽表引擎无法通过控制台进行升级，请联系Lindorm技术支持（钉钉号：s0s3eg3）。
- Z-ORDER函数：为一个或多个时空数据类型列创建时空二级索引。语法如下：
```
Z-ORDER '(' column_identifier ( ',' column_identifer )*  ')'
```
  column_identifer的数据类型必须为时空数据类型。关于时空索引的详细介绍，请参见时空索引。
- CAST函数：对一个列类型进行数据类型转换后的结果建立索引。语法如下：
```
CAST(column_identifier AS type)
```
  其中，type为数据类型，详细介绍，请参见基础数据类型。
- S2函数：为一个时空数据类型列创建S2网格二级索引。语法如下：
```
S2 '(' column_identifier, level ')'
```
  column_identifer的数据类型必须为POLYGON或MULTIPOLYGON，level的取值范围为[1,30]。详细介绍，请参见S2索引函数说明。
- MD5函数：为一个VARCHAR类型列的MD5编码值创建索引。语法如下：
```
MD5(column_identifier)
```
  MD5函数的详细介绍，请参见MD5函数。
- SHA256函数：为一个VARCHAR类型列的SHA256编码值创建索引。语法如下：
```
SHA256(column_identifier)
```
  SHA256函数的详细介绍，请参见SHA256函数。

通配符常量（wildcard_string_literal）

仅搜索索引支持通配符常量（*）。

通配符常量（*）用于对所有列构建索引，例如CREATE INDEX IF NOT EXISTS idx5 USING SEARCH ON test(*);。

重要

在执行之后，后续增加的列不会自动添加到搜索索引和列存索引中，您需要通过ALTER INDEX语句自行添加。
动态列不会被包含到索引中。详细介绍，请参见动态列。

冗余列（include_expression）

冗余列是指在索引表中冗余一部分主表的列，也称为冗余索引或覆盖索引，目的是避免查询命中索引后再回查主表，影响查询性能。

说明

二级索引可以通过WITH关键字添加表属性冗余动态列，详细介绍，请参见二级索引。

索引分区（partition_definition）

仅搜索索引支持索引分区。

索引分区是一种索引管理策略，服务端自动拆分并存储数据，查询数据时系统将自动裁剪分区。

搜索索引支持RANGE分区和HASH分区，详细介绍，请参见分区索引。

索引构建方式（ASYNC|SYNC）

在使用CREATE INDEX语句创建索引时，可以通过ASYNC或SYNC关键字指定索引的构建方式。

ASYNC：异步构建索引。CREATE INDEX语句执行后立即开启索引构建任务，无论索引是否构建成功，CREATE INDEX语句立刻返回。
SYNC：同步构建索引。CREATE INDEX语句执行后立即开启索引构建任务，CREATE INDEX语句在索引构建完毕后才返回。

两种索引对构建方式的支持情况如下：

索引构建方式	二级索引	搜索索引
ASYNC 重要自宽表引擎2.6.1版本开始，执行`CREATE INDEX`语句后索引构建方式默认异步构建。	〇	〇
SYNC 重要仅宽表引擎2.6.3及以上版本支持同步构建索引。	〇	〇

索引属性（index_options）

通过CREATE INDEX语句创建索引时，可以通过WITH关键字指定索引属性。支持的索引属性如下所示。

二级索引

属性	类型	说明
COMPRESSION	STRING	索引表的压缩算法，支持的压缩算法包括： SNAPPY ZSTD LZ4
INDEX_COVERED_TYPE	STRING	索引的冗余方式，取值如下： COVERED_ALL_COLUMNS_IN_SCHEMA：冗余表结构中的所有预定义的非主键列。 COVERED_DYNAMIC_COLUMNS：冗余表结构中所有预定义的非主键列和动态列。说明指定INDEX_COVERED_TYPE时无需在语句中指定INCLUDE子句。冗余动态列前必须保证已开启动态列功能，详细介绍，请参见动态列。您也可以通过`INCLUDE`关键字冗余指定的列。
STARTKEY	STRING	索引表的起始Key。重要不支持为时间戳列或类型为空间数据类型的列设置起始Key。
ENDKEY	STRING	索引表的终止Key。重要不支持为时间戳列或类型为空间数据类型的列设置终止Key。
NUMREGIONS	INTEGER	索引表的预分区数。重要不支持为时间戳列或类型为空间数据类型的列设置预分区数。

搜索索引

属性	类型	说明
indexState	STRING	搜索索引状态，取值如下： ACTIVE：索引为可用状态。 INACTIVE：索引不可用。 DISABLED：禁用索引。
numShards	INTEGER	指定分片数，默认是搜索节点个数的两倍。
RANGE_TIME_PARTITION_START	INTEGER	表示创建索引操作前多少天开始创建分区。适用于有历史数据的场景，当历史数据的时间戳早于开始分区的时间时，会导致报错。说明创建分区索引时，必须指定此参数。
RANGE_TIME_PARTITION_INTERVAL	INTEGER	表示间隔多少天创建新分区，例如`RANGE_TIME_PARTITION_INTERVAL='7'`，表示每隔一周创建一个新分区。说明创建分区索引时，必须指定此参数。
RANGE_TIME_PARTITION_TTL	INTEGER	表示保留多少天的分区数据，例如`RANGE_TIME_PARTITION_TTL='180'`，表示保留半年的分区数据，历史分区数据会被自动清理掉。不指定时则表示不会被清理。说明创建分区索引时，必须指定此参数。
RANGE_TIME_PARTITION_MAX_OVERLAP	INTEGER	如果写入的数据时间点是将来的时间，这个参数表示最多允许与当前时刻的时间间隔，单位为天。不指定时默认允许写入未来1天的数据。
RANGE_TIME_PARTITION_FIELD_TIMEUNIT	LONG	表示业务指定的时间分区字段单位，默认单位为毫秒（ms）。分区字段单位设置为秒（s），数字长度为10位。分区字段单位设置为毫秒（ms），数字长度为13位。
RANGE_TIME_PARTITION_CHS	INTEGER	冷热分界线。表示将多久之前的数据转移到冷存储，默认单位为秒（s）。例如`RANGE_TIME_PARTITION_CHS='864000'`，表示将10天之前的数据归档至冷存储。说明不设置该参数表示不开启冷热分离，数据默认只存储在热存储中。
INDEX_SETTINGS	STRING	自定义的索引属性，以字符串形式表示的JSON对象。兼容Elasticsearch索引设置语法。重要 INDEX_SETTINGS参数仅适用于搜索引擎Elasticsearch兼容版本。

示例

假设主表test的建表语句如下：

CREATE TABLE test (
  p1 VARCHAR NOT NULL,
  p2 INTEGER NOT NULL,
  c1 BIGINT,
  c2 DOUBLE,
  c3 VARCHAR,
  c4 TIMESTAMP,
  c5 GEOMETRY(POINT),
  PRIMARY KEY(p1, p2)
) WITH (CONSISTENCY = 'strong', MUTABILITY='MUTABLE_LATEST');

二级索引

异步构建索引

不设置构建方法时，默认异步构建索引。

CREATE INDEX idx1 ON test(c1 desc) include(c3,c4) WITH (COMPRESSION='ZSTD');

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

创建联合索引

为c1，c2，c3列同步创建联合索引。

CREATE INDEX idx1 ON test(c1, c2, c3) include(c4) SYNC WITH ( COMPRESSION ='ZSTD');

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

冗余所有列

CREATE INDEX idx1 ON test(c4 desc) WITH (INDEX_COVERED_TYPE ='COVERED_ALL_COLUMNS_IN_SCHEMA');

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

冗余所有动态列

CREATE INDEX idx1 ON test(c4 desc) WITH (INDEX_COVERED_TYPE='COVERED_DYNAMIC_COLUMNS');

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

设置索引表的预分区数

设置索引表的预分区数为32。

CREATE INDEX idx1 ON test(c4 desc) include(c5,c6)  WITH (NUMREGIONS ='32');

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

指定索引表的起止Key

创建二级索引时指定索引表的起止Key和预分区数，将索引表在11111111和9999999之间分成32个预分区。

CREATE INDEX idx1 ON test(c3 desc) include(c5,c6) WITH (NUMREGIONS ='32', STARTKEY ='11111111', ENDKEY = '9999999');

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

创建Z-ORDER二级索引

为面数据类型列c5创建Z-ORDER二级索引。

CREATE INDEX idx1 ON test(Z-ORDER(c5));

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

创建网格二级索引

为面类型列c5创建网格二级索引。目前仅支持异步创建模式。
```
CREATE INDEX idx1 ON test(S2(c5, 10));
```
构建网格二级索引。
```
BUILD INDEX s2_idx ON test;
```

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

转换指定列的数据类型

将c3列的数据类型转换为INTEGER类型后创建二级索引。

CREATE INDEX idx1 ON test(CAST(c3 AS INTEGER));

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

搜索索引

异步构建索引

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(p1, p2, c1, c2, c3);

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

为所有列创建索引

为所有列创建搜索索引，不指定具体列属性时，均为默认值。

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test('*');

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

添加索引键属性

添加非自定义索引键属性
创建所有列的搜索索引，如果索引列有c3, 索引列的属性都为type=text，analyzer=ik，indexed=true。
```
CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test('*',c3(type=text,analyzer=ik,indexed=true));
```
结果验证
您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。
添加自定义索引键属性
假设表结构如下：
```
CREATE TABLE test (
  p1 VARCHAR NOT NULL,
  p2 INTEGER NOT NULL,
  c1 BIGINT,
  c2 DOUBLE,
  c3 VARCHAR,
  c4 TIMESTAMP,
  PRIMARY KEY(p1, p2)
) WITH (CONSISTENCY = 'strong', MUTABILITY='MUTABLE_LATEST');
```
创建所有列的搜索索引，其中索引列c3指定类型为text，分词器为ik_max_word。
```
CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test('*',c3(mapping='{
  "type": "text",
  "analyzer": "ik_max_word"
}'));
```
重要
- mapping参数仅适用于搜索引擎Elasticsearch兼容版本。
- 若使用了mapping参数，该索引键的其它所有参数将无效。
结果验证
您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

设置索引状态

指定列，并指定部分列属性，同时指定搜索索引状态为ACTIVE。

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(c1, c3(type=text,analyzer=ik)) WITH (indexState=ACTIVE,numShards=4);

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

设置自定义索引属性

创建搜索索引，指定搜索索引状态为ACTIVE，并通过自定义设置指定索引的分片数量为4，压缩方式为ZSTD，刷新时间间隔为10秒。

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(c1, c3(type=text,analyzer=ik)) WITH (indexState=ACTIVE,INDEX_SETTINGS='{
  "index": {
    "codec": "zstd",
    "refresh_interval": "10s",
    "number_of_shards": 4
   }
}');

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。

设置时间分区

按时间列c4分区，从30天前开始，每7天自动分区，默认保留90天的分区数据。

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test (c1, c2, c3, c4)
PARTITION BY RANGE TIME(c4) PARTITIONS 16
WITH (indexState=ACTIVE, RANGE_TIME_PARTITION_START='30', RANGE_TIME_PARTITION_INTERVAL='7', RANGE_TIME_PARTITION_TTL='90', RANGE_TIME_PARTITION_MAX_OVERLAP='90');

结果验证

您可以执行SHOW INDEX FROM test;查看索引是否已创建成功。