mysql slowlog中querytime分析_技术分享 | Slow Query Log 使用详解
作者：宓祥康
愛可生交付服務(wù)部團(tuán)隊(duì) DBA 擅長(zhǎng)日志分析、問題排查等；主要負(fù)責(zé)處理 MySQL 與我司自研數(shù)據(jù)庫(kù)自動(dòng)化管理平臺(tái) DMP 的日常運(yùn)維問題，對(duì)數(shù)據(jù)庫(kù)及周邊技術(shù)有濃厚的學(xué)習(xí)興趣。
本文來源：原創(chuàng)投稿
愛可生開源社區(qū)出品，原創(chuàng)內(nèi)容未經(jīng)授權(quán)不得隨意使用，轉(zhuǎn)載請(qǐng)聯(lián)系小編并注明來源。
引言
什么是 Slow Query Log？
該如何使用它？
它的存在對(duì)運(yùn)維數(shù)據(jù)庫(kù)有什么幫助呢？
一、 簡(jiǎn)介
慢查詢?nèi)罩?#xff0c;開啟它我們可以通過參數(shù)來控制其記錄執(zhí)行或查詢時(shí)間長(zhǎng)的 SQL、全表掃描的 SQL、沒有使用索引的 SQL。沒錯(cuò)，它的作用就是記錄哪些糟糕的、讓數(shù)據(jù)庫(kù)變慢的 SQL，把它們揪出來。
我們一般通過如下參數(shù)來控制 slow 的開啟與記錄：slow_query_log、slow_query_log_file、long_query_time、min_examined_row_limit、log_output、log_queries_not_using_indexes、log_throttle_queries_not_using_indexes。
slow_query_log：控制 slow_query_log 是否開啟，參數(shù) ON|OFF
slow_query_log_file：控制文件的寫入位置，參數(shù)為文件的具體位置，如：/data/slow.log
long_query_time：設(shè)置 SQL 執(zhí)行時(shí)間大于等于多少秒(可精確到微秒)時(shí)記錄到日志中
min_examined_row_limit：設(shè)置檢查的行數(shù)大于等于多少行時(shí)記錄到日志中
log_output：設(shè)置慢查詢記錄到哪里，參數(shù) FILE|TABLE
log_queries_not_using_indexes：控制查詢過程中未使用索引或全表掃描的 SQL 是否記錄到日志中
log_throttle_queries_not_using_indexes：開啟 log_queries_not_using_indexes 后，此參數(shù)會(huì)限制每分鐘可以寫入慢速查詢?nèi)罩镜拇祟惒樵兊臄?shù)量，參數(shù)設(shè)置 0 為不限制
二、查看方式與內(nèi)容分析
慢日志分析的方式有兩種，因?yàn)槁罩疚募话爿^小，所以一種方式為在慢日志文件中直接使用 less 或 more 命令來查看。第二種方式則是利用 MySQL 官方提供給我們的程序：mysqldumpslow 來快速查看 slowlog 日志中記錄的慢 SQL。
對(duì)于我們?cè)敿?xì)來分析 SQL 的話，一般采用第一種方式，查找到對(duì)應(yīng)時(shí)間點(diǎn)的對(duì)應(yīng) SQL 來進(jìn)行分析。
show master status;
# Time: 2020-11-16T08:27:16.777259+08:00
# User@Host: root[root] @ [127.0.0.1] Id: 248
# Query_time: 15.293745 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1605486436;
那么如何讀懂慢日志里面對(duì)這些信息呢？
show master status #慢 SQL
Time #出現(xiàn)該慢 SQL 的時(shí)間
query_time # SQL 語句的查詢時(shí)間(在 MySQL 中所有類型的 SQL 語句執(zhí)行的時(shí)間都叫做 query_time,而在 Oracle 中則僅指 select)
lock_time: #鎖的時(shí)間
rows_sent: #返回了多少行,如果做了聚合就不準(zhǔn)確了
rows_examined: #執(zhí)行這條 SQL 處理了多少行數(shù)據(jù)
SET timestamp #時(shí)間戳
通過這些我們就可以來明確的知道一條 SQL 究竟執(zhí)行了多長(zhǎng)時(shí)間的查詢，有沒有發(fā)生鎖等待，此查詢實(shí)際在數(shù)據(jù)庫(kù)中讀取了多少行數(shù)據(jù)了。
三、如何在線安全清空 slow.log 文件
在開啟 log_queries_not_using_indexes 后，slow log 文件不僅僅會(huì)記錄慢查詢?nèi)罩?#xff0c;還會(huì)把查詢過程中未使用索引或全表掃描的 SQL 記錄到日志中，久而久之日志的空間便會(huì)變得越來越大，那么如何在線且安全的清空這些 slow log 日志，為磁盤釋放空間呢？
MySQL 對(duì)于慢日志的輸出方式支持兩種，TABLE 和 FILE，查看方法如下：
mysql> show variables like '%log_output%';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| log_output | FILE,TABLE |
+---------------+------------+
確認(rèn)清楚輸出方式后，可以分別對(duì)不同的輸出方式選擇不同的清空方法，本次將對(duì)兩種清空方法共同介紹。
3.1 FILE 類型清空方法
查詢 slow query log 開啟狀態(tài)
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /opt/mysql/data/3306/mysql-slow.log |
+---------------------+-------------------------------------+
關(guān)閉 slow query log
mysql> set global slow_query_log=0;
確認(rèn)關(guān)閉成功
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /opt/mysql/data/3306/mysql-slow.log |
+---------------------+-------------------------------------+
對(duì)日志進(jìn)行重命名或移除
mv /opt/mysql/data/3306/mysql-slow.log /opt/mysql/data/3306/mysql-old-slow.log
重新開啟 slow query log
mysql> set global slow_query_log=1;
執(zhí)行 SQL 進(jìn)行驗(yàn)證
mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
驗(yàn)證新生成文件記錄成功
cat /opt/mysql/data/3306/mysql-slow.log
[root@DMP1 3306]# cat mysql-slow.log
/opt/mysql/base/5.7.31/bin/mysqld, Version: 5.7.31-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /opt/mysql/data/3306/mysqld.sock
Time Id Command Argument
# Time: 2021-01-05T13:26:44.001647+08:00
# User@Host: root[root] @ localhost [] Id: 81786
# Query_time: 5.000397 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1609824404;
select sleep(5);
清理舊的 slowlog 文件
mv /opt/mysql/data/3306/mysql-old-slow.log /mysqlback
3.2 TABLE 類型清空方法
先關(guān)閉 slow query log
mysql> set global slow_query_log=0;
確認(rèn)關(guān)閉成功
mysql> show variables like 'slow_query_log';
+---------------------+-------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------+
| slow_query_log | OFF |
+---------------------+-------------------------------------+
TABLE 類型的 slowlog 存放在 mysql.slow_log 表中,對(duì) slow_log 進(jìn)行重命名為 old_slow_log
mysql> use mysql
mysql> ALTER TABLE slow_log RENAME old_slow_log;
創(chuàng)建全新的 slow_log 文件拷貝原來 slow_log 文件的表結(jié)構(gòu)
mysql> CREATE TABLE slow_log LIKE old_slow_log;
啟動(dòng) slow query log
mysql> SET GLOBAL slow_query_log = 1;
測(cè)試驗(yàn)證
mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
mysql> select * from slow_log \G
*************************** 1. row ***************************
start_time: 2021-01-05 13:49:13.864002
user_host: root[root] @ localhost []
query_time: 00:00:05.000322
lock_time: 00:00:00.000000
rows_sent: 1
rows_examined: 0
db: mysql
last_insert_id: 0
insert_id: 0
server_id: 874143039
sql_text: select sleep(5)
thread_id: 339487
刪除舊的 slow_log 表
mysql> drop table old_slow_log;
總結(jié)
該文章主要講述了 slow log 的開啟方式、分析方法與清空操作，熟練使用分析 slow log 文件可以實(shí)時(shí)觀察數(shù)據(jù)庫(kù) SQL 的執(zhí)行情況，并為 SQL 優(yōu)化奠定基礎(chǔ)。
總結(jié)
以上是生活随笔為你收集整理的mysql slowlog中querytime分析_技术分享 | Slow Query Log 使用详解的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： sharepoint文档库文档版本信息操
- 下一篇： 兴业信用卡随借金怎么收费 兴业信用卡随借