何时使用位掩码来存储标志比使用关联表更好？

Question

我正在开发一个应用程序，用户拥有使用不同功能的不同权限（例如读取、创建、下载、打印、批准等）。 权限列表不会经常更改。 我有几个选项可以将这些权限存储在数据库中。

在什么情况下选项2会更好？

选项1

使用关联表。

User
----
UserId (PK)
Name
Department

Permission
----
PermissionId (PK)
Name

User_Permission
----
UserId (FK)
PermissionId (FK)

选项2

为每个用户存储位掩码。

User
----
UserId (PK)
Name
Department
Permissions

[Flags]
enum Permissions {
    Read = 1,
    Create = 2,
    Download = 4,
    Print = 8,
    Approve = 16
}

- Ryan Kohn

Answer 1

好问题！

首先，让我们对“更好”做一些假设。

我假设您并不太关心磁盘空间 - 从空间角度来看，位掩码是高效的，但如果您正在使用SQL服务器，则我不确定这是否很重要。

我假设您关心速度。在使用计算时，位掩码可以非常快速 - 但是在查询位掩码时，您将无法使用索引。这本应该并不太重要，但如果您想知道哪些用户具有创建访问权限，则查询将类似于

select * from user where permsission & CREATE = TRUE

(今天没有访问SQL Server，正在路上)。由于数学运算的影响，该查询将无法使用索引 - 因此，如果您拥有大量用户，这将非常痛苦。

我假设您关心可维护性。从可维护性的角度来看，位掩码不如存储显式权限那么表达问题域。您几乎肯定需要在多个组件（包括数据库）之间同步位掩码标志的值。不是不可能，但很麻烦。

因此，除非有另一种评估“更好”的方法，否则我会说位掩码方案不如在规范化数据库结构中存储权限好。我不同意它会“变慢，因为你必须进行连接”-除非您的数据库完全失灵，否则您将无法测量此效果（而即使有几千条记录，在没有活动索引的情况下查询可能会变得明显更慢）。

- Neville Kuyt

---

由于布尔型（或在 SQL Server 中为位类型）列的基数非常低，对这些列建立索引是完全无用的。因此，规范化方案也无法利用该优化。 - Clodoaldo Neto

SQL Server是否将相邻的位字段打包成字节，基本上将其存储为位掩码？ - crush

Answer 2

个人而言，我会使用关联表。

按位掩码字段很难进行查询和连接。

你可以始终将其映射到C#的标志枚举中，如果性能成为问题，再重新设计数据库。

可读性优先于过早优化 ;)

- Oded

---

管理和维护。当关键信息被模糊化存储在位掩码列中时，管理和维护数据库中的数据会更加困难。而且任何性能提升几乎肯定不足以产生实际差别。 - Philip Kelley

Answer 3

没有明确的答案，所以做适合自己的。但是这里有我的建议：

如果满足以下条件，请使用选项1

• 您预计权限将增长到很多
• 如果您可能需要在数据库存储过程中进行权限检查
• 您不希望记录表中的记录大量增长，例如百万用户

如果满足以下条件，请使用选项2

• 权限将被限制为少数
• 您预计会有数百万用户

- Aliostad

---

现代（甚至是像样的旧版）关系型数据库管理系统中，数百万行是微不足道的数字。 - Adam Robinson

是的，但考虑到可能需要的索引和在搜索过程中可能会减慢进程的索引书签选项，我更喜欢第二个选项。 - Aliostad

Answer 4

将权限规范化存储（即不使用位掩码）。虽然在您的情况下显然不是必需的（特别是如果权限不经常更改），但这将使查询更加容易和明显。

- Adam Robinson

Answer 5

我建议不要使用位掩码，原因如下：

• 索引无法高效使用
• 查询更加困难
• 可读性和维护性受到严重影响
• 普通开发人员不知道什么是位掩码
• 灵活性降低（数字中的位数上限）

根据您的查询模式、计划功能集和数据分布，我会选择您的选项1，甚至选择一些简单的东西:

user_permissions(
   user_id
  ,read     
  ,create   
  ,download 
  ,print    
  ,approve  
  ,primary key(user_id)
);

添加列是模式修改，但我猜添加“清除”权限将需要一些代码与之配合，因此权限可能不像你想象的那么动态。

如果您有一些数据分布异常的情况，例如90％的用户基础没有单个权限，则以下模型也可以正常工作（但在进行更大的扫描时（一个5路连接与单个完整表扫描）可能会出现问题）。

user_permission_read(
   user_id
  ,primary key(user_id)
  ,foreign key(user_id) references user(user_id)
)

user_permission_write(
   user_id
  ,primary key(user_id)
  ,foreign key(user_id) references user(user_id)
)

user_permission_etcetera(
   user_id
  ,primary key(user_id)
  ,foreign key(user_id) references user(user_id)
)

- Ronnis

Answer 6

唯一我能想到的使用位掩码字段存储权限的情况，就是当你在物理内存非常有限的情况下，比如在旧的移动设备上。实际上，你节省的内存量不值得这么做。即使在数百万用户的情况下，硬盘空间很便宜，可以通过使用非位掩码方法（比如基于谁拥有什么权限等情况进行报告）更容易地扩展权限等。

其中一个最大的麻烦是直接在数据库中分配用户权限。我知道应该尝试使用应用程序来管理自身，并减少与应用程序数据的交互，但有时候这是必须的。除非位掩码实际上是一个字符字段，并且你可以轻松地查看某个人拥有哪些权限，否则尝试向分析师等人员解释如何通过更新字段向某人授予写访问权限，并祈祷你的算术是正确的。

- kemiller2002

Answer 7

当它们的结构不会改变且总是一起使用时，它们非常有用。这样，您就可以减少与服务器之间的往返次数。从性能方面来看，它们也很好，因为您可以通过单个变量分配影响所有权限。

我个人不喜欢它们...在某些性能密集型应用程序中，它们仍然被使用。我记得使用它们实现过一个国际象棋AI，因为您可以通过单个比较评估一个棋盘..但是使用它们确实很麻烦。

- Simon Dufour

Answer 8

我通常会将其规范化存储，除非数据库仅仅是为您保存记录，并且您永远不会对其进行检索和保存以外。这种情况的一个场景是，在登录时获取用户的权限字符串，并在服务器代码中进行处理和缓存。在这种情况下，它的非规范化并不太重要。

如果您将其存储在字符串中，并尝试在数据库级别上进行操作，则必须进行一些花式操作才能从中获取页面X的权限，这可能很痛苦。

- Mike M.

Answer 9

使用标志枚举（位掩码）可以使您的查询运行更快，因为您不需要包含关联表的连接以理解该值。

- smartcaveman

---

这种说法是错误的，它错误地暗示了用连接查询速度会变慢。你还没有考虑查询本身的内容。如果查询是检查特定权限是否存在，一个在正确索引列上进行的连接操作将远优于位掩码字段，后者需要进行表扫描才能完成位运算。 - Adam Robinson

@Adam Robinson，（1）不，它实际上并不意味着那样。它意味着查询将运行得更快，这是正确的。（2）您正在将一张关联表上最高度优化的查询与整数字段上最差优化的查询进行比较。那真的不太实用。 - smartcaveman

虽然编写解释位掩码的代码可能比连接USER_PERMISSION表更有效，但性能差异似乎不太明显--这不太可能是瓶颈操作--而且代码的清晰度会大大降低。 - Justin Cave

您原始版本中写的是“快”，而不是现在的“更快”，这就是我第一次评论的原因。是的，我正在比较关联版本的“最高度优化查询”，但它也是最有可能存在的版本。我将其与位掩码字段上的“最差优化”查询进行比较，因为这也是最有可能存在的情况。无法在字段上创建位索引，如果您计划将权限作为查询的一部分进行检查，则无法避免位运算。您有更好的选项吗？ - Adam Robinson