MySQL中索引的分類詳解
一.按照功能劃分
按照功能劃分的話,索引分為四種:
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ǔ),對查找行很有效。
只有當(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ù)的插入自增。
非聚集索引
非聚集索引我們一般也稱為 二級索引或者輔助索引 ,對于非聚集索引,數(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ù)。
所以如果我們在查詢中用到了非聚集索引,那么就會(huì)搜索兩棵 B+Tree,第一次搜索 B+Tree 拿到主鍵值后再去搜索聚集索引的 B+Tree,這個(gè)過程就是所謂的 回表 。
一張表只能有一個(gè)聚集索引,但可以有多個(gè)非聚集索引。
使用聚集索引的時(shí)候,數(shù)據(jù)的查詢效率高,但如果對數(shù)據(jù)進(jìn)行插入,刪除,更新等操作,效率會(huì)比非聚集索引低。
相關(guān)文章
mysql實(shí)現(xiàn)事務(wù)的提交和回滾實(shí)例
這篇文章主要介紹了mysql實(shí)現(xiàn)事務(wù)的提交和回滾,需要的朋友可以參考下2014-06-06mysql 8.0.15 版本安裝教程 連接Navicat.list
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.15 版本安裝教程,連接Navicat.list,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2019-08-08

MySql存儲(chǔ)過程和游標(biāo)的使用實(shí)例

DBeaver如何將mysql表結(jié)構(gòu)以表格形式導(dǎo)出