mysql聚集索引和非聚集索引的差別是什麼

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

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

MySQL的Innodb儲存引擎的索引分為聚集索引和非聚集索引兩大類，理解聚集索引和非聚集索引可透過比較漢語字典的索引。漢語字典提供了兩類檢索漢字的方式，第一類是拼音檢索（前提是知道該漢字讀音），比如拼音為cheng的漢字排在拼音chang的漢字後面，根據拼音找到對應漢字的頁碼（因為按拼音排序，二分查找很快就能定位），這就是我們通常所說的字典序；第二類是部首筆畫檢索，根據筆畫找到對應漢字，查到漢字對應的頁碼。拼音檢索就是聚集索引，因為儲存的記錄（資料庫中是行資料、字典中是漢字的詳情記錄）是按照該索引排序的；筆畫索引，雖然筆畫相同的字在筆畫索引中相鄰，但是實際儲存頁碼卻不相鄰，這是非聚集索引。

聚集索引

索引中鍵值的邏輯順序決定了表中對應行的物理順序。

聚集索引決定表中資料的物理順序。聚集索引類似於電話簿，後者按姓氏排列資料。 聚集索引對於那些經常要搜尋範圍值的資料列特別有效。使用聚集索引找到包含第一個值的行後，便可以確保包含後續索引值的行在物理相鄰。例如，如果應用程式執行 的一個查詢經常檢索某一日期範圍​​內的記錄，則使用聚集索引可以迅速找到包含開始日期的行，然後檢索表中所有相鄰的行，直到到達結束日期。這樣有助於提高此 類查詢的效能。同樣，如果對從表中檢索的資料進行排序時經常要用到某一列，則可以將該表在該列上聚集（物理排序），避免每次查詢該列時都進行排序，從而節 省成本。

以上是innodb的b tree索引結構

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

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

b tree的特色是：

1、所有的非葉子節點只儲存關鍵字資訊。
2、所有衛星資料（具體資料）都存在葉子結點中。
3、所有的葉子結點中包含了全部元素的資訊。
4、所有葉子節點之間都有一個鏈指標。

我們發現，b trre有以下特性：

• 對一個範圍內的查詢特別有效快速（透過葉子的鏈指標）；
• #對具體的key值查詢只比b-tree低效一點（因為要到葉子一級），但也可以忽略；

#非聚集索引

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

　　其實依照定義，除了聚集索引以外的索引都是非聚集索引，只是人們想細分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引類比成現實生活中的東西，那麼非聚集索引就像新華字典的偏旁字典，他結構順序與實際存放順序不一定一致。

非聚集索引的儲存結構與前面是一樣的，不同的是在葉子結點的數據部分存的不再是具體的數據，而數據的聚集索引的key。所以透過非聚集索引查找的過程是先找到該索引key對應的聚集索引的key，然後再拿聚集索引的key到主鍵索引樹上查找對應的數據，這個過程稱為回表 ！

舉例說明下：

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)。

使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

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

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score：

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

聚集索引和非聚集索引区别

区别一：

聚集索引：就是以主键创建的索引，在叶子节点存储的是表中的数据

非聚集索引：就是以非主键创建的索引(也叫做二级索引)，在叶子节点存储的是主键和索引列。

区别二：

聚集索引中表记录的排列顺序和索引的排列顺序一致；所以查询效率快，因为只要找到第一个索引值记录，其余的连续性的记录在物理表中也会连续存放，一起就可以查询到。缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。

非聚集索引中表记录的排列顺序和索引的排列顺序不一致。

区别三：

聚集索引是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储不连续。

区别四：

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

【相关推荐：mysql视频教程】

以上是mysql聚集索引和非聚集索引的差別是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！