002 MySQL性能剖析

最新推荐文章于 2024-09-12 01:15:37 发布

道心不悔

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量242

点赞数

分类专栏： MySQL 文章标签： mysql 性能优化慢查询日志分析工具

本文链接：https://blog.csdn.net/weixin_41717359/article/details/85108349

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

性能剖析

性能优化简介
剖析单条查询

性能优化简介

完成一项任务所需要的时间可以分为两部分：执行时间和等待时间。

优化任务的执行时间：最好的办法是通过测量定位不同的子任务花费的时间，然后优化去掉一些子任务，降低子任务的执行频率或者提升子任务的效率。
优化任务的等待时间：相对来说要复杂一些。因为等待有可能是有其他系统间接影响导致，任务之间也可能由于争用磁盘或者CPU资源而相互影响。

1.通过性能剖析进行优化

性能剖析是测量和分析时间花费在哪里的主要方法。
性能剖析一般有两个步骤：测试任务所花费的时间；然后对结果进行统计和排序，将重要的任务排前面。
响应时间：在任务开始时启动计时器，在任务结束时停止计时器，然后用结束时间减去启动时间得到响应时间。
性能剖析报告会列出所有任务列表。每行记录一个任务，包括任务名，任务的执行时间，任务的消耗时间。任务的平均执行时间，以及该任务执行时间占全部时间的百分比。性能剖析报告会按照任务的消耗时间进行降序排序。

在这里插入图片描述

2.对应用程序进行性能剖析

对任务消耗时间的任务都可以做性能剖析。实际上，剖析应用程序一般比剖析数据库服务器容易，而且回报更多。
性能瓶颈可能有很多影响因素：外部资源，比如调用了外部的Web服务或者搜索引擎。应用需要处理大量的数据，如果分析一个超大的XML文件。在循环中执行昂贵的操作，比如滥用正则表达式。

3.剖析MySQL查询

大家可以使用慢查询日志，在mysql5.0及之前的版本中，慢查询日志的响应时间的单位是秒，粒度太粗了。在mysql5.1及更高版本中，慢日志的功能已经加强，可以通过设置long_query_time为0来捕获所有的查询，而且查询的响应时间单位已经可以精确到微秒级。在MySQL的当前版本中，慢查询日志是开销最低，精度最高的测量查询时间的工具。

剖析单条查询

方式有很多种，在这里我就只详细介绍我使用的三种。

1.show profile

这是mysql自带的功能。
show profile命令是在mysql5.1以后的版本中引入的，来源于开源社区中的JeremyCole的贡献（向大神学习）。这也是目前唯一一个在GA版本（GA版本：通用版本，可以在生产环境发布）包含的真正的查询剖析工具。默认是禁用的，但可以在会话级别动态修改。
在这里插入图片描述
原理：当一条查询提交到服务器时，此工具会记录剖析信息到一张临时表，并且给查询赋予一个从1开始的整数标识符，即Query_ID。
接下来我们用一条实际的SQL来剖析：

// 下面是对Sakila样本数据库的一个视图的剖析结果：先执行下面这条sql，再执行show profiles;
select * from sakila.nicer_but_slower_film_list;

在这里插入图片描述
更细精度：

在这里插入图片描述

// 下面是对Sakila样本数据库的一个视图的剖析结果：
SET @query_id = 3;

SELECT STATE, 
       SUM(DURATION) AS Total_R,
	ROUND(
	    100 * SUM(DURATION) / (
	      SELECT SUM(DURATION)
	      FROM INFORMATION_SCHEMA.PROFILING
	      WHERE QUERY_ID = @query_id
	    ),2
	) AS Pct_R,
	COUNT(*) AS Calls, SUM(DURATION) / COUNT(*) AS "R/Call"
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = @query_id
GROUP BY STATE
ORDER BY Total_R DESC

结果：

在这里插入图片描述
从结果可以得知大部分时间花在发送数据和创建排序索引上。

2.使用show status

首先清空status：flush status再执行查询：
在这里插入图片描述

3.慢查询日志

在这里插入图片描述

log_output 参数是指定日志的存储方式。log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件.
在这里插入图片描述
log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan(全索引扫描)的sql也会被记录到慢查询日志。
在这里插入图片描述

slow_queries：查询有多少条慢查询记录。
在这里插入图片描述

日志分析工具之mysqldumpslow

在实际生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow

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

查询最慢的10条SQL：
mysqldumpslow -s t -t 10 /home/nginxlog/mysqllog/mysql_slow.log

在这里插入图片描述

日志分析工具之pt-query-digest

这个工具比mysql自带的更加强大，以后可以专门出一期来介绍这个工具
pt-query-digest --limit=1 /home/nginxlog/mysqllog/mysql_slow.log

在这里插入图片描述
pt-query-digest /home/nginxlog/mysqllog/mysql_slow.log | more

道心不悔

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
002 MySQL性能剖析

性能剖析性能优化简介1.通过性能剖析进行优化2.对应用程序进行性能剖析3.剖析MySQL查询剖析单条查询1.show profile性能优化简介完成一项任务所需要的时间可以分为两部分：执行时间和等待时间。优化任务的执行时间：最好的办法是通过测量定位不同的子任务花费的时间，然后优化去掉一些子任务，降低子任务的执行频率或者提升子任务的效率。优化任务的等待时间：相对来说要复杂一些。因为等待有可...
复制链接

扫一扫

专栏目录