SQL Server：聚集索引、排序和分頁

在我的應用程序中，有幾次我必須顯示按某個欄位進行分頁和排序的結果。

例如一個按姓氏排序的簡單使用者列表。正因為如此，並且因為我也有邏輯刪除並且它是一個多租戶應用程序，所以我通常使用這樣的 CLUSTERED INDEX：

CREATE CLUSTERED INDEX [idx] ON [Users]
(
    IsDeleted ASC,
    [AccountId] ASC,
    [LastName] ASC
)

這意味著像分頁一樣的查詢SELECT TOP(20) * FROM Users WHERE IsDeleted = 0 AND AccountId = xxx是按姓氏排序的。我知道它不能保證被排序，但在實踐中它總是如此。

然而，在這裡閱讀關於聚集索引的 Kimberly Tripp 部落格文章時，她說這樣做是一個可怕的想法。更糟糕的是，IsDeleted (BIT) 欄位不允許我設置

但是，如果我將 CLUSTERED INDEX 更改為唯一 ID，我需要開始使用ORDER BY LastName，這實際上非常慢。

我的表有幾百萬條記錄（最多幾千萬條），一般使用如下：

• 查詢數據。大多數時候。
• 批量更新/插入，其中僅修改的數據在子集下IsDeleted = 0, AccountId = xxxx（僅批量更新單個帳戶的未刪除數據）。

題：

這些表的推薦索引（以及如何排序）是什麼？

此類表的另一個範例是具有以下列的調查結果表，IsDeleted (BIT), AccountId (FK GUID), UserId (FK GUID), QuestionKey (NVARCHAR), AnswerValue (TEXT)我的 CLUSTERED KEY 可能在其中，(IsDeleted, AccountId, UserId, QuestionKey)並且 99% 的時間我會查詢該表或通過前 3 個欄位批量更新它

WHERE IsDeleted = 0 
AND AccountId = xxx 
AND UserId = yyy

甚至是 4 個欄位：... AND QuestionKey = 'country'

編輯：

我這樣做的主要原因之一是因為批量更新和查詢總是限制在 1 頁或少量頁面。擁有一Identity列需要查詢和更新才能在大多數頁面中讀/寫。

編輯2：

以 Joe Obbish 為例：

這個查詢：

SELECT TOP (20) * 
FROM Users2
WHERE IsDeleted = 0 
AND AccountId = '46FC5693-7446-415A-8626-8937365460D1'
ORDER BY [LastName];

• 在(IsDeleted, AccountId, LastName)結果中有 1 個群索引：

CPU 時間 = 3 毫秒，經過的時間 = 3 毫秒。

表“使用者 2”。掃描計數 1，邏輯讀取 5，物理讀取 4，預讀讀取 0，lob 邏輯讀取 0，lob 物理讀取 0，lob 預讀讀取 0。

• 在新的 PK ID (NEWID()) 列中有一個集群索引（這樣數據在內部隨機排序）和一個非集群(IsDeleted, AccountId, LastName)結果：

CPU 時間 = 16 毫秒，經過時間 = 18 毫秒。

表“使用者 2”。掃描計數 1，邏輯讀取 533，物理讀取 5，預讀讀取 1240，lob 邏輯讀取 0，lob 物理讀取 0，lob 預讀讀取 0。

注意 IO 和時間。如果數據沒有儲存在一起，它會更慢並且需要更多的 IO。它可能需要更多空間，但速度差異是顯著的。我這樣做有錯嗎？

首先，我需要重申RDFozz在他的回答中所說的包含明確的ORDER BY. 如果 SQL Server 執行分配順序掃描，您可能會得到錯誤的結果。包含ORDER BY在查詢中不會導致性能下降。為什麼不這樣做？

從查詢性能的角度來看，您想要的索引取決於您一次返回多少行以及表中實際需要多少列。

首先，我將大約 650 萬行放入一個有六個租戶的表中：

CREATE TABLE dbo.Users2 (
   IsDeleted Bit NOT NULL,
   [AccountId] UNIQUEIDENTIFIER NOT NULL,
   [LastName] NVARCHAR(50) NOT NULL,
   [UsefulColumn] NVARCHAR(20) NOT NULL,
   [OtherColumns] NVARCHAR(100) NOT NULL
);

CREATE CLUSTERED INDEX [idx] ON [Users2]
(
    IsDeleted ASC,
    [AccountId] ASC,
    [LastName] ASC
);

CREATE TABLE #ids (id INT NOT NULL IDENTITY (0, 1), [AccountId] UNIQUEIDENTIFIER NOT NULL);
INSERT INTO #ids
SELECT TOP 6 NEWID()
FROM master..spt_values;

INSERT INTO [Users2] WITH (TABLOCK)
SELECT 
CASE WHEN t1.number % 10 = 1 THEN 1 ELSE 0 END
, #ids.[AccountId]
, LEFT(REPLACE(CONVERT(NVARCHAR(50), NEWID()), '-', ''), 12)
, REPLICATE(N'Z', 20)
, REPLICATE(N'Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
LEFT OUTER JOIN #ids ON ABS(t1.number % 6) = #ids.id;

DROP TABLE #ids;

-- get an ID: FFA7D6D8-63E8-422B-B5E7-F7020871CDB4
SELECT TOP 1 [AccountId] FROM Users2
WHERE IsDeleted = 0
ORDER BY [AccountId] DESC;

執行類似於您的查詢時：

SELECT TOP (20) * 
FROM Users2
WHERE IsDeleted = 0 
AND AccountId = 'FFA7D6D8-63E8-422B-B5E7-F7020871CDB4'
ORDER BY [LastName];

我得到了預期的聚集索引搜尋：

使用聚集索引是最好的選擇嗎？這取決於。如果您不需要從表中選擇每一列，那麼您可以定義一個較小的覆蓋索引，它無需顯式排序即可返回您需要的數據。當您進一步分頁到數據中時，具有較小的覆蓋索引有利於性能。假設您只需要UsefulColumn而不需要OtherColumns列。您可以定義以下索引：

CREATE NONCLUSTERED INDEX [idx_1] ON [Users2]
(
    [AccountId] ASC,
    [LastName] ASC
) 
INCLUDE ([UsefulColumn]) 
WHERE IsDeleted = 0;

它相當大。對於我的測試案例，它大約是數據大小的 28%。對於此索引，需要注意的是，更改表的聚集鍵不會對其大小產生很大影響。SQL Server 將聚集鍵列儲存在索引的葉節點中，除非它們已經包含在索引中。這可以通過一個簡單的測試來證明：

CREATE TABLE dbo.IX_TEST (
   COL1 BIGINT NOT NULL,
   COL2 BIGINT NOT NULL,
   FILLER VARCHAR(6) NOT NULL,
   PRIMARY KEY (COL1)
);

INSERT INTO dbo.IX_TEST WITH (TABLOCK)
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 6)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

EXEC sp_spaceused 'IX_TEST'; -- 96 KB

CREATE INDEX COL1 ON dbo.IX_TEST (COL1)
EXEC sp_spaceused 'IX_TEST'; -- 14032 KB

CREATE INDEX COL2 ON dbo.IX_TEST (COL2)
EXEC sp_spaceused 'IX_TEST'; --- 35920 KB

回到我們的表，如果我只在UsefulColumn列上創建索引，那麼列的成本（沒有壓縮）大約為 1 個字節IsDeleted， 16 個字節AccountId， 2 * 列的平均長度LastName，0 或4 個字節用於內部唯一符（僅適用於重複的姓氏）。對於我的測試數據來說，這是相當多的成本：

1 + 16 + 2 * 12 + 0 = 41 字節

然而，對於idx_1我在上面定義的索引，它大約只有 1 個字節（1 代表IsDeleted和 0 代表唯一性，假設不是很多重複的姓氏）。索引主要是因為我使用寬列作為鍵列。請記住，索引將與您目前的集群鍵大小相同，但是將表的集群鍵更改為更薄的列集將大大減小僅定義在 上的索引的大小UsefulColumn。

對於任何TOP值，我都應該在覆蓋索引上獲得一個不錯的索引搜尋。這個查詢：

SELECT TOP (200000) [LastName], [UsefulColumn] 
FROM Users2
WHERE IsDeleted = 0 AND AccountId = 'FFA7D6D8-63E8-422B-B5E7-F7020871CDB4'
ORDER BY [LastName];

有以下計劃：

即使您需要表中的每一列，上面定義的索引仍然可以提供足夠好的性能。您將獲得返回的每一行的鍵查找。對於單個最終使用者，在 20 行上進行鍵查找應該不會引起注意。在您的測試中，您看到執行時間分別為 3 毫秒和 18 毫秒。但是，如果您有一個高度並發的工作負載，它可能會有所作為。只有您可以正確評估。

即使沒有上述注意事項，在選擇多行時，您可能會注意到很大的性能差異：

第一個查詢有一個索引提示來強制使用索引。查詢優化器認為該計劃將比使用聚集索引的計劃昂貴得多。

如果我不得不猜測，我會說您可能不需要使用聚集索引來執行分頁。如果您的表上有很多索引，您可能會受益於為分頁查詢創建新的覆蓋索引以及在較小的唯一列集上定義聚集索引。

引用自：https://dba.stackexchange.com/questions/175788