MySQL的索引原理及使用

糖拌西红柿發表於2024-09-01
MySql索引

MySQL中的索引模型

Mysql中的索引使用的資料結構一般為搜尋樹，這裡的搜尋樹，一般使用B樹，這裡補一下資料結構中的B樹結構；說B樹之前，先順一個前置的知識點，平衡二叉樹；

平衡二叉樹

二叉樹應該都不陌生，大學資料結構的基本入門，二叉排序樹是基於二叉樹上多了個“有序”的概念，簡單來說，即 左 < 右或 右<左，反正就是，樹是按著順序建立的 。

相比較普通的二叉樹，二叉排序樹具有“順序”的特點，但是當極端情況下，即單邊順序排列下去，二叉排序樹就成了單連結串列了，失去了樹的意義，於是在二叉排序樹的基礎上進一步加強，即：滿足二叉排序樹特點同時又左右子樹高度差小於等於1，就有了平衡二叉樹。平衡二叉樹的特點如下：

  1. 二叉排序樹

  2. 任何一個節點的左子樹或者右子樹都是「平衡二叉樹」（左右高度差小於等於 1）

B樹

平衡二叉樹已經很好了，其因為順序、且限制了樹的飽和，於是使得平衡二叉樹在檢索時，具有很高的效能，複雜度才O(logN)，此時影響查詢效能的瓶頸就演變到節點數量N了；根據樹的特點，進行比對計算的次數取決於樹的高度，如果節點的數量固定，我們可以透過控制每層的節點數來控制樹的高度，不拘泥於“二叉”這一特性，變成多叉的平衡樹（B-樹）。

B樹是一個絕對平衡樹，所有的葉子節點在同一高度。在每個節點儲存多個元素，在每個節點除了指標節點外，還儲存相應的資料；相比二叉平衡查詢樹，在整個查詢過程中，雖然資料的比較次數並沒有明顯減少，但是磁碟IO次數會大大減少；B樹的高度一般2至3層就能滿足大部分的應用場景，所以使用B樹構建索引可以很好的提升查詢的效率。

B樹特點

  • 每個節點至多可以擁有m棵子樹（m階）。
  • 根節點，至少有2個節點。
  • 非根非葉的節點至少有的Ceil(m/2)個子樹(Ceil表示向上取整，圖中5階B樹，每個節點至少有3個子樹，也就是至少有3個叉)。
  • 非葉節點中的資訊包括[n,A0,K1,A1,K2,A2,…,Kn,An]，，其中n表示該節點中儲存的關鍵字個數，K為關鍵字且Ki<Ki+1，A為指向子樹根節點的指標。
  • 從根到葉子的每一條路徑都有相同的長度，也就是說，葉子節在相同的層，並且這些節點不帶資訊，實際上這些節點就表示找不到指定的值，也就是指向這些節點的指標為空。

B+樹

儘管B樹能較為明顯的提升效率，但是它節點攜帶資料這一特徵，隨著儲存資料的增長，必然導致空間的佔用，如果使用到資料庫索引時，意味著會樹相應就會變高，一個頁中可儲存的資料量就會變少，磁碟IO次數就會變大。

所以引入了B樹的另一增強B+樹，相比於B樹，B+樹的特點在於：

  1. 內部節點只儲存鍵值，不儲存資料。資料只儲存在葉子節點中，並且葉子節點包含了全部的鍵值和指向資料的指標。

  2. 葉子節點之間有雙向連結串列連結，這使得範圍查詢更加高效，因為可以透過連結串列順序訪問葉子節點。

B+樹的最底層葉子節點包含了所有的索引項。從圖上可以看到，B+樹在查詢資料的時候，由於資料都存放在最底層的葉子節點上，所以每次查詢都需要檢索到葉子節點才能查詢到資料。所以在需要查詢資料的情況下每次的磁碟的IO跟樹高有直接的關係，但是從另一方面來說，由於資料都被放到了葉子節點，所以放索引的磁碟塊鎖存放的索引數量是會跟這增加的，所以相對於B樹來說，B+樹的樹高理論上情況下是比B樹要矮的。也存在索引覆蓋查詢的情況，在索引中資料滿足了當前查詢語句所需要的全部資料，此時只需要找到索引即可立刻返回，不需要檢索到最底層的葉子節點。

索引分類

邏輯劃分

按功能分

主鍵索引

一張表只能有一個主鍵索引，不允許重複、不允許為 NULL，使用關鍵字PRIMARY KEY定義；PRIMARY KEY的通常基於B樹（B-Tree）實現，本質上是特殊的唯一索引。

ALTER TABLE TableName ADD PRIMARY KEY(column_list);

唯一索引

資料列不允許重複，允許為 NULL 值；一張表可有多個唯一索引，索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一；一般使用UNIQUE INDEX定義

CREATE UNIQUE INDEX IndexName ON `TableName`(`欄位名`(length));
# 或者
ALTER TABLE TableName ADD UNIQUE (column_list);

普通索引

一張表可以建立多個普通索引，一個普通索引可以包含多個欄位，允許資料重複，允許 NULL 值插入；

CREATE INDEX IndexName ON `TableName`(`欄位名`(length));
# 或者
ALTER TABLE TableName ADD INDEX IndexName(`欄位名`(length));

按使用分

按使用劃分其實就是單個列進行索引還是多個列組合索引。

  • 單例索引：一個索引只包含一個列，一個表可以有多個單例索引。
  • 組合索引：一個組合索引包含兩個或兩個以上的列。查詢的時候遵循 mysql 組合索引的 “最左字首”原則，即使用 where 時條件要按照建立索引的時候欄位的排列方式放置索引才會生效。MySQL中可以使用唯一索引和普通索引進行組合索引
-- 多列組合（普通索引）
CREATE INDEX <index_name> ON <table_name > (column1, column2, column3);
或
ALTER TABLE <table_name> ADD INDEX <index_name> (column1, column2,column3);

-- 多列組合（唯一索引）
CREATE UNIQUE INDEX <index_name> ON <table_name> (column1, column2,column3);
或
ALTER TABLE <table_name> ADD UNIQUE INDEX <index_name> (column1, column2,column3));

關於組合索引使用中的效能及使用原則，可直接跳至組合索引的使用原則

物理劃分

之所以說根據物理劃分是索引在物理儲存原理上的特點。而且主要是針對B+樹索引結構來講

簇的含義：為了提高某個屬性(或屬性組)的查詢速度，把這個或這些屬性(稱為聚簇碼)上具有相同值的元組集中存放在連續的物理塊。

聚簇索引

聚簇索引（clustered index）不是單獨的一種索引型別，而是一種資料儲存方式。這種儲存方式根據表的主鍵構造一棵B+樹，且B+樹葉子節點存放的都是表的行記錄資料時，該主鍵索引為聚簇索引。

在聚簇索引中，其資料檔案本身就是索引檔案。 樹的葉節點 data 域儲存了完整的資料記錄。這個索引的 key 是資料表的主鍵，進行查詢時，根據搜尋演算法，在樹上找到葉子節點後，資料也一併找到。這種情況也就是常說的覆蓋索引。

注：每張表最多隻能擁有一個聚簇索引

非聚簇索引

非聚簇索引又叫輔助索引或二級索引（在Mysql中primary key之外的均為輔助索引），與簇索引相反，資料和索引是分開的，B+Tree 葉節點的 data 域存放的是主鍵和該欄位的值。在索引檢索的時候，首先按照 B+Tree 搜尋演算法搜尋索引，如果指定的內容 存在，則取出其 data 域中的主鍵，然後再在聚簇索引（Primary Key 索引樹上再次搜尋）。

假設我們針對user表查詢

SELECT sex,age FROM user WHERE name = '陳芳';

此時user表存在基於主鍵ID建立的聚簇索引，和基於name建立的非聚簇索引，那麼查詢過程則是這樣的：

首先因為使用了name列進行等值查詢，此時會先使用Name索引的B+樹，進行搜尋，當找到name為陳芳的葉子節點時，會拿到其ID，再回到基於ID的聚簇索引B+樹上進行基於ID=1的搜尋，最終找到其葉子節點，拿到上面的性別和年齡，其中這種需要兩段使用索引的過程就是常說的回表查詢。

雖然InnoDB和MyISAM儲存引擎都預設使用B+樹結構儲存索引，但是隻有InnoDB的主鍵索引才是聚簇索引，InnoDB中的輔助索引以及MyISAM使用的都是非聚簇索引。

索引的使用

建立索引的原則

  • 在經常需要搜尋的列上，可以加快搜尋的速度
  • 在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構
  • 在經常用在連線（JOIN）的列上，這些列主要是一外來鍵，可以加快連線的速度
  • 在經常需要根據範圍（<，<=，=，>，>=，BETWEEN，IN）進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的
  • 在經常需要排序（order by）的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
  • 在經常使用在WHERE子句中的列上面建立索引，加快條件的判斷速度。

資料型別對於索引的不同表現

VARCHAR型別

VARCHAR型別用於儲存可變長度的字串。

  • 字首索引：由於VARCHAR型別的列可能很長，為了節省空間和提高效率，可以只為每個值的前幾個字元建立索引，稱為字首索引。但是，字首索引會降低索引的選擇性，可能需要掃描更多的行來找到匹配的記錄。
  • 區分度：在選擇字首長度時，應該考慮列的區分度。如果大多數行的字首都相同，那麼字首索引的效果可能不佳。
  • 索引效率：等值查詢、IN子句查詢等效率較高，VARCHAR型別的列在排序和比較時是按字典順序進行的，這可能會影響範圍查詢的效能。VARCHAR型別的索引在沒有使用函式或表示式的情況下效能較好，但如果在查詢中對字串列使用了函式（如CONCAT、SUBSTRING等），索引可能失效，導致全表掃描。

數字型別

數字型別泛指數字相關型別，例如INT、FLOAT、Double等。

  • 選擇性：整數型別的列通常具有很高的選擇性，這意味著索引中的值分佈均勻，可以有效地減少查詢中需要檢查的行數。
  • 索引效率：等值查詢、範圍查詢（>、<、BETWEEN等）效率較高，數字型別的索引在進行範圍查詢時查詢效能上通常優於字串型別。

日期型別

DATETIME型別用於儲存日期和時間。

  • 時區影響：在處理跨時區的資料時，需要注意時區轉換可能對索引的使用和查詢結果產生影響。
  • 函式影響：如果查詢中使用了日期函式（如DATE_FORMAT），可能會導致索引失效
  • 索引效率：DATETIME型別的列在進行等值查詢和範圍查詢時效率較高，因為日期和時間的比較操作非常快速。

其他

  • 列的NULL值：如果列中包含NULL值，索引的行為可能會受到影響。例如，VARCHAR型別的列如果允許NULL值，可能會在索引中佔用額外的空間。
  • 列的預設值：預設值可能會影響索引的選擇性。例如，如果一個INT型別的列有一個預設值，那麼很多行可能會有相同的預設值，這會降低索引的選擇性。
  • 列的更新頻率：頻繁更新的列可能會使索引變得碎片化，從而影響索引的效能。因此，對於經常更新的列，可能需要定期重建索引。

組合索引的使用原則

在MySQL中使用組合索引時，"最左字首"原則決定了索引的使用方式和查詢最佳化的效果。這個原則指的是，MySQL會按照組合索引中列的順序，從左到右使用索引中的列。只有當索引的最左列被包含在查詢條件中時，索引才會被有效地使用。如果查詢條件中跳過了索引中的某個列，那麼該列及其右邊的所有列都不會被用於索引查詢。

例如：將tb_flow_visit表的 源ip、目的ip、訪問時間 三個欄位設定為組合索引（注意設定的順序）

ALTER TABLE tb_flow_visit ADD INDEX commIndies (sip, dip,visit_time);

順序優先

在組合索引中，MySQL會按照（源ip、目的ip、訪問時間）這個順序來匹配條件。如果WHERE子句中首先出現的是sip欄位匹配相關的條件，那麼索引才會被使用。

-- 有效索引查詢（全部匹配）
SELECT * FROM tb_flow_visit WHERE sip = '192.168.1.1' AND dip = '192.168.1.2' AND visit_time = '2023-08-01';

-- 有效索引查詢（部分匹配）
SELECT * FROM tb_flow_visit WHERE sip = '192.168.1.1' AND dip = '192.168.1.2';

不能跳過

如果你的查詢條件首先使用了dip（或者visit_time）而沒有包含sip，那麼這個組合索引將不會被使用，因為違反了最左字首原則。

-- 無效索引，不能跳過sip
SELECT * FROM tb_flow_visit WHERE  dip = '192.168.1.2' AND visit_time = '2024-08-01'; 

-- 無效索引，組合索引中，如過不按照索引順序，即便單獨使用其中任何一列也無效
SELECT * FROM tb_flow_visit WHERE dip = '192.168.1.2';
或
SELECT * FROM tb_flow_visit WHERE visit_time > '2024-08-01';;

部分匹配

如果WHERE子句中包含了從索引最左列開始的連續列，那麼索引可以被部分使用。例如:使用了最左列sip的欄位和visit_time欄位，因此會有效使用sip列的索引，但不會使用dip和visit_time列的索引。

SELECT * FROM tb_flow_visit WHERE sip = '192.168.1.1' AND visit_time > '2024-08-01';

範圍查詢限制

在MySQL中，最左原則的範圍查詢限制是指在使用組合索引時，如果查詢條件中包含了範圍查詢（如>、<、BETWEEN、LIKE等），那麼這個範圍查詢必須應用於組合索引的最左列或者緊挨著最左列的列（如果最左列是常量條件）。如果範圍查詢跳過了最左列或者在非最左列上使用，那麼索引將不會被有效使用； 例如在最左列（sip）上使用了範圍查詢（如sip > '192.168.1.2'），那麼後續的列仍然可以被索引使用，但最左列之後的列必須是等值查詢，不能是範圍查詢。

-- 無效索引
SELECT * FROM tb_flow_visit WHERE  sip > '192.168.1.2' AND dip > '192.168.2.1';
AND  visit_time = '2024-08-01';
-- 有效索引
SELECT * FROM tb_flow_visit WHERE  sip > '192.168.1.2' AND dip = '192.168.2.1' AND visit_time = '2024-08-01';

相關文章