针对数据库的优化行为，需要先进行测量，测量之后，要对测量结果进行分析。这就需要benchmark和profiling。

benchmark可以用到多种工具。

性能剖析-Profiling

profiling需要我们有足够多的知识和经验。
对于性能的定义：完成某件任务所需要的时间度量，简单的说就是响应时间。
而这个时间可以分为执行时间与等待时间。
执行时间反应的是一条查询为什么会执行那么长时间，而等待时间则是需要弄清楚究竟这个任务在哪里被阻塞了这么久。

性能剖析（profiling）一般有两个步骤：
1.测量任务花费时间
2.对测量结果进行统计和排序，将重要的任务放在前面

对任务进行分组和分析，生成profile report分析报告。
理想的性能优化会在数据库系统中建立一系列的测量点，而大部分时候我们都是通过外部去对系统进行测量（黑盒）。

可以通过pt-query-digest来对系统进行剖析，同时得到有意义的聚合结果。
同时，需要对查询进行聚合，用户一系列的连续性操作行为可以聚合成一个更大的操作，然后对这个操作性能进行分析也是十分有价值的。
比如New Relic工具就可以带来很好的应用程序性能分析功能。

对MySQL查询进行剖析：

对查询性能的剖析可以从两个角度展开：整个服务器负载、针对单条查询语句的剖析。

服务器负载剖析：

对与服务器负载的剖析，可以使用MySQL查询日志。比如设置mysql慢查询日志的阈值为0。
当然可以对通用日志进行分析，这些通用日志可以被记录在数据表中，多数情况下分析通用日志没什么必要。
Percona Server的慢查询日志更为详细
当权限不足无法进行日志写入时，可以使用–processlist不断查看PROCESSLIST或者通过tcpdump的形式抓取数据包，然后用pt-query-digest --type=tcpdump来分析抓包内容。
获取了服务器查询日志之后，就需要对这些日志进行分析。
针对峰谷型服务，可以对负载峰值的时间段进行分析，而如果业务比较稳定，就可以随便选取一分钟来进行分析。
可以使用pt-query-digest来直接对慢查询日志进行分析，分析工具将会为我们提供有意义的统计分析结果。
针对最差的报告，我们开可以详细展开，去看它的执行的详细情况。
工具还为我们准备好了典型sql的explain代码，直接复制拷贝出来就可以在mysql中运行然后查看执行计划

单条查询语句剖析：

SHOW PROFILE:

首先需要设置会话级别的分析开始：
SET profiling = 1;
这时候执行一条sql语句就会被记录下来，然后通过
SHOW PROFILE;
的命令就可以查看详细的剖析结果。
这时候得到的结果没有经过排序，也可以通过直接查询INFORMATION_SCHEMA数据表来获得格式化的数据结果。

SHOW STATUS：

这是一个有用的工具，但并不是一款剖析工具。大部分的结果都是一个计数器，展示了某些活动的频繁程度，而没有办法详细展示消耗的时间。

慢查询日志：

Percona Server提供了详细的慢查询日志的信息，包含show profile和show status的全部输出。

使用Performance Schema：

目前的一些限制使得performance schema还没办法成为一个通用的性能剖析工具
对于大部分用户来说，直接看performance schema中的裸数据需要过多的先验知识和底层原理的理解。

诊断间歇性问题

针对间歇性问题的诊断，尽量不要采用试错的方式，耗费大量时间且得不偿失，并且带来很大的线上风险。
对于间歇性问题的诊断，首先要判断是单条查询的问题还是服务器的问题
如果是服务器所有的查询都变慢，可以优先考虑是服务器整体存在性能问题，而当只有部分查询出现变慢的情况，就要开始考虑是不是单条的查询存在问题了，这里介绍几种诊断的方法：

使用SHOW GLOBAL STATUS：

通过高频率（比如说每秒）执行show global status的方式，收集数据然后分析结果，耗时长但对服务器的压力负载比较小。可以在服务器上长时间执行，并将最后的结果绘制出来，用来发现出现问题的原因。

使用SHOW PROCESSLIST：

持续不断的show processlist可以帮助我们观察是否存在大量线程处于不正确的状态或者有其他不正常的特征。

tips：通过\G可以垂直输出结果，有助于我们后期通过sort|uniq|sort的方式统计某一列值出现的次数。
除此之外，使用INFORMATION_SCHEMA中的PROCESSLIST表或者innotop工具也可以很好的帮助我们实现类似的功能。

使用慢查询日志：

在全局设置long_query_time为0就可以将所有的查询都记录下来，这可能需要重制所有的连接来保证这项修改已经生效。
当无法直接修改long_query_time的时候，也可以使用tcpdump+pt-query-digest来实现类似的功能。
一项查询，是在它已经完成了之后才会被写入慢查询日志，所以如果出现查询堆积，就会造成大量的查询处于完成阶段。
理解发现的问题：
通过gunplot或者R等工具，将以上采集到的大量日志通过图表的形式展示出来，这有助于我们更快的发现异常时间点，定位相关问题。
通过上面的步骤，我们能够收集尽可能多的日志信息，找到问题的时间点，在这之后呢？我们就需要捕获可以用于诊断的数据。这样的收集要尽可能的多，宁可多收集一千条正确数据，也不要放过一条错误的数据。
这里我们就需要一个很好的诊断触发器以及一个数据收集的工具。

诊断触发器：

诊断触发器就是要找到一些能在异常场景下与正常场景的某些阈值进行区分的指标。
合理的阈值将会保证在正常场景下不会被触发（误报），同时异常场景下不会被忽视（漏报）。
针对这种情况，我们可以自己编写脚本来实现监控触发器，或者直接使用pt-stalk。
需要收集什么样的数据？
明确了工具之后，我们就要来看一下哪些数据值得被收集
原则上就是在需要的时间段内尽可能多的进行收集
在GNU/Linux平台，可以使用oprofile，或者strace来分析服务器的系统调用。
对于剖析查询收集而言，使用tcpdump抓取mysql流量也是个不错的选择
对于等待分析，最常用的办法是GDB的堆栈跟踪。pt-pmp工具也可以完成类似的工作。
pt-collect可以用于加速我们的分析工作。

解释结果

对于结果的解释从两方面开始入手：确认问题是否真正发生和寻找明显的跳跃性变化。
对于结果的解释，尤其是针对操作系统和mysql底层的解释，需要非常多MySQL和InnoDB的源码知识，普通用户很难在这些分析的结果里推断出合理的解释。

性能优化有两个需要明确认识到的点：
1. 不要把原因和结果进行混淆
2. 在确认问题之前不要随便尝试改动系统企图修复问题

文章来源: zclhit.blog.csdn.net，作者：zclhit_，版权归原作者所有，如需转载，请联系作者。

原文链接：zclhit.blog.csdn.net/article/details/104622428