MySQL DBA教程：Mysql性能優化之緩存參數優化

在平時被問及最多的問題就是關于 MySQL 資料庫性能優化方面的問題，是以最近打算寫一個MySQL資料庫性能優化方面的系列文章，希望對國中級 MySQL DBA 以及其他對 MySQL 性能優化感興趣的朋友們有所幫助

資料庫屬于 IO 密集型的應用程式，其主要職責就是資料的管理及存儲工作。而我們知道，從記憶體中讀取一個資料庫的時間是微秒級别，而從一塊普通硬碟上讀取一個IO是在毫秒級别，二者相差3個數量級。是以，要優化資料庫，首先第一步需要優化的就是 IO，盡可能将磁盤IO轉化為記憶體IO。本文先從 MySQL 資料庫IO相關參數(緩存參數)的角度來進行IO優化：

一、query_cache_size/query_cache_type (global)

    Query cache 作用于整個 MySQL Instance，主要用來緩存 MySQL 中的 ResultSet，也就是一條SQL語句執行的結果集，是以僅僅隻能針對select語句。當我們打開了 Query Cache 功能，MySQL在接受到一條select語句的請求後，如果該語句滿足Query Cache的要求(未顯式說明不允許使用Query Cache，或者已經顯式申明需要使用Query Cache)，MySQL 會直接根據預先設定好的HASH算法将接受到的select語句以字元串方式進行hash，然後到Query Cache 中直接查找是否已經緩存。也就是說，如果已經在緩存中，該select請求就會直接将資料傳回，進而省略了後面所有的步驟(如 SQL語句的解析，優化器優化以及向存儲引擎請求資料等)，極大的提高性能。

    當然，Query Cache 也有一個緻命的缺陷，那就是當某個表的資料有任何任何變化，都會導緻所有引用了該表的select語句在Query Cache 中的緩存資料失效。是以，當我們的資料變化非常頻繁的情況下，使用Query Cache 可能會得不償失。

    Query Cache的使用需要多個參數配合，其中最為關鍵的是 query_cache_size 和 query_cache_type ，前者設定用于緩存 ResultSet 的記憶體大小，後者設定在何場景下使用 Query Cache。在以往的經驗來看，如果不是用來緩存基本不變的資料的MySQL資料庫，query_cache_size 一般 256MB 是一個比較合适的大小。當然，這可以通過計算Query Cache的命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))來進行調整。query_cache_type可以設定為0(OFF)，1(ON)或者2(DEMOND)，分别表示完全不使用query cache，除顯式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache，隻有顯示要求才使用query cache(使用sql_cache)。

二、binlog_cache_size (global)

    Binlog Cache 用于在打開了二進制日志(binlog)記錄功能的環境，是 MySQL 用來提高binlog的記錄效率而設計的一個用于短時間内臨時緩存binlog資料的記憶體區域。

    一般來說，如果我們的資料庫中沒有什麼大事務，寫入也不是特别頻繁，2MB～4MB是一個合适的選擇。但是如果我們的資料庫大事務較多，寫入量比較大，可與适當調高binlog_cache_size。同時，我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設定的binlog_cache_size是否足夠，是否有大量的binlog_cache由于記憶體大小不夠而使用臨時檔案(binlog_cache_disk_use)來緩存了。

三、key_buffer_size (global)

    Key Buffer 可能是大家最為熟悉的一個 MySQL 緩存參數了，尤其是在 MySQL 沒有更換預設存儲引擎的時候，很多朋友可能會發現，預設的 MySQL 配置檔案中設定最大的一個記憶體參數就是這個參數了。key_buffer_size 參數用來設定用于緩存 MyISAM存儲引擎中索引檔案的記憶體區域大小。如果我們有足夠的記憶體，這個緩存區域最好是能夠存放下我們所有的 MyISAM 引擎表的所有索引，以盡可能提高性能。

    此外，當我們在使用MyISAM 存儲的時候有一個及其重要的點需要注意，由于 MyISAM 引擎的特性限制了他僅僅隻會緩存索引塊到記憶體中，而不會緩存表資料庫塊。是以，我們的 SQL 一定要盡可能讓過濾條件都在索引中，以便讓緩存幫助我們提高查詢效率。

四、bulk_insert_buffer_size (thread)

    和key_buffer_size一樣，這個參數同樣也僅作用于使用 MyISAM存儲引擎，用來緩存批量插入資料的時候臨時緩存寫入資料。當我們使用如下幾種資料寫入語句的時候，會使用這個記憶體區域來緩存批量結構的資料以幫助批量寫入資料檔案：

複制代碼代碼如下:

insert … select …

     insert … values (…) ,(…),(…)…

     load data infile… into… (非空表)

五、innodb_buffer_pool_size(global)

    當我們使用InnoDB存儲引擎的時候，innodb_buffer_pool_size 參數可能是影響我們性能的最為關鍵的一個參數了，他用來設定用于緩存 InnoDB 索引及資料塊的記憶體區域大小，類似于 MyISAM 存儲引擎的 key_buffer_size 參數，當然，可能更像是 Oracle 的 db_cache_size。簡單來說，當我們操作一個 InnoDB 表的時候，傳回的所有資料或者去資料過程中用到的任何一個索引塊，都會在這個記憶體區域中走一遭。

    和key_buffer_size 對于 MyISAM 引擎一樣，innodb_buffer_pool_size 設定了 InnoDB 存儲引擎需求最大的一塊記憶體區域的大小，直接關系到 InnoDB存儲引擎的性能，是以如果我們有足夠的記憶體，盡可将該參數設定到足夠打，将盡可能多的 InnoDB 的索引及資料都放入到該緩存區域中，直至全部。

    我們可以通過 (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算緩存命中率，并根據命中率來調整 innodb_buffer_pool_size 參數大小進行優化。

六、innodb_additional_mem_pool_size（global）

這個參數我們平時調整的可能不是太多，很多人都使用了預設值，可能很多人都不是太熟悉這個參數的作用。innodb_additional_mem_pool_size 設定了InnoDB存儲引擎用來存放資料字典資訊以及一些内部資料結構的記憶體空間大小，是以當我們一個MySQL Instance中的資料庫對象非常多的時候，是需要适當調整該參數的大小以確定所有資料都能存放在記憶體中提高通路效率的。

這個參數大小是否足夠還是比較容易知道的，因為當過小的時候，MySQL 會記錄 Warning 資訊到資料庫的 error log 中，這時候你就知道該調整這個參數大小了。

七、innodb_log_buffer_size （global）

這是 InnoDB 存儲引擎的事務日志所使用的緩沖區。類似于 Binlog Buffer，InnoDB 在寫事務日志的時候，為了提高性能，也是先将資訊寫入 Innofb Log Buffer 中，當滿足 innodb_flush_log_trx_commit 參數所設定的相應條件（或者日志緩沖區寫滿）之後，才會将日志寫到檔案（或者同步到磁盤）中。可以通過 innodb_log_buffer_size 參數設定其可以使用的最大記憶體空間。 

注：innodb_flush_log_trx_commit 參數對 InnoDB Log 的寫入性能有非常關鍵的影響。該參數可以設定為0，1，2，解釋如下：

0：log buffer中的資料将以每秒一次的頻率寫入到log file中，且同時會進行檔案系統到磁盤的同步操作，但是每個事務的commit并不會觸發任何log buffer 到log file的重新整理或者檔案系統到磁盤的重新整理操作； 

1：在每次事務送出的時候将log buffer 中的資料都會寫入到log file，同時也會觸發檔案系統到磁盤的同步； 

2：事務送出會觸發log buffer 到log file的重新整理，但并不會觸發磁盤檔案系統到磁盤的同步。此外，每秒會有一次檔案系統到磁盤同步操作。

此外，MySQL文檔中還提到，這幾種設定中的每秒同步一次的機制，可能并不會完全確定非常準确的每秒就一定會發生同步，還取決于程序排程的問題。實際上，InnoDB 能否真正滿足此參數所設定值代表的意義正常 Recovery 還是受到了不同 OS 下檔案系統以及磁盤本身的限制，可能有些時候在并沒有真正完成磁盤同步的情況下也會告訴 mysqld 已經完成了磁盤同步。

八、innodb_max_dirty_pages_pct （global）

這個參數和上面的各個參數不同，他不是用來設定用于緩存某種資料的記憶體大小的一個參數，而是用來控制在 InnoDB Buffer Pool 中可以不用寫入資料檔案中的Dirty Page 的比例（已經被修但還沒有從記憶體中寫入到資料檔案的髒資料）。這個比例值越大，從記憶體到磁盤的寫入操作就會相對減少，是以能夠一定程度下減少寫入操作的磁盤IO。

但是，如果這個比例值過大，當資料庫 Crash 之後重新開機的時間可能就會很長，因為會有大量的事務資料需要從日志檔案恢複出來寫入資料檔案中。同時，過大的比例值同時可能也會造成在達到比例設定上限後的 flush 操作“過猛”而導緻性能波動很大。