CREATE INDEX

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个搜索索引1个列存索引

索引类型(index_method_definition)

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

参数

索引类型

说明

KV

二级索引

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

重要

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

SEARCH

搜索索引

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

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

重要
  • 创建搜索索引前,请先开通搜索索引功能。开通搜索索引将收取搜索节点和LTS节点费用。如何开通,请参见开通搜索索引

  • 搜索索引的索引键必须包含一个非主键列。

  • Lindorm实例对搜索索引的构建任务数无限制。

COLUMAR

列存索引

创建列存索引。可以增强宽表中海量数据的分析计算能力,主要适用于车联网与物联网的设备信息统计、电商领域的数据分析、物流行业的订单统计等场景。详细介绍,请参见列存索引

列存索引支持除DATE、TIME和TIMESTAMP外的所有基础数据类型。数据类型的说明,请参见基础数据类型

重要
  • 创建列存索引前,请先开通列存索引功能。开通列存索引将收取文件引擎和计算引擎费用。如何开通,请参见前提条件

  • 列存索引的索引键必须包含所有主键列。

  • 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)

二级索引搜索索引支持通过INCLUDE关键字冗余动态列以外的列。

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

说明

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

索引分区(partition_definition

搜索索引列存索引支持索引分区。

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

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

列存索引仅支持ENUMERABLE分区,详细介绍,请参见列存索引

索引构建方式(ASYNC|SYNC

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

  • 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兼容版本。

列存索引

属性

类型

说明

lindorm_columnar.user.index.database

STRING

自定义列存表所属Database。

lindorm_columnar.user.index.table

STRING

自定义列存表的表名。

lindorm_columnar.user.syncer.skip.fullsync

BOOLEAN

指示构建索引时是否跳过宽表中的存量数据,只为增量数据构建列存索引。

lindorm_columnar.user.syncer.lci.jsonMapping.json_col

STRING

指定表中需要映射的JSON列。

重要

由于列存索引的属性名均通过分隔符(.)连接,因此为避免语义解释上的偏差,在使用WITH关键字添加表属性时,必须在表属性的前后添加反引号(`),将其作为完整的标识符使用。例如,WITH(`lindorm_columnar.user.syncer.skip.fullsync1` = 'true')

示例

假设主表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和预分区数,将索引表在111111119999999之间分成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;查看索引是否已创建成功。

创建网格二级索引

  1. 为面类型列c5创建网格二级索引。目前仅支持异步创建模式。

    CREATE INDEX idx1 ON test(S2(c5, 10));
  2. 构建网格二级索引。

    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;查看索引是否已创建成功。

列存索引

创建索引

CREATE INDEX IF NOT EXISTS idx3 USING COLUMNAR
ON test(p1, p2, c1, c2, c3)
PARTITION BY ENUMERABLE (p1, p2, bucket(128, p1, p2));

结果验证

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

自定义列存表名

创建列存索引,将索引表命名为my_index_tbl

CREATE INDEX IF NOT EXISTS idx3 USING COLUMNAR ON test(*)
PARTITION BY ENUMERABLE (p1, p2, bucket(128, p1, p2))
WITH (`lindorm_columnar.user.index.database` = 'my_index_db',
`lindorm_columnar.user.index.table` = 'my_index_tbl');

结果验证

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

创建增量列存索引

创建一个仅为增量数据构建列存索引的索引。

CREATE INDEX IF NOT EXISTS idx3 USING COLUMNAR ON test(*)
PARTITION BY ENUMERABLE (p1, p2, bucket(128, p1, p2))
WITH (`lindorm_columnar.user.syncer.skip.fullsync` = 'true');

结果验证

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

为JSON列展开后的字段创建索引

针对JSON列展开后的字段创建列存索引。假设包含JSON列的表建表语句如下:

CREATE TABLE IF NOT EXISTS my_json_tbl(id BIGINT, col1 INTEGER, col2 DOUBLE, json_col JSON, PRIMARY KEY(id));

写入JSON数据的结构如下:

{
 "a": {
 "b": {
 "c": "hello,world",
 "d": 123
 },
 "e": false
 },
 "f": 3.14
}

创建列存索引时,基于WITH关键字,指定展开JSON字段的映射规则。

CREATE INDEX IF NOT EXISTS columnar_idx USING COLUMNAR ON my_json_tbl(*) 
 PARTITION BY ENUMERABLE (id%16, bucket(16,id)) 
 WITH (
 `lindorm_columnar.user.syncer.lci.jsonMapping.json_col` = 'a.b.c VARCHAR, a.e BOOLEAN ,f DOUBLE');

结果验证

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