多租户系统中SQL嵌套查询的最佳实践？

tenant_id 必须在每一层 WHERE 和 JOIN 条件中显式声明，嵌套查询、窗口函数、子查询等所有涉及业务表的场景均需严格过滤，禁止动态推导或遗漏，确保租户数据隔离。

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 租户第 1 条可能是序号 87，B 租户第 1 条是 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 = ? 容易漏掉权限校验点——这些细节比语法本身更决定隔离成败。