在 sql 查询中按位操作实现索引查找的替代方法

本文关键字：索引查找 方法 实现 位操作 sql 查询 | 更新日期: 2023-09-27 17:57:06

假设有一个表有两个字段"id"和"value"。我创建了一个non-clustured index on "value"字段有一个参数"@valueToCompare".

For example : 
MyTable
Id(Int) Value(int)
1       9
2       11
3       13
4       7
5       8
6       20
@valueToCompare = 27

现在我想写一个查询，它将为您提供满足"value & @valueToCompare = value"条件的结果集因此我的查询将是

select * from MyTable where value & @valueToCompare = value

这将在计算后给出结果

9 & 27   =9
11 & 27  =11
13 & 27  =9
7 & 27   =3
8 & 27   =8
20 & 27  =16

现在这个查询的问题是优化器会做index scan rather than Index seek，因此效率较低。

所以想知道有没有办法编写查询来实现索引搜索。

注意：我将在 c# 代码中使用结果集

在 sql 查询中按位操作实现索引查找的替代方法

索引查找可能比扫描更有效，也可能不比扫描更有效。 扫描窄覆盖索引可能比查找和查找更快，除了最高的选择性查询。

单个比特列只能是 0 或 1;它的选择性平均为 50%，因此可能不是一个很好的索引候选者。您可以创建索引，但考虑到非常高的查找成本，优化程序可能会也可能不会使用它。

将其组合成位域可能是更好的解决方案，如果您了解数据并巧妙地对位进行排序，从高选择性到低选择性，则甚至更多。为了说明这个想法：

select * from MyTable where value < @valueToCompare and value & @valueToCompare = value

SQL Server 不会使用索引来执行按位运算。 您不走运，但是您可以先通过 CTE 或派生表创建较小的结果集，然后在较小的集上执行操作。

由于 @valueToCompare 的当前值参与具有其中一列的表达式，因此无法将其转换为索引查找。但是，如果您愿意将各个标志拆分为自己的列，则可能会获得更快的查找速度。

要表示从 0 到 int.MaxValue 的 int s，您需要 31 位列 - 例如，v0 到 v30 ，包括。您可以对它们创建索引，然后将搜索编号拆分为单独的位，然后运行查询。而不是value & @valueToCompare = value，你可以编写一个对应于@valueToCompare二进制值的检查组合。

例如，如果@valueToCompare为 910 或 10012，则查询将如下所示：

SELECT ...
WHERE v0=1 AND v3=1

v0是包含位零的列; v3是包含位 3 的列。这些是用 9 的二进制表示形式设置的两个位。

您可以让 SQL Server 为您管理列，而不是手动管理列。

create table bitfields (
id int,
value int,
v0 as value & 1 persisted,
v1 as value & 2 persisted,
v2 as value & 4 persisted,
v3 as value & 8 persisted,
v4 as value & 16 persisted,
v5 as value & 32 persisted,
v6 as value & 64 persisted,
v7 as value & 128 persisted
-- ...and so on
)
create index bitfields_idx on bitfields (v0,v1,v2,v3,v4,v5,v6,v7)

现在，您可以根据已为您分离出的位值进行搜索。此表上的value & 9 = 9查询如下所示：

SELECT ...
WHERE v0=1 AND v3=8

您比较vN的值是 2N，因此对于v0它是 1 ，对于v1它是2，对于v2它是4，对于v3它是8，依此类推。

相关文章：