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

附上转载地址：

http://software.cnw.com.cn/software-database/htm2012/20120529_247735.shtml

与所有的关系型数据库一样，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个技巧中所提到的，队列扩展性差的原因就是如此。