特定查詢的聚集索引改進

我找到了一個看起來像這樣的表：

CREATE TABLE [dbo].Table1 (  
 id INT primary key IDENTITY (1, 1),  
 [idUser] INT  NOT NULL ,  
 [Amount] INT NOT NULL ,  
 [Attempts] INT NOT NULL ,  
 [date] [datetime] NOT NULL ,  
 [SUM_Amount] INT NOT NULL   
) ON [PRIMARY]  

此表由作業創建並填充特定時期的聚合數據。

特點：

• 該表最多可容納一百萬行
• idUser 是唯一的
• sum_Amount 是前幾行金額的總和。

該表將保持原樣，沒有更新或刪除或插入操作。只是這種類型的查詢：

select top (@n) * from table1
order by [SUM_Amount] desc, [Attempts] desc

select top (@n) * from table1
where [SUM_Amount] >=@m order by [SUM_Amount] asc 

我認為更改為這樣的聚集索引會提高性能：

CREATE TABLE [dbo].Table2 (  
 id INT IDENTITY (1, 1),  
 [idUser] INT  NOT NULL ,  
 [Amount] INT NOT NULL ,  
 [Attempts] INT NOT NULL ,  
 [date] [datetime] NOT NULL ,  
 [SUM_Amount] INT NOT NULL  

CONSTRAINT [PK_Nueva]
  PRIMARY KEY CLUSTERED ([SUM_Amount] desc, [Attempts] desc, id asc)   

) ON [PRIMARY]  

我讀到使用無唯一聚集索引會添加一個 4 字節隱藏列（http://msdn.microsoft.com/en-us/library/ms190639(v=sql.90).aspx），所以我決定添加集群索引的身份（不確定它是否是正確的方法）

我想問（冒著聽起來荒謬的風險，但需要確定）：

• 如何改進？
• 我會影響磁碟大小嗎？
• 插入所有數據後是否應該重建索引？

編輯：

關於id，我認為這只是一個壞習慣。我會保留它，不確定以前的工作如何計算執行總數（我無權訪問它）

有很多這樣的桌子，每天都有數百張（不要問我為什麼）。這就是 DBA 團隊因為大小問題而要求我不要創建新索引的原因。這就是為什麼我考慮通過聚集索引重新排列表結構的原因。還會更改超出正常範圍的數據類型。

是的，按照您的建議放置聚集索引將為這兩種特定類型的查詢提供出色的查詢性能，但對於該表上的大多數其他 SELECT 查詢可能會造成災難。我會提供一個替代解決方案：

我建議在一個唯一的列上添加一個基本的聚集索引，例如idUser：

ALTER TABLE dbo.Table1 ADD
   PRIMARY KEY CLUSTERED (idUser)
       WITH (FILLFACTOR=100);

…然後專門為您的查詢建構一個單獨的非聚集索引：

CREATE INDEX IX_Table1_SUM_Amount
   ON dbo.Table1 (SUM_Amount DESC, Attempts DESC)
   WITH (FILLFACTOR=100);

聚集索引（主鍵）不會為您的表添加任何空間 - 它只是通過對二叉樹中的行進行預排序來組織儲存的另一種方式，這將使訪問表的速度更快。

非聚集索引會佔用一些空間，但相對較小：兩者SUM_Amount都是Attempts整數值，因此它們每個都有 4 個字節大，這意味著您的非聚集索引最終應該大約為 12 MB，包含一百萬行（包括“集群鍵”，它也是一個整數）。

您的兩個範例查詢將使用非聚集索引來查找最大/最小值，這將Key lookup在執行計劃中生成一個運算符，但是因為您使用的是TOP (@n)，我認為您甚至不會注意到它的性能成本. 如果您絕對需要更好的性能並且想要消除Key lookup，您可以INCLUDE ()將非聚集索引中的所有相關列形成所謂的覆蓋索引，有效地使索引成為原始表的排序副本：

CREATE INDEX IX_Table1_SUM_Amount
   ON dbo.Table1 (SUM_Amount DESC, Attempts DESC)
   INCLUDE (id, idUser, Amount, date)
   WITH (FILLFACTOR=100);

如果數據沒有更改，則無需重建索引，尤其是在單個批次中填充整個表的情況下。但是，如果該表已隨著時間的推移填充，您可能會看到一些碎片。

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