【已解决】N-Gram索引性能优化问题

Viewed 76
2.0

导入2000w数据，进行模糊匹配，查询速度要接近3s，请问是什么原因，是我设置N-Gram索引参数问题？还是我同时给alarm_text设置了倒排索引和N-Gram索引的原因？还是N-Gram索引查询速度就这样慢，是否可以优化查询？

建表语句如下：

CREATE TABLE `alarm_test_ngram` (
        `serialno` bigint(20) NOT NULL,
        `insert_time` datetime NULL,
        `subuuid` varchar(256) NULL,
        `alarm_text` varchar(6000) NULL,
        INDEX idx_serialno (`serialno`) USING INVERTED COMMENT '',
        INDEX idx_insert_time (`insert_time`) USING INVERTED COMMENT '',
        INDEX idx_subuuid (`subuuid`) USING INVERTED COMMENT '',
        INDEX idx_alarm_text (`alarm_text`) USING INVERTED COMMENT '',
        INDEX idx_ngrambf (`alarm_text`) USING NGRAM_BF PROPERTIES("gram_size" = "256", "bf_size" = "512") COMMENT ''
) ENGINE=OLAP
    UNIQUE KEY(`serialno`, `insert_time`)
COMMENT 'OLAP'
PARTITION BY RANGE(`insert_time`)()
DISTRIBUTED BY HASH(`serialno`) BUCKETS AUTO
PROPERTIES (
    --"replication_num" = "1",
    -- 设置写入时合并
    "enable_unique_key_merge_on_write" = "true",

    -- 动态分区
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
);

select *
      from alarm_test_ngram
      where alarm_text like '%110576259%'
[2024-06-20 14:45:12] 1 row retrieved starting from 1 in 2 s 899 ms (execution: 2 s 878 ms, fetching: 21 ms)
caolei45
1 Answers
徐振超@SelectDB6951