主页 / dba / 问题 / 254877
Accepted
Artashes Khachatryan
Asked: 2019-12-06 03:20:54 +0800 CST

SQL Server 选择非选择性索引

  • 772

我正在测试 SQL Server 索引并发现非常奇怪的行为。这是我的代码：

DROP TABLE IF EXISTS  dbo._Test
DROP TABLE IF EXISTS  dbo._Newtest
GO
CREATE TABLE _Test(
ID INT NOT NULL, 
UserSystemID INT NOT NULL, 
Age INT
)
GO
INSERT INTO dbo._Test
        ( ID, UserSystemID, Age )

SELECT TOP 10000000 ABS(CHECKSUM(NEWID())) % 5000000, ABS(CHECKSUM(NEWID())) % 2, ABS(CHECKSUM(NEWID())) % 100
FROM sys.all_columns
CROSS JOIN sys.all_objects a
CROSS JOIN sys.all_objects b
CROSS JOIN sys.all_objects c

; WITH cte AS (
SELECT ID, UserSystemID,  age, ROW_NUMBER() OVER(PARTITION BY ID, UserSystemID ORDER BY GETDATE()) rn
FROM dbo._Test
)

SELECT cte.ID ,
       cte.UserSystemID ,
       cte.Age
INTO _newTest
FROM cte
WHERE cte.rn = 1

CREATE UNIQUE NONCLUSTERED INDEX  IX_test ON dbo._NewTest(ID, UserSystemID) INCLUDE(age)
GO
ALTER TABLE dbo._NewTest ADD CONSTRAINT PK_NewTest PRIMARY KEY CLUSTERED(UserSystemID, ID)
GO

此时，我在同一张表和同一列上有两个索引。第一个是非集群的，第二个是集群的。该Id列更具选择性（大约 5000000 个唯一值）而UserSystemID不是（两个唯一值）。

然后我运行以下查询来测试使用了哪个索引：

SELECT id,  UserSystemID, age   
FROM _NewTest
WHERE id = 1502945
AND UserSystemID = 1

它寻找聚集索引。你可以在这里看到计划。

问题是为什么 SQL Server 更喜欢聚集索引而不是唯一的非聚集索引。

我的聚集索引的前导列比其他唯一非聚集索引的选择性要低得多。所以我希望聚集索引的性能一定会更差，但实际上并非如此。

sql-server index

1 个回答

  1. Best Answer

    给定唯一索引，您的查询将最多选择一行。

    优化器知道它只需要将索引 b-tree 下降一次，并且不需要从该点向前或向后扫描以找到更多匹配项。这被称为单例搜索（对唯一索引的平等测试）。

    当前的索引匹配实现碰巧总是在可以使用单例查找时选择聚集索引。

    这里聚集索引和非聚集索引的选择一般不是很重要。导航 b 树的上层（使用二分搜索或线性插值）可能会产生很小的额外成本，但这甚至很难测量。请记住，非叶子索引页面上只有ID和关键组件。UserSystemID

    有人可能会争辩说，平均而言，更广泛的聚集索引叶页不太可能在内存中。还有一些其他极端情况的后果，但我认为这种行为不会很快改变。

    但是我的聚集索引的前导列比其他唯一非聚集索引的选择性要低得多。所以我希望聚集索引的性能一定会更差，但实际上并非如此。

    选择性对于复合 b 树索引上的相等搜索无关紧要。

    您的唯一聚集复合索引具有键 (UserSystemID, id)。

    要查找 (UserSystemID = 1 和 id = 1502945) 的行，SQL Server 不会查找 UserSystemID = 1 的所有行，然后查找 id = 1502945 的行。那将非常低效。

    您可以使用 来判断您的测试查询涉及多少页SET STATISTICS IO ON。您的示例构建了一个具有两个非叶级别的聚集索引。总而言之，找到您想要的行意味着要接触三页 - 索引的每一级都有一页。

    行在索引中按 UserSystemID 和 id 排序。我的演示表副本在聚集索引的根（顶级）页面上具有以下布局：

    根页面

    在此页面上执行二进制搜索很容易：

    • 从中间行开始。
    • 将 UserSystemID 与您要查找的用户系统ID 进行比较。
      • 如果不相等，以通常的方式继续二分查找（根据需要在前面或后面的行中选择一个新的中点）。
      • 如果在 UserSystemID 上相等，将 id 与您要查找的 id 进行比较，然后继续二进制搜索

    按照这个逻辑，我们将快速找到子（下一个较低级别）索引页面，如果它们存在，则可以在其中找到搜索到的键。在该页面上重复二进制搜索，依此类推，直到我们到达必须包含我们正在查找的行（如果存在）的单个叶级页面。

    • 11

相关问题