mysql coalesce性能 mysql性能分析

MySQL性能分析篇

性能分析的思路

1. 首先需要使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句
2. 其次【查看执行计划】查看有问题的SQL的执行计划
3. 最后可以使用【show profile[s]】 查看有问题的SQL的性能使用情况

慢查询日志

慢查询日志介绍

数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化 SQL，更重要的是得先找到需要优化的SQL。MySQL数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL语句，这将极大程度帮助我们快速定位到症结所在，以便对症下药。

至于查询时间的多少才算慢，每个项目、业务都有不同的要求。

MySQL的慢查询日志功能默认是关闭的，需要手动开启。

开启慢查询功能

查看是否开启慢查询功能

- 【slow_query_log】 ：是否开启慢查询日志，1为开启，0为关闭。

- 【log-slow-queries】 ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置

该参数，系统则会默认给一个缺省的文件host_name-slow.log

- 【slow-query-log-file】：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不

设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

- 【long_query_time】 ：慢查询阈值，当查询时间多于设定的阈值时，记录日志，【单位为秒】。

临时开启慢查询功能

set global slow_query_log  = ON;
set global long_query_time = 1;

永久开启慢查询功能

修改/etc/my.cnf配置文件，重启 MySQL, 这种永久生效

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1

慢查询日志格式

格式说明：

第一行,SQL查询执行的具体时间

第二行,执行SQL查询的连接信息，用户和连接IP

第三行,记录了一些我们比较有用的信息，如下解析

第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间。

第五行及后面所有行（第二个# Time:之前）,执行的sql语句记录信息，因为sql可能会很长。

说明：

Query_time,这条SQL执行的时间,越长则越慢

Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间

Rows_sent,查询返回的行数

Rows_examined,查询检查的行数，越长就当然越费时间

分析慢查询日志的工具

使用mysqldumpslow工具

mysqldumpslow是MySQL自带的慢查询日志工具。

可以使用mysqldumpslow工具搜索慢查询日志中的SQL语句

[root@localhost mysql]# mysqldumpslow -s t -t 10 -g“left join”/var/log/mysql/slow.log

常用参数说明：

-s：是表示按照何种方式排序

-t：是topn的意思，即为返回前面多少条的数据

-g：后边可以写一个正则匹配模式，大小写不敏感的

使用percona-toolkit工具

percona-toolkit是一组高级命令行工具的集合，可以查看当前服务的摘要信息，磁盘检测，分析慢查询日志，查找重复索引，实现表同步等等。

下载：

https://www.percona.com/downloads/percona-toolkit/3.0.11/binary/tarball/percona-toolkit-3.0.11_x86_64.tar.gz

安装 ：

yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
yum install -y perl-Time-HiRes
yum install perl-Digest-MD5.x86_64
tar -xf percona-toolkit-3.0.11_x86_64.tar.gz
cd percona-toolkit-3.0.11
perl Makefile.PL
make
make install

pt-query-digest语法及重要选项

pt-query-digest /var/lib/mysql/localhost-slow.log

pt-query-digest [OPTIONS] [FILES] [DSN]

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

用法示例

pt-query-digest     slow.log > slow_report.log
pt-query-digest     --since=12h    slow.log> slow_report2.log
pt-query-digestslow.log --since '2017-01-07 09:30:00' --until '2017-01-0710:00:00'> >slow_report3.log
pt-query-digest--filter '$event->{fingerprint} =~ m/^select/i' slow.log>slow_report4.log
pt-query-digest--filter '($event->{user} || "") =~ m/^root/i' slow.log>slow_report5.log
pt-query-digest--filter '(($event->{Full_scan} || "") eq "yes")||(($event->{Full_join} || "") eq "yes")' slow.log>slow_report6.log
pt-query-digest--user=root –password=abc123 --reviewh=localhost,D=test,t=query_review--create-review-table       slow.log
pt-query-digest     --user=root –password=abc123 --reviewh=localhost,D=test,t=query_history--create-review-table       slow.log_0002
tcpdump -s65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest--type tcpdump mysql.tcp.txt> slow_report9.log
mysqlbinlogmysql-bin.000093 > mysql-bin000093.sql
pt-query-digest     --type=binlog        mysql-bin000093.sql > slow_report10.log
pt-query-digest     --type=genlog       localhost.log > slow_report11.log

profile分析语句

介绍

Query Profiler是MySQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能瓶颈在什么地方。

通常我们是使用的explain,以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该 工具只有在MySQL 5.0.37以及以上版本中才有实现。

默认的情况下，MYSQL的该功能没有打开，需要自己手动启动。

语句使用

show profile 和 show profiles 语句可以展示当前会话(退出session后,profiling重置为0) 中执行语句的资源使用情况.

showprofiles :以列表形式显示最近发送到服务器上执行的语句的资源使用情况.显示的记录数由变量:profiling_history_size 控制,默认15条

show profile: 展示最近一条语句执行的详细资源占用信息,默认显示 Status和Duration两列

show profile 还可根据 show profiles 列表中的 Query_ID ,选择显示某条记录的性能分析信息

开启Profile功能

Profile 功能由MySQL会话变量:profiling控制,默认是OFF关闭状态。

select @@profiling;
show variables like ‘%profil%’;

set profiling=1; --1是开启、0是关闭

示例

看是否打开了性能分析功能

select @@profiling;
show variables like ‘%profil%’;

打开 profiling 功能

set profiling=1; --1是开启、0是关闭
select @@profiling;

执行sql语句

执行 show profiles 查看分析列表

查询第二条语句的执行情况

showprofile for query 2;

可指定资源类型查询