[技術探討]SQL Server索引 的重要性

SQL Server索引

實務上曾遇到客戶既有的LOB系統在運作,而我們需開發一套新的系統與其資料庫介接,以維持現有功能的運作,故不能對其現有的DB Schema做變更。當新系統開發完進行壓力測試時,發現資料量少時,運作是正常的,但資料量一變大,就不斷的出現錯誤訊息。探究其原因,竟是資料表沒有建立任何索引,故有此篇文章的產生。

假設有一個名為Demo的資料庫,擁有兩個一樣Schema的Table,Schema如下圖所示,一個Table名稱SendSMSWithoutIndex,沒有建立任何主鍵與索引;另一個Table名稱SendSMSWithIndex,沒有建立主鍵但有建立索引。兩個Table都塞入了30萬筆的資料。

SQL Server索引

其中Table SendSMSWithIndex的索引資訊如下圖

SQL Server索引

首先對SendSMSWithoutIndex執行下列指令:

begin tran
update [dbo].[SendSMSWithoutIndex] set status = 0 where msg_id = 150000

利用下列指令檢查該資料庫的Lock狀況:

select resource_type, request_mode, request_type, request_reference_count,
request_lifetime, request_session_id, request_owner_type 
from sys.dm_tran_locks where resource_database_id = db_id('Demo')
order by resource_type, request_lifetime

目前Lock狀況如下:

SQL Server索引

另開一個新連線,並執行下列指令

begin tran
update [dbo].[SendSMSWithoutIndex] set status = 0 where msg_id = 200000

重新檢查該資料庫的Lock狀況:

SQL Server索引

SQL Server索引

 

結果發現原本預期應只鎖定兩筆Row,實際上卻是產生兩筆Row的X Lock,8452筆Page的U Lock,11筆Table的IX Lock。

下表對Lock模式做個簡單的整理

鎖定模式描述
共用 (S)用於不變更或更新資料的讀取作業,例如 SELECT 陳述式。
更新 (U)用於可更新的資源上。在可重複讀取或可序列化交易中,可防止當多個工作階段正在讀取、鎖定及後來可能更新資源時發生常見的死結。
獨佔 (X)用於資料修改動作,例如 INSERT、UPDATE 或 DELETE。確保不能對相同資源同時進行多重更新。
意圖獨佔 (IX)保護在階層較低位置的某些 (但不是全部) 資源上要求的或取得的獨佔鎖定。IX 是 IS 的超集,它也保護在較低層級資源要求的共用鎖定。
更新意圖獨佔 (UIX)U 和 IX 鎖定的結合,這是個別取得這些鎖定又同時保留兩種鎖定的結果。

在此要注意的是U Lock,雖然其描述說明在「可重複讀取」或「可序列化」交易中可防止死結,但其實資料庫預設為「讀取認可」交易,也就是說Table SendSMSWithoutIndex發生這麼多筆Page的U Lock,實際結果等同於發生了Table Lock。

接下來改對SendSMSWithIndex執行下列指令:

begin tran
update [dbo].[SendSMSWithIndex] set status = 0 where msg_id = 150000

目前Lock狀況如下:

SQL Server索引

另開一個新連線,並執行下列指令

begin tran
update [dbo].[SendSMSWithIndex] set status = 0 where msg_id = 200000

重新檢查該資料庫的Lock狀況:

SQL Server索引

結果產生了產生兩筆Row的X Lock,兩筆Page的IX Lock,兩筆Table的IX Lock,符合預期。

進一步以執行計畫來驗證Table有無建立索引所造成的影響:

在SendSMSWithoutIndex針對一筆資料執行Update指令,使用了較無效率的資料表掃描。

SQL Server索引

相同指令在SendSMSWithIndex執行,則是使用較有效率的索引搜尋。

SQL Server索引

以上實驗,說明了資料表有無建立索引會產生截然不同的結果,因此開發程式的同時,也應重視建立正確索引的重要性。

再以應用程式執行的角度來看,當同時有多筆資料要更新時,SendSMSWithoutIndex會發生下列錯誤訊息「已超過連接 Timeout 的設定。在作業完成之前超過逾時等待的時間,或者是伺服器未回應。」或是「交易 (處理序識別碼 xx) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。」,尤其當程式寫的效率越好時,此錯誤訊息發生的機率會越高,但僅僅只要改善Table索引的設定,即可讓程式保有執行的高效率及資料的正確性,這也提醒了我們開發一套系統時,任何細節均不能輕忽。

參考資料

鎖定模式:http://technet.microsoft.com/zh-tw/library/ms175519(v=sql.105).aspx

交易隔離等級:http://technet.microsoft.com/zh-tw/library/ms189122(v=sql.105).aspx


發表迴響

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料