SQL Server非聚集索引能給我們帶來什么？

作者：田園里的蟋蟀 2014-08-28 10:06:57

最近在做的一個項目，頁面訪問的時候很慢（大概幾秒鐘的樣子），然后用日志記錄的方式，來排查這個問題，最后發現是Entity Framework初始化的一個坑（大概要花 6-7 秒），詳見：《來，給Entity Framework熱熱身》，但是除了這個問題，還發現當一些用戶數據量很大的時候，訪問也是有些慢，這個就不是 Entity Framework 的問題了（因為初始化已完成）。下面是筆者解決問題的過程。

用 Sql Server Profiler 來跟蹤頁面訪問的時 SQL 的執行情況，因為應用程序很簡單，頁面加載的時候，跟蹤檢測到三個 SQL 執行，看了下也沒什么問題（兩個獲取數量，一個獲取列表），數量獲取的 SQL，這個應該執行會很快，所以把分析焦點放在了那個獲取列表的 SQL 上，因為 SQL 沒什么問題，那應該是關于這條 SQL 建的索引有問題。注：上面所說項目中大概有 100 萬的數據。

[[118947]]

關于數據庫中的索引概念，記得在很早之前整理了一篇博文《T-Sql（八）字段索引和數據加密》，現在來看，寫的真是一坨屎，概念講的再多沒個毛用，關鍵在于對實際應用中產生問題的分析。在研究這個問題之前，搜了一些相關資料，主要來自園中的幾位 SQL Server 大神（CareySon、樺仔、聽風吹雨等），稍微看了下，關于索引，主要是一些數據庫專業術語，看的不是很明白，作為程序員，我們知道索引分為聚集性索引和非聚集性索引，聚集性索引一般為主鍵（也可以不是），在創建表的時候會自動創建，針對上面我那個應用查詢問題，查詢條件是一些非主鍵字段，所以這邊探討下非聚集性索引。

我不會說一些數據庫概念，所以只能用做一些實踐來理解概念的意義，以下應用場景中的用例是虛擬出來的，只是作為個人研究使用。

程序員應該有刨根問底的怪癖，雖然這是個數據庫問題。

應用場景

有一個 Product 表，字段如下：

數據添加腳本：

begin tran 
declare @index int 
set @index=0 
while(@index<1000000) 
  begin 
   insert into [dbo].[Product]([Name],Remarks,ProviderID,[Time],[State])  
   values('我是測試標題1','我是測試備注1我是測試備注1我是測試備注1我是測試備注1我是測試備注1我是測試備注1',1,GETDATE(),0) 
   insert into [dbo].[Product]([Name],Remarks,ProviderID,[Time],[State])  
   values('我是測試標題2','我是測試備注2我是測試備注2我是測試備注2我是測試備注2我是測試備注2我是測試備注2',1,GETDATE(),1) 
   insert into [dbo].[Product]([Name],Remarks,ProviderID,[Time],[State])  
   values('我是測試標題3','我是測試備注3',3,GETDATE(),1) 
   insert into [dbo].[Product]([Name],Remarks,ProviderID,[Time],[State])  
   values('我是測試標題4','我是測試備注4我是測試備注4我是測試備注4我是測試備注4我是測試備注4我是測試備注4',4,GETDATE(),1) 
   set @index=@index+1 
  end 
commit

Product 表中插入了四百萬的數據，為了接近我們現實生產環境，所以對數據進行了不同插入。

一般應用環境查詢，有時候我們會針對一個字段進行 where 查詢，有時候也會 and 另一個字段進行查詢，這個時候，關于這兩個字段的索引怎么建？還是不需要建？是分別建兩個？還是建一個組合的？其實說真的，可能看到這的數據庫大神會莞爾一笑，但是作為程序員，這些我真不知道，搜索的資料中也并沒有對這些雞毛蒜皮進行的說明，沒辦法，只能自己瞎折騰下。我們下面要做是 ProviderID 和 State 的查詢操作，有分別查詢，也有組合查詢，然后我們再對 Product 表建立這兩個字段的索引，看看有什么不同之處？還有就是針對不同的索引方式，查詢又會有什么不同？我們睜大眼睛來看一下。

問題分析

我再對上面的分析進行說明下，首先，查詢主要為2種：

where ProviderID=?

where ProviderID=? and State=?

非聚集性索引的創建主要為3種：

不創建索引

ProviderID 字段索引

ProviderID 和 State 字段索引

針對這個應用場景和上面的分析，會得出 3*2 六種結果，其實我最想知道的是下面的第三種，即創建一個組合字段索引，對單個字段的查詢會不會有影響？還有就是反過來，單個字段的索引創建，對組合字段查詢會不會有影響？當然試過了才知道，看一下執行結果。

執行結果

測試腳本：

declare @begin_date datetime 
declare @end_date datetime 
select @begin_date = getdate() 
select * from [dbo].[Product] where ... 
select @end_date = getdate() 
select datediff(ms,@begin_date,@end_date) as '用時/毫秒'