10个MySQL性能调优技巧 译文 精选

译者 | 崔皓

审校 | 孙淑娟

模式设计、索引、查询、配置、I/O......还会出错？遵循这10个提示，保证让你的MySQL服务器流畅到飞起。

MySQL是世界上使用最广泛的开源数据库，它在业界的受欢迎程度让其他数据库望尘莫及。它是一个关系型数据库管理系统，多年来都作为流行应用程序的核心。然而，在使用上可能会遇到挑战，因此在性能提高上存在很多机会。

在过去的几年里，MySQL也有一些重要的新发展。本文更新了Baron Schwartz??之前??提供的??一套MySQL性能调整技巧??。

下面是10个MySQL性能调优技巧。

目录

• MySQL性能提示1：模式设计与任何其他MySQL设置一样重要
• MySQL性能提示2：辅助索引（Secondary?Key）不是你的敌人
• MySQL性能提示3：行可以从索引中获得服务
• MySQL性能提示4 :审查与回顾
• MySQL性能提示5：可见性很重要
• MySQL性能提示6：谨慎使用调优工具
• MySQL性能提示7：I/O操作仍然昂贵
• MySQL性能提示8：利用通用表的表达式优势
• MySQL性能提示9：注意云计算
• MySQL性能提示10：保持Replication的最新状态

MySQL性能提示1：模式设计与任何其他MySQL设置一样重要

模式设计是在数据库中最应该重视的事情之一。这一数据库设计原则，早在20世纪70年代就被引入。自MySQL从5.6版本中转移到InnoDB作为默认存储引擎后，模式设计变得更加重要。

为什么会这样呢？在InnoDB中，所有的东西都是主键!这与InnoDB组织数据的方式有关。在InnoDB中，主键（Primary?Key）是集群的，每一个辅助索引（Secondary?Key）都会为主键增加一个入口指针。如果你在设计模式时没有考虑到这一点，那么性能将受到负面影响。

数据是使用B树索引存储的，因此以有序的方式插入数据（即使用准序列值）可以防止主键碎片化，从而减少寻找叶节点所需的I/O操作。

在一些用例中，顺序主键不是正确的选择--这里的一个很好的例子是通用唯一标识符或UUID。

MySQL性能提示2：辅助索引（Secondary?Key）不是你的敌人

辅助索引（Secondary?Key）是由一个后台进程更新的。因此，对性能的影响并不像你所期望的那样严重。相反，问题是围绕着磁盘占用的，因为增加辅助索引（Secondary?Key）会增加存储需求。

对一个没有索引的字段进行过滤，可能会导致每次查询运行时都要进行全表扫描。当然，这可能会导致巨大的性能影响。因此，有一个辅助索引（Secondary?Key）比没有要好。

也就是说，不应该过度添加索引，因为增加过多的索引可能不会实现性能改进。同时，这些额外的索引可能会增加你的存储成本，而且InnoDB必须执行许多后台操作来保持索引的更新。

MySQL性能提示3：行可以从索引中获得服务

InnoDB可以直接从索引中找到并实际服务于行记录，而辅助索引（Secondary?Key）则指向主键，主键则包含行记录本身。如果InnoDB缓冲池足够大，它也可以在内存中容纳大多数数据。你甚至可以使用复合键，这对查询来说通常比单独的每列键更有效。MySQL可以在每个表的访问中使用一个索引，所以如果你正在运行带有WHERE x=1和y=2这样的子句的查询，那么在x,y上建立联合索引比在每个列上有单独的索引要好。

此外，对x,y的联合索引也可以提高以下查询的性能。

SELECT y FROM table WHERE x=1

MySQL将使用覆盖索引，并从内存中的索引中提供Y。

在实践中，当你有机会的时候，你可以通过使用联合索引来提高性能。无论何时，当你设计联合索引时，可以通过从左到右的方式读取索引，所以给定一个这样的查询。

SELECT a,b,c FROM table WHERE a=1 and b=2

那么，一个关于a,b的联合索引将有助于查询。但是如果查询是下面这个格式。

SELECT a,b,c FROM table WHERE b=2

那么，这个a，b的联合索引就是无效的，因为违反的最左原则，也就是从左往右读取索引，因此会导致全表扫描。总是从左边读取索引的想法也适用于其他一些情况。例如，给定以下查询。

SELECT a,b,c FROM table WHERE a=1 and c=2

那么在a,b,c上的联合索引将只读取第一列，因为没有通过列b过滤的WHERE子句。所以在这种情况下，MySQL可以部分地读取索引，这比全表扫描要好，但仍然不足以获得查询的最佳性能。

另一个与查询设计有关的元素是最左边的索引方法，因为这是MySQL中常用的优化。例如，一个关于a,b,c的索引将不包括像select a,c where c=x这样的查询，因为该查询不能跳过索引的第一部分，即a,b。然而，如果你有一个类似select c,count(c) where a=x and b=y group by c的查询，它对a,b进行过滤并对c进行分组，那么a,b,c上的一个索引可以帮助进行过滤和分组。

MySQL性能提示4 :审查与回顾

仅仅拥有一辆一级方程式赛车并不能赢得比赛。如果你把一个没有经验的司机放在方向盘后面，而他们在第一个弯道就撞车了，那就不会赢。同样地，你可能有地球上调整得最好的MySQL服务器，但如果你有糟糕的查询，你的数据库就会比它应该的慢。

你应该随着时间的推移定期审查你的查询设计，因为你的应用程序会随着新功能和错误的修复而改变。应用程序的数据集和使用模式也可能随着时间的推移而改变，所有这些都会影响查询的性能。

留出时间进行查询审查和监控查询，执行时间是非常重要的。你可以为此使用慢速查询日志或性能模式，但实施监控工具将帮助你获得更好的数据。

请记住，并不总是最慢的查询才是最需要解决的问题。例如，你可能有一个耗时30秒但每天运行两次的查询，与一个耗时1秒但每分钟运行100次的查询并存。为了取得大的胜利，你应该开始优化第二个查询，因为从长远来看，改进这个查询可以节省大量的时间和资源。

MySQL性能提示5：可见性很重要

监测是性能调整的关键因素之一。如果不了解当前的工作负载和模式，就很难给出任何具体的建议。近年来，MySQL改进了对低级别的MySQL/InnoDB指标的暴露，这有助于了解工作负载。

例如，在早期版本中，性能模式是一个瓶颈，有相当大的影响，特别是如果你有许多表。在最近的MySQL版本中，就存在许多变化，如新的数据字典，已经改善了性能，现在的版本可以有很多表，但并不会对性能造成大的影响。

大多数现代监控工具都以某种方式使用性能模式，所以一个很好的建议是查看这些工具并选择最适合你的工具。对性能数据的可见性可能是一笔巨大的财富。

MySQL性能提示6：谨慎使用调优工具

调优工具给出的建议在大多数情况下是有效的。然而，每个工作负载和每个模式有所不同。在某些情况下，调优工具的建议并不奏效，在相信这些建议时，谨慎行事是明智的选择。对于MySQL而言，可以对配置进行如下更改。

例如，将innodb_buffer_pool_size设置为总内存的75%是好的经验法则。然而，现在在数百GB的内存服务器的情况下，如果你有512GB的内存，那就会留下128GB的自由空间，而不是专门用于缓冲池，这是一种很大的浪费。

innodb_log_file_size和innodb_log_files_in_group也是根据RAM的数量来定义。在内存超过128GB的服务器上，这个设置没有什么意义，因为它将创建64个重做日志文件（Redo?log），每个2GB。这将导致128GB的重做日志（Redo?log）存储在磁盘上。在大多数情况下，不需要大的重做日志文件（Redo?log），即使在最繁忙的环境中。因此，这并不是一个好的建议。

innodb_flushing_method是启用自动配置时唯一正确配置的值。这个变量将flushing 方法设置为O_DIRECT_NO_FSYNC，这是使用Ext4或XFS文件系统时推荐的方法，因为它避免了数据的双重缓冲。

一个好的建议是，在专用服务器上将innodb_buffer_pool_size设置为75%或80%。在拥有大量内存的服务器上，即超过128GB的服务器，在对内存消耗进行适当的分析后，将其增加到90%甚至更多。同样，对于innodb_log_file_size和innodb_log_files_in_group ?来说大多数情况下，从2GB的文件开始，监测写日志操作。通常情况下，在确定重做日志（Redo?log）的大小时，建议覆盖大约一个小时的写入量。

关于innodb_flush_method，对于Ext4或XFS等现代Linux文件系统，这个选项应该被设置为O_DIRECT或O_DIRECT_NO_FSYNC。

MySQL性能提示7：I/O操作仍然昂贵

MySQL和InnoDB试图最小化它们进行的I/O操作的数量，因为访问存储层在应用性能方面是昂贵的。有一些设置可以影响InnoDB执行的I/O操作的数量。其中有两个设置经常被误解，而改变它们往往会导致性能问题。

innodb_io_capacity和innodb_io_capacity_max是与后台Flushing的I/O操作数量有关的变量。许多客户增加这些设置的值，以利用现代固态硬盘的优势，它可以在相对较低的延迟下提供非常高的I/O容量。虽然这个想法看上去很合理，但增加I/O容量设置会导致一些问题。

第一个问题是通过使InnoDB过快地刷新脏页而导致性能下降，从而减少了“被刷新前多次修改一个页面的机会”。将脏页保留在内存中可以大大减少将数据写入存储的I/O操作。

其次，固态硬盘在出现性能下降之前有一个预期的写入次数。因此，增加写操作的数量会影响你的固态硬盘的寿命，即使你使用的是高端硬盘。

虽说云主机最近很流行，在云中运行MySQL服务实例也是可行的。然而，云中的服务器往往会有I/O限制，或者会对使用更多的I/O收取更多的费用。通过了解这些限制，你可以仔细配置这些参数，以确保不达到这些限制，并使I/O操作最小化。

提到innodb_lru_scan_depth也很重要，因为这个设置控制了缓冲池LRU页面列表中，页面清洁器线程在多远的位置扫描脏页。如果你有一个大的缓冲池和许多缓冲池实例的重写工作负载，你可以通过减少这个变量来减少I/O的操作。

一个好的建议是保持默认值，除非你知道你需要改变它们。

还值得一提的是，最新的固态硬盘是专门为交易型数据库而优化的。西部数据就是一个例子，该公司寻求专家的帮助，以帮助他们满足正在创建的新一轮应用的要求。

MySQL性能提示8：利用通用表的表达式优势

MySQL 8.0引入了通用表的表达式（CTE），这可以避免创建派生表的嵌套查询。这个功能允许创建一个自定义查询并引用结果，就好像是一个临时表或一个视图一样。不同的是，CTEs可以在一个事务中被多次引用，而不需要明确地创建和删除它们。

鉴于CTEs只被实例化一次，它们在运行多个查询的复杂事务中往往更快。另外，支持CTE递归，可以在SQL语言中轻松创建复杂的结构，如分层模型和系列。

MySQL性能提示9：注意云计算

对于MySQL部署，有许多不同的云选项值得考虑，从在虚拟机中实施MySQL服务器实例，到使用数据库即服务（DBaaS）解决方案，选择的范围很广。

许多这样的服务承诺提供显著的性能提升。在一些简单的用例中，这种做法是可行的。然而，即使是在云端，也必须理解数据库的基本原理，否则成本将大大增加。这种成本增加往往是通过增加更多的硬件来解决问题，而并没有从设计上找问题。

MySQL性能提示10：保持Replication的最新状态

近年来，围绕着MySQL Replication进行了许多改进，在许多情况下，它无法及时同步主服务器写入操作。在最新的MySQL主要版本中，Replication默认是并行的，这意味着多个Replication线程正在运行并试图同时应用事务。

当然，执行效率在很大程度上取决于应用程序写入的工作量，在大多数情况下，并行复制可以帮助复制体跟上写入操作。你可以用replica_parallel_type和replica_parallel_workers这两个变量来控制。使用LOGICAL_CLOCK类型，事务被并行应用，并根据时间戳追踪依赖关系。

总的来说，MySQL是数百万开发者的领先开源数据库，它将继续成为世界范围内创建应用程序的首选平台。通过研究围绕模式设计、索引、调整和I/O的问题，可以极大地提高应用程序的性能。不要忘了，像转移到云端这样的部署方法也会有性能影响，因此要谨慎考虑。

译者介绍

崔皓，51CTO社区编辑，资深架构师，拥有18年的软件开发和架构经验，10年分布式架构经验。

原文标题：??10 more essential MySQL performance tuning tips??，作者：Tibor K?r?cz