mysql慢查詢功能詳解

有人的地方就有江湖，資料庫也是，sql優化這個問題，任重道遠，我們總是禁不住有爛sql。怎麼辦呢，還好各大資料庫都有相關爛sql的收集功能，而mysql的慢查詢收集也是異曲同工，配合分析sql的執行計劃，這個優化就有了搞頭了。

開啟mysql慢查詢日志

1.檢視目前慢查詢設定情況

<code>#檢視慢查詢時間，預設10s，建議降到1s或以下，</code>

<code>mysql> show variables like </code><code>"long_query_time"</code><code>;</code>

<code>| Variable_name | Value |</code>

<code>| long_query_time | 1.000000 |</code>

<code>#檢視慢查詢配置情況</code>

<code>mysql> show variables like </code><code>"%slow%"</code><code>;</code>

<code>| Variable_name | Value |</code>

<code>| log_slow_admin_statements | OFF |</code>

<code>| log_slow_filter | |</code>

<code>| log_slow_rate_limit | 1 |</code>

<code>| log_slow_rate_type | session |</code>

<code>| log_slow_slave_statements | OFF |</code>

<code>| log_slow_sp_statements | ON |</code>

<code>| log_slow_verbosity | |</code>

<code>| max_slowlog_files | 0 |</code>

<code>| max_slowlog_size | 0 |</code>

<code>| slow_launch_time | 2 |</code>

<code>| slow_query_log | ON |</code>

<code>| slow_query_log_always_write_time | 10.000000 |</code>

<code>| slow_query_log_file | </code><code>/tmp/slow_querys</code><code>.log |</code>

<code>| slow_query_log_use_global_control | |</code>

其中，slow_query_log的值是on就是已開啟功能了。

2.如何開啟慢查詢功能

方法一：在伺服器上找到mysql的配置檔案my.cnf , 然後再mysqld子產品裡追加一下内容，這樣的好處是會一直生效，不好就是需要重新開機mysql程序。

<code>[mysqld]</code>

<code>slow_query_log = ON</code>

<code>#定義慢查詢日志的路徑</code>

<code>slow_query_log_file = </code><code>/tmp/slow_querys</code><code>.log</code>

<code>#定義查過多少秒的查詢算是慢查詢，我這裡定義的是1秒，5.6之後允許設定少于1秒，例如0.1秒</code>

<code>long_query_time = 1</code>

<code>#用來設定是否記錄沒有使用索引的查詢到慢查詢記錄,預設關閉,看需求開啟,會産生很多日志,可動态修改</code>

<code>#log-queries-not-using-indexes</code>

<code>管理指令也會被記錄到慢查詢。比如OPTIMEZE TABLE, ALTER TABLE,預設關閉,看需求開啟,會産生很多日志,可動态修改</code>

<code>#log-slow-admin-statements</code>

然後重新開機mysql伺服器即可，這是通過一下指令看一下慢查詢日志的情況：

<code>tail</code> <code>-f </code><code>/tmp/slow_querys</code><code>.log</code>

方法二：通過修改mysql的全局變量來處理，這樣做的好處是，不用重新開機mysql伺服器，登陸到mysql上執行一下sql腳本即可，不過重新開機後就失效了。

<code>#開啟慢查詢功能，1是開啟，0是關閉</code>

<code>mysql> </code><code>set</code> <code>global slow_query_log=1;</code>

<code>mysql> </code><code>set</code> <code>global long_query_time=1;</code>

<code>mysql> </code><code>set</code> <code>global slow_query_log_file=</code><code>'/tmp/slow_querys.log'</code><code>;</code>

<code>#關閉功能：set global slow_query_log=0;</code>

<code>然後通過一下指令檢視是否成功</code>

<code>mysql> show variables like </code><code>'long%'</code><code>; </code>

<code>mysql> show variables like </code><code>'slow%'</code><code>;</code>

<code>#設定慢查詢記錄到表中</code>

<code>#set global log_output='TABLE';</code>

當然了，你也可以兩者合一，一方面不用重新開機mysql程序就能生效，另一方面也不用怕重新開機後參數失效，效果也是一緻的。

特别要注意的是long_query_time的設定，5.6之後支援設定低于0.1秒，是以記錄的詳細程度，就看你自己的需求，資料庫容量比較大的，超過0.1秒還是比較多，是以就變得有點不合理了。

慢查詢日志的記錄定義

直接檢視mysql的慢查詢日志分析，比如我們可以tail -f slow_query.log檢視裡面的内容

<code>tail</code> <code>-f slow_query.log</code>

<code># User@Host: root[root] @ localhost []</code>

<code># Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774</code>

<code>SET timestamp=1294388531;</code>

<code>select</code> <code>count(*) from ep_friends;</code>

字段意義解析：

第一行,SQL查詢執行的時間

第二行,執行SQL查詢的連接配接資訊，使用者和連接配接IP

第三行,記錄了一些我們比較有用的資訊，如下解析

Query_time,這條SQL執行的時間,越長則越慢

Lock_time,在MySQL伺服器階段(不是在存儲引擎階段)等待表鎖時間

Rows_sent,查詢傳回的行數

Rows_examined,查詢檢查的行數，越長就當然越費時間

第四行,設定時間戳，沒有實際意義，隻是和第一行對應執行時間。

第五行及後面所有行（第二個# Time:之前）,執行的sql語句記錄資訊，因為sql可能會很長。

分析慢查詢的軟體

雖然慢查詢日志已經夠清晰，但是往往我們的日志記錄到的不是隻有一條sql，可能有很多很多條，如果不加以統計，估計要看到猴年馬月，這個時候就需要做統計分析了。

方法一：使用mysql程式自帶的mysqldumpslow指令分析，例如：

mysqldumpslow -s c -t 10 /tmp/slow-log

這會輸出記錄次數最多的10條SQL語句，得出的結果和上面一般慢查詢記錄的格式沒什麼太大差别，這裡就不展開來詳細解析了。

參數解析：

-s：是表示按照何種方式排序，子參數如下：

c、t、l、r：分别是按照記錄次數、時間、查詢時間、傳回的記錄數來排序，

ac、at、al、ar：表示相應的倒叙；

-t：傳回前面多少條的資料，這裡意思就是傳回10條資料了（也可以說是前十）

-g：後邊可以寫一個正則比對模式，大小寫不敏感的，比如：

/path/mysqldumpslow -s r -t 10 /tmp/slow-log，得到傳回記錄集最多的10個查詢。

/path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log，得到按照時間排序的前10條裡面含有左連接配接的查詢語句。

方法二：使用pt(Percona Toolkit)工具的pt-query-digest進行統計分析。這個是由Percona公司出品的一個用perl編寫的腳本，隻有安裝上pt工具集才會存在，有興趣的朋友就要先安裝pt工具了。直接分析慢查詢檔案，執行如下：

pt-query-digest slow_querys.log >t.txt

因為記錄裡還是可能有很多sql在，看起來還是費勁，是以建議輸出到檔案來看了。輸出的資訊會分成三部分，

第一部分：總體統計結果

<code># 580ms user time, 0 system time, 35.46M rss, 100.01M vsz</code>

<code># Current date: Wed Jul 19 14:32:40 2017</code>

<code># Hostname: yztserver1</code>

<code># Files: slow_querys.log</code>

<code># Overall: 2.63k total, 36 unique, 0.03 QPS, 0.03x concurrency ___________</code>

<code># Time range: 2017-07-18T03:17:17 to 2017-07-19T06:30:18</code>

<code># Attribute total min max avg 95% stddev median</code>

<code># Rows examine 1.06G 0 2.12M 421.02k 619.64k 155.33k 419.40k</code>

<code># Rows affecte 0 0 0 0 0 0 0</code>

<code># Bytes sent 9.00M 11 6.24M 3.51k 13.78k 119.76k 65.89</code>

<code># Query size 735.85k 6 2.19k 286.72 463.90 128.05 246.02</code>

記錄這個慢日志檔案裡面的所有慢查詢統計資訊，通常粗略看看就好了:

Overall: 這個檔案裡總共有多少條查詢，上例為總共2.63k個查詢，也就是2.63k條慢查詢。

Time range: 查詢執行的時間範圍。

unique: 唯一查詢數量，即對查詢條件進行參數化以後，統計的總共有多少個不同的查詢，該例為36。也就是說這2.63K條慢查詢，實際歸類為36條。

Attribute：屬性解析，其他子項：

total: 總計，min:最小，max: 最大，avg:平均，

95%: 把所有值從小到大排列，位置位于95%的那個數，這個數一般最具有參考價值，

median: 中位數，把所有值從小到大排列，位置位于中間那個數。

其他就字面意思，去翻譯一下就好。

第二部分：查詢分組統計結果

<code># Profile</code>

<code># Rank Query ID Response time Calls R/Call V/M Item</code>

<code># 1 0x8965CC929FB1C7B2 2080.0546 66.1% 1816 1.1454 0.03 SELECT 1 </code>

<code># 2 0x9C57D04CEA1970B4 228.4754 7.3% 131 1.7441 0.10 SELECT 2 </code>

<code># 3 0x94B4D7AA44982464 138.5964 4.4% 112 1.2375 0.05 SELECT 3</code>

<code># 4 0x6BD09392D1D0B5D7 84.1681 2.7% 70 1.2024 0.03 SELECT 4 </code>

<code># 5 0x1E9926677DBA3657 81.2260 2.6% 68 1.1945 0.03 SELECT 5 </code>

<code># 6 0xBBCE31227D8C6A93 69.6594 2.2% 56 1.2439 0.05 SELECT 6</code>

<code># 7 0x9A691CB1A14640F4 60.4517 1.9% 51 1.1853 0.04 SELECT 7 </code>

<code># 8 0xDA99A20C8BE81B9C 55.7751 1.8% 46 1.2125 0.05 SELECT 8 </code>

<code># 9 0x1F53AC684A365326 55.6378 1.8% 45 1.2364 0.03 SELECT 9_ </code>

<code># 10 0x664E0C18531443A5 38.6894 1.2% 31 1.2480 0.05 SELECT way_bill_main </code>

<code># 11 0xF591153EC390D8CA 32.5370 1.0% 28 1.1620 0.01 SELECT way_bill_main </code>

<code># 12 0xA3A82D5207F1BC2E 24.6582 0.8% 20 1.2329 0.06 SELECT way_bill_main </code>

<code># 13 0xFCED276145371CE4 22.2543 0.7% 18 1.2363 0.05 SELECT way_bill_main </code>

<code># 14 0x4895DF4252D5A600 21.3184 0.7% 17 1.2540 0.07 SELECT way_bill_main </code>

<code># 16 0xA4DD833DF8C96D04 14.6107 0.5% 13 1.1239 0.01 SELECT way_bill_main </code>

<code># 17 0x0426A3C3538CBBA8 13.9799 0.4% 13 1.0754 0.00 SELECT way_bill_main </code>

<code># 18 0x2C52F334EF3D8D2D 12.5960 0.4% 10 1.2596 0.03 SELECT way_bill_main </code>

<code># MISC 0xMISC 110.2030 3.5% 83 1.3277 0.0 <19 ITEMS></code>

這部分對查詢進行參數化并分組，然後對各類查詢的執行情況進行分析，結果按總執行時長，從大到小排序，恕我改了些顯示。

Response: 總的響應時間。

time: 該查詢在本次分析中總的時間占比。

calls: 執行次數，即本次分析總共有多少條這種類型的查詢語句。

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

Item : 查詢對象

第三部分：每一種查詢的詳細統計結果，這是其中一個

<code># Query 1: 0.02 QPS, 0.02x concurrency, ID 0x8965CC929FB1C7B2 at byte 868609</code>

<code># This item is included in the report because it matches --limit.</code>

<code># Scores: V/M = 0.03</code>

<code># Time range: 2017-07-18T03:17:56 to 2017-07-19T06:30:18</code>

<code># Attribute pct total min max avg 95% stddev median</code>

<code># Count 69 1816</code>

<code># Rows examine 71 771.37M 262.54k 440.03k 434.96k 419.40k 24.34k 419.40k</code>

<code># Rows affecte 0 0 0 0 0 0 0 0</code>

<code># Bytes sent 1 120.49k 65 68 67.94 65.89 0.35 65.89</code>

<code># Query size 60 443.31k 248 250 249.97 246.02 0.00 246.02</code>

<code># String:</code>

<code># Databases ytttt</code>

<code># Hosts 10.25.28.2</code>

<code># Last errno 0</code>

<code># Users gztttttt</code>

<code># Query_time distribution</code>

<code># Tables</code>

<code># SHOW TABLE STATUS FROM `ytttt` LIKE 'way_bill_main'\G</code>

<code># SHOW CREATE TABLE `ytttt`.`way_bill_main`\G</code>

<code># SHOW TABLE STATUS FROM `ytttt` LIKE 'scheduler_task'\G</code>

<code># SHOW CREATE TABLE `ytttt`.`scheduler_task`\G</code>

<code># EXPLAIN /*!50100 PARTITIONS*/</code>

<code>SELECT COUNT(1) FROM 1 as w inner </code><code>join</code> <code>.....此處省略。。。</code>

這部分的上面一部分和第一部分資訊類似，統計該記錄sql的總運作效率資訊，下面一部分的解析如下：

Databases: 庫名

Users: 各個使用者執行的次數（占比），現在隻有一個使用者，因為我授權的就是一個庫一個獨立使用者。

Query_time distribution : 查詢時間分布, 長短展現區間占比，本例中基本上都是1s。

Tables: 查詢中涉及到的表

Explain: 示例，也就是這條sql本身的資訊。

後面其他資訊也大體和這個類似，隻是顯示不同的sql資訊而已，都屬于這個第三部分。

------------------------------------------------------------------------------------------

pt-query-digest參數說明：

--create-review-table 當使用--review參數把分析結果輸出到表中時，如果沒有表就自動建立。

--create-history-table 當使用--history參數把分析結果輸出到表中時，如果沒有表就自動建立。

--filter 對輸入的慢查詢按指定的字元串進行比對過濾後再進行分析

--limit限制輸出結果百分比或數量，預設值是20,即将最慢的20條語句輸出，如果是50%則按總響應時間占比從大到小排序，輸出到總和達到50%位置截止。

--host MySQL伺服器位址

--user mysql使用者名

--password mysql使用者密碼

--history 将分析結果儲存到表中，分析結果比較詳細，下次再使用--history時，如果存在相同的語句，且查詢所在的時間區間和曆史表中的不同，則會記錄到資料表中，可以通過查詢同一CHECKSUM來比較某類型查詢的曆史變化。

--review 将分析結果儲存到表中，這個分析隻是對查詢條件進行參數化，一個類型的查詢一條記錄，比較簡單。當下次使用--review時，如果存在相同的語句分析，就不會記錄到資料表中。

--output 分析結果輸出類型，值可以是report(标準分析報告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于閱讀。

--since 從什麼時間開始分析，值為字元串，可以是指定的某個”yyyy-mm-dd [hh:mm:ss]”格式的時間點，也可以是簡單的一個時間值：s(秒)、h(小時)、m(分鐘)、d(天)，如12h就表示從12小時前開始統計。

--until 截止時間，配合—since可以分析一段時間内的慢查詢。

其他指令示例：

1.分析最近12小時内的查詢：

pt-query-digest --since=12h slow.log > slow_report2.log

2.分析指定時間範圍内的查詢：

pt-query-digest slow.log --since '2014-04-17 09:30:00' --until '2014-04-17 10:00:00'>>slow_report3.log

3.分析指含有select語句的慢查詢

pt-query-digest--filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log

4.針對某個使用者的慢查詢

pt-query-digest--filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log

5.查詢所有所有的全表掃描或full join的慢查詢

pt-query-digest--filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log

6.把查詢儲存到query_review表

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review--create-review-table slow.log

7.把查詢儲存到query_history表

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_ history--create-review-table slow.log_20140401

pt-query-digest --user=root –password=abc123--review h=localhost,D=test,t=query_history--create-review-table slow.log_20140402

8.通過tcpdump抓取mysql的tcp協定資料，然後再分析

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_report9.log

9.分析binlog

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql

pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log

10.分析general log

pt-query-digest --type=genlog localhost.log > slow_report11.log

--------------------------------------------------------------------------------------------

其實pt-query-digest雖然資訊很多，但是輸出的有用資訊不見得就比mysqldumpslow好很多，反而眼花缭亂的，而且還要裝多個工具才能用。不過可以甩問題給開發看到效率有多差也算是一個好事，可以說清楚着個sql執行了多少次慢查詢，是以實際使用上還是見仁見智，自己看着辦。

題外話

一般慢查詢日志解決不了問題的話，就建議開查詢日志general-log來跟蹤sql了

大體和上面操作差不多，先檢視目前狀态

show variables like 'general%';

可以在my.cnf裡添加

general-log = 1開啟（0關閉）

log = /log/mysql_query.log路徑

也可以設定變量那樣更改

set global general_log=1開啟（0關閉）

本文轉自arthur376 51CTO部落格，原文連結：http://blog.51cto.com/arthur376/1948944，如需轉載請自行聯系原作者

mysql慢查詢功能詳解

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述