SelectDB BloomFilter索引 - ApsaraDB for SelectDB

ApsaraDB for SelectDB的NGram BloomFilter索引，提高了LIKE查詢的執行速度。其效果分析，與BloomFilter索引的效果分析很類似。本文介紹雲資料庫SelectDB版如何建立和使用NGram BloomFilter索引。

注意事項

建立NGram BloomFilter索引的列需滿足以下所有條件。
- 列類型為string或varchar。
- NGram BloomFilter索引與BloomFilter索引為互斥關係，所需目標列上未建立BloomFilter索引。
NGram BloomFilter索引在查詢中生效，查詢需滿足以所有下條件。
- 查詢為LIKE查詢。
- LIKE pattern中的連續字元個數要大於等於索引定義中參數gram_size的值。
  說明
  - 建立索引時未指定gram_size：預設為2。
  - 建立索引時指定了gram_size：通過SHOW INDEXES FROM <table_name>;查看錶建立的索引詳細資料。

建立索引

建表時建立索引

此操作為同步過程，建表成功後索引即完成建立。

文法

CREATE TABLE  [IF NOT EXISTS] [db_name.]<table_name>
(
  <column_definition_list>,
  [<index_definition_list>] 
)
table_properties;

參數說明

建表參數說明

參數	是否必填	描述
db_name	否	目標資料庫名。
table_name	是	目標表名。
column_definition_list	是	列定義列表，更多詳情，請參見CREATE-TABLE。
table_properties	是	表的屬性定義，如資料模型、分區分桶等。更多詳情，請參見資料模型。
index_definition_list	否	索引定義列表。

index_definition_list說明

在建立表時定義索引，可以定義多個索引。其格式為index_definition[, index_definition][, index_definition]...。

index_definition定義

INDEX <index_name>(<column_name>) <index_type> [PROPERTIES("<key>" = "<value>")] [COMMENT '<comment>']

index_definition參數說明

重要

在選填參數PROPERTIES中，如果您未指定gram_size和bf_size參數，系統將自動為您的索引設定這兩個參數，預設值為"gram_size"="2"和"bf_size"="256"。建議您根據自己業務情境，進行手動設定這兩個參數，使得NGram BloomFilter索引達到最佳效果。具體參數設定，請參見下述選填參數的PROPERTIES。

必填參數

參數名稱	參數說明
index_name	索引名稱。建議命名規範：列名前面加首碼`idx_`。重要索引名稱必須表層級唯一。
column_name	建索引的列名稱。重要一個列只能對應一個`NGram BloomFilter`索引或者`BloomFilter`索引。
index_type	索引類型。固定寫為`USING NGRAM_BF`，表示指定索引類型是NGramBloomFilter索引。

選填參數

PROPERTIES

PROPERTIES用來指定索引是否進行分詞。它由一個或多個用“,”分割的"<key>" = "<value>"形式的索引值對組成。

key

value

gram_size

gram_size指的是NGram中的N。

每N個連續字元作為一個詞處理，比如an ngram在N = 3的時候會分成'an'、'n n'、' ng'、'ngr'、'gra'、'ram'6個詞。

說明

N的大小跟實際查詢情境相關，建議取LIKE pattern中字串的最小長度，且不小於2。
如果N比較小，可以適當增加BloomFilter大小，即調整參數bf_size的大小，達到NGram BloomFilter索引調優目的。推薦您設定 "gram_size"="3", "bf_size"="1024"。如何查詢Profile進行調優，請參見查詢Profile。

bf_size

BloomFilter的位元組數。

單位：Bit。

bf_size決定每個資料區塊對應的索引大小。一般情況下，該值越大，過濾效果越佳，同時Hash碰撞的機率也會降低。然而，位元組數的增加也會導致索引儲存和記憶體成本的上升。因此，建議將該參數設定為256進行驗證測試，設定最佳參數值。

COMMENT

參數名稱	參數說明
comment	索引描述。

建表並建立索引樣本

CREATE TABLE `test_table` (
  `siteid` int(11) NULL DEFAULT "10" COMMENT "",
  `citycode` smallint(6) NULL COMMENT "",
  `username` varchar(32) NULL DEFAULT "" COMMENT "",
  `review_body` varchar(320) NULL,
  INDEX idx_ngrambf (`review_body`) USING NGRAM_BF 
  PROPERTIES("gram_size"="3", "bf_size"="256") 
  COMMENT 'review_body ngram_bf index'
) ENGINE=OLAP
AGGREGATE KEY(`siteid`, `citycode`, `username`, `review_body`) COMMENT "OLAP"
DISTRIBUTED BY HASH(`siteid`) BUCKETS 10;

已有表增加索引

此操作為非同步作業，可通過SHOW ALTER TABLE COLUMN;查看索引增加進度。

文法

ALTER TABLE <table_name> ADD INDEX <index_name>(<column_name>) <index_type> [PROPERTIES("<key>" = "<value>")];

參數說明

此處參數與建表時的參數相同。

樣本

ALTER TABLE test_table ADD INDEX idx_ngrambf2(username) using NGRAM_BF PROPERTIES("gram_size"="2", "bf_size"="512")comment 'username ngram_bf index'

查看錶的所有索引

文法

SHOW INDEXES FROM <table_name>;

樣本

查看錶test_table中的所有索引。

SHOW INDEX FROM test_table;

刪除索引

刪除索引是非同步過程，查看刪除進度，請參見查看索引。

重要

刪除索引會導致查詢效能下降，請謹慎操作。

文法

ALTER TABLE <table_name> DROP INDEX <index_name>;

樣本

刪除test_table的索引idx_ngrambf。

ALTER TABLE test_table DROP INDEX idx_ngrambf;

查看索引變更進度

通過ALTER變更索引，是非同步過程，可通過以下語句查看變更進度。

SHOW ALTER TABLE COLUMN;

常見問題

Q：如何查看查詢是否使用了NGram BloomFilter索引？

A：可以通過查詢的Profile資訊查看查詢是否使用了索引。如何查詢Profile，請參見查詢Profile。

Q：通過ALTER語句，給已有表增加索引時報以下錯誤。

ERROR 1105 (HY000): errCode = 2, detailMessage = NGRAM_BF index for columns (review_body ) already exist.

A：NGram BloomFilter索引支援在一張表中建立多個該類型索引，但這些索引的索引列需指定為不同列。如果您添加索引的目標列已經建立了NGram BloomFilter索引或者BloomFilter索引，則會導致新增索引失敗。