高性能MySQL第三章

性能优化简介

我们将性能定义为完成某件任务所需要的时间度量，换句话说，性能即响应时间。无法测量就无法有效地优化。

性能优化不仅仅是降低CPU利用率，有时候新的版本可能会提高CPU利用率，但是这样也会伴随着查询速度的增快。

两种比较常见地情况会导致不合适的测量：

• 在错误的时间启动和停止测量
• 测量的是聚合后的信息，而不是目标活动本身

例如：一个常见的错误是先查看慢查询，然后又区排查整个服务器的情况来判断问题出在哪里。如果确认有慢查询，那么就应该测量慢查询而不是测量服务器。

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

• 如果要优化执行时间，最好的办法是测量定位不同的子任务花费的时间，然后去优化一些子任务
• 如果要优化等待时间则比较复杂，因为等待可能有很多情况都会这样。

对应用程序进行性能剖析

通过性能剖析进行优化

性能剖析一般有两个步骤

1. 测量任务所花费的时间
2. 然后对结果进行统计和排序，将重要的任务排到前面

我们将实际地讨论两种类型的性能剖析：

1. 基于执行实际的分析：分析研究的是什么任务的执行时间最长
2. 基于等待的分析：判断任务在什么地方被阻塞的时间最长

事实上，当基于执行时间的分析发现一个任务需要花费太多时间的时候，应该深入去分析，可能会发现某些执行时间实际上在等待。在对系统性能剖析之前，必须先要能够进行测量。实际系统很少可以做到可测量化。但幸运的是，我们可以从外部去测量系统，如果测量失败，也可以根据对系统的了解做出一些靠谱的猜测。

理解性能剖析

• 值得优化的查询：一些只占总响应时间比重很小的查询是不值得优化的，如果花费了1000美元去优化一个业务，但业务的收入没有任何增加，那么可以说反而导致业务被逆优化了1000美元。如果优化的成本大于收益，就应当停止优化。
• 异常情况：某些任务即使没有出现在性能剖析输出的前面也需要优化。比如某些任务执行次数很少，但每次执行都非常慢，严重影响用户体验
• 未知的未知：一款好的性能剖析工具会显示可能的“丢失的时间”—任务的总时间和实际测量到的时间之间的差。如果处理器的CPU时间是10秒，而性能剖析到的任务总时间是9.7秒，那么就有300毫秒的丢失时间，说明有一些任务没有测量到。
• 被掩盖的细节：性能剖析无法显示所有响应时间的分布，只相信平均值是非常危险的。

对应用程序进行性能剖析

性能瓶颈可能有很多影响因素：

• 外部资源，比如调用了外部的web服务或搜索引擎
• 应用需要处理大量的数据，比如分析一个超大XML文件
• 在循环中执行昂贵的操作
• 使用了低效的算法，比如用暴力搜索法或者猴子算法来查找。

性能剖析本身会导致服务器变慢，问题在于这部分的开销由此获得的收益是否能够抵消这些开销。为应用构建一些永久使用的轻量级的性能剖析也是有意义的。比如记录给SQL语句加上脚本总时间统计。

书中是介绍了测量PHP应用程序，这里就不具体展开了。

剖析MySQL查询

剖析服务器负载

在服务器端可以有效地审计效率低下的查询。定位和优化坏查询能显著地提升应用地性能。对MySQL来说就是各种日志了。

第一种是捕获慢查询日志和通用日志，慢查询日志是一种轻量而功能全面地性能剖析工具没事优化服务器查询的利器。详细介绍如何找到MySQL上的慢查询日志

第二种是通过抓取TCP网络包。详细介绍如何进行TCP抓包书上的例子还是有些难懂，还是看博客来得实在。

剖析单条查询

在定位到需要优化的单条查询后，可以针对此查询钻取更多信息，确认为什么会花费这么长的时间执行以及如何去优化。

• 使用SHOW PROFILE它可以返回最近一条查询的详细信息详细使用前面的命令
• 使用SHOW STATUS这个命令返回了一些计数器，如果执行SHOW GLOBAL STATUS则可以查看服务器级别的从服务器启动时开始计算的查询次数统计。不过全局的计数器也会出现在前者那个命令中。不过这个工具仅仅只是一个计数器而已，我们可能需要使用脚本循坏读取某些数据，做成表才能看出其中的奥秘。

诊断间歇性问题

间歇性的问题很难诊断。有些幻影问题只在没有注意到的时候才发生，而且无法确认如何重新，诊断这样的问题往往要花费很多时间。尽量不要用使用试错的方式来解决问题，这种方式有很大的风险，结果可能变坏而且比较低效。

下面列举了一些以及解决的一些间歇性数据库性能问题的实际案例：

• 应用通过curl从一个运行得很慢得外部服务来获取汇率报价得数据。
• memcached缓存中的一些重要条目过期，导致大量请求落在MySQL以生成缓存条目（和Redis的缓存击穿，缓存雪崩差不多的意思）
• DNS查询偶尔会有超时的现象
• 由于互斥锁争用，或者内部删除查询缓存的算法效率太低的缘故，MySQL的查询缓存有时候会导致服务有短暂的停顿。
• 当并发度超过某个阈值时，InnoDB的扩展性限制导致查询计划的优化需要很长的时间

单条查询问题还是服务器问题

如果服务器上所有的程序都突然变慢，又突然变好，每一条查询都变慢，那么慢查询可能就不一定时原因，而是由于其他问题导致的结果。相反，如果整体运行没有问题，则就把注意力放在这条特定的查询上面。具体的情况大多数都可以通过三种技术来解决！

1. 使用SHOW GLOBAL STATUS：实际上就是以一种比较高的频率执行该命令来捕获数据，问题出现时，就可以通过分析由这些计数器生成的表的尖峰或者凹陷来判断，而且这个命令对服务器的消耗也比较小。
2. 使用SHOW PROCESSLIST：通过不断捕获该命令的输出，来观察是否有大量线程处于不正常的状态或者其他不正常的特征。假如长时间处于statistics状态，一般是说服务器在查询优化阶段如何确定表关联的顺序—通常都是非常快的。一个经典例子是很多处于Locked状态，这是MyISAM的一个问题，它只有表锁定
3. 使用查询日志，这个之前的博客中都有写，就是去看日志。

捕获诊断数据

当出现间歇性问题时，需要尽可能多地收集所有数据，而不只是问题出现时地数据。但是也不能收集所有数据，这样太难找了，所以一个可靠且实时地触发器是非常重要的。

那么好的触发器的标准是什么呢？误报和漏检少一些的触发器（当然好像是人为控制的阈值），误报就是收集了很多诊断数据，但是期间没有发生任何问题。漏报指问题出现时没有捕获到数据。

选择一个合适的阈值很重要，既要足够高，以确保在正常时不会触发；又不能太高，要确保问题发生时不会错过。问题持续上升的趋势一般会导致更多的问题，所以不能设置太高。比如线程连接数偶尔出现非常高的尖峰，所以设置到4999可以捕获到问题，但是非这么高才收集就会出现漏报。假如正常一般不会超过150，那么设置在200或300就会更好。