技術文章 » mysql有哪些索引型別

mysql有哪些索引型別

2022-06-27 18:00:42

索引型別有：1、B-樹索引，使表中的每一行都會在索引上有一個對應值；2、雜湊索引，可根據索引列對應的雜湊值的方法獲取表的記錄行；3、普通索引，允許在定義索引的列中插入重複值和空值；4、唯一索引，可以避免資料出現重複；5、主鍵索引，是為主鍵欄位建立的索引；6、空間索引，是對空間資料型別的欄位建立的索引；7、全文索引，用來查詢文字中的關鍵字；8、單列索引，即索引只包含原表的一個列。

本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。

MySQL索引型別詳解

索引的型別和儲存引擎有關，每種儲存引擎所支援的索引型別不一定完全相同。MySQL 索引可以從儲存方式、邏輯角度和實際使用的角度來進行分類。

儲存方式區分

根據儲存方式的不同，MySQL 中常用的索引在物理上分為 B-樹索引和 HASH 索引兩類，兩種不同型別的索引各有其不同的適用範圍。

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) 普通索引

普通索引是 MySQL 中最基本的索引型別，它沒有任何限制，唯一任務就是加快系統對資料的存取速度。

普通索引允許在定義索引的列中插入重複值和空值。

建立普通索引時，通常使用的關鍵字是 INDEX 或 KEY。

例 1

下面在 tb_student 表中的 id 欄位上建立名為 index_id 的索引。

CREATE INDEX index_id ON tb_student(id);

2) 唯一索引

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

唯一索引列的值必須唯一，允許有空值。如果是組合索引，則列值的組合必須唯一。

建立唯一索引通常使用 UNIQUE 關鍵字。

例 2

下面在 tb_student 表中的 id 欄位上建立名為 index_id 的索引，SQL 語句如下：

CREATE UNIQUE INDEX index_id ON tb_student(id);

其中，id 欄位可以有唯一性約束，也可以沒有。

3) 主鍵索引

顧名思義，主鍵索引就是專門為主鍵欄位建立的索引，也屬於索引的一種。

主鍵索引是一種特殊的唯一索引，不允許值重複或者值為空。

建立主鍵索引通常使用 PRIMARY KEY 關鍵字。不能使用 CREATE INDEX 語句建立主鍵索引。

4) 空間索引

空間索引是對空間資料型別的欄位建立的索引，使用 SPATIAL 關鍵字進行擴充套件。

建立空間索引的列必須將其宣告為 NOT NULL，空間索引只能在儲存引擎為 MyISAM 的表中建立。

空間索引主要用於地理空間資料型別 GEOMETRY。對於初學者來說，這類索引很少會用到。

例 3

下面在 tb_student 表中的 line 欄位上建立名為 index_line 的索引，SQL 語句如下：

CREATE SPATIAL INDEX index_line ON tb_student(line);

其中，tb_student 表的儲存引擎必須是 MyISAM，line 欄位必須為空間資料型別，而且是非空的。

5) 全文索引

全文索引主要用來查詢文字中的關鍵字，只能在 CHAR、VARCHAR 或 TEXT 型別的列上建立。在 MySQL 中只有 MyISAM 儲存引擎支援全文索引。

全文索引允許在索引列中插入重複值和空值。

不過對於大容量的資料表，生成全文索引非常消耗時間和硬碟空間。

建立全文索引使用 FULLTEXT 關鍵字。

例 4

在 tb_student 表中的 info 欄位上建立名為 index_info 的全文索引，SQL 語句如下：

CREATE FULLTEXT INDEX index_info ON tb_student(info);

其中，index_info 的儲存引擎必須是 MyISAM，info 欄位必須是 CHAR、VARCHAR 和 TEXT 等型別。

實際使用區分

索引在邏輯上分為以上 5 類，但在實際使用中，索引通常被建立成單列索引和組合索引。

1）單列索引

單列索引就是索引只包含原表的一個列。在表中的單個欄位上建立索引，單列索引只根據該欄位進行索引。

單列索引可以是普通索引，也可以是唯一性索引，還可以是全文索引。只要保證該索引只對應一個欄位即可。

例 5

下面在 tb_student 表中的 address 欄位上建立名為 index_addr 的單列索引，address 欄位的資料型別為 VARCHAR(20)，索引的資料型別為 CHAR(4)。SQL 語句如下：

CREATE INDEX index_addr ON tb_student(address(4));

這樣，查詢時可以只查詢 address 欄位的前 4 個字元，而不需要全部查詢。

2）多列索引

組合索引也稱為複合索引或多列索引。相對於單列索引來說，組合索引是將原表的多個列共同組成一個索引。多列索引是在表的多個欄位上建立一個索引。該索引指向建立時對應的多個欄位，可以通過這幾個欄位進行查詢。但是，只有查詢條件中使用了這些欄位中第一個欄位時，索引才會被使用。

例如，在表中的 id、name 和 sex 欄位上建立一個多列索引，那麼，只有查詢條件使用了 id 欄位時，該索引才會被使用。

例 6

下面在 tb_student 表中的 name 和 address 欄位上建立名為 index_na 的索引，SQL 語句如下：

CREATE INDEX index_na ON tb_student(name,address);

該索引建立好了以後，查詢條件中必須有 name 欄位才能使用索引。

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

【相關推薦：】

以上就是mysql有哪些索引型別的詳細內容，更多請關注TW511.COM其它相關文章！