索引對于數(shù)據(jù)庫的性能是非常重要的撤蚊，尤其是隨著數(shù)據(jù)量越來越大河绽。如果數(shù)據(jù)量比較少沒有索引數(shù)據(jù)庫性能可能表現(xiàn)也還不錯巢钓，但是隨著數(shù)據(jù)增多位喂，數(shù)據(jù)庫性能下降的的會非常厲害，因此索引對于數(shù)據(jù)庫的重要性是不言而喻的罢洲，索引優(yōu)化可能是提高數(shù)據(jù)庫性能的最佳方式踢故。今天就來學(xué)習(xí)一下數(shù)據(jù)庫索引方面知識點，這個也是面試必問的問題了惹苗，所以重新學(xué)習(xí)一下還是非常有必要的殿较，畢竟金三銀四的季節(jié)已經(jīng)來了。
一鸽粉、索引基本知識
1斜脂、什么是索引
反正每次說到數(shù)據(jù)庫優(yōu)化大家一般第一個想到的就是索引，但是什么是索引呢触机？簡單的說索引就是數(shù)據(jù)庫系統(tǒng)中的一種數(shù)據(jù)結(jié)構(gòu)帚戳，方便快速查詢和更新數(shù)據(jù)庫表中的數(shù)據(jù)玷或。至于它的工作原理下面再講。
2片任、索引類型
這里說的類型指的是索引的數(shù)據(jù)結(jié)構(gòu)類型偏友。索引的類型有很多，不同的索引類型有著不同的目的对供。索引實現(xiàn)是在存儲引擎這一層級位他，而不是服務(wù)端，這樣索引在不同的存儲引擎可能會有不同产场。我們看下mysql數(shù)據(jù)庫都支持哪些類型以及它們的優(yōu)缺點鹅髓。
2.1 B-Tree索引
mysql的存儲引擎基本都支持B-Tree索引，但是有一點需要理清楚京景，我們說使用B-Tree索引窿冯，但是存儲引擎內(nèi)實現(xiàn)的數(shù)據(jù)結(jié)構(gòu)可能并不一定就是B-tree，意思索引名稱是B-Tree确徙，但是實際使用的數(shù)據(jù)結(jié)構(gòu)并不是B-tree醒串。比如mysql默認(rèn)的存儲引擎InnoDB，它的數(shù)據(jù)結(jié)構(gòu)實際上是B+ tree鄙皇。關(guān)于數(shù)據(jù)結(jié)構(gòu)內(nèi)容這里就不做介紹了芜赌，但是面試的時候也會經(jīng)常問到B Tree和B+ Tree的區(qū)別，建議看看以B tree和B+ tree的區(qū)別來分析mysql索引實現(xiàn)伴逸。
2.1 hash 索引
hash索引是建立在hash表上的缠沈，并且只有精確匹配索引所有列的查詢才有效，它是不支持范圍查詢的违柏。存儲引擎對所有的索引列計算出一個hash code博烂，將其存儲在索引中，同時哈希表中保存每個數(shù)據(jù)行的指針漱竖。這樣，對于此種索引查找速度是非承蠓ィ快的馍惹。出現(xiàn)哈希碰撞的話，索引會以鏈表的形式存放多個記錄指針到同一個哈希條目中玛界。在MySQL中只有內(nèi)存存儲引擎（數(shù)據(jù)存儲在內(nèi)存中万矾，而不是硬盤上）顯性支持hash索引。
2.3 Spatial(R-Tree)索引
MyISAM引擎支持這種索引慎框，但是MySQL支持并不好良狈，因此人們一般不使用，而是使用PostgreSQL笨枯。
2.4 full-text索引
即全文本索引薪丁，以前好像只有MyISAM支持遇西，現(xiàn)在InnoDB也支持全文本索引。全文本索引指的是在文本中查找關(guān)鍵字而不是直接比較索引中的值严嗜。全文本索引只能使用在數(shù)據(jù)類型為 CHAR粱檀、VARCHAR和TEXT這三種類型。
除了上面說的這幾種漫玄，還有一些第三方存儲引擎的索引類型茄蚯，這里就不再做介紹了，感興趣的可以查閱相關(guān)資料睦优。
3渗常、索引的優(yōu)勢
幫助加快數(shù)據(jù)查詢速度只是索引的優(yōu)點之一，除此之外汗盘，以最常用的B-Tree索引為例凳谦，還有以下幾個好處：
i 減少服務(wù)器必須檢查數(shù)據(jù)量；
ii 幫助服務(wù)器避免排序和臨時表衡未；
iii 將隨機I/O轉(zhuǎn)為有序I/O
二尸执、索引策略
1、隔離列
除非在查詢中隔離列缓醋，否則MySQL通常不能使用列上的索引如失，“隔離”意思是在語句中，它不能是表達(dá)式的一部分或者在一個函數(shù)內(nèi)部送粱。比如:
select * from t_user where id + 1 = 4;
其實這個查詢就不會使用到索引褪贵，因為查詢中使用了表達(dá)式“id + 1 = 4”，我們使用explain來對比一下結(jié)果：
根據(jù)上面的圖片可以看出來抗俄，表達(dá)式查詢結(jié)果type為ALL脆丁，rows為4，這樣的查詢效率是非常低的动雹。對比第二種查詢槽卫，type為const，rows為1胰蝠。之所以有這樣的區(qū)別就是表達(dá)式中查詢的時候沒有走索引歼培。
關(guān)于explain的詳解可以參考MySQL Explain 使用詳解，對返回結(jié)果的解釋都很清楚茸塞。
2躲庄、多列索引
因為索引的種種好處，尤其是提高查詢速度方面的優(yōu)點钾虐，有人覺得在表的每個列上都加上索引是不是一個好的選擇噪窘，想象很美好，但是現(xiàn)實是不行的效扫。首先索引會占用一部分空間倔监，而且索引也是需要維護(hù)直砂，這樣可能會導(dǎo)致數(shù)據(jù)變更時速度很慢。另外就是索引的順序丐枉，因為索引遵循的最左優(yōu)先的原則哆键，而如果sql問題依然是不能使用的。因此瘦锹，在多個列上建立獨立索引在大多情況下并不能提高M(jìn)ySQL的查詢性能籍嘹。MySQL在5.0版本中引入了“index merge”的策略，一定程度上可以使用表上的多個單列索引來定位指定的行弯院。
索引合并策略使用時機：
當(dāng)服務(wù)器需要對多個索引做相交操作（即多個and條件）辱士，通常意味著需要一個包含所有相關(guān)列的多列索引，而不是多個獨立的單列索引听绳。
當(dāng)服務(wù)器需要對多個索引做聯(lián)合操作（多個or條件）颂碘，這種情況通常需要消耗大量的CPU和內(nèi)存資源在算法的緩存、排序記憶合并操作上椅挣。特別是當(dāng)其中有些索引的選擇性不高头岔，需要合并掃描返回大量數(shù)據(jù)的時候。
還是通過一個實際操作來展示吧鼠证，現(xiàn)在給我的表上添加兩個單列索引峡竣，然后通過查詢看看結(jié)果是怎么樣的。
看下圖：
根據(jù)上圖可以看出量九，單列索引在使用and查詢時是沒有生效的适掰；而使用or查詢時，用到了索引荠列，type類型為"index_merge"类浪，使用到的索引為PRIMARY和age_index。
現(xiàn)在刪除除主鍵索引之外的所有單列索引肌似，并新建一個多列索引费就，同樣使用and和or查詢，如下圖：
因為我在列age和mobile兩列上加了一個索引锈嫩，根據(jù)結(jié)果可以看出使用and查詢和單列查詢時使用了索引受楼，而是用or查詢沒有使用索引，使用的是全表掃描呼寸。
3、順序選擇
創(chuàng)建多列索引時列的順序是非常重要的猴贰，合理的索引順序取決于你使用這個索引查詢的內(nèi)容对雪，而且必須要如何更好的滿足排序和聚集。在一個多列B－Tree索引中列順序意味著這個索引按照最左列進(jìn)行排序米绕，其次是第二列瑟捣，依此類推馋艺。并且，索引可以使用正向或者方向掃描迈套，以滿足order by捐祠、group by、distinct等與列順序匹配的查詢需求桑李。
有一比較老的規(guī)則是將查詢最多列放在最前面踱蛀，這個好像沒什么問題，我以前也覺得就該這樣贵白。在某些情況下這么做是沒問題的率拒，但是更多情況下它并不如避免隨機I/O和排序重要。在不需要考慮排序或者聚集時把最常查詢的列放在第一位可能是好的注意禁荒，在這種情況下索引的目的僅僅是為了優(yōu)化where查詢猬膨。下面看一個簡單的例子：
select * from t_user where age = 22 and address = "us";
這種情況下我們的索引應(yīng)該怎么創(chuàng)建，age_address_index(age,address)還是說應(yīng)該反過來呛伴？?查詢一下滿足每個條件的行總數(shù)勃痴，如下圖：
根據(jù)上圖可見滿足age=4的數(shù)量是小于address="us"的數(shù)量的（說明情況就行），所以將age列放在第一列是合適的热康。
或者根據(jù)列的可選擇性判斷哪一列放在前面合適沛申，如下圖：
根據(jù)結(jié)果可以看出age列具有更高的可選擇性，再次推斷出將age列放在第一位褐隆。
上面只是比較簡單的一種污它，實際上索引的優(yōu)化還是有很多內(nèi)容的，網(wǎng)上也有不少的資料庶弃。
4衫贬、聚簇索引
聚簇索引不是一種獨立索引類型，而是一種數(shù)據(jù)存儲方式歇攻，不同數(shù)據(jù)庫存儲引擎的實現(xiàn)細(xì)節(jié)也各有不同固惯，而且，也不是所有的存儲引擎都支持聚簇索引缴守。MySQL的InnoDB引擎將索引和數(shù)據(jù)行存儲在同一個數(shù)據(jù)結(jié)構(gòu)中葬毫，當(dāng)一個表有聚簇索引的時候，它數(shù)據(jù)行實際上存儲在索引的葉子頁屡穗，“聚簇”意思就是說具有相鄰鍵值的行彼此靠近存儲贴捡，也就是說如果鍵的值相鄰，那么他們對應(yīng)的數(shù)據(jù)也會相鄰存儲村砂。每一個表只能有一個聚簇索引烂斋，因為你不可能一次把你的數(shù)據(jù)行存放到兩個地方（覆蓋索引是可以模擬多個聚簇索引的，有興趣可以的可以網(wǎng)上看下聚簇索引）。
下面這個圖片展示了一個聚簇索引的數(shù)據(jù)布局汛骂。葉子頁包含了所有數(shù)據(jù)行罕模，但是節(jié)點頁只包含索引的列，這種情況下索引列包含整數(shù)值帘瞭。
InnoDB引擎通過主鍵對數(shù)據(jù)進(jìn)行“聚集”淑掌，也就是說上圖中“索引列”就是主鍵列。如果沒有定義主鍵蝶念，InnoDB會嘗試使用唯一非空索引替換抛腕，如果沒有這樣索引，那么InnoDB會定義一個隱藏的主鍵祸轮，并將數(shù)據(jù)聚集到這個主鍵上去兽埃。 InnoDB僅僅在頁面內(nèi)將記錄聚集在一起， 具有相鄰鍵值的頁可能彼此遠(yuǎn)離适袜。一個聚集主鍵值確實提高性能柄错，但是它也可能導(dǎo)致嚴(yán)重的問題，所以還是應(yīng)該仔細(xì)考慮聚集這種情況苦酱，尤其是將表存儲引擎從InnoDB改變成其他時候售貌。
聚集索引有以下幾個好處：
1、將相關(guān)聚集到一起疫萤，這個主要應(yīng)該是減少了隨機I/O颂跨。
2、數(shù)據(jù)訪問速度更快扯饶，聚簇索引同時將索引和數(shù)據(jù)保存在一個B-Tree中恒削，因此從一個聚集索引中獲取數(shù)據(jù)要比從一個非聚集索引通過比較查詢數(shù)據(jù)速度更快。
3尾序、使用覆蓋索引的查詢時可以使用葉節(jié)點中包含的主鍵值钓丰。
同樣，聚集索引也有缺點：
1每币、聚集為I / O綁定工作負(fù)載提供了最大的改進(jìn)携丁。但是如果數(shù)據(jù)保存在內(nèi)存中，那么它的訪問順序并不重要兰怠，聚集索引不會帶來太多好處梦鉴。
2、插入數(shù)據(jù)的速度在很大程度上依賴插入的順序揭保，以主鍵順序插入行是將數(shù)據(jù)加載到InnoDB表中的最快方法肥橙。如果沒有按照主鍵順序加載數(shù)據(jù)行，那么在加載大量數(shù)據(jù)之后使用OPTIMIZE_TABLE重新組織表是一個好主意秸侣。
3快骗、更新聚簇索引的開銷比較大娜庇，因為它強制將每一行需要更新數(shù)據(jù)都移動到新的位置塔次。
4方篮、當(dāng)插入新的數(shù)據(jù)行行或者因為更新行的主鍵而導(dǎo)致必須移動行時，基于聚簇索引構(gòu)建的表將進(jìn)行頁面拆分励负。當(dāng)行的鍵值表面必須將行放入充滿數(shù)據(jù)的頁時就會發(fā)生頁面拆分藕溅。存儲引擎必須將頁面拆分為兩個
容納行，頁面拆分可能會導(dǎo)致表使用更多的磁盤空間继榆。
5巾表、聚簇索引可能會導(dǎo)致全表掃描速度變慢，尤其是因為頁面拆分導(dǎo)致數(shù)據(jù)行不那么密集地打包或非順序存儲時略吨。
6集币、輔助（非聚簇）索引可能比預(yù)期的要大，因為它們的葉節(jié)點包含引用行的主鍵列翠忠。
7鞠苟、二級索引訪問需要兩個索引查找而不是一個。之所以需要經(jīng)過兩個索引查找是因為存儲在輔助索引的“行指針”的特性秽之，葉子節(jié)點存儲的不是引用數(shù)據(jù)行物理位置的指針当娱，而是數(shù)據(jù)行的主鍵值。也就意味著使用輔助索引查找數(shù)據(jù)行時考榨，存儲引擎先查找到輔助索引中存儲的葉子節(jié)點跨细，然后使用存儲的主鍵值定位到主鍵然后查詢到數(shù)據(jù)行。
5河质、覆蓋索引
通常情況下都建議為where條件查詢創(chuàng)建索引冀惭，這個當(dāng)然是正確的，但是索引不應(yīng)該僅僅為了where查詢創(chuàng)建掀鹅，而是應(yīng)該為所有查詢設(shè)計散休。索引確實是一種查詢數(shù)據(jù)行的有效方式，MySQL也可以直接通過索引去獲取某列的數(shù)據(jù)淫半，而不需要讀取所有的數(shù)據(jù)行溃槐。畢竟這個索引的葉子節(jié)點就包含了它們索引的值，所以當(dāng)索引本身就包含了你想要的數(shù)據(jù)時候為什么還要去讀取數(shù)據(jù)行呢科吭？
所以什么是覆蓋索引昏滴？包含（或者說“覆蓋”）滿足一個查詢所需的所有數(shù)據(jù)的索引就稱為覆蓋索引。簡單點說对人，就是這個索引本身包含了你想要的數(shù)據(jù)谣殊。覆蓋索引是一個很強大的工具，可以極大提升數(shù)據(jù)庫性能牺弄∫黾福考慮下只讀取索引而不讀取數(shù)據(jù)行的好處：
1、索引條目本身要比完整數(shù)據(jù)行小得多，因此MySQL可以訪問更少的數(shù)據(jù)如果它只讀取索引的話蛇捌。這對于緩存工作負(fù)載非常重要抚恒，其中大部分響應(yīng)時間來自復(fù)制數(shù)據(jù)。 并且它對I/O綁定工作負(fù)載也很有幫助络拌，因為索引比數(shù)據(jù)更小俭驮、更適合內(nèi)存。
2春贸、索引根據(jù)它們的索引值排序（至少在頁面內(nèi)）混萝，因此I/O綁定的范圍訪問與訪問隨機硬盤位置上每一行相比，需要更少的的I/O萍恕。
3逸嘀、某些存儲引擎（如MyISAM）僅緩存MySQL內(nèi)存中的索引。 由于操作系統(tǒng)為MyISAM引擎緩存數(shù)據(jù)允粤，因此訪問它通常需要系統(tǒng)調(diào)用崭倘。，這可能會對性能產(chǎn)生巨大影響维哈，尤其是對于緩存的工作負(fù)載绳姨，即系統(tǒng)調(diào)用非常昂貴部分的數(shù)據(jù)訪問。
4阔挠、覆蓋索引對InnoDB引擎的數(shù)據(jù)庫表是非常有幫助的飘庄，因為InnoDB的聚簇索引的。InnoDB的輔助索引在它們的葉子節(jié)點保存了行的主鍵值购撼，這樣一個覆蓋查詢輔助索引避免了其它索引在主鍵中查找跪削。
覆蓋索引并不是什么索引都可以，這個索引必須存儲有它包含列的值迂求。MySQL中只能使用B－Tree索引來覆蓋查詢碾盐，不同的存儲引擎實現(xiàn)覆蓋查詢的方式也不相同，也不是所有的存儲引擎都支持覆蓋查詢揩局。
今天主要是簡單了解了一下數(shù)據(jù)庫索引的一些知識毫玖，因為最近又要出去面試了，感覺自己數(shù)據(jù)庫方面挺薄弱的凌盯，優(yōu)化什么的了解的都比較少付枫，所以想臨時突擊一下。我看的是《高性能MySQL》這本書驰怎，感覺還是很不錯的阐滩，只是缺少一些新的特性，畢竟MySQL已經(jīng)更新到8.0版本了县忌。另外這本書也挺厚的（700多頁）掂榔，有時候真的沒有拿起來看得欲望（尷尬）继效。自己實際開發(fā)中也缺少大量數(shù)據(jù)開發(fā)場景，所以數(shù)據(jù)庫優(yōu)化方面經(jīng)驗比較少装获，不過實際歸實際瑞信，理論知識也還是要具備的，畢竟理論指導(dǎo)實踐嘛饱溢。最后一點我覺得還是重視數(shù)據(jù)結(jié)構(gòu)和算法方面的知識喧伞，真的太太太重要了。好了绩郎，今天學(xué)習(xí)先到這里了，后面有時間繼續(xù)學(xué)習(xí)數(shù)據(jù)庫優(yōu)化方面的知識翁逞。
