译者注：随着规模和负载的增长，MySQL性能趋于下降。请牢记这些提示，以保持MySQL平稳运行。衡量应用程序的方法之一是查看性能。性能指标之一是用户体验。流行的说法是“用户是否需要等待更长的时间才能得到他们想要的”。该指标在不同的应用中有所不同。对于移动购物应用程序，响应时间不应超过几秒。对于员工的HR页面，可能需要多花几秒钟。有很多关于性能如何影响用户行为的研究：79%的客户不太可能返回到速度慢的网站47%的消费者希望网页在2秒或更短时间内加载40%的用户体验的网站加载时间比A放弃3秒页面加载时间的1秒延迟可能会导致7%的损失和11%的页面浏览量减少无论使用何种标准，都必须保持良好的应用程序性能。否则，用户会抱怨（或者更糟，转到其他应用程序）。影响应用程序性能的因素之一是数据库性能。应用程序、网站和数据库之间的交互对于确定应用程序的性能至关重要。这种交互的一个核心组成部分是应用程序如何查询数据库以及数据库如何响应请求。无论如何，MySQL是最流行的数据库管理系统之一。越来越多的企业转向使用MySQL（和其他开源数据库）作为生产环境中的数据库解决方案。有许多方法可以配置MySQL以帮助确保数据库快速响应查询并最大限度地降低应用程序性能。以下是一些有助于优化MySQL数据库性能的基本技巧。优化提示#1：学习如何使用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，“过滤”："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认为这个特定查询在查询执行的总成本方面昂贵的程度，并且基于许多不同的因素。简单查询的查询开销通常小于1,000。开销在1,000到100,000之间的查询被认为是中等开销，如果您每秒只运行几百个查询（而不是数万个），通常会更快。成本超过100,000的查询可以被认为是昂贵的。通常，当您是系统上的单个用户时，这些查询仍会快速运行，但您应该仔细考虑在交互式应用程序中使用此类查询的频率（尤其是随着用户数量的增长）。当然，这些数字只是性能的粗略指示，但它们展示了一般原则。根据其体系结构和配置，您的系统可能更好或更差地处理查询工作负载。决定查询成本的主要因素是查询是否正确使用索引。EXPLAIN命令可以告诉您查询是否使用索引（通常是因为索引是如何在数据库中创建的，或者查询本身是如何设计的）。这就是为什么学习使用EXPLAIN如此重要的原因。优化提示#2：创建正确的索引索引通过减少查询必须扫描的数据库中的数据量来提高查询效率。MySQL中的索引用于加速数据库访问并帮助实施数据库约束（例如UNIQUE和FOREIGNKEY）。数据库索引很像书籍索引。它们保存在自己的位置并包含主数据库中已存在的信息。它们是数据所在位置的引用方法或映射。索引不会更改数据库中的任何数据。它们只是指向数据的位置。任何工作负载都没有完美的索引。相反，始终在系统运行的查询上下文中查看索引。索引良好的数据库不仅运行速度更快，而且即使缺少索引也会使数据库慢得像爬行一样。使用EXPLAIN（如前所述）查找缺失的索引并添加它们。但要小心：不要添加不需要的索引！不必要的索引会减慢您的数据库优化技巧#3：拒绝使用默认设置与任何软件一样，MySQL有许多可配置的设置，可用于修改行为（以及最终的性能）。与任何软件一样，这些可配置设置中有许多会被管理员忽略，并最终以默认模式使用。要从MySQL获得最佳性能，了解可配置的MySQL设置非常重要，更重要的是，将它们设置为最适合您的数据库环境。默认情况下，MySQL用于小型开发安装，而不是生产规模。您通常希望将MySQL配置为使用所有可用的内存资源，并允许您的应用程序需要的连接数。以下是您应该经常仔细检查的三个MySQL性能调优设置：innodb_buffer_pool_size：缓冲池用于存储缓存数据和索引。这是使用具有大量RAM的系统作为数据库服务器的主要原因。如果只运行InnoDB存储引擎，通常有80%的内存分配给缓冲池。如果您正在运行非常复杂的查询，或者有大量并发数据库连接，或者有大量表，您可能希望将此值降低一个档次，以便为其他操作分配更多内存。在设置InnoDBbufferpoolsize的时候，需要注意不要设置的太大，否则会造成swapping。这肯定会影响数据库性能。一种简单的检查方法是查看Percona监控和管理中系统概览图中的交换活动：如图所示，有时进行一些交换是可以的。但是，如果您看到每秒1MB或更多的一致交换活动，则需要减少缓冲池大小（或其他内存使用量）。如果您在第一次访问时没有正确获取innodb_Buffer_pool_size值，请不要担心。从MySQL5.7开始，可以在不重启数据库服务器的情况下动态更改InnoDB缓冲池的大小。innodb_log_file_size：这是单个InnoDB日志文件的大小。默认情况下，InnoDB使用两个值，因此您可以将此数字加倍以获得InnoDB用于确保事务持久性的循环重做日志空间的大小。这也优化了对数据库的应用更改。设置innodb_log_file_size是一个权衡问题。分配的重做空间越多，写入密集型工作负载的性能就越好，但如果系统断电或出现其他问题，从崩溃中恢复所需的时间就越长。你怎么知道MySQL性能是否受限于当前的InnoDB日志文件大小？您可以通过查看实际使用了多少可用重做日志空间来判断。最简单的方法是查看PerconaMonitorandManagementInnoDBMetrics仪表板。在下图中，InnoDB日志文件的大小不够大，因为已用空间非常接近可用重做日志空间（用红线表示）。日志文件的大小应至少比用于保持系统正常运行的空间大20%。MAX_Connections：较大的应用程序通常需要比默认值更多的连接数。与其他变量不同，如果设置不正确，将不会出现性能问题（本身）。相反，如果连接数不足以满足您的应用程序需求，那么您的应用程序将无法连接到数据库（这对您的用户来说就像是停机）。所以正确处理这个变量很重要。如果您有一个复杂的应用程序，其中有多个组件在多个服务器上运行，可能很难知道需要多少个连接。幸运的是，MySQL可以很容易地查看在高峰操作期间使用了多少连接。通常，您希望确保应用程序使用的最大连接数与可用的最大连接数之间至少有30%的差距。查看这些数字的一种简单方法是使用PerconaMonitoringandAdministrationMySQLOverviewDashboard中的MySQLConnectionGraph。下图显示了一个健全的系统，其中有大量额外的连接可用。要记住的一件事是，如果数据库运行缓慢，应用程序通常会创建过多的连接。在这种情况下，您应该处理数据库的性能，而不是简单地允许更多连接。更多的连接会使潜在的性能问题变得更糟。（注意：当设置max_Connections变量明显高于默认值时，通常需要考虑增加其他参数，例如表缓存的大小和打开的MySQL文件的数量。然而，这不在本文的讨论范围内.)优化技巧#4：将数据库保存在内存中近年来，我们看到了向固态磁盘(SSD)的转变。尽管SSD比旋转硬盘驱动器快得多，但它们仍然无法将数据保存在RAM中。这种差异不仅来自存储性能本身，还来自数据库为从磁盘或SSD存储检索数据而必须执行的额外工作。随着全球硬件的改进，越来越有可能将数据库存储在内存中，无论是在云端运行还是管理自己的硬件。更好的消息是，您无需将所有数据库都放入内存中即可获得内存中的大部分性能优势。您只需要将工作数据（最常访问的数据）存储在内存中。您可能已经看到一些文章给出了数据库的哪一部分应该保留在内存中的具体数字，范围从10%到33%。事实上，没有“一刀切”的数字。适合内存以获得最大性能优势的数据量取决于工作负载。与其寻找特定的“***”号，不如检查数据库在其稳定状态下运行的I/O（通常在启动后几个小时）。看一下READ，因为如果数据库在内存中，READ完全可以去掉。无论您有多少可用内存，写入总是需要发生。下面，您可以在用于Percona监控和管理的InnoDBMetrics仪表板中的InnoDBI/O图中看到I/O。在上图中，您可以看到每秒高达2,000次I/O操作的峰值，这表明（至少对于某些部分的工作负载）数据库工作集不适合内存。优化提示#5：使用SSD存储如果您的数据库不适合内存（即使它不适合），您仍然需要快速存储来处理写入并避免在数据库变热（重启后）时出现性能问题。如今，SSD已成为快速存储的代名词。出于成本或可靠性的原因，一些“专家”仍然提倡使用旋转磁盘（机械磁盘）。坦率地说，当涉及到操作数据库时，这些论点通常已经过时或者完全错误。如今，SSD以高昂的价格提供令人印象深刻的性能和可靠性。但是，并非所有SSD都适用。对于数据库服务器，您应该使用专为服务器工作负载设计的SSD，以保护数据（例如，在停电期间）。避免使用专为台式电脑和笔记本电脑设计的商用SSD。通过NVMe或英特尔OpTan技术连接的固态硬盘可提供最佳性能。即使作为SAN、NAS或云块设备进行远程连接，与旋转磁盘相比，SSD仍可提供卓越的性能。优化技巧#6：向外扩展即使是高性能服务器也有其局限性。扩展有两种方式：向上扩展和向外扩展。扩大规模意味着购买更多硬件。这可能很昂贵，而且硬件很快就会过时。横向扩展以处理更多负载有几个好处：可以使用更小且成本更低的系统。线性扩展更快更容易。因为数据库分布在多台物理机器上，所以数据库不受单点硬件故障的影响。虽然水平扩展有好处，但也有一定的局限性。扩展需要复制，例如基本的MySQL复制或PerconaXtraDBCluster，用于数据同步。但作为回报，您可以获得额外的性能和高可用性。如果您需要更大的扩展性，请使用MySQL分片。您还需要确保连接到集群架构的应用程序能够找到它们需要的数据——通常是通过一些代理服务器和负载均衡器（例如ProxySQL或HAProxy）。计划横向扩展时，避免过早地横向扩展。使用分布式数据库往往更加复杂。现代硬件和MySQL服务器将为您提供仅一台服务器的良好体验。最近发布的MySQL8候选版本表明它能够在单个系统上处理超过200万个简单查询。优化技巧#7：可观察性设计最好的系统时要考虑到可观察性——MySQL也不例外。一旦启动、运行并适当调整了MySQL环境，就不能只对其进行设置而不对其进行管理。数据库环境可能会受到系统或工作负载变化的影响。为流量高峰??、应用程序错误和MySQL故障等意外情况做好准备。这些事情可能而且将会发生。当出现问题时，您需要快速有效地解决它们。做到这一点的唯一方法是设置某种监控解决方案并正确初始化它。这使您能够在生产环境中运行时查看正在运行的数据库环境，并在出现问题时分析服务器数据。理想情况下，该系统允许您在问题发生之前或在它们发展到用户可以看到其影响的程度之前预防问题。监控工具包括MySQLEnterpriseMonitor、Monyog和PerconaMonitoringandManagement(PMM)，它们具有免费和开源的额外优势。这些工具为监控和故障排除提供了很好的可操作性。随着越来越多的公司转向开源数据库，尤其是MySQL，以在大规模生产环境中管理和服务其业务数据，他们将需要专注于保持这些数据库的优化并以最高效率运行。与对您的业务目标至关重要的所有事情一样，您的数据库性能可以成就或破坏您的业务目标或结果。MySQL是适用于应用程序和网站的出色数据库解决方案，但需要对其进行调整以满足您的需求并进行监控以发现和防止瓶颈和性能问题。PeterZaitsev是Percona的联合创始人兼首席执行官，Percona是一家企业级MySQL和MongoDB解决方案和服务提供商。《High Performance MySQL》由O'Reilly出版，是最好的MySQL性能书籍之一。Zaitsev经常在PerconaDatabasePerformanceBlog.com上发表博客，并在世界各地的会议上发表演讲。
