【資料庫】——聚集索引和非聚集索引

聚集索引

    聚集索引是一種特殊索引，使資料按照索引的排序順序存放表中。實際上重組了表中的标準。當資料按值的範圍查詢時，聚集索引就顯得特别有用。當大量資料修改的時候，不再适合使用聚集索引。

建立聚集索引的思想

1、大多數表都應該有聚集索引或使用分區來降低對表尾頁的競争，在一個高事務的環境中，對最後一頁的封鎖嚴重影響系統的吞吐量。

2、在聚集索引下，資料在實體上按順序排在資料頁上，重複值也排在一起，因而在那些包含範圍檢查(between、<、<=、>、>=)或使用group by或order by的查詢時，一旦找到具有範圍中第一個鍵值的行，具有後續索引值的行保證實體上連在一起而不必進一步搜尋，避免了大範圍掃描，可以大大提高查詢速度。

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,orderby,groupby。

3、查尋出的資料不超過表中資料量的20%。

總結