前言

前面幾節(jié)都是講的基礎(chǔ)內(nèi)容，本節(jié)我們講講索引性能優(yōu)化，當(dāng)對(duì)大數(shù)據(jù)進(jìn)行處理時(shí)首先想到的就是索引，一旦遇到這樣的問(wèn)題則手忙腳亂，各種查資料，為何平常不扎實(shí)基本功呢，我們由淺入深，簡(jiǎn)短的內(nèi)容，深入的理解，而非一上來(lái)就把問(wèn)題給框死，立馬給出解決方案，拋出問(wèn)題，再到解決問(wèn)題，你GET了沒(méi)有。

Bookmark Lookup、RID Lookup、Key Lookup定義

一說(shuō)到這三者，如果對(duì)索引研究不深的童鞋估計(jì)是懵逼的，什么玩意，我們姑且將上面三者翻譯為：標(biāo)簽查找、行ID查找、鍵查找。標(biāo)簽查找和鍵查找是一個(gè)意思，在SQL 2005之前叫Key Lookup。怎么解釋，如何定義呢？首先我們不看定義，直接看下面一步一步解析，如果你實(shí)在忍不住，請(qǐng)看園友【永紅】的見(jiàn)解，解釋還是非常到位。我們簡(jiǎn)短的說(shuō)明下此三者概念。

在查詢中，我們對(duì)返回的列在查詢條件上若建立了非聚集索引，此時(shí)將可能嘗試使用非聚集索引查找，如果返回的列沒(méi)有創(chuàng)建非聚集索引，此時(shí)會(huì)返回到數(shù)據(jù)頁(yè)中去獲取這些列的數(shù)據(jù)，即使表中存在聚集索引或者沒(méi)有，都會(huì)返回到表中或者聚集索引中去獲取數(shù)據(jù)。對(duì)于以上場(chǎng)景描述，如果表沒(méi)有創(chuàng)建聚集索引則稱為Bookmar Lookup，如果表中沒(méi)有聚集索引但是存在非聚集索引我們稱為RID Lookup?？吹竭@里我們就會(huì)想法操作如此耗時(shí)，還要返回到基表中去獲取數(shù)據(jù)，所以才有了我們本節(jié)來(lái)移除以上三者來(lái)提高查詢性能。接下來(lái)我們一起來(lái)看看。

拋出Bookmark Lookup、RID Lookup、Key Lookup問(wèn)題

我們首先創(chuàng)建如下表

USE TSQL2012 
GO
CREATE TABLE Sales.Orders 
(
[orderid] INT,
[shipaddress] VARCHAR(100),
[shipcity] VARCHAR(100),
[shipregion] VARCHAR(100))
GO

接著進(jìn)行查詢

USE TSQL2012
GO
SELECT orderid, shipaddress, shipregion
FROM Sales.Orders
WHERE shipcity = '深圳'

這個(gè)不用多講，沒(méi)添加任何索引，執(zhí)行查詢計(jì)劃是全表掃描。接下來(lái)我們創(chuàng)建在orderid上創(chuàng)建聚集索引如下：

CREATE CLUSTERED INDEX idx_cls_orderid ON Sales.Orders(orderid)

我們?cè)賵?zhí)行上述查詢

此時(shí)我們創(chuàng)建了聚集索引，所以此時(shí)查詢走聚集索引，到這里我們看到情況由全表掃描轉(zhuǎn)換成了索引掃描。我們?cè)诓樵儠r(shí)一直是帶了查詢條件的，而對(duì)查詢條件我們未作任何操作，如果我們此時(shí)在查詢條件上創(chuàng)建了索引，此時(shí)查詢的性能又會(huì)得到一點(diǎn)改善。我們開(kāi)始對(duì)查詢條件創(chuàng)建一個(gè)非聚集索引。

CREATE NONCLUSTERED INDEX idx_nc_shipcity ON Sales.Orders(shipcity)

我們?cè)俳又鴪?zhí)行查詢

我們觀察到對(duì)查詢條件創(chuàng)建了非聚集索引，查詢計(jì)劃會(huì)使用非聚集索引查找返回結(jié)果，但是對(duì)于shipaddress, shipcity, shipregion并不是索引的一部分，此時(shí)查詢引擎會(huì)返回到基表中得到這些數(shù)據(jù)再返回。這種行為就叫做Bookmark Lookup或者Key Lookup。下面我們就如本文標(biāo)題一樣問(wèn)題出現(xiàn)來(lái)解決問(wèn)題，移除Bookmark Lookup或者Key Lookup。我們嘗試用兩種不同的方法來(lái)解決。

解決Bookmark Lookup、RID Lookup、Key Lookup問(wèn)題

創(chuàng)建非聚集索引覆蓋索引

我們對(duì)查詢條件以及檢索列創(chuàng)建非聚集索引。

CREATE NONCLUSTERED INDEX idx_all_cover ON Sales.Orders(shipaddress,orderid,shipcity,shipregion)

此時(shí)我們對(duì)檢索列創(chuàng)建了非聚集索引，此時(shí)將不會(huì)再到數(shù)據(jù)頁(yè)中獲取數(shù)據(jù)，而是從索引中直接返回，所以到這里我們算是移除了Key Lookup。但是此時(shí)觸發(fā)另外一個(gè)問(wèn)題，執(zhí)行查詢計(jì)劃走的卻是索引掃描，索引到底是什么呢？我們打個(gè)比方，一個(gè)索引相當(dāng)于是數(shù)據(jù)庫(kù)中一個(gè)本書開(kāi)始的索引，我們需要快速?gòu)臅胁檎业轿覀兯枰臄?shù)據(jù)，這個(gè)時(shí)候書就是我們所說(shuō)的表。索引掃描意味著要讀取表中的所有行，然后返回滿足條件的所有數(shù)據(jù)，當(dāng)執(zhí)行索引掃描時(shí)，所有行上葉子節(jié)點(diǎn)上的所有都會(huì)被掃描，這也就意味著索引上的所有行都會(huì)被檢索一遍而不是直接檢索表，和表掃描對(duì)比的話，表掃描是直接讀取表中數(shù)據(jù)，所以表掃描和索引掃描還是有一點(diǎn)點(diǎn)不同，而索引查找則是依賴于索引頁(yè)數(shù)據(jù)來(lái)定位滿足條件的所有行，索引查找僅僅只影響滿足條件以及頁(yè)上包含這些滿足條件的行，所以說(shuō)索引查找更加高效。

上述我們稍微講解了下索引掃描和索引查找，而上述的問(wèn)題是我們創(chuàng)建了非聚集索引，但是結(jié)果執(zhí)行的查詢計(jì)劃是索引掃描，很是納悶，對(duì)于剛學(xué)索引小白的我來(lái)說(shuō)，不知該如何是好，以為是緩存的緣故，清除各種緩存均不好使。于是開(kāi)始胡思亂想是不是檢索列中數(shù)據(jù)有為NULL引起的，是不是檢索列數(shù)據(jù)重復(fù)引起的，嘗試了無(wú)數(shù)次，最終發(fā)現(xiàn)某一次居然好使。如下

CREATE NONCLUSTERED INDEX idx_cls_cover ON 
Sales.Orders(shipcity,orderid,shipaddress,shipregion)

此時(shí)若我們將查詢條件進(jìn)行如下修改。

USE TSQL2012
GO
SELECT orderid, shipaddress, shipregion
FROM Sales.Orders
WHERE shipaddress = '深圳' 
GO

到這里我們應(yīng)該發(fā)現(xiàn)了，唯一的區(qū)別在于我們創(chuàng)建非聚集索引時(shí)的順序和查詢條件不同就會(huì)導(dǎo)致索引掃描和索引查找的轉(zhuǎn)換，那么到底什么時(shí)候才會(huì)執(zhí)行索引查找呢？我們可以進(jìn)行如下一般性總結(jié)：

索引查找的一般性結(jié)論：如果條件中包含WHERE或者ON的話，查詢條件必須是位于索引集合列中首位，此時(shí)索引查找將會(huì)被使用。

此時(shí)我們穿插一點(diǎn)內(nèi)容，上述我們創(chuàng)建了覆蓋索引，我們來(lái)比較下覆蓋索引和默認(rèn)情況下聚集索引查找的性能開(kāi)銷。

覆蓋索引與默認(rèn)聚集索引性能開(kāi)銷比較

FROM Sales.Orders WITH(INDEX([PK_Orders]))
WHERE orderid11072
go
SELECT orderid, shipaddress, shipregion
FROM Sales.Orders WITH(INDEX([idx_noncls_include_exceptorderid]))
WHERE orderid11072
GO

從上可知，覆蓋索引的開(kāi)銷要比默認(rèn)主鍵聚集索引性能開(kāi)銷要好一點(diǎn)，同時(shí)我們可以看看如下二者IO代價(jià)。

通過(guò)上述覆蓋索引與默認(rèn)聚集索引的對(duì)比，我們能夠有效的減少IO，這一點(diǎn)也是非常明確的，當(dāng)然下面的INCLUDE索引對(duì)比也是另外一種好的方案。

創(chuàng)建INCLUDE非聚集索引

USE TSQL2012
GO
CREATE NONCLUSTERED INDEX [ix_noncls_include] ON [TSQL2012].[Sales].[Orders] (
 shipcity
) INCLUDE (shipaddress, shipregion, orderid)

至此我們用兩種方式來(lái)移除了Bookmark Lookup、RID Lookup、Key Lookup，通過(guò)使用索引和覆蓋索引。

既然有如上兩種方式，我們應(yīng)該有所取舍，二者誰(shuí)的性能更好呢？我們接下來(lái)比較上述二者的開(kāi)銷差異。

比較移除Bookmark Lookup等兩種方式差異

USE TSQL2012
GO
SELECT orderid, shipaddress, shipcity, shipregion
FROM Sales.Orders WITH(INDEX(idx_all_cover))
WHERE shipcity = '深圳'
GO
SELECT orderid, shipaddress, shipcity, shipregion
FROM Sales.Orders WITH(INDEX(ix_noncls_include))
WHERE shipcity = '深圳'
GO

我們從上所知，二者開(kāi)銷一樣，并未有什么區(qū)別，當(dāng)然相信我們更傾向于的是將第二種方式作為解決方案。到這里算是基本結(jié)束了，但是還有一個(gè)小問(wèn)題，我們?cè)谥耙呀?jīng)創(chuàng)建了orderid的聚集索引，后面在解決方案中我們也添加了orderid的非聚集索引，難道非得添加嗎，我們?nèi)サ粼囋嚳础?/p>

CREATE NONCLUSTERED INDEX idx_noncls_cover_exceptorderid
ON Sales.Orders(shipcity,shipaddress,shipregion)
CREATE NONCLUSTERED INDEX idx_noncls_include_exceptorderid
ON Sales.Orders(shipcity) INCLUDE(shipaddress,shipregion)

去除orderid比較二者開(kāi)銷差異：

USE TSQL2012
GO
SELECT orderid, shipaddress, shipregion
FROM Sales.Orders WITH(INDEX([idx_noncls_cover_exceptorderid]))
WHERE shipaddress = '深圳' 
GO
SELECT orderid, shipaddress, shipregion
FROM Sales.Orders WITH(INDEX([idx_noncls_include_exceptorderid]))
WHERE shipaddress = '深圳' 
GO

由上知，非聚集索引列不需要包含創(chuàng)建了聚集索引的列，那么事實(shí)到底是怎樣的呢？

結(jié)論：其實(shí)對(duì)于任何非聚集索引列都不需要包含創(chuàng)建了聚集索引的列，因?yàn)閯?chuàng)建聚集索引的列是非聚集索引集合列的一部分，也就是說(shuō)只要一個(gè)表上的列創(chuàng)建了聚集索引，那么非聚集索引集合列就包含了這個(gè)聚集索引。

總結(jié)

本節(jié)我們比較詳細(xì)就問(wèn)題的拋出到問(wèn)題的解決，從而來(lái)提高查詢性能，好了，到此結(jié)束，我們下節(jié)再會(huì)。簡(jiǎn)短的內(nèi)容，深入的理解

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