聚集索引與非聚集索引的總結

一.索引簡介

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

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

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

二.聚集索引

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

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

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

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

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

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

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

創建聚集索引

如果不創建索引，系統會自動創建一個隱含列作為表的聚集索引。

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

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

2.創建表后添加聚集索引

SQL Server

create?clustered?index?clustered_index?on?table_name(colum_name)

MySQL

alter?table?table_name?add?primary?key(colum_name)

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

三.非聚集索引

非聚集（unclustered）索引。

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

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

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

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

非聚集索引葉節點仍然是索引節點，只是有一個指針指向對應的數據塊，此如果使用非聚集索引查詢，而查詢列中包含了其他該索引沒有覆蓋的列，那么他還要進行第二次的查詢，查詢節點上對應的數據行的數據。

如有以下表t1：

以及聚集索引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?=?'小明'

在SQL Server里面查詢效率如下所示，Index Seek就是索引所花費的時間，Key Lookup就是二次查詢所花費的時間?？梢钥吹某龆尾樵兯ㄙM的查詢開銷占比很大，達到50%。

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

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

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

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

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

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

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

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

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

四.總結與使用心得

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

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

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

另附本人博客地址

參考資料：

[1]：微軟技術支持官方博客

[2]：聚集索引和非聚集索引（整理）

MySQL SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。