SQL優(yōu)化：索引的重要性

開篇小測驗(yàn)

　　下面這樣一個(gè)小SQL 你該怎么樣添加最優(yōu)索引

　　兩個(gè)表上現(xiàn)在只有聚集索引

　　bigproduct?表上已經(jīng)有聚集索引?ProductID

?

　　bigtransactionhistory?表上已經(jīng)有聚集索引?TransactionID

select?p.productnumber,p.reorderpoint,th.Quantity
from?bigproduct?as?p
join?bigtransactionhistory?as?th?on?th.productid=p.productid?and?th.TransactionDate?>?p.SellStartDate
where?p.name?in?('LL?Crankarm1000','ML?Crankarm1000')?and?th.TransactionDate?>?'2010-01-01'

?

?

?

　　你是否一眼就能看出來呢？

　　答案將在文章中逐步揭曉~~~

簡單粗暴的添加索引

　　看過我前面文章的看官們一定會發(fā)現(xiàn)我很喜歡用“簡單粗暴”這個(gè)詞，一是因?yàn)樵~匯量小文筆也差，真心用不出高大上的詞兒！ 再一個(gè)，你們不喜歡簡單粗暴么~~干貨最重要，不是么？

　　首先我們看一下沒有優(yōu)化前的執(zhí)行計(jì)劃

　　clustered index scan 這其實(shí)就是表掃描，不是table scan 只是因?yàn)楸砩嫌芯奂饕?/p>

　　可以看出這個(gè)查詢倆表都使用了表掃描！　　

　　where 條件添加索引

　　首先大多數(shù)人都知道?where 條件中的字段需要添加索引! 我們添加一下看看效果創(chuàng)建?

　　在?bigproduct 表上創(chuàng)建 name 列索引 ，在bigtransactionhistory表上創(chuàng)建?TransactionDate 列索引。

　　再次執(zhí)行語句看一下效果！

　　添加where索引以后可以看到以下幾個(gè)現(xiàn)象

bigproduct 從原來的clustered index scan 變成 index seek另外多出來個(gè)KEY Lookup(clustered)bigproduct 上添加的索引起了作用，邏輯讀bigproduct 由 601 變成 10。bigtransactionhistory 沒啥變化啊 還是clustered index scan

　　解釋一下出現(xiàn)的現(xiàn)象 ： 首先一點(diǎn)bigproduct 邊添加的where 條件索引，起到了作用，執(zhí)行的時(shí)候不是全表掃描了，邏輯讀有明顯的下降，出現(xiàn)的 KEY Lookup 是因?yàn)檫x擇（select）的列，在索引中沒有，而需要通過聚集索引再查找一次，再找一次也意味著多一部分開銷！

　　那么同樣添加了where 條件索引的bigtransactionhistory 表為什么沒起作用呢？ 那是因?yàn)镾QL優(yōu)化器在選擇計(jì)劃的時(shí)候認(rèn)為，不使用TransactionDate 列索引查找效率會更好!?

　　真的么？ 我們來驗(yàn)證一下，通過指定選擇索引，來讓優(yōu)化器選擇索引查找！

?

?

　　　強(qiáng)制使用索引以后，可以看出邏輯讀由 14W 變成1961W，語句時(shí)間也變得很長，這就是優(yōu)化器為什么不選用你加的索引！優(yōu)化器還是很智能的吧。

?

　　高能預(yù)警：優(yōu)化器可不是什么時(shí)候都這么智能的...由于緩存計(jì)劃或優(yōu)化器抽風(fēng)等原因，也會出現(xiàn)優(yōu)化器用了這種索引，導(dǎo)致你的語句奇慢，讀飆升直接影響到你的內(nèi)存、磁盤、CPU資源！另外如果這樣一條語句是系統(tǒng)中一條很頻繁運(yùn)行的語句，你的系統(tǒng)就掛了！沒錯(cuò)就掛了！這就是開篇拋出的問題就是因?yàn)橐粭l語句！

?

?

　　消滅Key Lookup 添加select 字段

　　?這就是傳說中的覆蓋索引！?

　　　看到執(zhí)行計(jì)劃中存在Key Lookup　而且消耗占比很高，如上面強(qiáng)制索引的計(jì)劃，那么我們就要想到的 在索引中包含那些SELECT 的列！如果消耗低，邏輯讀少，如上面bigproduct 表中的Key Lookup 就可以忽略（如果你追求完美，也一樣優(yōu)化就可以了）。

　　　包含列的圖形化創(chuàng)建 ：?

　　　語句創(chuàng)建就是 ：

CREATE?NONCLUSTERED?INDEX?TransactionDate
ON?[dbo].[bigTransactionHistory]?([TransactionDate])

------INCLUDE?就是包含列
INCLUDE?([ProductID],[Quantity])
GO

?

?

?

?

　　　下面我們添加一下看看效果 ：

?

?

　　添加select 索引字段后可以看出的現(xiàn)象：

優(yōu)化器自己選擇了index seekbigtransactionhistory占比最高的Key Lookup消失了邏輯讀由原來無索引的14W變成1Wbigtransactionhistory表還提示缺少索引？

　　　通過優(yōu)化索引添加select 字段，我們看出語句又一次得到了提升?bigtransactionhistory 從表掃描變成索引查找，邏輯讀由14W變成 1W！這是一個(gè)質(zhì)的飛躍啊！

?

?　　那為什么還提示缺少索引呢？ 創(chuàng)建一下試試吧！

　　索引再優(yōu)化加入表關(guān)聯(lián)列

　　按照提示我們創(chuàng)建索引 ： 和上一個(gè)索引的不同?ProductID 列由包含列變成了索引列！

USE?[AdventureWorks2012]
GO
CREATE?NONCLUSTERED?INDEX?ProductID_TransactionDate
ON?[dbo].[bigTransactionHistory]?([ProductID],[TransactionDate])
INCLUDE?([Quantity])

?

?

　　我們看一下效果：

?

?

　　再次優(yōu)化索引以后可以看到以下幾個(gè)現(xiàn)象

bigtransactionhistory表還是索引查找index seekbigtransactionhistory依然沒有了Key Lookup兩表關(guān)聯(lián)的hash join 變成了nested loops并行計(jì)劃變成了串行邏輯讀又從1W 變成18

?

　　又一次質(zhì)的飛躍！讀從原來的14W 變成1W 又變成18，這樣大大減少了內(nèi)存和IO的消耗，另外并行計(jì)劃也變成了串行，無疑又減少了大量CPU的消耗！語句時(shí)間，我想這里就不用多說了吧？

　　高能預(yù)警：這里所說的hash join，并行變串行，不懂的朋友可以在百度自行學(xué)習(xí)，這里只是針對當(dāng)前語句的情況，不能一概而論！

?

?

?

　　精簡你的索引

　　大家都知道，索引會導(dǎo)致update、insert、delete操作變慢！那么盡量精簡你的索引就是一個(gè)很重要的話題了！

　　?上面的優(yōu)化過程中我們創(chuàng)建了幾個(gè)索引，以bigTransactionHistory為例來看一下：

　　　腳本這里就不貼了，其實(shí)我們最后創(chuàng)建的索引?ProductID_TransactionDate包含Quantity?已經(jīng)包含了前兩個(gè)索引，而且可以說無論任何類似語句都使用ProductID_TransactionDate包含Quantity?就可以了！

　　　那么我們就可以清除前兩個(gè)索引！

?

　　至此語句的優(yōu)化算是結(jié)束了，留下的就是bigproduct 依然有一個(gè)Key Lookup可以優(yōu)化，可以仿照上面的繼續(xù)優(yōu)化，這里就不細(xì)說了。語句只是經(jīng)過了簡單的索引優(yōu)化就從一輛2手QQ變成了法拉利，是不是很神奇？

　　這就是索引的重要性！