SQL Server非聚集索引能給我們帶來什么？
用 Sql Server Profiler 來跟蹤頁面訪問的時(shí) SQL 的執(zhí)行情況，因?yàn)閼?yīng)用程序很簡(jiǎn)單，頁面加載的時(shí)候，跟蹤檢測(cè)到三個(gè) SQL 執(zhí)行，看了下也沒什么問題（兩個(gè)獲取數(shù)量，一個(gè)獲取列表），數(shù)量獲取的 SQL，這個(gè)應(yīng)該執(zhí)行會(huì)很快，所以把分析焦點(diǎn)放在了那個(gè)獲取列表的 SQL 上，因?yàn)?SQL 沒什么問題，那應(yīng)該是關(guān)于這條 SQL 建的索引有問題。注：上面所說項(xiàng)目中大概有 100 萬的數(shù)據(jù)。
關(guān)于數(shù)據(jù)庫中的索引概念，記得在很早之前整理了一篇博文《T-Sql（八）字段索引和數(shù)據(jù)加密》，現(xiàn)在來看，寫的真是一坨屎，概念講的再多沒個(gè)毛用，關(guān)鍵在于對(duì)實(shí)際應(yīng)用中產(chǎn)生問題的分析。在研究這個(gè)問題之前，搜了一些相關(guān)資料，主要來自園中的幾位 SQL Server 大神（CareySon、樺仔、聽風(fēng)吹雨等），稍微看了下，關(guān)于索引，主要是一些數(shù)據(jù)庫專業(yè)術(shù)語，看的不是很明白，作為程序員，我們知道索引分為聚集性索引和非聚集性索引，聚集性索引一般為主鍵（也可以不是），在創(chuàng)建表的時(shí)候會(huì)自動(dòng)創(chuàng)建，針對(duì)上面我那個(gè)應(yīng)用查詢問題，查詢條件是一些非主鍵字段，所以這邊探討下非聚集性索引。
我不會(huì)說一些數(shù)據(jù)庫概念，所以只能用做一些實(shí)踐來理解概念的意義，以下應(yīng)用場(chǎng)景中的用例是虛擬出來的，只是作為個(gè)人研究使用。
程序員應(yīng)該有刨根問底的怪癖，雖然這是個(gè)數(shù)據(jù)庫問題。
應(yīng)用場(chǎng)景
有一個(gè) Product 表，字段如下：
數(shù)據(jù)添加腳本：
- begin tran
- declare @index int
- set @index=0
- while(@index<1000000)
- begin
- insert into [dbo].[Product]([Name],Remarks,ProviderID,[Time],[State])
- values('我是測(cè)試標(biāo)題1','我是測(cè)試備注1我是測(cè)試備注1我是測(cè)試備注1我是測(cè)試備注1我是測(cè)試備注1我是測(cè)試備注1',1,GETDATE(),0)
- insert into [dbo].[Product]([Name],Remarks,ProviderID,[Time],[State])
- values('我是測(cè)試標(biāo)題2','我是測(cè)試備注2我是測(cè)試備注2我是測(cè)試備注2我是測(cè)試備注2我是測(cè)試備注2我是測(cè)試備注2',1,GETDATE(),1)
- insert into [dbo].[Product]([Name],Remarks,ProviderID,[Time],[State])
- values('我是測(cè)試標(biāo)題3','我是測(cè)試備注3',3,GETDATE(),1)
- insert into [dbo].[Product]([Name],Remarks,ProviderID,[Time],[State])
- values('我是測(cè)試標(biāo)題4','我是測(cè)試備注4我是測(cè)試備注4我是測(cè)試備注4我是測(cè)試備注4我是測(cè)試備注4我是測(cè)試備注4',4,GETDATE(),1)
- set @index=@index+1
- end
- commit
Product 表中插入了四百萬的數(shù)據(jù)，為了接近我們現(xiàn)實(shí)生產(chǎn)環(huán)境，所以對(duì)數(shù)據(jù)進(jìn)行了不同插入。
一般應(yīng)用環(huán)境查詢，有時(shí)候我們會(huì)針對(duì)一個(gè)字段進(jìn)行 where 查詢，有時(shí)候也會(huì) and 另一個(gè)字段進(jìn)行查詢，這個(gè)時(shí)候，關(guān)于這兩個(gè)字段的索引怎么建？還是不需要建？是分別建兩個(gè)？還是建一個(gè)組合的？其實(shí)說真的，可能看到這的數(shù)據(jù)庫大神會(huì)莞爾一笑，但是作為程序員，這些我真不知道，搜索的資料中也并沒有對(duì)這些雞毛蒜皮進(jìn)行的說明，沒辦法，只能自己瞎折騰下。我們下面要做是 ProviderID 和 State 的查詢操作，有分別查詢，也有組合查詢，然后我們?cè)賹?duì) Product 表建立這兩個(gè)字段的索引，看看有什么不同之處？還有就是針對(duì)不同的索引方式，查詢又會(huì)有什么不同？我們睜大眼睛來看一下。
問題分析
我再對(duì)上面的分析進(jìn)行說明下，首先，查詢主要為2種：
where ProviderID=?
where ProviderID=? and State=?
非聚集性索引的創(chuàng)建主要為3種：
不創(chuàng)建索引
ProviderID 字段索引
ProviderID 和 State 字段索引
針對(duì)這個(gè)應(yīng)用場(chǎng)景和上面的分析，會(huì)得出 3*2 六種結(jié)果，其實(shí)我最想知道的是下面的第三種，即創(chuàng)建一個(gè)組合字段索引，對(duì)單個(gè)字段的查詢會(huì)不會(huì)有影響？還有就是反過來，單個(gè)字段的索引創(chuàng)建，對(duì)組合字段查詢會(huì)不會(huì)有影響？當(dāng)然試過了才知道，看一下執(zhí)行結(jié)果。
執(zhí)行結(jié)果
測(cè)試腳本：
- 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 '用時(shí)/毫秒'
為了接近測(cè)試結(jié)果，每次語句執(zhí)行三次，然后再取平均值，截圖太麻煩了，這邊就直接貼下執(zhí)行結(jié)果。
不創(chuàng)建索引
-
where ProviderID=1（二百萬數(shù)據(jù)）
執(zhí)行結(jié)果：13806毫秒，13380毫秒，12730毫秒
平均結(jié)果：13305毫秒 -
where ProviderID=1 and State=1（一百萬數(shù)據(jù)）
執(zhí)行結(jié)果：6556毫秒，6613毫秒，6706毫秒
平均結(jié)果：6625毫秒
創(chuàng)建索引字段 ProviderID
-
where ProviderID=1
執(zhí)行結(jié)果：13986毫秒，13810毫秒，15853毫秒
平均結(jié)果：14549毫秒 -
where ProviderID=1 and State=1
執(zhí)行結(jié)果：7153毫秒，7190毫秒，13950毫秒
平均結(jié)果：7122毫秒
創(chuàng)建索引字段 ProviderID 和 State
-
where ProviderID=1
執(zhí)行結(jié)果：13840毫秒，14163毫秒，15853毫秒
平均結(jié)果：14618毫秒 -
where ProviderID=1 and State=1
執(zhí)行結(jié)果：7033毫秒，7220毫秒，7023毫秒
平均結(jié)果：7152毫秒
結(jié)果分析
雖然測(cè)試的有些不完整，但是看到結(jié)果，哥有些凌亂了（建了索引，性能反而會(huì)降低？），難道是我插入的數(shù)據(jù)有問題？還是創(chuàng)建索引有問題？還是我人品有問題？？？坐等數(shù)據(jù)庫大神指教。
原文鏈接：http://www.cnblogs.com/xishuai/p/3922964.html
