一.索引簡介

眾所周知，索引是關(guān)系型數(shù)據(jù)庫中給數(shù)據(jù)庫表中一列或多列的值排序后的存儲結(jié)構(gòu)，SQL的主流索引結(jié)構(gòu)有B+樹以及Hash結(jié)構(gòu)，聚集索引以及非聚集索引用的是B+樹索引。這篇文章會總結(jié)SQL Server以及MySQL的InnoDB和MyISAM兩種SQL的索引。

SQL Sever索引類型有：唯一索引，主鍵索引，聚集索引，非聚集索引。

MySQL 索引類型有：唯一索引，主鍵（聚集）索引，非聚集索引，全文索引。

二.聚集索引

聚集（clustered）索引，也叫聚簇索引。

定義：數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個表中只能擁有一個聚集索引。

單單從定義來看是不是顯得有點抽象，打個比方，一個表就像是我們以前用的新華字典，聚集索引就像是拼音目錄，而每個字存放的頁碼就是我們的數(shù)據(jù)物理地址，我們?nèi)绻樵円粋€“哇”字，我們只需要查詢“哇”字對應(yīng)在新華字典拼音目錄對應(yīng)的頁碼，就可以查詢到對應(yīng)的“哇”字所在的位置，而拼音目錄對應(yīng)的A-Z的字順序，和新華字典實際存儲的字的順序A-Z也是一樣的，如果我們中文新出了一個字，拼音開頭第一個是B，那么他插入的時候也要按照拼音目錄順序插入到A字的后面，現(xiàn)在用一個簡單的示意圖來大概說明一下在數(shù)據(jù)庫中的樣子：

地址idusernamescore

0x01	1	小明	90
0x02	2	小紅	80
0x03	3	小華	92
..	..	..	..
0xff	256	小英	70

注：第一列的地址表示該行數(shù)據(jù)在磁盤中的物理地址，后面三列才是我們SQL里面用的表里的列，其中id是主鍵，建立了聚集索引。

結(jié)合上面的表格就可以理解這句話了吧：數(shù)據(jù)行的物理順序與列值的順序相同，如果我們查詢id比較靠后的數(shù)據(jù)，那么這行數(shù)據(jù)的地址在磁盤中的物理地址也會比較靠后。而且由于物理排列方式與聚集索引的順序相同，所以也就只能建立一個聚集索引了。

聚集索引實際存放的示意圖

從上圖可以看出聚集索引的好處了，索引的葉子節(jié)點就是對應(yīng)的數(shù)據(jù)節(jié)點（MySQL的MyISAM除外，此存儲引擎的聚集索引和非聚集索引只多了個唯一約束，其他沒什么區(qū)別），可以直接獲取到對應(yīng)的全部列的數(shù)據(jù)，而非聚集索引在索引沒有覆蓋到對應(yīng)的列的時候需要進行二次查詢，后面會詳細講。因此在查詢方面，聚集索引的速度往往會更占優(yōu)勢。

創(chuàng)建聚集索引

如果不創(chuàng)建索引，系統(tǒng)會自動創(chuàng)建一個隱含列作為表的聚集索引。

1.創(chuàng)建表的時候指定主鍵（注意：SQL Sever默認主鍵為聚集索引，也可以指定為非聚集索引，而MySQL里主鍵就是聚集索引）

create table t1(id int primary key,name nvarchar(255) )

2.創(chuàng)建表后添加聚集索引

SQL Server

create clustered index clustered_index on table_name(colum_name)

MySQL

alter table table_name add primary key(colum_name)

值得注意的是，最好還是在創(chuàng)建表的時候添加聚集索引，由于聚集索引的物理順序上的特殊性，因此如果再在上面創(chuàng)建索引的時候會根據(jù)索引列的排序移動全部數(shù)據(jù)行上面的順序，會非常地耗費時間以及性能。

三.非聚集索引

非聚集（unclustered）索引。

定義：該索引中索引的邏輯順序與磁盤上行的物理存儲順序不同，一個表中可以擁有多個非聚集索引。

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

非聚集索引實際存放的示意圖

非聚集索引的二次查詢問題

非聚集索引葉節(jié)點仍然是索引節(jié)點，只是有一個指針指向?qū)?yīng)的數(shù)據(jù)塊，此如果使用非聚集索引查詢，而查詢列中包含了其他該索引沒有覆蓋的列，那么他還要進行第二次的查詢，查詢節(jié)點上對應(yīng)的數(shù)據(jù)行的數(shù)據(jù)。

如有以下表t1：

idusernamescore

1	小明	90
2	小紅	80
3	小華	92
..	..	..
256	小英	70

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

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

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

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

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

在SQL Server里面查詢效率如下所示，Index Seek就是索引所花費的時間，Key Lookup就是二次查詢所花費的時間。可以看的出二次查詢所花費的查詢開銷占比很大，達到50%。

在SQL Server里面會對查詢自動優(yōu)化，選擇適合的索引，因此如果在數(shù)據(jù)量不大的情況下，SQL Server很有可能不會使用非聚集索引進行查詢，而是使用聚集索引進行查詢，即便需要掃描整個聚集索引，效率也比使用非聚集索引效率要高。

本人試過在含有30w行表上建立非聚集索引，查詢非聚集索引覆蓋以外的列就會變成聚集索引的全索引掃描（index scan）查詢來避免二次查詢，而在另外一張200w行表才會用到非聚集索引seek對應(yīng)的列再進行kek lookup，有關(guān)于SQL Server的有Index seek，index scan, table scan，key LookUp這幾個概念，可以查看這個blog，描寫比較詳細。

但在MySQL里面就算表里數(shù)據(jù)量少且查詢了非鍵列，也不會使用聚集索引去全索引掃描，但如果強制使用聚集索引去查詢，性能反而比非聚集索引查詢要差，這就是兩種SQL的不同之處。

還有一點要注意的是非聚集索引其實葉子節(jié)點除了會存儲索引覆蓋列的數(shù)據(jù)，也會存放聚集索引所覆蓋的列數(shù)據(jù)。

如何解決非聚集索引的二次查詢問題

復合索引（覆蓋索引）

建立兩列以上的索引，即可查詢復合索引里的列的數(shù)據(jù)而不需要進行回表二次查詢，如index(col1, col2)，執(zhí)行下面的語句

select col1, col2 from t1 where col1 = '213';

要注意使用復合索引需要滿足最左側(cè)索引的原則，也就是查詢的時候如果where條件里面沒有最左邊的一到多列，索引就不會起作用。

在SQL Server中還有include的用法，可以把非聚集索引里包含的列包含進來，而不一定需要建立復合索引。

四.總結(jié)與使用心得

使用聚集索引的查詢效率要比非聚集索引的效率要高，但是如果需要頻繁去改變聚集索引的值，寫入性能并不高，因為需要移動對應(yīng)數(shù)據(jù)的物理位置。

非聚集索引在查詢的時候可以的話就避免二次查詢，這樣性能會大幅提升。

不是所有的表都適合建立索引，只有數(shù)據(jù)量大表才適合建立索引，且建立在選擇性高的列上面性能會更好。

另附本人博客地址

參考資料：

[1]：微軟技術(shù)支持官方博客
[2]：聚集索引和非聚集索引（整理）

轉(zhuǎn)載于:https://www.cnblogs.com/s-b-b/p/8334593.html

總結(jié)

以上是生活随笔為你收集整理的聚集索引与非聚集索引的总结的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。