MySQL开启慢查询日志并使用pt(2)

/usr/sbin/mysqld, Version: 5.7.25-0Ubuntu0.18.04.2 ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.25-0ubuntu0.18.04.2-log ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.25-0ubuntu0.18.04.2-log ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                Id Command    Argument
;
# Time: 2019-02-27T14:07:42.841770Z
# User@Host: skip-grants user[lgj] @ localhost []  Id:    2
# Query_time: 0.016232  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1551276462;
;
# Time: 2019-02-27T14:07:51.774192Z
# User@Host: skip-grants user[lgj] @ localhost []  Id:    2
# Query_time: 0.000485  Lock_time: 0.000221 Rows_sent: 1  Rows_examined: 1
SET timestamp=1551276471;
select * from user;
# Time: 2019-02-27T14:07:55.839223Z
# User@Host: skip-grants user[lgj] @ localhost []  Id:    2
# Query_time: 0.000564  Lock_time: 0.000259 Rows_sent: 1  Rows_examined: 1
SET timestamp=1551276475;
# 查询语句
select * from user;
# 记录时间
# Time: 2019-02-27T14:08:07.404666Z
# User@Host: skip-grants user[lgj] @ localhost []  Id:    2
#查询时间  锁表时间 
# Query_time: 0.006318  Lock_time: 0.000435 Rows_sent: 13  Rows_examined: 1026
SET timestamp=1551276487;

从上面可以获知查询时间和锁表时间，但是如果文件比较大，查找时间最长的查询将会非常麻烦，需要使用相关的工具来进行分析。

2. qt-query-digest的使用

2.1 安装
•  创建目录： mkdir db-analysis && cd db-analysis
• 下载 pt-query-digest: curl -LO https://percona.com/get/pt-query-digest
•  设置执行权限：chmod +x pt-query-digest
•  将慢查询日志的文件复制到当前的目录下
•  执行分析：  ./pt-query-digest lgj-Lenovo-G470-slow.log

结果

# 220ms user time, 10ms system time, 33.67M rss, 90.27M vsz
# Current date: Wed Feb 27 22:55:05 2019
# Hostname: lgj-Lenovo-G470
# Files: lgj-Lenovo-G470-slow.log
# Overall: 9 total, 7 unique, 0.24 QPS, 0.00x concurrency ________________
# Time range: 2019-02-27T14:07:29 to 2019-02-27T14:08:07
# Attribute          total    min    max    avg    95%  stddev  median
# ============    ======= ======= ======= ======= ======= ======= =======
# Exec time          185ms  225us  122ms    21ms  122ms    37ms    6ms
# Lock time            2ms      0  485us  186us  467us  166us  159us
# Rows sent            50      0      13    5.56  12.54    5.25    0.99
# Rows examine      2.03k      0  1.00k  230.44 1012.63  419.72    0.99
# Query size          198      11      32      22  31.70    7.35  17.65