目錄

一,、優(yōu)化概述

二,、查詢(xún)與索引優(yōu)化分析

1性能瓶頸定位

Show命令

慢查詢(xún)?nèi)罩?/p>

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數(shù)據(jù)庫(kù)是常見(jiàn)的兩個(gè)瓶頸是CPU和I/O的瓶頸,，CPU在飽和的時(shí)候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤(pán)上讀取數(shù)據(jù)時(shí)候。磁盤(pán)I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時(shí)候,，如果應(yīng)用分布在網(wǎng)絡(luò)上,，那么查詢(xún)量相當(dāng)大的時(shí)候那么平瓶頸就會(huì)出現(xiàn)在網(wǎng)絡(luò)上，我們可以用mpstat, iostat, sar和vmstat來(lái)查看系統(tǒng)的性能狀態(tài),。

除了服務(wù)器硬件的性能瓶頸,，對(duì)于MySQL系統(tǒng)本身，我們可以使用工具來(lái)優(yōu)化數(shù)據(jù)庫(kù)的性能,，通常有三種：使用索引,，使用EXPLAIN分析查詢(xún)以及調(diào)整MySQL的內(nèi)部配置。

在優(yōu)化MySQL時(shí),，通常需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行分析，常見(jiàn)的分析手段有慢查詢(xún)?nèi)罩?，EXPLAIN 分析查詢(xún),，profiling分析以及show命令查詢(xún)系統(tǒng)狀態(tài)及系統(tǒng)變量，通過(guò)定位分析性能的瓶頸,，才能更好的優(yōu)化數(shù)據(jù)庫(kù)系統(tǒng)的性能,。

我們可以通過(guò)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存儲(chǔ)引擎的狀態(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/

在配置文件my.cnf或my.ini中在[mysqld]一行下面加入兩個(gè)配置參數(shù)

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

long_query_time=2                                                                 

注：log-slow-queries參數(shù)為慢查詢(xún)?nèi)罩敬娣诺奈恢茫话氵@個(gè)目錄要有mysql的運(yùn)行賬號(hào)的可寫(xiě)權(quán)限,，一般都將這個(gè)目錄設(shè)置為mysql的數(shù)據(jù)存放目錄,；

long_query_time=2中的2表示查詢(xún)超過(guò)兩秒才記錄；

在my.cnf或者my.ini中添加log-queries-not-using-indexes參數(shù),，表示記錄下沒(méi)有使用索引的查詢(xún),。

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

long_query_time=10                                                               

log-queries-not-using-indexes                                             

慢查詢(xún)?nèi)罩鹃_(kāi)啟方法二：

我們可以通過(guò)命令行設(shè)置變量來(lái)即時(shí)啟動(dòng)慢日志查詢(xún),。由下圖可知慢日志沒(méi)有打開(kāi)，slow_launch_time=# 表示如果建立線程花費(fèi)了比這個(gè)值更長(zhǎng)的時(shí)間,slow_launch_threads 計(jì)數(shù)器將增加

設(shè)置慢日志開(kāi)啟

MySQL后可以查詢(xún)long_query_time 的值 ,。

為了方便測(cè)試，可以將修改慢查詢(xún)時(shí)間為5秒,。

慢查詢(xún)分析mysqldumpslow

我們可以通過(guò)打開(kāi)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)查詢(xún)時(shí)間超過(guò)5 秒的SQL，而小于5秒的沒(méi)有出現(xiàn)在此日志中,。

如果慢查詢(xún)?nèi)罩局杏涗泝?nèi)容很多,，可以使用mysqldumpslow工具（MySQL客戶(hù)端安裝自帶）來(lái)對(duì)慢查詢(xún)?nèi)罩具M(jìn)行分類(lèi)匯總。mysqldumpslow對(duì)日志文件進(jìn)行了分類(lèi)匯總,，顯示匯總后摘要結(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                      

這會(huì)輸出記錄次數(shù)最多的10條SQL語(yǔ)句,，其中：

-s, 是表示按照何種方式排序,，c、t,、l,、r分別是按照記錄次數(shù)、時(shí)間,、查詢(xún)時(shí)間,、返回的記錄數(shù)來(lái)排序，ac,、at,、al、ar,，表示相應(yīng)的倒敘,；

-t, 是top n的意思，即為返回前面多少條的數(shù)據(jù),；

-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ǔ)句，對(duì)MySQL查詢(xún)語(yǔ)句的監(jiān)控,、分析,、優(yōu)化是MySQL優(yōu)化非常重要的一步。開(kāi)啟慢查詢(xún)?nèi)罩竞?，由于日志記錄操作,，在一定程度上?huì)占用CPU資源影響mysql的性能,，但是可以階段性開(kāi)啟來(lái)定位性能瓶頸。

使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢(xún)語(yǔ)句,，從而知道MySQL是如何處理你的SQL語(yǔ)句的,。這可以幫你分析你的查詢(xún)語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸。通過(guò)explain命令可以得到:

– 表的讀取順序

– 數(shù)據(jù)讀取操作的操作類(lèi)型

– 哪些索引可以使用

– 哪些索引被實(shí)際使用

– 表之間的引用

– 每張表有多少行被優(yōu)化器查詢(xún)

EXPLAIN字段：

Table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

possible_keys：顯示可能應(yīng)用在這張表中的索引,。如果為空,，沒(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)強(qiáng)制使用一個(gè)索引或者用IGNORE INDEX（index）來(lái)強(qiáng)制忽略索引

key_len：使用的索引的長(zhǎng)度。在不損失精確性的情況下,，長(zhǎng)度越短越好

ref：顯示索引的哪一列被使用了,，如果可能的話，是一個(gè)常數(shù)

rows：MySQL認(rèn)為必須檢索的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù)

type：這是最重要的字段之一,，顯示查詢(xún)使用了何種類(lèi)型,。從最好到最差的連接類(lèi)型為system、const,、eq_reg,、ref、range,、index和ALL

nsystem,、const：可以將查詢(xún)的變量轉(zhuǎn)為常量.  如id=1; id為 主鍵或唯一鍵.

neq_ref：訪問(wèn)索引,返回某單一行的數(shù)據(jù).(通常在聯(lián)接時(shí)出現(xiàn)，查詢(xún)使用的索引為主鍵或惟一鍵)

nref：訪問(wèn)索引,返回某個(gè)值的數(shù)據(jù).(可以返回多行) 通常使用=時(shí)發(fā)生

nrange：這個(gè)連接類(lèi)型使用索引返回一個(gè)范圍中的行,，比如使用>或<查找東西,，并且該字段上建有索引時(shí)發(fā)生的情況(注:不一定好于index)

nindex：以索引的順序進(jìn)行全表掃描，優(yōu)點(diǎn)是不用排序,缺點(diǎn)是還要全表掃描

nALL：全表掃描,，應(yīng)該盡量避免

Extra：關(guān)于MYSQL如何解析查詢(xún)的額外信息,，主要有以下幾種

nusing index：只用到索引,可以避免訪問(wèn)表. 

nusing where：使用到where來(lái)過(guò)慮數(shù)據(jù). 不是所有的where clause都要顯示using where. 如以=方式訪問(wèn)索引.

nusing tmporary：用到臨時(shí)表

nusing filesort：用到額外的排序. (當(dāng)使用order by v1,而沒(méi)用到索引時(shí),就會(huì)使用額外的排序)

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

通過(guò)慢日志查詢(xún)可以知道哪些SQL語(yǔ)句執(zhí)行效率低下，通過(guò)explain我們可以得知SQL語(yǔ)句的具體執(zhí)行情況,，索引使用等,，還可以結(jié)合show命令查看執(zhí)行狀態(tài)。

如果覺(jué)得explain的信息不夠詳細(xì),，可以同通過(guò)profiling命令得到更準(zhǔn)確的SQL執(zhí)行消耗系統(tǒng)資源的信息,。

profiling默認(rèn)是關(guān)閉的?？梢酝ㄟ^(guò)以下語(yǔ)句查看

打開(kāi)功能： mysql>set profiling=1; 執(zhí)行需要測(cè)試的sql 語(yǔ)句：

mysql> show profiles\G; 可以得到被執(zhí)行的SQL語(yǔ)句的時(shí)間和ID

mysql>show profile for query 1; 得到對(duì)應(yīng)SQL語(yǔ)句執(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是針對(duì)非常簡(jiǎn)單的select語(yǔ)句的資源信息,，對(duì)于較復(fù)雜的SQL語(yǔ)句,，會(huì)有更多的行和字段，比如converting HEAP to MyISAM ,、Copying to tmp table等等,，由于以上的SQL語(yǔ)句不存在復(fù)雜的表操作，所以未顯示這些字段,。通過(guò)profiling資源耗費(fèi)信息,，我們可以采取針對(duì)性的優(yōu)化措施。

測(cè)試完畢以后 ,，關(guān)閉參數(shù)：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)建。

大多數(shù)MySQL索引(PRIMARY KEY,、UNIQUE,、INDEX和FULLTEXT)使用B樹(shù)中存儲(chǔ)?？臻g列類(lèi)型的索引使用R-樹(shù),，MEMORY表支持hash索引。

單列索引和多列索引（復(fù)合索引）

索引可以是單列索引,，也可以是多列索引,。對(duì)相關(guān)的列使用索引是提高SELECT操作性能的最佳途徑之一。

多列索引：

MySQL可以為多個(gè)列創(chuàng)建索引,。一個(gè)索引可以包括15個(gè)列,。對(duì)于某些列類(lèi)型，可以索引列的左前綴,，列的順序非常重要,。

多列索引可以視為包含通過(guò)連接索引列的值而創(chuàng)建的值的排序的數(shù)組。一般來(lái)說(shuō),，即使是限制最嚴(yán)格的單列索引,，它的限制能力也遠(yuǎn)遠(yuǎn)低于多列索引。

最左前綴

多列索引有一個(gè)特點(diǎn),，即最左前綴（Leftmost Prefixing）,。假如有一個(gè)多列索引為key(firstname lastname age),，當(dāng)搜索條件是以下各種列的組合和順序時(shí)，MySQL將使用該多列索引：

firstname,，lastname,，age

firstname，lastname

firstname

也就是說(shuō),，相當(dāng)于還建立了key(firstname lastname)和key(firstname),。

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

 快速找出匹配一個(gè)WHERE子句的行。

 刪除行,。當(dāng)執(zhí)行聯(lián)接時(shí),，從其它表檢索行。

 對(duì)具體有索引的列key_col找出MAX()或MIN()值,。由預(yù)處理器進(jìn)行優(yōu)化,，檢查是否對(duì)索引中在key_col之前發(fā)生所有關(guān)鍵字元素使用了WHERE key_part_# = constant。在這種情況下,，MySQL為每個(gè)MIN()或MAX()表達(dá)式執(zhí)行一次關(guān)鍵字查找,，并用常數(shù)替換它。如果所有表達(dá)式替換為常量,，查詢(xún)立即返回,。例如：

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

 如果對(duì)一個(gè)可用關(guān)鍵字的最左面的前綴進(jìn)行了排序或分組(例如，ORDER BY key_part_1,key_part_2),，排序或分組一個(gè)表,。如果所有關(guān)鍵字元素后面有DESC，關(guān)鍵字以倒序被讀取,。

 在一些情況中,，可以對(duì)一個(gè)查詢(xún)進(jìn)行優(yōu)化以便不用查詢(xún)數(shù)據(jù)行即可以檢索值。如果查詢(xún)只使用來(lái)自某個(gè)表的數(shù)字型并且構(gòu)成某些關(guān)鍵字的最左面前綴的列,，為了更快,，可以從索引樹(shù)檢索出值。

SELECT key_part3 FROM tb WHERE key_part1=1

有時(shí)MySQL不使用索引,，即使有可用的索引,。一種情形是當(dāng)優(yōu)化器估計(jì)到使用索引將需要MySQL訪問(wèn)表中的大部分行時(shí)。(在這種情況下,，表掃描可能會(huì)更快些）,。然而，如果此類(lèi)查詢(xún)使用LIMIT只搜索部分行,，MySQL則使用索引,，因?yàn)樗梢愿斓卣业綆仔胁⒃诮Y(jié)果中返回。例如：

合理的建立索引的建議：

(1)  越小的數(shù)據(jù)類(lèi)型通常更好：越小的數(shù)據(jù)類(lèi)型通常在磁盤(pán),、內(nèi)存和CPU緩存中都需要更少的空間,，處理起來(lái)更快,。 

(2)  簡(jiǎn)單的數(shù)據(jù)類(lèi)型更好：整型數(shù)據(jù)比起字符，處理開(kāi)銷(xiāo)更小,，因?yàn)樽址谋容^更復(fù)雜,。在MySQL中，應(yīng)該用內(nèi)置的日期和時(shí)間數(shù)據(jù)類(lèi)型,，而不是用字符串來(lái)存儲(chǔ)時(shí)間,；以及用整型數(shù)據(jù)類(lèi)型存儲(chǔ)IP地址。

(3)  盡量避免NULL：應(yīng)該指定列為NOT NULL,，除非你想存儲(chǔ)NULL,。在MySQL中，含有空值的列很難進(jìn)行查詢(xún)優(yōu)化,，因?yàn)樗鼈兪沟盟饕?、索引的統(tǒng)計(jì)信息以及比較運(yùn)算更加復(fù)雜。你應(yīng)該用0,、一個(gè)特殊的值或者一個(gè)空串代替空值

這部分是關(guān)于索引和寫(xiě)SQL語(yǔ)句時(shí)應(yīng)當(dāng)注意的一些瑣碎建議和注意點(diǎn)。

1. 當(dāng)結(jié)果集只有一行數(shù)據(jù)時(shí)使用LIMIT 1

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

從表中讀取越多的數(shù)據(jù),，查詢(xún)會(huì)變得更慢。他增加了磁盤(pán)需要操作的時(shí)間,，還是在數(shù)據(jù)庫(kù)服務(wù)器與WEB服務(wù)器是獨(dú)立分開(kāi)的情況下,。你將會(huì)經(jīng)歷非常漫長(zhǎng)的網(wǎng)絡(luò)延遲，僅僅是因?yàn)閿?shù)據(jù)不必要的在服務(wù)器之間傳輸,。

3. 使用連接（JOIN）來(lái)代替子查詢(xún)(Sub-Queries)

       連接（JOIN）.. 之所以更有效率一些,，是因?yàn)镸ySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表來(lái)完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢(xún)工作。

4. 使用ENUM,、CHAR 而不是VARCHAR,，使用合理的字段屬性長(zhǎng)度

5. 盡可能的使用NOT NULL

6. 固定長(zhǎng)度的表會(huì)更快

7. 拆分大的DELETE 或INSERT 語(yǔ)句

8. 查詢(xún)的列越小越快

 Where條件

在查詢(xún)中，WHERE條件也是一個(gè)比較重要的因素,，盡量少并且是合理的where條件是很重要的,，盡量在多個(gè)條件的時(shí)候，把會(huì)提取盡量少數(shù)據(jù)量的條件放在前面,，減少后一個(gè)where條件的查詢(xún)時(shí)間,。

有些where條件會(huì)導(dǎo)致索引無(wú)效：

 where子句的查詢(xún)條件里有！=,，MySQL將無(wú)法使用索引,。

 where子句使用了Mysql函數(shù)的時(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ú)效

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

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

1.   連接請(qǐng)求的變量：

MySQL的最大連接數(shù)，增加該值增加mysqld 要求的文件描述符的數(shù)量,。如果服務(wù)器的并發(fā)連接請(qǐng)求量比較大,，建議調(diào)高此值，以增加并行連接數(shù)量,，當(dāng)然這建立在機(jī)器能支撐的情況下,，因?yàn)槿绻B接數(shù)越多，介于MySQL會(huì)為每個(gè)連接提供連接緩沖區(qū),，就會(huì)開(kāi)銷(xiāo)越多的內(nèi)存,，所以要適當(dāng)調(diào)整該值，不能盲目提高設(shè)值,。

數(shù)值過(guò)小會(huì)經(jīng)常出現(xiàn)ERROR 1040: Too many connections錯(cuò)誤,，可以過(guò)’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è)置過(guò)低或者超過(guò)服務(wù)器負(fù)載上限了,，低于10%則設(shè)置過(guò)大。

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

back_log值指出在MySQL暫時(shí)停止回答新請(qǐng)求之前的短時(shí)間內(nèi)有多少個(gè)請(qǐng)求可以被存在堆棧中。只有如果期望在一個(gè)短時(shí)間內(nèi)有很多連接,，你需要增加它,，換句話說(shuō)，這值對(duì)到來(lái)的TCP/IP連接的偵聽(tīng)隊(duì)列的大小,。

當(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)程時(shí)，就要加大back_log 的值了,。

默認(rèn)數(shù)值是50,，可調(diào)優(yōu)為128，對(duì)于Linux系統(tǒng)設(shè)置范圍為小于512的整數(shù),。 

一個(gè)交互連接在被服務(wù)器在關(guān)閉前等待行動(dòng)的秒數(shù),。一個(gè)交互的客戶(hù)被定義為對(duì)mysql_real_connect()使用CLIENT_INTERACTIVE 選項(xiàng)的客戶(hù)。 

默認(rèn)數(shù)值是28800,，可調(diào)優(yōu)為7200,。 

2.   緩沖區(qū)變量

全局緩沖：

key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度,，尤其是索引讀的速度,。通過(guò)檢查狀態(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只對(duì)MyISAM表起作用。即使你不使用MyISAM表,，但是內(nèi)部的臨時(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，我們?cè)倏匆幌耴ey_buffer_size的使用情況：

mysql> show global status like ‘key_read%‘;

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

| Variable_name　  | Value    |

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

| Key_read_requests| 27813678764 |

| Key_reads　　　|  6798830      |

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

一共有27813678764個(gè)索引讀取請(qǐng)求,，有6798830個(gè)請(qǐng)求在內(nèi)存中沒(méi)有找到直接從硬盤(pán)讀取索引,，計(jì)算索引未命中緩存的概率：

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)。

使用查詢(xún)緩沖,，MySQL將查詢(xún)結(jié)果存放在緩沖區(qū)中,，今后對(duì)于同樣的SELECT語(yǔ)句（區(qū)分大小寫(xiě)），將直接從緩沖區(qū)中讀取結(jié)果。

通過(guò)檢查狀態(tài)值Qcache_*,，可以知道query_cache_size設(shè)置是否合理（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）,。如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況,，如果Qcache_hits的值也非常大,，則表明查詢(xún)緩沖使用非常頻繁，此時(shí)需要增加緩沖大??；如果Qcache_hits的值不大，則表明你的查詢(xún)重復(fù)率很低,，這種情況下使用查詢(xún)緩沖反而會(huì)影響效率,，那么可以考慮不用查詢(xún)緩沖。此外,，在SELECT語(yǔ)句中加入SQL_NO_CACHE可以明確表示不使用查詢(xún)緩沖,。

與查詢(xún)緩沖有關(guān)的參數(shù)還有query_cache_type、query_cache_limit,、query_cache_min_res_unit,。

query_cache_type指定是否使用查詢(xún)緩沖，可以設(shè)置為0,、1,、2，該變量是SESSION級(jí)的變量,。

query_cache_limit指定單個(gè)查詢(xún)能夠使用的緩沖區(qū)大小,，缺省為1M。

query_cache_min_res_unit是在4.1版本以后引入的,，它指定分配緩沖區(qū)空間的最小單位,，缺省為4K。檢查狀態(tài)值Qcache_free_blocks,，如果該值非常大,，則表明緩沖區(qū)中碎片很多，這就表明查詢(xún)結(jié)果都比較小,，此時(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)都是小數(shù)據(jù)量的話。

查詢(xún)緩存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查詢(xún)緩存利用率在25%以下的話說(shuō)明query_cache_size設(shè)置的過(guò)大,，可適當(dāng)減??；查詢(xún)緩存利用率在80％以上而且Qcache_lowmem_prunes > 50的話說(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è)連接的緩沖

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

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

隨機(jī)讀緩沖區(qū)大小。當(dāng)按任意順序讀取行時(shí)(例如,，按照排序順序),，將分配一個(gè)隨機(jī)讀緩存區(qū)。進(jìn)行排序查詢(xún)時(shí),，MySQL會(huì)首先掃描一遍該緩沖,，以避免磁盤(pán)搜索，提高查詢(xún)速度,，如果需要排序大量數(shù)據(jù),，可適當(dāng)調(diào)高該值。但MySQL會(huì)為每個(gè)客戶(hù)連接發(fā)放該緩沖空間,，所以應(yīng)盡量適當(dāng)設(shè)置該值,，以避免內(nèi)存開(kāi)銷(xiāo)過(guò)大。

一般可設(shè)置為16M 

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

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

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

record_buffer_size，read_rnd_buffer_size,，sort_buffer_size,，join_buffer_size為每個(gè)線程獨(dú)占，也就是說(shuō),，如果有100個(gè)線程連接,，則占用為16M*100

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

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

用戶(hù)可以創(chuàng)建的內(nèi)存表(memory table)的大小,。這個(gè)值用來(lái)計(jì)算內(nèi)存表的最大行數(shù)值。這個(gè)變量支持動(dòng)態(tài)改變,，即set @max_heap_table_size=#

這個(gè)變量和tmp_table_size一起限制了內(nèi)部?jī)?nèi)存表的大小,。如果某個(gè)內(nèi)部heap（堆積）表大小超過(guò)tmp_table_size，MySQL可以根據(jù)需要自動(dòng)將內(nèi)存中的heap表改為基于硬盤(pán)的MyISAM表,。

通過(guò)設(shè)置tmp_table_size選項(xiàng)來(lái)增加一張臨時(shí)表的大小,，例如做高級(jí)GROUP BY操作生成的臨時(shí)表。如果調(diào)高該值,，MySQL同時(shí)將增加heap表的大小,，可達(dá)到提高聯(lián)接查詢(xún)速度的效果，建議盡量?jī)?yōu)化查詢(xún),，要確保查詢(xún)過(guò)程中生成的臨時(shí)表在內(nèi)存中,，避免臨時(shí)表過(guò)大導(dǎo)致生成基于硬盤(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í)文件文件數(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堵塞

可以復(fù)用的保存在中的線程的數(shù)量,。如果有,，新的線程從緩存中取得,，當(dāng)斷開(kāi)連接的時(shí)候如果有空間，客戶(hù)的線置在緩存中,。如果有很多新的線程,，為了提高性能可以這個(gè)變量值。

通過(guò)比較 Connections和Threads_created狀態(tài)的變量,，可以看到這個(gè)變量的作用,。

默認(rèn)值為110，可調(diào)優(yōu)為80,。 

推薦設(shè)置為服務(wù)器 CPU核數(shù)的2倍,，例如雙核的CPU, 那么thread_concurrency的應(yīng)該為4；2個(gè)雙核的cpu, thread_concurrency的值應(yīng)為8,。默認(rèn)為8

指定一個(gè)請(qǐng)求的最大連接時(shí)間,，對(duì)于4GB左右內(nèi)存的服務(wù)器可以設(shè)置為5-10。

3.    配置InnoDB的幾個(gè)變量

innodb_buffer_pool_size

對(duì)于InnoDB表來(lái)說(shuō),，innodb_buffer_pool_size的作用就相當(dāng)于key_buffer_size對(duì)于MyISAM表的作用一樣,。InnoDB使用該參數(shù)指定大小的內(nèi)存來(lái)緩沖數(shù)據(jù)和索引。對(duì)于單獨(dú)的MySQL數(shù)據(jù)庫(kù)服務(wù)器,，最大可以把該值設(shè)置成物理內(nèi)存的80%,。

根據(jù)MySQL手冊(cè)，對(duì)于2G內(nèi)存的機(jī)器,，推薦值是1G（50%）,。

innodb_flush_log_at_trx_commit

主要控制了innodb將log buffer中的數(shù)據(jù)寫(xiě)入日志文件并flush磁盤(pán)的時(shí)間點(diǎn)，取值分別為0,、1,、2三個(gè)。0,，表示當(dāng)事務(wù)提交時(shí),，不做日志寫(xiě)入操作，而是每秒鐘將log buffer中的數(shù)據(jù)寫(xiě)入日志文件并flush磁盤(pán)一次,；1,，則在每秒鐘或是每次事物的提交都會(huì)引起日志文件寫(xiě)入、flush磁盤(pán)的操作,，確保了事務(wù)的ACID,；設(shè)置為2，每次事務(wù)提交引起寫(xiě)入日志文件的動(dòng)作,，但每秒鐘完成一次flush磁盤(pán)操作,。

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

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

innodb_log_buffer_size

log緩存大小,，一般為1-8M，默認(rèn)為1M,，對(duì)于較大的事務(wù),，可以增大緩存大小。

可設(shè)置為4M或8M,。

innodb_additional_mem_pool_size

該參數(shù)指定InnoDB用來(lái)存儲(chǔ)數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小,。缺省值是1M。通常不用太大,，只要夠用就行,，應(yīng)該與表結(jié)構(gòu)的復(fù)雜度有關(guān)系。如果不夠用,，MySQL會(huì)在錯(cuò)誤日志中寫(xiě)入一條警告信息,。

根據(jù)MySQL手冊(cè)，對(duì)于2G內(nèi)存的機(jī)器,，推薦值是20M,，可適當(dāng)增加。

innodb_thread_concurrency=8

推薦設(shè)置為 2*(NumCPUs+NumDisks),，默認(rèn)一般為8