一丶什么是索引

索引是存儲(chǔ)引擎快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。數(shù)據(jù)庫(kù)中的數(shù)據(jù)可以理解成字典中的單詞，而索引就是目錄，顯而易見這是一種空間換時(shí)間的做法，目錄占用了空間，但是加快了我們找到單詞的速度，正如索引需要空間存儲(chǔ)，但是利用索引我們可以快速的找到想要的數(shù)據(jù)。

InnoDB存儲(chǔ)引擎存在幾種常見的索引：

B+樹索引 全文索引 哈希索引

本文主要討論B+樹索引

二丶索引的數(shù)據(jù)結(jié)構(gòu)

可以加快查找速度的數(shù)據(jù)結(jié)構(gòu)很多，為什么mysql使用B+樹來(lái)實(shí)現(xiàn)昵，換句話說(shuō)哈希表，有序數(shù)組，跳表，平衡二叉搜索樹，B-樹等都可以優(yōu)化搜索效率，為什么偏偏使用B+樹

1.哈希表

哈希表，可以聯(lián)想Java中的HashMap，在HashMap源碼學(xué)習(xí)中，我們了解到Hash表的數(shù)據(jù)結(jié)構(gòu)。如下圖

哈希表通過(guò)hash算法將key映射到數(shù)組對(duì)應(yīng)的下標(biāo)進(jìn)行存儲(chǔ)，不可避免的會(huì)產(chǎn)生hash沖突（多個(gè)不同的key散列到相同的數(shù)組下標(biāo)中），解決hash沖突常用拉鏈法，顧名思義，就是把相同hash值的節(jié)點(diǎn)組成鏈表串起來(lái)。在根據(jù)key查找value的過(guò)程中，只需要再次使用相同的hash算法那么就能拿到對(duì)應(yīng)的數(shù)組下表，然后遍歷鏈表找到目標(biāo)值即可,查找的效率是o(1)。

明明存在鏈表需要遍歷為什么說(shuō)時(shí)間復(fù)雜度o(1)
首先hash算法計(jì)算是常數(shù)時(shí)間，
hash表會(huì)在需要的時(shí)候進(jìn)行擴(kuò)容，
維持鏈表長(zhǎng)度盡量在一個(gè)常數(shù)范圍，從而保證遍歷常數(shù)個(gè)鏈表節(jié)點(diǎn)

mysql中存在自適應(yīng)哈希索引，由innodb存儲(chǔ)引擎自己控制，利用查找O(1)的性質(zhì)優(yōu)化等值查詢。我們可以看出，hash表并不適合范圍查詢，對(duì)于Id<10這種范圍查詢只能遍歷hash表中每一個(gè)數(shù)據(jù)，相當(dāng)于要進(jìn)行一次全表掃描。我還有一個(gè)想法：從擴(kuò)容的角度看，每次擴(kuò)大數(shù)組大小后都需要移動(dòng)元素到新的數(shù)組空間中，這部分的復(fù)制移動(dòng)的開銷也許也是hash表不合適的原因（redis為了解決這個(gè)問題，使用漸進(jìn)式hash的方式，在擴(kuò)容的時(shí)候生成更大的數(shù)組，但不是一次移動(dòng)所以數(shù)據(jù)，而是插入的新元素都放到新數(shù)組，老數(shù)組使用到的數(shù)據(jù)才會(huì)慢慢移動(dòng)到新數(shù)組）redis基于內(nèi)存的數(shù)據(jù)庫(kù)都需要通過(guò)漸進(jìn)式hash優(yōu)化擴(kuò)容操作，基于磁盤的mysql若使用hash將慘不忍睹

2.有序數(shù)組

有序數(shù)組就是數(shù)據(jù)中元素有序，正因?yàn)橛行?，所以其在范圍查找上非常?yōu)秀，正因?yàn)橐S持有序，在更改數(shù)據(jù)的時(shí)候，也許需要移動(dòng)大量數(shù)組元素（比如插入一個(gè)較小的值，大于此值的數(shù)據(jù)都需要后移動(dòng)），所以有序數(shù)據(jù)只適用于靜態(tài)數(shù)據(jù)（比如2020年人口信息，這種不會(huì)改變的數(shù)據(jù)）

3.跳表

為了解決有序數(shù)組需要移動(dòng)元素的問題，我們可以使用鏈表來(lái)維護(hù)元素，從而使更改元素效率為o(1)，但是鏈表的查找非常慢。由于鏈表整體是有序的，那么我們可以使用二分查找優(yōu)化查找效率，如上我們建立多級(jí)的節(jié)點(diǎn)，在查找的時(shí)候我們首先通過(guò)多級(jí)的索引依次找到最下層。對(duì)于范圍查找，由于底層數(shù)據(jù)是有序的，查找id<7的數(shù)組，首先我們找到id=7然后向左遍歷集合（可以把跳表最下面一層優(yōu)化為雙向鏈表，從而讓范圍查找速度也很快）

哪為什么不使用跳表來(lái)做索引昵？

跳表是鏈表結(jié)構(gòu)，一條數(shù)據(jù)一個(gè)結(jié)點(diǎn)，如果最底層要存放2kw數(shù)據(jù)，且每次查詢都要能達(dá)到二分查找的效果，2kw大概在2的24次方左右，所以，跳表大概高度在24層左右。最壞情況下，這24層數(shù)據(jù)會(huì)分散在不同的數(shù)據(jù)頁(yè)里，也即是查一次數(shù)據(jù)會(huì)經(jīng)歷24次磁盤IO。

4.平衡二叉搜索樹

二叉搜索樹，即左子樹小于根，根小于右子樹，這種結(jié)構(gòu)在查找的時(shí)候可以進(jìn)行二分，根據(jù)根節(jié)點(diǎn)的值就可以確定期望的數(shù)據(jù)在左樹還是右樹。

但是二叉搜索樹在插入，刪除節(jié)點(diǎn)的時(shí)候可能出現(xiàn)樹極度不平衡的情況，出現(xiàn)樹退化成鏈表。

這個(gè)時(shí)候就需要維持樹的平衡——AVL:在滿足二叉搜索樹的條件下，要求任何節(jié)點(diǎn)的兩個(gè)子樹高度差不超過(guò)1。平衡二叉樹的查找是趨近于O(log(N))，但是需要維護(hù)一顆樹為AVL需要進(jìn)行左旋，右旋的操作，更新的時(shí)間復(fù)雜度也是 O(log(N))。

為什么不使用AVL做索引：節(jié)點(diǎn)存儲(chǔ)的數(shù)據(jù)內(nèi)容太少。因?yàn)椴僮飨到y(tǒng)和磁盤之間一次數(shù)據(jù)交換是以頁(yè)為單位的，一頁(yè) = 4K，即每次IO操作系統(tǒng)會(huì)將4K數(shù)據(jù)加載進(jìn)內(nèi)存。但是，在二叉樹每個(gè)節(jié)點(diǎn)的結(jié)構(gòu)只保存一個(gè)關(guān)鍵字，一個(gè)數(shù)據(jù)區(qū)，兩個(gè)子節(jié)點(diǎn)的引用，并不能夠填滿4K的內(nèi)容。幸幸苦苦做了一次的IO操作，卻只加載了一個(gè)關(guān)鍵字，在樹的高度很高，恰好又搜索的關(guān)鍵字位于葉子節(jié)點(diǎn)或者支節(jié)點(diǎn)的時(shí)候，取一個(gè)關(guān)鍵字要做很多次的IO。

5.B-樹，B+樹

B-樹就是B樹，英文是B-Tree，所以國(guó)內(nèi)有許多人稱之為B-樹。B樹和B+樹是多路平衡查找樹，之所以多路，是為了契合磁盤的io操作——操作系統(tǒng)和磁盤之間一次數(shù)據(jù)交換是以頁(yè)為單位的，多路能讓讀取一頁(yè)能獲取更多的數(shù)據(jù)，讓樹的高度更低。

上面兩圖，我們可以看出B樹和B+樹的區(qū)別

B+樹葉子節(jié)點(diǎn)使用雙向指針串聯(lián)起來(lái)，這讓B+樹相比于B樹更加適合范圍查找 B+樹非葉子節(jié)點(diǎn)并不存數(shù)據(jù)，所以每次查找數(shù)據(jù)都必須遍歷到葉子節(jié)點(diǎn)，時(shí)間復(fù)雜度穩(wěn)定為O(logN)，B-樹在運(yùn)氣好的時(shí)候可以在根節(jié)點(diǎn)直接拿到數(shù)據(jù)。但是正是因?yàn)榉侨~子節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)，可以讓一次磁盤讀取一頁(yè)中包含的索引數(shù)據(jù)更多，每個(gè)節(jié)點(diǎn)能索引的范圍更大更精確，讓我們可以更改定位到期望的數(shù)據(jù)。由于B+樹的葉子節(jié)點(diǎn)的數(shù)據(jù)都是使用鏈表連接起來(lái)的，而且他們?cè)诖疟P里是順序存儲(chǔ)的，所以當(dāng)讀到某個(gè)值的時(shí)候，磁盤預(yù)讀原理就會(huì)提前把這些數(shù)據(jù)都讀進(jìn)內(nèi)存，使得范圍查詢和排序都很快

B+樹在更改數(shù)據(jù)的時(shí)候，為了保證樹的平衡可能存在節(jié)點(diǎn)的分裂和合并，所以我們一般建議使用自增主鍵，在插入的時(shí)候，不會(huì)頻繁的發(fā)生節(jié)點(diǎn)的分裂。

三丶InnoDB索引方案

1.InnoDB行結(jié)構(gòu)

InnoDB存儲(chǔ)引擎存儲(chǔ)一行數(shù)據(jù)使用的數(shù)據(jù)結(jié)構(gòu)稱為行結(jié)構(gòu)。

COMPACT

變長(zhǎng)字段列表：如varchar(m),Text，Blob類型的列，稱為變長(zhǎng)字段，由于其字節(jié)數(shù)量不固定，需要在變成字段列表中存儲(chǔ)這些字段的長(zhǎng)度，在記錄的真實(shí)數(shù)據(jù)中存儲(chǔ)內(nèi)容 null值列表：如果表中沒用允許為null的列，那么null值列表就不存在，否則把每一個(gè)允許為null的列使用一個(gè)二進(jìn)制位來(lái)表示，二進(jìn)制為1的時(shí)候表示值為null 記錄頭信息：占用五個(gè)字節(jié)，其中包含delete_flag(標(biāo)記記錄是否被刪除)，next_record(下一條記錄的相對(duì)位置)等信息 記錄的真實(shí)信息：如果表沒用定義主鍵，也沒有唯一不可重復(fù)不可為null的列，那么innodb為我們生成一個(gè)隱藏列row_id,如果定義了主鍵那么此列不存在，并且還有trx_id,roll_pointer兩個(gè)隱藏列，后續(xù)便是每一個(gè)列的真實(shí)數(shù)據(jù)。（char(M)類型的列，如果使用定長(zhǎng)字符編碼，那么字節(jié)數(shù)不會(huì)加到變長(zhǎng)字段列表中，如果使用變長(zhǎng)編碼，占用長(zhǎng)度會(huì)加入到變成字段列表中（變長(zhǎng)編碼那么必須占用M個(gè)字節(jié)，varchar(M）則沒用這個(gè)要求）

REDUNDANT

字段長(zhǎng)度偏移列表：此種行格式會(huì)把記錄所有列長(zhǎng)度的偏移信息存儲(chǔ) null值的處理方式：先看偏移量的null比特位是否為1，如果為了那么表示為null

溢出列

如果一個(gè)列太長(zhǎng)，并不會(huì)傻乎乎的存儲(chǔ)所有數(shù)據(jù)在行記錄中，而是使用溢出列，COMPACT和REDUNDANT只會(huì)存儲(chǔ)該列的前768字節(jié)然后存儲(chǔ)指向其他頁(yè)的地址，剩下的數(shù)據(jù)存在其他頁(yè)中。

DYNAMIC和COMPRESSED

和COMPACT類似，但是二者不會(huì)存儲(chǔ)過(guò)長(zhǎng)列的前768字節(jié)，而是把真實(shí)數(shù)據(jù)都存儲(chǔ)到溢出中，記錄只存儲(chǔ)溢出頁(yè)的地址。COMPRESSED 還會(huì)使用壓縮算法對(duì)頁(yè)面進(jìn)行壓縮

2.InnoDB頁(yè)結(jié)構(gòu)

頁(yè)是InnoDB管理存儲(chǔ)空間的基本單位，其默認(rèn)大小為16k,InnoDB設(shè)計(jì)了很多不同的頁(yè)結(jié)構(gòu)：存放Change Buffer的頁(yè)，存儲(chǔ)undo log 日志的頁(yè)等等。對(duì)于表中數(shù)據(jù)，也存在在頁(yè)中

最開始的時(shí)候UserRecords并不存在，隨著數(shù)據(jù)的插入，會(huì)從FreeSpace中申請(qǐng)一個(gè)記錄大小的空間，將其劃分到UserRecords部分，當(dāng)FreeSpace用完只會(huì)繼續(xù)插入就需要申請(qǐng)新的頁(yè)。

2.1行結(jié)構(gòu)中記錄頭信息的作用

deleted_flag:標(biāo)記是否被刪除，1表示被刪除，被刪除的列表通過(guò)next_record串聯(lián)起來(lái)，并且會(huì)記錄被刪除記錄的空火箭，這部分空間可以重復(fù)使用

min_rec_flag: B+樹每層非葉子節(jié)點(diǎn)，最小的目錄項(xiàng)記錄會(huì)被添加此標(biāo)記

n_owned:

heap_no:UserRecords中存儲(chǔ)的用戶記錄是緊湊如同堆一樣排布的,heap_no是堆中記錄的編號(hào)，從2開始（0和1 被infimum+supremum占用，infimum虛擬的最小記錄，supremum虛擬最大記錄）

next_record:表示當(dāng)前記錄的真實(shí)數(shù)據(jù)，到下一條記錄的距離

next_record左邊是變長(zhǎng)字段列表和null值列表（二者都是逆序存放信息，也就是說(shuō)距離next_record最近的是第一個(gè)字段是否為null，第一個(gè)變長(zhǎng)字段的長(zhǎng)度）右邊是記錄的真實(shí)數(shù)據(jù)（順序存放），且可以使記錄中靠前字段和對(duì)應(yīng)的字段信息在內(nèi)存中更近，提高高速緩存的命中率。這里我們可以看到被刪除的記錄沒用立即被清除，只是不會(huì)被next_record串聯(lián)起來(lái)

記錄按照主鍵從小到大形成單向鏈表

2.2頁(yè)目錄

上面我們知道了記錄在頁(yè)中按照主鍵從小到大的順序串聯(lián)成單向鏈表，那么怎么在一個(gè)頁(yè)中根據(jù)主鍵找到目標(biāo)記錄昵——通過(guò)頁(yè)目錄進(jìn)行二分查找。

頁(yè)目錄生成過(guò)程

將infimum，supermum以及所有未被刪除的記錄，分成多個(gè)組 每一個(gè)組中最大的記錄的n_owned存儲(chǔ)組中記錄條數(shù) 將每一個(gè)組中最后的記錄在頁(yè)面中的地址偏移量，存儲(chǔ)到頁(yè)面尾部——Page Directory中，這些地址偏移量稱為槽

根據(jù)查詢頁(yè)面記錄的過(guò)程

通過(guò)二分法找到目標(biāo)記錄中的槽，然后遍歷槽所在組的所有記錄

3.InnoDB索引方案

3.1為頁(yè)建立目錄項(xiàng)

InnoDB使用也作為管理和存儲(chǔ)空間的基本單位，最多只能保證16k的連續(xù)存儲(chǔ)。

目錄項(xiàng)記錄的只是主鍵值和頁(yè)號(hào)兩個(gè)列，最下方是我們剛剛講到的innoDB存儲(chǔ)用戶記錄的頁(yè)。如果頁(yè)面數(shù)據(jù)量很大，可以繼續(xù)為目錄項(xiàng)建立目錄項(xiàng)

3.2 根據(jù)目錄項(xiàng)定位數(shù)據(jù)行的過(guò)程

例如查找主鍵為10記錄

根據(jù)目錄項(xiàng)中的內(nèi)容，確定目標(biāo)記錄所在的頁(yè)

如上圖頁(yè)33 存在記錄(1,30)，(320 32)，可以判斷主鍵位于1~320范圍的記錄在頁(yè)30，大于320的記錄在頁(yè)32

找到頁(yè)30后還要繼續(xù)在頁(yè)30中，通過(guò)目錄項(xiàng)記錄的頁(yè)確定目標(biāo)記錄真正所在的頁(yè)

在真正存儲(chǔ)用戶記錄的頁(yè)（頁(yè)28）中通過(guò)槽定位到組，然后遍歷槽所在組的所有記錄

三丶聚集索引和非聚集索引

InnoDB存儲(chǔ)引擎是索引組織表——表中的數(shù)據(jù)按照主鍵順序存放。非聚集索引也稱做輔助索引，無(wú)論是聚集還是非聚集，其原理都是一顆B+樹，葉子節(jié)點(diǎn)都存儲(chǔ)數(shù)據(jù)，不同的是聚集索引葉子節(jié)點(diǎn)存儲(chǔ)的是一整行的數(shù)據(jù)，非聚集索引葉子節(jié)點(diǎn)存儲(chǔ)的是聚集索引值（主鍵值）。

如果數(shù)據(jù)表定義了主鍵，那么這個(gè)主鍵索引就是聚集索引，如果沒有定義主鍵，mysql會(huì)選擇該表的第一個(gè)非空唯一的索引構(gòu)建聚集索引，如果都沒有那么mysql會(huì)生成一個(gè)隱藏的列（6字節(jié)的列，并且插入自增）

自增主鍵會(huì)把數(shù)據(jù)自動(dòng)向后插入，避免了插入過(guò)程中聚集索引節(jié)點(diǎn)分裂的問題。節(jié)點(diǎn)分裂會(huì)帶來(lái)大范圍的數(shù)據(jù)物理移動(dòng)，帶來(lái)磁盤IO的性能損耗，并且我們一般建議盡量不要改動(dòng)主鍵，主鍵的更改也會(huì)帶來(lái)page分裂，產(chǎn)生碎片。

四丶回表查詢

如上圖，假如我們有一張表存在三個(gè)字段id,age,name我們?cè)趇d上建立了主鍵索引，這時(shí)候id主鍵索引也是聚集索引，在age上建立了普通索引，這時(shí)候age索引就是非聚集索引。如果我們執(zhí)行select * from table where age=1這時(shí)候先走age索引（如果數(shù)據(jù)量較大，數(shù)據(jù)量少直接全表掃描了）那么會(huì)找到對(duì)應(yīng)的主鍵id，繼續(xù)到主鍵id索引中找到目標(biāo)數(shù)據(jù)，這個(gè)操作叫做回表。

這就是為什么根據(jù)主鍵查找快于根據(jù)其他索引列查找，因?yàn)槿绻渌饕袥]有包含我們select語(yǔ)句中需要的列（如果是select id from table where age<10,那么age索引是可以覆蓋到需要的數(shù)據(jù)的(葉子節(jié)點(diǎn)存儲(chǔ)了id)，那么也不會(huì)回表），那么會(huì)走主鍵索引拿到需要的數(shù)據(jù)，多了一步回表操作。

這里我們也可以看到為什么建議使用select * ,這意味著查找所有列，如果配合上普通索引，那么大概率這個(gè)普通索引不會(huì)覆蓋到索引列，導(dǎo)致需要回表查詢。并且select*這種"我全都要"大概率會(huì)查詢到我們不需要的列，造成不必要的網(wǎng)絡(luò)資源消耗，增加不必要的io，增加不必要的內(nèi)存消耗。

五丶聯(lián)合索引

聯(lián)合索引是指對(duì)表上的多個(gè)列建立索引，如上圖表存在四個(gè)字段id,address,name,age，我們?cè)趎ame和age上建立索引，上圖我們粗略的展示了聯(lián)合索引的B+樹結(jié)構(gòu)。我們可以觀察到在葉子節(jié)點(diǎn)中name是有序的，但是age無(wú)序，聯(lián)合索引是按照索引定義的順序排序的，這就導(dǎo)致select xxx from table where name='b'是可以根據(jù)上面定義的聯(lián)合索引查找數(shù)據(jù)的，但是``select xxx from table where age=12是無(wú)法走上面定義的聯(lián)合索引的。這就是常說(shuō)的最左前綴匹配原則`的原理。

聯(lián)合索引可以減少回表

如果我們執(zhí)行select age,id from table where name='a' and age=10,這個(gè)時(shí)候由于我們定義的聚集索引一級(jí)包含了需要的數(shù)據(jù)就不需要進(jìn)行回表操作了（這其實(shí)也被稱為覆蓋索引，即非聚集索引中可以查詢到全部需要的列，那么就不需要走聚集索引回表查詢數(shù)據(jù)）

聯(lián)合索引可以優(yōu)化排序

上圖中的聯(lián)合索引，我們可以看到，名稱相同的節(jié)點(diǎn)，其年齡是有序的

也就是說(shuō)select * from table where name='a' order by age這個(gè)語(yǔ)句將避免多一次的排序操作（select* from table where id=1 order by age會(huì)走主鍵索引拿到所有符合數(shù)據(jù)進(jìn)行排序，這里說(shuō)的避免一次排序操作指拿到的數(shù)據(jù)本身就是有序的 所有不需要再次排序）

索引下推ICP

全稱Index Condition PushDown,mysql 5.6后支持的一種根據(jù)索引進(jìn)行查詢優(yōu)化的操作。mysql數(shù)據(jù)庫(kù)會(huì)在取出所有數(shù)據(jù)的同時(shí)判斷是否進(jìn)行where條件的過(guò)濾，將where的部分過(guò)濾放在存儲(chǔ)引擎層。

mysql5.6之前如果執(zhí)行select * from table where name like '張%' and age=10這時(shí)候會(huì)先從name age的聯(lián)合索引中拿到name滿足張開頭的數(shù)據(jù)，然后回表，mysql支持ICP后，效果圖如下

mysql會(huì)根據(jù)聯(lián)合索引中記錄的age對(duì)數(shù)據(jù)進(jìn)行過(guò)濾，這時(shí)候age不等于10的數(shù)據(jù)將不會(huì)回表，將回表次數(shù)從4優(yōu)化到了2，這就是索引下推。

如何安排聯(lián)合索引的順序

第一原則是，如果通過(guò)調(diào)整順序，可以少維護(hù)一個(gè)索引，那么這個(gè)順序往往就是需要優(yōu)先考慮采用的，比如業(yè)務(wù)中存在兩個(gè)高頻查詢，根據(jù)name，以及根據(jù)name查詢后根據(jù)age排序，這個(gè)時(shí)候我們應(yīng)該建立name age的聯(lián)合索引，上面我們說(shuō)過(guò)name,age的所有其中name是有序的，age只在name相同的情況下才是有序的，這樣可以減少建立name的普通索引，并且優(yōu)化排序，甚至利用索引下推減少回表。如果還存在根據(jù)age進(jìn)行的查詢，那么需要單獨(dú)維護(hù)一個(gè)age的普通索引

六丶索引與排序和分組

1.索引用于排序

假設(shè)我們有一張表存儲(chǔ)id，姓名，年齡以及城市，我們?cè)诔鞘凶侄紊辖⑺饕?/p>

執(zhí)行select city,name,age from t where city='杭州' order by name limit 1000 ;city上具備索引，那么可以通過(guò)city字段拿到符合要求的數(shù)據(jù)

拿到城市和主鍵的信息之后，還需要回表，來(lái)到主鍵索引上查詢到需要的列，接下來(lái)需要排序

如果sort_buffer(MySQL 會(huì)給每個(gè)線程分配一塊內(nèi)存用于排序，稱為 sort_buffer)可以容納下目標(biāo)記錄，那么mysql會(huì)使用sort_buffer進(jìn)行快速排序，這個(gè)過(guò)程叫做全字段排序(全部的字段都在sort_buffer中)

如果sort_buffer無(wú)法容納下這么多記錄，將使用外部文件排序，mysql把需要排序的數(shù)據(jù)分為多個(gè)文件，分別快排然后合并

如果mysql認(rèn)為行太長(zhǎng)，那么會(huì)使用row_id排序——從city索引找到一條數(shù)據(jù)，回表拿到索引需要排序的字段以及主鍵id，在sort_buffer中只存儲(chǔ)需要排序的字段和主鍵，然后排序后，再次回表查詢?nèi)啃枰牧?，組成結(jié)構(gòu)集返回

如果直到的limit 比較小，比如limit 3，也許mysql會(huì)維護(hù)一個(gè)大小為3的堆，進(jìn)行排序獲得前3條

上面講了mysql是如何排序的，可以看到上述的排序方式，都需要利用內(nèi)存或者利用磁盤文件進(jìn)行排序，總體來(lái)說(shuō)是浪費(fèi)空間以及效率不高的，那么如何可以讓order by更快昵——創(chuàng)建一個(gè) city 和 name 的聯(lián)合索引

有了這個(gè)聯(lián)合索引，mysql可以找到城市為杭州的數(shù)據(jù)，然后回標(biāo)查詢需要的字段，然后向右取下一條，并不需要排序，因?yàn)閏ity=杭州的數(shù)據(jù)name自然是有序的。這就是索引對(duì)排序的優(yōu)化

聯(lián)合索引排序順序需要符合最左前綴原則

聯(lián)合索引排序，不能將ACS和DESC混合使用（mysql8降序索引似乎可以解決這個(gè)問題）

如果形成掃描區(qū)間的列 和排序的列不是同一個(gè)索引，可能也不能使用到索引優(yōu)化排序

select * from key1 = a oder by key2 key1，key2不是聯(lián)合索引，各自包含一個(gè)索引，那么mysql選擇key1索引數(shù)據(jù)。

排序列如何使用了函數(shù)，那么不能排序，函數(shù)也許會(huì)改變索引的單調(diào)性

2.索引用于分組

select key1,key2,key3 ,count(*) from table group by key1,key2,key3 如果key1，key2，key3沒有建立聯(lián)合索引，那么需要建立用于統(tǒng)計(jì)的臨時(shí)表，將掃描的數(shù)據(jù)加入到臨時(shí)表進(jìn)行統(tǒng)計(jì)，但是如果我們按照 key1,key2,key3的順序建立了聯(lián)合索引，那么索引中的主鍵自然就是分好組的。索引用于分組的注意事項(xiàng)基本上和排序相同，這里不做過(guò)多贅述

七丶多范圍讀取MRR

上面我們說(shuō)到回表，是每從二級(jí)索引中獲取一條符合的數(shù)據(jù)都會(huì)到聚簇索引根據(jù)主鍵進(jìn)行回表，但是二級(jí)索引中的主鍵是無(wú)序的，這導(dǎo)致每次執(zhí)行回表操作都是隨機(jī)IO，導(dǎo)致性能開銷巨大，mysql為了優(yōu)化這種隨機(jī)IO，使用了MRR多范圍讀取，即先讀取一部分二級(jí)索引，然后將主鍵值排序后再統(tǒng)一執(zhí)行回標(biāo)，將隨機(jī)IO優(yōu)化為順序IO。

八丶索引合并

通常情況下，mysql只會(huì)為單個(gè)索引生成掃描區(qū)間，但是存在特殊情況，mysql可以為多個(gè)索引生成掃描區(qū)間，這種多個(gè)索引生成掃描區(qū)間來(lái)完成依次查詢的方法稱為索引合并

1.交集索引合并

select xxx ,xxx from table where key1=1 and key2=2 （key1和key2都是二級(jí)索引）mysql可以選擇使用key1，也可以使用key2索引，獲取符合的主鍵然后回表并過(guò)濾不符合的記錄。也可以分別從key1索引中獲取滿足key1=1，從key2索引中獲取 key2=2的主鍵值，再獲取二者交集最后進(jìn)行回表，這樣可與減少不必要的回表操作。

使用交集索引合并的話，要求通過(guò)二級(jí)索引查到的主鍵本身就是有序的，這樣獲取交集效率更高，并且減少了隨機(jī)IO。

如果具有a,b兩個(gè)普通索引,執(zhí)行查詢select * from table where a>1 and b=2 那么是無(wú)法進(jìn)行交集索引合并的,因?yàn)?code>a>1得到的主鍵并不是有序的， 同樣聯(lián)合索引q,w,e 普通索引r 執(zhí)行select * from table where q=1 and w=2 and r=3也不可以使用交集索引合并，因?yàn)槁?lián)合索引是依次根據(jù)q,w,e排序的，滿足q=1 and w=2的數(shù)據(jù)主鍵并不是有序的。 普通索引a，主鍵為id，select * from a=1 and id>100這樣的查詢理論上是主鍵有序可與使用的，但是mysql會(huì)找到滿足a=1且id>100的第一條記錄，然后向右直到不符合條件的數(shù)據(jù)出現(xiàn)，這種情況也不需要使用交集索引合并

2.并集索引合并

select * from table where a>1 or b>2 a,b均為普通索引，無(wú)法只使用a或者b索引進(jìn)行查詢，但是可分別從a和b中獲取滿足條件的主鍵，然后取二者并集后回表即可。這稱之為并集索引合并，同樣也是要求從單個(gè)索引獲取到的主鍵值是有序的，

3.排序并集索引合并

并集索引合并要求根據(jù)單個(gè)索引獲取到的主鍵是有序的，然后取并集回表，條件比較苛刻。mysql支持分別從各個(gè)索引中掃描得到記錄的主鍵讓排序，再取并集進(jìn)行回標(biāo)查詢，這種稱為排序并集索引合并

九丶索引建立和使用原則

1.為搜索，排序，分組的列建立索引

一般只為出現(xiàn)在where后面的列，連接子句中的列，出現(xiàn)在order by,或者group by的列進(jìn)創(chuàng)建索引。不要無(wú)腦建立索引，索引是需要存儲(chǔ)在磁盤上的，占用空間，并且在新增，刪除，修改的時(shí)候還需要維護(hù)索引，是需要時(shí)間的。

比如select xxx from table where name= 'a' order by user_no,這條查詢語(yǔ)句可以選擇在name上建立索引，也可以選擇在user_no 上建立索引，后者可以優(yōu)化排序。

2.考慮列中不重復(fù)的個(gè)數(shù)建立索引

select xxxx from table where sex=1 這里不要為sex性別建立索引，性別通常只有男和女，為其建立索引，b+樹只有兩個(gè)節(jié)點(diǎn)，查找之后還要對(duì)一半的進(jìn)行回表，不如直接走全表掃描

3.索引列盡可能小

mysql基本數(shù)據(jù)類型十分豐富，整數(shù)類型有tinyint,mediumint,int，bigint，我們應(yīng)該盡量使用占用字節(jié)數(shù)小的數(shù)據(jù)類型，這樣可以讓每次讀取磁盤獲取一頁(yè)的數(shù)據(jù)，可以獲得更多的范圍信息

4.為列前綴進(jìn)行索引

比如說(shuō)有英文名可能很長(zhǎng)，每次都是根據(jù)FirstName 進(jìn)行l(wèi)ike查找，這時(shí)候可以選擇為列的前10個(gè)字符建立索引(alter table user add index idx_name(name(10)))。但是十個(gè)字符之后將無(wú)法使用索引。且前綴索引會(huì)無(wú)法使用到覆蓋索引減少回表的功能，比如select name id,where name=abc123，加入為name前三個(gè)字符建立了索引，會(huì)在前綴索引中找到符合的數(shù)據(jù)比如abc111,abc121等等這個(gè)時(shí)候name的前綴索引還是需要獲取主鍵回表然后判斷name是否符合要求。

5.合理的建立覆蓋索引

在聯(lián)合索引小節(jié)中，我們總結(jié)了聯(lián)合索引的好處，減少回表，優(yōu)化排序和分組，索引下推。

6.不要在uuid上建立索引

首先uuid占用字節(jié)大，導(dǎo)致每一頁(yè)范圍信息少，并且uuid無(wú)序，這會(huì)導(dǎo)致插入數(shù)據(jù)的時(shí)候節(jié)點(diǎn)的分裂。這里也說(shuō)明了自增主鍵優(yōu)秀的點(diǎn)，不會(huì)頻繁的節(jié)點(diǎn)分裂，并且不要修改主鍵，避免不必要的節(jié)點(diǎn)分裂。相比于uuid作為主鍵，不如使用分布式自增主鍵生成的方案

7.存在聯(lián)合索引的情況下，不要重復(fù)建立索引

存在name,age的聯(lián)合索引，那么不需要再為name單獨(dú)建立索引了，但是可以為age建立索引，原理在聯(lián)合索引中進(jìn)行了講解。

8.盡量使用自增主鍵

自增主鍵能減少聚簇索引的頁(yè)分裂，如果插入的主鍵一會(huì)兒天一會(huì)兒底，會(huì)造成頁(yè)面的分裂，同樣更新主鍵也會(huì)導(dǎo)致移動(dòng)復(fù)制

9.普通索引和唯一索引如何做出抉擇

如果業(yè)務(wù)邏輯可以保證索引列的唯一，不需要依賴唯一索引保證唯一性的話，盡量使用普通索引。

9.1普通索引唯一索引等值查詢的性能差異微乎其微，唯一索引略勝一籌

對(duì)于普通索引來(lái)說(shuō)，查找到滿足條件的第一個(gè)記錄 (5,500) 后，需要查找下一個(gè)記錄，直到碰到第一個(gè)不滿足 k=5 條件的記錄。對(duì)于唯一索引來(lái)說(shuō)，由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止繼續(xù)檢索。InnoDB 的數(shù)據(jù)是按數(shù)據(jù)頁(yè)為單位來(lái)讀寫的。也就是說(shuō)，當(dāng)需要讀一條記錄的時(shí)候，并不是將這個(gè)記錄本身從磁盤讀出來(lái)，而是以頁(yè)為單位，將其整體讀入內(nèi)存。在InnoDB 中，每個(gè)數(shù)據(jù)頁(yè)的大小默認(rèn)是 16KB，也就是說(shuō)只有唯一索引滿足等值條件的數(shù)據(jù)跨頁(yè)的時(shí)候，才需要再一次io，這個(gè)概率是比較小的

9.2插入和更新的效率，普通索引優(yōu)于唯一索引

對(duì)于唯一索引來(lái)說(shuō)，需要將數(shù)據(jù)頁(yè)讀入內(nèi)存，判斷到?jīng)]有沖突，插入這個(gè)值，語(yǔ)句執(zhí)行束；對(duì)于普通索引來(lái)說(shuō)，則是將更新記錄在 change buffer，語(yǔ)句執(zhí)行就結(jié)束了

當(dāng)需要更新一個(gè)數(shù)據(jù)頁(yè)時(shí)，如果數(shù)據(jù)頁(yè)在內(nèi)存中就直接更新，而如果這個(gè)數(shù)據(jù)頁(yè)還沒有在內(nèi)
存中的話，在不影響數(shù)據(jù)一致性的前提下，InooDB 會(huì)將這些更新操作緩存在 change
buffer 中，這樣就不需要從磁盤中讀入這個(gè)數(shù)據(jù)頁(yè)了。在下次查詢需要訪問這個(gè)數(shù)據(jù)頁(yè)的
時(shí)候，將數(shù)據(jù)頁(yè)讀入內(nèi)存，然后執(zhí)行 change buffer 中與這個(gè)頁(yè)有關(guān)的操作。通過(guò)這種方
式就能保證這個(gè)數(shù)據(jù)邏輯的正確性.
change buffer 在內(nèi)存中有拷貝，也會(huì)被寫入到磁盤上。
將 change buffer 中的操作應(yīng)用到原數(shù)據(jù)頁(yè)，得到最新結(jié)果的過(guò)程稱為 merge。除了訪問
這個(gè)數(shù)據(jù)頁(yè)會(huì)觸發(fā) merge 外，系統(tǒng)有后臺(tái)線程會(huì)定期 merge。在數(shù)據(jù)庫(kù)正常關(guān)（shutdown）的過(guò)程中，也會(huì)執(zhí)行 merge 操作

十丶索引失效

上圖是mysql的基本架構(gòu)，其中存在優(yōu)化器，其作用是不改變sql執(zhí)行j結(jié)果的情況下，讓sql更加簡(jiǎn)單，并且根據(jù)成本分析，制定執(zhí)行計(jì)劃。是否走索引，走什么索引也是優(yōu)化器來(lái)決定的（sql中可以提示使用什么索引，強(qiáng)制使用某一個(gè)索引）。

常見索引失效的原因有

1.不滿足最左前綴原則

如果存在a,b,c的聯(lián)合索引，select * from table where b=2 and a=1這種時(shí)候還是可能走聯(lián)合索引的，mysql會(huì)優(yōu)化語(yǔ)句,但是select * from table where b=1 and c=2是無(wú)法走聯(lián)合索引的，因?yàn)閎，c在b+樹中整體無(wú)序

2.使用了select*

使用select*需要回表，也許mysql優(yōu)化器評(píng)估后覺得走非聚集索引，不如直接全表掃描

3.like查詢左邊有%

以xxx開頭是可以走索引的，因?yàn)槭怯行虻?，但是以xxx結(jié)尾和包含xxx是無(wú)法走索引的。因?yàn)樽址谋容^是從最左的字符開始比較的

4.order by 使用了聯(lián)合索引中不存在的列，或者順序不符合最左前綴匹配

5.group by 使用了聯(lián)合索引中不存在的列，或者順序不符合最左前綴匹配

6.不要在條件字段函數(shù)操作，注意隱式類型轉(zhuǎn)換，小心隱式字符編碼轉(zhuǎn)換

例如在A表的key1上建立索引，key1是int類型

6.1條件字段函數(shù)操作

select xxx from A where key1+1<10 理論上說(shuō)mysql可以進(jìn)行優(yōu)化，但是最好不要這么做，更不要select xxx from key1 where abs(key1)<10，mysql任何索引列上進(jìn)行這些操作是會(huì)影響單調(diào)性的，直接無(wú)腦不走索引，分組排序也一樣 ，select * from B left join A on B.key2 = A.key1+1 這個(gè)語(yǔ)句也一樣（連表查詢的原理見Mysql單表訪問方法，索引合并，多表連接原理，基于規(guī)則的優(yōu)化，子查詢優(yōu)化）

6.2 注意隱式類型轉(zhuǎn)換

select * from A where key1>'10' 這個(gè)語(yǔ)句中key1是int類型，通樣無(wú)法走索引，因?yàn)槭菍ey1轉(zhuǎn)化為字符串比較，還是將'10' 轉(zhuǎn)化為數(shù)字比較昵，如果是前者那么key=9符合要求，如果是后者key1=9不符合要求。

6.3 小心隱式字符編碼轉(zhuǎn)換

如果兩個(gè)表的字符集不同，那么做表連接查詢的時(shí)候用不上關(guān)聯(lián)字段的索引，比如字符集 utf8mb4 是 utf8 的超集，所以當(dāng)這兩個(gè)類型的字符串在做比較的時(shí)候，MySQL 內(nèi)部的操作是，先把 utf8 字符串轉(zhuǎn)成 utf8mb4 字符集，再做比較，相當(dāng)于其中一列需要使用convert函數(shù)導(dǎo)致索引失效

到此這篇關(guān)于一篇文章講解清楚MySQL索引的文章就介紹到這了,更多相關(guān)MySQL索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論