多子表条件下父表Mail的高效参数化SQL查询方案

表结构与规则说明 #

• 父表Mail：存储邮件基础记录，核心字段为IdEmail（邮件唯一ID，主键）、Subject（邮件主题）及其他业务字段，单表数据量50万+
• 子表MailTag：存储邮件与标签的关联关系，核心字段为Id（关联记录唯一ID，主键）、IdTag（标签ID）、IdMail（关联的邮件ID，外键对应Mail.IdEmail），单表数据量50万+
• 业务规则：每封邮件至少关联1个标签，单封邮件可关联多个标签
• 实现约束：不使用循环语句、支持标签列表参数化传入、查询效率优先

前置性能优化（必做） #

50万+数据量级下，不建索引会导致查询耗时达到秒级甚至更高，必须先创建覆盖索引

• 在MailTag表上创建联合覆盖索引：

CREATE INDEX idx_mailtag_idmail_idtag ON MailTag(IdMail, IdTag);

该索引包含了标签匹配需要的所有字段，查询时不需要回表，可大幅降低IO开销。

场景1：纯白带名单筛选 #

需求：支持传入0到多个白名单标签ID，返回同时关联了所有传入白名单标签的邮件；传入0个白名单时，默认返回全部有效邮件。

SQL实现 #

SELECT m.*
FROM Mail m
WHERE
  -- 白名单数量为0时，跳过白名单匹配逻辑
  (
    @white_tag_cnt = 0
    OR EXISTS (
      SELECT 1
      FROM MailTag mt
      WHERE mt.IdMail = m.IdEmail
        AND mt.IdTag IN ( /* 参数化传入白名单ID列表，例：9,11 */ )
      GROUP BY mt.IdMail
      -- 匹配到的标签数等于传入的白名单总数，即命中全部白名单
      HAVING COUNT(DISTINCT mt.IdTag) = @white_tag_cnt
    )
  );

参数说明 #

• @white_tag_cnt：传入的白名单标签总数量，和IN里的标签列表个数保持一致，比如传入2个白名单时该值为2
• 示例：传入白名单9、11时，@white_tag_cnt=2，SQL会返回IdEmail为3、5、6的符合要求的邮件记录。

场景2：黑白名单组合筛选 #

需求：支持传入0到多个白名单标签ID、0到多个黑名单标签ID，返回同时关联所有白名单标签、且未关联任意一个黑名单标签的邮件；任意名单为空时跳过对应匹配逻辑。

SQL实现 #

SELECT m.*
FROM Mail m
WHERE
  -- 白名单匹配逻辑和场景1完全一致
  (
    @white_tag_cnt = 0
    OR EXISTS (
      SELECT 1
      FROM MailTag mt
      WHERE mt.IdMail = m.IdEmail
        AND mt.IdTag IN ( /* 参数化传入白名单ID列表，例：9,11 */ )
      GROUP BY mt.IdMail
      HAVING COUNT(DISTINCT mt.IdTag) = @white_tag_cnt
    )
  )
  -- 黑名单匹配：不存在任何关联标签在黑名单列表中
  AND (
    @black_tag_cnt = 0
    OR NOT EXISTS (
      SELECT 1
      FROM MailTag mt
      WHERE mt.IdMail = m.IdEmail
        AND mt.IdTag IN ( /* 参数化传入黑名单ID列表，例：10,12 */ )
    )
  );

参数说明 #

• 新增参数@black_tag_cnt：传入的黑名单标签总数量，和黑名单IN里的标签列表个数保持一致，比如传入2个黑名单时该值为2
• 示例：传入白名单9、11，黑名单10、12时，@white_tag_cnt=2、@black_tag_cnt=2，SQL会返回IdEmail为6的符合要求的邮件记录。

性能说明 #

• 所有标签匹配逻辑均走预先创建的覆盖索引，子查询无需回表，单条查询耗时稳定在毫秒级
• 全程无循环逻辑，标签列表直接通过IN子句参数化传入，数据库可缓存预编译执行计划，重复调用无额外解析开销
• 空名单场景通过计数参数直接跳过匹配段，不会出现无效全表扫描问题

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