订阅内容权限查询：BIT_AND与关联表的性能及索引效果对比

场景背景 #

现有视频类订阅制内容权限场景中，订阅类型（basic=0、movies=1、sports=2、full=3）无递进关系，无法通过SELECT * from content where content_susbcription<=$userSubscriptionID这类简单范围查询获取用户可访问内容，为此提出两种查询方案。以下基于十万级数据量、查询字段均建索引的前提，分析性能差异、索引利用情况，并给出高并发场景下的最优选择。

方案a：关联表查询 #

实现逻辑

通过建立1-n关联表content_subscriptions存储内容ID与订阅类型的对应关系，查询SQL如下：

SELECT * FROM content c, content_subscriptions s where c.contentID=s.contentID and s.subscription = $userSubscriptionID

索引利用与性能表现

• 索引利用：给content_subscriptions表的subscription和contentID建立联合索引，content表的contentID作为主键默认带索引。查询时数据库可通过联合索引快速定位当前订阅类型对应的所有内容ID，再通过主键索引关联content表获取完整数据，属于等值匹配+索引关联，能被数据库优化器高效利用。
• 十万级数据下性能：查询逻辑清晰，索引过滤效率高，即使单订阅类型对应内容较多，也能避免全表扫描，执行速度稳定，资源开销低。

方案b：二进制权限字段+BIT_AND运算查询 #

实现逻辑

在content表新增二进制权限字段，通过位运算判断用户是否有权限访问，查询SQL如下：

SELECT * FROM content where BIT_AND(content_susbcription, $userSubscriptionID)<>0

索引利用与性能表现

• 索引利用：BIT_AND属于字段上的函数运算，主流数据库（如MySQL、PostgreSQL）无法直接利用该字段的普通B-tree索引。因为B-tree索引基于字段原始值排序，函数运算后的结果无法匹配索引的排序逻辑，执行时基本会触发全表扫描。
• 十万级数据下性能：全表扫描加上每条记录的位运算计算，会占用大量CPU和IO资源，查询延迟明显高于方案a；数据量越大性能差距越显著，高并发场景下会导致数据库资源瓶颈，吞吐量急剧下降。

高并发场景最优方案选择 #

优先选择方案a，核心原因：

• 性能稳定高效：索引能被充分利用，避免全表扫描，高并发下能保持较低的查询延迟和较高的吞吐量。
• 扩展性更强：后续新增订阅类型时，只需在content_subscriptions表新增关联记录，无需修改content表结构或调整查询逻辑。
• 维护成本更低：关联表结构更直观，权限关系清晰，排查问题和维护索引的成本远低于位运算方案。

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