优化索引设计和使用

问题场景

建表时Key列顺序为(event_time, user_id)，但业务高频查询按user_id过滤。由于user_id不是Key列前缀，查询无法命中前缀索引，导致全表扫描：

-- 建表：Key列顺序为 (event_time, user_id)
CREATE TABLE user_events (
    event_time DATETIME NOT NULL,
    user_id BIGINT NOT NULL,
    event_type VARCHAR(32),
    device_id VARCHAR(64),
    page_url TEXT
)
DUPLICATE KEY(event_time, user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO;

-- 高频查询：按 user_id 过滤，无法命中前缀索引
SELECT * FROM user_events WHERE user_id = 12345;

优化方案

方案A：调整Key列顺序（建议新表使用）

将高频过滤列放在Key定义的首位：

-- 推荐：将高频过滤列 user_id 放在 Key 首位
CREATE TABLE user_events (
    user_id BIGINT NOT NULL,
    event_time DATETIME NOT NULL,
    event_type VARCHAR(32),
    device_id VARCHAR(64),
    page_url TEXT
)
DUPLICATE KEY(user_id, event_time)
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO;

方案B：创建Rollup（已有表使用）

对已有表无法修改Key列顺序时，通过Rollup构建以目标列为前缀的索引视图：

-- 为已有表创建Rollup，以 event_type 为前缀
ALTER TABLE user_events ADD ROLLUP rollup_event_type(event_type, event_time, user_id, device_id, page_url);

Rollup创建完成后，查询按event_type过滤时优化器会自动选择该Rollup。

前缀索引设计原则

• 高频过滤列在前：将查询中最常出现在WHERE条件中的列放在Key列的前面位置。

• 定长类型在前：前缀索引仅取前36字节，VARCHAR类型会占用较多索引字节。将INT、BIGINT等定长类型放前面可覆盖更多列。

• 等值查询列在范围查询列之前：范围条件会终止前缀匹配，等值列放前面能保证后续列继续参与索引过滤。

场景A：全文检索

对文本列创建带分词器的倒排索引，使用MATCH_ALL（所有词都匹配）或MATCH_ANY（任意词匹配）进行全文检索：

-- 建表时内联定义倒排索引（中文分词）
CREATE TABLE articles (
    id BIGINT NOT NULL,
    title VARCHAR(256),
    content TEXT,
    publish_time DATETIME,
    INDEX idx_content(content) USING INVERTED PROPERTIES("parser" = "chinese")
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS AUTO;

-- 全文检索：查找同时包含"SelectDB"和"索引"的文章
SELECT * FROM articles WHERE content MATCH_ALL 'SelectDB 索引';

-- 全文检索：查找包含"优化"或"导入"的文章
SELECT * FROM articles WHERE content MATCH_ANY '优化 导入';

分词器选择：

• 中文文本：parser = "chinese"

• 英文文本：parser = "unicode"（支持大小写归一化，配合"lower_case" = "true"）

• 不需要分词的等值匹配：不指定parser（默认不分词）

场景B：多条件组合过滤

对多列分别建立倒排索引，查询时优化器自动组合各列的索引结果进行过滤：

-- 对已有表的多个列各创建倒排索引
CREATE INDEX idx_title ON articles(title) USING INVERTED;
CREATE INDEX idx_publish_time ON articles(publish_time) USING INVERTED;

-- 多条件组合查询：倒排索引自动加速
SELECT * FROM articles
WHERE title = 'SelectDB最佳实践'
  AND publish_time >= '2024-01-01';

建表时内联定义

除了通过CREATE INDEX在已有表上追加，也可以在建表时直接内联定义多个倒排索引：

CREATE TABLE articles (
    id BIGINT NOT NULL,
    title VARCHAR(256),
    content TEXT,
    publish_time DATETIME,
    INDEX idx_title(title) USING INVERTED,
    INDEX idx_content(content) USING INVERTED PROPERTIES("parser" = "chinese"),
    INDEX idx_pub_time(publish_time) USING INVERTED
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS AUTO;

创建方式

建表时指定：

CREATE TABLE user_events (
    event_time DATETIME NOT NULL,
    user_id BIGINT NOT NULL,
    event_type VARCHAR(32),
    device_id VARCHAR(64),
    page_url TEXT
)
DUPLICATE KEY(event_time, user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO
PROPERTIES (
    "bloom_filter_columns" = "device_id"
);

已有表追加：

-- 追加 Bloom Filter 列（需列出所有要保留的列）
ALTER TABLE user_events SET ("bloom_filter_columns" = "device_id, page_url");

适用场景

-- 按设备ID精确查询（device_id 基数高，Bloom Filter 有效）
SELECT * FROM user_events WHERE device_id = 'device_abc_123';

-- IN 查询同样有效
SELECT * FROM user_events WHERE device_id IN ('device_001', 'device_002', 'device_003');

反例：低基数列

对取值种类少的列（如性别、状态字段）创建Bloom Filter索引，由于大量数据块都包含目标值，过滤器几乎无法排除任何块，索引失去作用：

-- 反例：event_type 取值仅有 click/view/purchase 三种，Bloom Filter 无法有效过滤
PROPERTIES ("bloom_filter_columns" = "event_type")  -- 不推荐

使用限制

• 仅对等值查询（=、IN）有效，不加速范围查询（>、<、BETWEEN）。

• 不支持TINYINT、FLOAT、DOUBLE类型的列。

• 同一列不能同时存在Bloom Filter索引和NGram BF索引，二者互斥。

创建方式

-- 对 title 列创建 NGram BF 索引
CREATE INDEX idx_title_ngram ON articles(title) USING NGRAM_BF PROPERTIES("gram_size" = "3", "bf_size" = "1024");

创建后，以下查询可利用该索引减少扫描量：

-- LIKE 模糊匹配，NGram BF 索引自动加速
SELECT * FROM articles WHERE title LIKE '%最佳实践%';

gram_size选择原则

gram_size决定拆分片段的长度，设置为搜索关键词的最小字符数效果最佳：

• 关键词通常为2～3个字（如中文产品名）：设置gram_size = "2"或"3"。

• 关键词较长（如英文单词、URL片段）：设置gram_size = "4"或"5"。

• gram_size越小，索引越大、误判率越高；越大，对短关键词无法生效。建议与实际查询中最短的关键词长度匹配。

与倒排索引的选择对比

使用限制

• 同一列不能同时存在Bloom Filter索引和NGram BF索引，二者互斥。

• 仅支持字符串类型列（CHAR、VARCHAR、STRING）。

查看EXPLAIN输出

EXPLAIN SELECT * FROM user_events WHERE device_id = 'device_abc_123';

-- 在输出的 OlapScanNode 中关注以下字段：
-- PARTITIONS: 1/1          -- 分区裁剪结果
-- TABLETS: 10/10           -- 扫描的 tablet 数量
-- tabletList=...           -- 涉及的具体 tablet

使用EXPLAIN VERBOSE可查看更详细信息，包括索引过滤条件的下推情况。

查看已创建的索引

-- 查看表上的所有索引
SHOW INDEX FROM user_events;

-- 查看表的Bloom Filter配置
SHOW CREATE TABLE user_events;

已有表如何追加索引？

Bloom Filter通过ALTER TABLE追加：

ALTER TABLE table_name SET ("bloom_filter_columns" = "col1, col2");

倒排索引和NGram BF索引通过CREATE INDEX追加：

CREATE INDEX idx_name ON table_name(column_name) USING INVERTED;
CREATE INDEX idx_name ON table_name(column_name) USING NGRAM_BF PROPERTIES("gram_size" = "3", "bf_size" = "1024");

索引创建为异步操作（Schema Change），通过SHOW ALTER TABLE COLUMN查看进度。同一张表不能同时执行多个Schema Change操作。

可以在同一列上叠加多种索引吗？

Bloom Filter索引和NGram BF索引互斥，同一列只能有其中一种。倒排索引可以与Bloom Filter或NGram BF共存。前缀索引和ZoneMap索引自动创建，不受影响。

索引会影响写入性能吗？

索引会增加写入时的计算和存储开销。Bloom Filter和NGram BF的额外开销较小。倒排索引（特别是带分词器的）开销相对较大，但对大多数场景仍可接受。建议仅对实际查询需要的列创建索引，避免盲目为所有列添加索引。

如何删除不需要的索引？

倒排索引和NGram BF索引通过DROP INDEX删除：

DROP INDEX idx_name ON table_name;

Bloom Filter索引通过清空列列表删除：

ALTER TABLE table_name SET ("bloom_filter_columns" = "");