多标签筛选程序的SQL查询：动态实现与性能疑问

表结构定义 #

CREATE TABLE tags (
    id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL,
);

CREATE TABLE programs (
    id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL,
);

CREATE TABLE program_tag (
    program_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (program_id, tag_id),
    CONSTRAINT fk_program FOREIGN KEY(program_id) REFERENCES programs(id),
    CONSTRAINT fk_tag FOREIGN KEY(tag_id) REFERENCES tags(id)
);

需求说明 #

一个程序可关联多个标签，需要筛选出标签完全匹配指定集合的程序，当前使用的查询语句如下：

SELECT * FROM programs p
WHERE p.id IN (SELECT program_id FROM program_tag pt JOIN tags t ON pt.tag_id = t.id WHERE t.name = 'tag1')
AND p.id IN (SELECT program_id FROM program_tag pt JOIN tags t ON pt.tag_id = t.id WHERE t.name = 'tag2')

问题解答 #

1. 如何动态适配任意数量的标签查询？ #

可以通过分组统计的方式实现，无需频繁修改SQL语句：

-- 替换括号内的标签列表为实际需要匹配的集合，修改数字为标签总数即可
SELECT p.*
FROM programs p
JOIN program_tag pt ON p.id = pt.program_id
JOIN tags t ON pt.tag_id = t.id
WHERE t.name IN ('tag1', 'tag2', 'tag3')
GROUP BY p.id, p.name
-- 匹配到的标签数量必须等于指定标签的总数量
HAVING COUNT(DISTINCT t.name) = 3;

如果需要严格保证程序仅包含指定标签、无其他额外标签（完全匹配而非包含），需添加排除多余标签的逻辑：

SELECT p.*
FROM programs p
-- 先筛选出包含所有指定标签的程序
WHERE p.id IN (
    SELECT pt.program_id
    FROM program_tag pt
    JOIN tags t ON pt.tag_id = t.id
    WHERE t.name IN ('tag1', 'tag2', 'tag3')
    GROUP BY pt.program_id
    HAVING COUNT(DISTINCT t.name) = 3
)
-- 排除关联了非指定标签的程序
AND p.id NOT IN (
    SELECT pt.program_id
    FROM program_tag pt
    JOIN tags t ON pt.tag_id = t.id
    WHERE t.name NOT IN ('tag1', 'tag2', 'tag3')
);

这种方式只需在应用层动态拼接IN子句的标签列表和HAVING后的数字，即可适配任意数量的标签。

2. 当前查询的性能问题分析 #

当前多IN子查询的写法在标签数量较少时性能尚可，但存在明显隐患：

• 标签数量越多，AND IN的子查询越多，数据库需执行多次独立子查询，查询计划复杂度随标签数量线性上升，性能持续下降。
• 若program_tag和tags表数据量大，且未建立合适索引，每次子查询都会触发全表扫描，性能急剧恶化。

优化建议：

• 给tags.name建立唯一索引：CREATE UNIQUE INDEX idx_tags_name ON tags(name);，避免标签重复同时加速名称查找。
• 利用program_tag已有的主键索引（主键为(program_id, tag_id)联合索引），无需额外创建，可直接加速关联查询。
• 替换为分组统计写法，减少子查询次数，让数据库生成更高效的查询计划。

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