译者|崔浩评论|孙淑娟Schema设计、索引、查询、配置、I/O……还是出错？遵循这10条提示以确保您的MySQL服务器正常运行。MySQL是世界上使用最广泛的开源数据库，其在业界的流行度令其他数据库望尘莫及。它是一个关系数据库管理系统，多年来一直是流行应用程序的核心。但是，使用起来可能具有挑战性，因此有很多提高性能的机会。在过去几年中，MySQL也有一些重要的新发展。本文更新了BaronSchwartz之前提供的一组MySQL性能调优技巧。这里有10个MySQL性能调优技巧。目录MySQL性能技巧1：模式设计与任何其他MySQL设置一样重要MySQL性能技巧2：二级索引(SecondaryKeys)不是你的敌人MySQL性能技巧3：可以从索引提供行MySQL性能技巧4：回顾ReviewwithMySQL性能技巧5：可见性很重要MySQL性能技巧6：谨慎使用调优工具MySQL性能技巧7：I/O操作仍然很昂贵MySQL性能技巧8：利用通用表的表达式MySQL性能技巧9：关注云计算MySQL性能技巧10：使复制保持最新MySQL性能技巧1：模式设计与任何其他MySQL设置一样重要模式设计是数据库中最重要的事情之一。早在1970年代就引入了这种数据库设计原则。自从MySQL在5.6版本中将InnoDB作为默认存储引擎，模式设计变得更加重要。为什么会这样？在InnoDB中，一切都是主键！这与InnoDB组织数据的方式有关。在InnoDB中，主键（PrimaryKey）是聚簇的，每个二级索引（SecondaryKey）都会添加一个指向主键的入口指针。如果您在设计架构时没有考虑到这一点，性能将会受到负面影响。数据使用B树索引存储，因此以有序方式插入数据（即使用准顺序值）可防止主键碎片，从而减少查找叶节点所需的I/O操作。在某些用例中，顺序主键不是正确的选择——这里的一个很好的例子是通用唯一标识符或UUID。MySQL性能技巧2：二级索引（SecondaryKey）不是你的敌人二级索引（SecondaryKey）是由后台进程更新的。因此，性能损失并不像您预期??的那样严重。相反，问题围绕着磁盘使用，因为添加辅助密钥(SecondaryKey)会增加存储要求。对未建立索引的列进行过滤会导致每次运行查询时都进行全表扫描。当然，这会导致巨大的性能损失。因此，有二级索引（SecondaryKey）总比没有好。也就是说，您不应该过度添加索引，因为添加太多索引可能无法实现性能提升。同时，这些额外的索引可能会增加你的存储成本，InnoDB必须执行许多后台操作来保持索引更新。MySQL性能技巧3：可以从索引提供行InnoDB可以直接从索引找到并实际提供行记录，而二级索引(SecondaryKey)指向包含行记录本身的主键。如果InnoDB缓冲池足够大，它也可以在内存中容纳大部分数据。您甚至可以使用组合键，这对于查询通常比单独的每列键更有效。MySQL可以为每个表访问使用一个索引，因此如果您使用WHEREx=1和y=2之类的子句运行查询，那么在x,y上构建联合索引比在每一列上构建联合索引要好最好有一个单独的索引。另外，在x,y上建立联合索引还可以提高后面查询的性能。SELECTyFROMtableWHEREx=1MySQL将使用覆盖索引并从内存中的索引提供Y。实际上，如果有机会，您可以通过使用连接索引来提高性能。每当你设计一个连接索引时，你都可以从左到右读取索引，所以给出这样的查询。SELECTa,b,cFROMtableWHEREa=1andb=2然后，a,b上的联合索引将有助于查询。但是如果查询是下面的格式。SELECTa,b,cFROMtableWHEREb=2那么a和b的联合索引是无效的，因为违反了最左原则，即从左到右读取索引，会造成全表扫描.总是从左边读取索引的想法也适用于其他一些情况。例如，给定以下查询。SELECTa,b,cFROMtableWHEREa=1andc=2那么a,b,c上的连接索引将只读取第一列，因为没有按b列过滤的WHERE子句。所以在这种情况下，MySQL可以部分读取索引，这比全表扫描要好，但仍然不足以获得最佳的查询性能。与查询设计相关的另一个元素是最左索引方法，因为这是MySQL中常用的优化。例如，a,b,c上的索引将不包括像selecta,cwherec=x这样的查询，因为查询不能跳过索引的第一部分a,b。然而，如果你有一个像selectc,count(c)wherea=xandb=ygroupbyc这样的查询，它过滤a,b和groupsbyc，那么一个关于a,b,c的索引可以帮助过滤和分组。MySQL性能技巧4：回顾和回顾仅仅拥有一辆一级方程式赛车并不能赢得比赛。如果你让一个没有经验的司机坐在方向盘后面，他们在第一个转弯就撞车了，那它就赢不了。同样，您可能拥有地球上调优最好的MySQL服务器，但如果您有错误的查询，您的数据库将比应有的速度慢。随着时间的推移，您应该定期检查您的查询设计，因为您的应用程序随着新功能和错误修复而变化。应用程序的数据集和使用模式也可能随时间发生变化，所有这些都会影响查询性能。留出时间进行查询审查和监控查询执行时间非常重要。您可以为此使用慢速查询日志或性能模式，但实施监控工具将帮助您获得更好的数据。请记住，最需要解决的并不总是最慢的查询。例如，您可能有一个耗时30秒但每天运行两次的查询，以及一个耗时1秒但每分钟运行100次的查询。为了大获全胜，您应该开始优化第二个查询，因为从长远来看，改进此查询可以节省大量时间和资源。MySQL性能技巧五：可见性很重要监控是性能调优的关键因素之一。在不了解您当前的工作负载和模式的情况下，很难提出任何具体建议。近年来，MySQL改进了对低级MySQL/InnoDB指标的接触，这有助于了解工作负载。例如，在早期版本中，性能模式是一个具有相当大影响的瓶颈，尤其是当您有很多表时。在最近的MySQL版本中，有很多变化，比如新的数据字典，提高了性能。当前版本可以有很多表，但是对性能不会有太大的影响。大多数现代监控工具都以某种方式使用PerformanceSchema，因此一个好的建议是查看这些工具并选择最适合你的工具。性能数据的可见性可能是一笔巨大的财富。MySQL性能技巧No.6：谨慎使用调优工具调优工具给出的建议在大多数情况下都是有效的。但是，每种工作负载和每种模式都是不同的。在某些情况下，调整工具的建议不起作用，在依赖这些建议时谨慎行事是明智的。对于MySQL，可以进行以下配置更改。例如，将innodb_buffer_pool_size设置为总内存的75%是一个很好的经验法则。但是，现在对于具有数百GB内存的服务器来说，如果您有512GB的内存，那么还有128GB的??可用空间没有专门用于缓冲池，这是一个很大的浪费。innodb_log_file_size和innodb_log_files_in_group也是根据RAM量定义的。在内存超过128GB的服务器上，这个设置没有意义，因为它会创建64个重做日志文件（Redolog），每个2GB。这将导致在磁盘上存储128GB的??重做日志(Redolog)。在大多数情况下，不需要大型重做日志文件(Redolog)，即使在最繁忙的环境中也是如此。因此，这不是一个好的建议。innodb_flushing_method是启用自动配置时唯一正确配置的值。该变量将刷新方法设置为O_DIRECT_NO_FSYNC，这是使用Ext4或XFS文件系统时推荐的方法，因为它避免了数据的双缓冲。一个好的建议是在专用服务器上将innodb_buffer_pool_size设置为75%或80%。在内存较大的服务器上，即超过128GB，在适当分析内存消耗后，将其增加到90%或更多。同样，对于innodb_log_file_size和innodb_log_files_in_group，大多数情况下，从2GB文件开始，监控写日志操作。通常，在确定重做日志（Redolog）的大小时，建议覆盖大约一小时的写入量。关于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操作数相关的变量。许多客户增加这些设置的值以利用现代SSD，它可以以相对较低的延迟提供非常高的I/O容量。虽然这个想法听起来不错，但增加I/O容量设置可能会导致问题。第一个问题是性能下降，因为InnoDB刷新脏页的速度太快，减少了“页面在刷新前被多次修改的可能性”。将脏页保留在内存中可以大大减少将数据写入存储所需的I/O操作。其次，SSD在性能下降之前具有预期的写入次数。因此，增加写入操作次数会影响SSD的使用寿命，即使您使用高端驱动器也是如此。虽然现在云托管很流行，但是也可以在云端运行MySQL服务实例。但是，云中的服务器往往受I/O限制，或者对使用更多I/O收费更高。通过了解这些限制，您可以仔细配置这些参数，以确保不会达到这些限制并最大限度地减少I/O操作。提及innodb_lru_scan_depth也很重要，因为此设置控制页面清理器线程扫描脏页进入缓冲池LRU页面列表的深度。如果您有一个大型缓冲池和具有许多缓冲池实例的重写工作负载，您可以通过减少此变量来减少I/O操作。一个好的建议是保留默认值，除非您知道需要更改它们。还值得一提的是，最新的SSD针对交易数据库进行了优化。一个例子是WesternDigital，它寻求专家的帮助，以帮助他们满足他们正在创建的新一波应用程序的要求。MySQL性能提示#8：利用公用表表达式MySQL8.0引入了公用表表达式(CTE)，它避免了创建派生表的嵌套查询。此功能允许创建自定义查询并引用结果，就好像它是临时表或视图一样。不同之处在于CTE可以在事务中多次引用，而无需显式创建和删除它们。鉴于CTE仅实例化一次，它们在运行多个查询的复杂事务中往往更快。此外，还支持CTE递归，可以轻松创建SQL语言中层次模型、系列等复杂结构。MySQL性能提示#9：小心云计算对于MySQL部署，有许多不同的云选项需要考虑，从在虚拟机中实施MySQL服务器实例到使用数据库即服务(DBaaS)解决方案，选项范围很广。其中许多服务有望显着提高性能。在一些简单的用例中，这种方法是有效的。然而，即使在云中，也必须了解数据库基础知识，否则成本会大大增加。这种成本增加往往通过增加更多的硬件来解决，而不是从设计中寻找问题。MySQLPerformanceTip#10:KeepReplicationUp-to-Date近年来，围绕MySQLReplication进行了许多改进，但在很多情况下，它无法及时同步主服务器的写操作。在最新的MySQL主要版本中，复制默认是并行的，这意味着多个复制线程正在运行并尝试并发应用事务。当然，执行效率高度依赖于应用程序的写入工作量，在大多数情况下，并行复制可以帮助副本跟上写入操作。可以使用replica_parallel_type和replica_parallel_workers这两个变量来控制。使用LOGICAL_CLOCK类型，并行应用事务并根据时间戳跟踪依赖关系。总的来说，MySQL是数百万开发人员的领先开源数据库，它将继续成为全球范围内创建应用程序的首选平台。通过调查围绕模式设计、索引、调整和I/O的问题，可以极大地提高应用程序性能。不要忘记，像迁移到云这样的部署方法也会对性能产生影响，因此请仔细考虑。译者介绍崔浩，社区编辑，资深架构师。他拥有18年的软件开发和架构经验，以及10年的分布式架构经验。原标题：10个更必备的MySQL性能调优技巧，作者：TiborK?r?cz
