当前位置: 代码迷 >> 综合 >> mysql系列(六)mysql 慢日志查询(pt-query-digest)/如何单条SQL分析和Explain及trace工具
  详细解决方案

mysql系列(六)mysql 慢日志查询(pt-query-digest)/如何单条SQL分析和Explain及trace工具

热度:6   发布时间:2023-12-06 10:30:01.0
序号 名称 链接地址
1  mysql系列(一) centos7 安装msql https://blog.csdn.net/qq_38130094/article/details/103529535
2 mysql系列(二)mysql简介之逻辑架构/锁粒度/事务/死锁/事务日志/MVCC https://blog.csdn.net/qq_38130094/article/details/103549194
3 mysql系列(三) mysql存储引擎简介 https://blog.csdn.net/qq_38130094/article/details/103599497
4 mysql系列(四) mysql数据库设计优化 https://blog.csdn.net/qq_38130094/article/details/103551778
5  mysql系列(五) mysql索引详细解析及使用 https://blog.csdn.net/qq_38130094/article/details/103553971
6 mysql系列(六)mysql 慢日志查询(pt-query-digest)/如何单条SQL分析和Explain及trace工具 https://blog.csdn.net/qq_38130094/article/details/103551705
7 mysql系列(七)mysql 主从复制和mysql查询优化 https://blog.csdn.net/qq_38130094/article/details/103603586

对MySQL的sql查询进行性能分析主要有两种方式:

  1. 分析整个数据库服务器,这样可以找到哪些查询是主要的压力来源。
  2. 定位具体需要优化的查询后,可以对这些查询进行单独的剖析,分析哪些子任务是影响时间的主要消耗者。

1.mysql 开启慢查询日志

#是否开启慢查询日志,1/on表示开启,0/off表示关闭
SHOW VARIABLES like 'slow_query_log';
#未使用索引的查询也被记录到慢查询日志中,on表示开启,off表示关闭
SHOW VARIABLES like 'log_queries_not_using_indexes';
#慢查询阈值(秒级),当查询时间大于设定的阈值时,记录日志
SHOW VARIABLES like 'long_query_time';
#慢查询日志存储路径
SHOW VARIABLES like 'slow_query_log_file';

1.1 mysqldumpslow工具:mysql自带工具可以直接使用

-s, 是表示按照何种方式排序
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t, 是top n的意思,即为返回前面多少条的数据;
-g, 后边可以写一个正则匹配模式,大小写不敏感的;

例如:

#得到返回记录集最多的10个SQL 
mysqldumpslow -s r -t 10 /tmp/mysql-slow.log #得到查询次数最多的10个SQL 
mysqldumpslow -s c -t 10 /tmp/mysql-slow.log#得到查询次数最多,查询中包含“select”的10个SQL 
mysqldumpslow -s c -t 10 -g "select" /tmp/mysql-slow.log

1.2 pt-query-digest工具(第三方工具)很重要的一个mysql性能分析工具

1.下载安装工具;

wget percona.com/get/pt-query-digest

2.授予用户执行权限;

chmod u+x pt-query-digest

4.安装与Perl相关的模块;

yum install perl-DBIyum install perl-Digest-MD5

常用命令:

#分析慢查询日志中SQL的统计结果
pt-query-digest /tmp/mysql-slow.log | more
#分析select语句统计结果
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /tmp/mysql-slow.log | more
#查询指定用户(root)访问的查询
pt-query-digest --filter '($event->{user}) =~ m/^root/i' /tmp/mysql-slow.log | more
#分析所有全表扫描语句的统计结果
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes")' /tmp/mysql-slow.log | more
#分析所有Full join语句的统计结果
pt-query-digest --filter '(($event->{Full_join} || "") eq "yes")' /tmp/mysql-slow.log | more
#通过tcpdump抓取mysql的tcp协议数据,然后再分析
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

在这里简要的介绍下pt-query-digest /tmp/mysql-slow.log | more 命令

执行 pt-query-digest 日志路径 | more

1.3 第一部分:总体统计结果

Exec time Lock time Rows sent Rows examine Query size

执行时间

锁定时间

发送行数

扫描行数

查询字符数

1.4 第二部分:查询分组统计结果

Rank 所有语句的排名,默认按查询时间降序排列,通过--order-by指定
Query ID 语句的ID,(去掉空格和查询条件中的文本值,计算hash值)
Response 总的响应时间
time 该查询在本次分析中总的时间占比
calls 执行次数,即本次分析总共有多少条这种类型的查询语句
R/Call 平均每次执行的响应时间
V/M 方差均值比(Variance-to-mean),也就是常说的离差指数。
最后一列 查询对象

1.5 第三部分:每一种查询的详细统计结果

查询各项数据的百分比、总数、最小、最大、平均、95%等各项目的统计,包括SQL执行次数、执行时间、锁占用时间、发送行数、扫描行数、查询字符数,表格中也统计了查询涉及的数据库、查询时间直方图(Query_time distribution)等信息。

Rows Examine(扫描的行数越大磁盘IO越大)

Rows sent(发送的行数) 

如果扫描的行数远远大于发送的行数说明可优化的空间很大

总结:

通过pt-query-digest工具可以分析出需要优化的SQL

一般查询次数较多且每次查询占用时间长的SQL;

IO大的SQL(也就是扫描行比发送行多很多的)未使用索引的SQL

2. 单条SQL分析:SHOW PROFILE

SHOW PROFILE命令是在MySQL5.1以后的版本中引入的,来源于开源社区中的Jeremy Cole的贡献。该工具默认是禁用的, 可以通过   SET profiling = 1;  修改开启:这个命令是针对一个session的需要执行sql后在执行这个命令才能看到

#查看开启工具后的每条SQL执行总体情况:show PROFILE;

#根据query_id查看某个查询的详细时间耗费:SHOW PROFILE FOR QUERY 1;

#对每一个子任务的花费时间进行已统计排序:

SELECT state, SUM(duration) AS Total_R, ROUND(100 * SUM(duration) / (SELECT SUM(duration) FROM information_schema.profiling WHERE query_id = 1), 2) AS Pct_R, COUNT(*) as Calls, SUM(duration) /COUNT(*) AS "R/Call" FROM information_schema.profiling
WHERE query_id = 1 GROUP BY state ORDER BY total_r DESC;
Creating sort index Sending data table lock System lock Sorting result copying to tmp table Creating tmp table
当前的SELECT中需要用到临时表在进行ORDER BY排序。建议:创建适当的索引 发送数据 表锁 系统锁。建议确认是由于哪个锁引起的,通常是因为MySQL或InnoDB内核级的锁引起的 结果的排序 将数据复制到临时表 创建临时表

3. explain(执行计划)

3.1:执行计划各个字段代表的意思:(type:查询类型,key:使用的索引,Extra)为重要部分

Extra:

  1. Using index:列数据仅仅使用了索引中的信息而没有读取实际的表

  2. Using where:MySQL服务器将在存储引擎检索行后,通过Where子句条件进行过滤

  3. Using temporary:MYSQL需要创建一个临时表来存储结果,用于排序

  4. Using filesort:MySQL将对结果进行外部排序

table type possible_keys key key_len rows Extra

对应的表

连接类型(system、const、eq_ref、ref、range、index、all)

可能使用的索引

实际使用的索引

使用索引长度

预计扫描行数 解析查询的额外信息(using index、using where、using temporary、using filesort)
EXPLAIN SELECT id from company;

3.2:Extra: 全索引扫描

EXPLAIN SELECT id from company

MySQL执行计划的局限

  • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
  • EXPLAIN不考虑各种Cache
  • EXPLAIN不能显示MySQL在执行查询时所作的优化工作
  • 部分统计信息是估算的,并非精确值
  • EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划

4. trace工具sql执行的索引成本计算

mysql最终是否选择走索引或者一张表涉及多个索引,mysql最终如何选择索引,我们可以用trace工具来一查究竟,开启trace工具会影响mysql性能,所以只能临时分析sql使用,用完之后立即关闭;

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;‐‐开启trace
mysql> select * from test where id = '111';
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

执行后的结果保存在 information_schema.OPTIMIZER_TRACE 表中;其中最重要的就是trace字段;

 

  相关解决方案