一丶什么是索引

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

InnoDB存儲引擎存在幾種常見的索引：

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

本文主要討論B+樹索引

二丶索引的數(shù)據(jù)結構

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

1.哈希表

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

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

明明存在鏈表需要遍歷為什么說時間復雜度o(1)
首先hash算法計算是常數(shù)時間，
hash表會在需要的時候進行擴容，
維持鏈表長度盡量在一個常數(shù)范圍，從而保證遍歷常數(shù)個鏈表節(jié)點

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

2.有序數(shù)組

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

3.跳表

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

哪為什么不使用跳表來做索引昵？

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

4.平衡二叉搜索樹

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

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

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

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

5.B-樹，B+樹

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

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

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

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

三丶InnoDB索引方案

1.InnoDB行結構

InnoDB存儲引擎存儲一行數(shù)據(jù)使用的數(shù)據(jù)結構稱為行結構。

COMPACT

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

REDUNDANT

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

溢出列

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

DYNAMIC和COMPRESSED

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

2.InnoDB頁結構

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

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

2.1行結構中記錄頭信息的作用

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

min_rec_flag: B+樹每層非葉子節(jié)點，最小的目錄項記錄會被添加此標記

n_owned:

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

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

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

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

2.2頁目錄

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

頁目錄生成過程

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

根據(jù)查詢頁面記錄的過程

通過二分法找到目標記錄中的槽，然后遍歷槽所在組的所有記錄

3.InnoDB索引方案

3.1為頁建立目錄項

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

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

3.2 根據(jù)目錄項定位數(shù)據(jù)行的過程

例如查找主鍵為10記錄

根據(jù)目錄項中的內容，確定目標記錄所在的頁

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

找到頁30后還要繼續(xù)在頁30中，通過目錄項記錄的頁確定目標記錄真正所在的頁

在真正存儲用戶記錄的頁（頁28）中通過槽定位到組，然后遍歷槽所在組的所有記錄

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

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

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

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

四丶回表查詢

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

這就是為什么根據(jù)主鍵查找快于根據(jù)其他索引列查找，因為如果其他索引列沒有包含我們select語句中需要的列（如果是select id from table where age<10,那么age索引是可以覆蓋到需要的數(shù)據(jù)的(葉子節(jié)點存儲了id)，那么也不會回表），那么會走主鍵索引拿到需要的數(shù)據(jù)，多了一步回表操作。

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

五丶聯(lián)合索引

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

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

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

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

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

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

索引下推ICP

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

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

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

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

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

六丶索引與排序和分組

1.索引用于排序

假設我們有一張表存儲id，姓名，年齡以及城市，我們在城市字段上建立索引

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

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

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

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

如果mysql認為行太長，那么會使用row_id排序——從city索引找到一條數(shù)據(jù)，回表拿到索引需要排序的字段以及主鍵id，在sort_buffer中只存儲需要排序的字段和主鍵，然后排序后，再次回表查詢全部需要的列，組成結構集返回

如果直到的limit 比較小，比如limit 3，也許mysql會維護一個大小為3的堆，進行排序獲得前3條

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

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

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

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

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

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

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

2.索引用于分組

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

七丶多范圍讀取MRR

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

八丶索引合并

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

1.交集索引合并

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

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

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

2.并集索引合并

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

3.排序并集索引合并

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

九丶索引建立和使用原則

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

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

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

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

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

3.索引列盡可能小

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

4.為列前綴進行索引

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

5.合理的建立覆蓋索引

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

6.不要在uuid上建立索引

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

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

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

8.盡量使用自增主鍵

自增主鍵能減少聚簇索引的頁分裂，如果插入的主鍵一會兒天一會兒底，會造成頁面的分裂，同樣更新主鍵也會導致移動復制

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

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

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

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

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

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

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

十丶索引失效

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

常見索引失效的原因有

1.不滿足最左前綴原則

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

2.使用了select*

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

3.like查詢左邊有%

以xxx開頭是可以走索引的，因為是有序的，但是以xxx結尾和包含xxx是無法走索引的。因為字符串的比較是從最左的字符開始比較的

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

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

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

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

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

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

6.2 注意隱式類型轉換

select * from A where key1>'10' 這個語句中key1是int類型，通樣無法走索引，因為是將key1轉化為字符串比較，還是將'10' 轉化為數(shù)字比較昵，如果是前者那么key=9符合要求，如果是后者key1=9不符合要求。

6.3 小心隱式字符編碼轉換

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

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