【.com原稿】随着容量和负载的增加，MySQL的性能会越来越慢。这里有七个保持MySQL平稳运行的技巧。性能是我们衡量应用的一种方式，而应用性能的一个指标就是用户体验，也就是我们常说的：“用户是否需要等待超过合理的时间才能得到他们想要的东西？”在不同的情况和场景下，这个指标会有所不同。例如：对于移动购物应用，响应时间不能超过几秒；对于员工的人力资源页面，响应时间可以超过几秒。因此，无论标准如何，保持良好的应用程序性能至关重要，否则用户会抱怨（或者更糟的是，用户会切换到其他应用程序）。数据库性能是影响应用程序性能的因素之一。可以说，应用程序、网站和数据库之间的交互将直接影响到应用程序服务等级的建立。这种交互的一个核心组成部分是各种应用程序如何查询数据库以及数据库如何响应各种请求。无论标准如何，MySQL都是当今最流行的数据库管理系统之一。越来越多的企业转而使用MySQL（和其他开源数据库）作为其生产环境中的数据库解决方案。有多种方法可以配置MySQL，以确保您的数据库能够快速响应各种查询，而应用程序性能只会略有下降。以下是帮助您优化MySQL数据库性能的7个必备技巧：了解如何使用EXPLAIN创建正确的索引拒绝默认设置将数据库加载到内存中使用SSD存储横向扩展追求可见性了解如何使用EXPLAIN对你的数据库做出任何设计决策时，有两个方面非常重要：应用程序实体如何映射到每个数据表（数据库模式模式）。应用程序如何以他们需要的格式类型获取（查询）数据。复杂的应用程序必然具有复杂的架构模式和查询。如果你想让你的各种应用程序具有所需的性能和可扩展性，你不能依靠纯粹的直觉来理解各种查询的执行机制。建议您仔细学习如何使用EXPLAIN命令，而不是猜测。该命令将向您展示查询是如何执行的；并深入了解性能的真正表现以及查询如何随着数据量的变化而扩展。许多MySQLWorkbench等工具可以为您可视化EXPLAIN的输出，但您仍然需要了解它的基础知识。EXPLAIN命令的输出有两种不同的格式：旧式表格形式和提供更多详细信息的新型结构化JSON文档。如下所示：mysql>explainformat=jsonselectavg(k)fromsbtest1whereidbetween1000and2000\G******************************1.row***************************解释：{“query_block”：{“select_id”：1，“cost_info”：{“query_cost”：“762.40”}，“table”：{“table_name”：“sbtest1”，“access_type”：“range”，“possible_keys”：[“PRIMARY”]，“key”：“PRIMARY”，“used_key_parts”：[“id”]，“key_length”：“4”，“rows_examined_per_scan”：1874，“rows_produced_per_join”：1874，“filtered”：“100.00”，“cost_info”：{“read_cost”：“387.60”，“eval_cost”：“374.80”，“prefix_cost””：“762.40”，“data_read_per_join”：“351K”}，“used_columns”：[“id”，“k"],"attached_condition":"(`sbtest`.`sbtest1`.`id`between1000and2000)"}}}需要重点关注的部分是：查询成本查询成本是指基于查询的综合成本执行MySQL决定一个查询的成本，考虑了很多不同的因素。一般来说，一个简单的查询的成本会低于1000。成本值在1000到100,000之间被认为是一个中等成本的查询。因此，如果你是只运行如果你有几百个（不是几万个）这样的查询，速度一般应该是比较快的。如果查询成本在十万以上，那么开销就比较大。通常当你的系统只有一个用户时，此类查询仍然可以快速执行。当然，您需要仔细考虑在交互式应用程序中使用此类查询的频率（尤其是随着用户数量的增长）。虽然这些是近似数字，但它们可以反映总体实践中，您的系统性能会更好还是更差何时处理查询负载完全取决于其自身的体系结构和配置。决定查询成本的最重要因素之一是查询是否使用各种索引。如果没有使用索引进行查询，EXPLAIN命令会指出，通常是从数据库中索引是如何创建的以及查询本身是如何设计的。这就是为什么EXPLAIN值得去学习和用好它的原因。创建正确的索引索引是通过减少在数据库中查询时必须扫描的数据量来提高查询本身的效率。在MySQL中，索引用于加速访问数据库，帮助遵循数据库的各种约束条件（如UNIQUE和FOREIGNKEY）。数据库索引就像书的索引，保存了它们的位置信息，包含了数据库的主要信息。它们是一种引用方法或数据位置的映射，因此索引不会更改数据库中的任何数据。它们只是指向数据的存储位置。但是，索引并不总是匹配任何加载请求。在系统运行的过程中，要不断地创建各种索引。虽然索引好的数据库运行速度会更快，但是如果缺少单个索引，就会拖慢整个数据库的效率。因此，我们需要使用EXPLAIN找到缺失的索引并添加它。注意：不要添加不需要的索引，因为不必要的索引反过来会减慢数据库的速度。拒绝默认设置与任何其他软件一样，MySQL也可以修改其行为并通过各种可配置设置最终优化其性能。同时，这些配置设置往往被管理员忽略，一直保持着默认值。为了从MySQL获得最佳性能，重要的是要了解如何配置MySQL并将它们设置为最适合您的数据库环境的状态。默认情况下，MySQL针对小规模发布和安装进行了调整，而不是真正的生产规模。因此，您通常需要将MySQL配置为使用所有可用的内存资源，并允许您的应用程序所需的最大连接数。这里有3个与MySQL性能优化相关的设置值得你仔细配置：innodb_buffer_pool_size数据和索引用作缓存的缓冲池。当您的数据库服务器有大量系统内存时，可以使用此设置。如果您只运行InnoDB存储引擎，您通常可以将大约80%的内存分配给缓冲池。但是，如果您正在运行非常复杂的查询或者如果您有大量的并发数据库连接，或者如果您有非常大的数据表，那么您可能需要将这个值降低一个级别以便为其他调用分配更多的内存。当你设置InnoDBbufferpool的大小时，一定不要太大也不要太频繁导致交换，因为这些肯定会降低你的数据库性能。一种简单的检查方法是在“Percona监控和管理”中。如图所示，如果您看到恒定的交换活动大于每秒1MB，则需要减小缓冲池的大小，或使用额外的内存。如果您一开始没有正确获得innodb_buffer_pool_size值，请不要担心。从MySQL5.7开始，您可以在不重新启动数据库服务器的情况下动态更改InnoDB缓冲池的大小。innodb_log_file_size这是指单个InnoDB日志文件的大小。默认情况下，InnoDB使用两个值，因此您可以给InnoDBround-robin重做日志空间，通过加倍来确保事务持久性。这也优化了写入数据库的性能。设置innodb_log_file_size的值值得深思熟虑：分配的重做空间越多，写密集型工作负载的性能就越好。但是，如果您的系统遇到断电或其他崩溃问题，则需要更长的时间才能恢复。您可能会问：您如何知道您的MySQL性能是否受到当前InnoDB日志文件大小的限制？您可以通过查看未使用的重做日志空间量来判断。最简单的方法是查看“PerconaMonitoringandManagement”InnoDBMetricsDashboard。下图中，InnoDB的日志文件不够大，已用空间反复逼近可用重做日志空间，如红线所示：因此，您的日志文件至少应比用于保留的空间大20%系统处于***性能状态。max_connections大型应用程序通常需要比默认数量更多的连接。与其他变量不同，如果您未正确设置此值，则会遇到性能问题。也就是说，如果连接数不足以满足您的应用程序的需要，应用程序将根本无法连接到数据库，并且在用户看来是宕机了。可见正确设置非常重要。对于在多个服务器上运行多个组件的复杂应用程序，可能很难知道您需要多少个连接。幸运的是，MySQL可以轻松获知高峰期使用的连接数。通常，您希望确保应用程序使用的活动连接数与可用活动连接数之间至少存在30%的差异。查看这些数字的一种简单方法是查看Percona监控和管理的系统概览屏幕中的使用MySQL连接图。下图显示了一个健康的系统，具有足够数量的可用额外连接。还有一件事要记住：如果您的应用程序创建了过多的连接，通常会导致数据库运行缓慢。在这种情况下，您应该发挥数据库性能，而不是简单地允许建立更多连接。更多的连接会加剧潜在的性能问题。将数据库加载到内存中近年来，固态硬盘(SSD)的方向发生了转变。尽管固态硬盘比传统的旋臂硬盘快得多，但它们仍然无法在内存中存储数据。这种差异不仅来自存储性能本身，还来自数据库在从磁盘或SSD访问数据时所做的额外工作。随着近年来硬件技术的进步，将数据库加载到内存中已经变得可行，无论你是在云端运行还是管理自己的硬件。更令人兴奋的是：您不需要将整个数据库加载到内存中以获得其性能优势，您只需要将最常访问的数据集放入内存即可。您可能已经看过一些关于应将多少百分比（例如10%到33%）的数据库加载到内存中的文章。事实上，没有“一刀切”的法则，数据访问量的大小决定了加载到内存中获得的最大性能的提升程度。您可以在数据库达到稳定状态（通常是开始运行后的几个小时）时检查I/O，而不是寻找特定的“幻数”。看一下数据的读取，因为如果你的数据库加载到内存中，那么读取就完全完成了；而只要内存可用，写入就会一直发生。下面是来自PerconaMonitoringandManagement中InnoDBMetricsDashboard的InnoDBI/O图表：如上图所示，那些峰值为每秒2,000次的I/O操作表明（至少是一部分流量负载）它们是数据集在内存数据库中不匹配。使用SSD存储无论您的数据库是否加载到内存中，您都需要快速存储来处理写入操作并避免数据库启动后（重启后）出现性能问题。这里的快速存储指的是固态硬盘。一些所谓的“专家”基于成本和可靠性，仍然主张使用机械臂硬盘。坦率地说，当涉及到数据库操作时，这些建议通常已经过时或者完全错误。如今，固态硬盘性能卓越、可靠且价格实惠。并非所有SSD生而平等。对于数据库服务器，您应该选择能够处理服务器工作负载并妥善保管数据的SSD。例如：防止断电损坏，避免使用那些专为台式机和笔记本电脑设计的商用固态硬盘。通过NVMe或IntelOptane技术直接连接的SSD往往会提供最佳性能。即使远程连接到SAN、NAS或云中的块设备，SSD也可以提供优于旋转臂HDD的性能。即使是最好的服务器，横向扩展也是有限制的。行业一般采用两种方式进行扩张：纵向和横向。扩大规模意味着购买更多硬件。这不仅昂贵，而且硬件贬值很快。另一方面，横向扩展在处理负载方面有几个优势：您可以从更小、成本更低的系统中获益。水平扩展使系统的线性扩展更加方便快捷。由于数据库会跨越多台物理机增长，水平扩展可以在保护数据库的同时消除硬件单点故障。尽管有许多优点，水平缩放也有一定的局限性。Scale-out需要数据复制，比如基本的MySQLReplication或者PerconaXtraDBCluster来进行数据同步。但作为回报，您也可以获得更高的性能和可用性。如果您需要更高级的可伸缩性，请考虑使用MySQL分片。此外，您还需要确保连接到集群架构的应用程序能够找到它们需要的数据。这通常是通过一些代理服务器和负载均衡器（如ProxySQL或HAProxy）来实现的。当然，过早地规划横向扩展会增加分布式数据库的复杂性。最近发布的MySQL8候选版本已经声称能够在单个系统上处理超过200万个简单查询。追求可见性可见性是系统设计的终极境界，MySQL也不例外。一旦您设置、运行和调整了您的MySQL环境，不要认为您已经完成了。数据库环境会受到系统变化或流量负载的影响，以及流量高峰、应用程序错误和MySQL本身等各种问题。为了快速有效地解决各种问题，需要建立和实施一些监控机制，以便在出现错误时及时了解数据库环境的状态，及时分析服务器上的数据。因此，最理想的情况是在系统出现问题或被用户察觉之前预防问题。常用的监控工具有：MySQLEnterpriseMonitor（企业监视器）。莫尼奥格。PerconaMonitoringandManagement(PMM)具有免费和开源版本。这些工具为监控和故障排除提供了出色的操作可见性。随着越来越多的公司使用开源数据库（尤其是MySQL）在大规模生产环境中管理和服务其业务数据，他们需要专注于保持数据库的高效调优和运行。MySQL确实是一个优秀的数据库，可以提高您的应用程序和网站的性能，当然您需要通过调整它来监控、发现和防止任何瓶颈和性能问题以满足您的业务需求。JulianChen在IT项目、企业运维、风险管控等领域拥有十余年经验，日常工作深入系统安全的各个环节。作为CISSP证书持有者，他在各种专业期刊上发表了《IT运维的“六脉神剑”》、《律师事务所IT服务管理》和?的论文。他也持续分享和更新《廉环话》系列博文和各种外文技术翻译。曾被评为“信息安全实践者”、Future-S2015中国IT治理与管理实践者。【原创稿件，合作网站转载请注明原作者和出处为.com】
