安裝MySQL后，配置文件my.cnf在 /MySQL安裝目錄/share/mysql目錄中，該目錄中還包含多個配置文件可供參考，有my-large.cnf ，my-huge.cnf， my-medium.cnf，my-small.cnf，分別對應大中小型數(shù)據(jù)庫應用的配置。win環(huán)境下即存在于MySQL安裝目錄中的.ini文件。

        下面列出了對性能優(yōu)化影響較大的主要變量，主要分為連接請求的變量和緩沖區(qū)變量。 1. 連接請求的變量:

        1) max_connections MySQL的最大連接數(shù)，增加該值增加mysqld 要求的文件描述符的數(shù)量。如果服務器的并發(fā)連接請求量比較大，建議調高此值，以增加并行連接數(shù)量，當然這建立在機器能支撐的情況下，因為如果連接數(shù)越多，介于MySQL會為每個連接提供連接緩沖區(qū)，就會開銷越多的內存，所以要適當調整該值，不能盲目提高設值。

        數(shù)值過小會經(jīng)常出現(xiàn)ERROR 1040: Too many connections錯誤，可以過’conn%’通配符查看當前狀態(tài)的連接數(shù)量，以定奪該值的大小。

        show variables like ‘max_connections’ 最大連接數(shù)

        show status like ‘max_used_connections’響應的連接數(shù)

        如下:

        mysql> show variables like ‘max_connections‘;

        +———————–+——-+

        | Variable_name | Value |

        +———————–+——-+

        | max_connections | 256 |

        +———————–+——-+

        mysql> show status like ‘max%connections‘;

        +———————–+——-+

        | Variable_name | Value |

        +—————————-+——-+

        | max_used_connections | 256|

        +—————————-+——-+

        max_used_connections / max_connections * 100% (理想值? 85%) 如果max_used_connections跟max_connections相同 那么就是max_connections設置過低或者超過服務器負載上限了，低于10%則設置過大。

        2) back_log

        MySQL能暫存的連接數(shù)量。當主要MySQL線程在一個很短時間內得到非常多的連接請求，這就起作用。如果MySQL的連接數(shù)據(jù)達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log，如果等待連接的數(shù)量超過back_log，將不被授予連接資源。

        back_log值指出在MySQL暫時停止回答新請求之前的短時間內有多少個請求可以被存在堆棧中。只有如果期望在一個短時間內有很多連接，你需要增加它，換句話說，這值對到來的TCP/IP連接的偵聽隊列的大小。

        當觀察你主機進程列表(mysql> show full processlist)，發(fā)現(xiàn)大量264084 | unauthenticated user

        | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進程時，就要加大back_log 的值了。

        默認數(shù)值是50，可調優(yōu)為128，對于Linux系統(tǒng)設置范圍為小于512的整數(shù)。 3) interactive_timeout 一個交互連接在被服務器在關閉前等待行動的秒數(shù)。一個交互的客戶被定義為對mysql_real_connect()使用CLIENT_INTERACTIVE 選項的客戶。

        默認數(shù)值是28800，可調優(yōu)為7200。

        2. 緩沖區(qū)變量

        全局緩沖:

        4) key_buffer_size key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態(tài)值Key_read_requests和Key_reads，可以知道key_buffer_size設置是否合理。比例key_reads / key_read_requests應該盡可能的低，至少是1:100，1:1000更好(上述狀態(tài)值可以使用SHOW STATUS LIKE ‘key_read%’獲得)。

        key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁盤表是MyISAM表，也要使用該值?？梢允褂脵z查狀態(tài)值created_tmp_disk_tables得知詳情。 舉例如下:

        mysql> show variables like ‘key_buffer_size‘;

        +——————-+————+

        | Variable_name | Value |

        +———————+————+

        | key_buffer_size | 536870912 |

        +———— ———-+————+

        key_buffer_size為512MB，我們再看一下key_buffer_size的使用情況: mysql> show global status like ‘key_read%‘;

        +————————+————-+

        | Variable_name | Value |

        +————————+————-+

        | Key_read_requests| 27813678764 | | Key_reads | 6798830 |

        +————————+————-+

        一共有27813678764個索引讀取請求，有6798830個請求在內存中沒有找到直接從硬盤讀取索引，計算索引未命中緩存的概率:

        key_cache_miss_rate ,Key_reads / Key_read_requests * 100%，設置在1/1000左右較好 默認配置數(shù)值是8388600(8M)，主機有4GB內存，可以調優(yōu)值為268435456(256MB)。 5) query_cache_size 使用查詢緩沖，MySQL將查詢結果存放在緩沖區(qū)中，今后對于同樣的SELECT語句(區(qū)分大小寫)，將直接從緩沖區(qū)中讀取結果。

        通過檢查狀態(tài)值Qcache_*，可以知道query_cache_size設置是否合理(上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Qcache%’獲得)。如果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是在4.1版本以后引入的，它指定分配緩沖區(qū)空間的最小單位，缺省為4K。檢查狀態(tài)值Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多，這就表明查詢結果都比較小，此時需要減小query_cache_min_res_unit。

        舉例如下:

        mysql> show global status like ‘qcache%‘;

        +——————————-+—————–+

        | Variable_name | Value |

        +——————————-+—————–+

        | Qcache_free_blocks | 22756 |

        | Qcache_free_memory | 76764704 |

        | Qcache_hits | 213028692 |

        | Qcache_inserts | 208894227 |

        | Qcache_lowmem_prunes | 4010916 | | Qcache_not_cached | 13385031 |

        | Qcache_queries_in_cache | 43560 |

        | Qcache_total_blocks | 111212 |

        +——————————-+—————–+

        mysql> show variables like ‘query_cache%‘;

        +————————————–+————–+

        | Variable_name | Value |

        +————————————–+———–+

        | query_cache_limit | 2097152 |

        | query_cache_min_res_unit | 4096 |

        | query_cache_size | 203423744 |

        | query_cache_type | ON |

        | query_cache_wlock_invalidate | OFF |

        +————————————–+—————+

        查詢緩存碎片率= 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%

        示例服務器查詢緩存碎片率,20.46,，查詢緩存利用率,62.26,，查詢緩存命中率,1.94,，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。

        每個連接的緩沖

        6) record_buffer_size 每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)。如果你做很多順序掃描，你可能想要增加該值。

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

        7) read_rnd_buffer_size 隨機讀緩沖區(qū)大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區(qū)。進行排序查詢時，MySQL會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當調高該值。但MySQL會為每個客戶連接發(fā)放該緩沖空間，所以應盡量適當設置該值，以避免內存開銷過大。

        一般可設置為16M

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

        默認數(shù)值是2097144(2M)，可改為16777208 (16M)。

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

        record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size為每個線程獨占，也就是說，如果有100個線程連接，則占用為16M*100

        10) table_cache

        表高速緩存的大小。每當MySQL訪問一個表時，如果在表緩沖區(qū)中還有空間，該表就被打開并放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態(tài)值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發(fā)現(xiàn)open_tables等于table_cache，并且opened_tables在不斷增長，那么你就需要增加table_cache的值了(上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Open%tables’獲得)。注意，不能盲目地把table_cache設置成很大的值。如果設置得太高，可能會造成文件描述符不足，從而造成性能不穩(wěn)定或者連接失敗。

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

        11) max_heap_table_size 用戶可以創(chuàng)建的內存表(memory table)的大小。這個值用來計算內存表的最大行數(shù)值。這個變量支持動態(tài)改變，即set @max_heap_table_size=# 這個變量和tmp_table_size一起限制了內部內存表的大小。如果某個內部heap(堆積)表大小超過tmp_table_size，MySQL可以根據(jù)需要自動將內存中的heap表改為基于硬盤的MyISAM表。

        12) tmp_table_size

        通過設置tmp_table_size選項來增加一張臨時表的大小，例如做高級GROUP BY操作生成的臨時表。如果調高該值，MySQL同時將增加heap表的大小，可達到提高聯(lián)接查詢速度的效果，建議盡量優(yōu)化查詢，要確保查詢過程中生成的臨時表在內存中，避免臨時表過大導致生成基于硬盤的MyISAM表。

        mysql> show global status like ‘created_tmp%‘;

        +——————————–+———+

        | Variable_name | Value |

        +———————————-+———+

        | Created_tmp_disk_tables | 21197 | | Created_tmp_files | 58 |

        | Created_tmp_tables | 1771587 |

        +——————————–+———–+

        每次創(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%比如上面的服務器Created_tmp_disk_tables / Created_tmp_tables * 100% ,1.20%，應該相當好了 默認為16M，可調到64-256最佳，線程獨占，太大可能內存不夠I/O堵塞 13) thread_cache_size 可以復用的保存在中的線程的數(shù)量。如果有，新的線程從緩存中取得，當斷開連接的時候如果有空間，客戶的線置在緩存中。如果有很多新的線程，為了提高性能可以這個變量值。 通過比較 Connections和Threads_created狀態(tài)的變量，可以看到這個變量的作用。 默認值為110，可調優(yōu)為80。

        14) thread_concurrency

        推薦設置為服務器 CPU核數(shù)的2倍，例如雙核的CPU, 那么thread_concurrency的應該為4;2個雙核的cpu, thread_concurrency的值應為8。默認為8

        15) wait_timeout

        指定一個請求的最大連接時間，對于4GB左右內存的服務器可以設置為5-10。

        3. 配置InnoDB的幾個變量

        innodb_buffer_pool_size

        對于InnoDB表來說，innodb_buffer_pool_size的作用就相當于key_buffer_size對于MyISAM表的作用一樣。InnoDB使用該參數(shù)指定大小的內存來緩沖數(shù)據(jù)和索引。對于單獨的MySQL數(shù)據(jù)庫服務器，最大可以把該值設置成物理內存的80%。

        根據(jù)MySQL手冊，對于2G內存的機器，推薦值是1G(50%)。

        innodb_flush_log_at_trx_commit

        主要控制了innodb將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤的時間點，取值分別為0、1、2三個。0，表示當事務提交時，不做日志寫入操作，而是每秒鐘將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤一次;1，則在每秒鐘或是每次事物的提交都會引起日志文件寫入、flush磁盤的操作，確保了事務的ACID;設置為2，每次事務提交引起寫入日志文件的動作，但每秒鐘完成一次flush磁盤操作。

        實際測試發(fā)現(xiàn)，該值對插入數(shù)據(jù)的速度影響非常大，設置為2時插入10000條記錄只需要2秒，設置為0時只需要1秒，而設置為1時則需要229秒。因此，MySQL手冊也建議盡量將插入操作合并成一個事務，這樣可以大幅提高速度。

        根據(jù)MySQL手冊，在允許丟失最近部分事務的危險的前提下，可以把該值設為0或2。

        innodb_log_buffer_size

        log緩存大小，一般為1-8M，默認為1M，對于較大的事務，可以增大緩存大小。 可設置為4M或8M。

        innodb_additional_mem_pool_size

        該參數(shù)指定InnoDB用來存儲數(shù)據(jù)字典和其他內部數(shù)據(jù)結構的內存池大小。缺省值是1M。通常不用太大，只要夠用就行，應該與表結構的復雜度有關系。如果不夠用，MySQL會在錯誤日志中寫入一條警告信息。

        根據(jù)MySQL手冊，對于2G內存的機器，推薦值是20M，可適當增加。

        innodb_thread_concurrency=8

        推薦設置為 2*(NumCPUs+NumDisks)，默認一般為8