“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操作）

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

?

select wait_type, count(*) as num_waiting_tasks, sum(wait_duration_ms) as total_wait_time_ms from sys.dm_os_waiting_tasks where session_id>50 group by wait_type order by wait_type

?

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

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

?

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

　　

declare @i int set @i = 1 while @i <=10000 beginif @i %5000 = 0beginif (@@TRANCOUNT>0)beginCOMMIT TRANBEGIN TRANendend insert into Insert_Test2 select 'aaa'set @i =@i + 1 end if (@@TRANCOUNT>0) commit tran

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

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

轉載于:https://www.cnblogs.com/SQLServer2012/archive/2013/01/30/2882815.html

總結

以上是生活随笔為你收集整理的SQL Server Insert 操作效率（堆表 VS 聚集索引表）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。