MySQL慢查詢?nèi)罩緎lowlog的具體使用
0 慢查詢?nèi)罩径x
慢速查詢?nèi)罩居涗浀氖菆?zhí)行時間超過long_query_time秒和檢查的行數(shù)超過min_examined_row_limit的SQL語句，這些語句通常是需要進行優(yōu)化的。
官方參考文檔：https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html
1 慢查詢?nèi)罩镜呐渲脜?shù)
服務(wù)器使用以下順序的控制參數(shù)來決定是否將查詢語句寫入慢查詢?nèi)罩荆?/p>
- 查詢必須不是管理語句（如alter、optimize table等），或者必須啟用log_slow_admin_statements參數(shù)記錄管理類語句；
- 查詢必須至少花費了long_query_time秒，或者必須啟用log_queries_not_using_indexes，并且查詢的索引沒有行限制（如全表掃描、索引全掃描等）；
- 查詢必須至少檢索了min_examined_row_limit行；
- 不被參數(shù)log_throttle_queries_not_using_indexes設(shè)置閾值限制寫入慢sql日志。
下面介紹這些參數(shù)：
一、long_query_time
規(guī)定了查詢時間超過此參數(shù)值被定義為慢SQL，狀態(tài)變量Slow_queries記錄了慢查詢SQL的數(shù)量。long_query_time的單位為秒，可以設(shè)置成小數(shù)，精確到微妙。最小值為0，最大值為31536000，即365天，默認值為10。
查看當(dāng)前設(shè)置：
mysql> show variables like 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.01 sec)
查看慢sql數(shù)量：
mysql> show status like 'slow_queries'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 5 | +---------------+-------+ 1 row in set (0.01 sec)
將此參數(shù)設(shè)置為5：
mysql> set global long_query_time=5; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 5.000000 | +-----------------+----------+ 1 row in set (0.00 sec)
二、slow_query_log
此參數(shù)決定是否激活慢sql日志，默認值是off，即關(guān)閉。
啟用慢查詢?nèi)罩荆?/p>
mysql> set global slow_query_log=on; Query OK, 0 rows affected (0.00 sec)
三、slow_query_log_file
此參數(shù)指定慢sql日志的文件路徑和文件名，默認位置在數(shù)據(jù)目錄datadir中，默認文件名是hostname-slow.log。
mysql> show variables like 'slow_query_log_file'; +---------------------+-------------------------------+ | Variable_name | Value | +---------------------+-------------------------------+ | slow_query_log_file | /disk1/data/mysql001-slow.log | +---------------------+-------------------------------+ 1 row in set (0.00 sec)
查看慢sql日志文件：
[mysql@mysql001 log]$ tailf /disk1/data/mysql001-slow.log
/usr/sbin/mysqld, Version: 8.0.34 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2023-12-20T22:44:21.890879+08:00
# User@Host: root[root] @ localhost [] Id: 9
# Query_time: 0.009038 Lock_time: 0.000008 Rows_sent: 0 Rows_examined: 0 Thread_id: 9 Errno: 0 Killed: 0 Bytes_received: 286 Bytes_sent: 92 Read_first: 0 Read_last: 0 Read_key: 12 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 1 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 1 Created_tmp_disk_tables: 0 Created_tmp_tables: 1 Start: 2023-12-20T22:44:21.881841+08:00 End: 2023-12-20T22:44:21.890879+08:00
SET timestamp=1703083461;
select f.title, count(*) as cnt
from sakila.rental r
join sakila.inventory i
on r.inventory_id = i.inventory_id
join sakila.film f
on i.film_id = f.film_id
where r.rental_date between '2005-03-01' and '2005-03-31'
group by f.film_id
order by cnt desc
limit 10;
四、log_queries_not_using_indexes
啟用該變量，會記錄期望檢索所有行的查詢語句，也就是說做表全掃描。使用索引的查詢也會被記錄。例如，使用完整索引掃描的查詢使用索引，但會記錄日志，因為索引不會限制行數(shù)。默認值是false。
五、min_examined_row_limit
參數(shù)規(guī)定了只有當(dāng)檢索的行數(shù)超過了參數(shù)值的sql語句才會被記錄到慢sql日志文件中，默認值是0，沒有限制?？梢院蜕弦粋€參數(shù)log_queries_not_using_indexes搭配使用，可以避免記錄一些訪問小表的查詢。
六、log_throttle_queries_not_using_indexes
該參數(shù)限制每分鐘記錄到慢查詢?nèi)罩局械牟樵冋Z句數(shù)量，默認值是0，不限制。
七、log_slow_extra
參數(shù)log_slow_extra從MySQL 8.0.14開始可用，當(dāng)啟用時，將記錄與慢sql相關(guān)的額外信息，如狀態(tài)參數(shù)Handler_%。參數(shù)默認值為off，建議打開，將參數(shù)設(shè)置為on。
mysql> set global log_slow_extra=on; Query OK, 0 rows affected (0.00 sec)
2 使用mysqldumpslow解釋慢查詢?nèi)罩?/h2>
MySQL慢速查詢?nèi)罩景瑘?zhí)行時間較長的查詢信息，且包含的記錄較多時，看起來比較困難?？梢允褂胢ysqldumpslow解析MySQL慢速查詢?nèi)罩疚募⒖偨Y(jié)日志內(nèi)容。
一、摘要分析
mysqldumpslow會對查詢進行摘要分析，8.0版本新添的兩個分析摘要函數(shù)如下：
- statement_digest_text()：返回摘要文本；
- statement_digest()：返回摘要hashvalue。
用法如下：
mysql> select statement_digest_text("select user(),host from mysql.user where user = 'lu9up'");
+----------------------------------------------------------------------------------+
| statement_digest_text("select user(),host from mysql.user where user = 'lu9up'") |
+----------------------------------------------------------------------------------+
| SELECT SYSTEM_USER ( ) , HOST FROM `mysql` . `user` WHERE SYSTEM_USER = ? |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select statement_digest("select user(),host from mysql.user where user = 'lu9up'");
+-----------------------------------------------------------------------------+
| statement_digest("select user(),host from mysql.user where user = 'lu9up'") |
+-----------------------------------------------------------------------------+
| 12984e6ff7cbdbd28e2a377375af873fcd606891f82c670a74c04db83f7ac09c |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
二、mysqldumpslow操作
調(diào)用語法：
mysqldumpslow [options] [log_file ...]
options：
-s指定排序方式，默認是at，根據(jù)平均時間排序，共有七種排序方式：
mysqldumpslow操作示例：
使用mysqldumpslow對慢查詢?nèi)罩疚募M行分析，輸出平均執(zhí)行時間最久的兩條查詢：
[mysql@mysql001 ~]$ mysqldumpslow -s at -t 2 /disk1/data/mysql001-slow.log Reading mysql slow query log from /disk1/data/mysql001-slow.log Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost select f.title, count(*) as cnt from sakila.rental r join sakila.inventory i on r.inventory_id = i.inventory_id join sakila.film f on i.film_id = f.film_id where r.rental_date between 'S' and 'S' group by f.film_id order by cnt desc limit N Count: 8 Time=0.00s (0s) Lock=0.00s (0s) Rows=4.9 (39), root[root]@localhost show variables like 'S'
3 使用pt-query-digest解析慢查詢?nèi)罩?/h2>
pt-query-digest是Percona Toolkit的一個工具，用于分析MySQL的慢查詢?nèi)罩疚募?、通用查詢?nèi)罩疚募投M制日志文件中的查詢，也可以分析SHOW PROCESSLIST命令輸出的結(jié)果和tcpdump抓取的MySQL協(xié)議數(shù)據(jù)（如：網(wǎng)絡(luò)流量包）。默認情況下，對所有分析的查詢按摘要分組，分析結(jié)果按查詢時間降序輸出。
官方參考文檔：https://docs.percona.com/percona-toolkit/pt-query-digest.html
3.1 安裝pt-query-digest
一、下載Percona Toolkit：
[mysql@mysql001 ~]$ wget percona.com/get/pt-query-digest
二、賦權(quán)
[mysql@mysql001 ~]$ chmod +775 pt-query-digest
完成賦權(quán)后就可以正常使用了。
3.2 語法和選項
語法：
pt-query-digest [OPTIONS] [FILES] [DSN]
選項：
| optition name | comment |
|---|---|
| –ask-pass | 連接MySQL時提示輸入密碼。 |
| –continue-on-error | 即使出現(xiàn)錯誤，也要繼續(xù)解析，默認值時yes。該工具不會永遠繼續(xù):一旦任何進程導(dǎo)致100個錯誤，它就會停止。 |
| –create-review-table | 使用–review選項將分析結(jié)果輸出到表中時，如果表不存在，創(chuàng)建它，默認值是yes。 |
| –create-history-table | 使用–history選項將分析結(jié)果輸出到表中時，如果表不存在，創(chuàng)建它，默認值是yes。 |
| –defaults-file | 指定mysql的參數(shù)文件名，必須給出一個絕對路徑名。 |
| –explain | 使用此DSN對示例查詢運行EXPLAIN并打印結(jié)果。 |
| –filter | 該選項是一個Perl代碼字符串或包含Perl代碼的文件，使用此參數(shù)對要分析的文件進行過濾后再分析，將不符合Perl代碼的時間全部忽略。 |
| –review | 保存分析結(jié)果到表中，有重復(fù)的查詢在表中時，不會再記錄。只保存分析過的sql語句，不包含分析結(jié)果。 |
| –history | 保存分析結(jié)果到表中，有重復(fù)的查詢在表中時，也會記錄，但時間不一樣。與review不同，不僅保存分析的sql語句，也包含分析結(jié)果。 |
| –limit | 將輸出限制為給定的百分比或SQL語句數(shù)量。 |
| –max-line-length | 把輸出行的長度修剪到這個長度，0表示不裁剪。 |
| –order-by | 按此屬性和聚合函數(shù)對事件進行排序，默認為Query_time:sum。 |
| –output | 指定分析結(jié)果的輸出格式。 |
| –since | 指定分析從什么時間開始的sql語句。 |
| –until | 指定分析的sql語句的截至?xí)r間。 |
| –type | 指定日志文件的類型，可以是genlog、binlog、slowlog、tcpdump、rawlog等。 |
選項的具體使用細則參考官方文檔：https://docs.percona.com/percona-toolkit/pt-query-digest.html#options
3.3 用法示例
1）直接分析慢查詢文件
[mysql@mysql001 output]$ pt-query-digest /disk1/data/mysql001-slow.log > slow`date +"%Y%m%d"`.log [mysql@mysql001 output]$ ll total 20 -rw-rw-r-- 1 mysql mysql 17819 Dec 20 22:51 slow20231220.log
2）分析網(wǎng)絡(luò)流量包
從3306端口抓取1000個流量包輸出到文件mysql.tcp.txt：
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
分析抓取的網(wǎng)路流量包：
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log
3）分析pocesslist的輸出
pt-query-digest --processlist h = host1
4）保存分析過的sql語句到表中
pt-query-digest --review h=192.168.131.99 --no-report mysql001-slow.log
默認保存的表是percona_schema.query_review。
5）保存分析結(jié)果到表中
pt-query-digest --history h=192.168.131.99 --no-report mysql001-slow.log
默認保存的表是percona_schema.query_history。
到此這篇關(guān)于MySQL慢查詢?nèi)罩緎lowlog的具體使用的文章就介紹到這了,更多相關(guān)MySQL慢查詢?nèi)罩緎lowlog內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Mysql中大小寫敏感問題導(dǎo)致的MySql Error 1146 Tabel doen’t exist錯誤
這篇文章主要介紹了Mysql中大小寫敏感問題導(dǎo)致的MySql Error 1146 Tabel doen’t exist錯誤,需要的朋友可以參考下2014-10-10
解決MySQL讀寫分離導(dǎo)致insert后select不到數(shù)據(jù)的問題
這篇文章主要介紹了解決MySQL讀寫分離導(dǎo)致insert后select不到數(shù)據(jù)的問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
mysql命令行中執(zhí)行sql的幾種方式總結(jié)
下面小編就為大家?guī)硪黄猰ysql命令行中執(zhí)行sql的幾種方式總結(jié)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2016-11-11
解決MySQL主從數(shù)據(jù)庫沒有同步的兩種方法
這篇文章主要介紹了解決MySQL主從數(shù)據(jù)庫沒有同步的兩種方法,需要的朋友可以參考下面文章內(nèi)容2021-09-09
這篇文章主要介紹了mysql中TIMESTAMPDIFF案例詳解,本篇文章通過簡要的案例,講解了該項技術(shù)的了解與使用,以下就是詳細內(nèi)容,需要的朋友可以參考下2021-08-08
mysql中使用sql命令將時間戳解析成datetime類型存入
這篇文章主要介紹了mysql中使用sql命令將時間戳解析成datetime類型存入，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-11-11
Mysql實現(xiàn)企業(yè)級日志管理、備份與恢復(fù)的實戰(zhàn)教程
下面小編就為大家分享一篇Mysql實現(xiàn)企業(yè)級日志管理、備份與恢復(fù)的實戰(zhàn)教程，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2017-12-12
關(guān)于mysql調(diào)用新手們常犯的11個錯誤總結(jié)
對于很多新手們來說，使用PHP可以在短短幾個小時之內(nèi)輕松地寫出具有特定功能的代碼。但是，構(gòu)建一個穩(wěn)定可靠的數(shù)據(jù)庫卻需要花上一些時日和相關(guān)技能。下面這篇文章就來總結(jié)了關(guān)于mysql調(diào)用新手們常犯的十一個錯誤，需要的朋友可以參考學(xué)習(xí)。2017-03-03
這篇文章主要介紹了mysql累加計算實現(xiàn)方法,結(jié)合實例形勢分析了mysql累加計算原理、實現(xiàn)方法及操作注意事項,需要的朋友可以參考下2020-05-05
MySQL數(shù)據(jù)庫創(chuàng)建新用戶及授予權(quán)限的完整流程
這篇文章主要給大家介紹了MySQL數(shù)據(jù)庫創(chuàng)建新用戶及授予權(quán)限的完整流程,通過這些步驟,管理員可以有效管理數(shù)據(jù)庫用戶,確保數(shù)據(jù)庫的安全性和高效運行,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-11-11