一.索引簡介
眾所周知凫海，索引是關系型數(shù)據(jù)庫中給數(shù)據(jù)庫表中一列或多列的值排序后的存儲結構瀑凝，SQL的主流索引結構有B+樹以及Hash結構歼冰，聚集索引以及非聚集索引用的是B+樹索引凰萨。這篇文章會總結SQL Server以及MySQL的InnoDB和MyISAM兩種SQL的索引。
SQL Sever索引類型有：唯一索引侦高，主鍵索引，聚集索引厌杜，非聚集索引奉呛。
MySQL 索引類型有：唯一索引，主鍵（聚集）索引夯尽，非聚集索引瞧壮，全文索引。
二.聚集索引
聚集（clustered）索引匙握，也叫聚簇索引咆槽。
定義：數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個表中只能擁有一個聚集索引圈纺。
單單從定義來看是不是顯得有點抽象秦忿，打個比方麦射，一個表就像是我們以前用的新華字典，聚集索引就像是拼音目錄灯谣，而每個字存放的頁碼就是我們的數(shù)據(jù)物理地址潜秋，我們如果要查詢一個“哇”字，我們只需要查詢“哇”字對應在新華字典拼音目錄對應的頁碼胎许，就可以查詢到對應的“哇”字所在的位置峻呛，而拼音目錄對應的A-Z的字順序，和新華字典實際存儲的字的順序A-Z也是一樣的辜窑，如果我們中文新出了一個字钩述，拼音開頭第一個是B，那么他插入的時候也要按照拼音目錄順序插入到A字的后面穆碎，現(xiàn)在用一個簡單的示意圖來大概說明一下在數(shù)據(jù)庫中的樣子：
| 地址 | id | username | score |
|---|---|---|---|
| 0x01 | 1 | 小明 | 90 |
| 0x02 | 2 | 小紅 | 80 |
| 0x03 | 3 | 小華 | 92 |
| .. | .. | .. | .. |
| 0xff | 256 | 小英 | 70 |
注：第一列的地址表示該行數(shù)據(jù)在磁盤中的物理地址牙勘，后面三列才是我們SQL里面用的表里的列，其中id是主鍵惨远，建立了聚集索引谜悟。
結合上面的表格就可以理解這句話了吧：數(shù)據(jù)行的物理順序與列值的順序相同，如果我們查詢id比較靠后的數(shù)據(jù)北秽，那么這行數(shù)據(jù)的地址在磁盤中的物理地址也會比較靠后葡幸。而且由于物理排列方式與聚集索引的順序相同，所以也就只能建立一個聚集索引了贺氓。
聚集索引實際存放的示意圖
從上圖可以看出聚集索引的好處了蔚叨，索引的葉子節(jié)點就是對應的數(shù)據(jù)節(jié)點（MySQL的MyISAM除外，此存儲引擎的聚集索引和非聚集索引只多了個唯一約束辙培，其他沒什么區(qū)別）蔑水，可以直接獲取到對應的全部列的數(shù)據(jù)，而非聚集索引在索引沒有覆蓋到對應的列的時候需要進行二次查詢扬蕊，后面會詳細講搀别。因此在查詢方面，聚集索引的速度往往會更占優(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é)點仍然是索引節(jié)點擎鸠，只是有一個指針指向對應的數(shù)據(jù)塊缀磕，此如果使用非聚集索引查詢，而查詢列中包含了其他該索引沒有覆蓋的列劣光，那么他還要進行第二次的查詢袜蚕，查詢節(jié)點上對應的數(shù)據(jù)行的數(shù)據(jù)。
如有以下表t1：
| id | username | score |
|---|---|---|
| 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 = '小明'
但是使用以下語句進行查詢凿傅，就需要二次的查詢去獲取原數(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對應的列再進行kek lookup，有關于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';
要注意使用復合索引需要滿足最左側索引的原則闲孤，也就是查詢的時候如果where條件里面沒有最左邊的一到多列，索引就不會起作用肥照。
在SQL Server中還有include的用法，可以把非聚集索引里包含的列包含進來舆绎，而不一定需要建立復合索引吕朵。
四.總結與使用心得
- 使用聚集索引的查詢效率要比非聚集索引的效率要高，但是如果需要頻繁去改變聚集索引的值努溃，寫入性能并不高茅坛，因為需要移動對應數(shù)據(jù)的物理位置。
- 非聚集索引在查詢的時候可以的話就避免二次查詢贡蓖，這樣性能會大幅提升。
- 不是所有的表都適合建立索引彻桃，只有數(shù)據(jù)量大表才適合建立索引晾蜘，且建立在選擇性高的列上面性能會更好。
