一.索引簡(jiǎn)介
眾所周知，索引是關(guān)系型數(shù)據(jù)庫中給數(shù)據(jù)庫表中一列或多列的值排序后的存儲(chǔ)結(jié)構(gòu)，SQL的主流索引結(jié)構(gòu)有B+樹以及Hash結(jié)構(gòu)，聚集索引以及非聚集索引用的是B+樹索引。這篇文章會(huì)總結(jié)SQL Server以及MySQL的InnoDB和MyISAM兩種SQL的索引。
SQL Sever索引類型有：唯一索引，主鍵索引，聚集索引，非聚集索引。
MySQL 索引類型有：唯一索引，主鍵（聚集）索引，非聚集索引，全文索引。
二.聚集索引
聚集（clustered）索引，也叫聚簇索引。
定義：數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個(gè)表中只能擁有一個(gè)聚集索引。
單單從定義來看是不是顯得有點(diǎn)抽象，打個(gè)比方，一個(gè)表就像是我們以前用的新華字典，聚集索引就像是拼音目錄，而每個(gè)字存放的頁碼就是我們的數(shù)據(jù)物理地址，我們?nèi)绻樵円粋€(gè)“哇”字，我們只需要查詢“哇”字對(duì)應(yīng)在新華字典拼音目錄對(duì)應(yīng)的頁碼，就可以查詢到對(duì)應(yīng)的“哇”字所在的位置，而拼音目錄對(duì)應(yīng)的A-Z的字順序，和新華字典實(shí)際存儲(chǔ)的字的順序A-Z也是一樣的，如果我們中文新出了一個(gè)字，拼音開頭第一個(gè)是B，那么他插入的時(shí)候也要按照拼音目錄順序插入到A字的后面，現(xiàn)在用一個(gè)簡(jiǎn)單的示意圖來大概說明一下在數(shù)據(jù)庫中的樣子：
地址idusernamescore
0x011小明90
0x022小紅80
0x033小華92
........
0xff256小英70
注：第一列的地址表示該行數(shù)據(jù)在磁盤中的物理地址，后面三列才是我們SQL里面用的表里的列，其中id是主鍵，建立了聚集索引。
結(jié)合上面的表格就可以理解這句話了吧：數(shù)據(jù)行的物理順序與列值的順序相同，如果我們查詢id比較靠后的數(shù)據(jù)，那么這行數(shù)據(jù)的地址在磁盤中的物理地址也會(huì)比較靠后。而且由于物理排列方式與聚集索引的順序相同，所以也就只能建立一個(gè)聚集索引了。
聚集索引實(shí)際存放的示意圖
從上圖可以看出聚集索引的好處了，索引的葉子節(jié)點(diǎn)就是對(duì)應(yīng)的數(shù)據(jù)節(jié)點(diǎn)（MySQL的MyISAM除外，此存儲(chǔ)引擎的聚集索引和非聚集索引只多了個(gè)唯一約束，其他沒什么區(qū)別），可以直接獲取到對(duì)應(yīng)的全部列的數(shù)據(jù)，而非聚集索引在索引沒有覆蓋到對(duì)應(yīng)的列的時(shí)候需要進(jìn)行二次查詢，后面會(huì)詳細(xì)講。因此在查詢方面，聚集索引的速度往往會(huì)更占優(yōu)勢(shì)。
創(chuàng)建聚集索引
如果不創(chuàng)建索引，系統(tǒng)會(huì)自動(dòng)創(chuàng)建一個(gè)隱含列作為表的聚集索引。
1.創(chuàng)建表的時(shí)候指定主鍵（注意：SQL Sever默認(rèn)主鍵為聚集索引，也可以指定為非聚集索引，而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)建表的時(shí)候添加聚集索引，由于聚集索引的物理順序上的特殊性，因此如果再在上面創(chuàng)建索引的時(shí)候會(huì)根據(jù)索引列的排序移動(dòng)全部數(shù)據(jù)行上面的順序，會(huì)非常地耗費(fèi)時(shí)間以及性能。
三.非聚集索引
非聚集（unclustered）索引。
定義：該索引中索引的邏輯順序與磁盤上行的物理存儲(chǔ)順序不同，一個(gè)表中可以擁有多個(gè)非聚集索引。
其實(shí)按照定義，除了聚集索引以外的索引都是非聚集索引，只是人們想細(xì)分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引類比成現(xiàn)實(shí)生活中的東西，那么非聚集索引就像新華字典的偏旁字典，他結(jié)構(gòu)順序與實(shí)際存放順序不一定一致。
非聚集索引實(shí)際存放的示意圖
非聚集索引的二次查詢問題
非聚集索引葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)，只是有一個(gè)指針指向?qū)?yīng)的數(shù)據(jù)塊，此如果使用非聚集索引查詢，而查詢列中包含了其他該索引沒有覆蓋的列，那么他還要進(jìn)行第二次的查詢，查詢節(jié)點(diǎn)上對(duì)應(yīng)的數(shù)據(jù)行的數(shù)據(jù)。
如有以下表t1：
idusernamescore
1小明90
2小紅80
3小華92
......
256小英70
以及聚集索引clustered index(id), 非聚集索引index(username)。
使用以下語句進(jìn)行查詢，不需要進(jìn)行二次查詢，直接就可以從非聚集索引的節(jié)點(diǎn)里面就可以獲取到查詢列的數(shù)據(jù)。
select id, username from t1 where username = '小明'select username from t1 where username = '小明'但是使用以下語句進(jìn)行查詢，就需要二次的查詢?nèi)カ@取原數(shù)據(jù)行的score：
select username, score from t1 where username = '小明'在SQL Server里面查詢效率如下所示，Index Seek就是索引所花費(fèi)的時(shí)間，Key Lookup就是二次查詢所花費(fèi)的時(shí)間。可以看的出二次查詢所花費(fèi)的查詢開銷占比很大，達(dá)到50%。
在SQL Server里面會(huì)對(duì)查詢自動(dòng)優(yōu)化，選擇適合的索引，因此如果在數(shù)據(jù)量不大的情況下，SQL Server很有可能不會(huì)使用非聚集索引進(jìn)行查詢，而是使用聚集索引進(jìn)行查詢，即便需要掃描整個(gè)聚集索引，效率也比使用非聚集索引效率要高。
本人試過在含有30w行表上建立非聚集索引，查詢非聚集索引覆蓋以外的列就會(huì)變成聚集索引的全索引掃描（index scan）查詢來避免二次查詢，而在另外一張200w行表才會(huì)用到非聚集索引seek對(duì)應(yīng)的列再進(jìn)行kek lookup，有關(guān)于SQL Server的有Index seek，index scan, table scan，key LookUp這幾個(gè)概念，可以查看這個(gè)
blog，描寫比較詳細(xì)。
但在MySQL里面就算表里數(shù)據(jù)量少且查詢了非鍵列，也不會(huì)使用聚集索引去全索引掃描，但如果強(qiáng)制使用聚集索引去查詢，性能反而比非聚集索引查詢要差，這就是兩種SQL的不同之處。
還有一點(diǎn)要注意的是非聚集索引其實(shí)葉子節(jié)點(diǎn)除了會(huì)存儲(chǔ)索引覆蓋列的數(shù)據(jù)，也會(huì)存放聚集索引所覆蓋的列數(shù)據(jù)。
如何解決非聚集索引的二次查詢問題
復(fù)合索引（覆蓋索引）
建立兩列以上的索引，即可查詢復(fù)合索引里的列的數(shù)據(jù)而不需要進(jìn)行回表二次查詢，如index(col1, col2)，執(zhí)行下面的語句
select col1, col2 from t1 where col1 = '213';要注意使用復(fù)合索引需要滿足最左側(cè)索引的原則，也就是查詢的時(shí)候如果where條件里面沒有最左邊的一到多列，索引就不會(huì)起作用。
在SQL Server中還有include的用法，可以把非聚集索引里包含的列包含進(jìn)來，而不一定需要建立復(fù)合索引。
四.總結(jié)與使用心得
使用聚集索引的查詢效率要比非聚集索引的效率要高，但是如果需要頻繁去改變聚集索引的值，寫入性能并不高，因?yàn)樾枰苿?dòng)對(duì)應(yīng)數(shù)據(jù)的物理位置。
非聚集索引在查詢的時(shí)候可以的話就避免二次查詢，這樣性能會(huì)大幅提升。
不是所有的表都適合建立索引，只有數(shù)據(jù)量大表才適合建立索引，且建立在選擇性高的列上面性能會(huì)更好。
另附本人
博客地址參考資料：
[1]：
微軟技術(shù)支持官方博客[2]：
聚集索引和非聚集索引（整理）
