淺析SQL Server 聚焦索引對(duì)非聚集索引的影響

前言

在學(xué)習(xí)SQL 2012基礎(chǔ)教程過(guò)程中會(huì)時(shí)不時(shí)穿插其他內(nèi)容來(lái)進(jìn)行講解，相信看過(guò)SQL Server 2012 T-SQL基礎(chǔ)教程的童鞋知道前面寫的所有內(nèi)容并非都是摘抄書上內(nèi)容，如若是這樣那將沒(méi)有任何意義，學(xué)習(xí)的過(guò)程必須同時(shí)也是一個(gè)思考的過(guò)程，無(wú)論是獨(dú)立思考也好還是查資料也罷都是思考而非走馬觀花，要不然過(guò)一段時(shí)間又會(huì)健忘。簡(jiǎn)短的內(nèi)容，深入的理解。

話題

非聚集索引定義：非聚集索引也是一個(gè)B樹結(jié)構(gòu)，與聚集索引不同的是，B樹的葉子節(jié)點(diǎn)存的是指向堆或聚集索引的指針。你真的理解了嗎？？你能舉出例子嗎？？其實(shí)本節(jié)最終想表達(dá)的就是這個(gè)意思，定義太長(zhǎng)，我們抽象一點(diǎn)來(lái)定義并得出最終結(jié)論，請(qǐng)往下看。

聚集索引對(duì)非聚集索引影響

關(guān)于聚集索引和非聚集索引的概念、原理、創(chuàng)建都不會(huì)再敘述，若對(duì)此不太了解請(qǐng)參考園中其他園友的詳細(xì)介紹。

首先我們創(chuàng)建測(cè)試表

USE SQLStudy
GO
CREATE TABLE [dbo].[Test](
[ID] [int] NOT NULL,
[First] [nchar](10) NULL,
[Second] [nchar](10) NULL
)
GO

接下來(lái)我們?cè)賮?lái)創(chuàng)建測(cè)試數(shù)據(jù)

INSERT INTO [SQLStudy].[dbo].[Test] ([ID],[First],[Second])
SELECT 1,'First1','Second1'
UNION ALL
SELECT 2,'First2','Second2'
UNION ALL
SELECT 3,'First3','Second3'
UNION ALL
SELECT 4,'First4','Second4'
UNION ALL
SELECT 5,'First5','Second5'
GO

緊接著我們對(duì)表上的First和Second列創(chuàng)建聚集索引，如下

CREATE NONCLUSTERED INDEX [IX_MyTable_NonClustered] ON [dbo].[Test] (
[First] ASC,
[Second] ASC
)

此時(shí)我們來(lái)同時(shí)運(yùn)行兩個(gè)查詢，看看其執(zhí)行計(jì)劃【注】：上一篇已經(jīng)說(shuō)過(guò)，請(qǐng)啟用包括實(shí)際執(zhí)行的計(jì)劃。

SELECT ID
FROM [dbo].[Test] WHERE [First] = 'First1' AND [Second] = 'Second1'
SELECT Second
FROM [dbo].[Test] WHERE [First] = 'First1' AND [Second] = 'Second1'
GO

此時(shí)我們看到的執(zhí)行計(jì)劃如下：

通過(guò)上述毫無(wú)疑問(wèn)我們可以得出結(jié)論：查詢1是利用的全表掃描，而查詢2利用的非聚集索引查找。我們應(yīng)該對(duì)于這個(gè)結(jié)論沒(méi)有任何懷疑，因?yàn)橐诙€(gè)查詢的Second列在此之前已經(jīng)創(chuàng)建額非聚集索引，而對(duì)于查詢1中的ID則沒(méi)有，所以會(huì)造成查詢1的全表掃描，而查詢2則是非聚集索引查找。

下面我們對(duì)表上的列ID創(chuàng)建聚集索引。

CREATE CLUSTERED INDEX [IX_MyTable_Clustered] ON [dbo].[Test] (
[ID] ASC
)

此時(shí)我們?cè)賮?lái)運(yùn)行如下查詢：

SELECT ID
FROM [dbo].[Test] WHERE [First] = 'First1' AND [Second] = 'Second1'
SELECT Second
FROM [dbo].[Test] WHERE [First] = 'First1' AND [Second] = 'Second1'
GO

此時(shí)再來(lái)看看查詢執(zhí)行計(jì)劃：

通過(guò)上述我們對(duì)列ID創(chuàng)建了聚集索引，我們肯定能立馬知道兩者都是利用索引查找，確實(shí)沒(méi)錯(cuò)，但是，但是你發(fā)現(xiàn)沒(méi)有，睜大眼睛看看，我們明明在列ID上創(chuàng)建的是聚集索引，理論上應(yīng)該是聚集索引查找才對(duì)啊，這就是我們本文所需要討論的問(wèn)題。

問(wèn)題探討

我們將問(wèn)題進(jìn)行如下概述，當(dāng)我們?cè)诹猩蟿?chuàng)建聚集索引時(shí)且查詢返回該列，同時(shí)查詢條件是創(chuàng)建了非聚集索引的列，此時(shí)對(duì)于創(chuàng)建了聚集索引的列的查詢執(zhí)行計(jì)劃則是非聚集索引查找，這其中到底發(fā)生了什么？

實(shí)際發(fā)生的情況是非聚集索引內(nèi)部引用了聚集索引， 當(dāng)聚集索引被創(chuàng)建后在表中的數(shù)據(jù)會(huì)按照物理邏輯進(jìn)行排序，當(dāng)聚集索引沒(méi)有被創(chuàng)建時(shí)此時(shí)非聚集索引指向的表中的數(shù)據(jù)并最終返回?cái)?shù)據(jù)，但是一旦聚集索引創(chuàng)建了此時(shí)非聚集索引則會(huì)重建從而此時(shí)指向的是聚集索引，說(shuō)到這里對(duì)于園友CareySon對(duì)于非聚集索引的描述：非聚集索引也是一個(gè)B樹結(jié)構(gòu)，與聚集索引不同的是，B樹的葉子節(jié)點(diǎn)存的是指向堆或聚集索引的指針。概括的非常精準(zhǔn)，若創(chuàng)建了聚集索引此時(shí)非聚集索引的指針則指向的是聚集索引，否則此時(shí)指向的是堆也就是表中的數(shù)據(jù)。所以此時(shí)在這種情況下，當(dāng)查詢創(chuàng)建了聚集索引的列時(shí)是進(jìn)行了非聚集索引查找。

至此，我們可以得出結(jié)論：當(dāng)在檢索的列上創(chuàng)建了聚集索引時(shí)（僅僅返回創(chuàng)建聚集索引的列），此時(shí)查詢不會(huì)使用聚集索引查找來(lái)檢索結(jié)果而是使用非聚集索引查找來(lái)檢索結(jié)果。

總結(jié)

個(gè)人覺(jué)得對(duì)于一個(gè)定義出來(lái)之前我們得首先拋出這樣一個(gè)問(wèn)題，如上述非聚集索引的定義：非聚集索引也是一個(gè)B樹結(jié)構(gòu)，與聚集索引不同的是，B樹的葉子節(jié)點(diǎn)存的是指向堆或聚集索引的指針。初次看到這句感覺(jué)沒(méi)什么，泛泛而談，感覺(jué)似乎理解了，當(dāng)遇到這樣的問(wèn)題時(shí)卻不知所措，其實(shí)就是對(duì)定義理解的不夠深入或者說(shuō)不夠透，當(dāng)一個(gè)定義出來(lái)時(shí)你能舉出這個(gè)定義的例子或者場(chǎng)景，那可能才算是真正了解了。本節(jié)我們到此結(jié)束，對(duì)于SQL這一系列會(huì)秉著簡(jiǎn)短的內(nèi)容，深入的理解來(lái)講解，同時(shí)也會(huì)循序漸進(jìn)講講查詢性能問(wèn)題，由拋出問(wèn)題到最終解決問(wèn)題才算是收貨多多。

以上就是本文的全部?jī)?nèi)容，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，如果有疑問(wèn)大家可以留言交流，同時(shí)也希望多多支持腳本之家！