MySQL慢查詢?nèi)罩九e例分析

這篇文章主要介紹“MySQL慢查詢?nèi)罩九e例分析”，在日常操作中，相信很多人在MySQL慢查詢?nèi)罩九e例分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL慢查詢?nèi)罩九e例分析”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

慢查詢?nèi)罩?

打開慢查詢?nèi)罩?

慢查詢?nèi)罩荆櫭剂x就是記錄執(zhí)行比較慢查詢的日志。

查看是否開啟慢查詢?nèi)罩荆?

show variables like '%slow%';

打開慢查詢?nèi)罩?。修改MySQL的配置文件my.cn一般是在/etc目錄下面，加上下面三行配置后重啟MySQL。

slow_query_log = ON
slow_launch_time = 2 slow_query_log_file = /usr/local/mysql/data/slow_query.log

slow_launch_time只能精確到秒，如果需要更精確可以使用一些第三方的工具比如后面介紹的pt-query-digest。

注意：我這里用的MySQL版本是5.6，不同版本的MySQL開啟慢查詢的配置是不同的，比如5.6之前的某些版本是ong_query_time, long_query_time和log-slow-queries?？梢韵仍诮K端執(zhí)行show variables like '%slow%';查看下當(dāng)前版本具體配置是什么。

也可以在終端通過設(shè)置全局變量來打開慢查詢?nèi)罩荆?

set  @@global.slow_query_log = ON;

保存慢查詢?nèi)罩镜奖碇?

MySQL支持將慢查詢?nèi)罩颈４娴絤ysql.slow_log這張表中。通過@@global.log_output可以設(shè)置默認(rèn)為TABLE，F(xiàn)ILE和TABLE只能同時使用一個。

set @@global.log_output='TABLE';

我們可以使用下面的語句來模擬慢查詢：

select sleep(10);

慢查詢?nèi)罩痉治?

1、可以使用MySQL自帶的mysqldumpslow工具。使用很簡單，可以跟-help來查看具體的用法。

# -s：排序方式。c , t , l , r 表示記錄次數(shù)、時間、查詢時間的多少、返回的記錄數(shù)排序；
#                             ac , at , al , ar 表示相應(yīng)的倒敘；
# -t：返回前面多少條的數(shù)據(jù)；
# -g：包含什么，大小寫不敏感的；
mysqldumpslow -s r -t 10 /slowquery.log     #slow記錄最多的10個語句
mysqldumpslow -s t -t 10 -g "left join" /slowquery.log     #按照時間排序前10中含有"left join"的

2、可以導(dǎo)到mysql.slow_query表中，然后通過sql語句進(jìn)行分析。

3、使用第三方工具，下面會有介紹。

Percona Toolkit介紹

percona-toolkit是一組高級命令行工具的集合，用來執(zhí)行各種通過手工執(zhí)行非常復(fù)雜和麻煩的mysql和系統(tǒng)任務(wù)。這些任務(wù)包括：

• 檢查master和slave數(shù)據(jù)的一致性

• 有效地對記錄進(jìn)行歸檔

• 查找重復(fù)的索引

• 對服務(wù)器信息進(jìn)行匯總

• 分析來自日志和tcpdump的查詢

• 當(dāng)系統(tǒng)出問題的時候收集重要的系統(tǒng)信息

安裝

安裝percona-toolkit非常簡單，到官網(wǎng)下載.tar.gz包：

wget percona.com/get/percona-toolkit.tar.gz tar -zxvf percona-toolkit-2.2.5.tar.gz

然后依次執(zhí)行下面的命令：

perl Makefile.PL make make test make install

默認(rèn)的會被安裝在/usr/local/bin目錄下。執(zhí)行man percona-toolkit可以查看安裝了哪些工具。

運(yùn)行工具可能會遇到下面的錯誤：

這是因?yàn)槿鄙傧鄳?yīng)包，.pm包實(shí)際上perl的包，運(yùn)行下面的命令安裝即可：

yum install -y perl-Time-HiRes

如果安裝過程中出現(xiàn)” Error Downloading Packages”錯誤，嘗試yum clean all后再安裝。使用其Percona Toolkit中其他工具也可能會遇到類似的問題，按照提示安裝相應(yīng)的perl包就可以了。

Percona Toolkit整個工具箱提供了非常多實(shí)用的工具，具體的使用方法可以參看官方文檔。

下面有選擇的給大家介紹幾個有用的工具。

pt-query-digest

pt-query-digest可以從普通MySQL日志，慢查詢?nèi)罩疽约岸M(jìn)制日志中分析查詢，甚至可以從SHOW PROCESSLIST和MySQL協(xié)議的tcpdump中進(jìn)行分析，如果沒有指定文件，它從標(biāo)準(zhǔn)輸入流（STDIN）中讀取數(shù)據(jù)。

最簡單的用法如下：

pt-query-digest slow.logs

輸出信息大致如下:

整個輸出分為三大部分：

1、整體概要（Overall）

這個部分是一個大致的概要信息(類似loadrunner給出的概要信息)，通過它可以對當(dāng)前MySQL的查詢性能做一個初步的評估，比如各個指標(biāo)的最大值(max)，平均值(min)，95%分布值，中位數(shù)(median)，標(biāo)準(zhǔn)偏差(stddev)。這些指標(biāo)有查詢的執(zhí)行時間（Exec time），鎖占用的時間（Lock time），MySQL執(zhí)行器需要檢查的行數(shù)（Rows examine），最后返回給客戶端的行數(shù)（Rows sent），查詢的大小。

2、查詢的匯總信息（Profile）

這個部分對所有”重要”的查詢(通常是比較慢的查詢)做了個一覽表:

每個查詢都有一個Query ID，這個ID通過Hash計算出來的。pt-query-digest是根據(jù)這個所謂的Fingerprint來group by的。舉例下面兩個查詢的Fingerprint是一樣的都是select * from table1 where column1 = ?，工具箱中也有一個與之相關(guān)的工具pt-fingerprint。

select * from table1 where column1 = 2 select * from table1 where column1 = 3

• Rank整個分析中該“語句”的排名，一般也就是性能最常的。

• Response time  “語句”的響應(yīng)時間以及整體占比情況。

• Calls 該“語句”的執(zhí)行次數(shù)。

• R/Call 每次執(zhí)行的平均響應(yīng)時間。

• V/M 響應(yīng)時間的差異平均對比率。

在尾部有一行輸出，顯示了其他2個占比較低而不值得單獨(dú)顯示的查詢的統(tǒng)計數(shù)據(jù)。

3、詳細(xì)信息

這個部分會列出Profile表中每個查詢的詳細(xì)信息：

包括Overall中有的信息、查詢響應(yīng)時間的分布情況以及該查詢”入榜”的理由。

pt-query-digest還有很多復(fù)雜的操作，這里就不一一介紹了。比如：從PROCESSLIST中查詢某個MySQL中最慢的查詢：

pt-query-digest –processlist h=host1

從tcpdump中分析：

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

從一臺機(jī)器上講slow log保存到另外一臺機(jī)器上待稍后詳細(xì)分析：

pt-query-digest --review h=host2 --no-report slow.log

還可以跟一些過濾條件。詳見官方文檔：http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

另外結(jié)合一些第三方工具還能生成相應(yīng)的報表，可以參考這里：http://biancheng.dnbcw.info/mysql/433514.html

建議：當(dāng)slow log很大的時候最好還是將日志文件移到其他機(jī)器上進(jìn)行分析。

pt-index-usage

這個工具主要是用來分析查詢的索引使用情況。

pt-index-usage slow_query.log --h localhost --password 123456

詳細(xì)的用法 –help查看再對照官網(wǎng)就差不再贅述。

注意使用這個工具需要MySQL必須要有密碼，另外運(yùn)行時可能報找不到/var/lib/mysql/mysql.sock的錯，簡單的從/tmp/mysql.sock鏈接一個就行了。

重點(diǎn)要說明的是pt-index-usage只能分析慢查詢?nèi)罩?，所以如果想全面分析所有查詢的索引使用情況就得將slow_launch_time設(shè)置為0，因此請謹(jǐn)慎使用該工具，線上使用的話最好在凌晨進(jìn)行分析，尤其分析大量日志的時候是很耗CPU的。

整體來說這個工具是不推薦使用的，要想實(shí)現(xiàn)類似的分析可以考慮一些其他第三方的工具，比如：mysqlidxchx, userstat和check-unused-keys。網(wǎng)上比較推薦的是userstat，一個Google貢獻(xiàn)的patch。

Oracle是可以將執(zhí)行計劃保存到性能視圖中的，這樣分析起來可能更靈活，但是目前我還沒找到MySQL中類似的做法。

pt-upgrade

這個工具用來檢查在新版本中運(yùn)行的SQL是否與老版本一樣，返回相同的結(jié)果，最好的應(yīng)用場景就是數(shù)據(jù)遷移的時候。

pt-upgrade h=host1 h=host2 slow.log

pt-query-advisor 

靜態(tài)查詢分析工具。能夠解析查詢?nèi)罩尽⒎治霾樵兡Ｊ?，然后給出所有可能存在潛在問題的查詢，并給出足夠詳細(xì)的建議。這個工具好像2.2的版本給去掉了，有可能是因?yàn)閷π阅苡绊懕容^大新版本直接去掉了。

總結(jié):上面這些工具最好不要直接在線上使用，應(yīng)該作為上線輔助或故障后離線分析的工具，也可以做性能測試的時候配合著使用。

SHOW PROFILE

SHOW PROFILE是Google高級架構(gòu)師Jeremy Cole貢獻(xiàn)給MySQL社區(qū)的，它可以用來MySQL執(zhí)行語句時候所使用的資源。默認(rèn)是關(guān)閉的，需要打開執(zhí)行下面的語句：

set profiling = 1;#這個命令只在本會話內(nèi)起作用。

執(zhí)行簡單的SHOW PROFILES可以看到打開profiling之后所有查詢的執(zhí)行時間。

執(zhí)行SHOW PROFILE [TYPE] FOR QUERY Query_ID可以看到MySQL執(zhí)行某個查詢各個步驟的各項(xiàng)性能指標(biāo)的詳細(xì)信息：

如果沒有指定FOR QUERY則顯示最近一條查詢的詳細(xì)信息。TYPE是可選的，有以下幾個選項(xiàng)：

• ALL 顯示所有性能信息

• BLOCK IO 顯示塊IO操作的次數(shù)

• CONTEXT SWITCHES 顯示上下文切換次數(shù)，不管是主動還是被動

• CPU 顯示用戶CPU時間、系統(tǒng)CPU時間

• IPC 顯示發(fā)送和接收的消息數(shù)量

• MEMORY [暫未實(shí)現(xiàn)]

• PAGE FAULTS 顯示頁錯誤數(shù)量

• SOURCE 顯示源碼中的函數(shù)名稱與位置

• SWAPS 顯示SWAP的次數(shù)

MySQL在執(zhí)行查詢語句的時候會有很多步驟，這里就不一一贅述了，用到的時候網(wǎng)上搜下就行。需要特別說明的是Sending data這個步驟，給人感覺是MySQL把數(shù)據(jù)發(fā)送給客戶端的耗時，其實(shí)不然，這個步驟包括了MySQL內(nèi)部各個存儲之間復(fù)制數(shù)據(jù)的過程，比如硬盤的尋道。

到此，關(guān)于“MySQL慢查詢?nèi)罩九e例分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！