Mysql優化_慢查詢開啟說明及Mysql慢查詢分析工具mysqldumpslow用法講解

OldBoy~發表於2018-01-20
MySql優化

Mysql慢查詢開啟

Mysql的查詢訊日誌是Mysql提供的一種日誌記錄，它用來記錄在Mysql中響應時間超過閾值的語句，具體指執行時間超過long_query_time值得SQL，則會被記錄到慢查詢日誌中。long_query_time的預設為10，意識是執行10秒以上的語句。由它來看那些SQL語句超出了我們的最大忍耐值，比如一條SQL執行超過了5秒，我們就算慢查詢，我們就可以結合Explain進行分析。
預設Mysql沒有開啟慢查詢，需要我們說動設定這個引數。當然，如果不是調優需要的話，一般不建議開啟該引數，因為開啟慢查詢日誌會或多或少帶來一定的效能影響。慢查詢日誌支援將日誌寫入檔案。
進入mysql終端檢視是否開啟（我這裡都是自己改過的）

MySQL [test_db]> show variables like '%slow_query_log%';
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| slow_query_log      | ON                         |
| slow_query_log_file | /data/mysql/mysql-slow.log |
+---------------------+----------------------------+

上面查詢結果第一行，這裡是開啟的，第二行是預設查詢路徑檔名。

MySQL [test_db]> set global slow_query_log = 0|1; //設定開啟或者關閉，0為關閉，1為開啟

如果使用set global slow_query_log 命令開啟慢查詢日誌，只對當前資料庫生效，如果Mysql重啟後則會失效。
補充：如果通過終端命令設定的話，再查詢是看不到修改結果的，需要新開啟一個視窗檢視即可。
如果要永久生效，必須修改配置檔案，修改完記得習慣性重啟服務。還是那句話，不建議生產環境開啟。

[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log
MySQL [test_db]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

假如SQL執行時間剛好等於long_query_time的情況，並不會被記錄下來，也就是說，在Mysql原始碼裡是判斷大於long_query_time，而非大於等於

開始測試（在開啟慢查詢的情況下）

MySQL [test_db]> select sleep(5);

檢視慢查詢日誌檔案

# cat /data/mysql/mysql-slow.log 
# 
# select sleep(5);
# Time: 2018-01-20T18:10:24.443517Z
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 5.000564 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use test_db; 
SET timestamp=1524276624;
select sleep(5);

以上資訊包括實際查詢時間5.000564秒，時間戳timestamp=1524276624，還有庫test_db，出問題的SQL。

mysqldumpslow用法講解

上面測試的慢查詢SQL只有一條，假如在實際的生產環境中，慢查詢SQL遠遠高於測試的數量，十幾條甚至幾十條，假如幾條慢查詢出現的頻率很高，我們能做到根據輕重優先順序來分析並排除那是不是更好？那麼就用到了mysqldumpslow

# mysqldumpslow --help   ----------------------------------------------------//執行命令
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

s:是表示按照何種方式排序
c:訪問次數
i：鎖定時間
r：返回記錄
t：查詢時間
al:平均鎖定時間
ar：平均返回記錄數
at：平均查詢時間
t：即為返回前面多少條資料
g：後邊搭配一個正則匹配模式，大小寫不敏感

mysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log  //得到返回記錄集最多的10個SQL
mysqldumpslow -s c -t 10 /data/mysql/mysql-slow.log //得到訪問次數最多的10個SQL 
mysqldumpslow -s t -t 10 -g "left join" /data/mysql/mysql-slow.log  //得到按照時間排序的前10條裡面含有做了連線的查詢SQL
mysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log | more  //另外建議在使用這些命令時結合|和more使用，否則有可能出現爆屏情況

OK~既然學習了，就運用到實際工作中吧~

相關文章