ClickHouse中处理10k量级匹配数组，替代multiSearchAnyCaseInsensitive的方法

我有一个约10k条词汇的数组，需要检查数据表source中text列的内容是否包含该数组中的任意词汇。最初使用multiSearchAnyCaseInsensitive函数，对应的SQL语句如下：

WITH (select groupArray(word) from bad_words) AS patterns
SELECT
  s.id AS id,
  multiSearchAnyCaseInsensitive(s.text, patterns) AS has_bad_words 
FROM source s
GROUP BY id;

但该函数对数组元素数量存在限制，执行时出现错误：

Number of arguments for function multiSearchAnyCaseInsensitive doesn't match: passed 10002, should be at most 255

补充说明：无法按空格拆分文本，因为部分匹配词是多词组合。

可行方案 #

方案1：arrayExists + positionCaseInsensitive #

遍历词汇数组，逐个检查文本中是否存在匹配词，只要有一个匹配就返回true，无词汇数量限制：

WITH (select groupArray(word) from bad_words) AS patterns
SELECT
  s.id AS id,
  arrayExists(word -> positionCaseInsensitive(s.text, word) > 0, patterns) AS has_bad_words
FROM source s
GROUP BY id;

注意：词汇量极大时可能有性能损耗，可根据数据规模调整。

方案2：正则表达式拼接 + match #

将所有词汇拼接成不区分大小写的正则表达式（需转义特殊字符），用match函数一次性匹配：

WITH (
  select replaceRegexpAll(groupConcat(word, '|'), '([.+*?^$(){}[]\\])', '\\$1') AS pattern 
  from bad_words
)
SELECT
  s.id AS id,
  match(s.text, '(?i)' || pattern) AS has_bad_words
FROM source s
GROUP BY id;

说明：必须转义词汇中的正则特殊字符（如.、*），避免匹配逻辑出错；若词汇过长导致正则表达式超限，建议换用其他方案。

方案3：关联查询 + 聚合判断 #

通过LEFT JOIN关联source和bad_words表，检查匹配后聚合结果：

SELECT
  s.id AS id,
  max(positionCaseInsensitive(s.text, bw.word) > 0) AS has_bad_words
FROM source s
LEFT JOIN bad_words bw ON positionCaseInsensitive(s.text, bw.word) > 0
GROUP BY s.id;

优势：利用ClickHouse的join优化，性能优于数组遍历，适合大数据量场景。

方案4：Ngram索引优化（高频查询场景） #

预先为文本字段创建ngram索引，提升模糊匹配性能：

1. 创建带ngram索引的表：

CREATE TABLE source_ngram
(
  id UInt64,
  text String,
  INDEX text_ngram text TYPE ngram(3) GRANULARITY 1
)
ENGINE = MergeTree()
ORDER BY id;

2. 插入数据后执行查询：

SELECT
  s.id AS id,
  EXISTS (
    SELECT 1 FROM bad_words bw WHERE positionCaseInsensitive(s.text, bw.word) > 0
  ) AS has_bad_words
FROM source_ngram s
GROUP BY id;

适合需要频繁执行此类匹配查询的场景，索引能大幅缩短查询时间。

内容的提问来源于stack exchange，提问作者Егор Лебедев