单库多租户模式下PostgreSQL数据权限校验的高效方案对比咨询

两种实现的效率对比 #

第二种关联查询的方式效率明显更高，原因很直白：

• 减少数据库往返次数：第一种要发两次独立请求，每次都得经历网络传输、数据库处理、结果返回的完整流程，两次的开销加起来肯定比一次大。
• 数据库更擅长处理关联逻辑：数据库的查询优化器对JOIN操作有成熟的优化机制，只要给book_id、genre_id、tenant_id建了合适的索引，就能快速定位数据，比应用层查两次再手动判断的效率高得多。
• 避免竞态风险：第一种方式如果两次查询之间，书籍的genre_id被修改，会出现权限判断错误的情况，存在数据不一致的隐患。

更高效的实现方式 #

有几种比这两种更省心高效的方案：

1. 在books表冗余tenant_id字段 #

直接把tenant_id加到books表里，这样查询书籍时单表就能完成权限验证，完全不需要关联genre表：

SELECT * FROM book_data 
WHERE book_id = '0eokdpz0l' AND tenant_id = 'M1MzgzMDM'

这种方式效率最高，但要注意保持数据一致性——比如新增书籍时同步关联genre的tenant_id，修改genre的tenant_id时也要同步更新对应书籍的字段。可以通过数据库触发器、应用层事务或者ORM钩子函数实现同步。

2. 优化索引设计 #

如果不想修改表结构，就给表添加针对性的联合索引：

• 给genre_data表建立(genre_id, tenant_id)联合索引，数据库能通过genre_id快速定位对应的租户ID
• 给book_data表建立(book_id, genre_id)联合索引，查询书籍时能直接获取关联的genre_id
  这样数据库执行JOIN时，完全依靠索引就能定位数据，无需扫描全表，查询速度会大幅提升。

3. 用数据库视图封装权限逻辑 #

创建一个已经包含租户过滤逻辑的视图，应用层直接查询视图即可：

CREATE VIEW tenant_books AS
SELECT b.* FROM book_data b
JOIN genre_data g ON b.genre_id = g.genre_id
WHERE g.tenant_id = CURRENT_TENANT_ID -- 通过数据库上下文变量传递租户ID

每次查询书籍直接操作tenant_books视图，不用重复编写JOIN逻辑，数据库还会自动对视图查询做优化，保证效率。

内容的提问来源于stack exchange，提问作者SunAns