淺談MySQL數據庫性能優化

MySQL數據庫是 IO 密集型的程序，和其他數據庫一樣，主要功能就是數據的持久化以及數據的管理工作。本文側重通過優化MySQL 數據庫緩存參數如查詢緩存，表緩存，日志緩存，索引緩存，innodb緩存，插入緩存，以及連接參數等方式來對MySQL數據庫進行優化。

緩存參數

這裡先引用一句話，從內存中讀取一個數據的時間消耗是微秒級別，而從普通硬盤上讀取是毫秒級別，二者相差3個數量級。可見，想對MySQL數據庫進行優化，合理調配緩存參數顯得更為直接

表緩存

相關參數： table_open_cache

指定表緩存的大小。每當MySQL訪問一個表時，如果在表緩沖區中還有空間，該表就被打開並放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值，如果發現open_tables等於table_cache，並且opened_tables在不斷增長，那麼就需要增加table_open_cache的值了。注意，不能盲目地把這個參數設置得很大，如果設置太大，會引起文件描述符不足，造成性能不穩定或者數據庫連接失敗。建議為512

查詢緩存

相關參數： query_cache_size / query_cache_type

QC（注：查詢緩存簡稱） 主要用來緩存 MySQL 中的 結果集，也就是一條SQL語句執行的結果集，所以僅僅只能針對select 語句。如果啟用了QC 功能，MySQL在接到select 請求後，如果該語句滿足QC的要求，MySQL 會直接根據HASH算法將接收到的select 語句以字符串方式進行hash，然後到QC中直接查找，如果已經在緩存中，該select 請求就會直接將數據返回，從而省略了後面所有的步驟（如 SQL語法解析，優化器優化以及存儲引擎請求數據等），極大的提高性能。

當然，QC也有一個致命的缺陷，就是當表中數據有變化時，所有引用到該表的 QC 緩存全部失效。所以，當數據變化非常頻繁的情況下，使用QC 反而得不償失。

QC 的使用需要多個參數配合，其中最為關鍵的是 query_cache_size 和 query_cache_type ，前者設置緩存記錄集的內存大小，後者設置在何場景下使用QC 。

在以往的經驗來看，中等規模的網站，query_cache_size 設置 256MB 足夠了。當然，還可以通過計算QC的命中率來進行調整。

 Qcache_hits / (Qcache_hits + Qcache_inserts)  * 100%

 query_cache_type有三種選擇：0（OFF，不使用QC），1（ON，默認使用QC ），2（DEMAND，默認不使用QC）。

為什麼加上“默認”？MySQL還支持動態使用緩存的SQL語法，如下：

 # 強制使用緩存
SELECT SQL_CACHE id FROM table

# 強制不使用緩存
SELECT SQL_NO_CACHE id FROM table

 日志緩存

相關參數：binlog_cache_size

用於在打開了二進制日志（binlog）記錄功能的環境中，是 MySQL 用來提高 binlog 的記錄效率而設計的一個在短時間內緩存binlog 數據的內存緩存。

如果數據庫中沒有大事務，寫入不是特別頻繁，2MB～4MB是一個合適的選擇。但是如果數據庫大事務較多，寫入比較頻繁，可適當加大。使用的時候，還可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設置的binlog_cache_size是否足夠，是否有大量的binlog_cache由於內存大小不夠而使用臨時文件來緩存了。

索引緩存

相關參數：key_buffer_size

這個是對MyISAM表性能影響最大的一個參數，用來設置用於緩存 MyISAM存儲引擎中索引文件的內存區域大小。如果有足夠的內存，這個緩存區域大小可以設為所有的 MyISAM表的索引大小的總和，即 data 目錄下所有*.MYI文件大小的總和。

注意，由於 MyISAM 引擎只會緩存索引塊到內存中，而不會緩存表數據庫塊。所以，查詢SQL語句一定要盡可能讓過濾條件都在索引中，以便使用到索引緩存來提高查詢效率。

計算索引緩存未命中的概率：

 Key_reads / Key_read_requests * 100%

插入緩存

相關參數：bulk_insert_buffer_size

用於使用 MyISAM引擎，用來緩存批量插入數據的時候臨時緩存寫入數據。當我們使用如下幾種數據寫入語句的時候，會使用這個內存區域來緩存批量結構的數據以幫助批量寫入數據文件，默認8M，建議不要超過32M

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

InnoDB緩存

相關參數：innodb_buffer_pool_size / innodb_additional_mem_pool_size

innodb_buffer_pool_size參數是影響InnoDB存儲引擎性能的最為關鍵的一個參數，設置用於緩存 InnoDB 索引及數據塊的內存區域大小，類似於 MyISAM 存儲引擎的 key_buffer_size 參數，當然，可能更像是 Oracle 的 db_cache_size。簡單來說，當操作一個 InnoDB 表的時候，返回的所有數據或者去數據過程中用到的任何一個索引塊，都會涉及到這個內存區域。

innodb_buffer_pool_size 參數設置了 InnoDB 存儲引擎需求最大的一塊內存區域的大小，直接關系到 InnoDB存儲引擎的性能，所以如果有足夠的內存，盡可能加大該參數的值，將盡可能多的 InnoDB 的索引及數據都放入到該緩存區域中。

當然，可以通過計算緩存命中率，並根據命中率來調整這個參數的大小：

  (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%

innodb_additional_mem_pool_size 參數用來設置 InnoDB 存儲的數據目錄信息和其它內部數據結構的內存池大小。隨著數據庫對象越來越多，需要適當調整該參數的大小以確保所有數據都能存放在內存中提高訪問效率的。這個參數的大小是相對穩定的，沒有必要預留非常大的值。如果InnoDB引擎用光了這個池內的內存，InnoDB引擎就開始從操作系統申請內存，並往MySQL錯誤日志寫警告信息。默認值是1MB，當發現錯誤日志中已經有相關的警告信息時，就應該適當的增加該參數的大小。

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

innodb_flush_log_trx_commit 參數對 InnoDB引擎日志的寫入性能有非常關鍵的影響。該參數可以設置為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 參數用來控制在 InnoDB 緩沖池（Buffer Pool） 中可以不用寫入數據文件中的髒頁（Dirty Page） 的比例（已經被修但還沒有從內存中寫入到數據文件的髒數據）。這個比例值越大，從內存到磁盤的寫入操作就會相對減少，所以能夠一定程度下減少寫入操作的磁盤IO。但是，如果這個比例值過大，當數據庫崩潰（Crash）之後重啟的時間可能就會很長，因為會有大量的事務數據需要從日志文件恢復出來寫入數據文件中。同時，過大的比例值同時可能也會造成在達到比例設定上限後的 flush 操作“過猛”而導致性能波動很大。如果這個參數設置過大，將會導致MySQL啟動時間過長，關閉時間也過長。

連接參數

MySQL數據庫操作是建立在MySQL數據庫連接的基礎上，所以提高MySQLl處理連接的能力，也是提高MySQL的性能的一個重要途經。

連接數量

相關參數：max_connections / back_log

max_connections參數設置MySQL的最大連接數，也就是允許同時連接的客戶數量。如果服務器的並發連接請求比較大，建議調高此值，以增加並行連接數量。但連接數越大，MySQL會為每個連接提供連接緩沖區，就會開銷越多的內存，服務器消耗的內存越多，可能會影響服務器性能，所以要根據服務器的配置適當調整該值，不能盲目提高設值。默認數值是100。

計算MySQL繁忙時處理連接的情況，建議值50% ~ 80%

max_used_connections / max_connections * 100%

back_log參數設置MySQL能暫存的連接數量。當MySQL在一個很短時間內收到非常多的連接請求時起作用。如果MySQL的連接數達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過back_log，將不被授予連接資源。設定back_log高於你的操作系統的限制是無效的。默認數值是50。

連接超時

相關參數：wait_timeout / interactive_timeout

服務器關閉連接之前等待活動的秒數。MySQL所支持的最大連接數是有限的，因為每個連接的建立都會消耗內存，因此我們希望MySQL 處理完相應的操作後，應該斷開連接並釋放占用的內存。如果你的MySQL Server有大量的閒置連接，他們不僅會白白消耗內存，而且如果連接一直在累加而不斷開，最終肯定會達到MySQL Server的連接上限數，這會報'too many connections'的錯誤。對於wait_timeout的值設定，應該根據系統的運行情況來判斷。在系統運行一段時間後，可以通過show processlist命令查看當前系統的連接狀態，如果發現有大量的sleep狀態的連接進程，則說明該參數設置的過大，可以進行適當的調整小些。建議120 ~ 300

連接檢查

相關參數：skip-name-resolve

skip-name-resolve參數用於禁止DNS的反向解析。MySQL默認開啟了DNS的反向解析，當有新的連接到來時，MySQL會解析連接主機的DNS，這就影響了連接速度。使用該參數也有一個代價，就是每次連接都要使用ip地址，就不能再使用localhost，改成127.0.0.1

配置建議值

根據以往經驗取值，僅供參考，不一定適用於所有場景，建議在生產環境中進一步分析調整

MySQL配置 建議值說明 table_open_cache 如果設置太大，會造成系統不穩定或者數據庫連接失敗，建議最大512 query_cache_type 沒有使用MyISAM引擎，建議0；否則建議1，如果寫入過於頻繁，建議2 query_cache_size 根據實際命中率進行調整，不需要太大，建議256MB binlog_cache_size 建議2MB ~ 4MB，事務較大且寫入頻繁可以適當調大，但不要超過32MB key_buffer_size 如果使用MyISAM，在內存允許的情況下，盡可能加大，參考值 512MB bulk_insert_buffer_size 如果經常性的需要使用批量插入數據，可以適當調大至32MB innodb_buffer_pool_size 如果使用InnoDB，在內存允許的情況下，可以設置50% ~ 80%內存 innodb_log_buffer_size 默認是1MB，數據庫操作頻繁的系統可適當增大至4MB ~ 16MB innodb_max_dirty_pages_pct 這個值越大，數據庫啟動時間和關閉時間越長，可以適當調大至90 max_connections 根據實際情況取值，過大反而影響性能。默認值100，建議128 ~ 512 back_log 默認數值是50，建議 128 ~ 256 wait_timeout 同時修改interactive_timeout，默認28800（8小時），建議120 ~ 300

可能用到的MySQL命令：

 # 查看當前MySQL運行狀態值
mysql> show global status like 'Thread_%';

# 查看當前MySQL配置信息
mysql> show global variables like '%binlog%';

 參考

http://blog.csdn.net/mycwq/article/details/16370525
http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter
http://database.51cto.com/art/201010/229939.htm
http://jackyrong.iteye.com/blog/781859