MySQL效率調校

索引（index）

• MySQL Indexing: Best Practices
• MySQL Indexing: Best Practices Slide PDF 
• Tools and Techniques for Index Design
• Tools and Techniques for Index Design PDF Slide

交易（Transaction）

• MySQL InnoDB 的 Transaction 的簡易用法
• START TRANSACTION, COMMIT, and ROLLBACK Syntax

語法（SQL）

• 數據庫操作優化（insert、update、delete、select)  
• mysql獲取數據庫和表中信息及常用優化
• EXPLAIN Demystified
• EXPLAIN Demystified PDF slide
• Rails-with-massive-data

伺服器（Server）

• Optimizing MySQL Configuration
• Optimizing MySQL Configuration PDF Slide
• Distributed Key-Value Database 
• MySQL 的調校 (軟硬體、版本、設定)

其他參考網站

• Percona MySQL Webinars
• Simple Optimization for PHP and MySQL

重點整理

• 控制索引更新

• 關閉索引更新：ALTER TABLE table_name DISABLE KEYS;
• 開啟索引更新：ALTER TABLE table_name ENABLE KEYS;

MySQL在新增（INSERT）、刪除（DELETE）、更新（UPDATE）的時候會去更新現有的索引表，而更新索引表也需要花費一些時間，當異動一筆資料的時候，索引表也做一次的異動，但當在做大量資料異動的時候，例如異動1000筆資料，索引表也需要異動1000次，而其實我們只需要最後一次（最新）的異動就好了，前面的999次都是不需要做的索引表異動更新，所以在異動大量資料前，可以使用指令ALTER TABLE table_name DISABLE KEYS;關閉索引更新，等異動完成後，再使用指令ALTER TABLE table_name ENABLE KEYS;開啟索引更新。

ALTER TABLE users DISABLE KEYS;
異動（INSERT、DELETE、UPDATE）大量資料SQL語法
ALTER TABLE users ENABLE KEYS;

• 控制InnoDB Transaction

• 關閉自動提交：SET autocommit=0;
• 開啟自動提交：SET autocommit=1;

在MySQL InnoDB預設所有的資料異動都是Transaction（交易），當與資料庫做連線的時候，InnoDB會採用自動提交（autocommit）的方式，所以除非使用BEGIN;及COMMIT;將異動語法包覆起來設定為同一個Transaction，否則任何一個資料異動的語法（INSERT、DELETE、UPDATE）會認為是一個獨立的Transaction，所以資料表每次做資料異動的時候會一直提交(COMMIT)去更新日誌，若有1000筆獨立的SQL要執行就會被COMMIT 1000次，所以在下異動語法之前，可以使用指令SET autocommit=0;關閉自動提交，等異動完成後，再使用指令SET autocommit=1;開啟自動提交

SET autocommit=0;
異動（INSERT、DELETE、UPDATE）大量資料SQL語法
SET autocommit=1;

• 資料庫伺服器設定

max_allowed_packet：允許最大封包

在INSERT或UPDATE的時候，因為封包資料過大，導致錯誤發生

max_connect_errors：最大連線錯誤

設 4294967295，可以避免當 client (像是 php) 發生太多錯誤時被 block 住。