mysql之慢查询详解，mysqldumpslow的使用
簡言
1. mysql中的慢查詢是我們分析問題，定位問題的利器，巧妙使用可以快速解決問題
2. 原理：運行時間超過 long_query_time 的sql語句會被寫入到慢查詢日志文件中或者mysql.slow_log表中，long_query_time的默認值是10s
3. 還可以使用慢日志分析工具 mysqldumpslow 來進行分析慢查詢
相關參數
slow_query_log ? ?：是否開啟慢查詢日志，1表示開啟，0表示關閉。
log-slow-queries ?：舊版（5.6以下版本）MySQL數據庫慢查詢日志存儲路徑。可以不設置該參數，系統則會默認給一個缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL數據庫慢查詢日志存儲路徑。可以不設置該參數，系統則會默認給一個缺省的文件host_name-slow.log
long_query_time ：慢查詢閾值，當查詢時間多于設定的該值時，則寫入日志。注意該值修改后再次查詢顯示的還是舊值，要使用 show global variables like 'long_query_time'; 這樣的語句查詢才更新
log_queries_not_using_indexes：未使用索引的查詢也被記錄到慢查詢日志中（可選項）。
log_output：日志存儲方式。log_output='FILE'表示將日志存入文件，默認值是'FILE'。log_output='TABLE'表示將日志存入數據庫，這樣日志信息就會被寫入到mysql.slow_log表中。MySQL數據庫支持同時兩種日志存儲方式，配置的時候以逗號隔開即可，如：log_output='FILE,TABLE'。日志記錄到系統的專用日志表中，要比記錄到文件耗費更多的系統資源，因此對于需要啟用慢查詢日志，又需要能夠獲得更高的系統性能，那么建議優先記錄到文件
演示步驟
1. 查詢慢查詢是否開啟，慢查詢的閾值，慢查詢日志文件的路徑
mysql> show global variables like '%query%'; +------------------------------+----------------------------------+ | Variable_name | Value | +------------------------------+----------------------------------+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit | 20 | | have_query_cache | NO | | long_query_time | 10.000000 | | query_alloc_block_size | 8192 | | query_prealloc_size | 8192 | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/kickseed-slow.log | +------------------------------+----------------------------------+ 8 rows in set (0.00 sec)?
long_query_time： 10.000000 表慢查詢的閾值是10s,可以精確到0.000001秒，也就是1微秒。關于運行時間正好等于long_query_time的情況，并不會被記錄下來。也就是說，在mysql源碼里是判斷大于long_query_time，而非大于等于
slow_query_log：OFF 表慢查詢未開啟
slow_query_log_file：slow_query_log_file 表慢查詢日志文件的位置
2. 打開慢查詢開關，修改慢查詢的閾值
mysql> set global slow_query_log=1; Query OK, 0 rows affected (0.00 sec)mysql> set global long_query_time=1.0; Query OK, 0 rows affected (0.00 sec)mysql> show global variables like '%query%'; +------------------------------+----------------------------------+ | Variable_name | Value | +------------------------------+----------------------------------+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit | 20 | | have_query_cache | NO | | long_query_time | 1.000000 | | query_alloc_block_size | 8192 | | query_prealloc_size | 8192 | | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/kickseed-slow.log | +------------------------------+----------------------------------+ 8 rows in set (0.00 sec)long_query_time： 1.000000 表慢查詢的閾值是1.0秒,運行時間超過1秒的sql語句會被寫入到慢日志文件
slow_query_log：ON?表慢查詢已開啟
謹記：
1) : 修改long_query_time后再查詢不能使用 “show variables like '%query%';”來進行查詢，這樣查詢還是舊的，必須要在前面添加global才能查到最新的 long_query_time 值。當然也可以斷開這個連接，重新連接進來再查詢不使用global也行
2) : 而且修改long_query_time后，執行sql語句的連接必須斷開后再次連接mysql，設置的閾值才能起效
3) : 設置slow_query_log=1開啟了慢查詢后只對當前數據庫生效，如果MySQL重啟后則會失效。如果要永久生效，就必須修改配置文件my.cnf（其它系統變量也是如此）
3. 測試慢sql語句，查看慢日志
mysql> select sleep(2); +----------+ | sleep(2) | +----------+ | 0 | +----------+ 1 row in set (2.01 sec)select sleep(2);表執行暫停2秒的sql語句
然后我們打開慢日志文件，也就是slow_query_log_file的值，即/var/lib/mysql/kickseed-slow.log文件，跳轉到最后可以看到如下內容
# Time: 2021-02-23T11:38:25.222093Z # User@Host: root[root] @ localhost [127.0.0.1] Id: 19 # Query_time: 2.000681 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1 SET timestamp=1614080303; select sleep(2);Time:Time: 2021-02-23T11:38:25.222093Z 表該條慢sql執行的時間點
User@Host: root[root] @ localhost [127.0.0.1] ?Id: 19 表該條慢sql的連接賬號為root，IP地址為127.0.0.1，Id表連接ID，每次連接該ID加1
Query_time: 2.000681? 表執行該sql的總時間為2.000681秒
Lock_time: 0.000000? 表鎖表時間
Rows_sent: 1 表發送sql結果的行數，只有1行
Rows_examined: 1 表執行該sql語句檢查數據庫的行數
4. 系統變量?log_output，?log-queries-not-using-indexes，?log_slow_admin_statements的作用
log_output='FILE'表示將日志存入文件，默認值是'FILE'。log_output='TABLE'表示將日志存入數據庫，這樣日志信息就會被寫入到mysql.slow_log表中。MySQL數據庫支持同時兩種日志存儲方式，配置的時候以逗號隔開即可，如：log_output='FILE,TABLE'。日志記錄到系統的專用日志表中，要比記錄到文件耗費更多的系統資源，因此對于需要啟用慢查詢日志，又需要能夠獲得更高的系統性能，那么建議優先記錄到文件
mysql> show variables like '%log_output%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+ 1 row in set (0.01 sec)log-queries-not-using-indexes：未使用索引的查詢也被記錄到慢查詢日志中（可選項）。如果調優的話，建議開啟這個選項。另外，開啟了這個參數，其實使用full index scan的sql也會被記錄到慢查詢日志
mysql> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF | +-------------------------------+-------+ 1 row in set (0.01 sec)log_slow_admin_statements表示是否將慢管理語句例如ANALYZE TABLE和ALTER TABLE等記入慢查詢日志
mysql> show variables like 'log_slow_admin_statements'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | log_slow_admin_statements | OFF | +---------------------------+-------+ 1 row in set (0.01 sec)5.?日志分析工具mysqldumpslow，mysql官方自帶的，只要安裝了mysql就可以使用它，可以用來幫助我們分析慢日志文件
在生產環境中，如果要手工分析日志文件，查找、分析SQL，顯然是個體力活，MySQL提供了日志分析工具mysqldumpslow，如下
root@kickseed:/home# 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 defaultal: average lock timear: average rows sentat: average query timec: countl: lock timer: rows sentt: 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: 訪問次數
l: 鎖定時間
r: 返回記錄的行數
t: 查詢時間
al:平均鎖定時間
ar:平均返回記錄的行數
at:平均查詢時間
-t? 是top n的意思，即為返回前面多少條的數據
-g? 后邊可以寫一個正則匹配模式，大小寫不敏感的
例子
得到返回記錄行數最多的10條SQL語句（行數最多排序需要 -s r ；限制10條需要 -t 10）
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
得到訪問次數最多的10個SQL（訪問次數最多排序需要 -s c?；限制10條需要 -t 10）
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
得到按照執行時間排序的前10條，且sql語句里面含有左連接的查詢語句（執行時間排序需要 -s t；限制10條需要 -t 10）
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
另外建議在使用這些命令時結合 | 和more 使用 ，否則有可能出現刷屏的情況。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
筆者以自己的慢日志為例，做一次查詢
mysqldumpslow -s t -t 5 kickseed-slow.log 表我要分析kickseed-slow.log文件，該文件是mysql的慢日志文件，得到按照執行時間，從大到小排序，最多5條sql語句
root@kickseed:/var/lib/mysql# mysqldumpslow -s t -t 5 kickseed-slow.log Reading mysql slow query log from kickseed-slow.log Count: 2 Time=2.00s (4s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhostselect sleep(N)Count: 3 Time=0.21s (0s) Lock=0.00s (0s) Rows=25.0 (75), root[root]@[192.168.54.28]SHOW TABLE STATUS FROM `hyj_player`Count: 1 Time=0.21s (0s) Lock=0.00s (0s) Rows=23211.0 (23211), root[root]@[192.168.54.28]SELECT * FROM `hyj_player`.`t_player_basic_data` LIMIT N, NCount: 1 Time=0.20s (0s) Lock=0.00s (0s) Rows=24211.0 (24211), root[root]@[192.168.54.28]SELECT * FROM t_player_basic_dataCount: 4 Time=0.02s (0s) Lock=0.00s (0s) Rows=1.0 (4), root[root]@[192.168.54.28]SELECT COUNT(*) FROM t_player_basic_data WHERE playerID < N從返回的結果中可以看到，從最大耗時的2秒，到最小耗時的0.02秒，一共5條記錄
總結
以上是生活随笔為你收集整理的mysql之慢查询详解，mysqldumpslow的使用的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： go语言的range使用
- 下一篇： golang递归获取目录下的所有文件