MySQL innoDB效能最佳化

起因：有一個innoDB引擎的表Table，在一個大概3000次的foreach迴圈中執行

INSERT INTO Table(columnA, columnB) VALUES (valueA, valueB)

結果居然超出了60S的php執行限制（當然這個限制可以在php.ini中修改），讓我很不解為何插入效率如此低下。

經過尋找資料以及摸索，得到以下最佳化方法：

1、innoDB是mysql引擎中唯一支援事務transaction的引擎。預設所有使用者行為都在事務內發生。
預設mysql建立新串連時，innoDB採用自動認可autocommit模式，每個SQL語句在它自己上形成一個單獨的事務，即insert一次就commit了一次，InnoDB在該事務提交時必須重新整理日誌到磁碟，因此效率受限於磁碟讀寫效率。
你可以通過

mysql_query("SET AUTOCOMMIT = 0");

來關閉自動認可模式。

如
果自動認可模式被關閉，那麼我們可以認為一個使用者總是有一個事務開啟著。一個SQL
COMMIT或ROLLBACK語句結束當前事務並且一個新事務開始。兩個語句都釋放所有在當前事務中被設定的InnoDB鎖定。一個COMMIT語句意
味著在當前事務中做的改變被產生為永久的，並且變成其它使用者可見的。一個ROLLBACK語句，在另一方面，撤銷所有當前事務做的修改。
當然如果是自動認可模式，通過用明確的START TRANSACTION或BEGIN語句來開始一個事務，並用COMMIT或者ROLLBACK語句來結束它，這樣使用者仍舊可以執行一個多重語句事務。

2、因此對於本例，在建立資料庫連接後，立即關閉自動認可，在foreach迴圈結束後，一次commit即可，效率大大提升。

mysql_query("SET AUTOCOMMIT = 0");
foreach(***)
  INSERT INTO Table(columnA, columnB) VALUES (valueA, valueB)
mysql_query("commit");

3、對於多次insert行到同一表的需求，你還可以採用多行插入文法來減少用戶端和伺服器之間的通訊開支。
即

INSERT INTO Table(columnA, columnB) VALUES (1,2), (5,5), (3,3), ...

4、如果你的表有索引，索引會拖慢insert速度。大量插入資料時，可以先關閉索引，然後再重建索引。

ALTER TABLE Table DISABLE KEYS;
INSERT INTO ***;
ALTER TABLE Table ENABLE KEYS;

· 如果你在第二個鍵上有UNIQUE約束，你可以在匯入會話中暫時關閉唯一性檢查以加速表的匯入：

SET UNIQUE_CHECKS=0;

對於大表，這節約了大量磁碟I/O，因為InnoDB可以使用它的插入緩衝來在一批內寫第二個索引記錄。

· 如果你對你的表有FOREIGN KEY約束，你可以在匯入會話過程中通過關閉外鍵檢查來提速表的匯入：

SET FOREIGN_KEY_CHECKS=0;

對於大表，這可以節約大量的磁碟I/O。

· 如果你經常有對不經常更新的表的重發查詢，請使用查詢快取：

[mysqld]
query_cache_type = ON
query_cache_size = 10M