关键的十个MySQL性能优化技巧

本文转载java知音

与所有的关系型数据库一样，Mysql仿佛是一头让人难以琢磨的怪兽。它会随时停摆，让应用限于停滞，或者让你的业务处于危险之中。

　　事实上，许多最常见的错误都隐藏在MySQL性能问题的背后。为了确保你的MySQL服务器能够一直处于全速运行的状态，提供持续稳定的性能，杜绝这些错误是非常重要的。然而，这些错误又往往隐藏在工作负载和配置问题之中。

　　幸运的是，许多MySQL性能问题都有着相似的解决方案，这使得排除故障与调整MySQL成为了一项易于管理的任务。以下就是10个让MySQL发挥最佳性能的技巧。

1、分析工作负载

　　通过分析工作负载，你能够发现进一步调整中最昂贵的查询。在这种情况下，时间是最重要的东西。因为当你向服务器发出查询指令时，除了如何快速完成查询外，你很少关注其他的东西。分析工作负载的最佳方式是，使用诸如MySQL Enterprise Monitor的查询分析器，或者Percona Toolkit的pt-query-digest等工具。

　　这些工具能够捕捉服务器所执行的查询，以降序的方式根据响应时间列出任务列表。它们会将最昂贵的和最耗时的任务置顶，这样你就能知道自己需要重点关注哪些地方。工作负载分析工具将相似的查询汇聚在一行中，允许管理者查看速度慢的查询，以及查看速度快但已多次执行的查询。

2、理解四个基本资源

　　功能性方面，一个数据库服务器需要四个基本资源：CPU、内存、硬盘和网络。如果这四个资源中任何一个性能弱、不稳定或超负载工作，那么就可能导致整个数据库服务器的性能低下。理解基本资源在两个特定的领域中至关重要：选择硬件和排除故障。

　　在为MySQL选择硬件时，应该确保全部选用性能优异的组件。这些组件相互匹配，彼此间能够实现合理平衡也很重要。通常情况下，企业会为服务器选择速度快的CPU和硬盘，但是内存却严重不足。在一些案例中，大幅提升性能的最廉价方式是增加内存，尤其是对于那些受制于磁盘读取速度的工作负载。这似乎看起来有点违背常理，但是在许多案例中，由于没有充足的内存以保存服务器正在使用的数据，因此导致了硬盘被过度使用。

　　关于获取这种平衡的另一个例子是CPU。在许多案例中，如果CPU速度快，那么MySQL的性能就非常出色，因为每一个查询都是单线程运行，而无法在CPU间并行运行。在进行故障排除时，应该检查这四个资源的性能和使用情况，关注它们是否性能低下或是超负荷工作。这方面的知识能够帮助你快速地解决问题。

3、不要将MySQL作为队列使用

　　队列以及与队列相似的访问方案会在你不知情的情况下悄悄地进入应用之中。例如，你设置了一个项目状态，以便在执行前，特定的Worker Process（工作进程）能够对其进行标记，那么你就等于在无意间创建了一个队列。例如，将电子邮件标记为未发送，然后发送它们，最后再将它们标记为已发送。

　　队列会导致出现一些问题，这里面有两大主要原因：它们对工作负载进行了序列化，阻碍任务被并行处理。这导致正在处理中的任务和以前在工作中处理过的历史数据会被根据序列排列在一个表单中。这样一来既增加了应用的延时，也增加了MySQL的负载。

4、以最廉价的方式过滤结果

　　优化MySQL的最佳方式是首先要做廉价和不精确的工作，然后再小规模地做困难的精确工作，最后再生成数据集。

　　例如，假设你计算某一个地理坐标点给定半径内的面积。在许多程序员的工具箱里第一个工具就是球面半正矢公式，以计算出球面的长度。这一方法的问题是，该方程式需要许多三角函数运算，需要拥有很强运算能力的CPU。球面半正矢计算不仅运行速度慢，而且会导致机器CPU的使用率飙升。在使用球面半正矢公式前，你可以先分解计算。有些分解计算并不需要使用三角函数。

5、弄清两个扩展性死亡陷阱

　　扩展性可能并不像你认为的那样模糊。实际上，扩展性有着精确的数学定义，它们以方程式的形式被表示出来。这些方程式既指出了系统无法扩展的原因，同时也指出了它们应该进行扩展的原因。通用扩展定律（Universal Scalability Law）揭示和量化了系统的扩展性特征。其通过两个基础性成本解释了扩展问题：即序列化与串扰（Crosstalk）。

　　并行处理要求必须中止序列化，这就限制了它们的扩展性。同样的，如果并行处理需要始终进行彼此对话以协调工作，那么它就相互进行了限制。为了避免序列化与串扰，应用进行了更好的扩展。这些在MySQL内部被翻译成了什么？结果不尽相同。不过，一些案例应该避免锁定在特定的行之中。就像第3个技巧中所提到的，队列扩展性差的原因就是如此。

6、不要过分关注配置

　　数据库管理员会花费许多时间调整配置。调整的结果通常不会有很大的改善，相反有时候会带来损害。我发现许多经过“优化的”服务器，在进行强度稍微高一点的运算时常常出现崩溃、内存不足和性能低下等问题。

　　虽然MySQL在交付时的默认设置严重过时，但是你并不需要对每一项都进行配置。最好是根据需要，进行基本纠正与设置调整。有10个选项调整正确，即可让服务器发挥95%的最大性能。在许多案例中，我们并不推荐所谓的调整工具，因为它们只是提供一个大概设置，对特定案例没有任何意义。有些工具甚至包含有危险的和错误的设备代码。

7、注意分页查询

　　分页查询应用会使服务器性能大降。这些应用会在网页上显示搜索结果，然后通过链接跳转至相应网页上。通常这些应用无法使用索引进行聚合与分类，而是使用LIMIT和OFFSET语句，这导致服务器工作负载大幅增加，并放弃行。 在用户界面上常常会发现优化选项。替代在结果中显示网页数量，以及分别与每个网页相连的链接。这样便可以仅显示至下一页的链接。你还可以阻止查询者浏览与首页过远的网页。

8、保存统计数据，提高报警阀值

　　监控与报警必不可少，但是监控系统被怎么处理了呢？当它们发布假的报警信息时，系统管理员会设置电子邮件过滤规则，以停止这些噪音。很快你的监控系统就彻底没用了。个人认为，应该以下面的两种方式进行监控：捕捉指标与报警。尽可能地捕捉与保存指标非常重要，因为在你试图搞明白系统中需要做哪些调整时，你会庆幸之前保存了它们。如果某一天出现奇怪问题时，你会很高兴自己有能力绘制出服务器工作负载变化的图形。

9、了解索引的三大规则

　　索引可能是数据库中被误解最多的一项。因为它们的工作方式有许多种，这导致人们常常对索引如何工作，以及服务器如何使用它们感到困惑。要想彻底搞清楚它们需要花上很大一番功夫。在被正确设计时，索引在数据库中主要用于实现以下三个重要目的：

　　1）它们让服务器寻找相邻行群组，而不是单个行。许多人认为，索引的目的是寻找单个行，但是寻找单个行会导致随时磁盘操作，速度很慢。寻找行群组就要好许多，与一次寻找一个行相比，这更具吸引力。

　　2）它们让服务器避免以期望的读行顺序对检索结果排序，排序成本十分高昂。以期望的顺序读行速度将更快。

　　3）它们能够满足来自一个索引的所有查询，从根本上避免了访问表单的需求。这被称为覆盖索引或索引查询。

　　如果你能设计出符合这三个规则的索引与查询，那么你的查询速度将大幅提升。

10、利用同行的专业知识

　　不要孤军奋战。如果你在苦苦思考某个问题，并着手制订明智的解决方案，那么这非常不错。在20次中，有19次问题会被顺利解决。但是其中会有一次让你不知所措，导致耗费大量的资金和时间，准确地说，是因为你正在尝试的解决方案只是貌似合理。

　　创建一个MySQL相关资源网的意义远远大于工具集与故障排除指南。许多经验丰富的专业人员就隐藏在论坛、问答网站之中。会议、展览以及本地用户集体活动，都会为我们提供获得新见解的机会和与同行建立联系的机会，关键时刻这将对你很有帮助。

来源：软件测试网

链接：http://www.51testing.com/html/26/n-815126.html

喜欢本文的朋友们，欢迎长按下图关注订阅号成猿之路，收看更多精彩内容！