Mysql索引(究極無敵細節版)

參考了：
https://www.jianshu.com/p/ace3cd6526c4
推薦up主https://space.bilibili.com/377905911
推薦書籍《mysql是怎樣執行的》
推薦極客時間《MySQL實戰45講》——林曉斌

系列文章目錄和關於我

一丶什麼是索引

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

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

B+樹索引
全文索引
雜湊索引

本文主要討論B+樹索引

二丶索引的資料結構

可以加快查詢速度的資料結構很多，為什麼mysql使用B+樹來實現暱，換句話說雜湊表，有序陣列，跳錶，平衡二元搜尋樹，B-樹等都可以優化搜尋效率，為什麼偏偏使用B+樹

1.雜湊表

雜湊表，可以聯想Java中的HashMap，在HashMap原始碼學習中，我們瞭解到Hash表的資料結構。如下圖

雜湊表通過hash演演算法將key對映到陣列對應的下標進行儲存，不可避免的會產生hash衝突（多個不同的key雜湊到相同的陣列下標中），解決hash衝突常用拉鍊法，顧名思義，就是把相同hash值的節點組成連結串列串起來。在根據key查詢value的過程中，只需要再次使用相同的hash演演算法那麼就能拿到對應的陣列下表，然後遍歷連結串列找到目標值即可,查詢的效率是o(1)。

明明存在連結串列需要遍歷為什麼說時間複雜度o(1)
首先hash演演算法計算是常數時間，
hash表會在需要的時候進行擴容，
維持連結串列長度儘量在一個常數範圍，從而保證遍歷常數個連結串列節點

mysql中存在自適應雜湊索引，由innodb儲存引擎自己控制，利用查詢O(1)的性質優化等值查詢。我們可以看出，hash表並不適合範圍查詢，對於Id<10這種範圍查詢只能遍歷hash表中每一個資料，相當於要進行一次全表掃描。我還有一個想法：從擴容的角度看，每次擴大陣列大小後都需要移動元素到新的陣列空間中，這部分的複製移動的開銷也許也是hash表不合適的原因（redis為了解決這個問題，使用漸進式hash的方式，在擴容的時候生成更大的陣列，但不是一次移動所以資料，而是插入的新元素都放到新陣列，老陣列使用到的資料才會慢慢移動到新陣列）redis基於記憶體的資料庫都需要通過漸進式hash優化擴容操作，基於磁碟的mysql若使用hash將慘不忍睹

2.有序陣列

有序陣列就是資料中元素有序，正因為有序，所以其在範圍查詢上非常優秀，正因為要維持有序，在更改資料的時候，也許需要移動大量陣列元素（比如插入一個較小的值，大於此值的資料都需要後移動），所以有序資料只適用於靜態資料（比如2020年人口資訊，這種不會改變的資料）

3.跳錶

為了解決有序陣列需要移動元素的問題，我們可以使用連結串列來維護元素，從而使更改元素效率為o(1)，但是連結串列的查詢非常慢。由於連結串列整體是有序的，那麼我們可以使用二分查詢優化查詢效率，如上我們建立多級的節點，在查詢的時候我們首先通過多級的索引依次找到最下層。對於範圍查詢，由於底層資料是有序的，查詢id<7的陣列，首先我們找到id=7然後向左遍歷集合（可以把跳錶最下面一層優化為雙向連結串列，從而讓範圍查詢速度也很快）

哪為什麼不使用跳錶來做索引暱？

跳錶是連結串列結構，一條資料一個結點，如果最底層要存放2kw資料，且每次查詢都要能達到二分查詢的效果，2kw大概在2的24次方左右，所以，跳錶大概高度在24層左右。最壞情況下，這24層資料會分散在不同的資料頁裡，也即是查一次資料會經歷24次磁碟IO。

4.平衡二元搜尋樹

二元搜尋樹，即左子樹小於根，根小於右子樹，這種結構在查詢的時候可以進行二分，根據根節點的值就可以確定期望的資料在左樹還是右樹。

但是二元搜尋樹在插入，刪除節點的時候可能出現樹極度不平衡的情況，出現樹退化成連結串列。

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

為什麼不使用AVL做索引：節點儲存的資料內容太少。因為作業系統和磁碟之間一次資料交換是以頁為單位的，一頁 = 4K，即每次IO作業系統會將4K資料載入進記憶體。但是，在二元樹每個節點的結構只儲存一個關鍵字，一個資料區，兩個子節點的參照，並不能夠填滿4K的內容。倖幸苦苦做了一次的IO操作，卻只載入了一個關鍵字，在樹的高度很高，恰好又搜尋的關鍵字位於葉子節點或者支節點的時候，取一個關鍵字要做很多次的IO。

5.B-樹，B+樹

B-樹就是B樹，英文是B-Tree，所以國內有許多人稱之為B-樹。B樹和B+樹是多路平衡查詢樹，之所以多路，是為了契合磁碟的io操作——作業系統和磁碟之間一次資料交換是以頁為單位的，多路能讓讀取一頁能獲取更多的資料，讓樹的高度更低。

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

B+樹葉子節點使用雙向指標串聯起來，這讓B+樹相比於B樹更加適合範圍查詢
B+樹非葉子節點並不存資料，所以每次查詢資料都必須遍歷到葉子節點，時間複雜度穩定為O(logN)，B-樹在運氣好的時候可以在根節點直接拿到資料。但是正是因為非葉子節點不儲存資料，可以讓一次磁碟讀取一頁中包含的索引資料更多，每個節點能索引的範圍更大更精確，讓我們可以更改定位到期望的資料。由於B+樹的葉子節點的資料都是使用連結串列連線起來的，而且他們在磁碟裡是順序儲存的，所以當讀到某個值的時候，磁碟預讀原理就會提前把這些資料都讀進記憶體，使得範圍查詢和排序都很快

B+樹在更改資料的時候，為了保證樹的平衡可能存在節點的分裂和合並，所以我們一般建議使用自增主鍵，在插入的時候，不會頻繁的發生節點的分裂。

三丶InnoDB索引方案

1.InnoDB行結構

InnoDB儲存引擎儲存一行資料使用的資料結構稱為行結構。

COMPACT
- 變長欄位列表：如varchar(m),Text，Blob型別的列，稱為變長欄位，由於其位元組數量不固定，需要在變成欄位列表中儲存這些欄位的長度，在記錄的真實資料中儲存內容
- null值列表：如果表中沒用允許為null的列，那麼null值列表就不存在，否則把每一個允許為null的列使用一個二進位制位來表示，二進位制為1的時候表示值為null
- 記錄頭資訊：佔用五個位元組，其中包含delete_flag(標記記錄是否被刪除)，next_record(下一條記錄的相對位置)等資訊
- 記錄的真實資訊：如果表沒用定義主鍵，也沒有唯一不可重複不可為null的列，那麼innodb為我們生成一個隱藏列row_id,如果定義了主鍵那麼此列不存在，並且還有trx_id,roll_pointer兩個隱藏列，後續便是每一個列的真實資料。（char(M)型別的列，如果使用定長字元編碼，那麼位元組數不會加到變長欄位列表中，如果使用變長編碼，佔用長度會加入到變成欄位列表中（變長編碼那麼必須佔用M個位元組，varchar(M）則沒用這個要求）
REDUNDANT
- 欄位長度偏移列表：此種行格式會把記錄所有列長度的偏移資訊儲存
- null值的處理方式：先看偏移量的null位元位是否為1，如果為了那麼表示為null
溢位列

如果一個列太長，並不會傻乎乎的儲存所有資料在行記錄中，而是使用溢位列，COMPACT和REDUNDANT只會儲存該列的前768位元組然後儲存指向其他頁的地址，剩下的資料存在其他頁中。
DYNAMIC和COMPRESSED

和COMPACT類似，但是二者不會儲存過長列的前768位元組，而是把真實資料都儲存到溢位中，記錄只儲存溢位頁的地址。COMPRESSED 還會使用壓縮演演算法對頁面進行壓縮

2.InnoDB頁結構

頁是InnoDB管理儲存空間的基本單位，其預設大小為16k,InnoDB設計了很多不同的頁結構：存放Change Buffer的頁，儲存undo log 紀錄檔的頁等等。對於表中資料，也存在在頁中

最開始的時候UserRecords並不存在，隨著資料的插入，會從FreeSpace中申請一個記錄大小的空間，將其劃分到UserRecords部分，當FreeSpace用完只會繼續插入就需要申請新的頁。

2.1行結構中記錄頭資訊的作用

deleted_flag:標記是否被刪除，1表示被刪除，被刪除的列表通過next_record串聯起來，並且會記錄被刪除記錄的空火箭，這部分空間可以重複使用
min_rec_flag: B+樹每層非葉子節點，最小的目錄項記錄會被新增此標記
n_owned:
heap_no:UserRecords中儲存的使用者記錄是緊湊如同堆一樣排布的,heap_no是堆中記錄的編號，從2開始（0和1 被infimum+supremum佔用，infimum虛擬的最小記錄，supremum虛擬最大記錄）
next_record:表示當前記錄的真實資料，到下一條記錄的距離

next_record左邊是變長欄位列表和null值列表（二者都是逆序存放資訊，也就是說距離next_record最近的是第一個欄位是否為null，第一個變長欄位的長度）右邊是記錄的真實資料（順序存放），且可以使記錄中靠前欄位和對應的欄位資訊在記憶體中更近，提高快取記憶體的命中率。這裡我們可以看到被刪除的記錄沒用立即被清除，只是不會被next_record串聯起來

記錄按照主鍵從小到大形成單向連結串列

2.2頁目錄

上面我們知道了記錄在頁中按照主鍵從小到大的順序串聯成單向連結串列，那麼怎麼在一個頁中根據主鍵找到目標記錄暱——通過頁目錄進行二分查詢。

頁目錄生成過程
1. 將infimum，supermum以及所有未被刪除的記錄，分成多個組
2. 每一個組中最大的記錄的n_owned儲存組中記錄條數
3. 將每一個組中最後的記錄在頁面中的地址偏移量，儲存到頁面尾部——Page Directory中，這些地址偏移量稱為槽
根據查詢頁面記錄的過程

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

3.InnoDB索引方案

3.1為頁建立目錄項

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

目錄項記錄的只是主鍵值和頁號兩個列，最下方是我們剛剛講到的innoDB儲存使用者記錄的頁。如果頁面資料量很大，可以繼續為目錄項建立目錄項

3.2 根據目錄項定位資料行的過程

例如查詢主鍵為10記錄

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

如上圖頁33 存在記錄(1,30)，(320 32)，可以判斷主鍵位於1~320範圍的記錄在頁30，大於320的記錄在頁32
找到頁30後還要繼續在頁30中，通過目錄項記錄的頁確定目標記錄真正所在的頁
在真正儲存使用者記錄的頁（頁28）中通過槽定位到組，然後遍歷槽所在組的所有記錄

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

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

如果資料表定義了主鍵，那麼這個主鍵索引就是聚集索引，如果沒有定義主鍵，mysql會選擇該表的第一個非空唯一的索引構建聚集索引，如果都沒有那麼mysql會生成一個隱藏的列（6位元組的列，並且插入自增）

自增主鍵會把資料自動向後插入，避免了插入過程中聚集索引節點分裂的問題。節點分裂會帶來大範圍的資料物理移動，帶來磁碟IO的效能損耗，並且我們一般建議儘量不要改動主鍵，主鍵的更改也會帶來page分裂，產生碎片。

四丶回表查詢

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

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

這裡我們也可以看到為什麼建議使用select * ,這意味著查詢所有列，如果配合上普通索引，那麼大概率這個普通索引不會覆蓋到索引列，導致需要回表查詢。並且select*這種"我全都要"大概率會查詢到我們不需要的列，造成不必要的網路資源消耗，增加不必要的io，增加不必要的記憶體消耗。

五丶聯合索引

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

聯合索引可以減少回表

如果我們執行select age,id from table where name='a' and age=10,這個時候由於我們定義的聚集索引一級包含了需要的資料就不需要進行回表操作了（這其實也被稱為覆蓋索引，即非聚集索引中可以查詢到全部需要的列，那麼就不需要走聚集索引回表查詢資料）
聯合索引可以優化排序

上圖中的聯合索引，我們可以看到，名稱相同的節點，其年齡是有序的

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

全稱Index Condition PushDown,mysql 5.6後支援的一種根據索引進行查詢優化的操作。mysql資料庫會在取出所有資料的同時判斷是否進行where條件的過濾，將where的部分過濾放在儲存引擎層。

mysql5.6之前如果執行select * from table where name like '張%' and age=10這時候會先從name age的聯合索引中拿到name滿足張開頭的資料，然後回表，mysql支援ICP後，效果圖如下

mysql會根據聯合索引中記錄的age對資料進行過濾，這時候age不等於10的資料將不會回表，將回表次數從4優化到了2，這就是索引下推。
如何安排聯合索引的順序

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

六丶索引與排序和分組

1.索參照於排序

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

執行select city,name,age from t where city='杭州' order by name limit 1000 ;city上具備索引，那麼可以通過city欄位拿到符合要求的資料

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

如果sort_buffer(MySQL 會給每個執行緒分配一塊記憶體用於排序，稱為 sort_buffer)可以容納下目標記錄，那麼mysql會使用sort_buffer進行快速排序，這個過程叫做全欄位排序(全部的欄位都在sort_buffer中)
如果sort_buffer無法容納下這麼多記錄，將使用外部檔案排序，mysql把需要排序的資料分為多個檔案，分別快排然後合併
如果mysql認為行太長，那麼會使用row_id排序——從city索引找到一條資料，回表拿到索引需要排序的欄位以及主鍵id，在sort_buffer中只儲存需要排序的欄位和主鍵，然後排序後，再次回表查詢全部需要的列，組成結構集返回
如果直到的limit 比較小，比如limit 3，也許mysql會維護一個大小為3的堆，進行排序獲得前3條

上面講了mysql是如何排序的，可以看到上述的排序方式，都需要利用記憶體或者利用磁碟檔案進行排序，總體來說是浪費空間以及效率不高的，那麼如何可以讓order by更快暱——建立一個 city 和 name 的聯合索引

有了這個聯合索引，mysql可以找到城市為杭州的資料，然後回標查詢需要的欄位，然後向右取下一條，並不需要排序，因為city=杭州的資料name自然是有序的。這就是索引對排序的優化

聯合索引排序順序需要符合最左字首原則
聯合索引排序，不能將ACS和DESC混合使用（mysql8降序索引似乎可以解決這個問題）
如果形成掃描區間的列和排序的列不是同一個索引，可能也不能使用到索引優化排序

select * from key1 = a oder by key2 key1，key2不是聯合索引，各自包含一個索引，那麼mysql選擇key1索引資料。
排序列如何使用了函數，那麼不能排序，函數也許會改變索引的單調性

2.索參照於分組

select key1,key2,key3 ,count(*) from table group by key1,key2,key3 如果key1，key2，key3沒有建立聯合索引，那麼需要建立用於統計的臨時表，將掃描的資料加入到臨時表進行統計，但是如果我們按照 key1,key2,key3的順序建立了聯合索引，那麼索引中的主鍵自然就是分好組的。索參照於分組的注意事項基本上和排序相同，這裡不做過多贅述

七丶多範圍讀取MRR

上面我們說到回表，是每從二級索引中獲取一條符合的資料都會到聚簇索引根據主鍵進行回表，但是二級索引中的主鍵是無序的，這導致每次執行回表操作都是隨機IO，導致效能開銷巨大，mysql為了優化這種隨機IO，使用了MRR多範圍讀取，即先讀取一部分二級索引，然後將主鍵值排序後再統一執行回標，將隨機IO優化為順序IO。

八丶索引合併

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

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兩個普通索引,執行查詢select * from table where a>1 and b=2 那麼是無法進行交集索引合併的,因為a>1得到的主鍵並不是有序的，
同樣聯合索引q,w,e 普通索引r 執行select * from table where q=1 and w=2 and r=3也不可以使用交集索引合併，因為聯合索引是依次根據q,w,e排序的，滿足q=1 and w=2的資料主鍵並不是有序的。
普通索引a，主鍵為id，select * from a=1 and id>100這樣的查詢理論上是主鍵有序可與使用的，但是mysql會找到滿足a=1且id>100的第一條記錄，然後向右直到不符合條件的資料出現，這種情況也不需要使用交集索引合併

2.並集索引合併

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

3.排序並集索引合併

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

九丶索引建立和使用原則

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

一般只為出現在where後面的列，連線子句中的列，出現在order by,或者group by的列進建立索引。不要無腦建立索引，索引是需要儲存在磁碟上的，佔用空間，並且在新增，刪除，修改的時候還需要維護索引，是需要時間的。

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

2.考慮列中不重複的個數建立索引

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

3.索引列儘可能小

mysql基本資料型別十分豐富，整數型別有tinyint,mediumint,int，bigint，我們應該儘量使用佔用位元組數小的資料型別，這樣可以讓每次讀取磁碟獲取一頁的資料，可以獲得更多的範圍資訊

4.為列字首進行索引

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

5.合理的建立覆蓋索引

在聯合索引小節中，我們總結了聯合索引的好處，減少回表，優化排序和分組，索引下推。

6.不要在uuid上建立索引

首先uuid佔用位元組大，導致每一頁範圍資訊少，並且uuid無序，這會導致插入資料的時候節點的分裂。這裡也說明了自增主鍵優秀的點，不會頻繁的節點分裂，並且不要修改主鍵，避免不必要的節點分裂。相比於uuid作為主鍵，不如使用分散式自增主鍵生成的方案

7.存在聯合索引的情況下，不要重複建立索引

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

8.儘量使用自增主鍵

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

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

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

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

對於普通索引來說，查詢到滿足條件的第一個記錄 (5,500) 後，需要查詢下一個記錄，直到碰到第一個不滿足 k=5 條件的記錄。對於唯一索引來說，由於索引定義了唯一性，查詢到第一個滿足條件的記錄後，就會停止繼續檢索。InnoDB 的資料是按資料頁為單位來讀寫的。也就是說，當需要讀一條記錄的時候，並不是將這個記錄本身從磁碟讀出來，而是以頁為單位，將其整體讀入記憶體。在InnoDB 中，每個資料頁的大小預設是 16KB，也就是說只有唯一索引滿足等值條件的資料跨頁的時候，才需要再一次io，這個概率是比較小的

9.2插入和更新的效率，普通索引優於唯一索引

對於唯一索引來說，需要將資料頁讀入記憶體，判斷到沒有衝突，插入這個值，語句執行束；對於普通索引來說，則是將更新記錄在 change buffer，語句執行就結束了

當需要更新一個資料頁時，如果資料頁在記憶體中就直接更新，而如果這個資料頁還沒有在內
存中的話，在不影響資料一致性的前提下，InooDB 會將這些更新操作快取在 change
buffer 中，這樣就不需要從磁碟中讀入這個資料頁了。在下次查詢需要存取這個資料頁的
時候，將資料頁讀入記憶體，然後執行 change buffer 中與這個頁有關的操作。通過這種方
式就能保證這個資料邏輯的正確性.
change buffer 在記憶體中有拷貝，也會被寫入到磁碟上。
將 change buffer 中的操作應用到原資料頁，得到最新結果的過程稱為 merge。除了存取
這個資料頁會觸發 merge 外，系統有後臺執行緒會定期 merge。在資料庫正常關（shutdown）的過程中，也會執行 merge 操作

十丶索引失效

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

常見索引失效的原因有

1.不滿足最左字首原則

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

2.使用了select*

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

3.like查詢左邊有%

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

4.order by 使用了聯合索引中不存在的列，或者順序不符合最左字首匹配

5.group by 使用了聯合索引中不存在的列，或者順序不符合最左字首匹配

6.不要在條件欄位函數操作，注意隱式型別轉換，小心隱式字元編碼轉換

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

6.1條件欄位函數操作

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

6.2 注意隱式型別轉換

select * from A where key1>'10' 這個語句中key1是int型別，通樣無法走索引，因為是將key1轉化為字串比較，還是將'10' 轉化為數位比較暱，如果是前者那麼key=9符合要求，如果是後者key1=9不符合要求。

6.3 小心隱式字元編碼轉換

如果兩個表的字元集不同，那麼做表連線查詢的時候用不上關聯欄位的索引，比如字元集 utf8mb4 是 utf8 的超集，所以當這兩個型別的字串在做比較的時候，MySQL 內部的操作是，先把 utf8 字串轉成 utf8mb4 字元集，再做比較，相當於其中一列需要使用convert函數導致索引失效