MySQL 8.0 官方文档 第八章 优化（一）—— 优化概述

MySQL 8.0 第八章 优化

本章介绍如何优化MySQL性能并提供示例。优化包括在多个级别上的配置、调优和度量性能。根据您的工作角色（开发人员、DBA或者是两者的结合），您可以在单个SQL语句、整个应用程序、单个数据库服务器或多个联网数据库服务器的级别上进行优化。有时，您可以积极主动地提前规划性能，而有时，您可能会在出现问题后对配置或代码问题进行故障排除。优化CPU和内存使用也可以提高可伸缩性，允许数据库在不降低速度的情况下处理更多的负载。

8.1 优化概述

数据库性能取决于数据库级别上的几个因素，例如表的设计、查询语句的编写和配置参数的设置。这些软件结构会导致在硬件级别上执行CPU和IO操作，您必须将这些操作最小化并尽可能提高效率。在研究数据库性能时，首先要学习软件方面的高级规则和准则，并使用挂钟时间来衡量性能。当您成为专家后，您将学习更多的关于内部发生的事件，并开始测量诸如CPU周期和IO操作之类的事件。

普通用户的目标是在现有的软件和硬件配置下获得最佳的数据库性能。高级用户寻找机会来改进MySQL软件本身，或者开发自己的存储引擎和硬件设备来扩展MySQL的生态系统。

在数据库级别上优化

使数据库应用程序能快速处理的最重要因素是其基本设计：

• 表的结构是否适当？尤其是，字段是否具有正确的数据类型，并且每个表是否有适当的适合业务类型的字段？例如，执行频繁更新的应用程序通常是表很多，每个表的字段较少，而进行大量数据分析的应用程序通常是表较少，每个表的字段很多。

• 是否有适当的索引来提高查询效率？

• 您是否为每个表使用了适当的存储引擎，并利用了您选择的每个存储引擎的优势和特性？特别是，选择事务性存储引擎（如InnoDB）或非事务性的存储引擎（如MyISAM）对于性能和可伸缩性非常重要。

  备注：
  InnoDB是新建表的默认存储引擎。实际上，先进的InnoDB性能特性意味着InnoDB表的性能通常优于更简单的MyISAM表，特别是对于繁忙的数据库。

• 每个表是否使用了适当的行格式？此选择还取决于表使用的存储引擎。特别是，压缩表使用更少的磁盘空间，因此需要更少的磁盘IO来读写数据。压缩功能可使用于InnoDB表的各种工作负荷，也可用于只读的MyISAM表。

• 应用程序是否使用了适当的锁策略？例如，在可能的情况下允许共享访问，以便数据库操作可以并发运行；并在适当的情况下请求独占访问，以便关键操作获得最高优先级。同样，存储引擎的选择也很重要。InnoDB存储引擎可以在不需要您的参与下处理大部分的锁问题，从而可以在数据库中实现更好的并发性，并减少代码的实验和调优量。

• 所有用于缓存的内存区域大小是否正确？也就是说，大到足以容纳频繁访问的数据，但不能大到使物理内存过载并导致分页。要配置的主内存区域是InnoDB缓冲池、MyISAM的键缓存和MySQL查询缓存。

在硬件级别上的优化

随着数据库变得越来越繁忙，任何数据库应用程序最终都会遇到硬件限制。DBA必须评估是否有可能调整应用程序或重新配置服务器以避免这些瓶颈，或者是否需要更多的硬件资源。系统瓶颈通常来自以下来源：

• 磁盘查找。从磁盘上查找一小段数据需要时间。对于现代磁盘，查找数据的平均时间通常低于10毫秒，因此理论上我们可以每秒搜索100次。这一时间随着新的磁盘出现缓慢提高，并且很难针对单个盘进行优化。优化寻道时间的方法是将数据分发到多个磁盘上。
• 磁盘读写。当磁盘位于正确的位置时，我们需要读取或写入数据。对于现代磁盘，一个磁盘的吞吐量至少为10~20MB/s。这比查找更容易优化，因为您可以并行读取多个磁盘。
• CPU周期。当数据在主内存中时，我们必须对它进行处理才能得到结果。与内存大小相比，大表的数量太多是最常见的限制因素。但是对于小表而言，速度通常不是问题。
• 内存带宽。当CPU需要的数据超过CPU缓存的容量时，主存带宽就成了瓶颈。对于大多数系统来说，这是一个不常见的瓶颈，但需要注意。

平衡可移植性和性能

要在可移植的MySQL程序中使用面向性能的SQL扩展，您可以使用/*! * /注释分隔符将MySQL特定的关键字进行包裹。其他SQL服务器忽略注释后的关键字。有关写注释的信息，请参见9.7节“注释”。

下一章 MySQL 8.0 官方文档 第八章 优化（二）——优化 SELECT 语句