深入理解MySQL系列之索引

曹自標發表於2020-12-24
MySql索引

索引

查詢一條資料的過程

先看下InnoDB的邏輯儲存結構：

  1. 表空間：可以看做是InnoDB儲存引擎邏輯結構的最高層，所有的資料都存放在表空間中。預設有個共享表空間ibdata1。如果啟用innodb_file_per_table引數，需要注意每張表的表空間記憶體放的只是資料、索引和插入緩衝Bitmap頁，其他類的資料，如回滾資訊、插入緩衝索引頁、系統事務資訊、二次寫緩衝等還是存放在原來共享表空間中。

  2. 段：
    表空間是由各個段組成，常見的段有資料段、索引段、回滾段等。資料段即為B+樹葉子節點(Leaf node segment)，索引段即為B+樹非葉子節點(Non-leaf node segment)
    區

  3. 區：是由連續頁組成的空間，在任何情況下每個區大小都為1MB。預設情況下，儲存引擎頁的大小為16KB，即一個區中一共有連續64個連續的頁。而為保證頁的連續性，InnoDB儲存引擎一次從磁碟申請4-5個區。

  4. 頁：
    頁(也可以稱塊)，是InnoDB磁碟管理的最小單位。預設每個頁大小16KB。1.2x版本後也可以通過引數innodb_page_size設定為4k、8k、16k

如查一條資料：select * from user where id=5;

這裡id是主鍵,我們通過這棵B+樹來查詢，首先會去找到根頁，每張表的根頁位置在表空間檔案中是固定的；找到根頁後通過二分查詢法，定位到id=5的資料應該在指標P5指向的頁中，那麼進一步去page number=5的頁中查詢，同樣通過二分查詢法即可找到id=5的記錄：

計算一棵B+樹可以存放多少行資料

也可以通過命令檢視InnoDB每頁預設16KB：

show variables like 'innodb_page_size';

先計算非葉子節點， 假設主鍵ID為bigint型別，長度為8位元組，而指標大小在InnoDB原始碼中設定為6位元組，這樣一共14位元組

而一個頁中能存放多少這樣的單元，其實就代表有多少指標，即16384/14=1170。

那麼可以算出一棵高度為2的B+樹，能存放1170*16=18720條這樣的資料記錄。

根據同樣的原理我們可以算出一個高度為3的B+樹可以存放：1170117016=21902400條這樣的記錄。

所以在InnoDB中B+樹高度一般為1-3層，它就能滿足千萬級的資料儲存。

索引一些概念
  1. 聚簇索引(clustered index): 就是將索引和資料放到一起，找到索引也就找到了資料；如下圖葉子節點存放一行所有資料。

  1. 輔助索引(Secondary Index或非聚簇索引): 就是將資料和索引分開，查詢時需要先查詢到索引，然後通過索引回表找到相應的資料。
    回表：先通過資料庫索引掃描出資料所在的行，再通過行主鍵id取出索引中未提供的資料，即基於非主鍵索引的查詢需要多掃描一棵索引樹。

如下圖，輔助索引查詢後，會再回表到聚簇索引，最後找到資料。

InnoDB有且只有一個聚簇索引，而MyISAM中都是非聚簇索引。

  1. 聯合索引：指對錶上多個列進行索引。

聯合索引的最左字首匹配原則： 對多個欄位同時建立的組合索引(有順序，ABC，ACB是完全不同的兩種聯合索引) 以聯合索引(a,b,c)為例，建立這樣的索引相當於建立了索引a、ab、abc三個索引。另外組合索引實際還是一個索引，並非真的建立了多個索引，只是產生的效果等價於產生多個索引。

  1. 覆蓋索引: 即從輔助索引中就可以得到查詢的記錄，而不需要查詢聚簇索引中的記錄。

使用覆蓋好處：

  • 輔助索引不包含整行記錄的所有資訊，故其大小要遠小於聚簇索引，減少大量IO操作。
  • 對某些統計(如count(id))並不會通過查詢聚簇索引來進行統計，減少IO操作

  1. 唯一索引：以唯一列生成的索引，該列不允許有重複值，但允許有空值(NULL)

  2. 索引下推：MySQL 5.6引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的欄位先做判斷，過濾掉不符合條件的記錄，減少回表字數。

為什麼選B+樹，而不是B樹

B樹不管葉子節點還是非葉子節點，都會儲存資料，這樣導致在非葉子節點中能儲存的指標數量變少

指標少的情況下要儲存大量資料，只能增加樹的高度，導致IO操作變多，查詢效能變低；

為什麼InnoDB只有一個聚簇索引，而不將所有索引都使用聚簇索引？

因為聚簇索引是將索引和資料都存放在葉子節點中，如果所有的索引都用聚簇索引，則每一個索引都將儲存一份資料，會造成資料的冗餘，在資料量很大的情況下，這種資料冗餘是很消耗資源的。

什麼情況下會發生明明建立了索引，但是執行的時候並沒有通過索引呢？

查詢優化器。

一條SQL語句的查詢，可以有不同的執行方案，至於最終選擇哪種方案，需要通過優化器進行選擇，選擇執行成本最低的方案。

優化過程大致如下：

  • 1、根據搜尋條件，找出所有可能使用的索引
  • 2、計算全表掃描的代價
  • 3、計算使用不同索引執行查詢的代價
  • 4、對比各種執行方案的代價，找出成本最低的那一個 。
索引的優缺點

索引的優點如下：

  • 1、唯一索引可以保證每一行資料的唯一性
  • 2、提高查詢速度
  • 3、加速表與表的連線
  • 4、顯著的減少查詢中分組和排序的時間
  • 5、通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的效能。

索引的缺點如下：

  • 建立索引時，需要對錶加鎖，在鎖表的同時，可能會影響到其他的資料操作
  • 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行 INSERT、UPDATE 和 DELETE。因為更新表時，MySQL 不僅要儲存資料，還要儲存索引檔案。
  • 建立索引會佔用磁碟空間的索引檔案。一般情況這個問題不算嚴重，但如果你在一個大表上建立了多種組合索引，且伴隨大量資料量插入，索引檔案大小也會快速膨脹。
  • 如果某個資料列包含許多重複的內容，為它建立索引就沒有太大的實際效果。
  • 對於非常小的表，大部分情況下簡單的全表掃描更高效。
使用索引時的注意事項

原則：
不應該

1、索引不是越多越好。索引太多，維護索引需要時間跟空間
2、 頻繁更新的資料，不宜建索引。
3、資料量小的表沒必要建立索引。

應該

1、重複率小的列建議生成索引。因為重複資料少，索引樹查詢更有效率，等價基數越大越好。
2、資料具有唯一性，建議生成唯一性索引。在資料庫的層面，保證資料正確性
3、頻繁group by、order by的列建議生成索引。可以大幅提高分組和排序效率
4、經常用於查詢條件的欄位建議生成索引。通過索引查詢，速度更快

索引失效的場景

1、模糊搜尋：左模糊或全模糊都會導致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%'
2、隱式型別轉換：比如select * from t where name = xxx , name是字串型別，但是沒有加引號，所以是由MySQL隱式轉換的，所以會讓索引失效
3、當語句中帶有or的時候：比如select * from t where name=‘sw’ or age=14
4、不符合聯合索引的最左字首匹配：(A,B,C)的聯合索引，你只where了C或B或只有B,C

其他注意事項：

  1. 索引不會包含有 null 值的列，只要列中包含有 null值都將不會被包含在索引中。
  2. 使用短索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和 I/O 操作
  3. 索引列排序。查詢只使用一個索引，因此如果 where 子句中已經使用了索引的話，那麼 order by 中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作；儘量不要包含多個列的排序，如果需要最好給這些列建立複合索引。
  4. 不要在列上進行運算，這將導致索引失效而進行全表掃描
  5. 不使用 not in 和 <> 操作，這不屬於支援的範圍查詢條件，不會使用索引。

《MySQL技術內幕》
https://mp.weixin.qq.com/s/6j64s9W6ogs5Y8BbhhkgnA
https://mp.weixin.qq.com/s/KB73550tKpNccW-WKxT7-A
https://mp.weixin.qq.com/s/ovMx9Dv9NCFxSsFM98uYFw

相關文章