一、優化概述

二、查詢與索引優化分析

1效能瓶頸定位

Show命令

慢查詢日誌

explain分析查詢

profiling分析查詢

2索引及查詢優化

三、配置優化

1) max_connections

2) back_log

3) interactive_timeout

4) key_buffer_size

5) query_cache_size

6) record_buffer_size

7) read_rnd_buffer_size

8) sort_buffer_size

9) join_buffer_size

10) table_cache

11) max_heap_table_size

12) tmp_table_size

13) thread_cache_size

14) thread_concurrency

15) wait_timeout

一、優化概述

MySQL資料庫是常見的兩個瓶頸是CPU和I/O的瓶頸，CPU在飽和的時候一般發生在資料裝入記憶體或從磁碟上讀取資料時候。磁碟I/O瓶頸發生在裝入資料遠大於記憶體容量的時候，如果應用分佈在網路上，那麼查詢量相當大的時候那麼平瓶頸就會出現在網路上，我們可以用mpstat, iostat, sar和vmstat來檢視系統的效能狀態。

除了伺服器硬體的效能瓶頸，對於MySQL系統本身，我們可以使用工具來優化資料庫的效能，通常有三種：使用索引，使用EXPLAIN分析查詢以及調整MySQL的內部配置。

二、查詢與索引優化分析

在優化MySQL時，通常需要對資料庫進行分析，常見的分析手段有慢查詢日誌，EXPLAIN 分析查詢，profiling分析以及show命令查詢系統狀態及系統變數，通過定位分析效能的瓶頸，才能更好的優化資料庫系統的效能。

1 效能瓶頸定位

Show命令

我們可以通過show命令檢視MySQL狀態及變數，找到系統的瓶頸：

Mysql> show status ——顯示狀態資訊（擴充套件show status like ‘XXX’）

Mysql> show variables ——顯示系統變數（擴充套件show variables like ‘XXX’）

Mysql> show innodb status ——顯示InnoDB儲存引擎的狀態

Mysql> show processlist ——檢視當前SQL執行，包括執行狀態、是否鎖表等

Shell> mysqladmin variables -u username -p password——顯示系統變數

Shell> mysqladmin extended-status -u username -p password——顯示狀態資訊

檢視狀態變數及幫助：

Shell> mysqld –verbose –help [|more #逐行顯示]

比較全的Show命令的使用可參考： http://blog.phpbean.com/a.cn/18/

慢查詢日誌

慢查詢日誌開啟：

在配置檔案my.cnf或my.ini中在[mysqld]一行下面加入兩個配置引數

log-slow-queries=/data/mysqldata/slow-query.log

long_query_time=2

注：log-slow-queries引數為慢查詢日誌存放的位置，一般這個目錄要有mysql的執行帳號的可寫許可權，一般都將這個目錄設定為mysql的資料存放目錄；

long_query_time=2中的2表示查詢超過兩秒才記錄；

在my.cnf或者my.ini中新增log-queries-not-using-indexes引數，表示記錄下沒有使用索引的查詢。

log-slow-queries=/data/mysqldata/slow-query.log

long_query_time=10

log-queries-not-using-indexes

慢查詢日誌開啟方法二：

我們可以通過命令列設定變數來即時啟動慢日誌查詢。由下圖可知慢日誌沒有開啟，slow_launch_time=# 表示如果建立執行緒花費了比這個值更長的時間,slow_launch_threads 計數器將增加

設定慢日誌開啟

MySQL後可以查詢long_query_time 的值。

為了方便測試，可以將修改慢查詢時間為5秒。

慢查詢分析mysqldumpslow

我們可以通過開啟log檔案檢視得知哪些SQL執行效率低下

[root@localhost mysql]# more slow-query.log

# Time: 081026 19:46:34

# User@Host: root[root] @ localhost []

# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961

select count(*) from t_user;

從日誌中，可以發現查詢時間超過5 秒的SQL，而小於5秒的沒有出現在此日誌中。

如果慢查詢日誌中記錄內容很多，可以使用mysqldumpslow工具（MySQL客戶端安裝自帶）來對慢查詢日誌進行分類彙總。mysqldumpslow對日誌檔案進行了分類彙總，顯示彙總後摘要結果。

進入log的存放目錄，執行

[root@mysql_data]#mysqldumpslow slow-query.log

Reading mysql slow query log from slow-query.log

Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql

select count(N) from t_user;

mysqldumpslow命令

/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log

這會輸出記錄次數最多的10條SQL語句，其中：

-s, 是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘；

-t, 是top n的意思，即為返回前面多少條的資料；

-g, 後邊可以寫一個正則匹配模式，大小寫不敏感的；

例如：

/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log

得到返回記錄集最多的10個查詢。

/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log

得到按照時間排序的前10條裡面含有左連線的查詢語句。

使用mysqldumpslow命令可以非常明確的得到各種我們需要的查詢語句，對MySQL查詢語句的監控、分析、優化是MySQL優化非常重要的一步。開啟慢查詢日誌後，由於日誌記錄操作，在一定程度上會佔用CPU資源影響mysql的效能，但是可以階段性開啟來定位效能瓶頸。

explain分析查詢

使用 EXPLAIN 關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結構的效能瓶頸。通過explain命令可以得到:

– 表的讀取順序

– 資料讀取操作的操作型別

– 哪些索引可以使用

– 哪些索引被實際使用

– 表之間的引用

– 每張表有多少行被優化器查詢

EXPLAIN欄位：

?Table：顯示這一行的資料是關於哪張表的

?possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句

?key：實際使用的索引。如果為NULL，則沒有使用索引。MYSQL很少會選擇優化不足的索引，此時可以在SELECT語句中使用USE INDEX（index）來強制使用一個索引或者用IGNORE INDEX（index）來強制忽略索引

?key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好

?ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數

?rows：MySQL認為必須檢索的用來返回請求資料的行數

?type：這是最重要的欄位之一，顯示查詢使用了何種型別。從最好到最差的連線型別為system、const、eq_reg、ref、range、index和ALL

nsystem、const：可以將查詢的變數轉為常量. 如id=1; id為主鍵或唯一鍵.

neq_ref：訪問索引,返回某單一行的資料.(通常在聯接時出現，查詢使用的索引為主鍵或惟一鍵)

nref：訪問索引,返回某個值的資料.(可以返回多行) 通常使用=時發生

nrange：這個連線型別使用索引返回一個範圍中的行，比如使用>或

nindex：以索引的順序進行全表掃描，優點是不用排序,缺點是還要全表掃描

nALL：全表掃描，應該儘量避免

?Extra：關於MYSQL如何解析查詢的額外資訊，主要有以下幾種

nusing index：只用到索引,可以避免訪問表.

nusing where：使用到where來過慮資料. 不是所有的where clause都要顯示using where. 如以=方式訪問索引.

nusing tmporary：用到臨時表

nusing filesort：用到額外的排序. (當使用order by v1,而沒用到索引時,就會使用額外的排序)

nrange checked for eache record(index map:N)：沒有好的索引.

profiling分析查詢

通過慢日誌查詢可以知道哪些SQL語句執行效率低下，通過explain我們可以得知SQL語句的具體執行情況，索引使用等，還可以結合show命令檢視執行狀態。

如果覺得explain的資訊不夠詳細，可以同通過profiling命令得到更準確的SQL執行消耗系統資源的資訊。

profiling預設是關閉的。可以通過以下語句檢視

開啟功能： mysql>set profiling=1; 執行需要測試的sql 語句：

mysql> show profiles\G; 可以得到被執行的SQL語句的時間和ID

mysql>show profile for query 1; 得到對應SQL語句執行的詳細資訊

Show Profile命令格式：

SHOW PROFILE [type [, type] … ]

[FOR QUERY n]

[LIMIT row_count [OFFSET offset]]

type:

ALL

| BLOCK IO

| CONTEXT SWITCHES

| CPU

| IPC

| MEMORY

| PAGE FAULTS

| SOURCE

| SWAPS

以上的16rows是針對非常簡單的select語句的資源資訊，對於較複雜的SQL語句，會有更多的行和欄位，比如converting HEAP to MyISAM 、Copying to tmp table等等，由於以上的SQL語句不存在複雜的表操作，所以未顯示這些欄位。通過profiling資源耗費資訊，我們可以採取針對性的優化措施。

測試完畢以後，關閉引數：mysql> set profiling=0

2 索引及查詢優化

索引的型別

? 普通索引：這是最基本的索引型別，沒唯一性之類的限制。

? 唯一性索引：和普通索引基本相同，但所有的索引列值保持唯一性。

? 主鍵：主鍵是一種唯一索引，但必須指定為”PRIMARY KEY”。

? 全文索引：MYSQL從3.23.23開始支援全文索引和全文檢索。在MYSQL中，全文索引的索引型別為FULLTEXT。全文索引可以在VARCHAR或者TEXT型別的列上建立。

大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B樹中儲存。空間列型別的索引使用R-樹，MEMORY表支援hash索引。

單列索引和多列索引（複合索引）

索引可以是單列索引，也可以是多列索引。對相關的列使用索引是提高SELECT操作效能的最佳途徑之一。

多列索引：

MySQL可以為多個列建立索引。一個索引可以包括15個列。對於某些列型別，可以索引列的左字首，列的順序非常重要。

多列索引可以視為包含通過連線索引列的值而建立的值的排序的陣列。一般來說，即使是限制最嚴格的單列索引，它的限制能力也遠遠低於多列索引。

最左字首

多列索引有一個特點，即最左字首（Leftmost Prefixing）。假如有一個多列索引為key(firstname lastname age)，當搜尋條件是以下各種列的組合和順序時，MySQL將使用該多列索引：

firstname，lastname，age

firstname，lastname

firstname

也就是說，相當於還建立了key(firstname lastname)和key(firstname)。

索引主要用於下面的操作：

? 快速找出匹配一個WHERE子句的行。

? 刪除行。當執行聯接時，從其它表檢索行。

? 對具體有索引的列key_col找出MAX()或MIN()值。由前處理器進行優化，檢查是否對索引中在key_col之前發生所有關鍵字元素使用了WHERE key_part_# = constant。在這種情況下，MySQL為每個MIN()或MAX()表示式執行一次關鍵字查詢，並用常數替換它。如果所有表示式替換為常量，查詢立即返回。例如：

SELECT MIN(key2), MAX (key2) FROM tb WHERE key1=10;

? 如果對一個可用關鍵字的最左面的字首進行了排序或分組(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有關鍵字元素後面有DESC，關鍵字以倒序被讀取。

? 在一些情況中，可以對一個查詢進行優化以便不用查詢資料行即可以檢索值。如果查詢只使用來自某個表的數字型並且構成某些關鍵字的最左面字首的列，為了更快，可以從索引樹檢索出值。

SELECT key_part3 FROM tb WHERE key_part1=1

有時MySQL不使用索引，即使有可用的索引。一種情形是當優化器估計到使用索引將需要MySQL訪問表中的大部分行時。(在這種情況下，表掃描可能會更快些）。然而，如果此類查詢使用LIMIT只搜尋部分行，MySQL則使用索引，因為它可以更快地找到幾行並在結果中返回。例如：

合理的建立索引的建議：

(1) 越小的資料型別通常更好：越小的資料型別通常在磁碟、記憶體和CPU快取中都需要更少的空間，處理起來更快。

(2) 簡單的資料型別更好：整型資料比起字元，處理開銷更小，因為字串的比較更復雜。在MySQL中，應該用內建的日期和時間資料型別，而不是用字串來儲存時間；以及用整型資料型別儲存IP地址。

(3) 儘量避免NULL：應該指定列為NOT NULL，除非你想儲存NULL。在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計資訊以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值

這部分是關於索引和寫SQL語句時應當注意的一些瑣碎建議和注意點。

1. 當結果集只有一行資料時使用LIMIT 1

2. 避免SELECT *，始終指定你需要的列

從表中讀取越多的資料，查詢會變得更慢。他增加了磁碟需要操作的時間，還是在資料庫伺服器與WEB伺服器是獨立分開的情況下。你將會經歷非常漫長的網路延遲，僅僅是因為資料不必要的在伺服器之間傳輸。

3. 使用連線（JOIN）來代替子查詢(Sub-Queries)

連線（JOIN）.. 之所以更有效率一些，是因為MySQL不需要在記憶體中建立臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。

4. 使用ENUM、CHAR 而不是VARCHAR，使用合理的欄位屬性長度

5. 儘可能的使用NOT NULL

6. 固定長度的表會更快

7. 拆分大的DELETE 或INSERT 語句

8. 查詢的列越小越快

Where條件

在查詢中，WHERE條件也是一個比較重要的因素，儘量少並且是合理的where條件是很重要的，儘量在多個條件的時候，把會提取儘量少資料量的條件放在前面，減少後一個where條件的查詢時間。

有些where條件會導致索引無效：

? where子句的查詢條件裡有！=，MySQL將無法使用索引。

? where子句使用了Mysql函式的時候，索引將無效，比如：select * from tb where left(name, 4) = ‘xxx’

? 使用LIKE進行搜尋匹配的時候，這樣索引是有效的：select * from tbl1 where name like ‘xxx%’，而like ‘%xxx%’ 時索引無效

三、配置優化

安裝MySQL後，配置檔案my.cnf在 /MySQL安裝目錄/share/mysql目錄中，該目錄中還包含多個配置檔案可供參考，有my-large.cnf ，my-huge.cnf， my-medium.cnf，my-small.cnf，分別對應大中小型資料庫應用的配置。win環境下即存在於MySQL安裝目錄中的.ini文件。

下面列出了對效能優化影響較大的主要變數，主要分為連線請求的變數和緩衝區變數。

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 | 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能暫存的連線數量。當主要MySQL執行緒在一個很短時間內得到非常多的連線請求，這就起作用。如果MySQL的連線資料達到 max_connections時，新來的請求將會被存在堆疊中，以等待某一連線釋放資源，該堆疊的數量即back_log，如果等待連線的數量超過 back_log，將不被授予連線資源。

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

預設數值是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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23490154/viewspace-1063442/，如需轉載，請註明出處，否則將追究法律責任。

mysql效能優化-慢查詢分析、優化索引和配置

二、查詢與索引優化分析

1 效能瓶頸定位

Show命令

慢查詢日誌

explain分析查詢

profiling分析查詢

三、配置優化

1) max_connections

2) back_log

3) interactive_timeout

4) key_buffer_size

5) query_cache_size

6) record_buffer_size

7) read_rnd_buffer_size

8) sort_buffer_size

9) join_buffer_size

10) table_cache

11) max_heap_table_size

12) tmp_table_size

13) thread_cache_size

14) thread_concurrency

15) wait_timeout

相關文章

mysql效能優化-慢查詢分析、優化索引和配置

二、查詢與索引優化分析

1 效能瓶頸定位

Show命令

慢查詢日誌

explain分析查詢

profiling分析查詢

三、 配置優化

1) max_connections

2) back_log

3) interactive_timeout

4) key_buffer_size

5) query_cache_size

6) record_buffer_size

7) read_rnd_buffer_size

8) sort_buffer_size

9) join_buffer_size

10) table_cache

11) max_heap_table_size

12) tmp_table_size

13) thread_cache_size

14) thread_concurrency

15) wait_timeout

相關文章

三、配置優化