學習筆記：InnoDB表和索引結構(一)

MySQL把表的資料詞典資訊以.frm檔案的形式存在資料庫目錄裡，所有MySQL儲存引擎都是這樣的。但是每個InnoDB表在表空間內的InnoDB內部資料詞典裡也有它自己的條目。當MySQL移除表或資料庫，它不得不刪除.frm檔案和InnoDB資料詞典內的相應條目。這就是為什麼你不能在資料庫之間簡單地移動.frm檔案來移動InnoDB表。

[@more@]

1、聚集索引和第二索引

每個InnoDB有一個叫聚集索引（clustered index）的特殊索引，行的資料被存於其中。

• 如果你的表定義了主鍵，主鍵的索引就是聚集索引。
• 如果你的表沒有主鍵，MySQL就選擇第一個不可為空的唯一索引（UNIQUE）作為主鍵，並且InnoDB把它作為聚集索引。
• 如果你的表沒有主鍵，也沒有合適的唯一索引，InnoDB內部會在一個包含RowID的合成列上生成一個隱藏的聚集索引，其中是用InnoDB在分配給RowID來排序的。RowID是一個6位元組的域，它在新行被插入的時候單調增加。因此被RowID排序的行是物理地按照插入順序排的。

透過聚集索引訪問行速度很快，因為行資料與索引掃描頭部在同一資料頁上。如果表是巨大的，當對於那些索引與資料放在不同資料頁上的方案，聚集索引構架通常更節約磁碟I/O。（比如，MyISAM用一個檔案存放資料，另外一個檔案存放索引）。

在InnoDB中，非聚集索引裡的記錄（也稱為輔助索引或第二索引）包含對應行的主鍵值。InnoDB用這個主鍵值從聚集索引中搜尋行。注意，如果主鍵比較長，第二索引就會使用更多空間，因此最好使用一個比較短的主鍵。

2、索引的物理結構

所有InnoDB的索引是B樹索引，這種索引記錄被儲存在樹的葉子頁上。一個索引頁的預設大小是16KB。當新記錄被插入，InnoDB會為將來索引記錄的插入和更新留下十六分之一的頁空間。

如果索引記錄以連續的順序被插入（升序或者降序），結果索引頁大約是15/16滿。如果記錄被以隨機的順序被插入，頁面是從1/2到 15/16滿。如果索引頁的填充因子降到低於1/2，InnoDB會收縮索引樹來釋放頁。

3、插入緩衝

在資料庫應用中，主鍵是一個唯一的識別符，並且新行被以主鍵的升序來插入，這是個常見的情況。因此，聚集索引的插入不需要磁碟的隨機讀。

另一方面，第二索引通常是非唯一的，第二索引的插入順序也相對隨機。這可能會導致大量的隨機磁碟I/O操作，而沒有一個被用在InnoDB中的專用機制。

如果一個索引記錄應該被插入到一個非唯一第二索引，InnoDB檢查第二索引頁是否在緩衝池中。如果是，InnoDB直接插入到索引頁。如果索引頁沒有在緩衝池中被發現，InnoDB插入記錄到一個專門的插入緩衝結構。插入緩衝被保持得如此小以至於它完全適合在緩衝池，並且可以非常快地做插入。

插入緩衝週期性地被合併到資料庫中第二索引樹裡。把數個插入合併到索引樹的同一頁，節省磁碟I/O操作，經常地這是有可能的。據測量，插入緩衝可以提高到表的插入速度達15倍。

在插入事務被提交之後，插入緩衝合併可能連續發生。實際上，伺服器關閉和重啟之後，這會連續發生。

當許多第二索引必須被更新，並且許多行已被插入之時，插入緩衝合併可能需要數個小時。在這期間內，磁碟I/O將會增加，這樣會導致磁碟約束查詢明顯緩慢。另一個明顯的後臺I/O操作是淨化（purge）執行緒