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

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

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

    Query Cache的使用需要多個參數(shù)配合，其中最為關鍵的是 query_cache_size 和 query_cache_type ，前者設置用于緩存 ResultSet 的內存大小，后者設置在何場景下使用 Query Cache。在以往的經(jīng)驗來看，如果不是用來緩存基本不變的數(shù)據(jù)的MySQL數(shù)據(jù)庫，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)記錄功能的環(huán)境，是 MySQL 用來提高binlog的記錄效率而設計的一個用于短時間內臨時緩存binlog數(shù)據(jù)的內存區(qū)域。

    一般來說，如果我們的數(shù)據(jù)庫中沒有什么大事務，寫入也不是特別頻繁，2MB～4MB是一個合適的選擇。但是如果我們的數(shù)據(jù)庫大事務較多，寫入量比較大，可與適當調高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 緩存參數(shù)了，尤其是在 MySQL 沒有更換默認存儲引擎的時候，很多朋友可能會發(fā)現(xiàn)，默認的 MySQL 配置文件中設置最大的一個內存參數(shù)就是這個參數(shù)了。key_buffer_size 參數(shù)用來設置用于緩存 MyISAM存儲引擎中索引文件的內存區(qū)域大小。如果我們有足夠的內存，這個緩存區(qū)域最好是能夠存放下我們所有的 MyISAM 引擎表的所有索引，以盡可能提高性能。

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

四、bulk_insert_buffer_size (thread)
    和key_buffer_size一樣，這個參數(shù)同樣也僅作用于使用 MyISAM存儲引擎，用來緩存批量插入數(shù)據(jù)的時候臨時緩存寫入數(shù)據(jù)。當我們使用如下幾種數(shù)據(jù)寫入語句的時候，會使用這個內存區(qū)域來緩存批量結構的數(shù)據(jù)以幫助批量寫入數(shù)據(jù)文件：

五、innodb_buffer_pool_size(global)
    當我們使用InnoDB存儲引擎的時候，innodb_buffer_pool_size 參數(shù)可能是影響我們性能的最為關鍵的一個參數(shù)了，他用來設置用于緩存 InnoDB 索引及數(shù)據(jù)塊的內存區(qū)域大小，類似于 MyISAM 存儲引擎的 key_buffer_size 參數(shù)，當然，可能更像是 Oracle 的 db_cache_size。簡單來說，當我們操作一個 InnoDB 表的時候，返回的所有數(shù)據(jù)或者去數(shù)據(jù)過程中用到的任何一個索引塊，都會在這個內存區(qū)域中走一遭。

    和key_buffer_size 對于 MyISAM 引擎一樣，innodb_buffer_pool_size 設置了 InnoDB 存儲引擎需求最大的一塊內存區(qū)域的大小，直接關系到 InnoDB存儲引擎的性能，所以如果我們有足夠的內存，盡可將該參數(shù)設置到足夠打，將盡可能多的 InnoDB 的索引及數(shù)據(jù)都放入到該緩存區(qū)域中，直至全部。

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

六、innodb_additional_mem_pool_size（global）
這個參數(shù)我們平時調整的可能不是太多，很多人都使用了默認值，可能很多人都不是太熟悉這個參數(shù)的作用。innodb_additional_mem_pool_size 設置了InnoDB存儲引擎用來存放數(shù)據(jù)字典信息以及一些內部數(shù)據(jù)結構的內存空間大小，所以當我們一個MySQL Instance中的數(shù)據(jù)庫對象非常多的時候，是需要適當調整該參數(shù)的大小以確保所有數(shù)據(jù)都能存放在內存中提高訪問效率的。

這個參數(shù)大小是否足夠還是比較容易知道的，因為當過小的時候，MySQL 會記錄 Warning 信息到數(shù)據(jù)庫的 error log 中，這時候你就知道該調整這個參數(shù)大小了。

七、innodb_log_buffer_size （global）
這是 InnoDB 存儲引擎的事務日志所使用的緩沖區(qū)。類似于 Binlog Buffer，InnoDB 在寫事務日志的時候，為了提高性能，也是先將信息寫入 Innofb Log Buffer 中，當滿足 innodb_flush_log_trx_commit 參數(shù)所設置的相應條件（或者日志緩沖區(qū)寫滿）之后，才會將日志寫到文件（或者同步到磁盤）中?？梢酝ㄟ^ innodb_log_buffer_size 參數(shù)設置其可以使用的最大內存空間。
注：innodb_flush_log_trx_commit 參數(shù)對 InnoDB Log 的寫入性能有非常關鍵的影響。該參數(shù)可以設置為0，1，2，解釋如下：

0：log buffer中的數(shù)據(jù)將以每秒一次的頻率寫入到log file中，且同時會進行文件系統(tǒng)到磁盤的同步操作，但是每個事務的commit并不會觸發(fā)任何log buffer 到log file的刷新或者文件系統(tǒng)到磁盤的刷新操作；
1：在每次事務提交的時候將log buffer 中的數(shù)據(jù)都會寫入到log file，同時也會觸發(fā)文件系統(tǒng)到磁盤的同步；
2：事務提交會觸發(fā)log buffer 到log file的刷新，但并不會觸發(fā)磁盤文件系統(tǒng)到磁盤的同步。此外，每秒會有一次文件系統(tǒng)到磁盤同步操作。

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

八、innodb_max_dirty_pages_pct （global）
這個參數(shù)和上面的各個參數(shù)不同，他不是用來設置用于緩存某種數(shù)據(jù)的內存大小的一個參數(shù)，而是用來控制在 InnoDB Buffer Pool 中可以不用寫入數(shù)據(jù)文件中的Dirty Page 的比例（已經(jīng)被修但還沒有從內存中寫入到數(shù)據(jù)文件的臟數(shù)據(jù)）。這個比例值越大，從內存到磁盤的寫入操作就會相對減少，所以能夠一定程度下減少寫入操作的磁盤IO。

但是，如果這個比例值過大，當數(shù)據(jù)庫 Crash 之后重啟的時間可能就會很長，因為會有大量的事務數(shù)據(jù)需要從日志文件恢復出來寫入數(shù)據(jù)文件中。同時，過大的比例值同時可能也會造成在達到比例設定上限后的 flush 操作“過猛”而導致性能波動很大。

上面這幾個參數(shù)是 MySQL 中為了減少磁盤物理IO而設計的主要參數(shù)，對 MySQL 的性能起到了至關重要的作用。

優(yōu)化實例：
按照 mcsrainbow 朋友的要求，這里列一下根據(jù)以往經(jīng)驗得到的相關參數(shù)的建議值：

1.query_cache_type : 如果全部使用innodb存儲引擎，建議為0，如果使用MyISAM 存儲引擎，建議為2，同時在SQL語句中顯式控制是否是喲你gquery cache
2.query_cache_size: 根據(jù) 命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）進行調整，一般不建議太大，256MB可能已經(jīng)差不多了，大型的配置型靜態(tài)數(shù)據(jù)可適當調大
3.binlog_cache_size: 一般環(huán)境2MB～4MB是一個合適的選擇，事務較大且寫入頻繁的數(shù)據(jù)庫環(huán)境可以適當調大，但不建議超過32MB
4.key_buffer_size: 如果不使用MyISAM存儲引擎，16MB足以，用來緩存一些系統(tǒng)表信息等。如果使用 MyISAM存儲引擎，在內存允許的情況下，盡可能將所有索引放入內存，簡單來說就是“越大越好”
5.bulk_insert_buffer_size: 如果經(jīng)常性的需要使用批量插入的特殊語句（上面有說明）來插入數(shù)據(jù)，可以適當調大該參數(shù)至16MB～32MB，不建議繼續(xù)增大，某人8MB
6.innodb_buffer_pool_size: 如果不使用InnoDB存儲引擎，可以不用調整這個參數(shù)，如果需要使用，在內存允許的情況下，盡可能將所有的InnoDB數(shù)據(jù)文件存放如內存中，同樣將但來說也是“越大越好”
7.innodb_additional_mem_pool_size: 一般的數(shù)據(jù)庫建議調整到8MB～16MB，如果表特別多，可以調整到32MB，可以根據(jù)error log中的信息判斷是否需要增大
8.innodb_log_buffer_size: 默認是1MB，系的如頻繁的系統(tǒng)可適當增大至4MB～8MB。當然如上面介紹所說，這個參數(shù)實際上還和另外的flush參數(shù)相關。一般來說不建議超過32MB
9.innodb_max_dirty_pages_pct: 根據(jù)以往的經(jīng)驗，重啟恢復的數(shù)據(jù)如果要超過1GB的話，啟動速度會比較慢，幾乎難以接受，所以建議不大于 1GB/innodb_buffer_pool_size(GB)*100 這個值。當然，如果你能夠忍受啟動時間比較長，而且希望盡量減少內存至磁盤的flush，可以將這個值調整到90，但不建議超過90
注：以上取值范圍僅僅只是我的根據(jù)以往遇到的數(shù)據(jù)庫場景所得到的一些優(yōu)化經(jīng)驗值，并不一定適用于所有場景，所以在實際優(yōu)化過程中還需要大家自己不斷的調整分析，也歡迎大家隨時通過 Mail 與我聯(lián)系溝通交流優(yōu)化或者是架構方面的技術，一起探討相互學習。