SQL Server Insert 操作效率（堆表 VS 叢集索引表）

　　“SQL Server的Insert操作在堆表或者叢集索引表的時候，哪個效率更高？為什麼高？”

　　之前有同事問過我這個問題，為了確保日誌庫的記錄效率，於是我做了簡單測試了，首先要先強調幾點概念：

 

　　堆表：沒有叢集索引的表，記錄通過IAM頁以及PFS頁來確定哪頁有空閑空間。

　　叢集索引表：有叢集索引的表，記錄是根據聚集索引值所在頁的索引值邏輯順序維護的

 

Demo：如下

　　分別對堆表和聚集表進行5個並發線程，每個線程各10000次迴圈插入

　1.　　堆表測試

--1.    建立一張堆表create table Insert_Test (id int identity, name char(200))go

堆表Insert用時：34.127秒

2.　　叢集索引表測試

create table Insert_Test2 (id int identity primary key clustered, name char(200))go

叢集索引表Insert用時：22.885秒

結果：叢集索引的插入速度比堆表要快10秒以上（個人機器配置不同，時間差異也會高或低，我的本子效能較低）

 

分析

 

　　堆表插入：

　　每一次insert，總是被插入到表的任意可用空間上，通過IAM找到檔案中的哪段區間屬於目標表，通過PFS頁找出這些區間內的哪些頁面有可用空間，如果頁面沒有可用空間，需要通過GAM頁和SGAM頁尋找將分配的某個表的可用性區域間。

　　叢集索引：

　　由於我的聚集鍵為自增id列，所以每次插入都將集中在最後一個資料頁上。

　　總體來說：由於堆表插入的行的目標位置沒有定義，因此確定在堆表中哪裡放置行通常比在有叢集索引的表中放置行的效率低。

 

叢集索引表Insert的弊端

　　根據上面分析，叢集索引為自增列時，最後的資料頁會成為集中insert的目標頁，因此會成為熱點，通時，SQL Server 使用閂鎖，所以預測大並發insert操作會在最終頁產生資源阻塞，實測確實如此：

　　（200個並發線程，每個線程執行100次insert操作）

執行過程中，查看等待資源情況

 

selectwait_type,count(*) as num_waiting_tasks,sum(wait_duration_ms) as total_wait_time_msfrom sys.dm_os_waiting_taskswhere session_id>50group by wait_typeorder by wait_type

 

　　和預測情況一樣，98個請求在等待閂鎖資源。

　　那麼，推斷如果使用guid作為主鍵，插入時會分散各個資料頁面，進而將熱點頁平鋪開，這點確實有效果，但是拆分頁的成本會相當的高，拆分頁也是非常損傷效能的。

 

　　繼續補充個情況，假如你需要長期大量insert操作，不如採用batch，效果會更快，將上面的指令碼改為如下：

　　

declare @i int set @i = 1while  @i <=10000begin    if @i %5000 = 0    begin          if (@@TRANCOUNT>0)          begin              COMMIT TRAN              BEGIN TRAN          end    end     insert into Insert_Test2 select 'aaa'    set @i  =@i + 1endif (@@TRANCOUNT>0)commit tran

單次執行從原先的8秒降為3秒，有興趣的朋友可以自己測試

　　原因簡單說下，Insert操作時需要進行預寫記錄檔的步驟，每個單獨的insert操作都要寫一遍ldf檔案，這樣的效能很低，如果每5000條insert包含在一個事務中後提交，它把很多小的transaction合并成一個大的合適的 transaction來減少磁碟寫操作，從而獲得極大效能提升。Batch size究竟多大才是最佳的呢？這個取決您的機器，需要你自己測試。