多对多关系下Lead与Tag的ANY/ALL标签查询实现及ALL查询最优性能方案探讨

问题背景 #

咱们先明确一下数据库结构：这是一个典型的多对多关联模型，lead和tag通过中间表leads_tags建立关联，建表语句如下：

CREATE TABLE lead ( 
  id serial constraint lead_id_pk primary key, 
  name VARCHAR(20), 
  surname VARCHAR(20) 
);
CREATE TABLE tag ( 
  id serial constraint tag_id_pk primary key, 
  name VARCHAR(20), 
  description Text 
);
create table leads_tags ( 
  id serial constraint lead_tags_id_pk primary key, 
  lead_id integer not null constraint leads_tags_leads_id_fk references lead on delete cascade, 
  tag_id integer not null constraint leads_tags_tags_id_fk references tag on delete cascade, 
  constraint leads_tags_lead_id_tag_id_key unique (lead_id, tag_id) 
);

需要实现两类查询：

1. 获取包含标签列表中任意一个标签的所有Lead
2. 获取包含标签列表中所有标签的所有Lead

第一类查询（任意标签）的实现 #

你给出的这个方案已经很合理了，利用子查询过滤出关联了指定标签的Lead ID，再去重查询Lead详情：

Select distinct (id), * FROM lead where id in ( 
  Select lt.lead_id from leads_tags lt where tag_id in (324, 129) 
);

第二类查询（全部标签）的方案分析 #

你当前使用的array_agg(@>)方案是可行的，但算不上性能最优，尤其是当leads_tags表数据量较大时。

SELECT * FROM lead WHERE id IN ( 
  SELECT lt.lead_id FROM leads_tags lt 
  GROUP BY lt.lead_id 
  HAVING array_agg(tag_id) @> array [324,129] 
);

这个方案需要先对每个Lead的标签ID做数组聚合，再判断数组是否包含指定标签集合。如果一个Lead关联了很多标签，聚合数组的过程会消耗额外的内存和CPU，效率不如更直接的实现方式。

性能更优的实现方式 #

这里推荐两种常用且高效的方案：

方案1：分组计数法（最通用，性能出色）

利用JOIN过滤出关联指定标签的记录，分组后统计每个Lead关联的指定标签数量，当数量等于我们要的标签总数时，说明这个Lead包含所有指定标签。因为leads_tags有(lead_id, tag_id)的唯一约束，所以不需要DISTINCT，直接用COUNT(*)即可：

SELECT l.*
FROM lead l
JOIN leads_tags lt ON l.id = lt.lead_id
WHERE lt.tag_id IN (324, 129)
GROUP BY l.id
HAVING COUNT(*) = 2; -- 这里的数字要和指定标签的数量一致

这个方案的优势是逻辑清晰，利用数据库的分组统计能力，依赖leads_tags上的复合索引（已经由唯一约束自动创建），查询速度非常快，适合绝大多数场景。

方案2：多EXISTS子查询（标签数量少时更高效）

如果需要查询的标签数量不多（比如2-5个），可以用多个EXISTS子查询来逐一验证每个标签是否存在：

SELECT * FROM lead l
WHERE EXISTS (
  SELECT 1 FROM leads_tags lt1 WHERE lt1.lead_id = l.id AND lt1.tag_id = 324
)
AND EXISTS (
  SELECT 1 FROM leads_tags lt2 WHERE lt2.lead_id = l.id AND lt2.tag_id = 129
);

这种方式不需要分组操作，每个EXISTS都会利用leads_tags的复合索引做快速查找，性能和分组计数法相当，甚至在标签数量极少时更优。但如果标签数量较多（比如10个以上），写多个EXISTS会比较繁琐，此时分组计数法更合适。

性能对比总结 #

• 原array_agg(@>)方案：功能可行，但聚合数组的额外开销在大数据量下会拖慢性能，不推荐作为最优方案。
• 分组计数法：通用、高效，适合所有标签数量的场景，是大多数情况下的首选。
• 多EXISTS子查询：标签数量少时简洁高效，适合小批量标签的精确匹配。

另外要注意，leads_tags上的(lead_id, tag_id)唯一约束已经自动创建了复合索引，这是所有这些查询性能的关键，如果没有这个约束/索引，一定要手动创建：

CREATE UNIQUE INDEX idx_leads_tags_lead_tag ON leads_tags (lead_id, tag_id);

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