程式員眼中的 SQL Server－非叢集索引能給我們帶來什嗎？

標籤：sql



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

程式員應該有刨根問底的怪癖，雖然這是個資料庫問題。

應用情境

有一個 Product 表，欄位如下：

資料添加指令碼：

begin trandeclare @index intset @index=0while(@index<1000000)begininsert 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[email protected]index+1endcommit

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

一般應用環境查詢，有時候我們會針對一個欄位進行 where 查詢，有時候也會 and 另一個欄位進行查詢，這個時候，關於這兩個欄位的索引怎麼建？還是不需要建？是分別建兩個？還是建一個組合的？其實說真的，可能看到這的資料庫大神會莞爾一笑，但是作為程式員，這些我真不知道，搜尋的資料中也並沒有對這些雞毛蒜皮進行的說明，沒辦法，只能自己瞎折騰下。我們下面要做是 ProviderID 和 State 的查詢操作，有分別查詢，也有組合查詢，然後我們再對 Product 表建立這兩個欄位的索引，看看有什麼不同之處？還有就是針對不同的索引方式，查詢又會有什麼不同？我們睜大眼睛來看一下。技術提高的訪問www.cgzhw.com 遊戲編程網很不錯的技術網站。

問題分析

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

1. where ProviderID=?
   
2. where ProviderID=? and State=?

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

1. 不建立索引
   
2. ProviderID 欄位索引
   
3. ProviderID 和 State 欄位索引

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

執行結果

測試指令碼：

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

為了接近測試結果，每次語句執行三次，然後再取平均值，太麻煩了，這邊就直接貼下執行結果。

不建立索引

1. where ProviderID=1（二百萬資料）
   執行結果：13806毫秒，13380毫秒，12730毫秒
   平均結果：13305毫秒

2. where ProviderID=1 and State=1（一百萬資料）
   執行結果：6556毫秒，6613毫秒，6706毫秒
   平均結果：6625毫秒

建立索引欄位 ProviderID

1. where ProviderID=1
   執行結果：13986毫秒，13810毫秒，15853毫秒
   平均結果：14549毫秒

2. where ProviderID=1 and State=1
   執行結果：7153毫秒，7190毫秒，13950毫秒
   平均結果：7122毫秒

建立索引欄位 ProviderID 和 State

1. where ProviderID=1
   執行結果：13840毫秒，14163毫秒，15853毫秒
   平均結果：14618毫秒

2. where ProviderID=1 and State=1
   執行結果：7033毫秒，7220毫秒，7023毫秒
   平均結果：7152毫秒

結果分析

雖然測試的有些不完整，但是看到結果，哥有些淩亂了（建了索引，效能反而會降低？），難道是我插入的資料有問題？還是建立索引有問題？還是我人品有問題？？？坐等資料庫大神指教。。。