超大规模Data表与LookUp表的正则匹配非等值连接技术咨询

嘿，这个场景我太熟了——23亿行的Data表+非等值模糊匹配，直接硬跑肯定会崩，但好在你的LookUp表只有140万行、50MB，完全可以围绕这个小表做文章，把性能拉上来。下面是我整理的几个实用方案，按优先级排序：

核心思路：反转匹配逻辑，让小表当“主角” #

默认写法Data.col10 LIKE CONCAT('%', LookUp.value, '%')会把大表当驱动表，触发全表扫描，这是性能灾难。反过来，让小表做驱动表，每次用LookUp里的一个值去匹配大表，成本会低得多。

1. 用全文索引/Trgm索引加速模糊匹配（最推荐） #

如果你的数据库支持全文索引或 trigram 索引，这是最快的方式：

• MySQL方案：先给LookUp表的value列建全文索引，再反转匹配逻辑

  -- 给LookUp表建全文索引
  ALTER TABLE LookUp ADD FULLTEXT INDEX idx_lookup_value (value);
  -- 小表驱动大表查询
  SELECT d.*, l.*
  FROM LookUp l
  JOIN Data d ON MATCH(d.col10) AGAINST(l.value IN BOOLEAN MODE)
  

• PostgreSQL方案：用pg_trgm扩展实现高效模糊匹配

  -- 先安装trgm扩展
  CREATE EXTENSION pg_trgm;
  -- 给Data.col10建GIN trigram索引
  CREATE INDEX idx_data_col10_trgm ON Data USING GIN (col10 gin_trgm_ops);
  -- 用trgm模糊匹配运算符查询
  SELECT d.*, l.*
  FROM LookUp l
  JOIN Data d ON d.col10 % l.value;
  

这两种方式都能避免全表扫描，性能比原生LIKE快几个数量级。

2. 把模糊匹配转成等值连接（适合有结构的文本） #

如果Data.col10是有规律的文本（比如带分隔符的字符串、URL、日志关键词），可以提前把col10拆分成单个关键词，存到一个分词表，然后做等值连接：

• 先通过ETL或触发器生成分词表（比如Data_Tokens，包含data_id和拆分后的token）：

  -- MySQL示例：按逗号拆分col10生成分词表
  INSERT INTO Data_Tokens (data_id, token)
  SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(col10, ',', n), ',', -1)
  FROM Data
  -- 生成1-10的数字序列，支持最多10个分词，可按需调整
  JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) numbers
  ON n <= LENGTH(col10) - LENGTH(REPLACE(col10, ',', '')) + 1;
  

• 然后做等值连接，速度会大幅提升：

  SELECT d.*, l.*
  FROM Data d
  JOIN Data_Tokens dt ON d.id = dt.data_id
  JOIN LookUp l ON dt.token = l.value;
  

3. 大数据引擎用广播连接（Spark/Flink场景） #

如果你是在大数据平台处理，直接用广播连接把小表推到所有节点，避免跨节点数据传输：

-- Spark SQL示例
SELECT d.*, l.*
FROM Data d
JOIN BROADCAST(LookUp) l ON d.col10 RLIKE l.value;

因为LookUp只有50MB，完全可以放到内存里，每个节点处理本地的Data分片，性能会非常可观。

4. 分批次离线处理（兜底方案） #

如果以上方案都不适用，那就把Data表拆成小批次处理，比如按id或者时间范围分片，每次处理1000万行，最后合并结果：

-- 示例：按id分批次查询
SELECT d.*, l.*
FROM Data d
JOIN LookUp l ON d.col10 LIKE CONCAT('%', l.value, '%')
WHERE d.id BETWEEN 1 AND 10000000;

-- 依次处理后续批次，直到覆盖所有数据

这种方式虽然麻烦，但能避免一次性占用过多资源，适合离线跑数场景。

几个关键注意点 #

• 绝对别在大表的col10上做函数运算（比如LIKE CONCAT('%', l.value, '%')），会直接让索引失效，触发全表扫描。
• 数据库优化器有时候会犯傻，没选小表当驱动表，这时候可以用STRAIGHT_JOIN（MySQL）或者FORCE ORDER（PostgreSQL）强制指定。
• 如果是实时查询，尽量把关联逻辑提前到ETL阶段，直接把结果存到新表，别让用户等实时匹配。

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