MySQL技術内幕 InnoDB存儲引擎 B+樹索引的使用 筆記

什麼時候添加B+樹索引呢？

在通路表中很少一部分時使用B+樹索引才有意義。

Q: 怎麼檢視索引是否是高選擇性的呢？

A：可以通過SHOW INDEX 結果中的列 Cardinality 來觀察。Cardinality 表示索引中不重複記錄數量的預估值。

Q: 為什麼Cardinality 是預估值呢？

A : 因為Cardinality 是通過采樣(Sample)的方法來完成的。預設InnoDB存儲引擎會對8個葉子節點（Leaf Page)進行采用。

取得B+樹索引中葉子節點的數量，記為A。随機取得B+樹索引中的8個葉子節點。統計每個頁不同記錄的個數，即為P1,P2,...,P8.

根據采樣資訊給出Cardinality 的預估值：Cardinality = (P1+P2+....+P8) * A/8

Q: 更新Cardinality 的政策是什麼呢？

A: 表中 1/16的資料已發生過變化。

stat_modified_counter>2 000 000 000. 變化次數

不同應用中B+樹索引的使用

1. 在具體的生産環境中使用索引，并觀察索引使用的情況
2. 判斷是否真的需要使用索引，不要盲從任何人給你的經驗意見，Think Different. => 獨立思考，養成獨立思考的習慣，關注實際問題。

聯合索引

可以使用聯合索引在一些 order by 的場景下去優化我們的Sql,減少查詢的時間

覆寫索引

從輔助索引中就可以得到查詢記錄，而不需要查詢聚集索引中的記錄。使用覆寫索引的好處是輔助索引不包含整行記錄的所有資訊，故其大小要遠小于聚集索引，是以可以減少大量的IO操作。也就是不回表。

某些統計問題可以走輔助索引，輔助索引小于聚集索引，可以減少IO操作。

優化器不走索引的情況

使用者要選取的資料是整行資料資訊，而輔助索引不能覆寫我們要查詢的資訊。因為走了輔助索引以後，我們還要進行一次書簽通路來查找整行的資料細膩下。雖然輔助索引是順序存放的，但是再一次進行書簽查找的資料則是無序的，因為變為了磁盤上的離散讀操作。 順序讀 > 離散讀。

Multi-Range Read 優化

Multi-Range Read 優化的目的就是為了減少磁盤的随機通路，并且将随機通路轉化為較為順序的資料通路。

1. MRR 使資料通路變得較為順序。在查詢輔助索引時，首先根據得到的查詢結果，按照主鍵進行排序，并按照主鍵排序的順序進行書簽查找。
2. 減少緩沖池中頁被替換的次數
3. 批量處理對鍵值的查詢操作

Index Condition Pushdown(ICP) 優化