我读过关于布尔列如何不太充当搜索索引的文章。。但我的问题是…如果聚集索引会影响记录的物理排列难道它不能用来把一种类型的记录放在一起(在同一页中),这样这些页加载到内存中的机会就会减少吗。。我会尝试更好地解释:对于表
[BookPages]
ID(int)
Deleted(Boolean)
Text(Varchar)
如果聚集索引在ID
列上,则示例数据将是
1, true, 'the quick..'
2, false, 'hello w..'
3, true, 'stack m..'
4, false, 'just thin...'
这意味着删除/活动记录是交错的,所以如果我们搜索记录2
SELECT [Text] FROM [BookPages] WHERE [Deleted] = false AND [ID] = 2
"leaf"数据页可能以行(1,2)结尾,这意味着我们正在加载到内存中,记录中有已删除的字段,我们永远不会感兴趣。。但是如果索引在列Deleted,ID
中,则数据现在将是
2, false, 'hello w..'
4, false, 'just thin...'
1, true, 'the quick..'
3, true, 'stack m..'
现在,当SQL加载页面时,我们只针对活动记录时,页面将只包含活动记录。。
因此,在一个有着悠久历史和大量删除记录的数据库上,我们可以在我们想要的记录上有更好的位置,并帮助IO.
在成千上万的页面上,我们可以确保其中的一大块永远不会加载到内存中,并且数据永远只保留在磁盘上。
这个推理正确吗?这会影响(提高)大型数据库的整体性能吗?
分区也有类似的效果。不过,选择聚类键的权重更轻,效果也同样好。
通常,在自动递增的数字上聚类也具有良好的局部性,因为IDENTITY
值与年龄相关,而年龄与使用频率相关。
同样的优化并不直接应用于非聚集索引。您也可以为它们使用布尔前缀,但您需要以可搜索的形式提供它:
WHERE SomeNCIndexCol = '1234' AND Deleted IN (0, 1)
SQL Server不够聪明,无法自己解决这个问题。它不能像Oracle那样"跳过"第一个索引级别。因此,我们必须手动提供查找键。(连接项目:https://connect.microsoft.com/SQLServer/feedback/details/695044)
另一个问题是写性能。将一行标记为已删除(SET Deleted = 1
)现在需要为CI添加一个物理删除+插入对,再为每个NCI添加一个。大多数ORM不支持主键更改,因此您可能不应该将该集群键设置为主键。
顺便说一句,在位列上创建索引还有其他用例。如果99%的值为零或一,则可以使用索引执行查找和关键字查找。您也可以使用这样的索引进行计数(或对位列进行分组)。
在具有两个或几个可能值的列上创建索引实际上会适得其反。对布尔列进行聚类也可能不明智,因为您可能希望将其保存为其他经常查询的列。例如,CustomerName。如果您的数据库服务器支持分段,您可以在逻辑上将访问次数最少的行放在一个单独的表中,其中Deleted列的值为false。请参阅以下相关问题/答案。