时间戳

首页 > 解决方案 > 外键连接加范围条件的最佳索引

sql-server - 外键连接加范围条件的最佳索引

问题描述

我有几个表，其中包含 30+ 百万条记录，我需要使用以下结构对其执行分页查询：

CREATE TABLE A (
    ID INT NOT NULL,
    [DATE] DATETIME NOT NULL,
    PRIMARY KEY (ID, [DATE]) --Clustered
)

在处理这种大小的数据时，无法使用OFFSET FETCH，因为 sql server 需要首先读取所有记录，直到它到达我想要的“页面”。

因此，对于每个表，我都有一个创建的辅助“索引”表，该表有一个ROWNUMBER列，ROW_NUMBER()用于对记录进行排序：

CREATE TABLE A_Index (
    ROWNUMBER INT NOT NULL,
    ID INT NOT NULL,
    [DATE] DATETIME NOT NULL,
)

使用这个索引表，我们执行我们的分页查询，如下所示：

SELECT A.*
FROM A
INNER JOIN A_Index ON A.ID = A_Index.ID AND A.[DATE] = A_Index.[DATE]
WHERE A_Index.ROWNUMBER > 10000000 AND A_Index.ROWNUMBER <= 10100000

所以我的问题是，在这个“索引”表上创建的最佳索引是什么？我选择了 CLUSTERED 索引，但无法确定该ROWNUMBER列应该是索引中的第一列还是最后一列。我查看了两者的查询计划并得到了好坏参半的结果。

--Option 1: ROWNUMBER is the first column in the index:
CREATE CLUSTERED INDEX IDX_ROWNUMBER ON A_Index(ROWNUMBER, ID, [DATE])

--Option 2: ROWNUMBER is the last column in the index:
CREATE CLUSTERED INDEX IDX_ROWNUMBER ON A_Index(ID, [DATE], ROWNUMBER)

使用选项 1，至少在我看来，我已经看到不必要的 ID 和 DATE 列的排序。而选项 2 通常需要更长的时间才能开始返回结果......

谢谢。

标签： sql-serverindexing

解决方案

我将建议两个可能的指标，然后讨论每个指标何时更有意义。第一个索引：

CREATE INDEX idx1 ON A_Index (ID, [DATE], ROWNUMBER);

该索引将加快连接回原始表的速度。ROWNUMBER对于每个连接的记录（来自两个表），然后可以使用第三个索引列来满足WHERE子句中的范围条件。这是可行的，因为ROWNUMBER它将按升序排序，因此 SQL Server 只需扫描某个范围。

第二个指标：

CREATE INDEX idx2 ON A_Index (ROWNUMBER, ID, [DATE]);

该索引首先在 上拆分ROWNUMBER，这将使 SQL Server 将索引范围缩小到仅具有匹配行号值的那些记录。在此之后，它将扫描索引以建立连接条件。

如果您希望行号范围相对较窄，例如只有几十或几百个值，则第二个索引会更有意义。在这种情况下，第二个索引将丢弃几乎所有的表，只留下几个要扫描的值。

如果您希望行号范围相当大，第一个索引会更有意义。在这种情况下，加速连接将比扫描行号值具有更高的优先级。

推荐阅读