2012年12月22日 星期六

MySQL效率調校

索引(index)

語法(SQL)

重點整理

  • 控制索引更新

    • 關閉索引更新: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 住。


沒有留言:

張貼留言

ADS