如何選擇聚集鍵值的最佳實踐是什么？一個好的聚集鍵值應該有下列屬性：

• 范圍小的（Narrow）
• 靜態的（Static）
• 自增長的（Ever Increasing）

我們來具體看下所有這3個屬性，還有在SQL Server里為什么自增長值實際上是不會擴展的。

聚集鍵值應該i越小越好。為什么？因為它要占用空間，聚集鍵值也在每個非聚集索引的葉子曾作為邏輯指針。如果你的聚集鍵值很廣，你的非聚集索引也會很大。如果你定義了非唯一非聚集索引（Non-Unique Non-Clustered Index）（基本上是這個情況），聚集鍵也是你非聚集索引導航結構的一部分。因此你的索引會變得很大。我們的目標是最小化我們的索引。因為我們要為此承擔更多的物理存儲，緩存池，這些都是SQL Server從存儲緩存讀取的索引頁的地方。

一般我們會選擇技術性鍵值（technical key value）（像INT/BIGINT數據類型），而不是自然鍵值（natural key value）。當我也看到很多長度有100 bytes甚至更長的聚集鍵值（包含LastName, FirstName, SocialSecurityNumber等）。相信我——你這是在浪費內存！沒有必要這樣做。選擇一個技術性鍵值就可以了。

因為聚集鍵值在每個非聚集索引里都會復制一份，你的聚集鍵值應該從不改變！不然SQL Server需要經常維護，去更新執行計劃里每個在你表上定義的非聚集索引。你再次引入了你不需要的額外計算。把你的CPU用在其它重要的事情上。我們都知道，自然鍵值是會改變的（例如LastName列，當你結婚了就會改變）。

技術性鍵值（像INT IDENTITY）不會改變（默認）。因此在你非聚集索引里的邏輯指針（聚集鍵值格式）保持穩定——永遠沒有必要修改他們！

“好”的聚集鍵值第3個重要屬性是選擇列應該給你自增長的值。為什么？因為你總是在你聚集索引的末尾增加額外記錄，因此你可以避免昂貴的分頁（Page Splits）（涉及到CPU周期，事務日志等問題）和索引碎片。使用像INT IDENTITY自增長值列，在99%的情況下是沒有問題的，但還是有些情形，這個方法會導致嚴重的擴展性問題。假設你有個工作量，那里有很多不同用戶用自增長聚集鍵值對同個表永久插入鍵值。想下日志/審計表（Logging/Auditing Table）。

我們來仔細看下當你在內存里讀寫頁時，在SQL Server內部會發生什么。當SQL Server訪問特定內存機構（像存儲在緩存池里的頁）時，這些內存訪問必須被多個線程上同步。你不能在內存里并發寫入同個頁。當一個線程寫入一個頁時，其他一些線程同時就不能讀這個頁。另外并發編程你用互斥器（Mutexes）解決那個問題——像臨界區（Critical Section）。一些代碼路徑是人為互斥的。閂鎖（latches）用來在線程/查詢間的同步。每次當你讀一個頁，工作線程需要獲得共享鎖（Shared Latch（SH）），每次當你寫一個頁，工作線程需要獲得排它閂鎖（Exclusive Latch（EX））。而且這些閂鎖彼此是不兼容的。

當你進行INSERT語句時，工作線程在INSERT語句發生的頁獲得排它閂鎖。同時沒有線程可以從這個頁讀寫。使用自增長聚集鍵值這個方法實際上不會擴展，因為你在你聚集索引的末尾插入你的記錄。因此你的并行線程/查詢在你聚集索引里同個最后頁為閂鎖競爭。作為一個副作用SQL Server會連續執行你的INSERT語句——一個接著一個INSERT，你就碰到了著名的最后頁插入閂鎖競爭（Last Page Insert Latch Contention）。我們來看下面的圖片。

用自增長聚集鍵值的最佳實踐，在聚集鍵的末尾你有一個熱區。你的記錄越小，這里就會有更多的競爭。如果解決那個問題？簡單：把你的INSERT語句擴散到聚集索引的整個B樹結果。有很多方法可以實現這個：

• 使用隨機聚集鍵值（像UNIQUEIDENTIFIER）。但要意識到副作用：在每個非聚集索引里邏輯指針更大，頁分裂問題……
• 實現哈希分區（Hash Partitioning），如果你使用企業版本的SQL Server。
• 使用SQL Server 2014里內不能優化表OLTP來剔除閂鎖。
• 使用逆向索引（Reverse index），很遺憾SQL Server并不提供你像Oracle那樣的內建索引。你也可以自己寫一個的……

 1 CREATE FUNCTION BitReverse 2 ( 3     @Input bigint 4 ) 5 RETURNS bigint 6 AS 7 BEGIN 8     DECLARE @WorkValue bigint=@Input 9     DECLARE @Result bigint=0;10     DECLARE @Counter int=0;11     WHILE @Counter<6312     BEGIN13         SET @Result=@Result*214         IF (@WorkValue&1)=115         BEGIN16             SET @Result=@Result+117             SET @WorkValue=@WorkValue-118         END19         SET @WorkValue=@WorkValue/220         SET @Counter=@Counter+121     END22     23     RETURN @Result24     25 END

使用像INT IDENTITY數據類型的范圍小，靜態的，自增長的聚集鍵值99%的情況都沒問題。但在一些有大量并發INSERT語句的情況（日志/審計表（Logging/Auditing Table）），用那個方法你會碰到最后也插入閂鎖競爭（Last Page Insert Latch Contention）。如果你碰到這個特定問題，你就會離開這99%的太平區域，你要保證INSERT語句散布到你的整個B樹結構?；旧夏憔驮谌绻麑⒍嗑€程散步到典型B樹結構做斗爭。

希望這篇文章可以幫助你從內部理解：為什么自增長聚集鍵值會傷及你表的擴展性。

感謝關注。