Mysql中聚簇索引和非聚簇索引的區(qū)別詳解

更新時間：2023年07月07日 11:19:11 作者：向程序猿進化

這篇文章主要介紹了Mysql中聚簇索引和非聚簇索引的區(qū)別詳解,聚簇索引就是按照每張表的主鍵構造一顆B+樹，同時葉子節(jié)點中存放的就是整張表的行記錄數(shù)據(jù)，也將聚集索引的葉子節(jié)點稱為數(shù)據(jù)頁,需要的朋友可以參考下

聚簇索引(聚集索引)

聚簇索引并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式。具體細節(jié)依賴于其實現(xiàn)方式。

MySQL數(shù)據(jù)庫中innodb存儲引擎，B+樹索引可以分為聚簇索引（也稱聚集索引，clustered index）和輔助索引（有時也稱非聚簇索引或二級索引，secondary index，non-clustered index）。

這兩種索引內(nèi)部都是B+樹，聚集索引的葉子節(jié)點存放著一整行的數(shù)據(jù)。

Innobd中的主鍵索引是一種聚簇索引，非聚簇索引都是輔助索引，像復合索引、前綴索引、唯一索引。

Innodb使用的是聚簇索引，MyISam使用的是非聚簇索引

聚簇索引就是按照每張表的主鍵構造一顆B+樹，同時葉子節(jié)點中存放的就是整張表的行記錄數(shù)據(jù)，也將聚集索引的葉子節(jié)點稱為數(shù)據(jù)頁。這個特性決定了索引組織表中數(shù)據(jù)也是索引的一部分，每張表只能擁有一個聚簇索引。

Innodb通過主鍵聚集數(shù)據(jù)，如果沒有定義主鍵，innodb會選擇非空的唯一索引代替。如果沒有這樣的索引，innodb會隱式的定義一個主鍵來作為聚簇索引。

聚簇索引的優(yōu)缺點

優(yōu)點：

數(shù)據(jù)訪問更快，因為聚簇索引將索引和數(shù)據(jù)保存在同一個B+樹中，因此從聚簇索引中獲取數(shù)據(jù)比非聚簇索引更快
- 聚簇索引對于主鍵的排序查找和范圍查找速度非常快

缺點：

插入速度嚴重依賴于插入順序，按照主鍵的順序插入是最快的方式，否則將會出現(xiàn)頁分裂，嚴重影響性能。因此，對于InnoDB表，我們一般都會定義一個自增的ID列為主鍵
更新主鍵的代價很高，因為將會導致被更新的行移動。因此，對于InnoDB表，我們一般定義主鍵為不可更新。
二級索引訪問需要兩次索引查找，第一次找到主鍵值，第二次根據(jù)主鍵值找到行數(shù)據(jù)。

輔助索引（非聚簇索引）

在聚簇索引之上創(chuàng)建的索引稱之為輔助索引，輔助索引訪問數(shù)據(jù)總是需要二次查找。輔助索引葉子節(jié)點存儲的不再是行的物理位置，而是主鍵值。通過輔助索引首先找到的是主鍵值，再通過主鍵值找到數(shù)據(jù)行的數(shù)據(jù)頁，再通過數(shù)據(jù)頁中的Page Directory找到數(shù)據(jù)行。

Innodb輔助索引的葉子節(jié)點并不包含行記錄的全部數(shù)據(jù)，葉子節(jié)點除了包含鍵值外，還包含了相應行數(shù)據(jù)的聚簇索引鍵。

輔助索引的存在不影響數(shù)據(jù)在聚簇索引中的組織，所以一張表可以有多個輔助索引。在innodb中有時也稱輔助索引為二級索引。

InnoDB索引實現(xiàn)

InnoDB也使用B+Tree作為索引結構，但具體實現(xiàn)方式卻與MyISAM截然不同.

1）主鍵索引：

MyISAM索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址。而在InnoDB中，表數(shù)據(jù)文件本身就是按B+Tree組織的一個索引結構，這棵樹的葉節(jié)點data域保存了完整的數(shù)據(jù)記錄。這個索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。

(圖inndb主鍵索引）是InnoDB主索引（同時也是數(shù)據(jù)文件）的示意圖，可以看到葉節(jié)點包含了完整的數(shù)據(jù)記錄。這種索引叫做聚集索引。因為InnoDB的數(shù)據(jù)文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統(tǒng)會自動選擇一個可以唯一標識數(shù)據(jù)記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含字段作為主鍵，這個字段長度為6個字節(jié)，類型為長整形。

2）InnoDB的輔助索引

InnoDB的所有輔助索引都引用主鍵作為data域。例如，下圖為定義在Col3上的一個輔助索引：

InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一種非常快速的主鍵查找性能。不過，它的輔助索引（Secondary Index，也就是非主鍵索引）也會包含主鍵列，所以，如果主鍵定義的比較大，其他索引也將很大。如果想在表上定義、很多索引，則爭取盡量把主鍵定義得小一些。InnoDB 不會壓縮索引。

文字符的ASCII碼作為比較準則。聚集索引這種實現(xiàn)方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。

不同存儲引擎的索引實現(xiàn)方式對于正確使用和優(yōu)化索引都非常有幫助，例如知道了InnoDB的索引實現(xiàn)后，就很容易明白1、為什么不建議使用過長的字段作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。再例如，2、用非單調(diào)的字段作為主鍵在InnoDB中不是個好主意，因為InnoDB數(shù)據(jù)文件本身是一顆B+Tree，非單調(diào)的主鍵會造成在插入新記錄時數(shù)據(jù)文件為了維持B+Tree的特性而頻繁的分裂調(diào)整，十分低效，而使用自增字段作為主鍵則是一個很好的選擇。

InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹中，而行數(shù)據(jù)就儲存在葉子節(jié)點上，若使用"where id = 14"這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找到對應的葉節(jié)點，之后獲得行數(shù)據(jù)。若對Name列進行條件搜索，則需要兩個步驟：第一步在輔助索引B+樹中檢索Name，到達其葉子節(jié)點獲取對應的主鍵。第二步使用主鍵在主索引B+樹種再執(zhí)行一次B+樹檢索操作，最終到達葉子節(jié)點即可獲取整行數(shù)據(jù)。

MyISAM索引實現(xiàn)

MyISAM索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址

1）主鍵索引：

MyISAM引擎使用B+Tree作為索引結構，葉節(jié)點的data域存放的是數(shù)據(jù)記錄的地址。下圖是MyISAM主鍵索引的原理圖：

這里設表一共有三列，假設我們以Col1為主鍵，圖myisam1是一個MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件僅僅保存數(shù)據(jù)記錄的地址。

2）輔助索引（Secondary key）

在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區(qū)別，只是主索引要求key是唯一的，而輔助索引的key可以重復。如果我們在Col2上建立一個輔助索引，則此索引的結構如下圖所示：

同樣也是一顆B+Tree，data域保存數(shù)據(jù)記錄的地址。因此，MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址，讀取相應數(shù)據(jù)記錄。

MyISAM的索引方式也叫做“非聚集”的，之所以這么稱呼是為了與InnoDB的聚集索引區(qū)分。

MyISM使用的是非聚簇索引，非聚簇索引的兩棵B+樹看上去沒什么不同，節(jié)點的結構完全一致只是存儲的內(nèi)容不同而已，主鍵索引B+樹的節(jié)點存儲了主鍵，輔助鍵索引B+樹存儲了輔助鍵。表數(shù)據(jù)存儲在獨立的地方，這兩顆B+樹的葉子節(jié)點都使用一個地址指向真正的表數(shù)據(jù)，對于表數(shù)據(jù)來說，這兩個鍵沒有任何差別。由于索引樹是獨立的，通過輔助鍵檢索無需訪問主鍵的索引樹。

為了更形象說明這兩種索引的區(qū)別，我們假想一個表如下圖存儲了4行數(shù)據(jù)。其中Id作為主索引，Name作為輔助索引。圖示清晰的顯示了聚簇索引和非聚簇索引的差異。

問題：主鍵索引是聚集索引還是非聚集索引？

在Innodb下主鍵索引是聚集索引，在Myisam下主鍵索引是非聚集索引