MySQL參數(shù)優(yōu)化對(duì)于不同的網(wǎng)站，及其在線(xiàn)量，訪(fǎng)問(wèn)量，帖子數(shù)量，網(wǎng)絡(luò)情況，以及機(jī)器硬件配置都有關(guān)系，優(yōu)化不可能一次性完成，需要不斷的觀(guān)察以及調(diào)試，才有可能得到最佳的效果。
MySQL的大連接數(shù)，如果服務(wù)器的并發(fā)連接請(qǐng)求量較大，建議調(diào)高此值，以增加并行連接數(shù)量，當(dāng)然這建立在機(jī)器能支撐的情況下，因?yàn)槿绻B接數(shù)越多，MySQL回味每個(gè)連接提供連接緩沖區(qū)，就會(huì)開(kāi)銷(xiāo)越多的內(nèi)存，所以要適當(dāng)調(diào)整該值，不能盲目提高設(shè)值。
數(shù)值過(guò)小經(jīng)常會(huì)出現(xiàn)ERROR 1040：Too mant connetcions錯(cuò)誤，可以通過(guò)mysql>show status like ‘connections'；通配符來(lái)查看當(dāng)前狀態(tài)的連接數(shù)量（試圖連接到MySQL（不管是否連接成功）的連接數(shù)），以定奪該值的大小。
show variadles like ‘max_connections'大連接數(shù)
show variables like ‘max_used_connection'相應(yīng)連接數(shù)
max_used_connection/max_connections*100%（理想值約等于85%）
如果max_used_connections和max_connections相同，那么就是max_connections值設(shè)置過(guò)低或者超過(guò)服務(wù)器的負(fù)載上限了，低于10%則設(shè)置過(guò)大了。
MySQL能夠暫存的連接數(shù)量。當(dāng)主要MySQL線(xiàn)程在一個(gè)很短時(shí)間內(nèi)得到非常多的連接請(qǐng)求，他就會(huì)起作用。如果MySQL的連接數(shù)據(jù)達(dá)到max_connections時(shí)，新的請(qǐng)求將會(huì)被存在堆棧中，以等待某一連接釋放資源，該堆棧數(shù)量即back_log，如果等待連接的數(shù)量超過(guò)back_log，將不被接受連接資源。
wait_timeout：指的是MySQL再關(guān)閉一個(gè)非交互的連接之前所需要等待的秒數(shù)。
interative_timeout：指的是關(guān)閉一個(gè)交互的連接之前所需要等待的秒數(shù)。
對(duì)性能的影響
wait_timeout
（1）如果設(shè)置太小，那么連接關(guān)閉的很快，從而使一些持久的連接不起作用
（2）如果設(shè)置太大容易造成連接打開(kāi)時(shí)間過(guò)長(zhǎng)，在show processlist時(shí)，能夠看到太多的sleep狀態(tài)的連接，從而造成too many connections錯(cuò)誤。
（3）一般希望wait_timeuot盡可能的低
interative_timeout的設(shè)置將對(duì)你的web application沒(méi)有多大的影響
全局緩沖
key_buffer_size指定索引緩沖區(qū)的大小，他決定索引的處理速度，尤其是索引讀的速度。通過(guò)檢查狀態(tài)值 key_read_requests和key_reads，可以知道key_buffer_size設(shè)置是否合理。比例key_reads/key_read_requests應(yīng)該盡可能的低，至少是1：100，1：1000更好（上述狀態(tài)值可以使用show status like ‘key_read%'獲得）
未命中緩存的概率：
key_cache_miss_rate = key_reads/key_read_requests*100%
key_buffer_size只對(duì)MAISAM表起作用。
如何調(diào)整key_buffer_size的值
默認(rèn)的配置數(shù)時(shí)8388608（8M），主機(jī)有4G內(nèi)存可以調(diào)優(yōu)值為268435456（256M）
使用查詢(xún)緩存，MySQL將查詢(xún)結(jié)果存放在緩沖區(qū)中，今后對(duì)同樣的select語(yǔ)句（區(qū)分大小寫(xiě)），將直接從緩沖區(qū)中讀取結(jié)果。
一個(gè)SQL查詢(xún)?nèi)绻詓elect開(kāi)頭，那么MySQL服務(wù)器將嘗試對(duì)其使用查詢(xún)緩存。
注：兩個(gè)SQL語(yǔ)句，只要相差哪怕是一個(gè)字符（例如 大小寫(xiě)不一樣：多一個(gè)空格等），那么兩個(gè)SQL將使用不同的cache
通過(guò) show ststus like ‘Qcache%' 可以知道query_cache_size的設(shè)置是否合理
Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個(gè)數(shù)。如果該值顯示過(guò)大，則說(shuō)明Query Cache中的內(nèi)存碎片較多了。
注：當(dāng)一個(gè)表被更新后，和他相關(guān)的cache block將被free。但是這個(gè)block依然可能存在隊(duì)列中，除非是在隊(duì)列的尾部?？梢杂?flush query cache語(yǔ)句來(lái)清空f(shuō)ree blocks。
Qcache_free_memory:Query Cache 中目前剩余的內(nèi)存大小。通過(guò)這個(gè)參數(shù)我們可以較為準(zhǔn)確的觀(guān)察當(dāng)前系統(tǒng)中的Query Cache內(nèi)存大小是否足夠，是需要增多還是過(guò)多了。
Qcache_hits：表示有多少次命中緩存。我們主要可以通過(guò)該值來(lái)驗(yàn)證我們的查詢(xún)能緩存的效果。數(shù)字越大緩存效果越理想。
Qcache_inserts：表示多少次未命中而插入，意思是新來(lái)的SQL請(qǐng)求在緩存中未找到，不得不執(zhí)行查詢(xún)處理，執(zhí)行查詢(xún)處理后把結(jié)果insert帶查詢(xún)緩存中。這樣的情況次數(shù)越多，表示查詢(xún)緩存 應(yīng)用到的比較少，效果也就不理想。
Qcache_lowmen_prunes：多少條Query因?yàn)閮?nèi)存不足而被清除出Query Cache，通過(guò)Qcache_lowmem_prunes和Qcache_free_memory 相互結(jié)合，能夠更清楚的了解到我們系統(tǒng)中Query Cache的內(nèi)存大小是否真的足夠，是否非常頻繁的出現(xiàn)因?yàn)閮?nèi)存不足而有Query被換出。這個(gè)數(shù)字最好是長(zhǎng)時(shí)間來(lái)看，如果這個(gè)數(shù)字在不斷增長(zhǎng)，就表示可能碎片化非常嚴(yán)重，或者內(nèi)存很少。
Qcache_queries_in_cache：當(dāng)前Query Cache 中cache的Query數(shù)量
Qcache_total_blocks：當(dāng)前Query Cache中block的數(shù)量
查詢(xún)服務(wù)器關(guān)于query_cache的配置
各字段的解釋?zhuān)?br />
query_cache_limit：超出此大小的查詢(xún)將不被緩存
query_cache_min_res_unit：緩存塊的最小大小，query_cache_min_res_unit的配置是一柄雙刃劍，默認(rèn)是 4KB ，設(shè)置值大對(duì)大數(shù)據(jù)查詢(xún)有好處，但是如果你查詢(xún)的都是小數(shù)據(jù)查詢(xún)，就容易造成內(nèi)存碎片和浪費(fèi)。
query_cache_size：查詢(xún)緩存大小（注：QC存儲(chǔ)的單位最小是1024byte，所以如果你設(shè)定的一個(gè)不是1024的倍數(shù)的值。這個(gè)值會(huì)被四舍五入到最接近當(dāng)前值的等于1024的倍數(shù)的值。）
query_cache_type：緩存類(lèi)型，決定緩存什么樣子的查詢(xún)，注意這個(gè)值不能隨便設(shè)置必須設(shè)置為數(shù)字，可選值以及說(shuō)明如下：
0：OFF 相當(dāng)于禁用了
1：ON 將緩存所有結(jié)果，除非你的select語(yǔ)句使用了SQL_NO_CACHE禁用了查詢(xún)緩存
2：DENAND 則只緩存select語(yǔ)句中通過(guò)SQL_CACHE指定需要緩存的查詢(xún)。
query_cache_wlock_invalidate：當(dāng)有其他客戶(hù)端正在對(duì)MyISAM表進(jìn)行寫(xiě)操作時(shí)，如果查詢(xún)?cè)趒uery cache中，是否返回cache結(jié)果還是等寫(xiě)操作完成在讀表獲取結(jié)果。
查詢(xún)緩存碎片率：Qcache_free_block/Qcache_total_block*100%
如果查詢(xún)緩存碎片率超過(guò)20%，可以用flush query cache整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢(xún)都是小數(shù)據(jù)量的話(huà)。
查詢(xún)緩存利用率：（query_cache_size-Qcache_free_memory)/query_cache_size*100%
查詢(xún)緩存利用率在25%以下的話(huà)說(shuō)明query_cache_size設(shè)置過(guò)大，可以適當(dāng)減?。翰樵?xún)緩存利用率在80%以上而且Qcache_lowmem_prunes>50
的話(huà)說(shuō)明query_cache_size可能有點(diǎn)小，要不就是碎片太多
查詢(xún)緩存命中率：Qcache_hits/(Qcache_hits+Qcache_inserts)*100%
Query Cache的限制
a）所有子查詢(xún)中的外部查詢(xún)SQL 不能被Cache：
b）在p'rocedure，function以及trigger中的Query不能被Cache
c）包含其他很多每次執(zhí)行可能得到不一樣的結(jié)果的函數(shù)的Query不能被Cache
是一個(gè)MySQL中與安全有關(guān)的計(jì)數(shù)器值，他負(fù)責(zé)阻止過(guò)多嘗試失敗的客戶(hù)端以防止暴力破解密碼的情況，當(dāng)超過(guò)指定次數(shù)，MySQL服務(wù)器將禁止host的連接請(qǐng)求，直到mysql服務(wù)器重啟或通過(guò)flush hotos命令清空此host的相關(guān)信息。（與性能并無(wú)太大的關(guān)系）
每個(gè)需要排序的線(xiàn)程分配該大小的一個(gè)緩沖區(qū)。增加這值加速ORDER BY 或 GROUP BY操作
sort_buffer_size是一個(gè)connection級(jí)的參數(shù)，在每個(gè)connection（session）第一次需要使用這個(gè)buffer的時(shí)候，一次性分配設(shè)置的內(nèi)存。
sort_buffer_size：并不是越大越好，由于是connection級(jí)的參數(shù)，過(guò)大的設(shè)置+高并發(fā)可能會(huì)耗盡系統(tǒng)的內(nèi)存資源。例如：500個(gè)連接將會(huì)消耗500*sort_buffer_size(2M)=1G
根據(jù)配置文件限制server接受的數(shù)據(jù)包大小。
用于表示關(guān)聯(lián)緩存的大小，和sort_buffer_size一樣，該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享。
服務(wù)器線(xiàn)程緩存，這個(gè)值表示可以重新利用保存在緩存中的線(xiàn)程數(shù)量，當(dāng)斷開(kāi)連接時(shí)，那么客戶(hù)端的線(xiàn)程將被放到緩存中以響應(yīng)下一個(gè)客戶(hù)而不是銷(xiāo)毀（前提時(shí)緩存數(shù)未達(dá)上限），如果線(xiàn)程重新被請(qǐng)求，那么請(qǐng)求將從緩存中讀取，如果緩存中是空的或者是新的請(qǐng)求，這個(gè)線(xiàn)程將被重新請(qǐng)求，那么這個(gè)線(xiàn)程將被重新創(chuàng)建，如果有很多新的線(xiàn)程，增加這個(gè)值可以改善系統(tǒng)性能，通過(guò)比較Connections和Threads_created狀態(tài)的變量，可以看到這個(gè)變量的作用。
設(shè)置規(guī)則如下：1G內(nèi)存配置為8，2G內(nèi)存為16.服務(wù)器處理此客戶(hù)的線(xiàn)程將會(huì)緩存起來(lái)以響應(yīng)下一個(gè)客戶(hù)而不是被銷(xiāo)毀（前提是緩存數(shù)未到達(dá)上限）
Threads_cached：代表當(dāng)前此時(shí)此刻線(xiàn)程緩存中有多少空閑線(xiàn)程。
Threads_connected：代表當(dāng)前已建立連接的數(shù)量，因?yàn)橐粋€(gè)連接就需要一個(gè)線(xiàn)程，所以也可以看成當(dāng)前被使用的線(xiàn)程數(shù)。
Threads_created：代表最近一次服務(wù)啟動(dòng)，已創(chuàng)建線(xiàn)程的數(shù)量，如果發(fā)現(xiàn)Threads_created值過(guò)大的話(huà)，說(shuō)明MySQL服務(wù)器一直在創(chuàng)建線(xiàn)程，這也比較消耗資源，可以適當(dāng)增加配置文件中thread_cache_size值
Threads_running：代表當(dāng)前激活的（非睡眠狀態(tài)）線(xiàn)程數(shù)。并不是代表正在使用的線(xiàn)程數(shù)，有時(shí)候連接已建立，但是連接處于sleep狀態(tài)。
對(duì)于innodb表來(lái)說(shuō)，innodb_buffer_pool_size的作用相當(dāng)于key_buffer_size對(duì)于MyISAM表的作用一樣。Innodb使用該參數(shù)指定大小的內(nèi)存來(lái)緩沖數(shù)據(jù)和索引。大可以把該值設(shè)置成物理內(nèi)存的80%。
主要控制了innodb將log buffer中的數(shù)據(jù)寫(xiě)入日志文件并flush磁盤(pán)的時(shí)間點(diǎn)，取值分別為0，1，2.
實(shí)際測(cè)試發(fā)現(xiàn)，該值對(duì)插入數(shù)據(jù)的速度影響非常大，設(shè)置為2時(shí)插入10000條記錄只需要兩秒，設(shè)置為0時(shí)只需要一秒，設(shè)置為1時(shí)，則需要229秒。因此，MySQL手冊(cè)也建議盡量將插入操作合并成一個(gè)事務(wù)，這樣可以大幅度提高速度。
此參數(shù)用來(lái)設(shè)置innodb線(xiàn)程的并發(fā)數(shù)，默認(rèn)值為0表示不被限制，若要設(shè)置則與服務(wù)器的CPU核心數(shù)相同或是CPU的核心數(shù)的2倍。
此參數(shù)確定日志文件所用的內(nèi)存大小，以M為單位。緩沖區(qū)更大能提高性能，對(duì)于較大的事務(wù)，可以增大緩存大小。
此參數(shù)確定數(shù)據(jù)日志文件的大小，以M為單位，更大的設(shè)置可以提高性能。
為提高性能，MySQL可以以循環(huán)方式將日志文件寫(xiě)到多個(gè)文件。推薦設(shè)置為3
MySQL 讀入緩沖區(qū)大小。對(duì)表進(jìn)行順序掃描的請(qǐng)求將分配到一個(gè)讀入緩沖區(qū)MySQL會(huì)為他分配一段內(nèi)存緩沖區(qū)
MySQL 的隨機(jī)讀（查詢(xún)操作）緩沖區(qū)大小。當(dāng)按任意順序讀取行時(shí)（例如，按照排序順序），將分配到一個(gè)隨機(jī)都緩沖區(qū)。進(jìn)行排序查詢(xún)時(shí)，MySQL會(huì)首先掃描一遍該緩沖區(qū)，以避免磁盤(pán)搜索，提高查詢(xún)速度，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值。但是MySQL會(huì)為每個(gè)客戶(hù)連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存消耗過(guò)大。
注：順序讀是根據(jù)索引的葉節(jié)點(diǎn)數(shù)據(jù)就能順序的讀取所需要的行數(shù)據(jù)。隨機(jī)讀是指一般需要根據(jù)輔助索引葉節(jié)點(diǎn)中的主鍵尋找侍其巷進(jìn)行數(shù)據(jù)，而輔助索引和主鍵所在的數(shù)據(jù)端不同，因此訪(fǎng)問(wèn)方式是隨機(jī)的。
批量插入數(shù)據(jù)緩存大小，可以有效的提高插入效率，默認(rèn)為8M
binlog_cache_size=2M //為每個(gè)session分配的內(nèi)存，在事務(wù)過(guò)程中用來(lái)存儲(chǔ)二進(jìn)制日志的緩存，提高記錄bin-log的效率。
max_binlog_cache_size=8M //表示的是binlog能夠使用的大cache內(nèi)存大小
max_binlog_size=512M //指定binlog日志文件的大小。不能將變量設(shè)置為大于1G或小于4096字節(jié)。默認(rèn)值為1G.在導(dǎo)入大容量的sql文件時(shí)，建議關(guān)閉，sql_log_bin，否則硬盤(pán)扛不住，而且建議定期做刪除。
expire_logs_days=7 //定義了mysql清除過(guò)期日志的時(shí)間
[mysqld] basedir = /usr/local/mysql datadir = /usr/local/mysql/data server_id = 1 socket = /usr/local/mysql/mysql.sock log-error = /usr/local/mysql/data/mysqld.err slow_query_log = 1 slow_query_log_file=/usr/local/mysql/data/slow-query.log long_query_time = 1 log-queries-not-using-indexes max_connections = 1024 back_log = 128 wait_timeout = 60 interactive_timeout = 7200 key_buffer_size = 256M query_cache_size = 256M query_cache_type = 1 query_cache_limit = 50M max_connect_errors = 20 sort_buffer_size = 2M max_allowed_packet = 32M join_buffer_size = 2M thread_cache_size = 200 innodb_buffer_pool_size = 2048M innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 128M innodb_log_files_in_group = 3 log-bin=/usr/local/mysql/data/mysqlbin binlog_cache_size = 2M max_binlog_cache_size = 8M max_binlog_size = 512M expire_logs_days = 7 read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線(xiàn)，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性?xún)r(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專(zhuān)為企業(yè)上云打造定制，能夠滿(mǎn)足用戶(hù)豐富、多元化的應(yīng)用場(chǎng)景需求。
重慶分公司
重慶分公司
