InnoDB索引與底層原理

pinoky發表於2024-09-14
索引

InnoDB索引與底層原理

索引介紹

索引：一顆B+樹，除了葉子節點外，其餘的節點都作為目錄項，且都是有序排列的

  • 在目錄項page裡面，一條記錄對應著下層的一個page

    一條記錄至少有兩個列：列1作為主鍵 記錄該page最小的主鍵值；列2記錄該page的頁號

  • 在葉子節點才真正地存放資料

    • 聚簇索引：以主鍵為主鍵，葉子節點儲存完整的使用者記錄

    • 二級索引：以某列為主鍵，葉子節點列1為索引列，列2為原資料表裡的主鍵；目錄項節點為索引列的值+主鍵值+頁號（保證除頁號外是唯一的）

      即二級索引的葉節點為了節省空間，不實際儲存使用者記錄，需要回表

    • 聯合索引：以多個列作為排序規則，葉子節點為所有用於排序的列+主鍵；目錄項節點為用於排序的列+頁號，必須使用到第一個列才能用到聯合索引，否則用不上

一個頁面至少需要有兩條記錄，否則會導致相同數量的記錄耗費很多的目錄層級，且建立索引是有代價的，一是儲存它的空間代價，二是資料crud時調整索引的時間代價

當寫下select * from student where uid = 3;時，如果uid有索引=》儲存引擎請求核心=》磁碟IO讀取索引檔案到記憶體上=》用索引的資料構建B樹來加速搜尋

innoDB預設使用B+樹構建索引（樹高，平均，範圍查詢）

第一個原因是：B樹每個節點既存key也存data，但由於每個節點的儲存空間有限，這種儲存方式會導致每個節點能儲存的key資料量變小，當需要儲存的資料量很大時，會導致B樹的高度變高；而B+樹的話非葉子節點只包含key值，資料都存放在葉子節點上，這樣每個磁碟塊就能容納更多的鍵值對，樹的高度就會比較小，在進行索引搜尋的時候就能減少B+樹訪問磁碟的IO次數，提高資料的讀取效率

其次是因為B+樹的所有資料都存放在葉子節點上，所以要是在B+樹上搜尋關鍵字，找到的時間是平均的，不會像B樹那樣，越靠近根節點的資料找到得越快

最後是B+樹的所有葉子節點都被連線成了有序的連結串列結構，範圍查詢的時候可以沿著連結串列進行順序遍歷，不需要回溯到內部節點，提高了範圍查詢的效率

索引細節

覆蓋索引

減少樹的搜尋次數，顯著提升查詢效能，即建立聯合索引，將查詢的值也加入到索引中，這樣目標值已經在索引樹上，可以直接提供查詢結果，不需要回表

最左字首原則

B+樹這種索引結構，可以利用最左字首來定位記錄，只要檢索的值滿足最左字首，就能利用索引來加速檢索（這個最左字首可以是聯合索引的最左N個欄位，也可以是字串索引的最左M個字元）

基於這個原則，建立聯合索引時如果可以透過調整順序，可以少維護一個索引，則這個順序就是需要優先使用的（比如有（a，b）索引後，就不需要單獨為a建立索引了）

索引下推

MySQL5.6之後，在索引遍歷過程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數img

普通索引和唯一索引

普通索引允許在索引列中存在重複值；唯一索引要求索引值是唯一的

  • 查詢過程的區別：普通索引查詢到第一個滿足條件的記錄後還需要找下一個記錄，但由於InnoDB是按資料頁為單位進行讀寫的，當讀到第一個滿足條件的記錄時會將整個16KB的頁讀到記憶體，所以和唯一索引的查詢效率相差無幾

  • 更新過程的區別：普通索引在進行更新操作時，如果要更新的記錄不在記憶體中的話，可以利用到change buffer，減少隨機磁碟訪問，可以明顯提升更新效能；而唯一索引進行更新操作時需要判斷更新操作有沒有衝突，不能使用change buffer

    change buffer：

    當更新一個資料頁的時候如果它在記憶體上就直接更新；如果資料頁不在記憶體上的話，innoDB會將更新操作快取在change buffer中，下次查詢訪問到這個資料頁將它讀到記憶體之後，再利用change buffer進行資料頁的更新（這個過程叫做merge，會定期、訪問到資料頁、正常關閉資料庫時觸發）

    對於寫多讀少的業務來說，寫完就訪問頁面的機率比較小，此時change buffer的效果最好

    而如果寫完後立刻做查詢，那麼把更新記錄到change buffer之後要立刻做merge，反而增加了change buffer的維護代價

索引選擇

最佳化器會選擇索引，找到最優執行方案，判斷標準有掃描行數（包括回表查詢的代價也會計算進去）、是否使用臨時表、是否排序等等

mysql會透過取樣統計來求得索引的基數，即預設選擇N個資料頁，然後統計這些頁面上的不同值，得到平均值後再乘以這個索引的頁面數，當變更資料行數超過1/M之後再自動觸發重新做一次索引統計

索引選擇異常和處理

當發現explain的結果預估的rows值和實際情況差距較大，可以執行analyze table重新統計索引資訊

採用force index強行選擇一個索引：select * from t force index(a) where a between 1 and 5000; 缺點在於變更不及時，要等到線上出問題了再去修改sql語句加上force index

考慮修改語句，引導MySql使用我們期望的索引，比如說將order by b改成order by b,a，引導MySql也考慮用a作為索引

或是直接刪除掉這個索引，新增一個更合適的索引供最佳化器使用

正確地使用字首做索引

  • 字首索引可能會增加掃描行數

使用字首索引，定義好這個字首的長度，如果能夠形成高區分度，讓重複的key值變少，就能夠做到既節省空間，又不用額外增加太多的查詢成本

先計算該列上有多少個不同的值：select count(distinct email) as L from User;，然後依次選取不同長度的字首來看這個值：

select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

預設一個可以接受的損失區分度比例a%，然後在返回的L4~L7中，找出不小於L*(1-a%)的值

  • 使用字首索引就無法使用到覆蓋索引的最佳化

因為使用字首索引查到結果後，系統不確定字首索引的定義是否截斷了完整資訊，所以都會再回表查詢

如果字首的區分度不夠好的話（比如說身份證前6位都相同），可以用兩種方法解決：

  • 使用倒序儲存：呼叫reverse()將欄位倒序存入
  • 使用hash欄位：在表上建立一個整數字段儲存校驗碼，同時在這個欄位上建立索引，每插入資料時都呼叫crc32()求校驗碼

更好地使用innodb索引

挑選一個優秀的列作為索引列

  • 索引列的型別儘量小（索引就越小）
  • 索引列的基數儘量大（基數：該索引列的不重複資料數量）
  • 索引列出現在排序、搜尋或分組的列
  • 索引列可以只針對字串的字首建立
  • 索引列儘量設定為AUTO_INCREMENT（防止插入的記錄中索引列忽大忽小，發生頁面分裂和合並）

瞭解寫出什麼樣的語句可以用上索引

  • 全值匹配（搜尋條件的列=索引列，直接二分法快速匹配）
  • 匹配一個或多個左邊的列
  • 匹配列字首（字首都是排好序的）
  • 匹配範圍值
  • 精確匹配某一列並且範圍匹配另一列
  • 用於order by排序（使用索引列免去在記憶體或檔案中排序）
  • 用於group by分組
  • 比較表示式中索引列單獨出現（比如c1<2可以用上索引，2*c1<4就不行）
  • 在查詢列表裡只包含索引列（這樣在聯合索引中查詢到目標時就不需要回表了，即索引覆蓋）

避免以下情況：

  • 各個列遞增或遞減不一致
  • select選取的列不在聯合索引或是二級索引中，儘量避免直接select *
  • WHERE子句中出現了非排序使用到的索引列
  • 排序列包含非同一個索引的列
  • 排序使用了複雜的表示式：比如SELECT * FROM xxx ORDER BY UPPER(c1) LIMIT 10

其他索引

MYISAM裡的索引：非聚簇索引，即葉子節點存放的是資料所在地址，而不是資料本身

memory裡的雜湊索引O(1）搜尋效率好+磁碟IO花費少，但雜湊表裡的資料沒有順序可言，只能用於等值搜尋，沒辦法處理磁碟上的資料然後載入到記憶體上構建高效的搜尋資料結構，因為他沒有辦法減少磁碟IO次數，只是基於記憶體上的搜尋

Innodb裡的自適應雜湊索引：自適應即不是自己建立的，而是最佳化的功能；如果檢測同樣的二級索引不斷被使用，它會根據這個二級索引，在記憶體上根據二級索引樹上的二級索引值，構建一個雜湊索引，來加速搜尋

使用show engine innodb status\G檢視兩個重要資訊

  • RW-latch等待的執行緒數量，預設分配8個分割槽，如果同一個分割槽等待的執行緒數量過多，考慮關掉自適應雜湊
  • 比較使用自適應雜湊搜尋的頻率 和 使用二級索引樹搜尋的頻率

可以使用show indexs from 表名檢視某個表裡的所有索引的資訊

相關文章