MySQL 使用慢查询日志 获取性能问题的SQL

sqlercn | | 访问(126)

  MySQL开启慢查询日志

vim /etc/my.cnf
log_output = file # 必须指定file或者是table如果是table则慢查询信息会保存到mysql库下的slow_log表中。这点要清楚。默认值是NONE
slow_query_log = on # 用于指定是否打开慢查询日志 
slow_query_log_file = /tmp/mysql-slow.log # 慢查询日志文件路径
long_query_time = 2 # 单位秒,指定了慢查询的阈值,即如果执行语句的时间超过该阈值则为慢查询语句,默认值为10秒。
log_queries_not_using_indexes = OFF #未使用索引的查询也被记录到慢查询日志中(可选)。尽量少使用。

show variables like 'log_%'; # 查看log相关配置

  慢查询日志分析工具(mysqldumpslow)

mysqldumpslow --help # 可显示其参数的使用
# 经常使用的参数:
-s #是order的顺序
al #平均锁定时间
ar #平均返回记录时间
at #平均查询时间(默认)
c  #计数
l  #锁定时间
r  #返回记录
t  #查询时间
-t #是top n的意思,即为返回前面多少条的数据
-g #后边可以写一个正则匹配模式,大小写不敏感的

mysqldumpslow -s t -t 10 g /tmp/mysql-slow.log # 查询筛选条件

  慢查询日志分析工具(pt-query-digest)

  pt-query-digest 是用于分析mysql慢查询的一个工具,与mysqldumpshow工具相比,py-query_digest 工具的分析结果更具体,更完善。

  1、安装于Perl相关的模块

yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5 # 安装依赖工具
cd /tmp # 跳转到 tmp 目录,下载文件
wget percona.com/get/pt-query-digest # 下载文件 
chmod u+x pt-query-digest # 授权
mv /tmp /usr/bin/ # 移动文件

  2、使用介绍

pt-query-digest --help
Usage: pt-query-digest [OPTIONS] [FILES] [DSN]
--host      #指定MySQL地址;
--port      #指定MySQL端口;
--socket    #指定MySQL SOCK文件;
--user      #指定MySQL用户;
--password  #指定MySQL密码;
--type      #指定将要分析的类型,默认是slowlog,还有如binlog,general log等;
--charset   #指定字符集;
--filter    #对输入的慢查询按指定的字符串进行匹配过滤后再进行分析;
--limit     #限制输出结果百分比或数量,默认值是20,即将最慢的20条语句输出,如果是50%则按总响应时间占比从大到小排序,输出到总和达到50%位置截止;
--review    #将分析结果保存到表中,这个分析只是对查询条件进行参数化,一个类型的查询一条记录,比较简单;当下次使用--review时,如果存在相同的语句分析,就不会记录到数据表中;
--history   #将分析结果保存到表中,分析结果比较详细,下次再使用--history时,如果存在相同的语句,且查询所在的时间区间和历史表中的不同,则会记录到数据表中,可以通过查询同--CHECKSUM来比较某类型查询的历史变化;
--since     #从什么时间开始分析,值为字符串,可以是指定的某个"yyyy-mm-dd [hh:mm:ss]"格式的时间点,也可以是简单的一个时间值:s(秒)、h(小时)、m(分钟)、d(天),如12h就表示从12小时前开始统计;
--until     #截止时间,配合--since可以分析一段时间内的慢查询;
--log       #指定输出的日志文件;
--output    #分析结果输出类型,值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon;一般使用report,以便于阅读;
--create-review-table     #当使用--review参数把分析结果输出到表中时,如果没有表就自动创建;
--create-history-table    #当使用--history参数把分析结果输出到表中时,如果没有表就自动创建;

  3、使用实例

  a) pt-query-digest分析慢查询日志;

pt-query-digest --report /tmp/mysql-slow.log

  b) 报告最近半个小时的慢查询;

pt-query-digest --report --since 1800s /tmp/mysql-slow.log

  c) 报告一个时间段的慢查询;

pt-query-digest --report --since '2018-07-17 00:00:00' --until '2018-07-18 23:59:59' /tmp/mysql-slow.log

  d) 报告只含select语句的慢查询;

pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /tmp/mysql-slow.log

  e) 报告针对某个用户的慢查询;

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /tmp/mysql-slow.log

  f) 报告所有的全表扫描或full join的慢查询;

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' /tmp/mysql-slow.log

  g) 把查询保存到query_review表;

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review --create-review-table /tmp/mysql-slow.log

  h) 把查询保存到query_history表;

pt-query-digest --user=root –password=abc123 --history h=localhost,D=test,t=query_history --create-history-table /tmp/mysql-slow.log

  i) 通过tcpdump抓取mysql的tcp协议数据,然后再分析;

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report.log

  j) 分析binlog;

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report.log

  k) 分析general log;

pt-query-digest --type=genlog localhost.log > slow_report.log

  MySQL 服务器处理查询请求的整个过程

  1、客户端发送SQL请求给服务器

  2、服务器检查是否可以在查询缓存中命中该SQL

  3、服务器端进行SQL解析,预处理,再由优化器生成对应的执行计划

  4、根据执行计划,调用存储引擎API来查询数据

  5、将结果返回给客户端

  MySQL查询缓存

  可以通过下面的SQL查看当前查询缓存相关参数状态:

show variables like 'have_query_cache';

  设置query_caceh_type参数

vim /etc/my.cnf # 编辑
query_caceh_type = 1

  0 表示不开启查询缓存,

  1 表示始终开启查询缓存(不要缓存使用sql_no_cache)

  2 表示按需开启查询缓存(需要缓存使用sql_cache)

  MySQL查询优化器

  https://blog.csdn.net/caomiao2006/article/details/52204617

  MySQL性能分析语句 show profile/show profiles

  https://blog.csdn.net/littleboyandgirl/article/details/68486642

  MySQL Performance Schema 用于监视MySQL服务器,且运行时消耗很少的性能

  MySQL 5.5开始新增一个数据库:PERFORMANCE_SCHEMA,主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA,而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。MySQL5.5默认是关闭的,需要手动开启,在配置文件里添加:

vim /etc/my.cnf # 编辑
performance_schema = ON

show variables like 'performance_schema'; # 查看是否开启

 https://www.cnblogs.com/zhoujinyi/category/421066.html