SelectDB BloomFilter索引_云数据库 SelectDB 版(SelectDB)-阿里云帮助中心

云数据库 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索引，则会导致新增索引失败。