mysql运维利器percona-toolkit工具的pt-query-digest语法以及用法，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
前提条件
mysql版本5.7.21
redhat 6.8
开启慢查询
启用慢查询
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.01 sec)启用未使用索引慢查询
mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)运行sql
创建测试表
mysql> use zxydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into t_slow select * from t_slow;
Query OK, 8388608 rows affected (35.04 sec)
Records: 8388608 Duplicates: 0 Warnings: 0执行sql查询
mysql> select count(*) from zxydb.t_slow;
+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set (6.48 sec)pt-query-digest语法
查询pt-query-digest选项
[root@three57 percona-toolkit-3.1.0]# pt-query-digest --help
pt-query-digest analyzes MySQL queries from slow, general, and binary log files.
It can also analyze queries from C<SHOW PROCESSLIST> and MySQL protocol data
from tcpdump. By default, queries are grouped by fingerprint and reported in
descending order of query time (i.e. the slowest queries first). If no C<FILES>
are given, the tool reads C<STDIN>. The optional C<DSN> is used for certain
options like L<"--since"> and L<"--until">. For more details, please use the
--help option, or try 'perldoc /usr/local/bin/pt-query-digest' for complete
documentation.
Usage: pt-query-digest [OPTIONS] [FILES] [DSN]
Options:
--ask-pass Prompt for a password when connecting to MySQL
--attribute-aliases=a List of attribute|alias,etc (default db|Schema)
--attribute-value-limit=i A sanity limit for attribute values (default 0)
--charset=s -A Default character set
--config=A Read this comma-separated list of config files;
if specified, this must be the first option on
the command line获取完整pt-query-digest全部语义信息
[root@three57 percona-toolkit-3.1.0]# man pt-query-digest>/pt-query.log使用pt-query-digest分析慢查询日志
[root@three57 percona-toolkit-3.1.0]# pt-query-digest /var/lib/mysql/three57-slow.log
用户时间分布及进程内程内存
# 100ms user time, 10ms system time, 21.88M rss, 173.04M vsz
当前系统时间
# Current date: Wed Nov 13 15:29:44 2019
主机名称
# Hostname: three57
慢查询日志
# Files: /var/lib/mysql/three57-slow.log
QPS及并发
# Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________
# Time range: all events occurred at 2019-11-13T07:28:59
属性：全部，最小，最大，平均，95%，平均利差，中差
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
SQL执行时间
# Exec time 7s 7s 7s 7s 7s 0 7s
锁定时间
# Lock time 127us 127us 127us 127us 127us 0 127us
SQL产生结果个数
# Rows sent 1 1 1 1 1 0 1
SQL扫描表记录
# Rows examine 16.00M 16.00M 16.00M 16.00M 16.00M 0 16.00M
查询大小
# Query size 33 33 33 33 33 0 33
符合慢查询SQL的概述，极重要，排版一个字，棒
# Profile
RANK为排名编号 query id为具体SQL response time为sql响应时间 calls为SQL执行次数
r/call为每次SQL执行的响应时间，具体的SQL语句
# Rank Query ID Response time Calls R/Call V/M
# ==== ================================== ============= ===== ====== =====
# 1 0xAC104A376C0A55B1F56FDA6E706E555F 6.7433 100.0% 1 6.7433 0.00 SELECT zxydb.t_slow
具体慢查询SQL语句
# Query 1: 0 QPS, 0x concurrency, ID 0xAC104A376C0A55B1F56FDA6E706E555F at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2019-11-13T07:28:59
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 100 1
SQL执行时间为7s
# Exec time 100 7s 7s 7s 7s 7s 0 7s
SQL锁定时间为127us,此值如极高，需要针对性分析
# Lock time 100 127us 127us 127us 127us 127us 0 127us
# Rows sent 100 1 1 1 1 1 0 1
全表扫描相关
# Rows examine 100 16.00M 16.00M 16.00M 16.00M 16.00M 0 16.00M
# Query size 100 33 33 33 33 33 0 33
# String:
产生SQL的主机及用户
# Hosts localhost
# Users root
SQL不同时间范围分布，极好极好
可见如上SQL执行时间在1S左右
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `zxydb` LIKE 't_slow'\G
# SHOW CREATE TABLE `zxydb`.`t_slow`\G
# EXPLAIN
select count(*) from zxydb.t_slow\G
[root@three57 percona-toolkit-3.1.0]#看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。
