10个提升MySQL性能的小技巧
从工作量分析到索引的三条规则，这些专家见解肯定会让您的MySQL服务器尖叫。
编译|枣泥布丁
在所有的关系数据库中，MySQL已经被证明了完全是一头野兽，只要通知停止运行就绝对不会让你多等一秒钟，使你的应用置于困境之中，你的工作也承受极大的风险。
不过事实是，普通的错误都在MySQL性能错误的射程之内。所以为了使你的MySQL服务器能够高速运转，提供稳定且持续的服务，消除这些错误是非常有必要的，但是这可能常常会被你的繁忙工作或配置陷阱微妙地遮蔽了。
幸运的是，许多MySQL性能问题其实都有相似的解决办法，发现并解决问题，然后你的MySQL用起来就顺手多啦。
接下来就和大家分享一下10个使MySQL性能提升的小技巧。
想要了解你的服务器到底如何支配时间，最好的办法就是对服务器的工作进行配置。通过配置你的服务器，你可以expose最昂贵的query来为将来的调优做准备。从这个角度，时间就是最重要的衡量标准，因为当你对你的服务器发起一个query之后，除了它到底多块的完成之外你不会关心任何其他事。
配置你的工作文件的最优解就是MySQL Enterprise Monitor的query分析仪或者Percona Toolkit的pt-query-digest。这些工具可以帮助你捕捉你的服务器正在执行的询问以及返回按响应时间递减顺序排序的任务表，它还会持续不断地把最昂贵、费时的任务更新在最上方，这样你就能知道你的精力应该更加集中在什么地方了。
工作文件配置工具会把相似的询问分在一个组，你可以很方便地查看低速运行或者是告诉运行但是多次进行的询问。
一个数据库服务器需要以下4种资源才能正常运转：CPU，内存，硬盘以及网络。如果这其中任何一种性能不足，运转不力或者超负荷运转的话，那么数据库服务器就非常可能表现不佳。
理解基础的资源是非常重要是以下两个层面：选择硬件以及疑难问题解答。
当为MySQL选择硬件的时候，确保所有的组件都表现良好。同样重要的是把它们进行合理的配置。大多是时候，一些机构会选择高转速的CPU以及硬盘，但是他们通常来讲内存都不够用。在某些情况下，按照数量级增加内存是提升性能最廉价的办法，尤其是工作负载是绑定磁盘的情况下。这听起来可能违反常识，但是在许多情况下，硬盘都是过度使用的，因为没有足够的内存来储存数据工作集。
另外一个平衡的典范当属CPU。在大多数情况下，MySQL使用高转速的CPU会运转得很好，因为每一个询问都是在单线程中运行而不能在CPU之间并行。
当要解答疑难问题的时候，请了解清楚所有资源的性能和使用情况，用你审慎的目光来判断它们到底是本来就性能差劲还是因为承载了过多的任务。这个姿势应该能让你解决问题快一些。
队列和队列访问模式可以在你完全没有察觉的情况下偷偷进入你的应用。举个栗子，如果你设置了某个项的状态，以便某个特定的工作进程在调用它之前可以声明它，那么你就在无意中创建了一个队列。把邮件标记为未发送，发送它们，然后它们被标记为已发送就是一个很好理解的栗子。
队列会产生问题主要有2个原因：它们连续运转你工作，防止它们并行，那么这通常就会产生一个表格，里面包含了进程中的工作还有很久以前已完成工作的历史数据。这不仅会使你的应用产生延迟而且也会给MySQL增加不必要的负荷。
优化MySQL性能最好的办法就是先完成廉价、不确定的工作，然后在最小的结果数据集中完成艰难、准确的工作。
例如你要通过一个给定的地理位置半径来找到你想要的东西。在大多数程序员的工具箱里，他们首先会想到的一定是计算球面上的距离的大圆公式(Haversine)。但是用这个公式的问题在于可能要用到很多三角方面的运算，这对CPU的要求是非常高的。大圆的计算往往运行缓慢，使得机器的CPU使用率飙升。
在你开始应用大圆公式之前，在总集当中将你的记录减少成最小的子集，并把结果集整合成一个确切的圆。一个包含圆（确切或不确切的）的正方形是解决这个问题最简单的方法。这样的话，正方形之外的一切都不回碰上这些成本昂贵的三角函数。
伸缩性其实并不像你想象的那样捉摸不定。实际上在数学当中已经有非常明确的将伸缩性表示为方程式的定义。这些方程式突出展现了为什么系统并没有如预期那样的良好伸缩。
参见通用可扩展法（Universal Scalability Law）—非常清晰地解释和量化了一个系统的伸缩性特性。它从两个基础成本方面对伸缩性问题进行了阐释：序列化（serialization）和串扰（crosstalk）。
多进程必须为在伸缩性上具有固有限制的序列化停止工作。相似地，如果多个进程必须时时刻刻互相交流才能配合他们的工作的话，他们就是在互相限制。
避免序列化以及串扰，你的应用伸缩性将会大大提升。那么在对MySQL来说意味着什么呢？因情况而异，但有些示例可以避免对行进行排它锁定。关于队列，参见技巧3，往往会因为队列伸缩性就变得很差。
DBA常常耗费大量的时间来调整配置。换来的结果有时却是伤害而不是大的提升。我看到过许多的最优化的服务器时不时就崩溃，内存不足，而且在工作负载稍微多一点的时候就表现很差。
MySQL上搭载的默认配置是一刀切并且严重过时的，但是它们也不需要完全重新配置。只要把最基础的设置正确，有需要的话再做小幅调整即可。在大多数情况下，通过正确设置大约10个选项，你可以获得服务器峰值性能的95%。其他无法应用此方法的的情况的话应该是非常特殊的情况，所以就不用去管他了。
在大多数情况下，服务器“转换”工具是不推荐的，因为它们常常会有一些在特定情况下并不适用的规则。有些甚至存在危险且不准确编码—例如缓存命中率和内存消耗公式。这些都是不对的，而且随这时代的进步他们变得更加地不对。
分页应用常常会把服务器搞瘫痪。在向你展示结果的页面当中，有翻到下一页的链接，这类应用通常不以索引的方式进行分类整理，然后他们使用一种 LIMIT和 offset使得服务器做大量的工作生成，然后丢弃行。
优化选项在用户界面常常自己就能找到。而不是展示确切的页数结果以及每个页面的单独链接，只展示下一列的链接就好。你也可以防止大家翻到太后面的页数。
从质询方面来看，你可以比你想要的多选取一行，然后当你点击“下一页”链接的时候，你可以指定最后一行作为下一组结果的起点，而不是使用带offset的 LIMIT。举个栗子，当用户在查看120行中的第101行时，你会同时select第121行；为了递交下一页，你可以向服务器询问第121行或者超过121的行，限定在21。
监管和预警是必不可少的，但是典型的监控系统到底怎么了？它开始发送一些错误的手势，然后系统管理员就设置了垃圾邮件过滤规则来停止这些烦扰。然后很快你的监管系统就会完全瘫痪。
我倾向于从两个方面来看待监管；获取指标以及发出预警。尽可能的获取并保存指标是非常重要的，因为当你想要知道系统到底改变了什么的时候你会很庆幸你当初保存了它们。有一天会突然出现一个很奇怪的错误，然后你就会很高兴你有能力指出服务器的工作负载中的一段然后展示这个改变。
相比之下，警告就可能有点多了。人们常常会对缓存命中率或者短期内每秒所创建的表格发出警告。问题是对这种缓存命中率并没有一个合适的阈值。正确的阈值并不是随着服务器的不同而变化，而是随着你工作负载的不同，每一个小时都是不一样的。
这就导致，警告只能有节制地并且只能在预示一个具体、可操作的问题时才是可行的。一个低缓存命中率并不是可操作的问题，而且他也不指向一个实在的问题，但对连接尝试没有响应的服务器才是真正需要结局的问题。
Index可能是数据库汇总最难理解的概念，因为很容易就对Index到底如何工作以及服务器如何使用它们感到困惑。确实要花些力气才能真正理解它到底是怎么回事。
Index经过适当设计后，主要在数据库服务器中提供如下三种服务：
1. Index让服务器查找相邻行的集合而不是单独的行。许多人可能会认为index的作用就是为了查找单独的行，但是查找单独的行会导致混乱的硬盘操作，速度就会变慢。而且查找行的集合要容易多了，所有或者说大多数都比一次只查找一个行要有趣多了。
2. Index通过按照阅读喜好进行排列省去了整理的过程。整理是耗费巨大的。按照自己的喜好进行阅读效率也更高。
3. Index完全满足了服务器的询问，根本就不需要再连接表格。这是众所周知的覆盖索引或仅索引查询。
如果你可以定义自己的索引和询问来利用这三个机会，你就可以使查询速度快几个数量级。
不要一个人冒险。如果你对一个问题感到烦恼，同时也在做一些对你来说有逻辑且隔离的解决方式，那很好。这在20次中可能会有19次是有效的。但是剩下的1次，你可能会掉进兔子洞里，会非常费时费力，这完全是因为你现在所做的努力只是看起来可能是有意义的。
建立与MySQL相关的资源网络，这超越了工具和故障排除指南。有一些非常有知识的人潜伏在邮件列表、论坛、问答网站上，等等。会议、展会和本地用户团体活动都提供了宝贵的机会，让你能与那些在紧要关头帮助你的同行建立联系。
如果你们想要找工具对上述的技巧进行实践，你可以看看Percona Configuration Wizard for MySQL, Percona Query Advisor for MySQL, 以及 Percona Monitoring Plugins。（你需要注册一个Percona账号才可以免费进入前两个链接。）配置向导可为新的服务器生成一个基准my.cnf文件，这个文件优于服务器上的样本文件。Query advisor会分析你的SQL查询来检测潜在的坏模式，如分页查询（技巧7）。Percona Monitoring Plugins是一系列的监管和图形绘制插件，它们可以帮助你及时保存数据并且警惕预警（技巧8）。所有这些工具都是免费的哦。
相关内容
微信扫码咨询专知VIP会员