如何编写高性能SQL实现Table A与Table B的多字段匹配查询

场景说明 #

现有两张数据表：

• Table A：包含字段A1、A2
• Table B：包含字段B1、B2、B3、B4、B5、B6

需求为查询Table B中符合如下条件的所有行：Table A中存在至少一行数据，该行的A1、A2两个字段的值同时出现在当前Table B行的任意字段内。

实现方案 #

基础通用方案 #

适配所有主流SQL数据库，逻辑清晰易维护，适合10万行以下的中小数据量场景：

SELECT DISTINCT b.*
FROM TableB b
INNER JOIN TableA a
ON 
  a.A1 IN (b.B1, b.B2, b.B3, b.B4, b.B5, b.B6)
  AND 
  a.A2 IN (b.B1, b.B2, b.B3, b.B4, b.B5, b.B6)

高性能优化方案 #

针对百万级以上数据量场景，通过预计算+索引的方式大幅提升查询效率：

1. 给Table B新增聚合字段，存储当前行所有字段的值，用特殊分隔符包裹避免匹配误差：

ALTER TABLE TableB ADD COLUMN all_fields TEXT;
-- 示例为MySQL语法，其他数据库替换对应拼接函数即可
UPDATE TableB 
SET all_fields = CONCAT('|', B1, '|', B2, '|', B3, '|', B4, '|', B5, '|', B6, '|');

2. 给聚合字段创建全文索引：

CREATE FULLTEXT INDEX idx_tb_allfields ON TableB(all_fields);

3. 优化后的查询语句，直接命中索引避免全表扫描：

SELECT DISTINCT b.*
FROM TableB b
INNER JOIN TableA a
ON MATCH(b.all_fields) AGAINST(CONCAT('+|', a.A1, '| +|', a.A2, '|') IN BOOLEAN MODE)

两种方案均可以匹配示例场景的预期输出结果。

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