为啥隔壁老王的MySQL性能那么好？

mysql 性能优化| 2017-10-27

有很多配置MySQL的方法可以帮助确保您的数据库快速响应查询，并且减少应用程序性能下降。

以下是帮助您优化MySQL数据库性能的一些重要技巧。

MySQL优化关键1：了解如何使用EXPLAIN

对于数据库，您做出的最重要的两个决策分别是：一、设计应用程序实体之间的关系如何映射到表(数据库模式)中，二、设计应用程序如何以所需格式(查询)获取所需的数据。

复杂的应用程序可能具有复杂的查询和模式。如果您要获得应用程序所需的性能和扩展性，不能仅仅直观的来了解查询是如何执行的。

您应该学习如何使用EXPLAIN命令。此命令向您展示了应该如何执行查询，并让您深入了解可以预期的性能以及查询如何随着数据大小的变化而缩放。

类似于MySQL Workbench的工具，都可以为您显示EXPLAIN输出，但您仍然需要学习基础知识以理解它。

EXPLAIN命令提供输出有两种不同格式：旧式表格格式和更现代化的结构化JSON文档，后者能提供更多的细节(如下所示)：

图0：为啥隔壁老王的MySQL性能那么好？

对于一个组件来说应该关注的是“查询成本”。查询成本是指基于许多不同的因素上，MySQL在查询执行的总体成本考虑了该特定查询成本。

简单查询的查询成本通常低于1000。成本在1000到100000之间的查询被视为中等成本查询，如果您每秒只运行数百个这样的查询(而不是数万)，通常认为是快速的。

超过100000的查询认为是高成本查询。通常，当您是系统上的单个用户时，这些查询仍然运行得很快，但是必须要考虑到在交互式应用程序中使用这些查询的频率(尤其是随着用户数量的增长)。

虽然这都是一些大致的数字，但是它们表现出了一般原则。体系结构和配置可能会影响系统的处理查询工作负载。

确定查询成本的主要因素是查询是否使用正确索引。 EXPLAIN命令可以告诉您查询是否要用索引。这就是为什么学习使用EXPLAIN 的重要原因。

MySQL优化关键2：创建正确的索引

索引可以减少查询必须扫描数据量来提高查询性能。 MySQL中的索引用于加速数据库中的访问，并帮助实施数据库约束(例如UNIQUE和FOREIGN KEY)。

数据库索引很像书籍索引。它们保存在自己的位置，并且包含已经在主数据库中的信息。它们是一种数据所在的参考方法。索引不会更改数据库中的任何数据，只是指向数据的位置。

在系统运行查询中，您应该始终查看索引。

一个缺失的索引也可能会使数据库运行速度速度降低。但要不要添加不需要的索引!不必要的索引会减慢数据库运行速度。

MySQL优化关键3：不要使用默认模式!

像任何软件一样，MySQL有许多可配置的设置，可用于修改行为。但是管理员忽略了许多可配置的设置，始终在默认模式下运行。

为了获取MySQL的最佳性能，了解可配置设置是非常重要的，更重要的是将它们设置为最适合您的数据库环境。

默认情况下，MySQL适合于小规模开发安装，而不是用于生产规模。您通常要配置MySQL，以使用可用的所有内存资源，并允许应用程序所需的连接数。

这里有三个MySQL性能调优设置：

innodb_buffer_pool_size：缓冲池是缓存数据和索引的地方。这是使用具有大量RAM的系统作为数据库服务器的主要原因。如果您只运行InnoDB存储引擎，通常会为缓冲池分配大约80%的内存。如果运行非常复杂的查询、有大量的并行数据库连接或者有大量的表，那么可能需要将此值降低一个级别，为其他的运行分配更多内存。

当您设置InnoDB缓冲池大小时，不要将其设置得太大否则会导致互换。这绝对会破坏数据库性能。一个简单的检查方法是查看Percona监控和管理系统概述图中的交换活动：

图1：为啥隔壁老王的MySQL性能那么好？

如图所示，一些交换是非常频繁的。如果您看到持续的交换活动为每秒1MB或更多，那么将需要减少缓冲池大小(或其他内存使用)。

如果第一次没有获得innodb_buffer_pool_size的正确值，不用担心。从MySQL 5.7开始，可以动态更改InnoDB缓冲池的大小，无需重新启动数据库服务器。

innodb_log_file_size：这是一个单独的InnoDB日志文件大小。默认情况下，InnoDB使用两个值，以便您可以将此数字加倍，以获取循环重做日志空间的大小，确保事务持久运行。这也优化了应用对数据库的更改。设置innodb_log_file_size是一个需要权衡的问题，分配的重做空间越大，写入密集型工作负载的性能越好，但如果系统遇到电源丢失或其他问题，花费的恢复时间也越长。

如何知道MySQL性能受当前InnoDB日志文件大小的限制呢?可以通过查看实际使用的重做日志空间来判断。最简单的方法是查看Percona Monitoring and Management InnoDB Metrics仪表板。在下图中，InnoDB日志文件大小不够大，因为使用的空间非常接近可用的重做日志空间(由红线表示)。日志文件大小应至少比用于保持系统执行最佳性能的空间大20%。

图2：为啥隔壁老王的MySQL性能那么好？

max_connections：大型应用程序通常需要比默认的连接数量多得多。与其他变量不同，如果不正确设置，就不会出现性能问题(本质上)。相反，如果连接数量不足以满足应用需求，那么您的应用程序将无法连接到数据库(这对用户来说看起来就像停机了)。获取这个政权变量是非常重要的。

在多个服务器上运行许多组件的复杂应用程序中，可能难以知道需要多少连接。但幸运的是，MySQL可以很容易地看到在峰值操作时使用了多少个连接。通常，为确保应用程序使用的最大可用连接数比最大连接数至少大30%。查看这些数字的简单方法是在Percona监控和管理的MySQL概述仪表板中使用MySQL连接图。下图显示了一个健康的系统，其中有很多额外的连接可用。

图3：为啥隔壁老王的MySQL性能那么好？

要记住的一点是，如果您的数据库运行缓慢，应用程序通常会创建过多的连接。在这种情况下，您应该处理数据库性能问题，而不是简单地允许更多的连接。过多的连接可能会使基础性能问题更糟。

(注意：当您将max_connections变量设置为显著高于默认值时，通常需要考虑增加其他参数，如表缓存的大小和MySQL允许的打开文件数)

MySQL优化关键4：将数据库保存在内存中

近年来，我们看到了固态硬盘(SSD)的转型。即使SSD比旋转硬盘驱动器要快得多，但是它们仍然与RAM中的数据不兼容。这中差异不仅来自于存储性能本身，还来自数据库在从磁盘或SSD存储中检索数据时必须执行的其他工作。

随着硬件改进，无论您是在云端运行还是管理自己的硬件，都越来越有可能将您的数据库存储在内存中 -。

更好的消息是，您不需要将所有数据库都装入内存，只需将常访问的工作数据集合放到内存中即可。

检查数据库在稳定状态下运行的I / O数量(通常在启动后几个小时)。下图您可以在Percona监控和管理的InnoDB Metrics仪表板上的InnoDBI / O。

图4：为啥隔壁老王的MySQL性能那么好？

在上图中，您可以看到峰值高达每秒2000个I / O，这表明(至少对于工作负载的某些部分)，数据库工作集与内存不匹配。

MySQL优化关键5：使用SSD存储

如果您的数据库不适合内存，但仍然需要快速存储来处理写入，并避免数据库加速(重新启动之后)时出现性能问题。这些快速存储意味着需要使用SSD。

由于成本或可靠性原因，一些“专家”仍然主张使用旋转磁盘。但在操作数据库中，这些观点往往是过时的或错误的。今天，SSD在友好的价格上提供了令人印象深刻的性能和可靠性。

然而，不是所有的SSD都是相同的。对于数据库服务器，您应该使用专为服务器工作负载设计的SSD。

一种直接通过NVMe或Intel Optane技术直接连接的SSD可提供最佳性能。即使作为SAN，NAS或云块设备进行远程连接，与旋转磁盘相比，SSD仍然具有优异的性能。

MySQL优化关键6：向外扩展

即使是性能最好的服务器也有局限性。有两种扩展方式：up和out。up意味着购买更多的硬件，但硬件很贵且很快就会过时。out有几个好处：

*可以利用更小、成本更低的系统。

*通过向外扩展能更快更容易的线性放缩。

*由于数据库分布在多台物理机上，因此数据库不会收到单椅硬件故障的影响。

虽然向外扩展有优势，但也有一定的局限性。味了数据同步，扩展需要复制，例如基本的MySQL或Percona XtraDB集群复制。

您还需要确保连接到集群架构的应用程序可以找到所需的数据，通常要通过一些代理服务器和负载平衡器来实现，如ProxySQL或HAProxy。

在计划扩展的同时，要避免过早的扩张，使用分布式数据库往往更复杂。

MySQL优化关键7：拥有可观察性

最好的系统在设计时要考虑到可观察性。

您将MySQL环境设置好、运行并正确调整之后，也不能就将它放置不管，数据库环境可能受到系统或工作负载更改的影响。为流量达到峰值、应用程序错误和MySQL故障等情况做好准备。

当这些情况发生时，你需要快速有效地解决它们。实现这一点的唯一方法是设置一些监控解决方案并进行正确的检测。这可以让您看到数据库环境中正在运行的情况，并在出现问题时分析错误。理想情况下，系统能在发生事件之前进行拦截。

MySQL Enterprise Monitor，Monyog和Percona监控和管理(PMM)都是不错的监控工具，具有免费和开源的优势。这些工具为监控和故障排除提供了良好的操作可见性

随着越来越多的公司转向开源数据库(特别是MySQL)，以此来管理和服务于大规模生产环境中的业务数据，他们需要专注于保持这些数据库的调整和运行的最佳效率。数据库性能可能会导致或破坏您的业务目标，MySQL为您的应用程序和网站提供优质的数据库解决方案，但要根据您的需求进行调整，以满足您的需求并进行监控、查找、防止瓶颈和性能问题。