MySQL的MyISAM與InnoDB的索引方式

標籤：

在MySQL中，索引屬於儲存引擎層級的概念，不同儲存引擎對索引的實現方式是不同的，本文主要討論MyISAM和InnoDB兩個儲存引擎的索引實現方式。

MyISAM索引實現

MyISAM引擎使用B+Tree作為索引結構，分葉節點的data域存放的是資料記錄的地址。是MyISAM索引的原理圖：

這裡設表一共有三列，假設我們以Col1為主鍵，則是一個MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引檔案僅僅儲存資料記錄的地址。在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重複。如果我們在Col2上建立一個輔助索引，則此索引的結構如所示：

 

同樣也是一顆B+Tree，data域儲存資料記錄的地址。因此，MyISAM中索引檢索的演算法為首先按照B+Tree搜尋演算法搜尋索引，如果指定的Key存在，則取出其data域的值，然後以data域的值為地址，讀取相應資料記錄。

MyISAM的索引方式也叫做“非聚集”的，之所以這麼稱呼是為了與InnoDB的叢集索引區分。聚簇索引（clustered index ），非聚簇索引（secondary index），這兩個名字雖然都叫做索引，但這並不是一種單獨的索引類型，而是一種資料存放區方式。對於聚簇索引儲存來說，行資料和主鍵B+樹儲存在一起，輔助鍵B+樹只儲存輔助鍵和主鍵，主鍵和非主鍵B+樹幾乎是兩種類型的樹。對於非聚簇索引儲存來說，主鍵B+樹在葉子節點儲存指向真正資料行的指標，而非主鍵。

InnoDB索引實現

雖然InnoDB也使用B+Tree作為索引結構，但具體實現方式卻與MyISAM截然不同。

第一個重大區別是InnoDB的資料檔案本身就是索引檔案。從上文知道，MyISAM索引檔案和資料檔案是分離的，索引檔案僅儲存資料記錄的地址。而在InnoDB中，表資料檔案本身就是按B+Tree組織的一個索引結構，這棵樹的分葉節點data域儲存了完整的資料記錄。這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主索引。

是InnoDB主索引（同時也是資料檔案）的，可以看到分葉節點包含了完整的資料記錄。這種索引叫做叢集索引。因為InnoDB的資料檔案本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表產生一個隱含欄位作為主鍵，這個欄位長度為6個位元組，類型為長整形。

第二個與MyISAM索引的不同是InnoDB的輔助索引data域儲存相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。例如，為定義在Col3上的一個輔助索引：

這裡以英文字元的ASCII碼作為比較準則。叢集索引這種實現方式使得按主鍵的搜尋十分高效，但是輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。

瞭解不同儲存引擎的索引實現方式對於正確使用和最佳化索引都非常有協助，例如知道了InnoDB的索引實現後，就很容易明白為什麼不建議使用過長的欄位作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。再例如，用非單調的欄位作為主鍵在InnoDB中不是個好主意，因為InnoDB資料檔案本身是一顆B+Tree，非單調的主鍵會造成在插入新記錄時資料檔案為了維持B+Tree的特性而頻繁的分裂調整，十分低效，而使用自增欄位作為主鍵則是一個很好的選擇。

下面一個更簡單明了的例子：

InnoDB的自適應hash索引

　　InnoDB儲存引擎會監控對錶上索引的尋找，如果觀察到建立雜湊索引可以帶來速度的提升，則建立雜湊索引，所以稱之為自適應（adaptive） 的。自適應雜湊索引通過緩衝池的B+樹構造而來，因此建立的速度很快。而且不需要將整個表都建雜湊索引，InnoDB儲存引擎會自動根據訪問的頻率和模式 來為某些頁建立雜湊索引。MySQL的Heap儲存引擎預設的索引類型為雜湊。

 

參考：

　　http://www.cnblogs.com/ylqmf/archive/2011/09/16/2179166.html

　　http://blog.codinglabs.org/articles/theory-of-mysql-index.html

　　http://www.codeceo.com/article/mysql-innodb-index.html

 

MySQL的MyISAM與InnoDB的索引方式