數據庫屬于 IO 密集型的應用程序，其主要職責就是數據的管理及存儲工(gōng)作。而我(wǒ)們知(zhī)道，從内存中(zhōng)讀取一(yī)個數據庫的時間是微秒級别，而從一(yī)塊普通硬盤上讀取一(yī)個IO是在毫秒級别，二者相差3個數量級。所以，要優化數據庫，首先第一(yī)步需要優化的就是 IO，盡可能将磁盤IO轉化爲内存IO。本文先從 MySQL 數據庫IO相關參數（緩存參數）的角度來看看可以通過哪些參數進行IO優化：

• query_cache_size/query_cache_type （global）

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

當然，Query Cache 也有一(yī)個緻命的缺陷，那就是當某個表的數據有任何任何變化，都會導緻所有引用了該表的select語句在Query Cache 中(zhōng)的緩存數據失效。所以，當我(wǒ)們的數據變化非常頻(pín)繁的情況下(xià)，使用Query Cache 可能會得不償失。

Query Cache的使用需要多個參數配合，其中(zhōng)最爲關鍵的是 query_cache_size 和 query_cache_type ，前者設置用于緩存 ResultSet 的内存大(dà)小(xiǎo)，後者設置在何場景下(xià)使用 Query Cache。在以往的經驗來看，如果不是用來緩存基本不變的數據的MySQL數據庫，query_cache_size 一(yī)般 256MB 是一(yī)個比較合适的大(dà)小(xiǎo)。當然，這可以通過計算Query Cache的命中(zhōng)率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）來進行調整。 query_cache_type可以設置爲0(OFF)，1(ON)或者2(DEMOND)，分(fēn)别表示完全不使用query cache，除顯式要求不使用query cache（使用sql_no_cache）之外(wài)的所有的select都使用query cache，隻有顯示要求才使用query cache（使用sql_cache）。

• binlog_cache_size （global）

Binlog Cache 用于在打開(kāi)了二進制日志(zhì)（binlog）記錄功能的環境，是 MySQL 用來提高binlog的記錄效率而設計的一(yī)個用于短時間内臨時緩存binlog數據的内存區域。

一(yī)般來說，如果我(wǒ)們的數據庫中(zhōng)沒有什麽大(dà)事務，寫入也不是特别頻(pín)繁，2MB～4MB是一(yī)個合适的選擇。但是如果我(wǒ)們的數據庫大(dà)事務較多，寫入量比較大(dà)，可與适當調高binlog_cache_size。同時，我(wǒ)們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分(fēn)析設置的binlog_cache_size是否足夠，是否有大(dà)量的binlog_cache由于内存大(dà)小(xiǎo)不夠而使用臨時文件（binlog_cache_disk_use）來緩存了。

• key_buffer_size （global）

Key Buffer 可能是大(dà)家最爲熟悉的一(yī)個 MySQL 緩存參數了，尤其是在 MySQL 沒有更換默認存儲引擎的時候，很多朋友可能會發現，默認的 MySQL 配置文件中(zhōng)設置最大(dà)的一(yī)個内存參數就是這個參數了。key_buffer_size 參數用來設置用于緩存 MyISAM存儲引擎中(zhōng)索引文件的内存區域大(dà)小(xiǎo)。如果我(wǒ)們有足夠的内存，這個緩存區域最好是能夠存放(fàng)下(xià)我(wǒ)們所有的 MyISAM 引擎表的所有索引，以盡可能提高性能。

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

• bulk_insert_buffer_size （thread）

和key_buffer_size一(yī)樣，這個參數同樣也僅作用于使用 MyISAM存儲引擎，用來緩存批量插入數據的時候臨時緩存寫入數據。當我(wǒ)們使用如下(xià)幾種數據寫入語句的時候，會使用這個内存區域來緩存批量結構的數據以幫助批量寫入數據文件：

insert … select …
insert … values (…) ,(…),(…)…
load data infile… into… (非空表)

• innodb_buffer_pool_size（global）

當我(wǒ)們使用InnoDB存儲引擎的時候，innodb_buffer_pool_size 參數可能是影響我(wǒ)們性能的最爲關鍵的一(yī)個參數了，他用來設置用于緩存 InnoDB 索引及數據塊的内存區域大(dà)小(xiǎo)，類似于 MyISAM 存儲引擎的 key_buffer_size 參數，當然，可能更像是 Oracle 的 db_cache_size。簡單來說，當我(wǒ)們操作一(yī)個 InnoDB 表的時候，返回的所有數據或者去(qù)數據過程中(zhōng)用到的任何一(yī)個索引塊，都會在這個内存區域中(zhōng)走一(yī)遭。

和key_buffer_size 對于 MyISAM 引擎一(yī)樣，innodb_buffer_pool_size 設置了 InnoDB 存儲引擎需求最大(dà)的一(yī)塊内存區域的大(dà)小(xiǎo)，直接關系到 InnoDB存儲引擎的性能，所以如果我(wǒ)們有足夠的内存，盡可将該參數設置到足夠打，将盡可能多的 InnoDB 的索引及數據都放(fàng)入到該緩存區域中(zhōng)，直至全部。

我(wǒ)們可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算緩存命中(zhōng)率，并根據命中(zhōng)率來調整 innodb_buffer_pool_size 參數大(dà)小(xiǎo)進行優化。

• innodb_additional_mem_pool_size（global）

這個參數我(wǒ)們平時調整的可能不是太多，很多人都使用了默認值，可能很多人都不是太熟悉這個參數的作用。 innodb_additional_mem_pool_size 設置了InnoDB存儲引擎用來存放(fàng)數據字典信息以及一(yī)些内部數據結構的内存空間大(dà)小(xiǎo)，所以當我(wǒ)們一(yī)個MySQL Instance中(zhōng)的數據庫對象非常多的時候，是需要适當調整該參數的大(dà)小(xiǎo)以确保所有數據都能存放(fàng)在内存中(zhōng)提高訪問效率的。

這個參數大(dà)小(xiǎo)是否足夠還是比較容易知(zhī)道的，因爲當過小(xiǎo)的時候，MySQL 會記錄 Warning 信息到數據庫的 error log 中(zhōng)，這時候你就知(zhī)道該調整這個參數大(dà)小(xiǎo)了。

• innodb_log_buffer_size （global）

這是 InnoDB 存儲引擎的事務日志(zhì)所使用的緩沖區。類似于 Binlog Buffer，InnoDB 在寫事務日志(zhì)的時候，爲了提高性能，也是先将信息寫入 Innofb Log Buffer 中(zhōng)，當滿足 innodb_flush_log_trx_commit 參數所設置的相應條件（或者日志(zhì)緩沖區寫滿）之後，才會将日志(zhì)寫到文件（或者同步到磁盤）中(zhōng)。可以通過 innodb_log_buffer_size 參數設置其可以使用的最大(dà)内存空間。
注：innodb_flush_log_trx_commit 參數對 InnoDB Log 的寫入性能有非常關鍵的影響。該參數可以設置爲0，1，2，解釋如下(xià)：

0：log buffer中(zhōng)的數據将以每秒一(yī)次的頻(pín)率寫入到log file中(zhōng)，且同時會進行文件系統到磁盤的同步操作，但是每個事務的commit并不會觸發任何log buffer 到log file的刷新或者文件系統到磁盤的刷新操作；
1：在每次事務提交的時候将log buffer 中(zhōng)的數據都會寫入到log file，同時也會觸發文件系統到磁盤的同步；
2：事務提交會觸發log buffer 到log file的刷新，但并不會觸發磁盤文件系統到磁盤的同步。此外(wài)，每秒會有一(yī)次文件系統到磁盤同步操作。

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

• innodb_max_dirty_pages_pct （global）

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

但是，如果這個比例值過大(dà)，當數據庫 Crash 之後重啓的時間可能就會很長，因爲會有大(dà)量的事務數據需要從日志(zhì)文件恢複出來寫入數據文件中(zhōng)。同時，過大(dà)的比例值同時可能也會造成在達到比例設定上限後的 flush 操作“過猛”而導緻性能波動很大(dà)。

注：以上取值範圍僅僅隻是我(wǒ)的根據以往遇到的數據庫場景所得到的一(yī)些優化經驗值，并不一(yī)定适用于所有場景，所以在實際優化過程中(zhōng)還需要大(dà)家自己不斷的調整分(fēn)析，也歡迎大(dà)家随時通過 Mail 與我(wǒ)聯系溝通交流優化或者是架構方面的技術，一(yī)起探讨相互學習。