索引這個非常大的表的最佳方法

我有下表

CREATE TABLE DiaryEntries
(
[userId] [uniqueidentifier] NOT NULL,
[setOn] [datetime] NOT NULL, -- always set to GETDATE().
[entry] [nvarchar](255) NULL
)

每個使用者每天將插入大約 3 個條目。將有大約 1'000'000 個使用者。這意味著該表中每天有 3'000'000 條新記錄。一旦記錄超過 1 個月，我們就會將其刪除。

大多數查詢都有以下 WHERE 子句：

WHERE userId = @userId AND setOn > @setOn

大多數查詢返回不超過 3 行，除了一個返回本月內插入的所有行（最多 90 行）。

插入記錄後，無法更改日期和使用者 ID。

現在我的問題是 - 如何最好地安排這個表的索引？我堅持兩種選擇：

1. (userId, setOn) 上的聚集索引 - 這會給我快速搜尋，但我擔心頁面拆分過多，因為我們將插入很多中間值（相同的 userId 但不同的日期）。
2. (userId) 和 (setOn) 上的非聚集索引 - 這也會導致 (userId) 索引上的頁面拆分（但它和第一個選項一樣昂貴嗎？）。因為我們使用的是 NC 索引，所以搜尋速度變慢了。
3. 附加列 (id) 上的聚集索引和 (userId, setOn) 上的非聚集索引 - 這將消除數據表的頁面拆分，但仍會導致一些 NC 索引。這個選項也不是搜尋的最佳選擇，因為我們使用 NC 索引進行搜尋。

你有什麼建議？還有其他選擇嗎？

PS - 感謝您的時間。

---

經過2天的思考，我想出了一個不同的解決方案來解決這個問題。

CREATE TABLE MonthlyDiaries
(
[userId] uniqueidentifier NOT NULL,
[setOn] datetime NOT NULL, -- always set to GETDATE().

[entry1_1] bigint NULL, -- FK to the 1st entry of the 1st day of the month.
[entry1_2] bigint NULL, -- FK to the 2nd entry of the 1st day of the month.
[entry1_3] bigint NULL,
[entry2_1] bigint NULL,
[entry2_2] bigint NULL,
[entry2_3] bigint NULL,
...
[entry31_1] bigint NULL,
[entry31_2] bigint NULL,
[entry31_3] bigint NULL,
PRIMARY KEY (userId, setOn)
)
CREATE TABLE DiaryEntries
(
[id] bigint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[entry] nvarchar(255) NOT NULL
)

基本上，我將 31 天分組為一行。這意味著我每個使用者每月只插入一次新記錄。這將頁面拆分從每個使用者每天 3 次減少到每個使用者每月一次。顯然有缺點，這裡有一些

• 行大小很大 - 但是在 99.999% 的時間裡，我只從 MonthlyDiaries 中查詢一行。
• 我使用的空間可能比我需要的多，因為有些日子可能沒有條目。沒什麼大不了的。
• 要查找特定日期的條目，需要在 DiaryEntries 上進行額外的索引查找。我相信這不會是一個很大的成本，因為我檢索的行數不超過 90 行，並且在 80% 的情況下我只檢索了 1 行。

總的來說，我認為這是一個很好的權衡：從 3 頁拆分/天/使用者減少到只有 1 頁拆分/月/使用者，但作為回報，我的搜尋速度稍微慢了一點。你怎麼看？

我假設您有充分的理由使用 guid 作為 id。

碎片化主要是掃描的問題，而不是搜尋。分片對預讀有很大影響，並且不使用也不需要預讀。列選擇不佳的未分段索引的性能總是比具有良好、可用列的 99% 分段索引更差。如果您已經描述了掃描表的 DW 報告樣式查詢，那麼我會建議專注於消除碎片，但是對於您描述的負載，專注於高效（覆蓋）搜尋和（小）範圍掃描更有意義。

鑑於您的訪問模式始終由@userId 驅動，這必須是聚集索引中最左邊的列。我還將 setOn 添加為聚集索引中的第二列，因為它在大多數查詢中添加了一些邊際值（我說邊際值是因為 @userId 非常有選擇性，最壞的情況是來自 9000 萬條記錄中的 90 條記錄，額外的過濾由@setOn 並不重要）。我不會添加任何非聚集索引，從您描述的查詢中不需要任何。

唯一的問題是刪除舊記錄（保留 30 天）。我建議不要使用二級 NC 索引來滿足這一點。我寧願部署帶有滑動視窗的每週分區方案，請參閱如何在 SQL Server 2005 上的分區表中實現自動滑動視窗。使用此解決方案，舊記錄由分區切換刪除，這可能是最有效的方法。每日分區方案將更準確地滿足 30 天的保留要求，也許值得嘗試和測試。我不太願意直接推薦 30 個分區，因為您描述了一些有可能在每個分區中尋找特定 @userId 記錄的查詢，並且 31 個分區可能會在重負載下產生性能問題。更好地測試和測量。

引用自：https://serverfault.com/questions/87061