目錄
一、優(yōu)化概述
二、查詢(xún)與索引優(yōu)化分析
1性能瓶頸定位
Show命令
慢查詢(xún)日志
explain分析查詢(xún)
profiling分析查詢(xún)
2索引及查詢(xún)優(yōu)化
三、配置優(yōu)化
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
一、 優(yōu)化概述
MySQL數據庫是常見(jiàn)的兩個(gè)瓶頸是CPU和I/O的瓶頸，CPU在飽和的時(shí)候一般發(fā)生在數據裝入內存或從磁盤(pán)上讀取數據時(shí)候。磁盤(pán)I/O瓶頸發(fā)生在裝入數據遠大于內存容量的時(shí)候，如果應用分布在網(wǎng)絡(luò )上，那么查詢(xún)量相當大的時(shí)候那么平瓶頸就會(huì )出現在網(wǎng)絡(luò )上，我們可以用mpstat, iostat, sar和vmstat來(lái)查看系統的性能狀態(tài)。
除了服務(wù)器硬件的性能瓶頸，對于MySQL系統本身，我們可以使用工具來(lái)優(yōu)化數據庫的性能，通常有三種：使用索引，使用EXPLAIN分析查詢(xún)以及調整MySQL的內部配置。
二、查詢(xún)與索引優(yōu)化分析在優(yōu)化MySQL時(shí)，通常需要對數據庫進(jìn)行分析，常見(jiàn)的分析手段有慢查詢(xún)日志，EXPLAIN 分析查詢(xún)，profiling分析以及show命令查詢(xún)系統狀態(tài)及系統變量，通過(guò)定位分析性能的瓶頸，才能更好的優(yōu)化數據庫系統的性能。
1 性能瓶頸定位Show命令我們可以通過(guò)show命令查看MySQL狀態(tài)及變量，找到系統的瓶頸：
Mysql> show status ——顯示狀態(tài)信息（擴展show status like ‘XXX’）
Mysql> show variables ——顯示系統變量（擴展show variables like ‘XXX’）
Mysql> show innodb status ——顯示InnoDB存儲引擎的狀態(tài)
Mysql> show processlist ——查看當前SQL執行，包括執行狀態(tài)、是否鎖表等
Shell> mysqladmin variables -u username -p password——顯示系統變量
Shell> mysqladmin extended-status -u username -p password——顯示狀態(tài)信息
查看狀態(tài)變量及幫助：
Shell> mysqld –verbose –help [|more #逐行顯示]
比較全的Show命令的使用可參考： http://blog.phpbean.com/a.cn/18/
慢查詢(xún)日志慢查詢(xún)日志開(kāi)啟：
在配置文件my.cnf或my.ini中在[mysqld]一行下面加入兩個(gè)配置參數
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=2
注：log-slow-queries參數為慢查詢(xún)日志存放的位置，一般這個(gè)目錄要有mysql的運行賬號的可寫(xiě)權限，一般都將這個(gè)目錄設置為mysql的數據存放目錄；
long_query_time=2中的2表示查詢(xún)超過(guò)兩秒才記錄；
在my.cnf或者my.ini中添加log-queries-not-using-indexes參數，表示記錄下沒(méi)有使用索引的查詢(xún)。
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=10
log-queries-not-using-indexes
慢查詢(xún)日志開(kāi)啟方法二：
我們可以通過(guò)命令行設置變量來(lái)即時(shí)啟動(dòng)慢日志查詢(xún)。由下圖可知慢日志沒(méi)有打開(kāi)，slow_launch_time=# 表示如果建立線(xiàn)程花費了比這個(gè)值更長(cháng)的時(shí)間,slow_launch_threads 計數器將增加
設置慢日志開(kāi)啟
MySQL后可以查詢(xún)long_query_time 的值 。
為了方便測試，可以將修改慢查詢(xún)時(shí)間為5秒。
慢查詢(xún)分析mysqldumpslow
我們可以通過(guò)打開(kāi)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;
從日志中，可以發(fā)現查詢(xún)時(shí)間超過(guò)5 秒的SQL，而小于5秒的沒(méi)有出現在此日志中。
如果慢查詢(xún)日志中記錄內容很多，可以使用mysqldumpslow工具（MySQL客戶(hù)端安裝自帶）來(lái)對慢查詢(xún)日志進(jìn)行分類(lèi)匯總。mysqldumpslow對日志文件進(jìn)行了分類(lèi)匯總，顯示匯總后摘要結果。
進(jìn)入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
這會(huì )輸出記錄次數最多的10條SQL語(yǔ)句，其中：
-s, 是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時(shí)間、查詢(xún)時(shí)間、返回的記錄數來(lái)排序，ac、at、al、ar，表示相應的倒敘；
-t, 是top n的意思，即為返回前面多少條的數據；
-g, 后邊可以寫(xiě)一個(gè)正則匹配模式，大小寫(xiě)不敏感的；
例如：
/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
得到返回記錄集最多的10個(gè)查詢(xún)。
/path/mysqldumpslow -s t -t 10 -g “l(fā)eft join” /database/mysql/slow-log
得到按照時(shí)間排序的前10條里面含有左連接的查詢(xún)語(yǔ)句。
使用mysqldumpslow命令可以非常明確的得到各種我們需要的查詢(xún)語(yǔ)句，對MySQL查詢(xún)語(yǔ)句的監控、分析、優(yōu)化是MySQL優(yōu)化非常重要的一步。開(kāi)啟慢查詢(xún)日志后，由于日志記錄操作，在一定程度上會(huì )占用CPU資源影響mysql的性能，但是可以階段性開(kāi)啟來(lái)定位性能瓶頸。
explain分析查詢(xún)使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執行SQL查詢(xún)語(yǔ)句，從而知道MySQL是如何處理你的SQL語(yǔ)句的。這可以幫你分析你的查詢(xún)語(yǔ)句或是表結構的性能瓶頸。通過(guò)explain命令可以得到:
– 表的讀取順序
– 數據讀取操作的操作類(lèi)型
– 哪些索引可以使用
– 哪些索引被實(shí)際使用
– 表之間的引用
– 每張表有多少行被優(yōu)化器查詢(xún)
EXPLAIN字段：
Table：顯示這一行的數據是關(guān)于哪張表的
possible_keys：顯示可能應用在這張表中的索引。如果為空，沒(méi)有可能的索引?？梢詾橄嚓P(guān)的域從WHERE語(yǔ)句中選擇一個(gè)合適的語(yǔ)句
key：實(shí)際使用的索引。如果為NULL，則沒(méi)有使用索引。MYSQL很少會(huì )選擇優(yōu)化不足的索引，此時(shí)可以在SELECT語(yǔ)句中使用USE INDEX（index）來(lái)強制使用一個(gè)索引或者用IGNORE INDEX（index）來(lái)強制忽略索引
key_len：使用的索引的長(cháng)度。在不損失精確性的情況下，長(cháng)度越短越好
ref：顯示索引的哪一列被使用了，如果可能的話(huà)，是一個(gè)常數
rows：MySQL認為必須檢索的用來(lái)返回請求數據的行數
type：這是最重要的字段之一，顯示查詢(xún)使用了何種類(lèi)型。從最好到最差的連接類(lèi)型為system、const、eq_reg、ref、range、index和ALL
nsystem、const：可以將查詢(xún)的變量轉為常量. 如id=1; id為 主鍵或唯一鍵.
neq_ref：訪(fǎng)問(wèn)索引,返回某單一行的數據.(通常在聯(lián)接時(shí)出現，查詢(xún)使用的索引為主鍵或惟一鍵)
nref：訪(fǎng)問(wèn)索引,返回某個(gè)值的數據.(可以返回多行) 通常使用=時(shí)發(fā)生
nrange：這個(gè)連接類(lèi)型使用索引返回一個(gè)范圍中的行，比如使用>或<查找東西，并且該字段上建有索引時(shí)發(fā)生的情況(注:不一定好于index)
nindex：以索引的順序進(jìn)行全表掃描，優(yōu)點(diǎn)是不用排序,缺點(diǎn)是還要全表掃描
nALL：全表掃描，應該盡量避免
Extra：關(guān)于MYSQL如何解析查詢(xún)的額外信息，主要有以下幾種
nusing index：只用到索引,可以避免訪(fǎng)問(wèn)表.
nusing where：使用到where來(lái)過(guò)慮數據. 不是所有的where clause都要顯示using where. 如以=方式訪(fǎng)問(wèn)索引.
nusing tmporary：用到臨時(shí)表
nusing filesort：用到額外的排序. (當使用order by v1,而沒(méi)用到索引時(shí),就會(huì )使用額外的排序)
nrange checked for eache record(index map:N)：沒(méi)有好的索引.
profiling分析查詢(xún)
通過(guò)慢日志查詢(xún)可以知道哪些SQL語(yǔ)句執行效率低下，通過(guò)explain我們可以得知SQL語(yǔ)句的具體執行情況，索引使用等，還可以結合show命令查看執行狀態(tài)。
如果覺(jué)得explain的信息不夠詳細，可以同通過(guò)profiling命令得到更準確的SQL執行消耗系統資源的信息。
profiling默認是關(guān)閉的?？梢酝ㄟ^(guò)以下語(yǔ)句查看
打開(kāi)功能： mysql>set profiling=1; 執行需要測試的sql 語(yǔ)句：
mysql> show profiles\G; 可以得到被執行的SQL語(yǔ)句的時(shí)間和ID
mysql>show profile for query 1; 得到對應SQL語(yǔ)句執行的詳細信息
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是針對非常簡(jiǎn)單的select語(yǔ)句的資源信息，對于較復雜的SQL語(yǔ)句，會(huì )有更多的行和字段，比如converting HEAP to MyISAM 、Copying to tmp table等等，由于以上的SQL語(yǔ)句不存在復雜的表操作，所以未顯示這些字段。通過(guò)profiling資源耗費信息，我們可以采取針對性的優(yōu)化措施。
測試完畢以后 ，關(guān)閉參數：mysql> set profiling=0
2 索引及查詢(xún)優(yōu)化
索引的類(lèi)型
普通索引：這是最基本的索引類(lèi)型，沒(méi)唯一性之類(lèi)的限制。
唯一性索引：和普通索引基本相同，但所有的索引列值保持唯一性。
主鍵：主鍵是一種唯一索引，但必須指定為”P(pán)RIMARY KEY”。
全文索引：MYSQL從3.23.23開(kāi)始支持全文索引和全文檢索。在MYSQL中，全文索引的索引類(lèi)型為FULLTEXT。全文索引可以在VARCHAR或者TEXT類(lèi)型的列上創(chuàng )建。
大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B樹(shù)中存儲?？臻g列類(lèi)型的索引使用R-樹(shù)，MEMORY表支持hash索引。
單列索引和多列索引（復合索引）
索引可以是單列索引，也可以是多列索引。對相關(guān)的列使用索引是提高SELECT操作性能的最佳途徑之一。
多列索引：
MySQL可以為多個(gè)列創(chuàng )建索引。一個(gè)索引可以包括15個(gè)列。對于某些列類(lèi)型，可以索引列的左前綴，列的順序非常重要。
多列索引可以視為包含通過(guò)連接索引列的值而創(chuàng )建的值的排序的數組。一般來(lái)說(shuō)，即使是限制最嚴格的單列索引，它的限制能力也遠遠低于多列索引。
最左前綴
多列索引有一個(gè)特點(diǎn)，即最左前綴（Leftmost Prefixing）。假如有一個(gè)多列索引為key(firstname lastname age)，當搜索條件是以下各種列的組合和順序時(shí)，MySQL將使用該多列索引：
firstname，lastname，age
firstname，lastname
firstname
也就是說(shuō)，相當于還建立了key(firstname lastname)和key(firstname)。
索引主要用于下面的操作：
快速找出匹配一個(gè)WHERE子句的行。
刪除行。當執行聯(lián)接時(shí)，從其它表檢索行。
對具體有索引的列key_col找出MAX()或MIN()值。由預處理器進(jìn)行優(yōu)化，檢查是否對索引中在key_col之前發(fā)生所有關(guān)鍵字元素使用了WHERE key_part_# = constant。在這種情況下，MySQL為每個(gè)MIN()或MAX()表達式執行一次關(guān)鍵字查找，并用常數替換它。如果所有表達式替換為常量，查詢(xún)立即返回。例如：
SELECT MIN(key2), MAX (key2) FROM tb WHERE key1=10;
如果對一個(gè)可用關(guān)鍵字的最左面的前綴進(jìn)行了排序或分組(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個(gè)表。如果所有關(guān)鍵字元素后面有DESC，關(guān)鍵字以倒序被讀取。
在一些情況中，可以對一個(gè)查詢(xún)進(jìn)行優(yōu)化以便不用查詢(xún)數據行即可以檢索值。如果查詢(xún)只使用來(lái)自某個(gè)表的數字型并且構成某些關(guān)鍵字的最左面前綴的列，為了更快，可以從索引樹(shù)檢索出值。
SELECT key_part3 FROM tb WHERE key_part1=1
有時(shí)MySQL不使用索引，即使有可用的索引。一種情形是當優(yōu)化器估計到使用索引將需要MySQL訪(fǎng)問(wèn)表中的大部分行時(shí)。(在這種情況下，表掃描可能會(huì )更快些）。然而，如果此類(lèi)查詢(xún)使用LIMIT只搜索部分行，MySQL則使用索引，因為它可以更快地找到幾行并在結果中返回。例如：
合理的建立索引的建議：
(1) 越小的數據類(lèi)型通常更好：越小的數據類(lèi)型通常在磁盤(pán)、內存和CPU緩存中都需要更少的空間，處理起來(lái)更快。
(2) 簡(jiǎn)單的數據類(lèi)型更好：整型數據比起字符，處理開(kāi)銷(xiāo)更小，因為字符串的比較更復雜。在MySQL中，應該用內置的日期和時(shí)間數據類(lèi)型，而不是用字符串來(lái)存儲時(shí)間；以及用整型數據類(lèi)型存儲IP地址。
(3) 盡量避免NULL：應該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進(jìn)行查詢(xún)優(yōu)化，因為它們使得索引、索引的統計信息以及比較運算更加復雜。你應該用0、一個(gè)特殊的值或者一個(gè)空串代替空值
這部分是關(guān)于索引和寫(xiě)SQL語(yǔ)句時(shí)應當注意的一些瑣碎建議和注意點(diǎn)。
1. 當結果集只有一行數據時(shí)使用LIMIT 1
2. 避免SELECT *，始終指定你需要的列
從表中讀取越多的數據，查詢(xún)會(huì )變得更慢。他增加了磁盤(pán)需要操作的時(shí)間，還是在數據庫服務(wù)器與WEB服務(wù)器是獨立分開(kāi)的情況下。你將會(huì )經(jīng)歷非常漫長(cháng)的網(wǎng)絡(luò )延遲，僅僅是因為數據不必要的在服務(wù)器之間傳輸。
3. 使用連接（JOIN）來(lái)代替子查詢(xún)(Sub-Queries)
連接（JOIN）.. 之所以更有效率一些，是因為MySQL不需要在內存中創(chuàng )建臨時(shí)表來(lái)完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢(xún)工作。
4. 使用ENUM、CHAR 而不是VARCHAR，使用合理的字段屬性長(cháng)度
5. 盡可能的使用NOT NULL
6. 固定長(cháng)度的表會(huì )更快
7. 拆分大的DELETE 或INSERT 語(yǔ)句
8. 查詢(xún)的列越小越快
Where條件
在查詢(xún)中，WHERE條件也是一個(gè)比較重要的因素，盡量少并且是合理的where條件是很重要的，盡量在多個(gè)條件的時(shí)候，把會(huì )提取盡量少數據量的條件放在前面，減少后一個(gè)where條件的查詢(xún)時(shí)間。
有些where條件會(huì )導致索引無(wú)效：
where子句的查詢(xún)條件里有！=，MySQL將無(wú)法使用索引。
where子句使用了Mysql函數的時(shí)候，索引將無(wú)效，比如：select * from tb where left(name, 4) = ‘xxx’
使用LIKE進(jìn)行搜索匹配的時(shí)候，這樣索引是有效的：select * from tbl1 where name like ‘xxx%’，而like ‘%xxx%’ 時(shí)索引無(wú)效
三、 配置優(yōu)化
安裝MySQL后，配置文件my.cnf在 /MySQL安裝目錄/share/mysql目錄中，該目錄中還包含多個(gè)配置文件可供參考，有my-large.cnf ，my-huge.cnf， my-medium.cnf，my-small.cnf，分別對應大中小型數據庫應用的配置。win環(huán)境下即存在于MySQL安裝目錄中的.ini文件。
下面列出了對性能優(yōu)化影響較大的主要變量，主要分為連接請求的變量和緩沖區變量。
1. 連接請求的變量：
1) max_connectionsMySQL的最大連接數，增加該值增加mysqld 要求的文件描述符的數量。如果服務(wù)器的并發(fā)連接請求量比較大，建議調高此值，以增加并行連接數量，當然這建立在機器能支撐的情況下，因為如果連接數越多，介于MySQL會(huì )為每個(gè)連接提供連接緩沖區，就會(huì )開(kāi)銷(xiāo)越多的內存，所以要適當調整該值，不能盲目提高設值。
數值過(guò)小會(huì )經(jīng)常出現ERROR 1040: Too many connections錯誤，可以過(guò)’conn%’通配符查看當前狀態(tài)的連接數量，以定奪該值的大小。
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設置過(guò)低或者超過(guò)服務(wù)器負載上限了，低于10%則設置過(guò)大。
2) back_logMySQL能暫存的連接數量。當主要MySQL線(xiàn)程在一個(gè)很短時(shí)間內得到非常多的連接請求，這就起作用。如果MySQL的連接數據達到max_connections時(shí)，新來(lái)的請求將會(huì )被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過(guò)back_log，將不被授予連接資源。
back_log值指出在MySQL暫時(shí)停止回答新請求之前的短時(shí)間內有多少個(gè)請求可以被存在堆棧中。只有如果期望在一個(gè)短時(shí)間內有很多連接，你需要增加它，換句話(huà)說(shuō)，這值對到來(lái)的TCP/IP連接的偵聽(tīng)隊列的大小。
當觀(guān)察你主機進(jìn)程列表（mysql> show full processlist），發(fā)現大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進(jìn)程時(shí)，就要加大back_log 的值了。
默認數值是50，可調優(yōu)為128，對于Linux系統設置范圍為小于512的整數。
3) interactive_timeout一個(gè)交互連接在被服務(wù)器在關(guān)閉前等待行動(dòng)的秒數。一個(gè)交互的客戶(hù)被定義為對mysql_real_connect()使用CLIENT_INTERACTIVE 選項的客戶(hù)。
默認數值是28800，可調優(yōu)為7200。
2. 緩沖區變量
全局緩沖：
4) key_buffer_sizekey_buffer_size指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。通過(guò)檢查狀態(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表，但是內部的臨時(shí)磁盤(pán)表是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個(gè)索引讀取請求，有6798830個(gè)請求在內存中沒(méi)有找到直接從硬盤(pán)讀取索引，計算索引未命中緩存的概率：
key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%，設置在1/1000左右較好
默認配置數值是8388600(8M)，主機有4GB內存，可以調優(yōu)值為268435456(256MB)。
5) query_cache_size使用查詢(xún)緩沖，MySQL將查詢(xún)結果存放在緩沖區中，今后對于同樣的SELECT語(yǔ)句（區分大小寫(xiě)），將直接從緩沖區中讀取結果。
通過(guò)檢查狀態(tài)值Qcache_*，可以知道query_cache_size設置是否合理（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現緩沖不夠的情況，如果Qcache_hits的值也非常大，則表明查詢(xún)緩沖使用非常頻繁，此時(shí)需要增加緩沖大??；如果Qcache_hits的值不大，則表明你的查詢(xún)重復率很低，這種情況下使用查詢(xún)緩沖反而會(huì )影響效率，那么可以考慮不用查詢(xún)緩沖。此外，在SELECT語(yǔ)句中加入SQL_NO_CACHE可以明確表示不使用查詢(xún)緩沖。
與查詢(xún)緩沖有關(guān)的參數還有query_cache_type、query_cache_limit、query_cache_min_res_unit。
query_cache_type指定是否使用查詢(xún)緩沖，可以設置為0、1、2，該變量是SESSION級的變量。
query_cache_limit指定單個(gè)查詢(xún)能夠使用的緩沖區大小，缺省為1M。
query_cache_min_res_unit是在4.1版本以后引入的，它指定分配緩沖區空間的最小單位，缺省為4K。檢查狀態(tài)值Qcache_free_blocks，如果該值非常大，則表明緩沖區中碎片很多，這就表明查詢(xún)結果都比較小，此時(shí)需要減小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 |
+————————————–+—————+
查詢(xún)緩存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%
如果查詢(xún)緩存碎片率超過(guò)20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢(xún)都是小數據量的話(huà)。
查詢(xún)緩存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查詢(xún)緩存利用率在25%以下的話(huà)說(shuō)明query_cache_size設置的過(guò)大，可適當減??；查詢(xún)緩存利用率在80％以上而且Qcache_lowmem_prunes > 50的話(huà)說(shuō)明query_cache_size可能有點(diǎn)小，要不就是碎片太多。
查詢(xún)緩存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例服務(wù)器查詢(xún)緩存碎片率＝20.46％，查詢(xún)緩存利用率＝62.26％，查詢(xún)緩存命中率＝1.94％，命中率很差，可能寫(xiě)操作比較頻繁吧，而且可能有些碎片。
每個(gè)連接的緩沖
6) record_buffer_size每個(gè)進(jìn)行一個(gè)順序掃描的線(xiàn)程為其掃描的每張表分配這個(gè)大小的一個(gè)緩沖區。如果你做很多順序掃描，你可能想要增加該值。
默認數值是131072(128K)，可改為16773120 (16M)
7) read_rnd_buffer_size隨機讀緩沖區大小。當按任意順序讀取行時(shí)(例如，按照排序順序)，將分配一個(gè)隨機讀緩存區。進(jìn)行排序查詢(xún)時(shí)，MySQL會(huì )首先掃描一遍該緩沖，以避免磁盤(pán)搜索，提高查詢(xún)速度，如果需要排序大量數據，可適當調高該值。但MySQL會(huì )為每個(gè)客戶(hù)連接發(fā)放該緩沖空間，所以應盡量適當設置該值，以避免內存開(kāi)銷(xiāo)過(guò)大。
一般可設置為16M
8) sort_buffer_size每個(gè)需要進(jìn)行排序的線(xiàn)程分配該大小的一個(gè)緩沖區。增加這值加速ORDER BY或GROUP BY操作。
默認數值是2097144(2M)，可改為16777208 (16M)。
9) join_buffer_size聯(lián)合查詢(xún)操作所能使用的緩沖區大小
record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size為每個(gè)線(xiàn)程獨占，也就是說(shuō)，如果有100個(gè)線(xiàn)程連接，則占用為16M*100
10) table_cache表高速緩存的大小。每當MySQL訪(fǎng)問(wèn)一個(gè)表時(shí)，如果在表緩沖區中還有空間，該表就被打開(kāi)并放入其中，這樣可以更快地訪(fǎng)問(wèn)表內容。通過(guò)檢查峰值時(shí)間的狀態(tài)值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發(fā)現open_tables等于table_cache，并且opened_tables在不斷增長(cháng)，那么你就需要增加table_cache的值了（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設置成很大的值。如果設置得太高，可能會(huì )造成文件描述符不足，從而造成性能不穩定或者連接失敗。
1G內存機器，推薦值是128－256。內存在4GB左右的服務(wù)器該參數可設置為256M或384M。
11) max_heap_table_size用戶(hù)可以創(chuàng )建的內存表(memory table)的大小。這個(gè)值用來(lái)計算內存表的最大行數值。這個(gè)變量支持動(dòng)態(tài)改變，即set @max_heap_table_size=#
這個(gè)變量和tmp_table_size一起限制了內部?jì)却姹淼拇笮?。如果某個(gè)內部heap（堆積）表大小超過(guò)tmp_table_size，MySQL可以根據需要自動(dòng)將內存中的heap表改為基于硬盤(pán)的MyISAM表。
12) tmp_table_size通過(guò)設置tmp_table_size選項來(lái)增加一張臨時(shí)表的大小，例如做高級GROUP BY操作生成的臨時(shí)表。如果調高該值，MySQL同時(shí)將增加heap表的大小，可達到提高聯(lián)接查詢(xún)速度的效果，建議盡量?jì)?yōu)化查詢(xún)，要確保查詢(xún)過(guò)程中生成的臨時(shí)表在內存中，避免臨時(shí)表過(guò)大導致生成基于硬盤(pán)的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 )建臨時(shí)表，Created_tmp_tables增加，如果臨時(shí)表大小超過(guò)tmp_table_size，則是在磁盤(pán)上創(chuàng )建臨時(shí)表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務(wù)創(chuàng )建的臨時(shí)文件文件數，比較理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服務(wù)器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，應該相當好了
默認為16M，可調到64-256最佳，線(xiàn)程獨占，太大可能內存不夠I/O堵塞
13) thread_cache_size可以復用的保存在中的線(xiàn)程的數量。如果有，新的線(xiàn)程從緩存中取得，當斷開(kāi)連接的時(shí)候如果有空間，客戶(hù)的線(xiàn)置在緩存中。如果有很多新的線(xiàn)程，為了提高性能可以這個(gè)變量值。
通過(guò)比較 Connections和Threads_created狀態(tài)的變量，可以看到這個(gè)變量的作用。
默認值為110，可調優(yōu)為80。
14) thread_concurrency推薦設置為服務(wù)器 CPU核數的2倍，例如雙核的CPU, 那么thread_concurrency的應該為4；2個(gè)雙核的cpu, thread_concurrency的值應為8。默認為8
15) wait_timeout指定一個(gè)請求的最大連接時(shí)間，對于4GB左右內存的服務(wù)器可以設置為5-10。
3. 配置InnoDB的幾個(gè)變量
innodb_buffer_pool_size
對于InnoDB表來(lái)說(shuō)，innodb_buffer_pool_size的作用就相當于key_buffer_size對于MyISAM表的作用一樣。InnoDB使用該參數指定大小的內存來(lái)緩沖數據和索引。對于單獨的MySQL數據庫服務(wù)器，最大可以把該值設置成物理內存的80%。
根據MySQL手冊，對于2G內存的機器，推薦值是1G（50%）。
innodb_flush_log_at_trx_commit
主要控制了innodb將log buffer中的數據寫(xiě)入日志文件并flush磁盤(pán)的時(shí)間點(diǎn)，取值分別為0、1、2三個(gè)。0，表示當事務(wù)提交時(shí)，不做日志寫(xiě)入操作，而是每秒鐘將log buffer中的數據寫(xiě)入日志文件并flush磁盤(pán)一次；1，則在每秒鐘或是每次事物的提交都會(huì )引起日志文件寫(xiě)入、flush磁盤(pán)的操作，確保了事務(wù)的ACID；設置為2，每次事務(wù)提交引起寫(xiě)入日志文件的動(dòng)作，但每秒鐘完成一次flush磁盤(pán)操作。
實(shí)際測試發(fā)現，該值對插入數據的速度影響非常大，設置為2時(shí)插入10000條記錄只需要2秒，設置為0時(shí)只需要1秒，而設置為1時(shí)則需要229秒。因此，MySQL手冊也建議盡量將插入操作合并成一個(gè)事務(wù)，這樣可以大幅提高速度。
根據MySQL手冊，在允許丟失最近部分事務(wù)的危險的前提下，可以把該值設為0或2。
innodb_log_buffer_size
log緩存大小，一般為1-8M，默認為1M，對于較大的事務(wù)，可以增大緩存大小。
可設置為4M或8M。
innodb_additional_mem_pool_size
該參數指定InnoDB用來(lái)存儲數據字典和其他內部數據結構的內存池大小。缺省值是1M。通常不用太大，只要夠用就行，應該與表結構的復雜度有關(guān)系。如果不夠用，MySQL會(huì )在錯誤日志中寫(xiě)入一條警告信息。
根據MySQL手冊，對于2G內存的機器，推薦值是20M，可適當增加。
innodb_thread_concurrency=8
推薦設置為 2*(NumCPUs+NumDisks)，默認一般為8
聯(lián)系客服
