技术更新！10个MySQL性能调优技巧

时间：2023-02-22 11:06:54
MySQL是世界上使用最广泛的开源数据库，它在业界的受欢迎程度让其他数据库望尘莫及。它是一个关系型数据库管理系统，多年来依然是应用程序的核心。 在过去几年里，MySQL有一些重要发展。因此，整理更新 10个MySQL性能调优技巧。
  1. 模式设计很重要
  2. 辅助索引（Secondary Key）
  3. 行可以从索引中获得服务
  4. 审查与回顾
  5. 可见性很重要
  6. 谨慎使用调优工具
  7. I/O操作仍然昂贵
  8. 利用通用表的表达式优势
  9. 注意云计算
  10. 保持Replication的最新状态

1、模式设计很重要

模式设计是在数据库中最应该重视的事情之一。自MySQL从5.6版本中转移到InnoDB作为默认存储引擎后，模式设计变得更加重要。

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

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

2、辅助索引（Secondary Key）

辅助索引（Secondary Key）是由一个后台进程更新，对性能影响并不大。主要影响磁盘空间占用，即增加辅助索引会增加存储需求。

对一个没有索引的字段进行过滤，可能会导致每次查询运行时都要进行全表扫描。因此， 过度添加索引不会实现性能改进反而会增加存储成本，而且InnoDB必须执行许多后台操作来保持索引的更新。

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上的一个索引可以帮助进行过滤和分组。

          4、审查与回顾

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

          定期审查执行时间非常重要，使用慢速查询日志或性能模式，或使用监控工具可以快速获取数据。

          当然，并不是最慢的查询才是最需要解决的问题。例如，一个耗时30秒但每天运行两次的查询，与一个耗时1秒但每分钟运行100次的查询并存。真正应该优化的是第二个查询，因为优化这个查询可以节省大量的时间和资源。

          5、可见性很重要

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

          在早期版本中，性能模式是一个瓶颈，有相当大的影响，特别是如果你有许多表。在最近的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。

          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的操作。

          一个好的建议是保持默认值。

          8、利用通用表的表达式优势

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

          在运行多个查询的复杂事务中往往更快。支持CTE递归，可以在SQL语言中轻松创建复杂的结构，如分层模型和系列。

          9、注意云计算

          对于MySQL部署，有许多不同的云选项值得考虑，从在虚拟机中实施MySQL服务器实例，到使用数据库即服务（DBaaS）解决方案，选择的范围很广。 即使是在云端，也必须理解数据库的基本原理，否则成本将大大增加。往往通过增加更多硬件来解决问题，并未从设计上找问题。

          10、保持Replication的最新状态

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

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

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



          原文标题： 10 more essential MySQL performance tuning tips ，作者：Tibor Köröcz
          转载注明出处。


          相关文章