淺談MySQL 資料庫性能優化

　　緩存參數

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

　　表緩存

　　相關參數： table_open_cache

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

　　查詢緩存

　　相關參數： query_cache_size / query_cache_type

　　當然，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

如果全部使用innodb引擎，建議為0，如果使用myisam引擎，建議為1，如果寫入過于頻繁，建議為2

query_cache_size

根據實際命中率進行調整，不需要太大，建議256mb

binlog_cache_size

一般環境2mb～4mb是一個合适的選擇，事務較大且寫入頻繁的資料庫環境可以适當調大，但不要超過32mb

key_buffer_size

如果不使用myisam引擎，可以不調整。如果使用myisam，在記憶體允許的情況下，盡可能加大，參考值 512mb

bulk_insert_buffer_size

如果經常性的需要使用批量插入的特殊語句（上面有說明）來插入資料，可以适當調大至32mb

innodb_buffer_pool_size

如果不使用innodb引擎，可以不用調整；如果使用innodb，在記憶體允許的情況下，可以設定50% ~ 80%記憶體

innodb_log_buffer_size

預設是1mb，資料庫操作頻繁的系統可适當增大至4mb ~ 16mb

innodb_max_dirty_pages_pct

這個值越大，資料庫啟動時間和關閉時間越長，可以适當調大至90

max_connections

根據實際情況取值，設定過大反而影響性能。預設值是100，建議256 - 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%';

最新内容請見作者的github頁：http://qaseven.github.io/