關(guān)于數(shù)據(jù)庫索引創(chuàng)建與優(yōu)化，首先需要明確的一點是，Mysql是一種插件式的數(shù)據(jù)存儲服務(wù)，MySQL只提供了一個server，而其實際存儲數(shù)據(jù)的工作是各個插件來實現(xiàn)的，這種形式的數(shù)據(jù)存儲方式會導(dǎo)致一個問題就是MySQL如果要對數(shù)據(jù)進行過濾，那么就得把可能符合條件的數(shù)據(jù)從存儲引擎中拉到MySQL server中，然后遍歷這些數(shù)據(jù)以獲取最終的數(shù)據(jù)，這會產(chǎn)生一個很大的問題就是，如果目標(biāo)數(shù)據(jù)量非常大，那么從存儲引擎中拉取的數(shù)據(jù)量將會非常大。數(shù)據(jù)庫索引的設(shè)計其實最終目的就是為了在MySQL從存儲引擎拉取數(shù)據(jù)之前先通過索引進行一部分的過濾，減少需要掃描的結(jié)果集大小，從而提升查詢的效率。
對于索引，從存儲數(shù)據(jù)的形式上來講，主要有兩種，一種是聚簇索引，另一種是二級索引，這兩種索引都是通過B+樹來組織的。對于聚簇索引，這里可以這么理解，MySQL在將數(shù)據(jù)存儲到磁盤時，數(shù)據(jù)在磁盤是有一個順序的，那么這里的順序就是使用聚簇索引來組織的，MySQL一般使用主鍵id作為聚簇索引。也就是說磁盤上的數(shù)據(jù)存儲的數(shù)據(jù)就是主鍵id的順序，而索引B+樹的葉節(jié)點上存儲了當(dāng)前數(shù)據(jù)在磁盤的地址值。對于二級索引，數(shù)據(jù)庫中所有除了聚簇索引以外的索引都是二級索引，二級索引與聚簇索引最大的區(qū)別就是二級索引的葉節(jié)點存儲的是聚簇索引的主鍵id值。在使用二級索引進行查詢的時候，首先通過查詢字段定位到二級索引的一部分葉節(jié)點，獲取對應(yīng)的主鍵id，然后通過id在聚簇索引中查詢對應(yīng)的數(shù)據(jù)。
常用的二級索引有單列索引，聯(lián)合索引，字符串前綴索引和hash索引。
這里單列索引就是簡單的為某個列建一個索引，單列索引主要需要注意的問題是字段的選擇性，如果一個字段的選擇性比較差，那么以該字段建立索引其實意義不大，因為根據(jù)索引過濾之后，MySQL還是需要在磁盤上拉取大量的數(shù)據(jù)。不過單列索引有一個好處就是可以進行索引合并掃描，比如查詢條件是A or B，這個時候為A和B兩列分別建立一個單列索引，MySQL就會用到這兩個索引，然后將過濾后的結(jié)果進行合并，再到磁盤上拉取數(shù)據(jù)。
對于聯(lián)合索引，這是使用最多的一個索引，其優(yōu)點主要有四個：①如果查詢條件是等值條件，那么只要這些等值條件與聯(lián)合索引的前綴相匹配，那么就可以使用到索引進行過濾；②如果查詢條件既有等值條件，也有非等值條件，那么如果聯(lián)合索引有覆蓋非等值條件的字段，MySQL就可以使用到索引覆蓋掃描，也就是對于非等值條件，雖然不能使用B+樹排序的特性，但是也可以直接從索引中進行數(shù)據(jù)掃描，而不用回到磁盤進行數(shù)據(jù)拉?。虎廴绻樵儣l件中有g(shù)roup by，order by，或者distinct等條件，這個時候如果group by，order by或distinct后的字段是聯(lián)合索引的最后一個字段，那么MySQL就可以不用再對數(shù)據(jù)進行這之類的處理了，因為在索引中數(shù)據(jù)就天然的已經(jīng)聚合，并且排序了；④如果查詢字段不多，那么建立一個聯(lián)合索引，將查詢條件和字段都放到索引里，這樣所需要的數(shù)據(jù)就都在索引里了，從而可以省略回磁盤讀取數(shù)據(jù)的過程了。在使用聯(lián)合索引的時候，有一個問題需要注意就是，聯(lián)合索引是有一個最左前綴匹配原則的，比如條件的字段是A和B，而聯(lián)合索引是(A，B，C)，由于索引的組織順序是按照先由A排序，當(dāng)A相等時再按照B排序，B相等時再按照C排序，這樣我們?nèi)绻皇褂肁和B兩個字段進行查詢，也是能夠使用到該聯(lián)合索引的，這里如果使用A和C字段進行查詢也能使用該索引，但只能首先根據(jù)A字段進行過濾，然后在過濾后的索引中全量掃描C字段，這樣雖然效率沒有使用前綴索引高，但是能夠使用索引覆蓋掃描，而不用回磁盤讀取數(shù)據(jù)。
對于字符串前綴索引，就是對于某些字符串字段比較長，并且前綴選擇性比較高的情況來創(chuàng)建的一個索引，MySQL是內(nèi)在支持這種索引的，原理就是取該字段的某一個前綴來組成一個索引。這種索引存在兩個問題：①前綴索引只能進行等值條件查詢，無法像聯(lián)合索引一樣對group by，distinct和order by等進行支持；②前綴長度的選擇需要通過計算字段的選擇性來決定，也就是分別取前綴為2，3，4，5等長度，看其選擇性與全字段建索引的選擇性是否一致。
對于hash索引，MySQL內(nèi)部是不支持的，不過我們可以通過冗余字段的方式來創(chuàng)建hash索引，比如對于一個很長的字符串字段，我們經(jīng)常需要對該字段進行單點查詢，那么這個時候直接為該字段建立前綴索引可能會導(dǎo)致索引片厚度太大，我們就可以新建一個列，用于存儲這個字符串字段值得hash值，因為都是整型值，我們這個時候就可以為這個新建的字段創(chuàng)建索引了。那么查詢的時候我們首先根據(jù)查詢條件按照同一種hash方式將查詢的數(shù)據(jù)映射為一個hash值，然后通過新建的hash字段定位到這個hash值所對應(yīng)的數(shù)據(jù)記錄，然后逐條比較其與我們需要的結(jié)果是否一致。