SQL Server索引進階：第十三級，插入，更新，刪除

標籤：

在第十級到十二級中，我們看了索引的內部結構，以及改變結構造成的影響。在本文中，繼續查看Insert，update，delete和merge造成的影響。首先，我們單獨看一下這四個命令。

插入INSERT

當向表中插入一行資料的時候，不管表是堆表還是叢集索引表，肯定會在表的索引中插入一個入口，過濾索引除外。這麼做的時候，SQL Server使用索引鍵的值從根頁到葉子層頁，到達葉子層頁之後，檢查頁的可用空間，如果有足夠的空閑空間，新的入口就會被插入適當的位置。

最終，SQL Server可能會試圖向一個已經沒有空間的頁插入入口資訊。這時候，SQL Server就會查詢位置結構，找一個有空閑空間的頁。一旦找到，就會做三件事，每一件都和要插入的索引鍵的順序有關：

隨機序列：正常情況，SQL Server會將滿頁的一半的入口移動的一個空頁，然後將新入口插入合適的頁，這就產生了兩個用了一半的頁。如果你的應用繼續插入資料，但是不刪除資料，這兩個頁將會從用了一半的狀態變成滿頁，然後再被分成兩個半頁，然後再次變成滿頁，這樣周而復始，迴圈往複。每頁的充滿率大概是75%。

增序序列：SQL Server發現新的入口需要插入滿頁的最後面，就會建立一頁，然後插入這個新入口，新頁再次滿了的話，就再建立一頁。一旦一頁滿了之後，他就一直是滿的，所以內部片段很小，甚至沒有。

降序序列：相反，如果SQL Server發現新的入口需要插入滿頁的開始，也會建立一頁，插入新的入口，但是由於是降序，內部片段接近100%。

刪除DELETE

當從表中刪除一行的時候，對應的索引入口會從索引中刪除。對每一個索引，SQL Server為了尋找入口，都要從根頁導航到葉子層頁。一旦SQL Server發現入口，就會做兩件事：立即刪除入口，或者是在行的頭部設定標記，使得入口變成ghost record，在適當的時候，就會刪除ghost record。

Ghost record在查詢的時候，會被忽略。它們只是在物理上還存在，邏輯上已經不存在了。一個索引的ghost record的數量可以通過系統函數sys.dm_db_index_physical_stats來擷取。

SQL Server沒有立即刪除是出於效能和並發管理的需要。不僅僅是刪除本身的效能，也包括隨後的交易回復效能。上面的做法使得復原一個刪除操作是很容易的，相比較從交易記錄中重新建立記錄而言。

下面的因素會影響刪除的處理過程：

• 如果行被鎖定，刪除的索引會變成ghost record。
• 如果執行的過程需要鎖定5000行資料，行層級的鎖會升級為表層級的鎖。
• 作為並發技術，行版本的使用，也會導致出現ghost record。
• 直到事務完成，才會刪除ghost record。
• SQL Server的後台線程ghost-cleanup負責刪除ghost record，但是，什麼時候刪除也是不可預期的。刪除操作本身並不通知ghost-cleanup線程去這麼做，隨後的頁掃描會將包含ghost record的頁加入一個列表，ghost-cleanup線程會週期性處理這個列表。
• ghost-cleanup線程大約每5秒鐘喚醒一次。每次會清理10頁。這些數字都是可以設定的。
• 你可以通過sp_clean_db_free_space或者sp_clean_db_file_free_space來強制清理，將會刪除整個資料庫或者資料檔案中的ghost record。

換句話說，當你刪除資料行的時候，邏輯上講已經刪除了。如果沒有被理解刪除，只要SQL Server認為是安全的，他們就會被刪除。

更新UPDATE

當更新表中資料行的時候，需要修改索引的入口。對於每一個索引入口，SQL Server會執行就地的更新，或者是刪除再插入。只要有可能，SQL Server還是會使用就地更新。但是，也有一些情況不能就地更新，SQL Server就會執行刪除緊跟著插入。下面是一些這方面的原因：

• 更新要修改鍵列，導致索引的入口需要重新分配。
• 更新要修改很多列，導致入口不合適在當前頁。
• 在表上有DML的觸發器。

如果修改的列是索引鍵的一部分，入口的位置肯定要變化。入口會從舊的位置刪除，以新鍵的順序在新的位置插入入口。大部分情況，都會再刪除之後執行插入。如果新的位置和舊的位置在同一頁，有可能會就地更新。SQL Server會從根頁到葉子層跑上兩次，一次用來尋找當前的入口位置，一次用來決定入口的新位置。

如果修改的列是叢集索引的一部分，所有的非叢集索引都需要更新，因為他們的標籤是由叢集索引的鍵組成的。

如果修改的不是索引鍵的一部分，入口的位置不會改變。但是，入口的大小可能會改變。如果頁中不夠空間存放新的入口，更新就會變成刪除再插入。

合并MERGER

在SQL Server 2008中引入了合併作業，很強大，很靈活，很好。合併作業會產生插入，更新，刪除語句。合并和你寫insert，udpate，delete產生的效果一樣，所以在本系列中沒有介紹。

MERGE 目標表  USING 源表  ON 匹配條件  WHEN MATCHED THEN     語句  WHEN NOT MATCHED THEN     語句; 

以上是MERGE的最最基本的文法，語句執行時根據匹配條件的結果，如果在目標表中 找到匹配記錄則執行WHEN MATCHED THEN後面的語句，如果沒有找到匹配記錄則執行WHEN NOT MATCHED THEN後面的語句。注意源表可以是表，也可以是一個子查詢語句。

格外強調一點，MERGE語句最後的分號是不能省略的!

MERGE ProductNew AS d USING     Product AS s ON s.ProductID = d.ProductId     WHEN NOT MATCHED THEN             INSERT( ProductID,ProductName,Price)                     VALUES(s.ProductID,s.ProductName,s.Price); 

MERGE ProductNew AS d USING     Product AS s ON s.ProductID = d.ProductId WHEN NOT MATCHED THEN     INSERT( ProductID,ProductName,Price)         VALUES(s.ProductID,s.ProductName,s.Price) WHEN MATCHED THEN     UPDATE SET d.ProductName = s.ProductName, d.Price = s.Price; 

一次性更新索引Index-at-a-Time Update

當執行插入，更新，刪除語句動作表的一行的時候，SQL Server肯定會修改資料，然後修改索引。在執行完插入，更新，刪除資料之後，SQL Server有兩個選擇：

• 對每一行，執行完操作之後，都去修改索引。
• 對每一行，執行完操作之後，對每個索引，將修改資訊掛起在一個集合中。等所有的行都執行完操作之後，在執行掛起的索引修改集合。

第二種叫做“一次性更新索引”，是插入，更新，刪除操作的一個選項。

SQL Server查詢最佳化工具將會決定採用哪一種來最佳化效能。如果修改的是表中的大部分行，很有可能會使用第二種。

為了證明，我們建立一張表，包含兩個索引。

USE AdventureWorks; GO IF EXISTS (SELECT *        FROM sys.objects          WHERE name = ‘FragTestII‘ and type = ‘U‘) BEGIN   DROP TABLE dbo.FragTestII; END GO CREATE TABLE dbo.FragTestII    (     PKCol  int not null    , InfoCol nchar(64) not null    , CONSTRAINT PK_FragTestII_PKCol primary key nonclustered (PKCol)    ); GO CREATE INDEX IX_FragTestII_InfoCol      ON dbo.FragTestII (InfoCol); GO 

先執行一個插入一條記錄的語句。

INSERT dbo.FragTestII VALUES (100000, ‘XXXX‘); 

的執行計畫，只是顯示了插入資料的過程，沒有顯示索引更新的資訊。這是因為，上面的情況下，索引的更新是行更新的一部分。

當時，當我們插入大量資料的時候，執行計畫就會不一樣了。

我們先構造一個20000條記錄的FragTest表，然後將FragTest的資料批量插入FragTestII表。

CREATE TABLE dbo.FragTest    (     PKCol  int IDENTITY(1,1)  not null    , InfoCol nchar(64) not null    , CONSTRAINT PK_FragTest_PKCol primary key nonclustered (PKCol)    ); GO  DECLARE @index INT SET @index=0  WHILE (@index<20000) BEGIN     INSERT INTO dbo.FragTest(InfoCol)VALUES(‘123‘)          SET @index=@index +1      END 

INSERT dbo.FragTestII   SELECT PKCol, InfoCol   FROM dbo.FragTest; 

執行計畫就是上面的樣子，包含很多的操作。一類操作是表中插入資料。有兩個排序，每個都包含一個插入索引的操作。

儘管是一個複雜的執行計畫，排序和更新掛起的索引的單獨執行的，但也是一個高效的執行計畫。相比隨即添加索引，有順序的添加索引，產生的片段會更少。

結論

在索引中插入入口會導致三種片段，這依賴於插入入口的順序。

從索引中刪除入口，包括從叢集索引中刪除，可能會立即刪除入口。也可能會建立ghost record使得索引入口成為邏輯刪除。ghost只是存在於葉子層。SQL Server在事務完成之後，才會刪除ghost record。

更新索引可能會立即就地更新，也可能是刪除後在插入。如果表中沒有DML的觸發器，如果更新沒有重新分配入口，或者增加入口的大小，通常還是會就地更新的。

如果資料修改語句影響的是大量的行，SQL Server可能會選擇一次性更新索引，先修改表，然後在更新每個索引。

SQL Server索引進階：第十三級，插入，更新，刪除