大表高效筛选：排除与小表短语匹配的SQL实现方案

问题说明 #

我有一张含1亿条记录的keywords大表，数据示例：

('water'),
('mineral water'),
('water bottle'),
('big bottle of water'),
('coke'),
('pepsi')

另有一张不足100条记录的negatives小表，需筛选出keywords中与negatives任意记录无短语匹配的记录，最终仅需输出coke、pepsi。

短语匹配规则 #

• 关键词完全等于water/wine/glass
• 关键词以water/wine/glass开头
• 关键词以water/wine/glass结尾
• 关键词中间（两个空格之间）包含water/wine/glass（注：waterize这类拼接词无需排除）

现有伪SQL（性能不足） #

原SQL使用多个LIKE通配符匹配，对亿级表会触发全表扫描，性能极差：

CREATE TABLE keywords (
  query TEXT
);
CREATE TABLE negatives (
  text TEXT
);

INSERT INTO keywords
  (query)
VALUES
  ('water'),
  ('mineral water'),
  ('water bottle'),
  ('big bottle of water'),
  ('coke'),
  ('pepsi');
  
INSERT INTO negatives (text) VALUES ('water', 'glass', 'wine');
  
SELECT *
FROM keywords 
WHERE NOT (
   query ~~ ('% ' || 'water' || ' %') OR 
   query ~~ ( 'water' || ' %') OR 
   query ~~ ('% ' || 'water') OR 
   query ~~ ('water')
 )

高性能优化方案 #

方案1：全文检索索引（PostgreSQL适用） #

利用PostgreSQL的GIN全文索引，高效处理短语匹配：

1. 创建全文索引：

CREATE INDEX idx_keywords_tsv ON keywords USING GIN (to_tsvector('simple', query));

2. 优化后的查询语句：

SELECT k.query
FROM keywords k
WHERE NOT EXISTS (
    SELECT 1
    FROM negatives n
    WHERE 
      -- 匹配完全相等、开头/结尾包含、中间包含的情况
      k.query = n.text
      OR k.query LIKE n.text || ' %'
      OR k.query LIKE '% ' || n.text
      OR to_tsvector('simple', k.query) @@ to_tsquery('simple', n.text || ':*')
);

to_tsquery('simple', n.text || ':*')会匹配以指定词开头的短语，结合其他条件覆盖所有规则，GIN索引可将检索速度提升几个数量级。

方案2：分词数组+GIN索引 #

将关键词按空格拆分为数组，利用数组索引快速匹配中间包含的短语：

1. 预处理并创建索引：

-- 添加分词数组字段（可实时生成，也可预存）
ALTER TABLE keywords ADD COLUMN query_words TEXT[];
UPDATE keywords SET query_words = string_to_array(query, ' ');
-- 创建数组GIN索引
CREATE INDEX idx_keywords_words ON keywords USING GIN (query_words);

2. 查询语句：

SELECT k.query
FROM keywords k
WHERE NOT EXISTS (
    SELECT 1
    FROM negatives n
    WHERE 
      k.query = n.text
      OR k.query LIKE n.text || ' %'
      OR k.query LIKE '% ' || n.text
      OR n.text = ANY(k.query_words)
);

数组索引直接匹配拆分后的单个词，完美覆盖“中间包含”规则，且检索效率远高于通配符匹配。

方案3：前缀/后缀表达式索引（针对头尾匹配） #

如果需要单独优化开头/结尾匹配，可创建表达式索引（以PostgreSQL为例）：

-- 针对开头匹配的索引（取与negatives词等长的前缀）
CREATE INDEX idx_keywords_prefix ON keywords (left(query, 5)); -- 5对应water的长度
-- 针对结尾匹配的索引
CREATE INDEX idx_keywords_suffix ON keywords (right(query, 5));

此方案适合negatives词长统一的场景，可配合数组索引进一步提升性能。

核心优化要点 #

• 禁用全表扫描：用索引替代%xxx%这类无法命中索引的通配符匹配
• 利用小表优势：negatives仅百条数据，批量生成匹配条件减少循环开销
• 拆分规则优化：将复杂匹配拆分为可利用索引的子条件，逐个优化

内容的提问来源于stack exchange，提问作者sparkle