怎么在SQL中通过嵌套查询实现多租户数据的隔离访问

不能直接在where中用tenant_id=(select tenant_id from users where user_id=?)，因子查询可能多行报错、重复查表性能差、易漏权限校验；租户id应由认证上下文预先提供，sql仅负责基于给定tenant_id查询数据。

怎么在sql中通过嵌套查询实现多租户数据的隔离访问_通过租户id子查询

为什么不能直接在 WHERE 中写 `tenant_id = (SELECT tenant_id FROM users WHERE user_id = ?)`

这种写法看似直观，但实际会出问题：子查询返回多行时直接报错 Subquery returns more than 1 row；更关键的是，它把租户身份校验逻辑和业务查询耦合在一起，每次都要重复查 users 表，既慢又容易漏掉权限校验（比如用户被禁用、租户已停用等）。真实系统里，租户上下文通常来自认证后的 session 或 token，不该在每条 SQL 里重新推导。

推荐做法：用参数化租户 ID 预先绑定，再嵌套过滤

把租户隔离控制在应用层或中间件层完成，SQL 只负责“给定租户 ID 后查数据”。嵌套查询真正该用的场景是：需要基于租户维度动态计算边界条件。例如：

SELECT * FROM orders 
WHERE tenant_id = ? 
  AND status = 'pending' 
  AND created_at > (
    SELECT COALESCE(MAX(closed_at), '2020-01-01') 
    FROM tenant_configs 
    WHERE tenant_id = ?
  );

tenant_id = ? 是必须的顶层过滤，不可省略
子查询只用于获取该租户专属配置值，不是用来“查租户是谁”
两个 ? 参数必须传入相同值，避免逻辑错位
如果 tenant_configs 没有对应记录，COALESCE 防止子查询返回 NULL 导致整行被过滤

误用嵌套查询导致 N+1 和越权的典型例子

下面这段 SQL 看似安全，实则危险：

SELECT o.* FROM orders o
WHERE o.id IN (
  SELECT order_id FROM order_access 
  WHERE user_id = ? AND tenant_id = (
    SELECT tenant_id FROM users WHERE id = ?
  )
);

问题在于：

内层 (SELECT tenant_id FROM users WHERE id = ?) 如果没加索引或用户不存在，可能超时或返回空
外层 order_access 表如果没有 (user_id, tenant_id) 联合索引，性能急剧下降
一旦 users 表被注入伪造数据（如租户 ID 被篡改），就可能绕过隔离
更糟的是，这个结构让数据库无法利用 orders.tenant_id 索引做快速定位，全表扫描风险高

真正需要嵌套的场景：跨租户聚合后按租户切片

比如统计每个租户最近 7 天订单数，但只显示“高于平均值”的租户：

SELECT tenant_id, COUNT(*) AS cnt
FROM orders 
WHERE created_at >= NOW() - INTERVAL 7 DAY
GROUP BY tenant_id
HAVING cnt > (
  SELECT AVG(cnt) FROM (
    SELECT COUNT(*) AS cnt 
    FROM orders 
    WHERE created_at >= NOW() - INTERVAL 7 DAY 
    GROUP BY tenant_id
  ) t
);

这里嵌套是合理的，因为：