《MySQL DBA修煉之道》——2.6 MySQL的主要參數設定

本節書摘來自華章出版社《mysql dba修煉之道》一書中的第2章，第2.6節，作者：陳曉勇，更多章節内容可以通路雲栖社群“華章計算機”公衆号檢視。

研發、測試人員往往熟悉sql語句的撰寫、表結構的設計，而不熟悉mysql的配置，這可能會導緻一些困惑，比較常見的是，線上上運作良好的查詢，到了線下就變慢了，下面介紹幾個常見的參數配置。一般情況下，配置好這幾個參數可以滿足大部分開發環境和測試環境的要求。

（1）innodb_buffer_pool_size

為了提升寫性能，可以把要寫的資料先在緩沖區（buffer）裡合并，然後再發送給下一級存儲。這樣做可提高i/o操作的效率。innodb buffer pool就是innodb用來緩存它的資料和索引的記憶體緩沖區，可由innodb_buffer_pool_size設定其大小。理論上，将這個值設定得越高，通路資料需要的磁盤i/o就越少。常見的做法是讓這個值大于熱點資料，這樣可以獲得比較好的性能。如果不清楚環境的資料量和通路模式，建議将其設定為機器實體記憶體大小的70%～80%。

（2）innodb_log_file_size

日志組裡每個日志檔案的大小。在32位計算機上日志檔案的合并大小必須小于4gb，預設大小是5mb，在生産環境下，這個值太小了。官方文檔推薦的值為從1mb到1/n的緩沖池大小，其中n是日志組裡日志檔案的數目（由innodb_log_files_in_group變量來确定，一般預設為2）。值越大，在緩沖池中需要檢查點重新整理的行為就越少，是以也越節約磁盤i/o，但更大的日志檔案也意味着在崩潰時恢複得更慢。建議将日志檔案的大小設定為256mb或更大，這樣可以滿足一般情況下的需要。

（3）innodb_flush_log_at_trx_commit，建議設定為2

這個選項的預設值是1。當設定為2時，在每個事務送出時，日志緩沖被寫到檔案中，但不對日志檔案做重新整理到磁盤的操作。對日志檔案的重新整理每秒才發生一次。是以，理論上，作業系統崩潰或掉電隻會丢失最後一秒的事務。

（4）sync_binlog，建議設定為0

如果是autocommit模式，那麼每執行一個語句就會向二進制日志寫入一次，否則每個事務寫入一次。如果sync_binlog的值為正，那麼每當sync_binlog參數設定的語句或事務數被寫入二進制日志後，mysql伺服器就會将它的二進制日志同步到硬碟上。預設值是0，不與硬碟同步。值為1是最安全的選擇，因為崩潰時，你最多丢掉二進制日志中的一個語句或事務。但是，這也是最慢的選擇，成本昂貴。

另外，在mysql中，資料庫對應資料目錄中的目錄。資料庫中的每個表至少對應資料庫目錄中的一個檔案（也可能是多個，取決于存儲引擎）。是以，所使用作業系統的大小寫敏感性決定了資料庫名和表名的大小寫敏感性。在大多數unix中資料庫名和表名對大小寫敏感，而在windows中對大小寫則不敏感。我們應設定變量lower_case_table_names=0，這也是unix/linux系統的預設值。開發環境、測試環境的mysql也建議部署在unix/linux平台，盡可能和生産環境一緻。