mysql 聚簇索引和非聚簇索引的區(qū)別

聚簇索引和非聚簇索引的區(qū)別：聚簇索引的葉子節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)，而非聚簇索引的葉子節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)，只不過(guò)有指向?qū)?yīng)數(shù)據(jù)塊的指針。

mysql 中不同的數(shù)據(jù)存儲(chǔ)引擎對(duì)聚簇索引的支持不同，我們可以看一下 mysql 中 myisam 和 innodb 兩種引擎的索引結(jié)構(gòu)。

假如原始數(shù)據(jù)如下：

1. myisam 引擎的數(shù)據(jù)存儲(chǔ)方式

myisam 是按列值與行號(hào)來(lái)組織索引的。它的葉子節(jié)點(diǎn)中保存的實(shí)際上是指向存放數(shù)據(jù)的物理塊的指針。從 myisam 存儲(chǔ)的物理文件我們能看出，myisam 引擎的索引文件（.myi）和數(shù)據(jù)文件(.myd)是相互獨(dú)立的。

2. innodb 引擎的數(shù)據(jù)存儲(chǔ)方式

innodb 按聚簇索引的形式存儲(chǔ)數(shù)據(jù)，所以它的數(shù)據(jù)布局有著很大的不同。

聚簇索引中的每個(gè)葉子節(jié)點(diǎn)包含主鍵值、事務(wù)id、回滾指針(rollback pointer用于事務(wù)和mvcc）和余下的列(如col2)。

innodb 的二級(jí)索引與主鍵索引有很大的不同。innodb 的二級(jí)索引的葉子包含主鍵值，而不是行指針(row pointers)，這減小了移動(dòng)數(shù)據(jù)或者數(shù)據(jù)頁(yè)面分裂時(shí)維護(hù)二級(jí)索引的開(kāi)銷，因?yàn)?innodb 不需要更新索引的行指針。其結(jié)構(gòu)大致如下：

innodb和myisam的主鍵索引與二級(jí)索引的對(duì)比：

innodb的的二級(jí)索引的葉子節(jié)點(diǎn)存放的是key字段加主鍵值。因此，通過(guò)二級(jí)索引查詢首先查到是主鍵值，然后innodb再根據(jù)查到的主鍵值通過(guò)主鍵索引找到相應(yīng)的數(shù)據(jù)塊。而myisam的二級(jí)索引葉子節(jié)點(diǎn)存放的還是列值與行號(hào)的組合，葉子節(jié)點(diǎn)中保存的是數(shù)據(jù)的物理地址。所以可以看出myisam的主鍵索引和二級(jí)索引沒(méi)有任何區(qū)別，主鍵索引僅僅只是一個(gè)叫做primary的唯一、非空的索引，且myisam引擎中可以不設(shè)主鍵。

3. 聚簇索引的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：

• 可以把相關(guān)數(shù)據(jù)保存在一起，數(shù)據(jù)訪問(wèn)就更快。
• 聚簇索引將索引和數(shù)據(jù)保存在同一個(gè)b-tree中，因此獲取數(shù)據(jù)比非聚簇索引要更快。
• 使用聚簇索引掃描的查詢可以直接使用頁(yè)節(jié)點(diǎn)中的主鍵值。

缺點(diǎn)：

• 聚簇索引最大程度提高了io密集型應(yīng)用的性能，如果數(shù)據(jù)全部在內(nèi)存中將失去優(yōu)勢(shì)。
• 更新聚簇索引列的代價(jià)很高，因?yàn)闀?huì)強(qiáng)制每個(gè)被更新的行移動(dòng)到新位置。
• 基于聚簇索引的表插入新行或主鍵被更新導(dǎo)致行移動(dòng)時(shí)，可能導(dǎo)致頁(yè)分裂，表會(huì)占用更多磁盤(pán)空間。
• 當(dāng)行稀疏或由于頁(yè)分裂導(dǎo)致數(shù)據(jù)存儲(chǔ)不連續(xù)時(shí)，全表掃描可能很慢。

4. 創(chuàng)建索引示例

建立索引之前選好表對(duì)象，假設(shè)表名為 indextesttable，此表中包含三個(gè)字段 id，name，uniquecode。為了更快的進(jìn)行姓名查詢，我們可以在 name 字段上添加非聚簇索引。

創(chuàng)建索引的格式如下：

create nonclustered index [index_name【索引名稱】] on [table_name【表名稱】]([column_name1【列名稱】],[column_name2【列名稱】],...);

我們給 indextesttable 表的 name 字段添加一個(gè)非聚簇索引：

create nonclustered index indextesttable_index_name on indextesttable(name);

給 indextesttable 表的 uniquecode 字段添加一個(gè)聚簇索引：

create clustered index indextesttable_index_uniquecode on indextesttable(uniquecode)

這是最簡(jiǎn)單最直接的設(shè)置索引的方式，而通常實(shí)際應(yīng)用中，會(huì)有多字段聯(lián)合添加索引的情況，這個(gè)就需要根據(jù)實(shí)際的應(yīng)用查詢場(chǎng)景，以及在 where 條件下最常用的查詢字段。

例如：在 tablex 中你最經(jīng)常查詢的條件：

select name，message 
from tablex 
where 1=1 
and deptid='003523' 
and limitedcondition='somevalue' 

這個(gè)時(shí)候你就可以 添加一個(gè)基于 deptid 和 limitedcondition 兩個(gè)字段的非聚簇索引，以便于加速查詢速度。

create nonclustered index tablex_index_departid_limitedcondition 
on tablex(deptid,limitedcondition);