多租户系统中SQL嵌套查询最佳实践指南

在多租户系统中，tenant_id 这层过滤一旦缺失，租户间的数据越权几乎无法避免。最常见的问题出现在嵌套查询里——你明明在主查询中加入了 WHERE tenant_id = ?，但子查询却忽略了它，结果就是：A 租户的订单详情里，突然出现了 B 租户的发片记录。这种隐患往往难以通过代码审查发现，直到生产环境报出权限异常，回头查看 SQL 日志才追悔莫及。

tenant_id 必须显式出现在每一层 WHERE 和 JOIN 条件中

遗漏 tenant_id 是多租户系统里最常见、最危险的数据越权根源。只要嵌套查询涉及业务表（例如 orders、invoices），无论子查询位于 WHERE、FROM 还是 SELECT 子句中，都必须显式添加 tenant_id = ? 过滤条件。

• 错误写法：WHERE id IN (SELECT order_id FROM invoices WHERE user_id = ?) —— 缺少 tenant_id 过滤，可能返回其他租户的发片记录
• 正确写法：WHERE id IN (SELECT order_id FROM invoices WHERE user_id = ? AND tenant_id = ?)
• JOIN 场景更容易被忽略：如果使用 LEFT JOIN users u ON o.user_id = u.id，而 users 表是跨租户共享的，必须额外加上 AND u.tenant_id = o.tenant_id 或通过中间映射表关联，不能默认“主表的 tenant_id 会自动生效”
• MySQL 5.7 对关联子查询的优化较差，EXISTS 比 IN 更稳定；但 EXISTS 子句内也必须写入 i.tenant_id = o.tenant_id，否则关联条件失效

不要用子查询推导 tenant_id，应通过上下文直接传入

tenant_id 不应该由 SQL 动态查询得到，例如 WHERE tenant_id = (SELECT tenant_id FROM users WHERE id = ?)。这种写法既低效又易出错——子查询可能返回多行导致报错，也可能因用户被禁用或租户停用导致结果为空，甚至引发越权。

• 实际开发中，tenant_id 应由认证环节（JWT、Session、拦截器等）解析并透传到 DAO 层，SQL 只负责“根据已知的租户 ID 查询数据”
• 嵌套查询真正适合的场景是：基于已知的 tenant_id 做租户级别的计算，例如 (SELECT MAX(closed_at) FROM tenant_configs WHERE tenant_id = ?)
• 两个 ? 参数必须严格保持一致；若子查询没有匹配记录，使用 COALESCE(..., '1970-01-01') 防止 NULL 导致整个条件失效

PARTITION BY tenant_id 是实现窗口函数隔离的前提

ROW_NUMBER()、RANK() 等窗口函数如果不加 PARTITION BY tenant_id，在多租户场景中会完全失去隔离意义——它会把全表当成一个序列进行编号，A 租户的第一条记录可能是序号 87，而 B 租户的第一条却是 88。

• 必须写成：ROW_NUMBER() OVER (PARTITION BY tenant_id ORDER BY created_at, id)
• ORDER BY 需要包含确定性字段（如 created_at + id），防止同一行在不同查询中编号跳动
• WHERE 过滤必须在窗口函数外层完成：先用子查询或 CTE 筛选出 tenant_id = ? AND status = 'paid' 的记录，再在其结果上应用窗口函数；否则编号会包含被过滤掉的记录，导致序号出现“空洞”
• MySQL 8.0 及以上版本支持该语法，旧版 MySQL 不支持，切勿直接在低版本中使用

嵌套层级超过两层时，需考虑重构

三层或以上的嵌套不仅可读性差，还容易导致优化器放弃索引——尤其在 MySQL 5.7 或未启用 semijoin 的环境中，相关子查询可能会退化为 N×M 的全表扫描。

• 典型反模式：WHERE x IN (SELECT y FROM t1 WHERE z IN (SELECT w FROM t2 WHERE tenant_id = ?))
• 优先改写为 JOIN：INNER JOIN t1 ON ... INNER JOIN t2 ON ... WHERE t1.tenant_id = ? AND t2.tenant_id = ?
• 逻辑复杂时，使用 CTE（公用表表达式）分步处理：先查询租户级配置，再查订单，最后关联统计，比深度嵌套更清晰且易于调试
• 所有中间结果集只选取必要字段，避免使用 SELECT *，减少冗余列对内存和网络传输的消耗

实际生产环境中你会发现，PARTITION BY tenant_id 在大型表上未必能下推过滤条件，部分数据库仍会扫描全部分区；而 tenant_id 字段名不统一（例如混用 org_id、account_id）时，直接写 WHERE org_id = ? 很容易遗漏权限校验点——这些细节比语法本身更直接影响数据隔离的成败。