mysql中聚集索引和非聚集索引有哪些區(qū)別

這篇文章主要介紹mysql中聚集索引和非聚集索引有哪些區(qū)別，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！

區(qū)別：1、聚集索引在葉子節(jié)點(diǎn)存儲的是表中的數(shù)據(jù)，而非聚集索引在葉子節(jié)點(diǎn)存儲的是主鍵和索引列；2、聚集索引中表記錄的排列順序和索引的排列順序一致，而非聚集索引的排列順序不一致；3、聚集索引每張表只能有一個，而非聚集索引可以有多個。

本教程操作環(huán)境：windows7系統(tǒng)、mysql8版本、Dell G3電腦。

MySQL的Innodb存儲引擎的索引分為聚集索引和非聚集索引兩大類，理解聚集索引和非聚集索引可通過對比漢語字典的索引。漢語字典提供了兩類檢索漢字的方式，第一類是拼音檢索（前提是知道該漢字讀音），比如拼音為cheng的漢字排在拼音chang的漢字后面，根據(jù)拼音找到對應(yīng)漢字的頁碼（因?yàn)榘雌匆襞判颍植檎液芸炀湍芏ㄎ唬?，這就是我們通常所說的字典序；第二類是部首筆畫檢索，根據(jù)筆畫找到對應(yīng)漢字，查到漢字對應(yīng)的頁碼。拼音檢索就是聚集索引，因?yàn)榇鎯Φ挠涗洠〝?shù)據(jù)庫中是行數(shù)據(jù)、字典中是漢字的詳情記錄）是按照該索引排序的；筆畫索引，雖然筆畫相同的字在筆畫索引中相鄰，但是實(shí)際存儲頁碼卻不相鄰，這是非聚集索引。

聚集索引

索引中鍵值的邏輯順序決定了表中相應(yīng)行的物理順序。

聚集索引確定表中數(shù)據(jù)的物理順序。聚集索引類似于電話簿，后者按姓氏排列數(shù)據(jù)。 聚集索引對于那些經(jīng)常要搜索范圍值的列特別有效。使用聚集索引找到包含第一個值的行后，便可以確保包含后續(xù)索引值的行在物理相鄰。例如，如果應(yīng)用程序執(zhí)行 的一個查詢經(jīng)常檢索某一日期范圍內(nèi)的記錄，則使用聚集索引可以迅速找到包含開始日期的行，然后檢索表中所有相鄰的行，直到到達(dá)結(jié)束日期。這樣有助于提高此 類查詢的性能。同樣，如果對從表中檢索的數(shù)據(jù)進(jìn)行排序時經(jīng)常要用到某一列，則可以將該表在該列上聚集（物理排序），避免每次查詢該列時都進(jìn)行排序，從而節(jié) 省成本。

以上是innodb的b+tree索引結(jié)構(gòu)

我們知道b+tree是從b-tree演變而來，一棵m階的B-Tree有如下特性：

1、每個結(jié)點(diǎn)最多m個子結(jié)點(diǎn)。
2、除了根結(jié)點(diǎn)和葉子結(jié)點(diǎn)外，每個結(jié)點(diǎn)最少有m/2（向上取整）個子結(jié)點(diǎn)。
3、如果根結(jié)點(diǎn)不是葉子結(jié)點(diǎn)，那根結(jié)點(diǎn)至少包含兩個子結(jié)點(diǎn)。
4、所有的葉子結(jié)點(diǎn)都位于同一層。
5、每個結(jié)點(diǎn)都包含k個元素（關(guān)鍵字），這里m/2≤k<m，這里m/2向下取整。
6、每個節(jié)點(diǎn)中的元素（關(guān)鍵字）從小到大排列。
7、每個元素（關(guān)鍵字）字左結(jié)點(diǎn)的值，都小于或等于該元素（關(guān)鍵字）。右結(jié)點(diǎn)的值都大于或等于該元素（關(guān)鍵字）。

b+tree的特點(diǎn)是：

1、所有的非葉子節(jié)點(diǎn)只存儲關(guān)鍵字信息。
2、所有衛(wèi)星數(shù)據(jù)（具體數(shù)據(jù)）都存在葉子結(jié)點(diǎn)中。
3、所有的葉子結(jié)點(diǎn)中包含了全部元素的信息。
4、所有葉子節(jié)點(diǎn)之間都有一個鏈指針。

我們發(fā)現(xiàn)，b+trre有以下特性：

• 對一個范圍內(nèi)的查詢特別有效快速（通過葉子的鏈指針）；

• 對具體的key值查詢僅僅比b-tree低效一點(diǎn)（因?yàn)橐饺~子一級），但也可以忽略；

非聚集索引

　　索引中索引的邏輯順序與磁盤上行的物理存儲順序不同。

　　其實(shí)按照定義，除了聚集索引以外的索引都是非聚集索引，只是人們想細(xì)分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引類比成現(xiàn)實(shí)生活中的東西，那么非聚集索引就像新華字典的偏旁字典，他結(jié)構(gòu)順序與實(shí)際存放順序不一定一致。

非聚集索引的存儲結(jié)構(gòu)與前面是一樣的，不同的是在葉子結(jié)點(diǎn)的數(shù)據(jù)部分存的不再是具體的數(shù)據(jù)，而數(shù)據(jù)的聚集索引的key。所以通過非聚集索引查找的過程是先找到該索引key對應(yīng)的聚集索引的key，然后再拿聚集索引的key到主鍵索引樹上查找對應(yīng)的數(shù)據(jù)，這個過程稱為回表！

舉個例子說明下：

create table student (

`id` INT UNSIGNED AUTO_INCREMENT,

`username` VARCHAR(255),
`score` INT,
PRIMARY KEY(`id`), KEY(`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

聚集索引clustered index(id), 非聚集索引index(username)。

使用以下語句進(jìn)行查詢，不需要進(jìn)行二次查詢，直接就可以從非聚集索引的節(jié)點(diǎn)里面就可以獲取到查詢列的數(shù)據(jù)。

select id, username from t1 where username = '小明'
select username from t1 where username = '小明'

但是使用以下語句進(jìn)行查詢，就需要二次的查詢?nèi)カ@取原數(shù)據(jù)行的score：

select username, score from t1 where username = '小明'

聚集索引和非聚集索引區(qū)別

區(qū)別一：

聚集索引：就是以主鍵創(chuàng)建的索引，在葉子節(jié)點(diǎn)存儲的是表中的數(shù)據(jù)

非聚集索引：就是以非主鍵創(chuàng)建的索引(也叫做二級索引)，在葉子節(jié)點(diǎn)存儲的是主鍵和索引列。

區(qū)別二：

聚集索引中表記錄的排列順序和索引的排列順序一致；所以查詢效率快，因?yàn)橹灰业降谝粋€索引值記錄，其余的連續(xù)性的記錄在物理表中也會連續(xù)存放，一起就可以查詢到。缺點(diǎn)：新增比較慢，因?yàn)闉榱吮ＷC表中記錄的物理順序和索引順序一致，在記錄插入的時候，會對數(shù)據(jù)頁重新排序。

非聚集索引中表記錄的排列順序和索引的排列順序不一致。

區(qū)別三：

聚集索引是物理上連續(xù)存在，而非聚集索引是邏輯上的連續(xù)，物理存儲不連續(xù)。

區(qū)別四：

聚集索引每張表只能有一個，非聚集索引可以有多個。

以上是“mysql中聚集索引和非聚集索引有哪些區(qū)別”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道！