目錄
一鲤嫡、優(yōu)化概述
二送挑、查詢與索引優(yōu)化分析
1性能瓶頸定位
Show命令
慢查詢?nèi)罩?/p>
explain分析查詢
profiling分析查詢
2索引及查詢優(yōu)化
三、配置優(yōu)化
max_connections
back_log
interactive_timeout
key_buffer_size
query_cache_size
record_buffer_size
read_rnd_buffer_size
sort_buffer_size
join_buffer_size
table_cache
max_heap_table_size
tmp_table_size
thread_cache_size
thread_concurrency
wait_timeout
一暖眼、 優(yōu)化概述
MySQL數(shù)據(jù)庫是常見的兩個瓶頸是CPU和I/O的瓶頸惕耕，CPU在飽和的時候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤上讀取數(shù)據(jù)時候。磁盤I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時候诫肠，如果應(yīng)用分布在網(wǎng)絡(luò)上司澎，那么查詢量相當(dāng)大的時候那么平瓶頸就會出現(xiàn)在網(wǎng)絡(luò)上，我們可以用mpstat, iostat, sar和vmstat來查看系統(tǒng)的性能狀態(tài)区赵。
除了服務(wù)器硬件的性能瓶頸，對于MySQL系統(tǒng)本身浪南，我們可以使用工具來優(yōu)化數(shù)據(jù)庫的性能笼才，通常有三種：使用索引，使用EXPLAIN分析查詢以及調(diào)整MySQL的內(nèi)部配置络凿。
二骡送、查詢與索引優(yōu)化分析
在優(yōu)化MySQL時，通常需要對數(shù)據(jù)庫進(jìn)行分析絮记，常見的分析手段有慢查詢?nèi)罩舅猓珽XPLAIN 分析查詢，profiling分析以及show命令查詢系統(tǒng)狀態(tài)及系統(tǒng)變量怨愤，通過定位分析性能的瓶頸派敷，才能更好的優(yōu)化數(shù)據(jù)庫系統(tǒng)的性能。
1 性能瓶頸定位Show命令
我們可以通過show命令查看MySQL狀態(tài)及變量，找到系統(tǒng)的瓶頸：
Mysql> show status ——顯示狀態(tài)信息（擴(kuò)展show status like ‘XXX’）
Mysql> show variables ——顯示系統(tǒng)變量（擴(kuò)展show variables like ‘XXX’）
Mysql> show innodb status ——顯示InnoDB存儲引擎的狀態(tài)
Mysql> show processlist ——查看當(dāng)前SQL執(zhí)行篮愉，包括執(zhí)行狀態(tài)腐芍、是否鎖表等
Shell> mysqladmin variables -u username -p password——顯示系統(tǒng)變量
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/
慢查詢?nèi)罩?/p>
慢查詢?nèi)罩鹃_啟：
在配置文件my.cnf或my.ini中在[mysqld]一行下面加入兩個配置參數(shù)
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=2
注：log-slow-queries參數(shù)為慢查詢?nèi)罩敬娣诺奈恢茫话氵@個目錄要有mysql的運(yùn)行帳號的可寫權(quán)限试躏，一般都將這個目錄設(shè)置為mysql的數(shù)據(jù)存放目錄猪勇；
long_query_time=2中的2表示查詢超過兩秒才記錄；
在my.cnf或者my.ini中添加log-queries-not-using-indexes參數(shù)颠蕴，表示記錄下沒有使用索引的查詢泣刹。
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=10
log-queries-not-using-indexes
慢查詢?nèi)罩鹃_啟方法二：
我們可以通過命令行設(shè)置變量來即時啟動慢日志查詢。由下圖可知慢日志沒有打開犀被，slow_launch_time=# 表示如果建立線程花費(fèi)了比這個值更長的時間,slow_launch_threads 計數(shù)器將增加
設(shè)置慢日志開啟
MySQL后可以查詢long_query_time 的值 椅您。
為了方便測試，可以將修改慢查詢時間為5秒弱判。
慢查詢分析mysqldumpslow
我們可以通過打開log文件查看得知哪些SQL執(zhí)行效率低下
[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ā)現(xiàn)查詢時間超過5 秒的SQL，而小于5秒的沒有出現(xiàn)在此日志中昌腰。
如果慢查詢?nèi)罩局杏涗泝?nèi)容很多开伏，可以使用mysqldumpslow工具（MySQL客戶端安裝自帶）來對慢查詢?nèi)罩具M(jìn)行分類匯總。mysqldumpslow對日志文件進(jìn)行了分類匯總遭商，顯示匯總后摘要結(jié)果固灵。
進(jìn)入log的存放目錄，運(yùn)行
[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
這會輸出記錄次數(shù)最多的10條SQL語句劫流，其中：
-s, 是表示按照何種方式排序巫玻，c、t祠汇、l仍秤、r分別是按照記錄次數(shù)、時間可很、查詢時間诗力、返回的記錄數(shù)來排序，ac我抠、at苇本、al、ar菜拓，表示相應(yīng)的倒敘瓣窄；
-t, 是top n的意思，即為返回前面多少條的數(shù)據(jù)纳鼎；
-g, 后邊可以寫一個正則匹配模式俺夕，大小寫不敏感的裳凸；
例如：
/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
得到返回記錄集最多的10個查詢。
/path/mysqldumpslow -s t -t 10 -g “l(fā)eft join” /database/mysql/slow-log
得到按照時間排序的前10條里面含有左連接的查詢語句啥么。
使用mysqldumpslow命令可以非常明確的得到各種我們需要的查詢語句登舞，對MySQL查詢語句的監(jiān)控、分析悬荣、優(yōu)化是MySQL優(yōu)化非常重要的一步菠秒。開啟慢查詢?nèi)罩竞螅捎谌罩居涗洸僮髀扔兀谝欢ǔ潭壬蠒加肅PU資源影響mysql的性能践叠，但是可以階段性開啟來定位性能瓶頸。
explain分析查詢
使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語句嚼蚀，從而知道MySQL是如何處理你的SQL語句的禁灼。這可以幫你分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸。通過explain命令可以得到:
– 表的讀取順序
– 數(shù)據(jù)讀取操作的操作類型
– 哪些索引可以使用
– 哪些索引被實(shí)際使用
– 表之間的引用
– 每張表有多少行被優(yōu)化器查詢
EXPLAIN字段：
?Table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
?possible_keys：顯示可能應(yīng)用在這張表中的索引轿曙。如果為空弄捕，沒有可能的索引〉嫉郏可以為相關(guān)的域從WHERE語句中選擇一個合適的語句
?key：實(shí)際使用的索引守谓。如果為NULL，則沒有使用索引您单。MYSQL很少會選擇優(yōu)化不足的索引斋荞，此時可以在SELECT語句中使用USE INDEX（index）來強(qiáng)制使用一個索引或者用IGNORE INDEX（index）來強(qiáng)制忽略索引
?key_len：使用的索引的長度。在不損失精確性的情況下虐秦，長度越短越好
?ref：顯示索引的哪一列被使用了平酿，如果可能的話，是一個常數(shù)
?rows：MySQL認(rèn)為必須檢索的用來返回請求數(shù)據(jù)的行數(shù)
?type：這是最重要的字段之一悦陋，顯示查詢使用了何種類型蜈彼。從最好到最差的連接類型為system、const俺驶、eq_reg幸逆、ref、range痒钝、index和ALL
system秉颗、const：可以將查詢的變量轉(zhuǎn)為常量. 如id=1; id為 主鍵或唯一鍵.
eq_ref：訪問索引,返回某單一行的數(shù)據(jù).(通常在聯(lián)接時出現(xiàn)痢毒，查詢使用的索引為主鍵或惟一鍵)
ref：訪問索引,返回某個值的數(shù)據(jù).(可以返回多行) 通常使用=時發(fā)生
range：這個連接類型使用索引返回一個范圍中的行送矩，比如使用>或<查找東西，并且該字段上建有索引時發(fā)生的情況(注:不一定好于index)
nindex：以索引的順序進(jìn)行全表掃描哪替，優(yōu)點(diǎn)是不用排序,缺點(diǎn)是還要全表掃描
ALL：全表掃描栋荸，應(yīng)該盡量避免
?Extra：關(guān)于MYSQL如何解析查詢的額外信息，主要有以下幾種
using index：只用到索引,可以避免訪問表.
using where：使用到where來過慮數(shù)據(jù). 不是所有的where clause都要顯示using where. 如以=方式訪問索引.
using tmporary：用到臨時表
using filesort：用到額外的排序. (當(dāng)使用order by v1,而沒用到索引時,就會使用額外的排序)
range checked for eache record(index map:N)：沒有好的索引.
profiling分析查詢
通過慢日志查詢可以知道哪些SQL語句執(zhí)行效率低下，通過explain我們可以得知SQL語句的具體執(zhí)行情況晌块，索引使用等爱沟，還可以結(jié)合show命令查看執(zhí)行狀態(tài)。
如果覺得explain的信息不夠詳細(xì)匆背，可以同通過profiling命令得到更準(zhǔn)確的SQL執(zhí)行消耗系統(tǒng)資源的信息呼伸。
profiling默認(rèn)是關(guān)閉的《凼可以通過以下語句查看
打開功能： mysql>set profiling=1; 執(zhí)行需要測試的sql 語句：
mysql> show profiles\G; 可以得到被執(zhí)行的SQL語句的時間和ID
mysql>show profile for query 1; 得到對應(yīng)SQL語句執(zhí)行的詳細(xì)信息
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語句的資源信息括享，對于較復(fù)雜的SQL語句，會有更多的行和字段珍促，比如converting HEAP to MyISAM 铃辖、Copying to tmp table等等，由于以上的SQL語句不存在復(fù)雜的表操作猪叙，所以未顯示這些字段娇斩。通過profiling資源耗費(fèi)信息搓扯，我們可以采取針對性的優(yōu)化措施襟己。
測試完畢以后 ，關(guān)閉參數(shù)：mysql> set profiling=0
2 索引及查詢優(yōu)化
索引的類型
? 普通索引：這是最基本的索引類型假哎，沒唯一性之類的限制藏否。
? 唯一性索引：和普通索引基本相同瓶殃，但所有的索引列值保持唯一性。
? 主鍵：主鍵是一種唯一索引副签，但必須指定為”PRIMARY KEY”遥椿。
? 全文索引：MYSQL從3.23.23開始支持全文索引和全文檢索。在MYSQL中淆储，全文索引的索引類型為FULLTEXT冠场。全文索引可以在VARCHAR或者TEXT類型的列上創(chuàng)建。
大多數(shù)MySQL索引(PRIMARY KEY本砰、UNIQUE碴裙、INDEX和FULLTEXT)使用B樹中存儲〉愣睿空間列類型的索引使用R-樹舔株，MEMORY表支持hash索引。
單列索引和多列索引（復(fù)合索引）
索引可以是單列索引还棱，也可以是多列索引载慈。對相關(guān)的列使用索引是提高SELECT操作性能的最佳途徑之一。
多列索引：
MySQL可以為多個列創(chuàng)建索引珍手。一個索引可以包括15個列办铡。對于某些列類型辞做，可以索引列的左前綴，列的順序非常重要寡具。
多列索引可以視為包含通過連接索引列的值而創(chuàng)建的值的排序的數(shù)組秤茅。一般來說，即使是限制最嚴(yán)格的單列索引童叠，它的限制能力也遠(yuǎn)遠(yuǎn)低于多列索引框喳。
最左前綴
多列索引有一個特點(diǎn)，即最左前綴（Leftmost Prefixing）厦坛。假如有一個多列索引為key(firstname lastname age)帖努，當(dāng)搜索條件是以下各種列的組合和順序時，MySQL將使用該多列索引：
firstname粪般，lastname拼余，age
firstname，lastname
firstname
也就是說亩歹，相當(dāng)于還建立了key(firstname lastname)和key(firstname)匙监。
索引主要用于下面的操作：
? 快速找出匹配一個WHERE子句的行。
? 刪除行小作。當(dāng)執(zhí)行聯(lián)接時亭姥，從其它表檢索行。
? 對具體有索引的列key_col找出MAX()或MIN()值顾稀。由預(yù)處理器進(jìn)行優(yōu)化达罗，檢查是否對索引中在key_col之前發(fā)生所有關(guān)鍵字元素使用了WHERE key_part_# = constant。在這種情況下静秆，MySQL為每個MIN()或MAX()表達(dá)式執(zhí)行一次關(guān)鍵字查找粮揉，并用常數(shù)替換它。如果所有表達(dá)式替換為常量抚笔，查詢立即返回扶认。例如：
SELECT MIN(key2), MAX (key2) FROM tb WHERE key1=10;
? 如果對一個可用關(guān)鍵字的最左面的前綴進(jìn)行了排序或分組(例如，ORDER BY key_part_1,key_part_2)殊橙，排序或分組一個表辐宾。如果所有關(guān)鍵字元素后面有DESC，關(guān)鍵字以倒序被讀取膨蛮。
? 在一些情況中叠纹，可以對一個查詢進(jìn)行優(yōu)化以便不用查詢數(shù)據(jù)行即可以檢索值。如果查詢只使用來自某個表的數(shù)字型并且構(gòu)成某些關(guān)鍵字的最左面前綴的列敞葛，為了更快誉察，可以從索引樹檢索出值。
SELECT key_part3 FROM tb WHERE key_part1=1
有時MySQL不使用索引制肮，即使有可用的索引冒窍。一種情形是當(dāng)優(yōu)化器估計到使用索引將需要MySQL訪問表中的大部分行時。(在這種情況下豺鼻，表掃描可能會更快些）综液。然而，如果此類查詢使用LIMIT只搜索部分行儒飒，MySQL則使用索引谬莹，因為它可以更快地找到幾行并在結(jié)果中返回。例如：
合理的建立索引****的建議：
(1) 越小的數(shù)據(jù)類型通常更好：越小的數(shù)據(jù)類型通常在磁盤桩了、內(nèi)存和CPU緩存中都需要更少的空間附帽，處理起來更快。
(2) 簡單的數(shù)據(jù)類型更好：整型數(shù)據(jù)比起字符井誉，處理開銷更小蕉扮，因為字符串的比較更復(fù)雜。在MySQL中颗圣，應(yīng)該用內(nèi)置的日期和時間數(shù)據(jù)類型喳钟，而不是用字符串來存儲時間；以及用整型數(shù)據(jù)類型存儲IP地址在岂。
(3) 盡量避免NULL：應(yīng)該指定列為NOT NULL奔则，除非你想存儲NULL。在MySQL中蔽午，含有空值的列很難進(jìn)行查詢優(yōu)化易茬，因為它們使得索引、索引的統(tǒng)計信息以及比較運(yùn)算更加復(fù)雜及老。你應(yīng)該用0抽莱、一個特殊的值或者一個空串代替空值
這部分是關(guān)于索引和寫SQL語句時應(yīng)當(dāng)注意的一些瑣碎建議和注意點(diǎn)。
1. ****當(dāng)結(jié)果集只有一行數(shù)據(jù)時使用****LIMIT 1
2. ****避免****SELECT *****骄恶，始終指定你需要的列
從表中讀取越多的數(shù)據(jù)岸蜗，查詢會變得更慢。他增加了磁盤需要操作的時間叠蝇，還是在數(shù)據(jù)庫服務(wù)器與WEB服務(wù)器是獨(dú)立分開的情況下璃岳。你將會經(jīng)歷非常漫長的網(wǎng)絡(luò)延遲，僅僅是因為數(shù)據(jù)不必要的在服務(wù)器之間傳輸悔捶。
3. ****使用連接（****JOIN****）來代替子查詢****(Sub-Queries)
連接（JOIN）.. 之所以更有效率一些铃慷，是因為MySQL不需要在內(nèi)存中創(chuàng)建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。
4. ****使用****ENUM****蜕该、****CHAR ****而不是****VARCHAR****犁柜，使用合理的字段屬性長度
5. ****盡可能的使用****NOT NULL
6. ****固定長度的表會更快
7. ****拆分大的****DELETE ****或****INSERT ****語句
8. ****查詢的列越小越快
Where條件
在查詢中，WHERE條件也是一個比較重要的因素堂淡，盡量少并且是合理的where條件是很重要的馋缅，盡量在多個條件的時候扒腕，把會提取盡量少數(shù)據(jù)量的條件放在前面，減少后一個where條件的查詢時間萤悴。
有些where條件會導(dǎo)致索引無效：
? where子句的查詢條件里有瘾腰！=，MySQL將無法使用索引覆履。
? where子句使用了Mysql函數(shù)的時候蹋盆，索引將無效，比如：select * from tb where left(name, 4) = ‘xxx’
? 使用LIKE進(jìn)行搜索匹配的時候硝全，這樣索引是有效的：select * from tbl1 where name like ‘xxx%’栖雾，而like ‘%xxx%’ 時索引無效
三、 配置優(yōu)化
安裝MySQL后伟众，配置文件my.cnf在 /MySQL安裝目錄/share/mysql目錄中析藕，該目錄中還包含多個配置文件可供參考，有my-large.cnf 凳厢，my-huge.cnf噪径， my-medium.cnf，my-small.cnf数初，分別對應(yīng)大中小型數(shù)據(jù)庫應(yīng)用的配置找爱。win環(huán)境下即存在于MySQL安裝目錄中的.ini文件。
下面列出了對性能優(yōu)化影響較大的主要變量泡孩，主要分為連接請求的變量和緩沖區(qū)變量车摄。
1. ******連接請求的變量：
max_connections
MySQL的最大連接數(shù)，增加該值增加mysqld 要求的文件描述符的數(shù)量仑鸥。如果服務(wù)器的并發(fā)連接請求量比較大吮播，建議調(diào)高此值，以增加并行連接數(shù)量眼俊，當(dāng)然這建立在機(jī)器能支撐的情況下意狠，因為如果連接數(shù)越多，介于MySQL會為每個連接提供連接緩沖區(qū)疮胖，就會開銷越多的內(nèi)存环戈，所以要適當(dāng)調(diào)整該值，不能盲目提高設(shè)值澎灸。
數(shù)值過小會經(jīng)常出現(xiàn)ERROR 1040: Too many connections錯誤院塞，可以過’conn%’通配符查看當(dāng)前狀態(tài)的連接數(shù)量，以定奪該值的大小性昭。
show variables like ‘max_connections’ 最大連接數(shù)
show status like ‘max_used_connections’響應(yīng)的連接數(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設(shè)置過低或者超過服務(wù)器負(fù)載上限了拦止，低于10%則設(shè)置過大。
back_log
MySQL能暫存的連接數(shù)量。當(dāng)主要MySQL線程在一個很短時間內(nèi)得到非常多的連接請求汹族，這就起作用萧求。如果MySQL的連接數(shù)據(jù)達(dá)到max_connections時，新來的請求將會被存在堆棧中顶瞒，以等待某一連接釋放資源夸政，該堆棧的數(shù)量即back_log，如果等待連接的數(shù)量超過back_log搁拙，將不被授予連接資源。
back_log值指出在MySQL暫時停止回答新請求之前的短時間內(nèi)有多少個請求可以被存在堆棧中法绵。只有如果期望在一個短時間內(nèi)有很多連接箕速，你需要增加它，換句話說朋譬，這值對到來的TCP/IP連接的偵聽隊列的大小盐茎。
當(dāng)觀察你主機(jī)進(jìn)程列表（mysql> show full processlist），發(fā)現(xiàn)大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進(jìn)程時徙赢，就要加大back_log 的值了字柠。
默認(rèn)數(shù)值是50，可調(diào)優(yōu)為128狡赐，對于Linux系統(tǒng)設(shè)置范圍為小于512的整數(shù)窑业。
interactive_timeout
一個交互連接在被服務(wù)器在關(guān)閉前等待行動的秒數(shù)。一個交互的客戶被定義為對mysql_real_connect()使用CLIENT_INTERACTIVE 選項的客戶枕屉。
默認(rèn)數(shù)值是28800常柄，可調(diào)優(yōu)為7200。
2. ******緩沖區(qū)變量
全局緩沖：
key_buffer_size
key_buffer_size指定索引緩沖區(qū)的大小搀擂，它決定索引處理的速度西潘，尤其是索引讀的速度。通過檢查狀態(tài)值Key_read_requests和Key_reads哨颂，可以知道key_buffer_size設(shè)置是否合理喷市。比例key_reads / key_read_requests應(yīng)該盡可能的低，至少是1:100威恼，1:1000更好（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘key_read%’獲得）品姓。
key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表箫措，但是內(nèi)部的臨時磁盤表是MyISAM表缭黔，也要使用該值〉倨疲可以使用檢查狀態(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個請求在內(nèi)存中沒有找到直接從硬盤讀取索引惧互，計算索引未命中緩存的概率：
key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%哎媚，設(shè)置在1/1000左右較好
默認(rèn)配置數(shù)值是8388600(8M)，主機(jī)有4GB內(nèi)存喊儡，可以調(diào)優(yōu)值為268435456(256MB)拨与。
query_cache_size
使用查詢緩沖，MySQL將查詢結(jié)果存放在緩沖區(qū)中艾猜，今后對于同樣的SELECT語句（區(qū)分大小寫）买喧，將直接從緩沖區(qū)中讀取結(jié)果。
通過檢查狀態(tài)值Qcache_*匆赃，可以知道query_cache_size設(shè)置是否合理（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）淤毛。如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況算柳，如果Qcache_hits的值也非常大低淡，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大兴蚕睢蔗蹋；如果Qcache_hits的值不大，則表明你的查詢重復(fù)率很低囱淋，這種情況下使用查詢緩沖反而會影響效率猪杭，那么可以考慮不用查詢緩沖。此外妥衣，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖胁孙。
與查詢緩沖有關(guān)的參數(shù)還有query_cache_type、query_cache_limit称鳞、query_cache_min_res_unit涮较。
query_cache_type指定是否使用查詢緩沖，可以設(shè)置為0冈止、1狂票、2，該變量是SESSION級的變量熙暴。
query_cache_limit指定單個查詢能夠使用的緩沖區(qū)大小闺属，缺省為1M。
query_cache_min_res_unit是在4.1版本以后引入的周霉，它指定分配緩沖區(qū)空間的最小單位掂器，缺省為4K。檢查狀態(tài)值Qcache_free_blocks俱箱，如果該值非常大国瓮，則表明緩沖區(qū)中碎片很多，這就表明查詢結(jié)果都比較小，此時需要減小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設(shè)置的過大，可適當(dāng)減嘘痢秘狞；查詢緩存利用率在80％以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點(diǎn)小，要不就是碎片太多蹈集。
查詢緩存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例服務(wù)器查詢緩存碎片率＝20.46％烁试，查詢緩存利用率＝62.26％，查詢緩存命中率＝1.94％雾狈，命中率很差廓潜，可能寫操作比較頻繁吧抵皱，而且可能有些碎片善榛。
每個連接的緩沖
- record_buffer_size
每個進(jìn)行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)。如果你做很多順序掃描呻畸，你可能想要增加該值移盆。
默認(rèn)數(shù)值是131072(128K)，可改為16773120 (16M)
read_rnd_buffer_size
隨機(jī)讀緩沖區(qū)大小伤为。當(dāng)按任意順序讀取行時(例如咒循，按照排序順序)，將分配一個隨機(jī)讀緩存區(qū)绞愚。進(jìn)行排序查詢時叙甸，MySQL會首先掃描一遍該緩沖，以避免磁盤搜索位衩，提高查詢速度裆蒸，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值糖驴。但MySQL會為每個客戶連接發(fā)放該緩沖空間僚祷，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開銷過大贮缕。
一般可設(shè)置為16M
sort_buffer_size
每個需要進(jìn)行排序的線程分配該大小的一個緩沖區(qū)辙谜。增加這值加速ORDER BY或GROUP BY操作。
默認(rèn)數(shù)值是2097144(2M)感昼，可改為16777208 (16M)装哆。
join_buffer_size
聯(lián)合查詢操作所能使用的緩沖區(qū)大小
record_buffer_size****，read_rnd_buffer_size，sort_buffer_size烂琴，join_buffer_size為每個線程獨(dú)占爹殊，也就是說，如果有100個線程連接奸绷，則占用為16M100*
- table_cache
表高速緩存的大小梗夸。每當(dāng)MySQL訪問一個表時，如果在表緩沖區(qū)中還有空間号醉，該表就被打開并放入其中反症，這樣可以更快地訪問表內(nèi)容。通過檢查峰值時間的狀態(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設(shè)置成很大的值憨愉。如果設(shè)置得太高烦绳，可能會造成文件描述符不足，從而造成性能不穩(wěn)定或者連接失敗配紫。
1G內(nèi)存機(jī)器径密，推薦值是128－256。內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為256M或384M躺孝。
- max_heap_table_size
用戶可以創(chuàng)建的內(nèi)存表(memory table)的大小享扔。這個值用來計算內(nèi)存表的最大行數(shù)值。這個變量支持動態(tài)改變植袍，即set @max_heap_table_size=#
這個變量和tmp_table_size一起限制了內(nèi)部內(nèi)存表的大小惧眠。如果某個內(nèi)部heap（堆積）表大小超過tmp_table_size，MySQL可以根據(jù)需要自動將內(nèi)存中的heap表改為基于硬盤的MyISAM表于个。
- tmp_table_size
通過設(shè)置tmp_table_size選項來增加一張臨時表的大小氛魁，例如做高級GROUP BY操作生成的臨時表。如果調(diào)高該值览濒，MySQL同時將增加heap表的大小呆盖，可達(dá)到提高聯(lián)接查詢速度的效果，建議盡量優(yōu)化查詢贷笛，要確保查詢過程中生成的臨時表在內(nèi)存中应又，避免臨時表過大導(dǎo)致生成基于硬盤的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服務(wù)創(chuàng)建的臨時文件文件數(shù)洞就，比較理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服務(wù)器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%盆繁，應(yīng)該相當(dāng)好了
默認(rèn)為16M，可調(diào)到64-256最佳旬蟋，線程獨(dú)占油昂，太大可能內(nèi)存不夠I/O堵塞
- thread_cache_size
可以復(fù)用的保存在中的線程的數(shù)量。如果有倾贰，新的線程從緩存中取得冕碟，當(dāng)斷開連接的時候如果有空間，客戶的線置在緩存中匆浙。如果有很多新的線程安寺，為了提高性能可以這個變量值。
通過比較 Connections和Threads_created狀態(tài)的變量首尼，可以看到這個變量的作用挑庶。
默認(rèn)值為110，可調(diào)優(yōu)為80软能。
- thread_concurrency
推薦設(shè)置為服務(wù)器 CPU核數(shù)的2倍迎捺，例如雙核的CPU, 那么thread_concurrency的應(yīng)該為4；2個雙核的cpu, thread_concurrency的值應(yīng)為8埋嵌。默認(rèn)為8
- wait_timeout
指定一個請求的最大連接時間破加，對于4GB左右內(nèi)存的服務(wù)器可以設(shè)置為5-10俱恶。
*3. *配置InnoDB的幾個變量
innodb_buffer_pool_size
對于InnoDB表來說雹嗦，innodb_buffer_pool_size的作用就相當(dāng)于key_buffer_size對于MyISAM表的作用一樣。InnoDB使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)和索引合是。對于單獨(dú)的MySQL數(shù)據(jù)庫服務(wù)器了罪，最大可以把該值設(shè)置成物理內(nèi)存的80%。
根據(jù)MySQL手冊聪全，對于2G內(nèi)存的機(jī)器泊藕，推薦值是1G（50%）。
innodb_flush_log_at_trx_commit
主要控制了innodb將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤的時間點(diǎn)难礼，取值分別為0娃圆、1、2三個蛾茉。0讼呢，表示當(dāng)事務(wù)提交時，不做日志寫入操作谦炬，而是每秒鐘將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤一次悦屏；1节沦，則在每秒鐘或是每次事物的提交都會引起日志文件寫入、flush磁盤的操作础爬，確保了事務(wù)的ACID甫贯；設(shè)置為2虹统，每次事務(wù)提交引起寫入日志文件的動作笆制，但每秒鐘完成一次flush磁盤操作。
實(shí)際測試發(fā)現(xiàn)逞度，該值對插入數(shù)據(jù)的速度影響非常大供炎，設(shè)置為2時插入10000條記錄只需要2秒常熙，設(shè)置為0時只需要1秒，而設(shè)置為1時則需要229秒碱茁。因此裸卫，MySQL手冊也建議盡量將插入操作合并成一個事務(wù)，這樣可以大幅提高速度纽竣。
根據(jù)MySQL手冊墓贿，在允許丟失最近部分事務(wù)的危險的前提下，可以把該值設(shè)為0或2蜓氨。
innodb_log_buffer_size
log緩存大小聋袋，一般為1-8M，默認(rèn)為1M穴吹，對于較大的事務(wù)幽勒，可以增大緩存大小。
可設(shè)置為4M或8M港令。
innodb_additional_mem_pool_size
該參數(shù)指定InnoDB用來存儲數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小啥容。缺省值是1M。通常不用太大顷霹，只要夠用就行咪惠，應(yīng)該與表結(jié)構(gòu)的復(fù)雜度有關(guān)系。如果不夠用淋淀，MySQL會在錯誤日志中寫入一條警告信息遥昧。
根據(jù)MySQL手冊，對于2G內(nèi)存的機(jī)器朵纷，推薦值是20M炭臭，可適當(dāng)增加。
innodb_thread_concurrency=8
推薦設(shè)置為 2*(NumCPUs+NumDisks)袍辞，默認(rèn)一般為8
