mysql資料庫的索引

王森發表於2019-05-15
MySql資料庫索引

什麼是索引

索引是對資料庫表中一列或多列的值【排好序】的資料結構。

為什麼要加索引

因為建立索引可以大大提高系統的查詢效能。

怎麼提高查詢效能的

簡單的理解：一張資料量比較大的表格如果沒有新增任何索引，那我們在執行查詢的時候
就會是進行全表掃描，逐行比對，這樣的讀取效率肯定很低，如果我們為資料建立了索引
索引的實現方式又是支援快速查詢的這樣我們只需要先查詢索引中符合條件的，
然後再通過索引指向的資料行位置就可以實現快速定位資料了，不用全表掃描了。

索引儲存在什麼地方

mysql資料庫的索引

資料庫索引是儲存在磁碟上，當表中的資料量比較大時，索引的大小也跟著增長，達到幾個G甚至更多。
當我們利用索引進行查詢的時候，不可能把索引全部載入到記憶體中，只能逐一載入每個磁碟頁，這裡的磁碟頁就對應索引樹的節點。

為什麼雜湊表、完全平衡二叉樹、B樹、B+樹都可以優化查詢，為何Mysql選擇B+樹？

雜湊表

雜湊表可能會出現雜湊衝突。
mysql資料庫的索引
最主要的原因是它不支援範圍查詢。

完全平衡二叉樹

mysql資料庫的索引
如圖如果一個樹的高度很大，如果查詢的資料剛好在葉子節點那經歷的磁碟Io的次數就是這個數的高度。
所以極端情況下平衡二叉樹也不是優選。

B-Tree

先說說幾個概念：

  • 度（節點的資料儲存個數）也就是說B-Tree上的一個節點可以儲存多個資料。
  • 葉節點具有相同的深度
  • 葉節點的指標為空
  • 節點中的資料從左到右遞增排列
    mysql資料庫的索引
    這的確解決了樹的高度問題，因為：B-Tree的節點可以儲存多個值，高度肯定小於平衡二叉樹，磁碟io的次數也會少。
    但是在範圍查詢方面較比B+Tree差點。
    題外話：B-Tree和BTree是一種樹。

B+Tree

B+Tree是B-Tree的變種，
mysql資料庫的索引
B+樹的表示要比B樹要“胖”，原因在於B+樹中的非葉子節點會冗餘一份在葉子節點中，並且葉子節點之間用指標相連。

mysql的實現中，B+Tree是把非葉子節點中只儲存索引，不儲存資料，只有葉子節點儲存資料，這樣節點中的空間更多的儲存了索引，增加了度。
最大程度的降低了樹的高度，再加上一個節點的大小設定成為一頁或頁的倍數，一次磁碟io就可以讀出了一個節點中的很多資料。
載入到記憶體中再進行查詢就很快了

所以最後總結：
使用B+Tree:可以提高查詢索引時的磁碟IO效率，並且可以提高範圍查詢的效率，並且B+樹裡的元素也是有序的。

MyISAM和InnoDB的B+Tree實現

MyISAM中的B+Tree

MYISAM中葉子節點的資料區域儲存的是資料記錄的地址.
mysql資料庫的索引
MyISAM的索引是單獨一個檔案存放的。
mysql資料庫的索引
MyISAM中的主鍵索引和輔助引是沒有區別的，其葉子節點存放的都是資料記錄的地址。

InnoDB中的B+Tree

InnoDB中的葉子節點資料區域儲存的內容和主鍵索引和輔助索引引有關。
如果是主鍵索引儲存的就是索引+資料（index+data）
mysql資料庫的索引
Innodb的主鍵索引要比MyISAM的主鍵索引查詢效率要高，因為找到主鍵索引就找到了資料,MyISAM還有通過地址查詢一次。
如果是輔助索引儲存的是主鍵的值
mysql資料庫的索引
因此可以看出InnoDB的輔助索引會發生兩次，一次通過輔助索引查詢主鍵索引，一次是通過主鍵索引查詢到資料。

InnoDB的索引是和資料檔案放在一起的。
mysql資料庫的索引

聚集索引和非聚集索引

聚集索引

資料行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個表中只能擁有一個聚集索引。

  • 聚集索引的葉子節點存放有對應的資料節點，可以直接獲取到對應的資料，
    mysql資料庫的索引

  • 如果不建立索引，系統會自動建立一個隱含列作為表的聚集索引。
  • 最好還是在建立表的時候新增聚集索引
  • 在經常用於查詢或聚合條件的欄位上建立聚集索引。這類查詢條件包括 between, >, <,group by, max,min, count等。
    缺點：

  • 插入和更新索引的速度會比較慢，因為將會導致被更新的行移動。

非聚集索引

資料行的物理順序與列值的邏輯順序不相同，一個表中可以擁有多個非聚集索引。

  • 葉子節點存放的不是實際資料，而是指向實際資料的指標。
    mysql資料庫的索引

  • 聚集索引以外的索引都是非聚集索引,細分可以分為：普通索引，唯一索引，全文索引

注意：

InnoDB 主鍵使用的是聚簇索引，MyISAM 不管是主鍵索引，還是二級索引使用的都是非聚簇索引

建立索引的依據

什麼樣的欄位適合建索引

索引是建立在資料庫表中的某些列的上面。因此，在建立索引的時候，應該仔細考慮在哪些列上可以建立索引，在哪些列上不能建立索引。 一般來說，應該在具備下述特性的列上建立索引：

  • 第一、在經常需要搜尋的列上，可以加快搜尋的速度；
  • 第二、在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構；
  • 第三、在經常用在連線的列上，這些列主要是一些外來鍵，可以加快連線的速度；
  • 第四、在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的；
  • 第五、在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
  • 第六、在經常使用在WHERE子句中的列上面建立索引，加快條件的判斷速度。
    建立索引，一般按照select的where條件來建立，
    比如： select的條件是where f1 and f2，那麼如果我們在欄位f1或欄位f2上建立索引是沒有用的，只有在欄位f1和f2上同時建立索引才有用等。

什麼樣的欄位不適合建立索引

  • 對於那些在查詢中很少使用或者參考的列不應該建立索引
  • 對於那些只有很少資料值的列也不應該增加索引，比如：性別，狀態，這是因為，由於這些列的取值很少，建立索引效果也不明顯。
  • 對於那些定義為text, image和bit資料型別的列不應該增加索引

記住這些可以提高索引的使用效率

mysql常見的優化策略

資源

資料結構：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
面試必備之Mysql索引底層原理分析
為什麼資料庫選B-tree或B+tree而不是二叉樹作為索引結構
關於B-樹問題的演示圖解

相關文章