技術文章 » MySQL索引簡介

MySQL索引簡介

2020-07-16 10:05:22
索引是 MySQL 資料庫中的重要物件之一，用於快速找出某個列中有某一特定值的行。

為什麼要使用索引

索引是 MySQL 中一種十分重要的資料庫物件。它是資料庫效能調優技術的基礎，常用於實現資料的快速檢索。

索引就是根據表中的一列或若干列按照一定順序建立的列值與記錄行之間的對應關係表，實質上是一張描述索引列的列值與原表中記錄行之間一一對應關係的有序表。

在 MySQL 中，通常有以下兩種方式存取資料庫表的行資料：

1) 順序存取

順序存取是在表中實行全表掃描，從頭到尾逐行遍歷，直到在無序的行資料中找到符合條件的目標資料。這種方式實現比較簡單，但是當表中有大量資料的時候，效率非常低下。例如，在幾千萬條資料中查詢少量的資料時，使用順序存取方式將會遍歷所有的資料，花費大量的時間，顯然會影響資料庫的處理效能。

2) 索引存取

索引存取是通過遍歷索引來直接存取表中記錄行的方式。使用這種方式的前提是對表建立一個索引，在列上建立了索引之後，查詢資料時可以直接根據該列上的索引找到對應記錄行的位置，從而快捷地查詢到資料。索引儲存了指定列資料值的指標，根據指定的排序順序對這些指標排序。

例如，在學生基本資訊表 students 中，如果基於 student_id 建立了索引，系統就建立了一張索引列到實際記錄的對映表，當使用者需要查詢 student_id 為 12022 的資料的時候，系統先在 student_id 索引上找到該記錄，然後通過對映表直接找到資料行，並且返回該行資料。因為掃描索引的速度一般遠遠大於掃描實際資料行的速度，所以採用索引的方式可以大大提高資料庫的工作效率。

索引的分類

索引的型別和儲存引擎有關，每種儲存引擎所支援的索引型別不一定完全相同。根據儲存方式的不同，MySQL 中常用的索引在物理上分為以下兩類。

1) B-樹索引

B-樹索引又稱為 BTREE 索引，目前大部分的索引都是採用 B-樹索引來儲存的。B-樹索引是一個典型的資料結構，其包含的元件主要有以下幾個：
  • 葉子節點：包含的條目直接指向表裡的資料行。葉子節點之間彼此相連，一個葉子節點有一個指向下一個葉子節點的指標。
  • 分支節點：包含的條目指向索引裡其他的分支節點或者葉子節點。
  • 根節點：一個 B-樹索引只有一個根節點，實際上就是位於樹的最頂端的分支節點。

基於這種樹形資料結構，表中的每一行都會在索引上有一個對應值。因此，在表中進行資料查詢時，可以根據索引值一步一步定位到資料所在的行。

B-樹索引可以進行全鍵值、鍵值範圍和鍵值字首查詢，也可以對查詢結果進行 ORDER BY 排序。但 B-樹索引必須遵循左邊字首原則，要考慮以下幾點約束：
  • 查詢必須從索引的最左邊的列開始。
  • 查詢不能跳過某一索引列，必須按照從左到右的順序進行匹配。
  • 儲存引擎不能使用索引中範圍條件右邊的列。

2) 雜湊索引

雜湊（Hash）一般翻譯為“雜湊”，也有直接音譯成“雜湊”的，就是把任意長度的輸入（又叫作預對映，pre-image）通過雜湊演算法變換成固定長度的輸出，該輸出就是雜湊值。

雜湊索引也稱為雜湊索引或 HASH 索引。MySQL 目前僅有 MEMORY 儲存引擎和 HEAP 儲存引擎支援這類索引。其中，MEMORY 儲存引擎可以支援 B- 樹索引和 HASH 索引，且將 HASH 當成預設索引。

HASH 索引不是基於樹形的資料結構查詢資料，而是根據索引列對應的雜湊值的方法獲取表的記錄行。雜湊索引的最大特點是存取速度快，但也存在下面的一些缺點：
  • MySQL 需要讀取表中索引列的值來參與雜湊計算，雜湊計算是一個比較耗時的操作。也就是說，相對於 B- 樹索引來說，建立雜湊索引會耗費更多的時間。
  • 不能使用 HASH 索引排序。
  • HASH 索引只支援等值比較，如“=”“IN()”或“<=>”。
  • HASH 索引不支援鍵的部分匹配，因為在計算 HASH 值的時候是通過整個索引值來計算的。

根據索引的具體用途，MySQL 中的索引在邏輯上分為以下 5 類：

1) 普通索引

普通索引是最基本的索引型別，唯一任務是加快對資料的存取速度，沒有任何限制。建立普通索引時，通常使用的關鍵字是 INDEX 或 KEY。

2) 唯一性索引

唯一性索引是不允許索引列具有相同索引值的索引。如果能確定某個資料列只包含彼此各不相同的值，在為這個資料列建立索引的時候就應該用關鍵字 UNIQUE 把它定義為一個唯一性索引。

建立唯一性索引的目的往往不是為了提高存取速度，而是為了避免資料出現重複。

3) 主鍵索引

主鍵索引是一種唯一性索引，即不允許值重複或者值為空，並且每個表只能有一個主鍵。主鍵可以在建立表的時候指定，也可以通過修改表的方式新增，必須指定關鍵字 PRIMARY KEY。

注意：主鍵是資料庫考察的重點。注意每個表只能有一個主鍵。

4) 空間索引

空間索引主要用於地理空間資料型別 GEOMETRY。

5) 全文索引

全文索引只能在 VARCHAR 或 TEXT 型別的列上建立，並且只能在 MyISAM 表中建立。

索引在邏輯上分為以上 5 類，但在實際使用中，索引通常被建立成單列索引和組合索引。
  • 單列索引就是索引只包含原表的一個列。
  • 組合索引也稱為複合索引或多列索引，相對於單列索引來說，組合索引是將原表的多個列共同組成一個索引。

提示：一個表可以有多個單列索引，但這些索引不是組合索引。一個組合索引實質上為表的查詢提供了多個索引，以此來加快查詢速度。比如，在一個表中建立了一個組合索引(c1，c2，c3)，在實際查詢中，系統用來實際加速的索引有三個：單個索引(c1)、雙列索引(c1，c2)和多列索引(c1，c2，c3)。

為了提高索引的應用效能，MySQL中的索引可以根據具體應用採用不同的索引策略。這些索引策略所對應的索引型別有聚集索引、次要索引、覆蓋索引、複合索引、字首索引、唯一索引等。

索引的使用原則和注意事項

雖然索引可以加快查詢速度，提高 MySQL 的處理效能，但是過多地使用索引也會造成以下弊端：
  • 建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加。
  • 除了資料表占資料空間之外，每一個索引還要佔一定的物理空間。如果要建立聚簇索引，那麼需要的空間就會更大。
  • 當對錶中的資料進行增加、刪除和修改的時候，索引也要動態地維護，這樣就降低了資料的維護速度。

注意：索引可以在一些情況下加速查詢，但是在某些情況下，會降低效率。

索引只是提高效率的一個因素，因此在建立索引的時候應該遵循以下原則：
  • 在經常需要搜尋的列上建立索引，可以加快搜尋的速度。
  • 在作為主鍵的列上建立索引，強制該列的唯一性，並組織表中資料的排列結構。
  • 在經常使用表連線的列上建立索引，這些列主要是一些外來鍵，可以加快表連線的速度。
  • 在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，所以其指定的範圍是連續的。
  • 在經常需要排序的列上建立索引，因為索引已經排序，所以查詢時可以利用索引的排序，加快排序查詢。
  • 在經常使用 WHERE 子句的列上建立索引，加快條件的判斷速度。

與此對應，在某些應用場合下建立索引不能提高 MySQL 的工作效率，甚至在一定程度上還帶來負面效應，降低了資料庫的工作效率，一般來說不適合建立索引的環境如下：
  • 對於那些在查詢中很少使用或參考的列不應該建立索引。因為這些列很少使用到，所以有索引或者無索引並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度，並增大了空間要求。
  • 對於那些只有很少資料值的列也不應該建立索引。因為這些列的取值很少，例如人事表的性別列。查詢結果集的資料行占了表中資料行的很大比例，增加索引並不能明顯加快檢索速度。
  • 對於那些定義為 TEXT、IMAGE 和 BIT 資料型別的列不應該建立索引。因為這些列的資料量要麼相當大，要麼取值很少。
  • 當修改效能遠遠大於檢索效能時，不應該建立索引。因為修改效能和檢索效能是互相矛盾的。當建立索引時，會提高檢索效能，降低修改效能。當減少索引時，會提高修改效能，降低檢索效能。因此，當修改效能遠遠大於檢索效能時，不應該建立索引。