索引分為聚簇索引和非聚簇索引。

以一本英文課本為例，要找第8課，直接翻書，若先翻到第5課，則往後翻，再翻到第10課，則又往前翻。這本書本身就是一個索引，即“聚簇索引”。
如果要找"fire”這個單詞，會翻到書後面的附錄，這個附錄是按字母排序的，找到F字母那一塊，再找到"fire”，對應的會是它在第幾課。這個附錄，為“非聚簇索引”。
由此可見，聚簇索引，索引的順序就是資料存放的順序，所以，很容易理解，一張資料表只能有一個聚簇索引。
聚簇索引要比非聚簇索引查詢效率高很多，特別是範圍查詢的時候。所以，至於聚簇索引到底應該為主鍵，還是其他欄位，這個可以再討論。
1、MYSQL的索引
mysql中，不同的儲存引擎對索引的實現方式不同，大致說下MyISAM和InnoDB兩種儲存引擎。
MyISAM的B+Tree的葉子節點上的data，並不是資料本身，而是資料存放的地址。主索引和輔助索引沒啥區別，只是主索引中的key一定得是唯一的。這裡的索引都是非聚簇索引。
MyISAM還採用壓縮機制儲存索引，比如，第一個索引為“her”，第二個索引為“here”，那麼第二個索引會被儲存為“3,e”，這樣的缺點是同一個節點中的索引只能採用順序查詢。

InnoDB的資料檔案本身就是索引檔案，B+Tree的葉子節點上的data就是資料本身，key為主鍵，這是聚簇索引。非聚簇索引，葉子節點上的data是主鍵(所以聚簇索引的key，不能過長)。為什麼存放的主鍵，而不是記錄所在地址呢，理由相當簡單，因為記錄所在地址並不能保證一定不會變，但主鍵可以保證。
至於為什麼主鍵通常建議使用自增id呢？
2、聚簇索引
聚簇索引的資料的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那麼對應的資料一定也是相鄰地存放在磁碟上的。如果主鍵不是自增id，那麼可以想象，它會幹些什麼，不斷地調整資料的實體地址、分頁，當然也有其他一些措施來減少這些操作，但卻無法徹底避免。但，如果是自增的，那就簡單了，它只需要一頁一頁地寫，索引結構相對緊湊，磁碟碎片少，效率也高。
聚簇索引不但在檢索上可以大大滴提高效率，在資料讀取上也一樣。比如：需要查詢f~t的所有單詞。
一個使用MyISAM的主索引，一個使用InnoDB的聚簇索引。兩種索引的B+Tree檢索時間一樣，但讀取時卻有了差異。
因為MyISAM的主索引並非聚簇索引，那麼他的資料的實體地址必然是凌亂的，拿到這些實體地址，按照合適的演算法進行I/O讀取，於是開始不停的尋道不停的旋轉。聚簇索引則只需一次I/O。
不過，如果涉及到大資料量的排序、全表掃描、count之類的操作的話，還是MyISAM佔優勢些，因為索引所佔空間小，這些操作是需要在記憶體中完成的。
鑑於聚簇索引的範圍查詢效率，很多人認為使用主鍵作為聚簇索引太多浪費，畢竟幾乎不會使用主鍵進行範圍查詢。但若再考慮到聚簇索引的儲存，就不好定論了。

下表給出了何時使用聚簇索引與非聚簇索引:

動作	使用聚簇索引	使用非聚簇索引
列經常被分組排序	應	應
返回某範圍內的資料	應	不應
一個或極少不同值	不應	不應
小數目的不同值	應	不應
大數目的不同值	不應	應
頻繁更新的列	不應	應
外來鍵列	應	應
主鍵列	應	應
頻繁修改索引列	不應	應

根據調優實踐，要注意聚簇索引的選擇。首先我們要找到我們最多用到的SQL查詢，像本例就是那句類似的組合條件查詢的情況，這種情況最好使用組合聚簇索引，而且最多用到的欄位要放在組合聚簇索引的前面，否則的話就索引就不會有好的效果。

Index seek 為什麼比 Index scan好？
索引掃描也就是遍歷B樹，而seek是B樹查詢直接定位。
Index scan多半是出現在索引列在表示式中。資料庫引擎無法直接確定你要的列的值，所以只能掃描整個整個索引進行計算。index seek就要好很多.資料庫引擎只需要掃描幾個分支節點就可以定位到你要的記錄。回過來,如果聚集索引的葉子節點就是記錄，那麼Clustered Index Scan就基本等同於full table scan。

一些最佳化原則
1、預設情況下建立的索引是非聚簇索引，但有時它並不是最佳的。在非群集索引下，資料在物理上隨機存放在資料頁上。合理的索引設計要建立在對各種查詢的分析和預測上。一般來說：
a.有大量重複值、且經常有範圍查詢（ > ,< ，> =,< =）和order by、group by發生的列，可考
慮建立群集索引；
b.經常同時存取多列，且每列都含有重複值可考慮建立組合索引；
c.組合索引要儘量使關鍵查詢形成索引覆蓋，其前導列一定是使用最頻繁的列。索引雖有助於提高效能但不是索引越多越好，恰好相反過多的索引會導致系統低效。使用者在表中每加進一個索引，維護索引集合就要做相應的更新工作。

2、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短語，任何一種索引都有助於SELECT的效能提高。

3、多表操作在被實際執行前，查詢最佳化器會根據連線條件，列出幾組可能的連線方案並從中找出系統開銷最小的最佳方案。連線條件要充份考慮帶有索引的表、行數多的表；內外表的選擇可由公式：外層表中的匹配行數*內層表中每一次查詢的次數確定，乘積最小為最佳方案。

4、任何對列的操作都將導致表掃描，它包括資料庫函式、計算表示式等等，查詢時要儘可能將操作移至等號右邊。

5、IN、OR子句常會使用工作表，使索引失效。如果不產生大量重複值，可以考慮把子句拆開。拆開的子句中應該包含索引。

建立聚簇索引的思想

1、大多數表都應該有聚簇索引或使用分割槽來降低對錶尾頁的競爭，在一個高事務的環境中，對最後一頁的封鎖嚴重影響系統的吞吐量。
2、在聚簇索引下，資料在物理上按順序排在資料頁上，重複值也排在一起，因而在那些包含範圍檢查(between、<、<=、>、>=)或使用group by或orderby的查詢時，一旦找到具有範圍中第一個鍵值的行，具有後續索引值的行保證物理上毗連在一起而不必進一步搜尋，避免了大範圍掃描，可以大大提高查詢速度。
3、在一個頻繁發生插入操作的表上建立聚簇索引時，不要建在具有單調上升值的列(如IDENTITY)上，否則會經常引起封鎖衝突。
4、在聚簇索引中不要包含經常修改的列，因為碼值修改後，資料行必須移動到新的位置。
5、選擇聚簇索引應基於where子句和連線操作的型別。

聚簇索引的侯選列

1、主鍵列,該列在where子句中使用並且插入是隨機的。
2、按範圍存取的列，如pri_order > 100 and pri_order < 200。
3、在group by或order by中使用的列。
4、不經常修改的列。
5、在連線操作中使用的列。

非聚簇索引

非聚簇索引，葉級頁指向表中的記錄，記錄的物理順序與邏輯順序沒有必然的聯絡。非聚簇索引則更像書的標準索引表，索引表中的順序通常與實際的頁碼順序是不一致的。

每個表只能有一個聚簇索引，因為一個表中的記錄只能以一種物理順序存放。但是，一個表可以有不止一個非聚簇索引。實際上，對每個表你最多可以建立249個非聚簇索引。非聚簇索引需要大量的硬碟空間和記憶體。另外，雖然非聚簇索引可以提高從表中取資料的速度，它也會降低向表中插入和更新資料的速度。每當你改變了一個建立了非聚簇索引的表中的資料時，必須同時更新索引。因此你對一個表建立非聚簇索引時要慎重考慮。如果你預計一個表需要頻繁地更新資料，那麼不要對它建立太多非聚簇索引。另外，如果硬碟和記憶體空間有限，也應該限制使用非聚簇索引的數量

非聚簇索引的使用

SQLServer預設情況下建立的索引是非聚簇索引，由於非聚簇索引不重新組織表中的資料，而是對每一行儲存索引列值並用一個指標指向資料所在的頁面。換句話說非聚簇索引具有在索引結構和資料本身之間的一個額外級。一個表如果沒有聚簇索引時,可有250個非聚簇索引。每個非聚簇索引提供訪問資料的不同排序順序。在建立非聚簇索引時，要權衡索引對查詢速度的加快與降低修改速度之間的利弊。另外，還要考慮這些問題：
1、索引需要使用多少空間。
2、合適的列是否穩定。
3、索引鍵是如何選擇的，掃描效果是否更佳。
4、是否有許多重複值。
對更新頻繁的表來說，表上的非聚簇索引比聚簇索引和根本沒有索引需要更多的額外開銷。對移到新頁的每一行而言，指向該資料的每個非聚簇索引的頁級行也必須更新，有時可能還需要索引頁的分理。從一個頁面刪除資料的程式也會有類似的開銷，另外，刪除程式還必須把資料移到頁面上部，以保證資料的連續性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情況:
1、某列常用於集合函式(如Sum,....)。
2、某列常用於join,order by,group by。
3、查尋出的資料不超過表中資料量的20%。

MySQL聚簇索引和非聚簇索引的原理及使用

相關文章