MySQL（二）：快速理解MySQL資料庫索引

方塊人發表於2020-12-20
MySql資料庫索引

索引

基本概念：索引是在儲存引擎層實現的，而不是在伺服器層實現的，所以不同儲存引擎具有不同的索引型別和實現。

資料結構

  • Tree 指的是 Balance Tree，也就是平衡樹。平衡樹是一顆查詢樹，並且所有葉子節點位於同一層。
  • B+ Tree 是基於 B Tree 和葉子節點順序訪問指標進行實現，它具有 B Tree 的平衡性，並且通過順序訪問指標來提高區間查詢的效能。
  • 在 B+ Tree 中，一個節點中的 key 從左到右非遞減排列，如果某個指標的左右相鄰 key 分別是 keyi 和 keyi+1，且不為 null，則該指標指向節點的所有 key 大於等於 keyi 且小於等於 keyi+1。

基本原理

  • 進行查詢操作時，首先在根節點進行二分查詢，找到一個 key 所在的指標，然後遞迴地在指標所指向的節點進行查詢。直到查詢到葉子節點，然後在葉子節點上進行二分查詢，找出 key 所對應的 data。
  • 插入刪除操作會破壞平衡樹的平衡性，因此在進行插入刪除操作之後，需要對樹進行分裂、合併、旋轉等操作來維護平衡性。

紅黑樹

紅黑樹等平衡樹也可以用來實現索引，但是檔案系統及資料庫系統普遍採用 B+ Tree 作為索引結構，這是因為使用 B+ 樹訪問磁碟資料有更高的效能。

  • B+ 樹有更低的樹高：平衡樹的樹高 O(h)=O(logdN)，其中 d 為每個節點的出度。紅黑樹的出度為 2，而 B+ Tree 的出度一般都非常大，所以紅黑樹的樹高 h 很明顯比 B+ Tree 大非常多。
  • 磁碟訪問原理：作業系統一般將記憶體和磁碟分割成固定大小的塊，每一塊稱為一頁，記憶體與磁碟以頁為單位交換資料。資料庫系統將索引的一個節點的大小設定為頁的大小，使得一次 I/O 就能完全載入一個節點。

    如果資料不在同一個磁碟塊上，那麼通常需要移動制動手臂進行尋道，而制動手臂因為其物理結構導致了移動效率低下，從而增加磁碟資料讀取時間。B+ 樹相對於紅黑樹有更低的樹高，進行尋道的次數與樹高成正比，在同一個磁碟塊上進行訪問只需要很短的磁碟旋轉時間，所以 B+ 樹更適合磁碟資料的讀取。
  • 磁碟預讀特性：為了減少磁碟 I/O 操作，磁碟往往不是嚴格按需讀取，而是每次都會預讀。預讀過程中，磁碟進行順序讀取，順序讀取不需要進行磁碟尋道，並且只需要很短的磁碟旋轉時間，速度會非常快。並且可以利用預讀特性，相鄰的節點也能夠被預先載入。

索引分類

B+樹索引

  1. 是大多數 MySQL 儲存引擎的預設索引型別。
  2. 因為不再需要進行全表掃描，只需要對樹進行搜尋即可，所以查詢速度快很多。
  3. 因為 B+ Tree 的有序性，所以除了用於查詢，還可以用於排序和分組。
  4. 可以指定多個列作為索引列，多個索引列共同組成鍵。
  5. 適用於全鍵值、鍵值範圍和鍵字首查詢，其中鍵字首查詢只適用於最左字首查詢。如果不是按照索引列的順序進行查詢，則無法使用索引。
  6. InnoDB 的 B+Tree 索引分為聚簇索引（主索引）和非聚簇索引（輔助索引）。
  7. 唯一索引：主鍵就是一種特殊的唯一索引，唯一索引允許null值，但主鍵列不允許為null值，一張表最多建議一個主鍵

雜湊索引

  1. 雜湊索引能以O（1）時間進行查詢，但是失去了有序性特點InnoDB
  2. 儲存引擎有一個特殊的功能叫“自適應雜湊索引”，當某個索引值被使用的非常頻繁時，會在 B+Tree 索引之上再建立一個雜湊索引，這樣就讓 B+Tree 索引具有雜湊索引的一些優點，比如快速的雜湊查詢。

全文索引

  1. MyISAM 儲存引擎支援全文索引，用於查詢文字中的關鍵詞，而不是直接比較是否相等。
  2. 查詢條件使用 MATCH(clo1,clo2..) AGAINST(value)，而不是普通的 WHERE。全文索引使用倒排索引實現，它記錄著關鍵詞到其所在文件的對映。
  3. InnoDB 儲存引擎在 MySQL 5.6.4 版本中也開始支援全文索引。
    FULLTEXT (clo1,clo2)只有建立的全文索引的列可以進行全文索引

索引優化

獨立的列

進行查詢時，索引列不能是表示式的一部分，也不能是函式的引數，否則無法使用索引

多列索引

在需要使用多個列作為條件進行查詢時，使用多列索引比使用多個單列索引效能更好。
列如下面的語句中可以將actor_id和film_id設定多列索引

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

索引列的順序

索引的選擇性是指：不重複的索引值和記錄總數的比值。最大值為 1，此時每個記錄都有唯一的索引與其對應。選擇性越高，每個記錄的區分度越高，查詢效率也越高。
讓選擇性最強的索引列放在最前面
輸入語句：

SELECT COUNT(DISTINCT addr_id)/COUNT(*) AS addr_id_selectivity,
COUNT(DISTINCT user_id)/COUNT(*) AS user_id_selectivity,
COUNT(*)
FROM user_address;

結果：

addr_id_selectivity：1.0000
user_id_selectivity：0.1250
count(*)：64
從上述結果分析：主鍵是唯一的，user_id會有重複，所以寫sql語句時，主鍵應該放在最前面
這樣可以逐個分析對應的索引

字首索引

  • 對於 BLOB、TEXT 和 VARCHAR 型別的列，必須使用字首索引，只索引開始的部分字元。
  • 字首長度的選取需要根據索引選擇性來確定。某些列，一般是字串型別，很長，全部作為索引大大增加儲存空間，索引也需要維護，對於長字串，又想作為索引列，一個可取的辦法就是取前一部分（字首），代表一整列作為索引串。
  • 如何確保這個字首能代表或大致代表這一列？所以mysql中有個概念是索引的選擇性，是指索引中不重複的值的數目（也稱基數）與整個表該列記錄總數（#T）的比值，比如一個列表（1,2,2,3），總數是4，不重複值數目為3，選擇性為3/4，因此選擇性範圍是[1/#T, 1]，這個值越大，表示列中不重複值越多，越適合作為字首索引，唯一索引（UNIQUE KEY）的選擇性是1。

MySQL效能優化神器Explain使用分析

  • Explain 用來分析 SELECT 查詢語句，開發人員可以通過分析 Explain 結果來優化查詢語句。
    • select_type : 查詢型別，有簡單查詢、聯合查詢、子查詢等
    • key : 使用的索引
    • rows : 掃描的行數

總結

  • 資料庫只做兩件事情：儲存資料、檢索資料。而索引是在你儲存的資料之外，額外儲存一些路標（一般是B+樹），以減少檢索資料的時間。所以索引是主資料衍生的附加結構。
  • 一張表可以建立任意多個索引，每個索引可以是任意多個欄位的組合。索引可能會提高查詢速度（如果查詢時使用了索引），但一定會減慢寫入速度，因為每次寫入時都需要更新索引，所以索引只應該加在經常需要搜尋的列上，不要加在寫多讀少的列上。
  • 多使用explain對語句進行索引分析，任何理論都是建立在實際的分析基礎上，explain語句關注那幾個關鍵詞即可。盲目的加索引並不是好的，有時候索引反而成為拖慢我們系統執行速度的源頭.優化索引其實就兩件事，第一找到區別度大的列，第二看生產的查詢場景是否有用到這個列，加上索引用explain分析，看是否會比不加上索引效能提升更快

相關文章