Mysql開啟慢SQL并分析原因

第一步.開啟mysql慢查詢

方式一:修改配置文件

Windows：Windows 的配置文件為 my.ini，一般在 MySQL 的安裝目錄下或者 c:\Windows 下。

Linux：Linux 的配置文件為 my.cnf ，一般在 /etc 下

在 my.ini 增加幾行:

[mysqlld] 
long_query_time=2 
#5.0、5.1等版本配置如下選項(xiàng) 
log-slow-queries="mysql_slow_query.log" 
#5.5及以上版本配置如下選項(xiàng) 
slow-query-log=On 
slow_query_log_file="mysql_slow_query.log" 
log-query-not-using-indexes 

第一句使用來(lái)定義查過(guò)多少秒的查詢算是慢查詢，我這里定義的是2秒

第二句使用來(lái)定義慢查詢?nèi)罩镜穆窂剑ㄒ驗(yàn)槭莣indows，所以不牽涉權(quán)限問(wèn)題）

第三句就是記錄下沒(méi)有使用索引的query

第二步：查看關(guān)于慢查詢的狀態(tài)

方式二:通過(guò)MySQL數(shù)據(jù)庫(kù)開啟慢查詢

上文的配置需要重啟mysql server進(jìn)程mysqld才會(huì)生效。但是很多時(shí)候，尤其是產(chǎn)品運(yùn)營(yíng)環(huán)境，不希望每次修改都需要重新啟動(dòng)mysql服務(wù)器，也希望能在某些特定時(shí)間記 錄。MySQL5.1給我們提供了更為靈活的運(yùn)行時(shí)控制，使得你不必重新啟動(dòng)mysql服務(wù)器，也能選擇性地記錄或者不記錄某些slow queries。

MySQL5.1中，提供了全局變量slow_query_log、slow_query_log_file可以靈活地控制enable/disable慢查詢。同時(shí)可以通過(guò)long_query_time設(shè)置時(shí)間

#//啟用slow query記錄
#注意:設(shè)置了slow_query_log全局變量, log_slow_queries也會(huì)隱性地跟著改變
mysql>set global slow_query_log=ON

不幸運(yùn)的是,在MySQL5.0并沒(méi)有提供類似的全局變量來(lái)靈活控制，但是我們可以通過(guò)將long_query_time設(shè)置得足夠大來(lái)避免記錄某些查詢語(yǔ)句。比如

mysql>set global long_query_time = 3600;

mysql>set global log_querise_not_using_indexes = ON;

MySQL5.0, 不關(guān)服務(wù)的情況下，希望不記錄日志的辦法是將日志文件成為/dev/null的符號(hào)鏈接(symbolic link)。注意:你只需要在改變后運(yùn)行FLUSH LOGS以確定MYSQL釋放當(dāng)前的日志文件描述符，重新把日志記錄到/dev/null

和MySQL5.0不同,MySQL5.1可以在運(yùn)行時(shí)改變?nèi)沼浶袨?，將日志記錄到?shù)據(jù)庫(kù)表中。只要將mysql全局變量log_output設(shè)置為 TABLE即可。MySQL會(huì)將日志分別記錄到表mysql.gengera_log和mysql.slow_log二張表中。但是，我們推薦將日志記錄 在日記文件中。

mysql> show variables like ‘log_output'\G
Variable_name: log_output
Value: FILE
mysql>set global log_output='table';

缺陷與審記

雖然記錄了slow query能夠幫助你優(yōu)化產(chǎn)品。但是MySQL目前版本，還有幾大蹩足的地方。

通過(guò)microslow-patch補(bǔ)丁可使用更細(xì)的時(shí)間粒度，和記錄所有執(zhí)行過(guò)的sql語(yǔ)句。不過(guò)，使用這個(gè)補(bǔ)訂不得不自己編譯MySQL，出于穩(wěn)定性考濾，我們推薦在開發(fā)測(cè)試環(huán)境，可以打上這個(gè)補(bǔ)丁，享受這個(gè)補(bǔ)丁帶來(lái)的便利。在運(yùn)營(yíng)環(huán)境盡量不要這么做…

第二步.驗(yàn)證慢查詢是否開啟

執(zhí)行如下SQL語(yǔ)句來(lái)查看mysql慢查詢的狀態(tài)

執(zhí)行結(jié)果會(huì)把是否開啟慢查詢、慢查詢的秒數(shù)、慢查詢?nèi)罩镜刃畔⒋蛴≡谄聊簧稀?/p>

/*查看慢查詢時(shí)間 */ 
show variables like "long_query_time";默認(rèn)10s 
/*查看慢查詢配置情況 */ 
show status like "%slow_queries%"; 
/*查看慢查詢?nèi)罩韭窂?*/ 
 show variables like "%slow%"; 

第三步：執(zhí)行一次慢查詢操作

其實(shí)想要執(zhí)行一次有實(shí)際意義的慢查詢比較困難，因?yàn)樵谧约簻y(cè)試的時(shí)候，就算查詢有20萬(wàn)條數(shù)據(jù)的海量表，也只需要0.幾秒。我們可以通過(guò)如下語(yǔ)句代替：

SELECT SLEEP(10);

第四步：查看慢查詢的數(shù)量

通過(guò)如下sql語(yǔ)句，來(lái)查看一共執(zhí)行過(guò)幾次慢查詢：

show global status like '%slow%';

mysql日志的配置：

注意：這些日文件在mysql重啟的時(shí)候才會(huì)生成 
#記錄所有sql語(yǔ)句 
log=E:/mysqllog/mysql.log 
#記錄數(shù)據(jù)庫(kù)啟動(dòng)關(guān)閉信息，以及運(yùn)行過(guò)程中產(chǎn)生的錯(cuò)誤信息 
log-error=E:/mysqllog/myerror.log 
# 記錄除select語(yǔ)句之外的所有sql語(yǔ)句到日志中，可以用來(lái)恢復(fù)數(shù)據(jù)文件 
log-bin=E:/mysqllog/bin 
#記錄查詢慢的sql語(yǔ)句 
log-slow-queries=E:/mysqllog/slow.log 
#慢查詢時(shí)間 
long_query_time=2 

第四步:分析慢查詢?nèi)罩?/p>

方式一:通過(guò)工具分析

MySQL自帶了mysqldumpslow工具用來(lái)分析slow query日志，除此之外，還有一些好用的開源工具。比如MyProfi(下載地址:http://sourceforge.net/projects/myprofi/)、mysql-log-filter，當(dāng)然還有mysqlsla

以下是mysqldumpslow常用參數(shù)說(shuō)明，詳細(xì)的可應(yīng)用mysqldumpslow -help查詢。

接下來(lái)就是用mysql自帶的慢查詢工具mysqldumpslow分析了（mysql的bin目錄下），我這里的日志文件名字是host-slow.log。

列出記錄次數(shù)最多的10個(gè)sql語(yǔ)句

mysqldumpslow -s c -t 10 host-slow.log

列出返回記錄集最多的10個(gè)sql語(yǔ)句

mysqldumpslow -s r -t 10 host-slow.log

按照時(shí)間返回前10條里面含有左連接的sql語(yǔ)句

mysqldumpslow -s t -t 10 -g "left join" host-slow.log

使用mysqldumpslow命令可以非常明確的得到各種我們需要的查詢語(yǔ)句，對(duì)MySQL查詢語(yǔ)句的監(jiān)控、分析、優(yōu)化起到非常大的幫助。

方式二:直接分析mysql慢查詢?nèi)罩?/p>

日志部分內(nèi)容如下:

# Time: 121017 17:38:54 
# User@Host: root[root] @ localhost [127.0.0.1] 
# Query_time: 3.794217 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 4194304 
SET timestamp=1350466734; 
select * from wei where text='orange'; 
# Time: 121017 17:46:22 
# User@Host: root[root] @ localhost [127.0.0.1] 
# Query_time: 3.819219 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 4194304 
SET timestamp=1350467182; 
select * from wei where text='xishizhaohua'; 

其實(shí)定位到了慢查詢語(yǔ)句就已經(jīng)完成了一大不了，執(zhí)行explain或者desc命令查看慢查詢語(yǔ)句，如下圖：

問(wèn)題很明顯，解決方式也很明顯，建索引了。

mysql> create index text_index on wei(text); 
Query OK, 4194304 rows affected (1 min 58.07 sec) 
Records: 4194304 Duplicates: 0 Warnings: 0 

然后在執(zhí)行查詢操作，用時(shí)明顯少了很多。

mysql> select * from wei where text='orange'; 
+---------+--------+ 
| id | text | 
+---------+--------+ 
| 4103519 | orange | 
+---------+--------+ 
1 row in set (0.33 sec) 

Slow Query日志，雖然幫助你記錄了那些執(zhí)行過(guò)了的SQL語(yǔ)句。但它不是萬(wàn)能的，意義可能沒(méi)有你想象的那么大。它只告訴了你哪些語(yǔ)句慢，但是為什么慢?具體 原因，還是需要你自己去分析，不斷的調(diào)試。也許，你只需要換一條更有效的sql語(yǔ)句，也許你只需簡(jiǎn)單地增加一個(gè)索引，但也有可能你需要調(diào)整你應(yīng)用程序的設(shè) 計(jì)方案。比如，上面那條語(yǔ)句是很明顯，它檢查了600多萬(wàn)行數(shù)據(jù)。不幸的是，并不是每條語(yǔ)句都這么明顯。也許還有別的原因，比如:

所以,不要過(guò)于緊張日志文件某條記錄，而應(yīng)該理性地審記，找出真正的原因。如果經(jīng)常出現(xiàn)的slow query需要特別注意。如果個(gè)別出現(xiàn)，則做一些常規(guī)檢查即可。我們建議，統(tǒng)計(jì)并且形成基準(zhǔn)報(bào)告，進(jìn)行比較排除，比胡亂瞎撞有用。希望大家不要在這部分過(guò)于浪費(fèi)時(shí)間與精力。

總結(jié)

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com