大型事實表和分區鍵困境
我有相當大的事實表（20 億條記錄，大約 120 GB）。此表未分區，查詢響應非常慢。我打算對錶和索引進行分區。該表有一個標識列，它是主鍵，上面有一個聚集索引。上面還有其他非聚集索引，但我不會在這裡詳細介紹。我試圖分區的列不是主鍵的一部分，但不是空的，這給我帶來了一個小小的困境。我有兩個選擇。
- 我將此列添加為主鍵的一部分，即復合主鍵。由於第一列是標識，因此組合始終是唯一的，這意味著我不必擔心應用程序訪問表。聚集索引將自動分區對齊，其他索引也可以分區對齊。
- seconds 選項是刪除標識列上的聚集索引並使其成為唯一的非聚集索引。該索引不能分區對齊，因為分區鍵不是它的一部分，因此必須位於一個驅動器上。然後在可以分區對齊的分區鍵列上創建一個聚集索引，以及所有其他非聚集索引。
我們的 DBA 支持第二種選擇，因為他不想更改主鍵。我擔心選項 2 中的性能影響，因為索引不是分區對齊的。
我將不勝感激任何回饋以及您在這種情況下會使用的任何其他方法。
我不想說出顯而易見的事實，但我想說的是測試這兩個場景並針對所有 3 個場景執行您的生產查詢（場景 1 是目前未分區的場景）。我之所以這麼說是因為我不知道您的程式碼在查詢什麼。它們實際上是否具有按標識列而不是另一列在基表中排序的好處？例如，您的查詢實際上是否經常查找行 ID？如果您不確定，您可能會對獲得一些性能優勢感到驚訝。使用 ID 作為集群鍵的一般想法是用於範圍掃描，但在我們的例子中，我們通過 customerID 和 Date 進行掃描，所以它對我們來說非常完美，也許對你來說也是如此。查看 Kim Tripp 的這篇文章： http ://www.sqlskills.com/blogs/kimberly/post/The-Clustered-Index-Debate-Continues.aspx
“通常被稱為 IDENTITY PRIMARY KEY 聚集索引定義的“原因”是其單調性，從而最大限度地減少頁面拆分。但是，我認為這是定義聚集索引的唯一“原因”，並且是列表中最糟糕的原因。頁面拆分由適當的 FILLFACTOR 管理，而不是增加 INSERTS。範圍掃描是評估聚集索引鍵定義時最重要的“原因”，而 IDENTIties 不能解決這個問題。此外，雖然對 IDENTITY 代理鍵進行集群會最小化由於其單調性質，頁面拆分和邏輯碎片，它不會減少 EXTENT FRAGMENTATION，這可能會導致與頁面拆分一樣有問題的查詢性能。簡而言之，參數執行淺“
通常，您希望聚集索引盡可能窄、唯一且不可為空，因為它被攜帶到所有其他索引中。我有一個大約有 100 億行的表，我們對 datetime 列進行了分區，效果很好。
在很多情況下，日期或日期鍵是事實表集群和分區的最佳選擇。
如果您經常按日期範圍進行掃描，包括標準中的日期，這可以很好地工作。通過對日期進行分區並通過更改它來對齊聚集索引（是的，這對 DBA 來說有點工作，但並不值得。我實際上喜歡很多表中的身份聚集索引和代理鍵，但事實上表我傾向於使用日期值。特別是當您對事實表的大多數查詢中都包含日期時，並且在很多情況下它是……例如，按日期進行的大量範圍掃描）你真的可以在工作期間利用分區消除。假設您通常按月查詢，按月分區和對齊可能會消除除您查詢的月份之外的所有分區。現在您正在沿著那個分區而不是整個大表進行掃描。
你真的需要像 SQL-Learner 所說的那樣進行測試.. 但是日期通常在插入中單調增加.. 如果您更改了集群鍵（真的沒有理由不更改集群鍵.. 您是否曾經使用過該身份你的事實表上有什麼嗎？
如果這是一個維度，那麼我將進行不同的討論。在這種情況下，我會問您通過哪個鍵將維度與事實表聯繫起來。它可以是自然鍵或代理鍵，在這種情況下，標識列將是維度的精細聚集鍵。事實表有點不同。在大多數倉庫應用程序中，事實表通常不會通過事實表的鍵連接到其他表，而是通過它們的鍵連接到維度。日期聚集索引至少是在倉庫事實表中嘗試的好方法。