分析SQL Server 給出的missing index建議

 

測試基於SQL Server 2008  AdventureWorks2008,TablePerson.Person.表中已經存在的索引定義：

 

CREATE NONCLUSTEREDINDEX [IX_Person_LastName_FirstName_MiddleName]ON [Person].[Person] 

(

[LastName] ASC,

[FirstName] ASC,

[MiddleName] ASC

)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,

 

DROP_EXISTING=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]

 

GO

 

我想根據FirstName查詢LastName,執行後看執行計畫：

 

 

650) this.width=650;" title="1.png" style="float:none;" src="http://img1.51cto.com/attachment/201312/203828125.png" alt="203828125.png" />

 

可以看到SQL Server執行了Index Scan,雖然SQL Server沒有以FirstName開頭的索引。剛開始學習SQL Server的時候認為SQL Server只有filter的欄位是索引的第一個欄位時才可以使用索引。其實看過這個例子就知道，這個理解是不對的。因為之前在表上已經存在了LastName的索引，SQL Server引擎會知道索引中存在FirstName的欄位，而且Index的Page頁面的數量遠遠少於整張表Page的數量，所以用Index Scan是最快的方法，所以仍然會使用Index Scan.

 

回到SQL Server給出的Missing Index指令碼：

 

USE [AdventureWorks2008]

GO

CREATENONCLUSTEREDINDEX [ix1]

ON [Person].[Person]([FirstName])

 

650) this.width=650;" title="2.png" style="float:none;" src="http://www.bkjia.com/uploads/allimg/131229/220T0NM-1.png" alt="203831563.png" />

 

可以看到SQL Server使用了Index Scan+Key Lookup，而且Key Lookup的Cost佔到了87%。解決Key Lookup的問題我們可以使用Include Index,將這個欄位包含到上面建立的索引中。索引修改後的代碼：

 

USE[AdventureWorks2008]

GO

CREATENONCLUSTEREDINDEX [ix_firstname]

ON[Person].[Person]([FirstName])

include(LastName)

 

重新運行看到SQL Server 選擇了Index Seek：

 

 650) this.width=650;" title="3.png" style="float:none;" src="http://www.bkjia.com/uploads/allimg/131229/220T03544-2.png" alt="203833178.png" />

 

 這次的效能要比上次的好，使用下面的指令碼可以證明：

 

setstatisticsioon

go

setstatisticstimeon

go

select LastName from Person.Personwhere FirstName ='ken'

setstatisticsiooff

go

setstatisticstimeoff

go

所以即使SQL Server給出的Missing Index 也要最好分析之後進行使用。建立Index之後要關注Index的使用狀況，比如Seek/Scan/Lookup/Update的比例，根據這些資料，進行相應的調整。因為對於更新多而查詢非常少的案例，建立多的索引可能會在造成負擔。

 

 

 

 

本文出自 “關注SQL Server技術” 部落格，請務必保留此出處http://lzf328.blog.51cto.com/1196996/1344845