Sql server Insert執行的秘密(下) 帶外鍵的INSERT分析

上一篇文章介紹了一個最簡單INSERT語句的執行計畫詳細情況，這一篇分析一下帶外鍵表的INSERT的例子。
 
本文所用的資料表結構如所示；其中Blog表上BlogID是自增的主鍵，並在CreateUserID和CreateTime列上分別建有兩個非唯一索引。

我們要往Blog表中插入一條資料，並分析其執行情況。
INSERT 語句如下：

INSERT INTO [DB_Cn].[dbo].[Blog]           ([Title]           ,[Tags]           ,[Content]           ,[CreateUserID]           ,[CreateTime]           ,[IP])     VALUES           ('這是一個測試部落格標題'           ,'測試'           ,'這是測試部落格的內容,博主的地址是http://www.cnblogs.com/yukaizhao/'           ,100           ,'2010-01-06'           ,'127.0.0.1');

其執行計畫要稍微複雜一些，如下所示
 

從右向左分析，第一步中的常量掃描是根據使用者輸入的sql語句產生一個資料行；第一個常量掃描產生了一個新的自增長id；第二個計算標量則是計算使用者輸入的sql語句中的常量值，這些在上一篇文章中有詳細的敘述，請參考上文。
第四步是分叉的兩步操作，上面的操作是叢集索引插入，下一步的操作是對User表的叢集索引尋找，如是叢集索引插入的詳細情況：
 
邏輯索引插入的部分估計開銷為90%，這一步插入Blog表的主鍵，Blog表的兩個索引IX_Blog和IX_Blog_CreateTime，對這兩個索引的操作說明了在表中建索引會對錶的插入操作效率產生負面影響；由於Blog表的CreateUserID欄位是個外鍵，所以這一步還有一個輸出資料行表輸出了CreateUserID欄位；這個欄位要用來做外鍵是否存在的判斷。

我們再看下對User表的叢集索引尋找操作的詳細情況：
 
這步中尋找的對象是PK_User及User表的主鍵，主鍵的掃描是非常迅速的，儘管如此當User表非常大時，掃描的開銷也是非常可觀的。這裡掃描的開銷可以分為兩個部分，一部分是cpu的開銷，另外一個方面是掃描時sql server會自動給主鍵加上一個共用鎖定，既然加鎖就有可能會造成死結或獨佔鎖定的等待。

從這一步看如果我們對響應速度的要求遠大於對資料一致性的要求時，可以考慮刪掉外鍵，去掉這一步不必要的開銷。

第五步：對第四步兩個分叉操作產生的輸出進行嵌套迴圈，這一步嵌套迴圈是為下一步的Assert做準備
第六部：Assert判斷嵌套迴圈產生的CreateUserID是否為NULL，如果為NULL則會引發外鍵不存在的異常
最後一步執行INSERT操作。

從以上分析可以得出幾點心得
1. 為什麼使用自增長欄位，在插入資料失敗時自增長欄位的編號會被佔用？
因為自增長欄位的值是在第二部計算標量是產生的，這一步已經將自增id加1了

2. 為什麼給表建的索引多了會影響插入的效能
因為每一次插入都需要對每一個索引進行插入

3. 為什麼在做大並發設計時，會不建外鍵，或將外鍵刪除掉
因為外鍵會帶來額外的cpu開銷和鎖資源的開銷

相關隨筆：Sql server Insert執行的秘密(上)一個最簡單的INSERT分析