共計 4715 個字符，預計需要花費 12 分鐘才能閱讀完成。

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

慢查詢日志
打開慢查詢日志

慢查詢日志，顧名思義就是記錄執行比較慢查詢的日志。

查看是否開啟慢查詢日志：

show variables like  %slow%

打開慢查詢日志。修改 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。可以先在終端執行 show variables like %slow% 查看下當前版本具體配置是什么。

也可以在終端通過設置全局變量來打開慢查詢日志：

set @@global.slow_query_log = ON;

保存慢查詢日志到表中

MySQL 支持將慢查詢日志保存到 mysql.slow_log 這張表中。通過 @@global.log_output 可以設置默認為 TABLE，FILE 和 TABLE 只能同時使用一個。

set @@global.log_output= TABLE

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

select sleep(10);

慢查詢日志分析

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

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

2、可以導到 mysql.slow_query 表中，然后通過 sql 語句進行分析。

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

Percona Toolkit 介紹

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

檢查 master 和 slave 數據的一致性

有效地對記錄進行歸檔

查找重復的索引

對服務器信息進行匯總

分析來自日志和 tcpdump 的查詢

當系統出問題的時候收集重要的系統信息

安裝

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

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

然后依次執行下面的命令：

perl Makefile.PL make make test make install

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

運行工具可能會遇到下面的錯誤：

這是因為缺少相應包，.pm 包實際上 perl 的包，運行下面的命令安裝即可：

yum install -y perl-Time-HiRes

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

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

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

pt-query-digest

pt-query-digest 可以從普通 MySQL 日志，慢查詢日志以及二進制日志中分析查詢，甚至可以從 SHOW PROCESSLIST 和 MySQL 協議的 tcpdump 中進行分析，如果沒有指定文件，它從標準輸入流（STDIN）中讀取數據。

最簡單的用法如下：

pt-query-digest slow.logs

輸出信息大致如下:

整個輸出分為三大部分：

1、整體概要（Overall）

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

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

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

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

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

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

Response time “語句”的響應時間以及整體占比情況。

Calls 該“語句”的執行次數。

R/Call 每次執行的平均響應時間。

V/M 響應時間的差異平均對比率。

在尾部有一行輸出，顯示了其他 2 個占比較低而不值得單獨顯示的查詢的統計數據。

3、詳細信息

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

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

pt-query-digest 還有很多復雜的操作，這里就不一一介紹了。比如：從 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

從一臺機器上講 slow log 保存到另外一臺機器上待稍后詳細分析：

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

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

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

建議：當 slow log 很大的時候最好還是將日志文件移到其他機器上進行分析。

pt-index-usage

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

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

詳細的用法 –help 查看再對照官網就差不再贅述。

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

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

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

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

pt-upgrade

這個工具用來檢查在新版本中運行的 SQL 是否與老版本一樣，返回相同的結果，最好的應用場景就是數據遷移的時候。

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

pt-query-advisor 

靜態查詢分析工具。能夠解析查詢日志、分析查詢模式，然后給出所有可能存在潛在問題的查詢，并給出足夠詳細的建議。這個工具好像 2.2 的版本給去掉了，有可能是因為對性能影響比較大新版本直接去掉了。

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

SHOW PROFILE

SHOW PROFILE 是 Google 高級架構師 Jeremy Cole 貢獻給 MySQL 社區的，它可以用來 MySQL 執行語句時候所使用的資源。默認是關閉的，需要打開執行下面的語句：

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

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

執行 SHOW PROFILE [TYPE] FOR QUERY Query_ID 可以看到 MySQL 執行某個查詢各個步驟的各項性能指標的詳細信息：

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

ALL 顯示所有性能信息

BLOCK IO 顯示塊 IO 操作的次數

CONTEXT SWITCHES 顯示上下文切換次數，不管是主動還是被動

CPU 顯示用戶 CPU 時間、系統 CPU 時間

IPC 顯示發送和接收的消息數量

MEMORY [暫未實現]

PAGE FAULTS 顯示頁錯誤數量

SOURCE 顯示源碼中的函數名稱與位置

SWAPS 顯示 SWAP 的次數

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

到此，關于“MySQL 慢查詢日志舉例分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！