高性能的MySQL（5）索引政策一聚簇索引

聚簇索引，并不是一種單獨的索引類型，而是一種資料存儲方式。InnoDB的聚簇索引實際上在同一個結構中儲存了B-Tree索引和資料行資訊。

因為無法把資料行存放在兩個不同的地方，是以一個表隻能有一個聚簇索引（不過可以通過覆寫索引來模拟，稍後再介紹）。

這裡主要讨論InnoDB引擎，InnoDB通過主鍵聚集資料，如果沒有主鍵會選擇一個非空的唯一索引，如果還沒有，InnoDB會隐式定義一個主鍵來作為聚簇索引。

聚簇索引是一把雙刃劍，要仔細考慮。

優點：

1、可以把相關資料儲存在一起，例如實作電子郵件時，根據使用者ID來聚集資料，這樣隻需要從磁盤都去少量資料頁就可以擷取某個使用者的全部郵件，如果沒有聚簇索引，則每封郵件都可能導緻一次I/O

2、資料通路更快。因為索引和資料都在一個B-Tree中。

3、使用聚簇索引的查詢，可以直接使用頁節點中的主鍵值。

缺點：

1、聚簇索引最大限度的提高了I/O密集型應用的性能，如果資料都放在記憶體中了，優勢就沒了。

2、插入速度嚴重依賴于插入順序，按照主鍵的順序插入到資料表中速度是最快的。如果不是，插入完資料之後最好使用OPTIMIZE TABLE指令組織一下。

3、更新聚簇索引列的代價很高，因為需要移動新的資料。

4、在插入新行，或者主鍵更新需要移動行的時候，可能導緻頁分裂，進而導緻占用更多的磁盤空間。

5、二級索引（非聚簇）可能比想象的要大，因為二級索引的葉子節點中包含了引用行的主鍵列。

6、二級索引通路需要2次索引查找，而不是一次，因為二級索引儲存的不是行的實體位置，而是主鍵值。

InnoDB和MyISAM資料分布對比。

<code>CREATE</code> <code>TABLE</code> <code>`layout_test` (</code>

<code> </code><code>PRIMARY</code> <code>KEY</code> <code>(`col1`),</code>

主鍵不是遞增的，随機順序插入，col2随即複制，重複的很多。

MyISAM比較簡單，他按照資料插入的順序存儲在磁盤上。

col2索引沒有什麼差別。

InnoDB支援聚簇索引，是以存儲方式非常不同

會發現，該圖顯示了整個表，而不是隻有索引。因為InnoDB中，聚簇索引“就是”表，是以不會像MyISAM那樣需要獨立的行存儲。

即使主鍵是一個字首索引，聚簇索引也會包含完整的主鍵列和其他列。

還有一點和MyISAM不同，InnoDB的二級索引和聚簇索引很不相同，InnoDB二級索引的葉子節點中存儲的不是行指針，而是主鍵值，這樣就減少了移動資料或者頁分裂時對二級索引的維護工作。使用主鍵占用的空間更多，但是無需更新二級索引。

下圖更直接的展示了兩者的差別。

如果在使用InnoDB表，并且沒有資料需要聚集，那麼可以定義一個和應用無關的主鍵，按主鍵順序插入行，最好使用AUTO_INCREMENT，避免使用随機并且範圍很大的聚簇索引，這會使聚簇索引的插入變得完全随機，不僅花費時間更長，而且索引占用空間也會更大。

對于高并發的工作負載，在InnoDB中按主鍵順序插入可能造成明顯的争用，主鍵的上界會成為“熱點”，因為插入都發生在這裡，造成鎖競争，另一個熱點可能是AUTO_INCREMENT鎖機制，對于比較新的版本更改innodb_autoinc_lock_mode配置，可能會工作的更好。

本文轉自shayang8851CTO部落格，原文連結：http://blog.51cto.com/janephp/1310976，如需轉載請自行聯系原作者

繼續閱讀