MySQL优化篇(一)：MySQL优化概述

MySQL优化篇系列介绍 MySQL 性能的优化并提供示例。优化涉及在多个级别上配置、调优和测量性能。

根据个人所在的工作角色（开发人员、DBA 或两者的组合），可以在单个 SQL 语句、整个应用程序、单个数据库服务器或多个联网数据库服务器的级别进行优化。

有时，可以主动并提前计划性能，而有时可能会在出现问题后对配置或代码问题进行故障排除。

优化 CPU 和内存使用还可以提高可扩展性，允许数据库处理更多负载而不会减慢速度。

概述

数据库性能取决于数据库级别的多个因素，例如表、查询和配置设置。这些软件结构会导致硬件级别的 CPU 和 I/O 操作，必须将其最小化并尽可能提高效率。

在研究数据库性能时，首先要学习软件方面的高级规则和准则，并使用挂钟时间来衡量性能。

当您成为专家时，您会更多地了解关于内部发生的事情，并开始测量诸如 CPU 周期和 I/O 操作之类的事情。

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

数据库级别优化

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

• 表格的结构是否正确？ 特别是，列是否具有正确的数据类型，每个表是否具有适合工作类型的列？

  例如，执行频繁更新的应用程序通常有很多列很少的表，而分析大量数据的应用程序通常只有很少的列有很多表。

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

• 是否为每个表使用了合适的存储引擎，并利用了所使用的每个存储引擎的优势和特性？

  特别是，选择事务性存储引擎（如 InnoDB）或非事务性存储引擎（如 MyISAM）对于性能和可伸缩性非常重要。

  NOTE
  InnoDB 是新表的默认存储引擎。 在实践中，先进的 InnoDB 性能特性意味着 InnoDB 表通常优于更简单的 MyISAM 表，尤其是对于繁忙的数据库。

• 每个表是否使用适当的行格式？此选择还取决于用于表的存储引擎。

  特别是，压缩表使用较少的磁盘空间，因此需要较少的磁盘 I/O 来读取和写入数据。压缩可用于包含 InnoDB 表的所有类型的工作负载，以及只读的 MyISAM 表。

• 应用程序是否使用了适当的锁定策略？

  例如，在可能的情况下允许共享访问，以便数据库操作可以并发运行，并在适当的时候请求独占访问，以便关键操作获得最高优先级。

  同样，存储引擎的选择很重要。 InnoDB 存储引擎无需您参与即可处理大多数锁定问题，从而提高数据库中的并发性并减少对代码的实验和调整量。

• 用于缓存的所有内存区域的大小是否正确？

  也就是说，大到足以容纳经常访问的数据，但又不能大到使物理内存过载并导致分页。要配置的主要内存区域是 InnoDB 缓冲池和 MyISAM 密钥缓存。

硬件级别优化

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

• 磁盘查找（寻道）

  磁盘找到一条数据需要时间。使用现代磁盘，平均时间通常低于 10 毫秒，因此理论上我们每秒可以执行大约 100 次寻道。这个时间随着新磁盘的增加而缓慢改善，并且很难针对单个表进行优化。优化寻道时间的方法是将数据分布到多个磁盘上。

• 磁盘读写

  当磁盘处于正确位置时，我们需要读取或写入数据。使用现代磁盘，一个磁盘可提供至少 10–20MB/s 的吞吐量。这比寻道更容易优化，因为可以从多个磁盘并行读取。

• CPU 周期

  当数据在主存中时，我们必须对其进行处理以获得我们的结果。与内存量相比，拥有大表是最常见的限制因素。但是对于小表，速度通常不是问题。

• 内存带宽

  当 CPU 需要的数据超过 CPU 缓存的容量时，主内存带宽就成为瓶颈。对于大多数系统来说，这是一个不常见的瓶颈，但需要注意。

平衡可移植性和性能

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