概述

今天主要總結(jié)一下MySQL數(shù)據(jù)庫的聚集索引和非聚集索引，下面一起來看看吧！

一、 MYSQL的索引

mysql中，不同的存儲引擎對索引的實現(xiàn)方式不同，先說下MyISAM和InnoDB兩種存儲引擎。

MyISAM的B+Tree的葉子節(jié)點上的data，并不是數(shù)據(jù)本身，而是數(shù)據(jù)存放的地址。主索引和輔助索引沒啥區(qū)別，只是主索引中的key一定得是唯一的。這里的索引都是非聚簇索引。MyISAM還采用壓縮機制存儲索引，比如，第一個索引為“her”，第二個索引為“here”，那么第二個索引會被存儲為“3,e”，這樣的缺點是同一個節(jié)點中的索引只能采用順序查找。

InnoDB的數(shù)據(jù)文件本身就是索引文件，B+Tree的葉子節(jié)點上的data就是數(shù)據(jù)本身，key為主鍵，這是聚簇索引。非聚簇索引，葉子節(jié)點上的data是主鍵(所以聚簇索引的key，不能過長)。為什么存放的主鍵，而不是記錄所在地址呢，理由相當簡單，因為記錄所在地址并不能保證一定不會變，但主鍵可以保證。

至于為什么主鍵通常建議使用自增id呢？

二、聚簇索引

1、概念

聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對應的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，那么可以想象，它會干些什么，不斷地調(diào)整數(shù)據(jù)的物理地址、分頁，當然也有其他一些措施來減少這些操作，但卻無法徹底避免。但，如果是自增的，那就簡單了，它只需要一頁一頁地寫，索引結(jié)構相對緊湊，磁盤碎片少，效率也高。

聚簇索引不但在檢索上可以大大滴提高效率，在數(shù)據(jù)讀取上也一樣。比如：需要查詢f~t的所有單詞。

一個使用MyISAM的主索引，一個使用InnoDB的聚簇索引。兩種索引的B+Tree檢索時間一樣，但讀取時卻有了差異。

因為MyISAM的主索引并非聚簇索引，那么他的數(shù)據(jù)的物理地址必然是凌亂的，拿到這些物理地址，按照合適的算法進行I/O讀取，于是開始不停的尋道不停的旋轉(zhuǎn)。聚簇索引則只需一次I/O。

不過，如果涉及到大數(shù)據(jù)量的排序、全表掃描、count之類的操作的話，還是MyISAM占優(yōu)勢些，因為索引所占空間小，這些操作是需要在內(nèi)存中完成的。

鑒于聚簇索引的范圍查詢效率，很多人認為使用主鍵作為聚簇索引太多浪費，畢竟幾乎不會使用主鍵進行范圍查詢。但若再考慮到聚簇索引的存儲，就不好定論了。

2、建立聚簇索引的思想：

1、大多數(shù)表都應該有聚簇索引或使用分區(qū)來降低對表尾頁的競爭，在一個高事務的環(huán)境中，對最后一頁的封鎖嚴重影響系統(tǒng)的吞吐量。
2、在聚簇索引下，數(shù)據(jù)在物理上按順序排在數(shù)據(jù)頁上，重復值也排在一起，因而在那些包含范圍檢查(between、<、<=、>、>=)或使用group by或orderby的查詢時，一旦找到具有范圍中第一個鍵值的行，具有后續(xù)索引值的行保證物理上毗連在一起而不必進一步搜索，避免了大范圍掃描，可以大大提高查詢速度。
3、在一個頻繁發(fā)生插入操作的表上建立聚簇索引時，不要建在具有單調(diào)上升值的列(如IDENTITY)上，否則會經(jīng)常引起封鎖沖突。
4、在聚簇索引中不要包含經(jīng)常修改的列，因為碼值修改后，數(shù)據(jù)行必須移動到新的位置。
5、選擇聚簇索引應基于where子句和連接操作的類型。

3、聚簇索引的侯選列：

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

三、非聚簇索引（新華字典的偏旁字典，結(jié)構順序與實際存放順序不一定一致）

1、概念

非聚簇索引，葉級頁指向表中的記錄，記錄的物理順序與邏輯順序沒有必然的聯(lián)系。非聚簇索引則更像書的標準索引表，索引表中的順序通常與實際的頁碼順序是不一致的。非聚集索引葉節(jié)點仍然是索引節(jié)點，只是有一個指針指向?qū)臄?shù)據(jù)塊，如果使用非聚集索引查詢，而查詢列中包含了其他該索引沒有覆蓋的列，那么他還要進行第二次的查詢，查詢節(jié)點上對應的數(shù)據(jù)行的數(shù)據(jù)。

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

2、非聚簇索引的使用：

非聚簇索引不重新組織表中的數(shù)據(jù)，而是對每一行存儲索引列值并用一個指針指向數(shù)據(jù)所在的頁面。每個非聚簇索引提供訪問數(shù)據(jù)的不同排序順序。在建立非聚簇索引時，要權衡索引對查詢速度的加快與降低修改速度之間的利弊。另外，還要考慮這些問題：

1、索引需要使用多少空間。
2、合適的列是否穩(wěn)定。
3、索引鍵是如何選擇的，掃描效果是否更佳。
4、是否有許多重復值。

對更新頻繁的表來說，表上的非聚簇索引比聚簇索引和根本沒有索引需要更多的額外開銷。對移到新頁的每一行而言，指向該數(shù)據(jù)的每個非聚簇索引的頁級行也必須更新，有時可能還需要索引頁的分理。從一個頁面刪除數(shù)據(jù)的進程也會有類似的開銷，另外，刪除進程還必須把數(shù)據(jù)移到頁面上部，以保證數(shù)據(jù)的連續(xù)性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情況: