架构与系统设计 / 系统课时
复杂 SQL 适配
复杂 SQL 适配
本课解决租户拦截器在联表、子查询、批量操作、分页和自定义 SQL 中“漏加条件或改写后语义变化”的问题。完成后,你可以建立一套可验证的 SQL 适配规则,并用日志与测试确认每个租户表都被正确隔离。
学习目标
- 判断复杂 SQL 中哪些表必须注入租户条件
- 处理联表、子查询、批量写入与分页查询的关键边界
- 为自定义 SQL 建立可重复执行的隔离验证方法
复杂 SQL 的隔离原则
租户隔离不是在整条 SQL 末尾机械追加一次 tenant_id = ?,而是为每个参与访问的租户表限定数据范围。平台公共表可以配置为忽略,但订单、用户、门店等租户业务表必须分别受限。
联表查询要特别关注外连接语义。对于 LEFT JOIN 右侧的租户表,租户条件通常应成为连接条件的一部分;如果错误地放入最终 WHERE,可能把原本允许为空的右表记录过滤掉,使外连接退化为内连接。

下面两段均为片段示例,依赖已有的 pay_order、store 和 refund_order 表。原始查询同时包含主表、左连接和相关子查询:
-- 尚未注入租户条件的原始查询
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;逻辑上正确的租户改写结果应覆盖三个租户表:
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;预期结果
只返回 tenant_id=1001 的订单、门店和退款统计;其他租户数据不会参与连接、计数或排序。这里的 1001 仅用于展示,真实系统应由当前租户上下文绑定参数,不要把租户编号直接拼接进 SQL。
联表与子查询适配
自定义 SQL 应始终为表设置明确别名,并使用标准、可解析的连接语法。别名一旦定义,字段引用也应统一使用别名,避免拦截器无法判断 tenant_id 属于哪张表。
子查询必须独立检查。无论是标量子查询、EXISTS、IN 还是派生表,只要内部读取租户表,就需要在该查询作用域中注入条件。外层已经限制 pay_order,并不代表内层 refund_order 自动安全。
对于公共字典表、行政区划表或全平台费率模板,应通过明确的忽略表清单排除,而不是在 SQL 中临时删除租户条件。忽略清单必须小而稳定,并由测试证明这些表确实不含租户私有数据。
批量写入与更新删除
批量插入时,租户字段应由服务端统一填充。客户端即使提交了 tenant_id,也应覆盖为当前上下文值,防止伪造其他租户编号。批量参数中的每一行都必须得到相同且可信的租户值。
INSERT ... SELECT ... 同时包含写入侧和读取侧:目标表需要写入当前租户编号,来源查询也必须限制为当前租户。否则可能把其他租户的数据复制到当前租户名下。
更新和删除必须同时带业务主键与租户条件。下面是一条可直接用于验证影响行数的 SQL,<ID> 与租户编号需替换为测试环境中的真实值:
UPDATE pay_order
SET subject = 'updated by tenant 1001'
WHERE id = <ID>
AND tenant_id = 1001;预期结果
Rows affected: 1如果 <ID> 属于其他租户,预期影响行数应为 0。这比只检查接口是否返回成功更能证明隔离条件实际生效。
分页与自定义 SQL
分页通常会生成数据查询和总数查询两条 SQL,两条都必须经过租户拦截。验收时不能只看当前页数据,还要确认 total 没有统计到其他租户记录。
自定义 SQL 应避免难以解析的数据库方言、动态拼接完整 SQL 片段以及缺少别名的多表表达式。确实需要使用窗口函数、公共表表达式或厂商特有语法时,应单独建立集成测试,并检查拦截后的最终 SQL。
建议在测试环境开启 SQL 日志,至少核对主查询、分页计数、子查询和批量操作四类语句。日志中的动态时间、线程名和参数序号可能不同,但每个租户表都应出现对应的隔离条件。
验收清单
- 同一查询在两个租户上下文中返回不同且互不交叉的数据
LEFT JOIN保持外连接语义,右表无匹配时主表记录仍可返回- 子查询、分页总数、更新和删除均包含租户限制
- 公共表只通过受控忽略清单排除,不在业务 SQL 中随意绕过
- 解析失败会显式报错或阻断执行,不允许静默降级为无隔离查询
本课总结
- 复杂 SQL 必须按查询作用域和租户表逐一注入条件
- 外连接、子查询与分页总数是最容易出现隔离漏洞的位置
- 最可靠的验收方式是检查最终 SQL、结果集合和受影响行数
下一课预告
下一课将实现受控的隔离绕过机制,让平台任务和迁移脚本可以临时跨租户执行,同时完整记录操作人、原因和作用范围。