架构与系统设计 / 系统课时

复杂 SQL 适配

复杂 SQL 适配

本课解决租户拦截器在联表、子查询、批量操作、分页和自定义 SQL 中“漏加条件或改写后语义变化”的问题。完成后,你可以建立一套可验证的 SQL 适配规则,并用日志与测试确认每个租户表都被正确隔离。

学习目标

  • 判断复杂 SQL 中哪些表必须注入租户条件
  • 处理联表、子查询、批量写入与分页查询的关键边界
  • 为自定义 SQL 建立可重复执行的隔离验证方法

复杂 SQL 的隔离原则

租户隔离不是在整条 SQL 末尾机械追加一次 tenant_id = ?,而是为每个参与访问的租户表限定数据范围。平台公共表可以配置为忽略,但订单、用户、门店等租户业务表必须分别受限。

联表查询要特别关注外连接语义。对于 LEFT JOIN 右侧的租户表,租户条件通常应成为连接条件的一部分;如果错误地放入最终 WHERE,可能把原本允许为空的右表记录过滤掉,使外连接退化为内连接。

复杂 SQL 的租户条件注入位置

下面两段均为片段示例,依赖已有的 pay_order、store 和 refund_order 表。原始查询同时包含主表、左连接和相关子查询:

sql
-- 尚未注入租户条件的原始查询
SELECT o.id,
       o.order_no,
       s.store_name,
       (SELECT COUNT(*)
          FROM refund_order r
         WHERE r.order_id = o.id) AS refund_count
  FROM pay_order o
  LEFT JOIN store s
    ON s.id = o.store_id
 WHERE o.status = 'SUCCESS'
 ORDER BY o.id DESC;

逻辑上正确的租户改写结果应覆盖三个租户表:

sql
SELECT o.id,
       o.order_no,
       s.store_name,
       -- 子查询内部的租户表需要独立限制
       (SELECT COUNT(*)
          FROM refund_order r
         WHERE r.order_id = o.id
           AND r.tenant_id = 1001) AS refund_count
  FROM pay_order o
  LEFT JOIN store s
    ON s.id = o.store_id
   -- 右表条件保留在 ON 中,避免破坏 LEFT JOIN 语义
   AND s.tenant_id = 1001
 WHERE o.status = 'SUCCESS'
   AND o.tenant_id = 1001
 ORDER BY o.id DESC;

预期结果

text
只返回 tenant_id=1001 的订单、门店和退款统计;其他租户数据不会参与连接、计数或排序。

这里的 1001 仅用于展示,真实系统应由当前租户上下文绑定参数,不要把租户编号直接拼接进 SQL。

联表与子查询适配

自定义 SQL 应始终为表设置明确别名,并使用标准、可解析的连接语法。别名一旦定义,字段引用也应统一使用别名,避免拦截器无法判断 tenant_id 属于哪张表。

子查询必须独立检查。无论是标量子查询、EXISTS、IN 还是派生表,只要内部读取租户表,就需要在该查询作用域中注入条件。外层已经限制 pay_order,并不代表内层 refund_order 自动安全。

对于公共字典表、行政区划表或全平台费率模板,应通过明确的忽略表清单排除,而不是在 SQL 中临时删除租户条件。忽略清单必须小而稳定,并由测试证明这些表确实不含租户私有数据。

批量写入与更新删除

批量插入时,租户字段应由服务端统一填充。客户端即使提交了 tenant_id,也应覆盖为当前上下文值,防止伪造其他租户编号。批量参数中的每一行都必须得到相同且可信的租户值。

INSERT ... SELECT ... 同时包含写入侧和读取侧:目标表需要写入当前租户编号,来源查询也必须限制为当前租户。否则可能把其他租户的数据复制到当前租户名下。

更新和删除必须同时带业务主键与租户条件。下面是一条可直接用于验证影响行数的 SQL,<ID> 与租户编号需替换为测试环境中的真实值:

sql
UPDATE pay_order
   SET subject = 'updated by tenant 1001'
 WHERE id = <ID>
   AND tenant_id = 1001;

预期结果

text
Rows affected: 1

如果 <ID> 属于其他租户,预期影响行数应为 0。这比只检查接口是否返回成功更能证明隔离条件实际生效。

分页与自定义 SQL

分页通常会生成数据查询和总数查询两条 SQL,两条都必须经过租户拦截。验收时不能只看当前页数据,还要确认 total 没有统计到其他租户记录。

自定义 SQL 应避免难以解析的数据库方言、动态拼接完整 SQL 片段以及缺少别名的多表表达式。确实需要使用窗口函数、公共表表达式或厂商特有语法时,应单独建立集成测试,并检查拦截后的最终 SQL。

建议在测试环境开启 SQL 日志,至少核对主查询、分页计数、子查询和批量操作四类语句。日志中的动态时间、线程名和参数序号可能不同,但每个租户表都应出现对应的隔离条件。

验收清单

  • 同一查询在两个租户上下文中返回不同且互不交叉的数据
  • LEFT JOIN 保持外连接语义,右表无匹配时主表记录仍可返回
  • 子查询、分页总数、更新和删除均包含租户限制
  • 公共表只通过受控忽略清单排除,不在业务 SQL 中随意绕过
  • 解析失败会显式报错或阻断执行,不允许静默降级为无隔离查询

本课总结

  • 复杂 SQL 必须按查询作用域和租户表逐一注入条件
  • 外连接、子查询与分页总数是最容易出现隔离漏洞的位置
  • 最可靠的验收方式是检查最终 SQL、结果集合和受影响行数

下一课预告

下一课将实现受控的隔离绕过机制,让平台任务和迁移脚本可以临时跨租户执行,同时完整记录操作人、原因和作用范围。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

复杂 SQL 适配

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通