MySQL資料庫伺服器端核心參數詳解和推薦配置

MySQL手冊上也有伺服器端參數的解釋，以及參數值的相關說明資訊，現針對我們大家重點需要注意、需要修改或影響效能 的伺服器端參數，作其用處的解釋和如何配置參數值的推薦，此事情拖了不少時間，為方便大家幫忙錯誤修正（註：總是斷斷續續寫的，可能存在筆誤），先以文章的方 式發布到網站，後續確定沒問題，將會通過新浪微博的微盤提供PPT下載，方便學習和參考。
l lower_case_table_names
Linux或類Unix平台，對檔案名稱大小寫敏感，也即對資料庫、表、預存程序等對象名稱大小寫敏 感，為減少開發人員的開發成本，為此推薦大家設定該參數使對象名稱都自動轉換成小寫；
l max_connect_errors
max_connect_errors預設值為10，也即mysqld線程沒重新啟動過，一台物理伺服器只要串連 異常中斷累計超過10次，就再也無法串連上mysqld服務，為此建議大家設定此值至少大於等於10W； 若異常中斷累計超過參數設定的值，有二種解決辦法，執行命令：FLUSH HOSTS;或者重新啟動mysqld服務；

l interactive_timeout and wait_timeout
u interactive_timeout
處於互動狀態串連的活動被伺服器端強制關閉，而等待的時間，單位：秒；
u wait_timeout
與伺服器端無互動狀態的串連，直到被伺服器端強制關閉而等待的時間，此參數只對基於TCP/IP或基於 Socket通訊協定建立的串連才有效，單位：秒；
u 推薦設定
interactive_timeout = 172800
wait_timeout = 172800
l transaction-isolation and binlog-format
u transaction-isolation
可供設定的值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、
SERIALIZABLE，預設的值為： REPEATABLE-READ，交易隔離等級設定的不同，對二進位日誌登記格
式影響非常大，詳細資料可見文章解讀MySQL事務的隔離等級和日誌登記模式選擇技巧；
u binlog-format
複製的模式，可供設定的值：STATEMENT、ROW、MIXED（註：5.0.*只有命令列式複製），
5.1.*版本預設設定：MIXED；
u 推薦配置
① 唯讀為主的業務應用情境
transaction-isolation = read-committed
binlog-format = mixed #5.1.*版本，5.0.*只能設定為 statement
① 非唯讀為主的業務應用情境
transaction-isolation = repeatabled-read
binlog-format = mixed #5.1.*版本，5.0.*只能設定為 statement
l event_scheduler
事務調度預設是關閉狀態，也推薦源碼編譯的版本可不編譯進來，以及實際生產環境保持預設禁用 狀態，當真正需要用的時候，可以臨時開啟，命令：SET GLOBAL event_scheduler=1;
l skip_external_locking
外部鎖，也即作業系統所實施的鎖，只對MyISAM引擎有效，且容易造成死結發生，為此我們一律禁用；
l innodb_adaptive_hash_index
InnoDB引擎會根據資料的訪問頻繁度，把表的資料逐漸緩到記憶體，若是一張表的資料大量緩衝在 記憶體中，則使用散列索引（註：Hash Index）會更高效。InnoDB內有Hash Index機制，監控資料的訪 問情況，可以自動建立和維護一個Hash Index，以提供訪問效率，減少記憶體的使用；
l innodb_max_dirty_pages_pct
InnoDB主線程直接更新Innodb_buffer_pool_size中存在的資料，並且不即時刷回磁碟，而是等待 相關的處罰事件發生，則允許緩衝空間的資料量不即時刷回磁碟的最大百分比。比例設定較小，有利於 減少mysqld服務出現問題的時候恢復，缺點則是需要更多的物理I/O，為此我們必鬚根據業務特點 和可承受範圍進行一個折中，一般範圍建議設定為5%~90%，像我們SNS遊戲行業的寫非常厲害，綜合 各方面因素，設定為20%；
l innodb_commit_concurrency
含義：同一時刻，允許多少個線程同時提交InnoDB事務，預設值為0，範圍0-1000。
0 — 允許任意數量的事務在同一時間點提交；
N>0 — 允許N個事務在同一時間點提交；
注意事項：
① mysqld提供服務時，不許把 innodb_commit_concurrency 的值從0改為非0，或非0的值改為0；

② mysqld提供時，允許把 innodb_commit_concurrency 的值N>0改為M，且M>0；
l innodb_concurrency_tickets
含義：
同一時刻，能訪問InnoDB引擎資料的線程數，預設值為500，範圍1-4294967295。
補充說明：當訪問InnoDB引擎資料的線程數達到設定的上線，線程將會被放到隊列中，等待其他線程釋放ticket。
建議：
MySQL資料庫服務最大線程串連數參數max_connections，一般情況下都會設定在128-1024的範圍，再結合實際業務可能的最大事務並發度，innodb_concurrency_tickets保持預設值一般情況下足夠。
l innodb_fast_shutdown and innodb_force_recovery
innodb_fast_shutdown：
含義：設定innodb引擎關閉的方式，預設值為：1，正常關閉的狀態；
0 — mysqld服務關閉前，先進行資料完全的清理和插入緩衝區的合併作業，若是髒資料
較多或者伺服器效能等因素，會導致此過程需要數分鐘或者更長時間；
1 — 正常關閉mysqld服務，針對innodb引擎不做任何其他的操作；
2 — 若是mysqld出現崩潰，立即刷交易記錄到磁碟上並且冷關閉mysqld服務；沒有提交
的事務將會丟失，但是再啟動mysqld服務的時候會進行交易回復恢複；
innodb_force_recovery：
含義：
mysqld服務出現崩潰之後，InnoDB引擎進行復原的模式，預設值為0，可設定的值0~6；
提示：
只有在需要從錯誤狀態的資料庫進行資料備份時，才建議設定innodb_force_recovery的值大於0。 若是把此參數作為安全選項，也可以把參數的值設定大於0，防止InnoDB引擎的資料變更，設定不同值的作用：
0 — 正常的關閉和啟動，不會做任何強迫恢複操作；
1 — 跳過錯誤頁，讓mysqld服務繼續運行。跳過錯誤索引記錄和儲存頁，嘗試用
SELECT * INOT OUTFILE ‘../filename' FROM tablename;方式，完成資料備份；
2 — 阻止InnoDB的主線程運行。清理操作時出現mysqld服務崩潰，則會阻止資料恢複操作；
3 — 恢複的時候，不進行交易回復；
4 — 阻止INSERT緩衝區的合併作業。不做合併作業，為防止出現mysqld服務崩潰。不計算
表的統計資訊
5 — mysqld服務啟動的時候不檢查復原日誌：InnoDB引擎對待每個不確定的事務就像提交
的事務一樣；
6 — 不做交易記錄前滾恢複操作；
推薦的參數組合配置：
innodb_fast_shutdown = 1
#若是機房條件較好可設定為0（雙路電源、UPS、RAID卡電池和供電系統穩定性）
innodb_force_recovery =0
#至於出問題的時候，設定為何值，要視出錯的原因和程度，對資料後續做的操作
l innodb_additional_mem_pool_size
含義：開闢一片記憶體用於緩衝InnoDB引擎的資料字典資訊和內部資料結構（比如：自適應HASH索引結構）；
預設值：build-in版本預設值為：1M；Plugin-innodb版本預設值為：8M；
提示：若是mysqld服務上的表對象數量較多，InnoDB引擎資料量很大，且 innodb_buffer_pool_size的值設定 較大，則應該適當地調整innodb_additional_mem_pool_size的值。若是出現緩衝區的記憶體不足，則會直接向作業系統申請記憶體分 配，並且會向MySQL的error log檔案寫入警告資訊；
l innodb_buffer_pool_size
含義：開闢一片記憶體用於緩衝InnoDB引擎表的資料和索引；
預設值：曆史預設值為：8M，現在版本預設值為：128M；
參數最大值：受限於CPU的架構，支援32位還是支援64位，另外還受限於作業系統為32位還是64位；
提示：
innodb_buffer_pool_size的值設定合適，會節約訪問表對象中資料的物理IO。官方手冊上建議專用 的資料庫伺服器，可考慮設定為實體記憶體總量的80%，但是個人建議要看物理伺服器的實體記憶體總量，以及考慮： 是否只使用InnoDB引擎、mysqld內部管理佔用的記憶體、最大線程串連數和暫存資料表等因素，官方提供的80%值作為一個參考，舉而個例子方便大家作決 定（前提：物理伺服器為mysqld服務專用，且只用InnoDB引擎,假設資料量遠大於實體記憶體）：
1).記憶體配置：24G 則 innodb_buffer_pool_size=18G
1).記憶體配置：32G 則 innodb_buffer_pool_size=24G
出現下列哪些情況，則可以考慮減小innodb_buffer_pool_size的值：
1).出現實體記憶體的競爭，可能導致作業系統的分頁；
2).InnoDB預分配額外的記憶體給緩衝區和結構管理，當分配的總記憶體量超過innodb_buffer_pool_size值的10%；
3).地址空間要求必須為連續的，在windows系統有一個嚴重問題，DLL需要載入在特定的地址空間；
4).初始化緩衝區的時間消耗，與緩衝區的大小成正比。官方提供的資料 Linux X86 64位系統 初始化 innodb_buffer_pool_size=10G 大概需要6秒鐘；
l innodb_flush_log_at_trx_commit AND sync_binlog
innodb_flush_log_at_trx_commit = N：
N=0 – 每隔一秒，把交易記錄緩衝區的資料寫到記錄檔中，以及把記錄檔的資料重新整理到磁碟上；
N=1 – 每個事務提交時候，把交易記錄從緩衝區寫到記錄檔中，並且重新整理記錄檔的資料到磁碟上；

N=2 – 每事務提交的時候，把交易記錄資料從緩衝區寫到記錄檔中；每隔一秒，重新整理一次記錄檔，但不一定重新整理到磁碟上，而是取決於作業系統的調度；
sync_binlog = N：
N>0 — 每向二進位記錄檔寫入N條SQL或N個事務後，則把二進位記錄檔的資料重新整理到磁碟上；
N=0 — 不主動重新整理二進位記錄檔的資料到磁碟上，而是由作業系統決定；
推薦配置組合：
N=1,1 — 適合資料安全性要求非常高，而且磁碟IO寫能力足夠支援業務，比如儲值消費系統；
N=1,0 — 適合資料安全性要求高，磁碟IO寫能力支援業務不富餘，允許備庫落後或無複製；
N=2,0或2,m(0<m<100) — 適合資料安全性有要求，允許丟失一點交易記錄，複製架構的延遲也能接受；
N=0,0 — 磁碟IO寫能力有限，無複製或允許複寫延遲稍微長點能接受，例如：日誌性登記業務；
l innodb_file_per_table
啟用單資料表空間，減少共用資料表空間維護成本，減少空閑磁碟空間釋放的壓力。另外，大資料量情況下 的效能，也會有效能上的提升，為此建議大家使用獨立資料表空間 代替 共用資料表空間的方式；
l key_buffer_size
key_buffer_size只能緩衝MyISAM或類MyISAM引擎的索引 資料，而innodb_buffer_pool_size不僅能緩衝索引資料，還能緩衝中繼資料，但是對於我們只使用InnoDB引擎的資料庫系統而言，此 參數值也不能設定過於偏小，因為暫存資料表可能會使用到此金鑰快取區空間，索引緩衝區推薦：64M；
l query_cache_type and query_cache_size
n query_cache_type=N
N=0 —- 禁用查詢快取的功能；
N=1 —- 啟用產訊緩衝的功能，緩衝所有符合要求的查詢結果集，除SELECT SQL_NO_CACHE..， 以及不符合查詢快取設定的結果集外；
N=2 —- 僅僅緩衝SELECT SQL_CACHE …子句的查詢結果集，除不符合查詢快取設定的結果集外；
n query_cache_size
查詢快取設定多大才是合理？至少需要從四個維度考慮：
① 查詢快取區對DDL和DML語句的效能影響；
② 查詢快取區的內部維護成本；
③ 查詢快取區的命中率及記憶體使用量率等綜合考慮
④ 業務類型