MySQL參數(shù)配置優(yōu)化

max_connections
Variable Scope：      Global
Dynamic Variable：  Yes
Default：                   151 （mysql5.5+）
Meaning：                 允許客戶端同時(shí)連接的最大數(shù)
默認(rèn)值以前是100,MySQL5.5+后151，但是默認(rèn)值對(duì)大部分應(yīng)用來說這都不夠。通過觀察Max_used_connections
狀態(tài)變量隨著時(shí)間的變化?？梢愿嬖V你服務(wù)器連接是不是在某個(gè)時(shí)間點(diǎn)有個(gè)尖峰。如果這個(gè)值達(dá)到了max_connections，說明客戶端至少被拒絕了一次。
建議值： 500+ （設(shè)置為你認(rèn)為正常情況下有300或者更多連接，則可以設(shè)置為500或更多）


thread_cache_size
Variable Scope：       Global
Dynamic Variable：   Yes
Default：                    0（mysql5.6.7-）
Meaning：                 有多少線程應(yīng)該緩存重用
其默認(rèn)值在mysql5.6.8+（autosized），根據(jù)如下公式得到：8 + (max_connections / 100)，其上限值為100.設(shè)置這個(gè)變量，可以通過觀察服務(wù)器一段時(shí)間的活動(dòng)，來計(jì)算一個(gè)有理有據(jù)的值。
觀察Threads_connected狀態(tài)變量并且找到它在一般情況下的最大值和最小值。例如：若Threads_connected狀態(tài)從150變化到175，可以設(shè)置線程緩存為75。但是不用設(shè)置的非常大，因?yàn)楸３执罅?br /> 等待連接的空閑線程并沒有什么真正的用處。也可以觀察Threads_created狀態(tài)隨時(shí)間的變化。如果這個(gè)值很大或一直增長，這是另一個(gè)線索，告訴你可能需要調(diào)大thread_cache_size變量。
Threads_cached來查看有多少線程已經(jīng)在緩存中了。
建議值：50-100


table_open_cache
Variable Scope:         Global
Dynamic Variable:     Yes


Default:                      400( mysql5.6.7- )
Meaning:                    所有線程打開表的數(shù)量
從官方文檔看出在MySQL5.6.8+開始默認(rèn)值為2000，就能簡(jiǎn)單的判斷出原來默認(rèn)值是不夠的?？梢酝ㄟ^觀察Opened_tables其值及其一段時(shí)間的變化來檢查該變量。如果看到Opened_tables的值很大
并且又不經(jīng)常執(zhí)行FLUSH TABLES（執(zhí)行其命令強(qiáng)制所有的表重新關(guān)閉且打開），那么可能你應(yīng)該增加該變量的值。
建議值： 4096（有另一種說法：這個(gè)值從max_connections的10倍開始設(shè)置）


open_files_limit
Variable Scope：       Global
Dynamic Variable：   No


Default：                    0 （mysql5.6.7-）
Meaning：                 操作系統(tǒng)允許mysqld服務(wù)打開的文件數(shù)。
其默認(rèn)值在mysql5.6.8+（autosized），根據(jù)如下公式得到：
1) 10 + max_connections + (table_open_cache * 2)
2) max_connections * 5
3) open_files_limit value specified at startup, 5000 if none


要知道每個(gè)MyISAM表打開需要2個(gè)文件句柄；每個(gè)客戶端的連接也是一個(gè)文件句柄。有效的open_files_limit的值是基于系統(tǒng)啟動(dòng)時(shí)所指定的值和max_connections，table_open_cache有關(guān)聯(lián)。
建議值：65535 （其值在大多操作系統(tǒng)是最安全的）


table_definition_cache
Variable Scope ：       Global
Dynamic Variable ：   Yes


Default ：                    400（mysql5.6.7-）
Meaning：                   緩存表定義的的數(shù)量（以.frm結(jié)尾的文件）
其默認(rèn)值在mysql5.6.8+（autosized），根據(jù)如下公式得到：400 + (table_open_cache / 2)   其上限值為：2000。?？梢园裻able_definition_cache 設(shè)置得足夠高，以緩存所有的表定義。
除非有上萬張表，否則這可能是最簡(jiǎn)單的方法。
建議值：  根據(jù)真正的數(shù)據(jù)庫中表的數(shù)量（例如：數(shù)據(jù)庫實(shí)例有1000張表，可以將其設(shè)置為1000+）


back_log
Variable Scope:           Global
Dynamic Variable:       No


Default:                        50( mysql5.6.5- )
Meaning:                     在很短時(shí)間內(nèi)，可以有多少個(gè)請(qǐng)求鏈接在堆棧中等待被處理。
其默認(rèn)值在mysql5.6.6+（autosized），根據(jù)如下公式得到：  50 + (max_connections / 5) 其上限值為：900。
如果每秒的連接數(shù)很多，可以將其值調(diào)大。其值和OS的TCP/IP鏈接有關(guān)聯(lián)，和內(nèi)核參數(shù)net.ipv4.tcp_max_syn_backlog的值相關(guān)，back_log的值不能大于其值。
建議值：2048 


max_allowed_packet
Variable Scope：         Global
Dynamic Variable：     Yes


Default:                         1MB（mysql5.6.5-）
Meaning:                       這個(gè)設(shè)置防止服務(wù)器發(fā)送太大的包，也會(huì)控制多大的包可以被接受。
其默認(rèn)值在mysql5.6.6+為4MB，其默認(rèn)值可能太小了，但設(shè)置太大也可能有危險(xiǎn)。如果設(shè)置太小，有時(shí)復(fù)制上會(huì)出現(xiàn)問題，通常表現(xiàn)為備庫不能接收主庫發(fā)過來的復(fù)制數(shù)據(jù)。使用mysql和mysqldump
客戶端程序都可以指定其值的大小。
建議值： 16MB


max_connect_errors
Variable Scope：          Global
Dynamic Variable：      Yes


Default：                        100（mysql5.6.6+）
Meaning：                     最大的連接錯(cuò)誤數(shù)
如果有時(shí)網(wǎng)絡(luò)短暫抽風(fēng)了，或者應(yīng)用配置出現(xiàn)錯(cuò)誤，或另有問題，如權(quán)限，在短暫的時(shí)間內(nèi)不斷地嘗試鏈接，客戶端可能被列入黑名單，然后將無法連接，知道再次刷新主機(jī)緩存（FLUSH HOSTS）。
這個(gè)選項(xiàng)的默認(rèn)設(shè)置太小了，很容易導(dǎo)致問題。你也許希望增加這個(gè)值，實(shí)際上，如果知道服務(wù)器可以充分抵御蠻力攻擊，可以把這個(gè)值設(shè)置的非常大，以有效地禁用主機(jī)黑名單。這個(gè)選項(xiàng)也就是
所謂的可以防止暴力破解。
建議值： 1000000 （其值為Percona 給出的建議值，但是應(yīng)該確定其主機(jī)的已有抵御蠻力攻擊的能力）


skip_name_resolve
Variable Scope：          Global
Dynamic Variable：      No


Default：                       OFF
Meaning：                     DNS查找
這個(gè)選項(xiàng)禁用了另一個(gè)網(wǎng)絡(luò)相關(guān)和鑒權(quán)認(rèn)證的陷進(jìn)：DNS查找。DNS是MySQL連接過程中的一個(gè)薄弱環(huán)節(jié)。當(dāng)連接服務(wù)器時(shí)，它試圖確定連接和使用的主機(jī)的主機(jī)名，作為身份驗(yàn)證憑據(jù)的一部分。
（就是說，你的憑據(jù)是用戶名，主機(jī)名，以及密碼，并不只是用戶名和密碼）但是驗(yàn)證主機(jī)來源，服務(wù)器需要執(zhí)行DNS的正向和反向查找。要是DNS有問題就悲劇了，在某些時(shí)間點(diǎn)這是必然的事。
為了避免這種情況，我們強(qiáng)烈建議設(shè)置這個(gè)選項(xiàng)，在驗(yàn)證時(shí)關(guān)閉DNS查找，這樣即快又安全。
建議值： ON


log_bin
Variable Scope：           Global
Dynamic Variable：       No


Meaning：                     是否開啟binlog
開啟此選項(xiàng)用來支持復(fù)制和時(shí)間點(diǎn)恢復(fù)。
建議值： 設(shè)置其值為mysql-bin來避免其默認(rèn)生成的文件名（也就是與主機(jī)名無關(guān)）


sync_binlog
Variable Scope：           Global
Dynamic Variable：       Yes


Default：                         0
Meaning：                      控制MySQL怎么刷新二進(jìn)制日志到磁盤
默認(rèn)值為0，意味著MySQL并不刷新，有操作系統(tǒng)自己決定什么時(shí)候刷新緩存到持久化設(shè)備。如果這個(gè)值比0大，它指定了兩次刷新到磁盤的動(dòng)作之間間隔多少次二進(jìn)制日志寫操作
（如果autocommit被設(shè)置了，每個(gè)獨(dú)立的語句都是一次寫，否則就是一個(gè)事務(wù)一次寫）。如果沒有設(shè)置sync_binlog為1，那么崩潰以后可能導(dǎo)致二進(jìn)制日志沒有同步事務(wù)數(shù)據(jù)。這可以輕易地導(dǎo)致復(fù)制中斷，
并且使得及時(shí)恢復(fù)變得不可能。無論如何，可以把這個(gè)值設(shè)置為1來獲得安全的保障。這樣就會(huì)要求MySQL把二進(jìn)制日志和事務(wù)日志兩個(gè)文件刷新到不同的位置。這可能需要磁盤尋道，相對(duì)來說是個(gè)很慢的操作。
建議值：   1


expire_log_days
Variable Scope ：           Global
Dynamic Variable：        Yes


Default：                         0
Meaning：                      服務(wù)器在指定的天數(shù)之后清理舊的二進(jìn)制日志
如果啟用了二進(jìn)制日志，應(yīng)該打開這個(gè)選項(xiàng)，可以讓服務(wù)器在指定的天數(shù)之后清理舊的二進(jìn)制日志。如果不啟用，最終服務(wù)器的空間會(huì)被耗盡，導(dǎo)致服務(wù)器卡住或崩潰。
建議值：  7~14


tmp_table_size和max_heap_table_size
這兩個(gè)設(shè)置控制使用Memory引擎的內(nèi)存臨時(shí)表能使用多大的內(nèi)存。如果隱式內(nèi)存臨時(shí)表的大小超過這兩個(gè)設(shè)置的值，將會(huì)被轉(zhuǎn)換為MyISAM表，所以它的大小可以繼續(xù)增長。（隱式臨時(shí)表是一種并非由自己創(chuàng)建，
而是服務(wù)器創(chuàng)建，用于保存執(zhí)行行中的查詢的中間結(jié)果的表）應(yīng)該簡(jiǎn)單地把這兩個(gè)變量設(shè)為同樣的值。但是要謹(jǐn)防這個(gè)變量太大了，臨時(shí)表最好呆在內(nèi)存里，但是如果它們被撐得很大，實(shí)際上還是讓它們使用
磁盤比較好，否則可能會(huì)讓服務(wù)器內(nèi)存溢出。假設(shè)查詢語句沒有創(chuàng)建龐大的臨時(shí)表（通常可以通過合理的索引和查詢?cè)O(shè)計(jì)來避免），那把這些變量設(shè)大一點(diǎn)，免得把內(nèi)存臨時(shí)表轉(zhuǎn)換為磁盤臨時(shí)表。這個(gè)過程可以
在SHOW PROCESSLIST中看到。使用臨時(shí)表的情況可以通過狀態(tài)變量Created_tmp_tables 和 Created_tmp_disk_tables 來監(jiān)控。
建議值： 設(shè)置兩個(gè)變量為同樣的值（這個(gè)大小要根據(jù)自己的SQL查詢級(jí)別及SQL語句的優(yōu)化情況）


query_cache_size
Variable Scope：          Global
Dynamic Variable ：     Yes
Default：                       0（mysql5.6.7-）
Meaning:                       緩存查詢結(jié)果的內(nèi)存大小
查詢緩存使用的總內(nèi)存空間，單位是字節(jié)。這個(gè)值必須是1024的整數(shù)倍，否則MySQL實(shí)際分配的數(shù)據(jù)會(huì)和你指定的略有不同。
建議值：<512MB




sort_buffer_size
Variable Scope:              Global, Session
Dynamic Variable:          Yes
Meaning:                        查詢需要做排序操作時(shí)為該緩存分配內(nèi)存大小
MySQL只會(huì)在有查詢需要做排序操作時(shí)才會(huì)為該緩存分配內(nèi)存，然后，一旦需要排序，MySQL就會(huì)立刻分配該參數(shù)指定大小的全部內(nèi)存，而不管該排序是否需要這么大的內(nèi)存。 通過SHOW GLOBAL STATUS查看如
Sort_merge_passes/s 有很多，可以考慮增加sort_buffer_size的值，以來加快order by 或 group by語句的查詢速度（其排序或分組操作已不能查詢優(yōu)化或索引優(yōu)化）。如果查詢必須使用一個(gè)更大的排序緩存
才能更好的執(zhí)行，可以考慮session級(jí)別的設(shè)置其值。
建議值：1MB


join_buffer_size
Variable Scope：            Global, Session
Dynamic Variable ：       Yes


Meaning：                       設(shè)置使用連接查詢緩存的大小
此選項(xiàng)可以提高沒有使用索引的連接查詢的性能。全局的建議不要設(shè)置太大，可以動(dòng)態(tài)設(shè)置session級(jí)別的值。
建議值：  8MB


read_rnd_buffer_size
Variable Scope ：              Global, Session
Dynamic Variable ：          Yes
Default：                            256kb
Meaning：                          讀取排序行的緩存的大小
MySQL只會(huì)在有查詢需要時(shí)才會(huì)為該緩存分配內(nèi)存，并且只會(huì)分配需要的內(nèi)存大小而不是全部指定的大小。如果增加其值，可以提高order by 的操作性能。
建議值： 16MB


key_buffer_size
Variable Scope:                    Global
Dynamic Variable :               Yes


Default:                                 8MB
Meaning:                               MyISAM 表索引緩存的大小
如果MySQL server的表全部是或者說大多為MyISAM存儲(chǔ)引擎的，可以考慮將其設(shè)置為OS內(nèi)存的30%。其緩存僅僅緩存的是索引塊，而不緩存數(shù)據(jù)。
建議值：  根據(jù)其數(shù)據(jù)庫中表的存儲(chǔ)引擎的類型來作為參考


myisam_sort_buffer_size
Variable Scope：                   Global, Session
Dynamic Variable ：              Yes
Default：                                8MB
Meaning：                             排序MyISAM的索引緩存的大小
當(dāng)REPAIR TABLE或者創(chuàng)建索引，修改索引時(shí)操作（CREATE INDEX，ALTER TABLE）時(shí)，分配給用于其MyISAM索引排序的大小。
建議值:  8MB~256MB


innodb_buffer_pool_size
Variable Scope :                    Global
Dynamic Variable:                 No
Default：                               128MB
Meaning：                             InnoDB緩沖池的大小
如果大部分都是InnoDB表，InnoDB緩沖池或許比其他任何東西更需要內(nèi)存。InnoDB緩沖池并不僅僅緩存索引：它還會(huì)緩存行的數(shù)據(jù)，自適應(yīng)哈希索引，插入緩沖，鎖，以及其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)。
建議值：80%+ 物理內(nèi)存


innodb_buffer_pool_instances


Variable Scope ：                  Global
Dynamic Variable ：              No
Default：                                1（mysql5.6.5-）
Meaning：                              InnoDB緩存池的實(shí)例個(gè)數(shù)
從InnoDB1.0.x版本開始，允許有多個(gè)緩沖池實(shí)例。每個(gè)頁根據(jù)哈希值平均分配到不同的緩沖池實(shí)例中。這樣做的好處是減少數(shù)據(jù)庫內(nèi)部資源競(jìng)爭(zhēng)，增加數(shù)據(jù)庫的并發(fā)處理能力?？梢酝ㄟ^參數(shù)
innodb_buffer_pool_instances來配置。在MySQL5.6.6+其默認(rèn)值為：（autosized），除了Window 32bit其值是根據(jù)innodb_buffer_pool_size的大小動(dòng)態(tài)得到，其它默認(rèn)值為8.
建議值：4+ （mysql5.5+）


innodb_log_buffer_size
Variable Scope：                   Global
Dynamic Variable ：              No
Default：                                8MB
Meaning：                              InnoDB寫日志文件到磁盤上的緩沖大小
一個(gè)較大的日志緩沖，可以使一個(gè)大的事務(wù)在commit之前不用將log寫到磁盤上。同樣的在update，delete，Insert
很多行時(shí)，也可以減少磁盤I/O的調(diào)用。
建議值：8~128MB


innodb_flush_log_at_trx_commit


Variable Scope：                   Global
Dynamic Variable：               Yes
Default：                                1
Meaning：                              控制日志緩沖刷新的頻繁程度
日志緩沖必須刷新到持久化存儲(chǔ)，以確保提交的事務(wù)完全被持久化了。如果和持久相比更在乎性能，可以修改
innodb_flush_log_at_trx_commit變量來控制日志緩沖刷新的頻繁程度。可能的設(shè)置如下：
0
把日志緩沖寫到日志文件，并且每秒刷新一次，但是事務(wù)提交時(shí)不做任何事情。
1
將日志緩沖寫到日志文件，并且每次事務(wù)提交都刷新到持久化存儲(chǔ)。這是默認(rèn)的（并且是最安全的）設(shè)置，該設(shè)置
能保證不會(huì)丟失任何已經(jīng)提交的事務(wù)，除非磁盤或者OS是’偽‘刷新。
2
每次提交時(shí)把日志緩沖寫到日志文件，但是并不刷新。Innodb每秒做一次刷新。0與2最重要的不同是，如果MySQL
進(jìn)程“掛了”，2不會(huì)丟失事務(wù)。
建議值： 1


innodb_log_file_size


Variable Scope：                   Global
Dynamic Variable ：              No
Meaning：                              指定每個(gè)重做日志文件的大小
重做日志文件的大小設(shè)置對(duì)于InnoDB存儲(chǔ)引擎的性能有著非常大的影響。一方面重做日志文件不能設(shè)置的太大，如果設(shè)置得很大，在恢復(fù)時(shí)可能需要很長的時(shí)間；另一方面又不能設(shè)置太小了，
否則可能導(dǎo)致一個(gè)事務(wù)的日志需要多次切換重做日志文件。
建議值： 根據(jù)自己能接受的方面（更好的性能or更短的恢復(fù)時(shí)間）決定


innodb_io_capacity
Variable Scope：                    Global
Dynamic Variable：                Yes
Default：                                  200
InnoDB曾經(jīng)在代碼里寫死了假設(shè)服務(wù)器運(yùn)行在每秒100個(gè)I/O操作的單硬盤上。默認(rèn)值很糟糕。現(xiàn)在可以告訴InnoDB服務(wù)器有多大的I/O能力。有時(shí)需要把這個(gè)值設(shè)置得相當(dāng)高（像SSD這樣極快的存儲(chǔ)
設(shè)備上需要設(shè)置為上萬）才能穩(wěn)定地刷新臟頁。
建議值： 根據(jù)server的I/O能力有關(guān)系


innodb_read_io_threads和innodb_write_io_threads
這些選項(xiàng)控制有多少后臺(tái)線程可以被I/O操作使用。最近版本的MySQL里，默認(rèn)值4個(gè)讀線程和4個(gè)寫線程，對(duì)大部分
服務(wù)器這都足夠了，尤其是MySQL5.5里面可以用操作系統(tǒng)原生的異步I/O以后。
建議值：各為4（即默認(rèn)值）


innodb_log_files_in_group


Variable Scope：                    Global
Dynamic Variable：                No
Default ：                                2
Meaning：                               每組InnoDB重做日志文件的個(gè)數(shù)
建議值：                                    2


innodb_file_per_table


Variable Scope ：                   Global
Dynamic Variable ：               Yes


Default：                                 OFF（mysql5.6.5-）
Meaning：                              控制InnoDB表空間存儲(chǔ)形式
其默認(rèn)值在mysql5.6.6+后為ON。開啟此選項(xiàng)后，關(guān)于InnoDB表的數(shù)據(jù)和索引單獨(dú)存儲(chǔ)在自己的表空間中（.ibd結(jié)尾的文件）。否則，存儲(chǔ)在系統(tǒng)的表空間中（ibdata）。
建議值： ON