建立索引 - 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支援人員（DingTalk號：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;查看索引是否已建立成功。