前言

简介

本文经验都是我看书学习的总结的一些经验，面试常问的知识点，所以请关注后再继续观看学习！下面已经给出了书的目录！今后将按目录的顺序继续更新学习心得！接上文继续分享

目标

希望通过这些MySQL的内部原理的知识可以帮助大家培养发现新问题的洞察力，能学习和实践的结合设计出维护基于MySQL的系统。

本章讲述的是服务器性能剖析，为什么某条语句执行不够快，如何确定堆积或卡死的某些间歇性疑难故障。如何优化某条语句的执行速度以及诊断或者解决那些很难观察到的问题。

更新目录

第一章：数据库基础知识（已更新完） 第二章：基准测试（已更新完）第三章：服务器性能刨析（正在更新）第四章：Schema与数据库类型优化 第五章：创建高性能的索引 第六章：查询性能优化 第七章：MySQL高级特性 第八章：优化服务器设置 第九章：操作系统和硬件优化 第十章：复制底层实现 第十一章：可扩展的MySQL 第十二章：高可用性 第十三章：云端的MySQL 第十四章：应用层优化 第十五章：备份与恢复 第十六章：MySQL用户工具

正文

性能优化简介

每个人每个常见对性能有所不同的理解，通过学习我们将性能定义为完成某件任务所需的时间度量，换句话说也就是性能即响应时间，这是一个非常重要的原则。数据库服务器的性能用查询的响应时间来度量，单位是每个查询花费的时间。

很多人会比较迷茫。什么是性能优化，我们暂时假设性能优化就是在一定的工作负载下尽可能地降低响应时间

1. 假如你认为性能优化是降低CPU利用率，那么可以减少对只有的使用。但是这个是一个陷阱，资源是用来消耗并且用来工作的，所以有时间消耗更多的资源能够加快查询速度。个人认为难道查询的响应时间体现性能不是更好吗！
2. 假如你认为性能优化仅仅看成是提升每秒查询量，其实就是吞吐量。吞吐量的提升可以看做性能优化的副产品。对查询的优化可以让服务器每秒执行更多的查询，因为每条查询的时间变短了。（吞吐量的定义是单位时间内的查询数量）
3. 如果性能优化是降低响应时间，那么就需要理解为什么服务器查询这么多时间，然后减去那些对查询结果不必要的工作。也就是说先搞清楚时间花在哪。这就引申出优化的第二个原则：无法测量就无法有效地优化。所以第一步应该测量时间花在什么地方。我们观察到很多人在优化的时候都在精力放在修改一些东西上，却很少去进行精确的测量。真正的做法刚好相反，应该花90%的时间来测量响应时间，如果通过测试还没答案那就是方式错了。如果找到了精确的数据，性能问题一般就暴露出来了，对症下药的解决方式就比较明了了。有两种比较常见的情况会导致不合适的测量

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

一个常见的错误是先查看慢查询，然后又去排查整个服务器的情况来判断问题在哪里。如果确认有慢查询，那么久应该测量慢查询，而不是测量整个服务器。测量应该是从慢查询的开始到结束的时间，而不是查询之前或查询之后的时间。

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

如何判断测量是否正确

实际上测量经常都是错误的，对数量的测量并不等于数量的本身，测量的错误可能很小，跟实际的情况区别不大。问题来了，测量到底有多么不准确？这个问题我们在下章节会逐一展开说明。

通过性能剖析进行优化

性能剖析是测量和分析时间花费在哪里的主要方法。主要有两个步骤：第一步测量任务所花费的时间，第二步对结果进行统计和排序，将重要的任务排到前面。

性能剖析我们主要讨论两种类型的：

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

如果任务执行时间长是因为消耗了太多的资源且大部分时间花费在执行上，等待的时间不多，这种情况下基于等待的分析作用就不大。反之亦然，如果任务一直在等待，没有消耗什么资源，去分析执行时间就不会有什么结果。如果不能确认问题是出在执行还是等待，那就都需要进行试试。

事实上，当基于执行时间的分析发现一个任务需要花费太多时间的时候，应该深入去分析一下，可能会发现某些执行时间实际上是在等待。

在对系统进行性能剖析之前，必须先要能够进行过测量，这需要系统可测量化的支持。

理解性能剖析

MySQL的性能剖析将重要的任务展示在前面，但有时候没显示出来的信息也很重要。尽管性能剖析输出了排名，总计和平均值，但还是有很多是缺失的。比如以下

1. 值得优化的查询
2. 异常情况
3. 未知的未知
4. 被掩藏的细节

对应于程序进行性能剖析

对任何需要消耗时间的任务都可以做性能剖析，然后也包括应用程序。实际上，刨析应用程序一般比剖析数据库服务器容易，而且回报更多。对性能剖析还是建议自上而下的进行，这样可以追逐自用户发起到服务器响应的整个过程。性能瓶颈可能有很多影响的因素

1. 外部资源，比如调用了外部的web服务或者搜索引擎
2. 应用需要处理大量的数据，比如分析一个超大的xml文件
3. 在循环中执行了昂贵的操作，比如滥用正则表达式
4. 使用了低效的算法比如使用暴力搜索算法来查找列表中的项

性能剖析会导致服务器变慢吗

是的，因为性能剖析确实会导致应用慢一点。说不是的话是因为性能剖析可以帮助应用运行的更快。下面解释一下为什么这么说:性能剖析和定期监测都会带来额外的开销，问题就在于这部分的开销有多少是否获取的收益可以抵消这些开销

大多数设计和构建高性能应用程序的人相信，应该尽可能的测量一切可以测量的地方，并且接受这个额外的开销，这些开销应该是应用程序的一部分。

真正的答案是：测量点至少为性能优化贡献了10%。大多数应用并不需要每天都运行详细的性能测量，所以实际贡献甚至要超过10%

TIP：MySQL企业监控器也是值得考虑的工具之一。它可以捕获发送给服务器的查询，要么是通过应用程序连接MySQL的库文件实现，要么就是在代理层实现。该工具有设计良好的用户界面，可以直观地显示查询的剖析结果。并且可以根据时间段进行缩放。

对MySQL进行剖析

对查询进行性能剖析有两种方式，每种方式都各自有各自的问题。可以剖析整个数据库服务器这样可以分析哪些查询是主要是压力来源。定位到具体需要优化的查询后也可以钻取下去对这些查询进行单独的剖析，分析哪些子任务是响应时间的主要消耗者

剖析服务器的负载

剖析服务器是非常有价值的

1. 因为可以有效的审计效率低下的查询。定位和优化坏查询能够显著地提升应用的性能
2. 也能解决某些特定的难题。还可以降低服务器的整体压力，这样所有的查询都将因为减少了对共享资源的争用而收益。
3. 降低服务器的负载也可以推迟或者避免升级更昂贵硬件的要求
4. 还可以发现和定位糟糕的用户体验，比如某些极端的情景

捕获MySQL的查询到日志文件中

1. 慢查询在MySQL中，慢查询日志最初只是捕获比较慢的查询，而性能剖析却需要针对所有的查询。慢查询是开销最低的精度最高的测量查询时间的工具。不必担心慢查询日志会带来额外的IO开销。慢查询带来的开销可以忽略不计，需要担心的应该是慢查询日志可能消耗大量的磁盘空间。如果长期开启慢查询日志一定要部署日志轮转工具。

2. 通用日志没有慢查询优秀不做介绍了，以后用慢查询就OK了

TIP：Percona Server的慢查询比MySQL有着更多的细节和价值信息，比如查询执行计划，锁，IO活动等。慢查询日志是一种轻量而且功能全面的性能剖析工具，是优化服务器查询的利器。

分析查询日志

利用慢查询日志捕获服务器上的所有查询，并且进行分析可以在一些典型的时间窗口如业务高峰的一个小时内查询。如果业务趋势比较均衡，那么一分钟甚至更短的时间内捕获需要优化的低效查询也是可行的。

不要直接打开整个慢查询日志，这样只会浪费时间和金钱。首先应该生成一份剖析报告，如果需要可以再查看日志中特别关注的部分。自顶向下是比较好的方式，否则有可能像前面提到的反而导致业务的逆优化。

TIP：生成剖析报告建议使用pt_query_digest 毫无疑问这是分析MySQL查询日志最有力的工具。该工具功能强大，包括可以将查询报告保存到数据库中，以及追踪工作负载随时间的变化。

剖析单条查询

在定位到需要优化的单条查询后，可以针对此查询钻取更多的信息，确认为什么会花费那么多时间去执行。以及需要如何去优化。具体细节会在今后做展开论述。

1.使用show profile。这是MySQL5.1引入的默认是禁用的 可以通过set profiling=1开启。这是在GA版本中包含的真正的查询剖析工具，默认虽然是禁用的但可以通过服务器变量在会话级别动态的修改。然后在服务器上执行所有的语句，都会测量其消耗的时间和其他一些查询执行状态变更相关的数据。这个功能有一定的作用，而且最初的设计功能更强大，但未来版本中可以会被Performance Schema所取代。尽管如此，这个工具最有用的作用还是在语句执行期间剖析服务器的具体工作。当一条查询提交给服务器时，此工具会记录剖析信息到一张临时表，并且给查询赋予一个从1开始的整数标识符。

2.使用show status。返回了一些计数器。既有服务器级别的全局计数器，也有基于某个连接的会话级别的计数器。它是一个有用的工具并不是剖析工具。show status大部分返回的结果都是计数器可以显示某些活动如读索引的频繁程序，但无法给出消耗了多少时间。尽管无法提供基于时间的统计，但对于执行完查询后观察某些计数器的值还是有帮助的，有时候可以猜测哪些操作代价较高或者消耗的时间较多。最有用的包括句柄计数器，临时文件和表计数器等。

3.使用慢查询日志。慢查询日志中包括了show profile和show status所有的输出，并且还有更多的信息。通过pt_query_digest发现坏查询后，在慢查询日志中可以获得足够有用的信息。

4.使用Performance Schema。在MySQL5.5中新增的Performance Schema表还不支持查询级别的剖析信息，无法被当做一个通用工具。无法提供查询执行阶段的细节信息和计时信息。（了解就好 优先考虑慢查询日志）

使用性能剖析

当获得服务器或者查询的剖析报告中怎么使用呢？

1.优化查询时，用户需要对服务器如何执行查询有较深的了解 2.剖析报告能够尽可能多地收集需要的信息，给出诊断问题的正确方向以及为其他诸如EXPLAIN等工具提供基础信息 TIP：这里简单描述一下后续将继续讨论。

如果使用MySQL，慢查询日志中没有执行计划或者详细的时间信息，对于偶尔记录到这几次查询慢的问题，很难知道到底是哪里出了问题。因为信息有限。也有可能系统的其他地方的应用程序消耗了资源。比如其他程序正在备份，也有可能是挣锁或阻塞。这种间歇性问题出来了，继续深入将告诉答案。

诊断间歇性问题

间歇性问题比如系统偶尔停顿或者慢查询，很难诊断。有些稀奇古怪的问题只在没有注意到的时候才发生，而且无法确认如何重视。诊断这类问题要花费非常多的时间。尽量不要使用试错的方式来解决此类问题，因为有可能引发其他地方的间歇性问题。如果一时无法定位问题，可能是测量的方式不正确或者测量的点选择有误也有可能是工具选型问题下面我们整理了都是间歇性数据库性能问题的实际案例 1.应用通过curl从一个运行得很慢的外部服务来获取汇率报价的数据。 2.memcached缓存中的一些重要条目过期，导致大量请求落到MySQL以重新生成缓存条目。 3.DNS查询偶尔会有超时现象。 4.可能是由于互斥锁争用，或者内部删除查询缓存的算法效率太低的缘故，MySQL的查询缓存有时候会导致服务有短暂的停顿。 5.当并发度超过某个阈值时，InnoDB的扩展性限制导致查询计划的优化需要很长的时间。

以上问题的确有些是出于数据库的问题，也有些不是。只有在问题发生的地方通过观察资源的使用情况，并尽可能测量出数据才能避免在没有问题的地方耗费精力。

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

首先确认这是单条查询的问题还是服务器的问题，这是解决问题的正确方向。如果服务器上所有的程序都突然变慢，又突然都变好，每一条查询也都变慢了，那么慢查询可能就不一定是原因，而是由于其他问题导致的结果。也就是说如果服务器整体运行没有问题，只有某些查询偶尔变慢就需要将注意力放在某条查询上面。具体怎么解决呢 将引出以下三种方式

1.使用show global status

实际上就是以较高的频率比如一秒执行一次show global status命令捕获数据，问题出现时，则可以通过某些计数器的尖刺或凹陷来发现。这个方法比较简单所有人都可以使用，对服务器影响也小，所以是一个花费实际不多却很好地了解问题的好方法

2.使用show processlist

实际上通过不停地捕获showprocesslist的输出，来观察是否有大量线程处于不正常的状态或者有不正常的特征。

3.使用查询日志

如果需要查询日志发现问题需要开启慢查询日志并在全局级别设置long_query_time为0，并且所有新的设置都采用了新的设置。可能需要重新连接以使新的全局设置生效。如果因为某些原因不能设置慢查询日志记录所有的查询，也可以通过tcpdum和pt-query-digest工具来模拟替代。要注意找到吞吐量突然下降时间段的日志。查询是在完成阶段才写入到慢查询日志的所以堆积会造成大量查询处于完成阶段，直到阻塞其他查询的资源占用者释放资源后其他查询才能执行完成。

捕获诊断数据

可视化数据最具有说服力！

开始之前先搞清楚两件事 1.一个可靠且实时的触发器，也就是能区分什么时候问题出现的方法。 2.一个收集诊断数据的工具

很多人有疑问，触发器是什么？

触发器是非常重要的。可以在出现问题时能够捕获数据的基础，有两个常见的问题可能导致无法达到预期的结果。误报和漏检。误报是指收集了很多诊断数据，但期间其实没有发生问题，这可能浪费时间。漏检则是指出问题出现时没有捕获到数据，错失了机会，一样地浪费时间。所以在开始行动前多花一点时间来确认触发器能够真正的识别问题是非常值得的。

究竟是什么导致了性能低下？

当一个资源变得效率低下时，应该了解一下为什么会这样 1.资源被过度使用，余量已经不足以正常工作 2.资源没有被正确配置 3.资源已经破坏或者失灵

以下抛出几个疑问点

1. 为什么我们不一开始就优化慢查询？
2. 查询由于糟糕的执行计划而执行缓慢不是一种警告吗？
3. 如果缓存项被重新生成了很多次，是不是会导致产生很多同样的查询呢？
4. 每秒有几百次select查询，但只有五次update。怎么能确定这五次update的压力不会导致问题呢?
5. IO风暴最初的证据看起来不是很充分？

其他剖析工具

使用USER_STATISTICS表

使用 strace

strace工具可以调查系统调用的情况。strace会对mysql这样大量线程场景产生一些副作用。当strace附上后mysqlid会运行的很慢，因此不适合在产品中使用。但是有一些场景是非常适用strace的。生成IO活动 剖析报告，其他方法很难达到这个目的

总结

1.我们认为定义性能最有效的方法是响应时间 2.如果无法测量就无法有效的优化，所以性能优化工作需要基于高质量，全方位及完整的响应时间测量。 3.测量的最佳开始点是应用程序而不是数据库。即使问题出在底层的数据库，借助良好的测量也可以很容易地发现问题 4.大多数系统无法完整的测量，测量有时候也会有错误的结果。但也可以想办法绕过一些限制，并得到好的结果。 5.完整的测量会产生大量需要分析的数据，所以需要用到剖析器。这是最佳的工具可以帮助将重要的问题冒泡到最前面，这样就可以决定从哪里开始分析会比较好 6.剖析报告是一种汇总信息，掩盖和丢弃了太多细节。而且它不会告诉你缺少了什么，所以完全依赖剖析报告也是不明智的。 7.有两种时间消耗的操作。工作或者等待。大多数剖析器只能测量因为工作而消耗的时间，所以等待分析有时候是很有用的补充，尤其是当CPU利用率很低但工作却一直无法完成的时候。 8.优化和提升是两回事，当继续提升的成本超过收益的时候应当停止优化 9.注意你的直觉，但应该只根据直觉来指导解决问题的思路，而不是用于确定系统的问题。决策应当尽量基于数据而不是感觉

总体来说，解决性能问题的方法首先要澄清问题，然后选择合适的技术来解答这些问题。如果你想尝试提升服务器的总体性能那么一个比较好的起点是将所有查询记录到日志中，然后利用pt_query_digest工具生成系统级别的剖析报告。如果是要追查某些性能低下的查询，记录和剖析的方法也会有帮助。可以把精力放在寻找那些消耗时间最多的，导致了糟糕的用户体验或者那些高度变化的，抑或有奇怪的响应时间直方图查询。当找到了坏数据时，钻取pt_query_digest报告中包含的该查询的详细信息，或者使用show profile及其他诸如explain这样的工具。如果找不到这些性能低下的原因，那么也可能遇到了服务器级别的性能问题。这时，可以较高精度的测量和绘制服务器状态计数器的细节部分。

结尾

目前更新出来的是我学习到的经验知识与心得，剩下的部分将过几天持续更新！

有一些地方不详细的地方请查阅专业的具体的资料，我只是提出了一些解决方式与方案。让大家知道用这个可以解决它。如果把具体的也描述一遍我可能要凉凉因为太多了。请谅解