一.按照功能劃分

按照功能劃分的話，索引分為四種：

1.普通索引

2.唯一索引

3.主鍵索引

4.全文索引

1.1 普通索引

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

普通索引 ：普通索引就是最基礎(chǔ)的索引，這種索引沒有任何的約束作用，它存在的主要意義就是提高查詢效率。

在上述表中： name字段就是一個(gè)普通索引。

1.2 唯一性索引

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

唯一性索引 : 在 普通索引的基礎(chǔ)上增加了數(shù)據(jù)唯一性的約束 。 一個(gè)表中可以有多個(gè)。

在上述表中 ：name字段就是唯一性索引。

1.3 主鍵索引

主鍵索引則是在唯一性索引的基礎(chǔ)上又增加了不為空的約束，則 NOT NULL+UNIQUE ,一張表中最多只有一個(gè)主鍵索引，但是一個(gè)主鍵索引中可以包含多個(gè)字段。

1.4 全文索引

一般很少用到。

二.按照功能劃分

聚集索引

聚集索引 在存儲(chǔ)的時(shí)候，可以按照主鍵（不是必須，看情況）來排序存儲(chǔ)數(shù)據(jù)，B+Tree 的葉子結(jié)點(diǎn)就是完整的數(shù)據(jù)行，查找的時(shí)候，找到了主鍵也就找到了完整的數(shù)據(jù)行。

在聚集索引里，表中數(shù)據(jù)行按索引的排序方式進(jìn)行存儲(chǔ)，對(duì)查找行很有效。

只有當(dāng)表包含聚集索引時(shí)，表內(nèi)的數(shù)據(jù)行才會(huì)按找索引列的值在磁盤上進(jìn)行物理排序和存儲(chǔ)。

每張表只能有一個(gè)聚集索引，原因很簡單，因?yàn)閿?shù)據(jù)行本身只能按一個(gè)順序存儲(chǔ)。

當(dāng)我們基于 InnoDB 引擎創(chuàng)建一張表的時(shí)候，都會(huì)創(chuàng)建一個(gè)聚集索引，每張表都有唯一的聚集索引：

1. 如果這張表定義了主鍵索引，那么這個(gè)主鍵索引就作為聚集索引。

2. 如果這張表沒有定義主鍵索引，那么該表的第一個(gè)唯一非空索引作為聚集索引。

3. 如果這張表也沒有唯一非空索引，那么 InnoDB 內(nèi)部會(huì)生成一個(gè)隱藏的主鍵

作為聚集索引，這個(gè)隱藏的主鍵是一個(gè) 6 個(gè)字節(jié)的列，該列的值會(huì)隨著數(shù)據(jù)的插入自增。

非聚集索引

非聚集索引我們一般也稱為 二級(jí)索引或者輔助索引 ，對(duì)于非聚集索引，數(shù)據(jù)庫會(huì) 有單獨(dú)的存儲(chǔ)空間來存放 。

非聚集索引在查找的時(shí)候要經(jīng)過兩個(gè)步驟，例如執(zhí)行select * from user where username=‘aaa’ （假設(shè) username 字段是非聚集索引）。

1.先搜索 username 這一列索引的 B+Tree，這個(gè) B+Tree 的葉子結(jié)點(diǎn)存儲(chǔ)的不是完整的數(shù)據(jù)行，而是主鍵值。

2.拿著主鍵值再去搜索主鍵索引的 B+Tree，就可以獲取到一行完整的數(shù)據(jù)。

所以如果我們?cè)诓樵冎杏玫搅朔蔷奂饕?，那么就?huì)搜索兩棵 B+Tree，第一次搜索 B+Tree 拿到主鍵值后再去搜索聚集索引的 B+Tree，這個(gè)過程就是所謂的 回表 。

一張表只能有一個(gè)聚集索引，但可以有多個(gè)非聚集索引。

使用聚集索引的時(shí)候，數(shù)據(jù)的查詢效率高，但如果對(duì)數(shù)據(jù)進(jìn)行插入，刪除，更新等操作，效率會(huì)比非聚集索引低。

最新評(píng)論