??? 前兩篇文章我總結(jié)了一些SQL數(shù)據(jù)庫索引的問題，這篇主要來分析下索引的優(yōu)缼點(diǎn)，以及如何正確使用索引。
??
??? 索引的優(yōu)點(diǎn)：這個(gè)顯而易見，正確的索引會(huì)大大提高數(shù)據(jù)查詢，對結(jié)果進(jìn)行排序、分組的操作效率。
??? 索引的缺點(diǎn):優(yōu)點(diǎn)顯而易見，同樣缺點(diǎn)也是顯而易見:
??? 1：創(chuàng)建索引需要額外的磁盤空間，索引最大一般為表大小的1.2倍左右。
?? ?2：在表數(shù)據(jù)修改時(shí)，例如增加，刪除，更新，都需要維護(hù)索引表，這是需要系統(tǒng)開銷的。
?? ?3：不合理的索引設(shè)計(jì)非但不能利于系統(tǒng)，反而會(huì)使系統(tǒng)性能下降。例如我們在一個(gè)創(chuàng)建有非聚集索引的列上做范圍查詢，此列的索引不會(huì)起到任何的優(yōu)化效果，反而由于數(shù)據(jù)的修改而需要維護(hù)索引表,從而影響了對數(shù)據(jù)修改的性能。
??

???? 實(shí)際例子：還是拿前兩篇文章的學(xué)生表來講吧，要查詢成績在50分以上的學(xué)生信息select * from student where score>50。學(xué)生表包含了100000行記錄，而且學(xué)分是隨機(jī)生成的,這樣從數(shù)據(jù)量以及數(shù)據(jù)分布上都有一定的保障。

???? 第一種情況：學(xué)生表有索引。
????? 1：存在聚集索引，但聚集索引不在學(xué)分上,這里只分析學(xué)分不是聚集索引的情況。
????????? (1):學(xué)分上沒有索引。此時(shí)SQL會(huì)通過聚集索引來查找數(shù)據(jù)，這點(diǎn)估計(jì)大家都會(huì)知道。
???????? （2):學(xué)分上有索引。這種情況，SQL會(huì)使用上學(xué)分上的索引嗎？這個(gè)問題估計(jì)不是每個(gè)人都能回答正確的。既然學(xué)分上有索引，而where中又有此列，理應(yīng)使用了索引，但實(shí)際情況并沒有使用索引。因?yàn)槌霈F(xiàn)了范圍查找，如果一個(gè)索引一個(gè)索引的比較，在性能上比起直接按聚集索引查找全部數(shù)據(jù)后再過濾來的差。那學(xué)分上的索引什么時(shí)候? SQL會(huì)優(yōu)先考慮呢？當(dāng)score指定為一個(gè)具體值時(shí)，就能使用學(xué)分索引查找了。從下圖的SQL執(zhí)行計(jì)劃可以得知。

??????????????

????? 2:不存在聚集索引。
????????? (1):在學(xué)分上沒有索引，其它字段有索引，這種情況就會(huì)出現(xiàn)表掃描。
????????? (2):在學(xué)分上有索引，是否會(huì)按照學(xué)分上的索引進(jìn)行查找呢？由于上面的表數(shù)據(jù)量也不少，一般會(huì)認(rèn)為SQL不會(huì)采用表掃描，因?yàn)闀?huì)查找全部記錄，但實(shí)際情況表明SQL對于范圍查詢也行采用表掃描而不是按學(xué)生索引查詢。我們也可以強(qiáng)制SQL按學(xué)分查詢，于是有下面的SQL執(zhí)行計(jì)劃比較，我們可以清楚的看出，強(qiáng)制使用學(xué)分做為索引查詢比表搜索的性能要差很多。

?????????????

??? 第二種情況：學(xué)生表沒有索引。這個(gè)情況沒有分析的價(jià)值。

????
?? 什么字段不適合創(chuàng)建索引？
?? 1:不經(jīng)常使用的列，這種索引帶來缺點(diǎn)遠(yuǎn)大于帶來的優(yōu)點(diǎn)。
?? 2:邏輯性的字段，例如性別字段等等,匹配的記錄太多，和表掃描比起來不相上下。
?? 3:字段內(nèi)容特別大的字段，例如text等，這會(huì)大大增大索引所占用的空間以及索引更新時(shí)的速度。
??
?? 我們說SQL在維護(hù)索引時(shí)要消耗系統(tǒng)資源，那么SQL維護(hù)索引時(shí)究竟消耗了什么資源？會(huì)產(chǎn)生哪些問題？究竟怎樣才能優(yōu)化字段的索引？

?? 第一：當(dāng)數(shù)據(jù)頁達(dá)到了8K(數(shù)據(jù)頁最大為8K) 容量，如此時(shí)發(fā)生插入或更新數(shù)據(jù)的操作，將導(dǎo)致頁的分裂。
?? 1、聚集索引的情況下：聚集索引將被插入和更新的行指向特定的頁，該頁由聚集索引關(guān)鍵字決定；
?? 2、只有堆的情況下：有空間就可以插入新的行，對行數(shù)據(jù)的更新需要更多的空間，如果大于了當(dāng)前頁的可用空間，行就被移到新的頁中，且在原位置留下一個(gè)轉(zhuǎn)發(fā)指針，指向被移動(dòng)的新行，如果具有轉(zhuǎn)發(fā)指針的行又被移動(dòng)了，那么原來的指針將重新指向新的位置；
?? 3、堆中有非聚集索引，盡管插入和更新操作，不會(huì)發(fā)生頁分裂，但非聚集索引上仍然產(chǎn)生頁分裂。
?? 總結(jié)：無論有無索引，很多數(shù)據(jù)將保留在老頁面，其它將放入新頁面，并且新頁面可能被分配到任何可用的頁，頻繁頁分裂，表會(huì)產(chǎn)生大量數(shù)據(jù)碎片，直接造成I/O 效率下降。
??
?? 引出問題：為什么數(shù)據(jù)庫對于varchar最大值設(shè)置為8000,而不是10000呢？
?? 答：是由于數(shù)據(jù)頁大小最大為8K。
??
?? 第二：針對上述索引可能造成的頁分頁的解決方案，填充因子。
?? 創(chuàng)建索引時(shí)，可以為索引指定一個(gè)填充因子，在索引的每個(gè)葉級頁面上保留一定百分比的空間，將來數(shù)據(jù)可以進(jìn)行擴(kuò)充和減少頁分裂。值從0到100的百分比數(shù)值，100 時(shí)表示將數(shù)據(jù)頁填滿。不對數(shù)據(jù)進(jìn)行更改時(shí)(例如只讀表中)才用此設(shè)置，實(shí)用價(jià)值不大。值越小則數(shù)據(jù)頁上的空閑空間越大，可以減少在索引增長過程中進(jìn)行頁分裂，但需要占用更多的硬盤空間。填充因子也不能設(shè)置過小，過小會(huì)影響SQL的讀取性能，因?yàn)樘畛湟蜃釉斐蓴?shù)據(jù)頁的增多。一般我們公司設(shè)置的填充因子是80。
??
?? 索引是否是一塵不變的？
?? 隨著業(yè)務(wù)的變化，數(shù)據(jù)的變化，會(huì)發(fā)生有些索引的用處可能發(fā)生變化，例如：
?? 1:原來主要靠用戶名搜索記錄，現(xiàn)在業(yè)務(wù)更改為按用戶所在城市搜索等等，此時(shí)我們需要即時(shí)變更表索引以適應(yīng)新業(yè)務(wù)的變化,即數(shù)據(jù)和使用模式發(fā)生了大幅度變化。
?? 2:系統(tǒng)上線前不合理的索引，隨著數(shù)據(jù)的增加，缺點(diǎn)越來越明顯，此時(shí)需要調(diào)整索引。
?? 3:隨著數(shù)據(jù)的增加，產(chǎn)生了越來越多的頁分裂，導(dǎo)致索引性能越來越低。
??
?? 上面的幾種情況，我們就需要選擇重建索引來徹底解決問題。
??
?? 總結(jié)索引使用原則：
?? 1:不要索引數(shù)據(jù)量不大的表，對于小表來講，表掃描的成本并不高。
?? 2:不要設(shè)置過多的索引，在沒有聚集索引的表中，最大可以設(shè)置249個(gè)非聚集索引，過多的索引首先會(huì)帶來更大的磁盤空間，而且在數(shù)據(jù)發(fā)生修改時(shí)，對索引的維護(hù)是特別消耗性能的。
?? 3:合理應(yīng)用復(fù)合索引，有某些情況下可以考慮創(chuàng)建包含所有輸出列的覆蓋索引。
?? 4:對經(jīng)常使用范圍查詢的字段，可能考慮聚集索引。
?? 5:避免對不常用的列，邏輯性列，大字段列創(chuàng)建索引。

?

?? 有說的不對的地方，歡迎大家指正。

作者：姜敏
出處：http://www.cnblogs.com/aspnet2008/?

　

?

???

轉(zhuǎn)載于:https://www.cnblogs.com/ASPNET2008/archive/2010/12/19/1910218.html

《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的软件开发人员真的了解SQL索引吗(索引使用原则)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。