MySQL配置優(yōu)化

一缀去、全局配置

（1）max_connections

最大連接數(shù)堡赔。默認值是151识脆，最多2000。如果服務器的并發(fā)連接請求量比較大，建議調(diào)高此值灼捂，以增加并行連接數(shù)量离例。但是如果連接數(shù)越多，介于MySQL會為每個連接提供連接緩沖區(qū)悉稠，就會開銷越多的內(nèi)存宫蛆，所以要適當調(diào)整該值。

查看最大連接數(shù)

mysql> SHOW VARIABLES LIKE 'max_connections';

查看響應的連接數(shù)

mysql> SHOW STATUS LIKE 'max%connections';

max_used_connections / max_connections * 100% （理想值≈85%）?

如果max_used_connections跟max_connections相同那么就是max_connections設置過低或者超過服務器負載上限了的猛，低于10%則設置過大耀盗。

（2）back_log

MySQL能暫存的連接數(shù)量，默認值是80卦尊，最多512叛拷，可設置為128。如果MySQL的連接數(shù)據(jù)達到max_connections時岂却，新來的請求將會被存在堆棧中胡诗，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log淌友。如果等待連接的數(shù)量超過back_log煌恢，將不被授予連接資源。當主要MySQL線程在一個很短時間內(nèi)得到非常多的連接請求震庭，這就起作用瑰抵。

（3）key_buffer_size

索引緩沖區(qū)的大小，它決定索引處理的速度器联，尤其是索引讀的速度二汛。

通過檢查狀態(tài)值Key_read_requests和Key_reads，可以知道key_buffer_size設置是否合理拨拓。

mysql> SHOW STATUS LIKE 'key_read%';

+-------------------+----------+

| Variable_name? ? | Value? ? |

+-------------------+----------+

| Key_read_requests | 90585564 |

| Key_reads? ? ? ? | 97031? ? |

+--------

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

key_cache_miss_rate = Key_reads / Key_read_requests * 100%肴颊，設置在1/1000左右較好

key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表渣磷，但是內(nèi)部的臨時磁盤表是MyISAM表婿着，也要使用該值。

默認配置數(shù)值是8388608(8M)醋界，主機有4GB內(nèi)存竟宋，可改為268435456(256M)

（4）query_cache_size

使用查詢緩存(query cache)，MySQL將查詢結(jié)果存放在緩沖區(qū)中形纺，今后對于同樣的SELECT語句（區(qū)分大小寫）丘侠，將直接從緩沖區(qū)中讀取結(jié)果。

最佳選項是將其從一開始就停用逐样，設為0（現(xiàn)在MySQL 5.6的默認值）并利用其他方法加速查詢：優(yōu)化索引蜗字、增加拷貝分散負載或者啟用額外的緩存（比如Redis或Memcached）打肝。

通過檢查狀態(tài)值qcache_*，可以知道query_cache_size設置是否合理

mysql> SHOW STATUS LIKE 'qcache%';

+-------------------------+----------+

| Variable_name? ? ? ? ? | Value? ? |

+-------------------------+----------+

| Qcache_free_blocks? ? ? | 1? ? ? ? |

| Qcache_free_memory? ? ? | 1031360? |

| Qcache_hits? ? ? ? ? ? | 0? ? ? ? |

| Qcache_inserts? ? ? ? ? | 0? ? ? ? |

| Qcache_lowmem_prunes? ? | 0? ? ? ? |

| Qcache_not_cached? ? ? | 10302865 |

| Qcache_queries_in_cache | 0? ? ? ? |

| Qcache_total_blocks? ? | 1? ? ? ? |

+-------------------------+----------+

查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查詢緩存碎片率超過20%挪捕，可以用FLUSH QUERY CACHE整理緩存碎片闯睹，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數(shù)據(jù)量的話担神。

查詢緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查詢緩存利用率在25%以下的話說明query_cache_size設置的過大楼吃，可適當減小妄讯；查詢緩存利用率在80％以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小孩锡，要不就是碎片太多。

查詢緩存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

如果Qcache_lowmem_prunes的值非常大亥贸，則表明經(jīng)常出現(xiàn)緩沖不夠的情況躬窜，如果Qcache_hits的值也非常大，則表明查詢緩沖使用非常頻繁炕置，此時需要增加緩沖大腥侔ぁ；如果Qcache_hits的值不大朴摊，則表明你的查詢重復率很低默垄，這種情況下使用查詢緩沖反而會影響效率，那么可以考慮不用查詢緩沖甚纲。此外口锭，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖。

與查詢緩沖有關的參數(shù)還有query_cache_type介杆、query_cache_limit鹃操、query_cache_min_res_unit。

query_cache_type指定是否使用查詢緩沖春哨，可以設置為0荆隘、1、2赴背，該變量是SESSION級的變量椰拒。

query_cache_limit指定單個查詢能夠使用的緩沖區(qū)大小，缺省為1M癞尚。

query_cache_min_res_unit指定分配緩沖區(qū)空間的最小單位耸三，缺省為4K。檢查狀態(tài)值Qcache_free_blocks浇揩，如果該值非常大，則表明緩沖區(qū)中碎片很多憨颠，這就表明查詢結(jié)果都比較小胳徽，此時需要減小query_cache_min_res_unit积锅。

（5）read_buffer_size

是MySQL讀入緩沖區(qū)的大小，將對表進行順序掃描的請求將分配一個讀入緩沖區(qū)养盗，MySQL會為它分配一段內(nèi)存緩沖區(qū)缚陷，read_buffer_size變量控制這一緩沖區(qū)的大小，如果對表的順序掃描非常頻繁往核，并你認為頻繁掃描進行的太慢箫爷，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。

默認數(shù)值是131072(128K)聂儒，可改為16773120(16M)

（6）read_rnd_buffer_size

隨機讀緩沖區(qū)大小虎锚。當按任意順序讀取行時(例如，按照排序順序)衩婚，將分配一個隨機讀緩存區(qū)窜护。進行排序查詢時，MySQL會首先掃描一遍該緩沖非春，以避免磁盤搜索柱徙，提高查詢速度，如果需要排序大量數(shù)據(jù)奇昙，可適當調(diào)高該值护侮。但MySQL會為每個客戶連接發(fā)放該緩沖空間，所以應盡量適當設置該值储耐，以避免內(nèi)存開銷過大概行。

默認數(shù)值是262144(256K)，可改為16777208(16M)

（7）sort_buffer_size

每個需要進行排序的線程分配該大小的一個緩沖區(qū)弧岳。增加這值加速ORDER BY或GROUP BY操作凳忙。

默認數(shù)值是10485760(1M)，可改為16777208(16M)

（8）join_buffer_size

聯(lián)合查詢操作所能使用的緩沖區(qū)大小

read_buffer_size禽炬，read_rnd_buffer_size涧卵，sort_buffer_size，join_buffer_size為每個線程獨占腹尖，也就是說柳恐，如果有100個線程連接，則占用為16M*100

（9）table_open_cache

表高速緩存的大小热幔。每當MySQL訪問一個表時乐设，如果在表緩沖區(qū)中還有空間，該表就被打開并放入其中绎巨，這樣可以更快地訪問表內(nèi)容近尚。

通過檢查峰值時間的狀態(tài)值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值场勤。

mysql> SHOW STATUS LIKE 'open%tables';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Open_tables? | 2000? |

| Opened_tables | 0? ? |

+---------------+-------+

如果open_tables等于table_cache戈锻，并且opened_tables在不斷增長歼跟，那么就需要增加table_cache的值了。注意格遭，不能盲目地把table_cache設置成很大的值哈街。如果設置得太高，可能會造成文件描述符不足拒迅，從而造成性能不穩(wěn)定或者連接失敗骚秦。

1G內(nèi)存機器，推薦值是128-256璧微。內(nèi)存在4GB左右的服務器該參數(shù)可設置為256M或384M作箍。

（10）max_heap_table_size

用戶可以創(chuàng)建的內(nèi)存表(memory table)的大小。這個值用來計算內(nèi)存表的最大行數(shù)值往毡。

這個變量和tmp_table_size一起限制了內(nèi)部內(nèi)存表的大小蒙揣。如果某個內(nèi)部heap（堆積）表大小超過tmp_table_size，MySQL可以根據(jù)需要自動將內(nèi)存中的heap表改為基于硬盤的MyISAM表开瞭。

（11）tmp_table_size

臨時表的大小懒震，例如做高級GROUP BY操作生成的臨時表。如果調(diào)高該值嗤详，MySQL同時將增加heap表的大小个扰，可達到提高聯(lián)接查詢速度的效果，建議盡量優(yōu)化查詢葱色，要確保查詢過程中生成的臨時表在內(nèi)存中递宅，避免臨時表過大導致生成基于硬盤的MyISAM表。

mysql> SHOW GLOBAL STATUS LIKE 'created_tmp%';

+-------------------------+----------+

| Variable_name? ? ? ? ? | Value? ? |

+-------------------------+----------+

| Created_tmp_disk_tables | 2884297? |

| Created_tmp_files? ? ? | 870? ? ? |

| Created_tmp_tables? ? ? | 15899696 |

+-------------------------+----------+

每次創(chuàng)建臨時表苍狰，Created_tmp_tables增加办龄，如果臨時表大小超過tmp_table_size，則是在磁盤上創(chuàng)建臨時表淋昭，Created_tmp_disk_tables也增加俐填。

Created_tmp_files表示MySQL服務創(chuàng)建的臨時文件文件數(shù)，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

（12）thread_cache_size

線程緩存翔忽。當客戶端斷開之后英融，服務器處理此客戶的線程將會緩存起來以響應下一個客戶而不是銷毀（前提是緩存數(shù)未達上限）。

mysql> SHOW STATUS LIKE 'threads%';

+-------------------+---------+

| Variable_name? ? | Value? |

+-------------------+---------+

| Threads_cached? ? | 5? ? ? |

| Threads_connected | 13? ? ? |

| Threads_created? | 1095313 |

| Threads_running? | 1? ? ? |

+-------------------+---------+

Threads_cached :代表當前此時此刻線程緩存中有多少空閑線程歇式。如果過大驶悟，表明MySQL服務器一直在創(chuàng)建線程，這也是比較耗資源材失，可以適當增加thread_cache_size

Threads_connected :代表當前已建立連接的數(shù)量痕鳍，因為一個連接就需要一個線程，所以也可以看成當前被使用的線程數(shù)豺憔。

Threads_created :代表從最近一次服務啟動额获，已創(chuàng)建線程的數(shù)量够庙。

Threads_running :代表當前激活的（非睡眠狀態(tài)）線程數(shù)恭应。并不是代表正在使用的線程數(shù)抄邀，有時候連接已建立，但是連接處于sleep狀態(tài)昼榛，這里相對應的線程也是sleep狀態(tài)境肾。

建議設置接近Threads_connected值，再結(jié)合物理內(nèi)存：1G-8胆屿；2G-16奥喻；3G-32 綜合考慮一下值。

（13）interactive_timeout

一個交互連接在被服務器在關閉前等待行動的秒數(shù)非迹。默認值是28800（8小時）环鲤，可設置為7200。

（14）wait_timeout

一個非交互連接在被服務器在關閉前等待行動的秒數(shù)憎兽。要同時設置interactive_timeout和wait_timeout才會生效冷离。

二、InnoDB配置

（1）innodb_buffer_pool_size

緩沖池的大小纯命，緩存數(shù)據(jù)和索引西剥，對InnoDB整體性能影響較大，相當于MyISAM的key_buffer_size亿汞。如果只用Innodb瞭空，可以把這個值設為內(nèi)存的70%-80%。越大越好疗我，這能保證你在大多數(shù)的讀取操作時使用的是內(nèi)存而不是硬盤咆畏。

（2）innodb_log_buffer_size?

尚未執(zhí)行的事務的緩存大小，默認值為8M吴裤，一般8M-16M旧找。如果你有很多事務的更新，插入或刪除操作嚼摩，通過這個參數(shù)會大量的節(jié)省了磁盤I/O钦讳。但是如果你的事務中包含有二進制大對象或者大文本字段的話，這點緩存很快就會被填滿并觸發(fā)額外的I/O操作枕面≡缸洌看看Innodb_log_waits狀態(tài)變量，如果它不是0潮秘，應該增大這個值琼开。但太大了也是浪費內(nèi)存，因為1秒鐘總會flush一次枕荞，所以不需要設到超過1秒的需求柜候。

（3）innodb_flush_log_at_trx_commit

把log buffer的數(shù)據(jù)寫入日志文件并flush磁盤的策略搞动，該值對插入數(shù)據(jù)的速度影響非常大。取值分別為0渣刷、1(默認值)鹦肿、2(推薦值)

0：事務提交時，不寫入磁盤辅柴，而是每秒把log buffer的數(shù)據(jù)寫入日志文件箩溃，并且flush(刷到磁盤)。速度最快碌嘀，但不安全涣旨。mysqld進程的崩潰會導致上一秒鐘所有事務數(shù)據(jù)的丟失。

1：每次事務提交時把log buffer的數(shù)據(jù)寫入日志文件股冗，并且flush(刷到磁盤)霹陡。最安全，但也最慢止状。確保了事務的ACID烹棉。

2：每次事務提交時把log buffer的數(shù)據(jù)寫入日志文件，每秒flush(刷到磁盤)导俘。速度較快峦耘，比0安全。操作系統(tǒng)崩潰或者系統(tǒng)斷電會導致上一秒鐘所有事務數(shù)據(jù)的丟失旅薄。

（4）innodb_log_file_size

在一個日志組每個日志文件的大小辅髓，用于確保寫操作快速而可靠并且在崩潰時恢復。一般用64M-512M少梁，具體取決于服務器的空間洛口。大的文件提供更高的性能，但數(shù)據(jù)庫恢復時會用更多的時間凯沪。

（5）innodb_additional_mem_pool_size

存儲數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小第焰。默認為1M，對于2G內(nèi)存的機器妨马，推薦值是20M挺举，通常不用太大，應該與表結(jié)構(gòu)的復雜度有關系烘跺。如果不夠用湘纵，MySQL會在錯誤日志中寫入一條警告信息。

（6）innodb_buffer_pool_instances

可以開啟多個內(nèi)存緩沖池滤淳，這樣可以并行的內(nèi)存讀寫梧喷。默認為8，一般為1-8。最常1s就會刷新一次铺敌，故不用太大汇歹。對于較大的事務，可以增大緩存大小偿凭。如果InnoDB緩存池被劃分成多個區(qū)域产弹，建議每個區(qū)域不小于1GB的空間。