mysql性能優(yōu)化-慢查詢分析、優(yōu)化索引和配置VIP

1、mysql性能優(yōu)化-慢查詢分析、優(yōu)化索引和配置目錄一、優(yōu)化概述二、查詢與索引優(yōu)化分析1性能瓶頸定位Show命令慢查詢?nèi)罩緀xplain分析查詢profiling分析查詢 2索引及查詢優(yōu)化三、配置優(yōu)化1)      max_connections2)      back_log3)      interactive_timeout4)      key_buffer_size5)      query_cache_size6)  

2、;    record_buffer_size7)      read_rnd_buffer_size8)      sort_buffer_size9)      join_buffer_size10)    table_cache11)    max_heap_table_size12)    tmp_table_size13)    thread_cache_size14)    

3、thread_concurrency15)    wait_timeout 一、 優(yōu)化概述MySQL數(shù)據(jù)庫(kù)是常見的兩個(gè)瓶頸是CPU和I/O的瓶頸，CPU在飽和的時(shí)候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤上讀取數(shù)據(jù)時(shí)候。磁盤I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時(shí)候，如果應(yīng)用分布在網(wǎng)絡(luò)上，那么查詢量相當(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分析查詢以及調(diào)整M

4、ySQL的內(nèi)部配置。二、查詢與索引優(yōu)化分析在優(yōu)化MySQL時(shí)，通常需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行分析，常見的分析手段有慢查詢?nèi)罩?，EXPLAIN 分析查詢，profiling分析以及show命令查詢系統(tǒng)狀態(tài)及系統(tǒng)變量，通過定位分析性能的瓶頸，才能更好的優(yōu)化數(shù)據(jù)庫(kù)系統(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>

5、; 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命令的使用可參考： 慢查詢?nèi)罩韭樵內(nèi)罩鹃_啟：在

6、配置文件f或my.ini中在mysqld一行下面加入兩個(gè)配置參數(shù)log-slow-queries=/data/mysqldata/slow-query.log           long_query_time=2                       

7、                                          注：log-slow-queries參數(shù)為慢查詢?nèi)罩敬娣诺奈恢?，一般這個(gè)目錄要有mysql的運(yùn)行帳號(hào)的可寫

8、權(quán)限，一般都將這個(gè)目錄設(shè)置為mysql的數(shù)據(jù)存放目錄；long_query_time=2中的2表示查詢超過兩秒才記錄；在f或者my.ini中添加log-queries-not-using-indexes參數(shù)，表示記錄下沒有使用索引的查詢。log-slow-queries=/data/mysqldata/slow-query.log           long_query_time=10        &#

9、160;                                                 &#

10、160;    log-queries-not-using-indexes                                        

11、60;    慢查詢?nèi)罩鹃_啟方法二：我們可以通過命令行設(shè)置變量來(lái)即時(shí)啟動(dòng)慢日志查詢。由下圖可知慢日志沒有打開，slow_launch_time=# 表示如果建立線程花費(fèi)了比這個(gè)值更長(zhǎng)的時(shí)間,slow_launch_threads 計(jì)數(shù)器將增加設(shè)置慢日志開啟MySQL后可以查詢long_query_time 的值 。 為了方便測(cè)試，可以將修改慢查詢時(shí)間為5秒。慢查詢分析mysqldumpslow我們可以通過打開log文件查看得知哪些SQL執(zhí)行效率低下rootlocalhost mysql# more slow-que

12、ry.log                            # Time: 081026 19:46:34                  

13、                                                  

14、      # UserHost: rootroot  localhost                                    &#

15、160;                       # Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961        select

16、0;count(*) from t_user;                                            

17、60;                                   從日志中，可以發(fā)現(xiàn)查詢時(shí)間超過5 秒的SQL，而小于5秒的沒有出現(xiàn)在此日志中。如果慢查詢?nèi)罩局杏涗泝?nèi)容很多，可以使用mysqldumpslow工具（MySQL客戶端安裝自帶）

18、來(lái)對(duì)慢查詢?nèi)罩具M(jìn)行分類匯總。mysqldumpslow對(duì)日志文件進(jìn)行了分類匯總，顯示匯總后摘要結(jié)果。進(jìn)入log的存放目錄，運(yùn)行rootmysql_data#mysqldumpslow  slow-query.log                                &

19、#160;Reading mysql slow query log from slow-query.log                            Count: 2 Time=11.00s (22s) Lock=0.00s&#

20、160;(0s) Rows=1.0 (2), rootrootmysql    select count(N) from t_user;                               &

21、#160;                mysqldumpslow命令/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log                    

22、60; 這會(huì)輸出記錄次數(shù)最多的10條SQL語(yǔ)句，其中：-s, 是表示按照何種方式排序，c、t、l、r分別是按照記錄次數(shù)、時(shí)間、查詢時(shí)間、返回的記錄數(shù)來(lái)排序，ac、at、al、ar，表示相應(yīng)的倒敘；-t, 是top n的意思，即為返回前面多少條的數(shù)據(jù)；-g, 后邊可以寫一個(gè)正則匹配模式，大小寫不敏感的；例如：/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log              &#

23、160;                  得到返回記錄集最多的10個(gè)查詢。/path/mysqldumpslow -s t -t 10 -g “l(fā)eft join” /database/mysql/slow-log       得到按照時(shí)間排序的前10條里面含有左連接的查詢語(yǔ)句。使用mysqldumpslow命令可以非常明確的得到各種我們需要的查

24、詢語(yǔ)句，對(duì)MySQL查詢語(yǔ)句的監(jiān)控、分析、優(yōu)化是MySQL優(yōu)化非常重要的一步。開啟慢查詢?nèi)罩竞?，由于日志記錄操作，在一定程度上?huì)占用CPU資源影響mysql的性能，但是可以階段性開啟來(lái)定位性能瓶頸。explain分析查詢使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語(yǔ)句，從而知道MySQL是如何處理你的SQL語(yǔ)句的。這可以幫你分析你的查詢語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸。通過explain命令可以得到: 表的讀取順序 數(shù)據(jù)讀取操作的操作類型 哪些索引可以使用 哪些索引被實(shí)際使用 表之間的引用 每張表有多少行被優(yōu)化器查詢EXPLAIN字段：ØTable：顯示這一行

25、的數(shù)據(jù)是關(guān)于哪張表的Øpossible_keys：顯示可能應(yīng)用在這張表中的索引。如果為空，沒有可能的索引。可以為相關(guān)的域從WHERE語(yǔ)句中選擇一個(gè)合適的語(yǔ)句Økey：實(shí)際使用的索引。如果為NULL，則沒有使用索引。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)為必

26、須檢索的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù)Øtype：這是最重要的字段之一，顯示查詢使用了何種類型。從最好到最差的連接類型為system、const、eq_reg、ref、range、index和ALLnsystem、const：可以將查詢的變量轉(zhuǎn)為常量.  如id=1; id為 主鍵或唯一鍵.neq_ref：訪問索引,返回某單一行的數(shù)據(jù).(通常在聯(lián)接時(shí)出現(xiàn)，查詢使用的索引為主鍵或惟一鍵)nref：訪問索引,返回某個(gè)值的數(shù)據(jù).(可以返回多行) 通常使用=時(shí)發(fā)生nrange：這個(gè)連接類型使用索引返回一個(gè)范圍中的行，比如使用>或<查找東西，并且該字段上建有索引時(shí)發(fā)生的

27、情況(注:不一定好于index)nindex：以索引的順序進(jìn)行全表掃描，優(yōu)點(diǎn)是不用排序,缺點(diǎn)是還要全表掃描nALL：全表掃描，應(yīng)該盡量避免ØExtra：關(guān)于MYSQL如何解析查詢的額外信息，主要有以下幾種nusing index：只用到索引,可以避免訪問表. nusing where：使用到where來(lái)過慮數(shù)據(jù). 不是所有的where clause都要顯示using where. 如以=方式訪問索引.nusing tmporary：用到臨時(shí)表nusing filesort：用到額外的排序. (當(dāng)使用order by v1,而沒用到索引時(shí),就會(huì)使用額外的排序)nrange c

28、hecked for eache record(index map:N)：沒有好的索引. profiling分析查詢通過慢日志查詢可以知道哪些SQL語(yǔ)句執(zhí)行效率低下，通過explain我們可以得知SQL語(yǔ)句的具體執(zhí)行情況，索引使用等，還可以結(jié)合show命令查看執(zhí)行狀態(tài)。如果覺得explain的信息不夠詳細(xì)，可以同通過profiling命令得到更準(zhǔn)確的SQL執(zhí)行消耗系統(tǒng)資源的信息。profiling默認(rèn)是關(guān)閉的?？梢酝ㄟ^以下語(yǔ)句查看  打開功能： mysql>set profiling=1; 執(zhí)行需要測(cè)試的sql 語(yǔ)句：mysql> show profi

29、lesG; 可以得到被執(zhí)行的SQL語(yǔ)句的時(shí)間和IDmysql>show profile for query 1; 得到對(duì)應(yīng)SQL語(yǔ)句執(zhí)行的詳細(xì)信息Show Profile命令格式：SHOW PROFILE type , type                                 

30、        FOR QUERY n                                         

31、                       LIMIT row_count OFFSET offset                        

32、0;    type:                                             

33、;                                         ALL          

34、                                                  

35、                     | BLOCK IO                             

36、                                           | CONTEXT SWITCHES      

37、60;                                              | CPU     &

38、#160;                                                 &

39、#160;                        | IPC                           

40、;                                                   

41、60;   | MEMORY                                              

42、                                | PAGE FAULTS                  &#

43、160;                                              | SOURCE    

44、60;                                                  &#

45、160;                  | SWAPS                    以上的16rows是針對(duì)非常簡(jiǎn)單的select語(yǔ)句的資源信息，對(duì)于較復(fù)雜的SQL語(yǔ)句，會(huì)有更多的行和字段，比如converting HEAP

46、to MyISAM 、Copying to tmp table等等，由于以上的SQL語(yǔ)句不存在復(fù)雜的表操作，所以未顯示這些字段。通過profiling資源耗費(fèi)信息，我們可以采取針對(duì)性的優(yōu)化措施。 測(cè)試完畢以后 ，關(guān)閉參數(shù)：mysql> set profiling=0  2     索引及查詢優(yōu)化 索引的類型Ø 普通索引：這是最基本的索引類型，沒唯一性之類的限制。Ø 唯一性索引：和普通索引基本相同，但所有的索引列值保持唯一性。Ø 主鍵：主鍵是

47、一種唯一索引，但必須指定為”PRIMARY KEY”。Ø 全文索引：MYSQL從3.23.23開始支持全文索引和全文檢索。在MYSQL中，全文索引的索引類型為FULLTEXT。全文索引可以在VARCHAR或者TEXT類型的列上創(chuàng)建。大多數(shù)MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B樹中存儲(chǔ)?？臻g列類型的索引使用R-樹，MEMORY表支持hash索引。單列索引和多列索引（復(fù)合索引）索引可以是單列索引，也可以是多列索引。對(duì)相關(guān)的列使用索引是提高SELECT操作性能的最佳途徑之一。多列索引：MySQL可以為多個(gè)列創(chuàng)建索引。一個(gè)索引可以

48、包括15個(gè)列。對(duì)于某些列類型，可以索引列的左前綴，列的順序非常重要。多列索引可以視為包含通過連接索引列的值而創(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，agefirstname，lastnamefirstname也就是說(shuō)，相當(dāng)于還建立了key(firstname lastname)和key(

49、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á)式替換為常量，查詢立即返回。例如：SELECT MIN(key2), MAX

50、 (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è)查詢進(jìn)行優(yōu)化以便不用查詢數(shù)據(jù)行即可以檢索值。如果查詢只使用來(lái)自某個(gè)表的數(shù)字型并且構(gòu)成某些關(guān)鍵字的最左面前綴的列，為了更快，可以從索引樹檢索出值。SELECT key_part3 FROM tb

51、 WHERE key_part1=1有時(shí)MySQL不使用索引，即使有可用的索引。一種情形是當(dāng)優(yōu)化器估計(jì)到使用索引將需要MySQL訪問表中的大部分行時(shí)。(在這種情況下，表掃描可能會(huì)更快些）。然而，如果此類查詢使用LIMIT只搜索部分行，MySQL則使用索引，因?yàn)樗梢愿斓卣业綆仔胁⒃诮Y(jié)果中返回。例如： 合理的建立索引的建議：(1)  越小的數(shù)據(jù)類型通常更好：越小的數(shù)據(jù)類型通常在磁盤、內(nèi)存和CPU緩存中都需要更少的空間，處理起來(lái)更快。 (2)  簡(jiǎn)單的數(shù)據(jù)類型更好：整型數(shù)據(jù)比起字符，處理開銷更小，因?yàn)樽址谋容^更復(fù)雜。在MySQL中，

52、應(yīng)該用內(nèi)置的日期和時(shí)間數(shù)據(jù)類型，而不是用字符串來(lái)存儲(chǔ)時(shí)間；以及用整型數(shù)據(jù)類型存儲(chǔ)IP地址。(3)  盡量避免NULL：應(yīng)該指定列為NOT NULL，除非你想存儲(chǔ)NULL。在MySQL中，含有空值的列很難進(jìn)行查詢優(yōu)化，因?yàn)樗鼈兪沟盟饕?、索引的統(tǒng)計(jì)信息以及比較運(yùn)算更加復(fù)雜。你應(yīng)該用0、一個(gè)特殊的值或者一個(gè)空串代替空值 這部分是關(guān)于索引和寫SQL語(yǔ)句時(shí)應(yīng)當(dāng)注意的一些瑣碎建議和注意點(diǎn)。1. 當(dāng)結(jié)果集只有一行數(shù)據(jù)時(shí)使用LIMIT 12. 避免SELECT *，始終指定你需要的列從表中讀取越多的數(shù)據(jù)，查詢會(huì)變得更慢。他增加了磁盤需要操作的時(shí)間，還是在數(shù)據(jù)庫(kù)服務(wù)器與WEB服務(wù)器是獨(dú)立分開

53、的情況下。你將會(huì)經(jīng)歷非常漫長(zhǎng)的網(wǎng)絡(luò)延遲，僅僅是因?yàn)閿?shù)據(jù)不必要的在服務(wù)器之間傳輸。3. 使用連接（JOIN）來(lái)代替子查詢(Sub-Queries)       連接（JOIN）. 之所以更有效率一些，是因?yàn)镸ySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表來(lái)完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢工作。4. 使用ENUM、CHAR 而不是VARCHAR，使用合理的字段屬性長(zhǎng)度5. 盡可能的使用NOT NULL6. 固定長(zhǎng)度的表會(huì)更快7. 拆分大的DELETE 或INSERT 語(yǔ)句8. 查詢的列越小越快  Where條件在查詢中，WHERE

54、條件也是一個(gè)比較重要的因素，盡量少并且是合理的where條件是很重要的，盡量在多個(gè)條件的時(shí)候，把會(huì)提取盡量少數(shù)據(jù)量的條件放在前面，減少后一個(gè)where條件的查詢時(shí)間。有些where條件會(huì)導(dǎo)致索引無(wú)效：Ø where子句的查詢條件里有！=，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 xx

55、x%，而like %xxx% 時(shí)索引無(wú)效 三、    配置優(yōu)化安裝MySQL后，配置文件f在 /MySQL安裝目錄/share/mysql目錄中，該目錄中還包含多個(gè)配置文件可供參考，有my-f ，my-f，  my-f，my-f，分別對(duì)應(yīng)大中小型數(shù)據(jù)庫(kù)應(yīng)用的配置。win環(huán)境下即存在于MySQL安裝目錄中的.ini文件。 下面列出了對(duì)性能優(yōu)化影響較大的主要變量，主要分為連接請(qǐng)求的變量和緩沖區(qū)變量。1.   連接請(qǐng)求的變量：1)     max_connectionsM

56、ySQL的最大連接數(shù)，增加該值增加mysqld 要求的文件描述符的數(shù)量。如果服務(wù)器的并發(fā)連接請(qǐng)求量比較大，建議調(diào)高此值，以增加并行連接數(shù)量，當(dāng)然這建立在機(jī)器能支撐的情況下，因?yàn)槿绻B接數(shù)越多，介于MySQL會(huì)為每個(gè)連接提供連接緩沖區(qū)，就會(huì)開銷越多的內(nèi)存，所以要適當(dāng)調(diào)整該值，不能盲目提高設(shè)值。數(shù)值過小會(huì)經(jīng)常出現(xiàn)ERROR 1040: Too many connections錯(cuò)誤，可以過conn%通配符查看當(dāng)前狀態(tài)的連接數(shù)量，以定奪該值的大小。show variables like max_connections 最大連接數(shù)show  status like max_used_conne

57、ctions響應(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 * 1

58、00% （理想值 85%） 如果max_used_connections跟max_connections相同 那么就是max_connections設(shè)置過低或者超過服務(wù)器負(fù)載上限了，低于10%則設(shè)置過大。2)     back_logMySQL能暫存的連接數(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ù)量超過back_log，將不被授予連接資源。back_l

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

60、;3)     interactive_timeout一個(gè)交互連接在被服務(wù)器在關(guān)閉前等待行動(dòng)的秒數(shù)。一個(gè)交互的客戶被定義為對(duì)mysql_real_connect()使用CLIENT_INTERACTIVE 選項(xiàng)的客戶。 默認(rèn)數(shù)值是28800，可調(diào)優(yōu)為7200。 2.   緩沖區(qū)變量全局緩沖：4)     key_buffer_sizekey_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態(tài)值Key_read_requests和

61、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í)磁盤表是MyISAM表，也要使用該值?？梢允褂脵z查狀態(tài)值created_tmp_disk_tables得知詳情。舉例如下：mysql> show variables like key_buffer_size;+-+| Variabl

62、e_name | Value      |+| key_buffer_size | |+ -+key_buffer_size為512MB，我們?cè)倏匆幌耴ey_buffer_size的使用情況：mysql> show global status like key_read%;+-+| Variable_name  | Value    |+-+| Key_read_requests| | Key_reads|  6798830      |+-+一共有

63、個(gè)索引讀取請(qǐng)求，有6798830個(gè)請(qǐng)求在內(nèi)存中沒有找到直接從硬盤讀取索引，計(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)值為(256MB)。5)     query_cache_size使用查詢緩沖，MySQL將查詢結(jié)果存放在緩沖區(qū)中，今后對(duì)于同樣的SELECT語(yǔ)句（區(qū)分大小寫），將直接從緩沖區(qū)中讀取結(jié)果。通過檢查狀態(tài)值Qcache_*，可以知道query_cache_si

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

65、he_min_res_unit。 query_cache_type指定是否使用查詢緩沖，可以設(shè)置為0、1、2，該變量是SESSION級(jí)的變量。query_cache_limit指定單個(gè)查詢能夠使用的緩沖區(qū)大小，缺省為1M。query_cache_min_res_unit是在4.1版本以后引入的，它指定分配緩沖區(qū)空間的最小單位，缺省為4K。檢查狀態(tài)值Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多，這就表明查詢結(jié)果都比較小，此時(shí)需要減小query_cache_min_res_unit。舉例如下：mysql> show global status lik

66、e qcache%;+-+| Variable_name                  | Value       |+-+| Qcache_free_blocks       | 22756      | Qcache_free_memory 

67、   | 76764704    | Qcache_hits      | | Qcache_inserts     |    | Qcache_lowmem_prunes   | 4010916      | Qcache_not_cached| 13385031    | Qcache_queries_in_cache | 43560

68、| Qcache_total_blocks          | 111212     |+-+mysql> show variables like query_cache%;+| Variable_name       | Value     |+| query_cache_limit    | 2097152   &#

69、160; | query_cache_min_res_unit     | 4096  | query_cache_size    | | query_cache_type   | ON          | query_cache_wlock_invalidate | OFF  |+查詢緩存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%如果查詢緩存

70、碎片率超過20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數(shù)據(jù)量的話。查詢緩存利用率= (query_cache_size Qcache_free_memory) / query_cache_size * 100%查詢緩存利用率在25%以下的話說(shuō)明query_cache_size設(shè)置的過大，可適當(dāng)減小；查詢緩存利用率在80以上而且Qcache_lowmem_prunes > 50的話說(shuō)明query_cache_size可能有點(diǎn)小，要不就是碎片太多。查詢緩存命中率= (Qcache_hits Qc

71、ache_inserts) / Qcache_hits * 100%示例服務(wù)器查詢緩存碎片率20.46，查詢緩存利用率62.26，查詢緩存命中率1.94，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。每個(gè)連接的緩沖6)    record_buffer_size每個(gè)進(jìn)行一個(gè)順序掃描的線程為其掃描的每張表分配這個(gè)大小的一個(gè)緩沖區(qū)。如果你做很多順序掃描，你可能想要增加該值。默認(rèn)數(shù)值是131072(128K)，可改為16773120 (16M)7)     read_rnd_buffer_size隨機(jī)讀緩沖區(qū)大小。

72、當(dāng)按任意順序讀取行時(shí)(例如，按照排序順序)，將分配一個(gè)隨機(jī)讀緩存區(qū)。進(jìn)行排序查詢時(shí)，MySQL會(huì)首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值。但MySQL會(huì)為每個(gè)客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開銷過大。一般可設(shè)置為16M 8)     sort_buffer_size每個(gè)需要進(jìn)行排序的線程分配該大小的一個(gè)緩沖區(qū)。增加這值加速ORDER BY或GROUP BY操作。默認(rèn)數(shù)值是2097144(2M)，可改為16777208 (16M)。9)   &#

73、160; join_buffer_size聯(lián)合查詢操作所能使用的緩沖區(qū)大小record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size為每個(gè)線程獨(dú)占，也就是說(shuō)，如果有100個(gè)線程連接，則占用為16M*10010)  table_cache表高速緩存的大小。每當(dāng)MySQL訪問一個(gè)表時(shí)，如果在表緩沖區(qū)中還有空間，該表就被打開并放入其中，這樣可以更快地訪問表內(nèi)容。通過檢查峰值時(shí)間的狀態(tài)值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發(fā)現(xiàn)open_tables等于table_cache