SQL Server跨表验证用户权限的最优方案及表结构优化咨询

是不是最优？ #

当前的IN子查询写法本身没问题，SQL Server的查询优化器一般会把它转换成等价的JOIN执行，性能上不会有太大瓶颈，但核心要看索引是否合理。

如果Permission表没配合适的索引，数据量变大后子查询可能会触发全表扫描。建议给Permission表建个复合索引：

CREATE NONCLUSTERED INDEX IX_Permission_UserId_OptionA_ProductId
ON [dbo].[Permission] ([UserId], [OptionA])
INCLUDE ([ProductId]);

这样能快速过滤出符合条件的ProductId，避免全表扫描。另外Product表的Id作为主键（默认自带聚集索引），产品查找本身效率很高。

如果数据量不大，当前写法完全够用；数据量上去后，保证索引到位才是关键，写法本身不是性能短板。

当权限要扩展到50个时，原来用多个BIT列（OptionA、OptionB…）的设计会暴露很多问题：每次加新权限都要改表结构，操作繁琐还可能影响线上业务；大量BIT列导致表结构冗余，权限的增删改查都不方便；没法统一管理权限的元数据（比如权限名称、描述）。

更优的方案是采用权限类型表+用户产品权限关联表的多对多模式：

1. 新建权限类型表 #

CREATE TABLE [dbo].[PermissionType]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1), -- 用INT比GUID查询效率更高
    [Name] NVARCHAR(100) NOT NULL UNIQUE, -- 存储权限标识，比如'OptionA'
    [Description] NVARCHAR(500) NULL -- 可选，记录权限的业务说明
);

先插入现有权限类型：

INSERT INTO [PermissionType] ([Name]) VALUES ('OptionA'), ('OptionB');

2. 重构原Permission表为关联表 #

去掉原来的BIT列，改成用户、产品、权限类型的关联表：

CREATE TABLE [dbo].[UserProductPermission]
(
    [UserId] UNIQUEIDENTIFIER NOT NULL,
    [ProductId] UNIQUEIDENTIFIER NOT NULL,
    [PermissionTypeId] INT NOT NULL,
    PRIMARY KEY ([UserId], [ProductId], [PermissionTypeId]), -- 复合主键避免重复授权
    FOREIGN KEY ([ProductId]) REFERENCES [Product]([Id]),
    FOREIGN KEY ([PermissionTypeId]) REFERENCES [PermissionType]([Id])
);

插入现有权限数据：

-- 先获取权限类型的ID
DECLARE @OptionAId INT = (SELECT [Id] FROM [PermissionType] WHERE [Name] = 'OptionA');
DECLARE @OptionBId INT = (SELECT [Id] FROM [PermissionType] WHERE [Name] = 'OptionB');

-- 批量插入用户权限
INSERT INTO [UserProductPermission] ([UserId], [ProductId], [PermissionTypeId])
VALUES 
('81C669A6-98FA-4BE1-98CF-5BE335819C2E', '0DB7F090-D314-438F-94F0-3C9E5D2AA353', @OptionAId),
('81C669A6-98FA-4BE1-98CF-5BE335819C2E', '0DB7F090-D314-438F-94F0-3C9E5D2AA353', @OptionBId),
('81C669A6-98FA-4BE1-98CF-5BE335819C2E', 'EB0B3A3C-D24E-4FAE-97FD-A944BED852A7', @OptionAId),
('81C669A6-98FA-4BE1-98CF-5BE335819C2E', 'EB0B3A3C-D24E-4FAE-97FD-A944BED852A7', @OptionBId),
('81C669A6-98FA-4BE1-98CF-5BE335819C2E', '76FFF884-BB9F-4EE6-B6A8-92A39C7C6FB6', @OptionAId),
('81C669A6-98FA-4BE1-98CF-5BE335819C2E', '76FFF884-BB9F-4EE6-B6A8-92A39C7C6FB6', @OptionBId);

3. 对应的查询语句 #

查询用户拥有指定权限的产品（比如OptionA） #

SELECT p.*
FROM [Product] p
JOIN [UserProductPermission] upp ON p.[Id] = upp.[ProductId]
JOIN [PermissionType] pt ON upp.[PermissionTypeId] = pt.[Id]
WHERE upp.[UserId] = '81C669A6-98FA-4BE1-98CF-5BE335819C2E'
  AND pt.[Name] = 'OptionA';

查询用户同时拥有多个权限的产品（比如OptionA和OptionB） #

如果需要筛选同时满足多个权限的产品，可以用分组统计：

SELECT p.*
FROM [Product] p
JOIN [UserProductPermission] upp ON p.[Id] = upp.[ProductId]
JOIN [PermissionType] pt ON upp.[PermissionTypeId] = pt.[Id]
WHERE upp.[UserId] = '81C669A6-98FA-4BE1-98CF-5BE335819C2E'
  AND pt.[Name] IN ('OptionA', 'OptionB')
GROUP BY p.[Id], p.[Name]
HAVING COUNT(DISTINCT pt.[Id]) = 2; -- 2对应需要满足的权限数量

这种设计的优势 #

• 新增权限只需往PermissionType表插数据，不用改表结构，扩展性拉满
• 权限的元数据（名称、描述）可以统一管理，便于维护
• 复合主键天然避免重复授权，数据更严谨
• 能灵活组合各种权限查询条件，适配不同业务场景

给关联表建复合索引，进一步提升查询效率：

CREATE NONCLUSTERED INDEX IX_UserProductPermission_UserId_PermissionTypeId
ON [dbo].[UserProductPermission] ([UserId], [PermissionTypeId])
INCLUDE ([ProductId]);

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