mysql常用引數使用說明及查詢

mysql 常用配置引數說明

1.   連線請求的變數：

1)max_connections

MySQL的最大連線數，增加該值增加mysqld 要求的檔案描述符的數量。如果伺服器的併發連線請求量比較大，建議調高此值，以增加並行連線數量，當然這建立在機器能支撐的情況下，因為如果連線數越多， 介於MySQL會為每個連線提供連線緩衝區，就會開銷越多的記憶體，所以要適當調整該值，不能盲目提高設值。數值過小會經常出現ERROR 1040: Too many connections錯誤，可以過’conn%’萬用字元檢視當前狀態的連線數量，以定奪該值的大小。
show variables like ‘max_connections’ 最大連線數
show  status like ‘max_used_connections’響應的連線數
如下：
mysql> show variables like ‘max_connections‘;
+———————–+——-+
| Variable_name　| Value |
+———————–+——-+
| max_connections | 512　|
+———————–+——-+

mysql> show status like ‘max%connections‘;
+———————–+——-+
| Variable_name　      | Value |
+—————————-+——-+
| max_used_connections | 512|
+—————————-+——-+

max_used_connections / max_connections * 100% （理想值≈ 85%） 
如果max_used_connections跟max_connections相同 那麼就是max_connections設定過低或者超過伺服器負載上限了，低於10%則設定過大。

2)back_log
MySQL能暫存的連線數量。當主要MySQL執行緒在一個很短時間內得到非常多的連線請求，這就起作用。如果MySQL的連線資料達到 max_connections時，新來的請求將會被存在堆疊中，以等待某一連線釋放資源，該堆疊的數量即back_log，如果等待連線的數量超過 back_log，將不被授予連線資源。
back_log值指出在MySQL暫時停止回答新請求之前的短時間內有多少個請求可以被存在堆疊中。只有如果期望在一個短時間內有很多連線，你需要增加它，換句話說，這值對到來的TCP/IP連線的偵聽佇列的大小。
當觀察你主機程式列表（mysql> show full processlist），發現大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連線程式時，就要加大back_log 的值了。
預設數值是50，可調優為128，對於Linux系統設定範圍為小於512的整數。 

3)interactive_timeout
一個互動連線在被伺服器在關閉前等待行動的秒數。一個互動的客戶被定義為對mysql_real_connect()使用CLIENT_INTERACTIVE 選項的客戶。 
預設數值是28800，可調優為7200。 

2.緩衝區變數
全域性緩衝：
4)key_buffer_size
key_buffer_size指定索引緩衝區的大小，它決定索引處理的速度，尤其是索引讀的速度。透過檢查狀態值 Key_read_requests和Key_reads，可以知道key_buffer_size設定是否合理。比例key_reads / key_read_requests應該儘可能的低，至少是1:100，1:1000更好（上述狀態值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。
key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁碟表是MyISAM表，也要使用該值。可以使用檢查狀態值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左右較好
預設配置數值是8388600(8M)，主機有4GB記憶體，可以調優值為268435456(256MB)。

5)query_cache_size

使用查詢緩衝，MySQL將查詢結果存放在緩衝區中，今後對於同樣的SELECT語句（區分大小寫），將直接從緩衝區中讀取結果。
透過檢查狀態值Qcache_*，可以知道query_cache_size設定是否合理（上述狀態值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。如果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是在4.1版本以後引入的，它指定分配緩衝區空間的最小單位，預設為4K。檢查狀態值 Qcache_free_blocks，如果該值非常大，則表明緩衝區中碎片很多，這就表明查詢結果都比較小，此時需要減小 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，如果你的查詢都是小資料量的話。
查詢快取利用率= (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

每個進行一個順序掃描的執行緒為其掃描的每張表分配這個大小的一個緩衝區。如果你做很多順序掃描，你可能想要增加該值。

預設數值是131072(128K)，可改為16773120 (16M)

7)read_rnd_buffer_size

隨機讀緩衝區大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀快取區。進行排序查詢時，MySQL會首先掃描一遍該緩衝，以避 免磁碟搜尋，提高查詢速度，如果需要排序大量資料，可適當調高該值。但MySQL會為每個客戶連線發放該緩衝空間，所以應儘量適當設定該值，以避免記憶體開 銷過大。 一般可設定為16M 

8)sort_buffer_size

每個需要進行排序的執行緒分配該大小的一個緩衝區。增加這值加速ORDER BY或GROUP BY操作。預設數值是2097144(2M)，可改為16777208 (16M)。

9)join_buffer_size

聯合查詢操作所能使用的緩衝區大小

record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size為每個執行緒獨佔，也就是說，如果有100個執行緒連線，則佔用為16M*100

10)table_cache

表快取記憶體的大小。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被開啟並放入其中，這樣可以更快地訪問表內容。透過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如 果你發現open_tables等於table_cache，並且opened_tables在不斷增長，那麼你就需要增加table_cache的值了 （上述狀態值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設定成很大的值。如果設定得太高，可能會造成檔案描述符不足，從而造成效能 不穩定或者連線失敗。1G記憶體機器，推薦值是128－256。記憶體在4GB左右的伺服器該引數可設定為256M或384M。

11)max_heap_table_size

使用者可以建立的記憶體表(memory table)的大小。這個值用來計算記憶體表的最大行數值。這個變數支援動態改變，即set @max_heap_table_size=# 這個變數和tmp_table_size一起限制了內部記憶體表的大小。如果某個內部heap（堆積）表大小超過tmp_table_size，MySQL可以根據需要自動將記憶體中的heap表改為基於硬碟的MyISAM表。

12)tmp_table_size

透過設定tmp_table_size選項來增加一張臨時表的大小，例如做高階GROUP BY操作生成的臨時表。如果調高該值，MySQL同時將增加heap表的大小，可達到提高聯接查詢速度的效果，建議儘量最佳化查詢，要確保查詢過程中生成的臨時表在記憶體中，避免臨時表過大導致生成基於硬碟的MyISAM表。

mysql> show global status like ‘created_tmp%‘;

+——————————–+———+
| Variable_name　　        | Value　|
+———————————-+———+
| Created_tmp_disk_tables  | 21197   |
| Created_tmp_files        | 58      |
| Created_tmp_tables       | 1771587 |
+——————————–+———–+

每次建立臨時表，Created_tmp_tables增加，如果臨時表大小超過tmp_table_size，則是在磁碟上建立臨時 表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務建立的臨時檔案檔案數，比較理想的配 置是：

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

可以複用的儲存在中的執行緒的數量。如果有，新的執行緒從快取中取得，當斷開連線的時候如果有空間，客戶的線置在快取中。如果有很多新的執行緒，為了提高效能可以這個變數值。透過比較 Connections和Threads_created狀態的變數，可以看到這個變數的作用。

預設值為110，可調優為80。 

14)thread_concurrency

推薦設定為伺服器 CPU核數的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使用該引數指定大小的記憶體來緩衝資料和索引。對於單獨的MySQL資料庫伺服器，最大可以把該值設定成實體記憶體的80%。根據MySQL手冊，對於2G記憶體的機器，推薦值是1G（50%）。

innodb_flush_log_at_trx_commit

主要控制了innodb將log buffer中的資料寫入日誌檔案並flush磁碟的時間點，取值分別為0、1、2三個。0，表示當事務提交時，不做日誌寫入操作，而是每秒鐘將log buffer中的資料寫入日誌檔案並flush磁碟一次；1，則在每秒鐘或是每次事物的提交都會引起日誌檔案寫入、flush磁碟的操作，確保了事務的 ACID；設定為2，每次事務提交引起寫入日誌檔案的動作，但每秒鐘完成一次flush磁碟操作。 實際測試發現，該值對插入資料的速度影響非常大，設定為2時插入10000條記錄只需要2秒，設定為0時只需要1秒，而設定為1時則需要229秒。因此，MySQL手冊也建議儘量將插入操作合併成一個事務，這樣可以大幅提高速度。根據MySQL手冊，在允許丟失最近部分事務的危險的前提下，可以把該值設為0或2。

innodb_log_buffer_size

log快取大小，一般為1-8M，預設為1M，對於較大的事務，可以增大快取大小。可設定為4M或8M。

innodb_additional_mem_pool_size

該引數指定InnoDB用來儲存資料字典和其他內部資料結構的記憶體池大小。預設值是1M。通常不用太大，只要夠用就行，應該與表結構的複雜度有關係。如果不夠用，MySQL會在錯誤日誌中寫入一條警告資訊。根據MySQL手冊，對於2G記憶體的機器，推薦值是20M，可適當增加。

innodb_thread_concurrency=8

推薦設定為 2*(NumCPUs+NumDisks)，預設一般為8