Schema的最佳化和索引

當你已經建立了一張表，有合適的資料類型，並添加了索引之後，其實你的工作還並沒有結束：你還需要維護你的表和索引使它們工作的更好。表的維護有三個主要的目標：發現和解決表的損壞，維護準確的索引統計，並且要降低儲存片段。

找到和修複損壞的表

最差的事情莫過於表已經損壞了。對於MyISAM，大部分是由於當機所造成的。然而，所有的儲存引擎都會由於硬體問題或者MySQL內部BUG再或者作業系統的原因導致索引的損壞。

損壞的索引能導致查詢返回不正確的結果，當沒有重複值出現卻拋出重複索引值的錯誤，或者導致查詢死結和當機。如果你碰到了奇怪的行為-比如一個你意想不到的錯誤，就CHECK TABLE來查看錶是否損壞。CHECK TABLE一般可以檢查大部分表和索引的損壞。

你可以使用REPAIR TABLE來修複。但是並不是所有的引擎都支援這個命令。這樣你可以使用ALTER命令，比如修改和表相同的儲存引擎。

mysql> ALTER TABLE innodb_tbl ENGINE=INNODB;

你也可以使用離線的針對儲存引擎的修複工具。比如myisamchk或者刪除資料再重新載入。然而，如果換壞是發生在系統中，或者在表中的“行資料”取代了索引，你就無能為力了。這種情況下，你只能從備份中恢複表或者從損壞的檔案中恢複資料。以後會詳細說到。

更新索引的統計

MySQL的查詢最佳化工具使用兩個API從儲存引擎中得知當決定怎樣使用索引的時候，索引是怎樣分布的。第一個是records_in_range調用。它傳入終結點範圍並且返回了範圍的記錄的值。第二個就是info(),它返回了不同類型的資料，包括了索引的基數（對於每個索引值有多少資料）。

當儲存引擎並沒有提供給最佳化器關於查詢行數的準確資訊，這個最佳化器就會使用索引的統計資訊。這個資訊你可以使用ANALYZE TABLE來估計下行數。MySQL的最佳化器是基於成本的，並且最主要的消耗因素就是這個查詢要訪問多少資料。如果這個統計資訊沒有產生，或者如果它們到期了，最佳化器可能就會有個比較差的決定。方案就是使用ANALYZE TABLE來產生統計資料。

每個儲存引擎產生索引的統計資料各不相同，索引你使用ANALYZE TABLE的頻率也不同，同樣的消耗成本也不同：

Memory儲存引擎不會儲存索引統計資訊。

MyISAM在硬碟上儲存統計資訊，並且ANALYZE TABLE執行了全索引掃描來計算。這個過程這張表是鎖定的。

InnoDB並不是在硬碟上儲存統計資訊。但是使用隨機索引進入首次開啟的表的方法來估算它們。對於InnoDB，ANALYZE TABLE使用的是隨機的方式。因此統計結果不精確的，它們不需要手動更新，除非你伺服器運行了很長時間。ANALYZE TABLE也不回加鎖消耗也相對低些。因此你可以線上的更新統計資料而不會影響正常工作。

你可以使用SHOW INDEX FROM來查看索引資訊。

mysql> SHOW INDEX FROM sakila.actor\G
*************************** 1. row ***************************
Table: actor
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: actor_id
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: actor
Non_unique: 1
Key_name: idx_actor_last_name
Seq_in_index: 1
Column_name: last_name
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE

給出了很多索引資訊。MySQL文檔有詳細說明。我們要注意的是Cardinality。這顯示了在索引中儲存引擎估算了多少個唯一的值。MySQL5.0中你也可以在INFORMATION_SCHEMA.STATISTICS表中獲得這些資訊，這樣更方便了。舉個例子，如果你可以寫一條查詢INFORMATION_SCHEMA的語句，來發現選擇性更低的索引。