MySQL配置最佳化

標籤：com   通過   pru   插入資料   blocks   掃描   sql最佳化   ble   效率   

一、全域配置

（1）max_connections
最大串連數。預設值是151，最多2000。如果伺服器的並發串連請求量比較大，建議調高此值，以增加並行串連數量。但是如果串連數越多，介於MySQL會為每個串連提供串連緩衝區，就會開銷越多的記憶體，所以要適當調整該值。
查看最大串連數

mysql> SHOW VARIABLES LIKE ‘max_connections‘;

查看響應的串連數

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能暫存的串連數量，預設值是80，最多512，可設定為128。如果MySQL的串連資料達到max_connections時，新來的請求將會被存在堆棧中，以等待某一串連釋放資源，該堆棧的數量即back_log。如果等待串連的數量超過back_log，將不被授予串連資源。當主要MySQL線程在一個很短時間內得到非常多的串連請求，這就起作用。
（3）key_buffer_size
索引緩衝的大小，它決定索引處理的速度，尤其是索引讀的速度。
通過檢查狀態值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表，但是內部的臨時磁碟表是MyISAM表，也要使用該值。
預設配置數值是8388608(8M)，主機有4GB記憶體，可改為268435456(256M)
（4）query_cache_size
使用查詢快取(query cache)，MySQL將查詢結果存放在緩衝區中，今後對於同樣的SELECT語句（區分大小寫），將直接從緩衝區中讀取結果。
最佳選項是將其從一開始就停用，設為0（現在MySQL 5.6的預設值）並利用其他方法加速查詢：最佳化索引、增加拷貝分散負載或者啟用額外的緩衝（比如memcache或redis）。
通過檢查狀態值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，如果你的查詢都是小資料量的話。
查詢快取利用率 = (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的值非常大，則表明經常出現緩衝不夠的情況，如果Qcache_hits的值也非常大，則表明查詢緩衝使用非常頻繁，此時需要增加緩衝大小；如果Qcache_hits的值不大，則表明你的查詢重複率很低，這種情況下使用查詢緩衝反而會影響效率，那麼可以考慮不用查詢緩衝。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩衝。
與查詢緩衝有關的參數還有query_cache_type、query_cache_limit、query_cache_min_res_unit。
query_cache_type指定是否使用查詢緩衝，可以設定為0、1、2，該變數是SESSION級的變數。
query_cache_limit指定單個查詢能夠使用的緩衝區大小，預設為1M。
query_cache_min_res_unit指定分配緩衝區空間的最小單位，預設為4K。檢查狀態值Qcache_free_blocks，如果該值非常大，則表明緩衝區中片段很多，這就表明查詢結果都比較小，此時需要減小query_cache_min_res_unit。
（5）read_buffer_size
是MySQL讀入緩衝區的大小，將對錶進行順序掃描的請求將分配一個讀入緩衝區，MySQL會為它分配一段記憶體緩衝區，read_buffer_size變數控制這一緩衝區的大小，如果對錶的順序掃描非常頻繁，並你認為頻繁掃描進行的太慢，可以通過增加該變數值以及記憶體緩衝區大小提高其效能。
預設數值是131072(128K)，可改為16773120(16M)
（6）read_rnd_buffer_size
隨機讀緩衝區大小。當按任意順序讀取行時(例如，按照排序次序)，將分配一個隨機讀緩衝區。進行排序查詢時，MySQL會首先掃描一遍該緩衝，以避免磁碟搜尋，提高查詢速度，如果需要排序大量資料，可適當調高該值。但MySQL會為每個客戶串連發放該緩衝空間，所以應盡量適當設定該值，以避免記憶體開銷過大。
預設數值是262144(256K)，可改為16777208(16M)
（7）sort_buffer_size
每個需要進行排序的線程分配該大小的一個緩衝區。增加這值加速ORDER BY或GROUP BY操作。
預設數值是10485760(1M)，可改為16777208(16M)
（8）join_buffer_size
聯集查詢操作所能使用的緩衝區大小
read_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size為每個線程獨佔，也就是說，如果有100個線程串連，則佔用為16M*100
（9）table_open_cache
表快取的大小。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被開啟並放入其中，這樣可以更快地訪問表內容。
通過檢查峰值時間的狀態值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設定成很大的值。如果設定得太高，可能會造成檔案描述符不足，從而造成效能不穩定或者串連失敗。
1G記憶體機器，推薦值是128-256。記憶體在4GB左右的伺服器該參數可設定為256M或384M。
（10）max_heap_table_size
使用者可以建立的記憶體表(memory table)的大小。這個值用來計算記憶體表的最大行數值。
這個變數和tmp_table_size一起限制了內部記憶體表的大小。如果某個內部heap（堆積）表大小超過tmp_table_size，MySQL可以根據需要自動將記憶體中的heap表改為基於硬碟的MyISAM表。
（11）tmp_table_size
暫存資料表的大小，例如做進階GROUP BY操作產生的暫存資料表。如果調高該值，MySQL同時將增加heap表的大小，可達到提高聯結查詢速度的效果，建議盡量最佳化查詢，要確保查詢過程中產生的暫存資料表在記憶體中，避免暫存資料表過大導致產生基於硬碟的MyISAM表。

mysql> SHOW GLOBAL STATUS LIKE ‘created_tmp%‘;+-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Created_tmp_disk_tables | 2884297  || Created_tmp_files       | 870      || Created_tmp_tables      | 15899696 |+-------------------------+----------+

每次建立暫存資料表，Created_tmp_tables增加，如果暫存資料表大小超過tmp_table_size，則是在磁碟上建立暫存資料表，Created_tmp_disk_tables也增加。
Created_tmp_files表示MySQL服務建立的臨時檔案檔案數，比較理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
（12）thread_cache_size
線程緩衝。當用戶端斷開之後，伺服器處理此客戶的線程將會緩衝起來以響應下一個客戶而不是銷毀（前提是緩衝數未達上限）。

mysql> SHOW STATUS LIKE ‘threads%‘;+-------------------+---------+| Variable_name     | Value   |+-------------------+---------+| Threads_cached    | 5       || Threads_connected | 13      || Threads_created   | 1095313 || Threads_running   | 1       |+-------------------+---------+

Threads_cached :代表當前此時此刻線程緩衝中有多少空閑線程。如果過大，表明MySQL伺服器一直在建立線程，這也是比較耗資源，可以適當增加thread_cache_size
Threads_connected :代表當前已建立串連的數量，因為一個串連就需要一個線程，所以也可以看成當前被使用的線程數。
Threads_created :代表從最近一次服務啟動，已建立線程的數量。
Threads_running :代表當前啟用的（非睡眠狀態）線程數。並不是代表正在使用的線程數，有時候串連已建立，但是串連處於sleep狀態，這裡相對應的線程也是sleep狀態。
建議設定接近Threads_connected值，再結合實體記憶體：1G—>8；2G—>16；3G—>32 綜合考慮一下值。
（13）interactive_timeout
一個互動串連在被伺服器在關閉前等待行動的秒數。預設值是28800（8小時），可設定為7200。
（14）wait_timeout
一個非互動串連在被伺服器在關閉前等待行動的秒數。要同時設定interactive_timeout和wait_timeout才會生效。
二、InnoDB配置
（1）innodb_buffer_pool_size
緩衝池的大小，快取資料和索引，對InnoDB整體效能影響較大，相當於MyISAM的key_buffer_size。如果只用Innodb，可以把這個值設為記憶體的70%-80%。越大越好，這能保證你在大多數的讀取操作時使用的是記憶體而不是硬碟。
（2）innodb_log_buffer_size  尚未執行的事務的緩衝大小，預設值為8M，一般8M-16M。如果你有很多事務的更新，插入或刪除操作，通過這個參數會大量的節省了磁碟I/O。但是如果你的事務中包含有二進位大對象或者大文字欄位的話，這點緩衝很快就會被填滿並觸發額外的I/O操作。看看Innodb_log_waits狀態變數，如果它不是0，應該增大這個值。但太大了也是浪費記憶體，因為1秒鐘總會flush一次，所以不需要設到超過1秒的需求。
（3）innodb_flush_log_at_trx_commit
把log buffer的資料寫入記錄檔並flush磁碟的策略，該值對插入資料的速度影響非常大。取值分別為0、1(預設值)、2(推薦值)
0：事務提交時，不寫入磁碟，而是每秒把log buffer的資料寫入記錄檔，並且flush(刷到磁碟)。速度最快，但不安全。mysqld進程的崩潰會導致上一秒鐘所有交易資料的丟失。
1：每次事務提交時把log buffer的資料寫入記錄檔，並且flush(刷到磁碟)。最安全，但也最慢。確保了事務的ACID。
2：每次事務提交時把log buffer的資料寫入記錄檔，每秒flush(刷到磁碟)。速度較快，比0安全。作業系統崩潰或者系統斷電會導致上一秒鐘所有交易資料的丟失。
（4）innodb_log_file_size
在一個日誌組每個記錄檔的大小，用於確保寫操作快速而可靠並且在崩潰時恢複。一般用64M-512M，具體取決於伺服器的空間。大的檔案提供更高的效能，但資料庫恢複時會用更多的時間。
（5）innodb_additional_mem_pool_size
儲存資料字典和其他內部資料結構的記憶體池大小。預設為1M，對於2G記憶體的機器，推薦值是20M，通常不用太大，應該與表結構的複雜度有關係。如果不夠用，MySQL會在錯誤記錄檔中寫入一條警告資訊。
（6）innodb_buffer_pool_instances
可以開啟多個記憶體緩衝池，這樣可以並行的記憶體讀寫。預設為8，一般為1-8。最常1s就會重新整理一次，故不用太大。對於較大的事務，可以增大緩衝大小。如果InnoDB緩衝池被劃分成多個地區，建議每個地區不小於1GB的空間。

MySQL最佳化

MySQL配置最佳化