Monodyee

这个屌丝很懒，什么也没留下！

热门标签

高性能 MySQL 第四版（GPT 重译）（二）

作者：Monodyee | 2024-05-21 08:16:06

踩

第四章：操作系统和硬件优化

你的 MySQL 服务器的性能只能和它最弱的环节一样好，而运行 MySQL 的操作系统和硬件通常是限制因素。磁盘大小、可用内存和 CPU 资源、网络以及连接它们的所有组件都限制了系统的最终容量。因此，你需要仔细选择硬件，并适当配置硬件和操作系统。例如，如果你的工作负载受到 I/O 限制，一种方法是设计你的应用程序以最小化 MySQL 的 I/O 工作负载。然而，升级 I/O 子系统、安装更多内存或重新配置现有磁盘通常更明智。如果你在云托管环境中运行，本章的信息仍然非常有用，特别是为了了解文件系统限制和 Linux I/O 调度程序。

什么限制了 MySQL 的性能？

许多不同的硬件组件可以影响 MySQL 的性能，但我们经常看到的最常见的瓶颈是 CPU 耗尽。当 MySQL 尝试并行执行太多查询或较少数量的查询在 CPU 上运行时间过长时，CPU 饱和就会发生。

I/O 饱和仍然可能发生，但发生频率要比 CPU 耗尽低得多。这在很大程度上是因为过渡到使用固态硬盘（SSD）。从历史上看，不再在内存中工作而转向硬盘驱动器（HDD）的性能惩罚是极端的。SSD 通常比 SSH 快 10 到 20 倍。如今，如果查询需要访问磁盘，你仍然会看到它们的性能不错。

内存耗尽仍然可能发生，但通常只会在尝试为 MySQL 分配过多内存时发生。我们在“配置内存使用”中讨论了防止这种情况发生的最佳配置设置，在第五章中。

如何为 MySQL 选择 CPU

当升级当前硬件或购买新硬件时，你应该考虑你的工作负载是否受 CPU 限制。你可以通过检查 CPU 利用率来确定工作负载是否受 CPU 限制，但不要只看整体 CPU 负载有多重，而是要看你最重要的查询的 CPU 使用率和 I/O 的平衡，并注意 CPU 是否均匀负载。

广义上说，你的服务器有两个目标：

低延迟（快速响应时间）

要实现这一点，你需要快速的 CPU，因为每个查询只会使用一个 CPU。

高吞吐量

如果你可以同时运行多个查询，你可能会从多个 CPU 为查询提供服务中受益。

如果你的工作负载没有利用所有的 CPU，MySQL 仍然可以利用额外的 CPU 执行后台任务，如清理 InnoDB 缓冲区、网络操作等。然而，与执行查询相比，这些工作通常较小。

平衡内存和磁盘资源

拥有大量内存的主要原因并不是为了能够在内存中保存大量数据：最终目的是为了避免磁盘 I/O，因为磁盘 I/O 比在内存中访问数据慢几个数量级。关键是平衡内存和磁盘大小、速度、成本和其他特性，以便为你的工作负载获得良好的性能。

缓存、读取和写入

如果你有足够的内存，你可以完全隔离磁盘免受读取请求。如果所有数据都适合内存，一旦服务器的缓存被热起来，每次读取都会是缓存命中。仍然会有来自内存的逻辑读取，但没有来自磁盘的物理读取。然而，写入是另一回事。写入可以像读取一样在内存中执行，但迟早它必须写入磁盘以便永久保存。换句话说，缓存可以延迟写入，但不能像读取那样消除写入。

实际上，除了允许延迟写入外，缓存还可以以两种重要的方式将它们分组在一起：

多写一次刷新

一条数据可以在内存中多次更改，而不需要将所有新值都写入磁盘。当数据最终刷新到磁盘时，自上次物理写入以来发生的所有修改都是永久的。例如，许多语句可以更新一个内存中的计数器。如果计数器递增了一百次然后写入磁盘，一百次修改已经被合并为一次写入。

I/O 合并

许多不同的数据可以在内存中被修改，并且修改可以被收集在一起，以便可以将物理写入作为单个磁盘操作执行。

这就是为什么许多事务系统使用预写式日志策略。预写式日志允许它们在内存中对页面进行更改而不刷新更改到磁盘，这通常涉及随机 I/O 并且非常慢。相反，它们将更改的记录写入顺序日志文件，这样做要快得多。后台线程可以稍后将修改的页面刷新到磁盘；当它这样做时，它可以优化写入。

写入受益于缓冲，因为它将随机 I/O 转换为更多的顺序 I/O。异步（缓冲）写入通常由操作系统处理，并且会被批处理，以便更优化地刷新到磁盘。同步（非缓冲）写入必须在完成之前写入磁盘。这就是为什么它们受益于在冗余磁盘阵列（RAID）控制器的电池支持写回缓存中进行缓冲（我们稍后会讨论 RAID）。

你的工作集是什么？

每个应用程序都有一个“工作集”数据，即它真正需要完成工作的数据。许多数据库还有很多不在工作集中的数据。你可以把数据库想象成一个带有文件抽屉的办公桌。工作集包括你需要放在桌面上以完成工作的文件。在这个类比中，桌面代表主内存，而文件抽屉代表硬盘。就像你不需要把每一张纸都放在桌面上才能完成工作一样，你不需要整个数据库都适合内存以获得最佳性能——只需要工作集。

在处理 HDD 时，寻找有效的内存到磁盘比例是一个好的做法。这在很大程度上是由于 HDD 的较慢的延迟和低的每秒输入/输出操作数（IOPS）。使用 SSD 时，内存到磁盘比例变得不那么重要。

固态存储

固态（闪存）存储是大多数数据库系统的标准，特别是在线事务处理（OLTP）。只有在非常大的数据仓库或传统系统中才会通常找到 HDD。这种转变是因为 2015 年左右 SSD 的价格显著下降。

固态存储设备使用由单元组成的非易失性闪存存储芯片，而不是磁盘盘片。它们也被称为非易失性随机存取存储器（NVRAM）。它们没有移动部件，这使它们的行为与硬盘非常不同。

以下是闪存性能的简要总结。高质量的闪存设备具有：

与硬盘相比，随机读写性能要好得多

闪存设备通常在读取方面比写入更好。

比硬盘更好的顺序读写性能

然而，与随机 I/O 相比，并没有那么显著的改进，因为硬盘在随机 I/O 方面比顺序 I/O 慢得多。

比硬盘更好的并发性支持

闪存设备可以支持更多的并发操作，事实上，只有在有很多并发时它们才能真正达到最高吞吐量。

最重要的是随机 I/O 和并发性能的改进。闪存给您提供了在高并发情况下非常好的随机 I/O 性能。

闪存存储概述

有旋转盘片和摆动磁头的硬盘具有固有的限制和特性，这些特性是物理学所涉及的结果。固态存储也是如此，它是建立在闪存之上的。不要以为固态存储很简单。在某些方面，它实际上比硬盘更复杂。闪存的限制相当严重且难以克服，因此典型的固态设备具有复杂的架构，包含许多抽象、缓存和专有的“魔法”。

闪存的最重要特性是它可以快速多次读取小单位，但写入要困难得多。一个单元不能在没有特殊擦除操作的情况下重写，并且只能在大块中擦除，例如 512 KB。擦除周期很慢，最终会使块磨损。一个块可以容忍的擦除周期数量取决于它使用的基础技术——稍后会详细介绍。

写入的限制是固态存储复杂性的原因。这就是为什么一些设备提供稳定、一致的性能，而其他设备则不提供。这些“魔法”都在专有的固件、驱动程序和其他组件中，使固态设备运行起来。为了使写入操作性能良好并避免过早磨损闪存块，设备必须能够重新定位页面并执行垃圾回收和所谓的磨损均衡。术语写入放大用于描述由于将数据从一个地方移动到另一个地方而导致的额外写入，由于部分块写入而多次写入数据和元数据。

垃圾回收

垃圾回收是很重要的。为了保持一些块的新鲜度并为新的写入做好准备，设备会回收块。这需要设备上的一些空闲空间。设备要么会有一些您看不到的内部保留空间，要么您需要通过不完全填满设备来自行保留空间；这因设备而异。无论哪种方式，随着设备填满，垃圾收集器必须更加努力地保持一些块的清洁，因此写入放大因子会增加。

因此，许多设备在填满时会变慢。每个供应商和型号的减速程度各不相同，这取决于设备的架构。一些设备即使在相当满时也设计为高性能，但总的来说，100 GB 文件在 160 GB SSD 上的表现与在 320 GB SSD 上的表现不同。减速是由于在没有空闲块时必须等待擦除完成。写入到空闲块需要几百微秒，但擦除速度要慢得多——通常是几毫秒。

RAID 性能优化

存储引擎通常将它们的数据和/或索引保存在单个大文件中，这意味着 RAID 通常是存储大量数据的最可行选项。RAID 可以帮助提高冗余性、存储容量、缓存和速度。但与我们一直在研究的其他优化一样，RAID 配置有许多变体，选择适合您需求的配置非常重要。

我们不会在这里涵盖每个 RAID 级别，也不会详细介绍不同 RAID 级别如何存储数据的具体细节。相反，我们专注于 RAID 配置如何满足数据库服务器的需求。以下是最重要的 RAID 级别：

RAID 0

RAID 0 是最便宜且性能最高的 RAID 配置，至少在您简单地衡量成本和性能时是这样（例如，如果包括数据恢复，它开始看起来更昂贵）。由于它不提供冗余性，我们认为 RAID 0 在生产数据库上永远不合适，但如果您真的想要节省成本，它可以是开发环境中的选择，其中完整服务器故障不会变成事故。

再次注意，RAID 0 不提供任何冗余性，尽管“冗余”是 RAID 首字母缩略词中的 R。事实上，RAID 0 阵列失败的概率实际上高于任何单个磁盘失败的概率，而不是低于！

RAID 1

RAID 1 对于许多场景提供了良好的读取性能，并且它会在磁盘之间复制您的数据，因此具有良好的冗余性。对于读取来说，RAID 1 比 RAID 0 稍快一点。它适用于处理日志和类似工作负载的服务器，因为顺序写入很少需要许多底层磁盘才能表现良好（与随机写入相反，后者可以从并行化中受益）。对于需要冗余但只有两个硬盘的低端服务器来说，这也是一个典型选择。

RAID 0 和 RAID 1 非常简单，通常可以很好地在软件中实现。大多数操作系统都可以让您轻松创建软件 RAID 0 和 RAID 1 卷。

RAID 5

RAID 5 曾经对数据库系统来说是相当可怕的，主要是由于性能影响。随着 SSD 变得普遍，现在它是一个可行的选择。它将数据分布在许多磁盘上，并使用分布式奇偶校验块，因此如果任何一个磁盘故障，数据可以从奇偶校验块重建。如果两个磁盘故障，整个卷将无法恢复。从每单位存储空间的成本来看，这是最经济的冗余配置，因为整个阵列只损失一个磁盘的存储空间。

RAID 5 最大的“坑”是如果一个磁盘故障时阵列的性能如何。这是因为数据必须通过读取所有其他磁盘来重建。这在 HDD 上严重影响了性能，这就是为什么通常不鼓励使用。如果您有很多磁盘，情况会更糟。如果您尝试在重建过程中保持服务器在线，不要指望重建或阵列的性能会很好。其他性能成本包括由于奇偶校验块的限制而导致的有限可扩展性——RAID 5 在超过 10 个磁盘左右时性能不佳——以及缓存问题。良好的 RAID 5 性能严重依赖于 RAID 控制器的缓存，这可能会与数据库服务器的需求发生冲突。正如我们之前提到的，SSD 在 IOPS 和吞吐量方面提供了显着改进的性能，而随机读/写性能不佳的问题也消失了。

RAID 5 的一个缓解因素是它非常受欢迎。因此，RAID 控制器通常针对 RAID 5 进行了高度优化，尽管存在理论限制，但使用缓存良好的智能控制器有时可以在某些工作负载下表现得几乎与 RAID 10 控制器一样好。这实际上可能反映出 RAID 10 控制器的优化程度较低，但无论原因是什么，这就是我们看到的情况。

RAID 6

RAID 5 的最大问题是丢失两个磁盘将是灾难性的。阵列中的磁盘越多，磁盘故障的概率就越高。RAID 6 通过添加第二个奇偶校验磁盘来帮助遏制故障可能性。这使您可以承受两个磁盘故障并仍然重建阵列。不足之处在于计算额外的奇偶校验会使写入速度比 RAID 5 慢。

RAID 10

RAID 10 对于数据存储是一个非常好的选择。它由镜像对组成，这些镜像对是条带化的，因此它既能很好地扩展读取又能扩展写入。与 RAID 5 相比，它重建速度快且容易。它也可以在软件中实现得相当好。

当一个硬盘故障时，性能损失仍然可能很显著，因为该条带可能成为瓶颈。根据工作负载的不同，性能可能会降低高达 50%。要注意的一件事是，某些 RAID 控制器使用“串联镜像”实现 RAID 10。这是次优的，因为缺乏条带化：您最常访问的数据可能只放在一对磁盘上，而不是分布在许多磁盘上，因此性能会很差。

RAID 50

RAID 50 由条带化的 RAID 5 阵列组成，如果你有很多硬盘，它可以在 RAID 5 的经济性和 RAID 10 的性能之间取得很好的折衷。这主要适用于非常大的数据集，比如数据仓库或极大型的 OLTP 系统。

表 4-1 总结了各种 RAID 配置。

表 4-1. RAID 级别比较

级别	摘要	冗余性	所需硬盘	更快读取	更快写入
RAID 0	便宜，快速，危险	否	N	是	是
RAID 1	快速读取，简单，安全	是	2（通常）	是	否
RAID 5	便宜，与 SSD 一起快速	是	N + 1	是	取决于
RAID 6	类似于 RAID 5 但更具弹性	是	N + 2	是	取决于
RAID 10	昂贵，快速，安全	是	2N	是	是
RAID 50	用于非常大型数据存储	是	2(N + 1)	是	是

RAID 故障、恢复和监控

RAID 配置（除了 RAID 0）提供冗余性。这很重要，但很容易低估同时硬盘故障的可能性。你不应该认为 RAID 是数据安全的强有力保证。

RAID 并不能消除——甚至不能减少——备份的需求。当出现问题时，恢复时间将取决于你的控制器、RAID 级别、阵列大小、硬盘速度以及在重建阵列时是否需要保持服务器在线。

硬盘同时发生故障的可能性是存在的。例如，电力波动或过热很容易导致两个或更多硬盘损坏。然而，更常见的是两个硬盘故障发生在较短的时间内。许多这样的问题可能不会被注意到。一个常见的原因是很少访问的物理介质上的损坏，这可能会在几个月内不被发现，直到你尝试读取数据或另一个硬盘故障并且 RAID 控制器尝试使用损坏的数据重建阵列。硬盘越大，这种情况发生的可能性就越大。

这就是为什么监视你的 RAID 阵列很重要。大多数控制器提供一些软件来报告阵列的状态，你需要跟踪这些信息，否则你可能完全不知道硬盘故障。你可能会错过恢复数据的机会，只有当第二个硬盘故障时才发现问题，那时已经太迟了。你应该配置一个监控系统，在硬盘或卷更改为降级或失败状态时通知你。

你可以通过定期主动检查阵列的一致性来减轻潜在损坏的风险。一些控制器的背景巡逻读取功能可以在所有硬盘在线时检查损坏的介质并修复它，也可以帮助避免这些问题。与恢复一样，非常大的阵列可能检查速度较慢，因此在创建大型阵列时一定要做好计划。

你还可以添加一个热备用硬盘，它是未使用的，并配置为控制器自动用于恢复的待机硬盘。如果你依赖每台服务器，这是一个好主意。对于只有少量硬盘的服务器来说，这是昂贵的，因为拥有一个空闲硬盘的成本相对较高，但如果你有很多硬盘，不配置热备用几乎是愚蠢的。请记住，随着硬盘数量的增加，硬盘故障的概率会迅速增加。

除了监视驱动器故障，你还应该监视 RAID 控制器的电池备份单元和写缓存策略。如果电池故障，默认情况下大多数控制器会通过将缓存策略更改为写穿透而不是写回来禁用写缓存。这可能会导致性能严重下降。许多控制器还会定期通过学习过程循环电池，在此期间缓存也被禁用。你的 RAID 控制器管理实用程序应该让你查看和配置学习周期何时安排，以免让你措手不及。新一代的 RAID 控制器通过使用使用 NVRAM 存储未提交写入的闪存支持缓存来避免这种情况，而不是使用电池支持的缓存。这避免了学习周期的整个痛苦。

你可能还想使用写穿透的缓存策略对系统进行基准测试，这样你就会知道可以期待什么。首选的方法是在低流量时段安排电池学习周期，通常在晚上或周末。如果在任何时候使用写穿透时性能严重下降，你也可以在学习周期开始之前切换到另一台服务器。作为最后的手段，你可以通过更改innodb_flush_log_at_trx_commit和sync_binlog变量来重新配置服务器，以降低耐久性设置。这将减少写穿透期间的磁盘利用率，并可能提供可接受的性能；然而，这真的应该作为最后的手段。降低耐久性会对在数据库崩溃期间可能丢失的数据量以及恢复数据的能力产生重大影响。

RAID 配置和缓存

通常可以通过在机器的引导序列期间输入其设置实用程序或通过从命令提示符运行来配置 RAID 控制器本身。尽管大多数控制器提供了许多选项，但我们关注的两个是条带阵列的块大小和控制器缓存（也称为RAID 缓存；我们可以互换使用这些术语）。

RAID 条带块大小

最佳条带块大小是与工作负载和硬件特定的。理论上，对于随机 I/O，拥有较大的块大小是有好处的，因为这意味着更多的读取可以从单个驱动器中满足。

要了解为什么会这样，请考虑你的工作负载的典型随机 I/O 操作的大小。如果块大小至少与该大小相同，并且数据不跨越块之间的边界，只需要一个驱动器参与读取。但是，如果块大小小于要读取的数据量，就无法避免多个驱动器参与读取。

理论就到此为止。实际上，许多 RAID 控制器不适用于大块。例如，控制器可能将块大小用作其缓存中的缓存单元，这可能是浪费的。控制器还可能匹配块大小、缓存大小和读取单元大小（单次操作中读取的数据量）。如果读取单元太大，其缓存可能不太有效，并且可能会读取比实际需要的数据量更多，即使是对于微小的请求。

也很难知道任何给定数据是否会跨越多个驱动器。即使块大小为 16 KB，与 InnoDB 的页面大小相匹配，你也不能确定所有读取是否都对齐在 16 KB 边界上。文件系统可能会使文件碎片化，并且通常会将碎片对齐在文件系统块大小上，通常为 4 KB。一些文件系统可能更智能，但你不应该指望它。

RAID 缓存

RAID 缓存是物理安装在硬件 RAID 控制器上的（相对较小的）内存量。它可用于在数据在磁盘和主机系统之间传输时作为缓冲区。以下是 RAID 卡可能使用缓存的一些原因：

缓存读取

在控制器从磁盘中读取一些数据并将其发送到主机系统后，它可以存储数据；这将使它能够在不再需要再次访问磁盘的情况下满足对相同数据的未来请求。

这通常是 RAID 缓存的非常糟糕的用法。为什么？因为操作系统和数据库服务器有自己更大的缓存。如果其中一个缓存中有缓存命中，RAID 缓存中的数据将不会被使用。反之，如果高级别缓存中有缓存未命中，那么 RAID 缓存中有缓存命中的机会几乎为零。由于 RAID 缓存要小得多，它几乎肯定已经被刷新并填充了其他数据。无论从哪个角度看，将读取缓存到 RAID 缓存中都是一种浪费内存的行为。

缓存预读数据

如果 RAID 控制器注意到对数据的顺序请求，它可能会决定进行预读操作——即预取它预测很快会需要的数据。但在数据被请求之前，它必须有地方存放数据。它可以使用 RAID 缓存来实现这一点。这种操作的性能影响可能会有很大的变化，你应该检查以确保它确实有帮助。如果数据库服务器正在执行自己的智能预读操作（如 InnoDB 所做的），预读操作可能不会有帮助，并且可能会干扰同步写入的重要缓冲。

缓存写入

RAID 控制器可以在其缓存中缓冲写入并安排它们在稍后执行。这样做的优点是双重的：首先，它可以比实际在物理磁盘上执行写入更快地向主机系统返回“成功”，其次，它可以累积写入并更有效地执行它们。

内部操作

一些 RAID 操作非常复杂——特别是 RAID 5 写入，它们必须计算可以用于在发生故障时重建数据的奇偶校验位。控制器需要为这种类型的内部操作使用一些内存。这是 RAID 5 在某些控制器上性能不佳的原因之一：它需要将大量数据读入缓存以获得良好的性能。一些控制器无法平衡缓存写入和 RAID 5 奇偶校验操作的缓存。

一般来说，RAID 控制器的内存是一种稀缺资源，你应该明智地使用它。将其用于读取通常是浪费，但将其用于写入是提高 I/O 性能的重要方式。许多控制器允许你选择如何分配内存。例如，你可以选择将多少内存用于缓存写入，将多少用于读取。对于 RAID 0、RAID 1 和 RAID 10，你可能应该将控制器内存的 100% 用于缓存写入。对于 RAID 5，你应该保留一些控制器内存用于其内部操作。这通常是一个好建议，但并不总是适用——不同的 RAID 卡需要不同的配置。

当你使用 RAID 缓存进行写入缓存时，许多控制器允许你配置延迟写入的时间（一秒、五秒等）。延迟时间更长意味着更多的写入可以被分组并优化地刷新到磁盘上。缺点是你的写入将更“突发”。这并不是一件坏事，除非你的应用程序恰好在控制器缓存填满时发出一堆写入请求，即将刷新到磁盘上。如果没有足够的空间来处理应用程序的写入请求，它将不得不等待。保持延迟时间较短意味着你将有更多的写入操作，它们将更不高效，但它可以平滑地处理尖峰，并帮助保持更多的缓存空闲以处理应用程序的突发请求。（我们在这里进行了简化——控制器通常具有复杂的、供应商特定的平衡算法，所以我们只是试图涵盖基本原则。）

写缓存对于同步写入非常有帮助，例如在事务日志上发出fsync()调用和启用sync_binlog创建二进制日志，但除非您的控制器有电池备份单元（BBU）或其他非易失性存储，否则不应启用它。在没有 BBU 的情况下缓存写入可能会导致数据库甚至事务性文件系统在断电时损坏。然而，如果您有 BBU，启用写缓存可以提高性能，例如对于执行大量日志刷新操作的工作负载，例如在事务提交时刷新事务日志。

最后一个考虑因素是许多硬盘都有自己的写缓存，可以通过欺骗控制器向物理介质写入数据来“伪造”fsync()操作。直接连接的硬盘（而不是连接到 RAID 控制器）有时可以让它们的缓存由操作系统管理，但这也并不总是有效的。这些缓存通常会在fsync()时被刷新，并且在同步 I/O 时被绕过，但是硬盘可能会撒谎。你应该确保这些缓存在fsync()时被刷新，或者禁用它们，因为它们没有备用电源。操作系统或 RAID 固件未正确管理的硬盘已经导致了许多数据丢失的情况。

出于这个原因和其他原因，当您安装新硬件时，进行真正的崩溃测试（从墙上拔下电源插头）总是一个好主意。这通常是发现微妙的配置错误或狡猾的硬盘行为的唯一方法。可以在在线找到一个方便的脚本。

要测试您是否真的可以依赖 RAID 控制器的 BBU，请确保将电源线拔掉一段现实时间。一些设备在没有电源的情况下的持续时间可能不如预期的长。在这里，一个糟糕的环节可能使您整个存储组件链变得无用。

网络配置

就像延迟和吞吐量对硬盘是限制因素一样，延迟和带宽对网络连接也是限制因素。对大多数应用程序来说，最大的问题是延迟；典型应用程序进行大量小型网络传输，每次传输的轻微延迟会累积起来。

网络运行不正确也是一个主要的性能瓶颈。数据包丢失是一个常见问题。即使 1%的丢包足以导致显著的性能下降，因为协议栈中的各个层将尝试使用策略来解决问题，例如等待一段时间然后重新发送数据包，这会增加额外的时间。另一个常见问题是破损或缓慢的 DNS 解析。

DNS 足以成为一个致命弱点，因此在生产服务器上启用skip_name_resolve是一个好主意。破损或缓慢的 DNS 解析对许多应用程序都是一个问题，但对于 MySQL 来说尤为严重。当 MySQL 收到连接请求时，它会进行正向和反向 DNS 查找。有很多原因可能导致这种情况出错。一旦出错，将导致连接被拒绝，连接到服务器的过程变慢，并且通常会造成混乱，甚至包括拒绝服务攻击。如果启用skip_name_resolve选项，MySQL 将不执行任何 DNS 查找。但是，这也意味着您的用户帐户在host列中必须只有 IP 地址、“localhost”或 IP 地址通配符。任何在host列中具有主机名的用户帐户将无法登录。

调整设置以有效处理大量连接和小查询通常更为重要。其中一个更常见的调整是更改本地端口范围。Linux 系统有一系列可用的本地端口。当连接返回给调用者时，它使用本地端口。如果有许多同时连接，您可能会用完本地端口。

这是一个配置为默认值的系统：

$ cat /proc/sys/net/ipv4/ip_local_port_range
32768 61000
1
2

有时你可能需要将这些值更改为更大的范围。例如：

$ echo 1024 65535 > /proc/sys/net/ipv4/ip_local_port_range
1

TCP 协议允许系统排队接收连接，就像一个桶。如果桶装满了，客户端就无法连接。你可以通过以下方式允许更多连接排队：

$ echo 4096 > /proc/sys/net/ipv4/tcp_max_syn_backlog
1

对于仅在本地使用的数据库服务器，你可以缩短在关闭套接字后的超时时间，以防对等方断开连接但不关闭连接的情况。在大多数系统上，默认值是一分钟，这相当长：

$ echo <value> > /proc/sys/net/ipv4/tcp_fin_timeout
1

大多数情况下，这些设置可以保持默认值不变。通常只有在发生异常情况时才需要更改它们，比如网络性能极差或连接数量非常大。在互联网上搜索“TCP 变量”会找到很多关于这些变量和更多变量的好文章。

选择文件系统

你的文件系统选择在很大程度上取决于你的操作系统。在许多系统中，比如 Windows，你实际上只有一两个选择，而且只有一个（NTFS）是真正可行的。另一方面，GNU/Linux 支持许多文件系统。

许多人想知道哪种文件系统在 GNU/Linux 上为 MySQL 提供最佳性能，甚至更具体地说，哪种选择对 InnoDB 最好。实际的基准测试显示，它们在大多数方面都非常接近，但是寻求文件系统性能实际上是一个干扰。文件系统的性能非常依赖于工作负载，并且没有一个文件系统是万能的。大多数情况下，一个给定的文件系统不会比其他文件系统表现明显更好或更差。唯一的例外是如果你遇到某些文件系统限制，比如它如何处理并发性、处理许多文件、碎片化等等。

总的来说，最好使用一个日志文件系统，比如 ext4、XFS 或 ZFS。如果不这样做，在崩溃后进行文件系统检查可能需要很长时间。

如果你使用 ext3 或其后继者 ext4，你有三个选项来记录数据的方式，你可以将它们放在*/etc/fstab*挂载选项中：

data=writeback

这个选项意味着只有元数据写入被记录。元数据写入不与数据写入同步。这是最快的配置，通常与 InnoDB 一起使用是安全的，因为它有自己的事务日志。唯一的例外是，在 MySQL 的 8.0 版本之前，如果在恰当的时机发生崩溃，可能会导致*.frm*文件损坏。

这里有一个示例，说��这种配置可能会导致问题。假设一个程序决定扩展一个文件使其更大。元数据（文件的大小）将在实际写入数据到（现在更大的）文件之前被记录和写入。结果是文件的尾部——新扩展区域——包含垃圾。

data=ordered

这个选项也只记录元数据，但通过在写入数据之前写入数据来提供一些一致性，以保持一致性。它只比writeback选项稍慢一点，但在发生崩溃时更安全。在这种配置下，如果我们再次假设一个程序想要扩展一个文件，文件的元数据在数据写入新扩展区域之前不会反映文件的新大小。

data=journal

此选项提供原子日志行为，将数据写入日志后再写入最终位置。通常情况下这是不必要的，并且比其他两个选项的开销要大得多。然而，在某些情况下，它可以提高性能，因为日志记录使文件系统能够延迟将数据写入最终位置。

无论文件系统如何，最好禁用一些特定选项，因为它们不提供任何好处，而且可能会增加相当多的开销。最著名的是记录访问时间，即使您只是读取文件或目录，也需要写入。要禁用此选项，请将noatime,nodiratime挂载选项添加到您的*/etc/fstab*；这有时可以提高性能 5%–10%，具体取决于工作负载和文件系统（尽管在其他情况下可能没有太大差异）。以下是我们提到的 ext3 选项的示例*/etc/fstab*行：

/dev/sda2 /usr/lib/mysql ext3 noatime,nodiratime,data=writeback 0 1
1

您还可以调整文件系统的预读行为，因为这可能是多余的。例如，InnoDB 会进行自己的预读预测。在 Solaris 的 UFS 上禁用或限制预读对性能特别有益。使用innodb_flush_method=O_DIRECT会自动禁用预读。

一些文件系统不支持您可能需要的功能。例如，如果您正在使用 InnoDB 的O_DIRECT刷新方法，对于直接 I/O 的支持可能很重要。此外，一些文件系统比其他文件系统更好地处理大量底层驱动器；例如，XFS 在这方面通常比 ext3 好得多。最后，如果您计划使用逻辑卷管理器（LVM）快照来初始化副本或进行备份，您应该验证您选择的文件系统和 LVM 版本是否能很好地配合。

表 4-2 总结了一些常见文件系统的特性。

表 4-2. 常见文件系统特性

文件系统	操作系统	日志记录	大目录
ext3	GNU/Linux	可选	可选/部分
ext4	GNU/Linux	是	是
日志文件系统 (JFS)	GNU/Linux	是	否
NTFS	Windows	是	是
ReiserFS	GNU/Linux	是	是
UFS (Solaris)	Solaris	是	可调
UFS (FreeBSD)	FreeBSD	否	可选/部分
UFS2	FreeBSD	否	可选/部分
XFS	GNU/Linux	是	是
ZFS	GNU/Linux, Solaris, FreeBSD	是	是

我们通常建议使用 XFS 文件系统。ext3 文件系统有太多严重的限制，比如每个 inode 只有一个互斥锁，以及不好的行为，比如在fsync()上刷新整个文件系统中的所有脏块，而不仅仅是一个文件的脏块。ext4 文件系统是一个可以接受的选择，尽管在特定内核版本中可能存在性能瓶颈，您在承诺之前应该调查一下。

在考虑为数据库选择任何文件系统时，考虑它已经可用多久，它有多成熟，以及在生产环境中它已经被证明。文件系统位是数据库中最低级别的数据完整性。

选择磁盘队列调度程序

在 GNU/Linux 上，队列调度程序确定请求发送到底层设备的顺序。默认值是完全公平队列，或cfq。对于笔记本电脑和台式机的日常使用，它可以防止 I/O 饥饿，但对于服务器来说很糟糕。因为它会不必要地使一些请求在队列中停滞。

您可以使用以下命令查看可用的调度程序以及哪个是活动的：

$ cat /sys/block/sda/queue/scheduler
noop deadline [cfq]
1
2

你应该用感兴趣的磁盘设备名称替换*sda*。在我们的示例中，方括号表示此设备正在使用哪种调度程序。另外两个选择适用于服务器级硬件，在大多数情况下它们的效果差不多。noop调度程序适用于在后台进行自己调度的设备，例如硬件 RAID 控制器和存储区域网络（SAN），而deadline适用于直接连接的 RAID 控制器和磁盘。我们的基准测试显示这两者之间几乎没有区别。最重要的是使用除了cfq之外的任何调度程序，因为它可能导致严重的性能问题。

内存和交换

MySQL 在分配给它大量内存时表现最佳。正如我们在第一章中学到的，InnoDB 使用内存作为缓存以避免磁盘访问。这意味着内存系统的性能直接影响查询服务的速度。即使在今天，确保更快的内存访问的最佳方法之一是用外部内存分配器（glibc）替换内置内存分配器，如tcmalloc或jemalloc。许多基准测试²表明，与glibc相比，这两者都提供了改进的性能和减少的内存碎片化。

当操作系统将一些虚拟内存写入磁盘因为没有足够的物理内存来保存时，就会发生交换。对于运行在操作系统上的进程，交换是透明的。只有操作系统知道特定虚拟内存地址是在物理内存中还是在磁盘上。

在使用 SSD 时，性能损失不像以前使用 HDD 那样严重。你仍然应该积极避免交换，即使只是为了避免不必要的写入可能缩短磁盘的整体寿命。你也可以考虑采用不使用交换的方法，这样可以避免潜在的问题，但会使你处于内存耗尽可能导致进程终止的情况。

在 GNU/Linux 上，你可以使用vmstat来监视交换（我们在下一节中展示了一些示例）。你需要查看交换 I/O 活动，报告在si和so列中，而不是交换使用情况，报告在swpd列中。swpd列可能显示已加载但未使用的进程，这并不是真正的问题。我们希望si和so列的值为0，它们肯定应该小于每秒 10 个块。

在极端情况下，过多的内存分配可能导致操作系统的交换空间耗尽。如果发生这种情况，由于虚拟内存的缺乏，MySQL 可能会崩溃。但即使不会耗尽交换空间，非常活跃的交换也可能导致整个操作系统无响应，甚至无法登录和终止 MySQL 进程。有时候当操作系统耗尽交换空间时，Linux 内核甚至会完全挂起。我们建议你完全不使用交换空间来运行数据库。磁盘仍然比 RAM 慢一个数量级，这样可以避免这里提到的所有问题。

在极端虚拟内存压力下经常发生的另一件事是内存不足（OOM）杀手进程会启动并终止某些进程。这经常是 MySQL，但也可能是另一个进程，比如 SSH，这可能导致你的系统无法从网络访问。你可以通过设置 SSH 进程的oom_adj或oom_score_adj值来防止这种情况发生。在使用专用数据库服务器时，我们强烈建议你识别任何关键进程，如 MySQL 和 SSH，并主动调整 OOM 杀手分数，以防止它们被首先选择终止。

您可以通过正确配置 MySQL 缓冲区来解决大多数交换问题，但有时操作系统的虚拟内存系统决定无论如何交换 MySQL，有时与 Linux 中的非统一内存访问（NUMA）的工作方式有关³。这通常发生在操作系统看到 MySQL 的大量 I/O 时，因此它试图增加文件缓存以容纳更多数��。如果内存不足，必须交换出某些内容，而这些内容可能是 MySQL 本身。一些较旧的 Linux 内核版本还具有不当的优先级，会在不应该交换时交换内容，但在较新的内核中已经有所缓解。

操作系统通常允许对虚拟内存和 I/O 进行一些控制。我们在 GNU/Linux 上提到了一些控制它们的方法。最基本的是将*/proc/sys/vm/swappiness*的值更改为低值，例如0或1。这告诉内核除非对虚拟内存的需求极端，否则不要交换。例如，这是如何检查当前值的方法：

$ cat /proc/sys/vm/swappiness
60
1
2

显示的值为 60，是默认的 swappiness 设置（范围从 0 到 100）。这对服务器来说是非常糟糕的默认值。这只适用于笔记本电脑。服务器应设置为0：

$ echo 0 > /proc/sys/vm/swappiness
1

另一个选项是更改存储引擎读取和写入数据的方式。例如，使用innodb_flush_method=O_DIRECT可以减轻 I/O 压力。直接 I/O 不会被缓存，因此操作系统不会将其视为增加文件缓存大小的原因。此参数仅适用于 InnoDB。

另一个选项是使用 MySQL 的memlock配置选项，将 MySQL 锁定在内存中。这将避免交换，但可能会很危险：如果没有足够的可锁定内存剩余，当 MySQL 尝试分配更多内存时，MySQL 可能会崩溃。如果锁定了太多内存，而操作系统没有足够的内存剩余，也可能会引起问题。

许多技巧特定于内核版本，因此要小心，特别是在升级时。在某些工作负载中，很难使操作系统表现得明智，您的唯一选择可能是将缓冲区大小降低到次优值。

操作系统状态

您的操作系统提供了工具，帮助您了解操作系统和硬件正在做什么。在本节中，我们将向您展示如何使用两个广泛可用的工具iostat和vmstat的示例。如果您的系统没有提供这两个工具中的任何一个，那么很可能会提供类似的工具。因此，我们的目标不是让您成为iostat或vmstat的专家，而只是向您展示在尝试使用这些工具诊断问题时要寻找什么。

除了这些工具，您的操作系统可能提供其他工具，如mpstat或sar。如果您对系统的其他部分感兴趣，例如网络，您可能想使用ifconfig（显示发生了多少网络错误等）或netstat等工具。

默认情况下，vmstat和iostat只生成一个报告，显示自服务器启动以来各种计数器的平均值，这并不是很有用。但是，您可以为这两个工具提供一个间隔参数。这使它们生成增量报告，显示服务器当前正在执行的操作，这更加相关。（第一行显示自系统启动以来的统计信息；您可以忽略此行。）

如何阅读 vmstat 输出

让我们先看一个vmstat的示例。要使其每五秒打印一个新报告，以兆字节为单位报告大小，请使用以下命令：

$ vmstat -SM 5
procs -------memory------- -swap- -----io---- ---system---- ------cpu-----
 r  b swpd free buff cache  si so    bi    bo     in     cs us sy id wa st
11  0    0 2410    4 57223   0  0  9902 35594 122585 150834 10  3 85  1  0
10  2    0 2361    4 57273   0  0 23998 35391 124187 149530 11  3 84  2  0
1
2
3
4
5

您可以使用 Ctrl-C 停止vmstat。您看到的输出取决于您的操作系统，因此您可能需要阅读手册页以弄清楚。

正如前面所述，尽管我们要求增量输出，但第一行的值显示了自服务器启动以来的平均值。第二行显示了当前的情况，随后的行将显示每隔五秒发生的情况。这些列按以下其中一个标题分组：

procs

r 列显示有多少进程正在等待 CPU 时间。b 列显示有多少进程处于不可中断的睡眠状态，这通常意味着它们正在等待 I/O（磁盘、网络、用户输入等）。

memory

swpd 列显示了被交换到磁盘（分页）的块数。剩下的三列显示了多少块是 free（未使用的）、多少块用于缓冲区（buff），以及多少块用于操作系统的 cache。

swap

这些列显示了交换活动：操作系统每秒交换进（从磁盘）和交换出（到磁盘）的块数。它们比 swpd 列更重要。我们希望大部分时间看到 si 和 so 为 0，绝对不希望看到超过 10 块每秒。突发也是不好的。

这些列显示每秒从块设备读入的块数（bi）和写出的块数（bo）。这通常反映了磁盘 I/O。

system

这些列显示每秒中断数（in）和每秒上下文切换数（cs）。

cpu

这些列显示了总 CPU 时间的百分比，用于运行用户（非内核）代码、运行系统（内核）代码、空闲以及等待 I/O。可能的第五列（st）显示了如果您使用虚拟化，则从虚拟机“窃取”的百分比。这指的是在虚拟机上有东西可以运行的时间，但是 hypervisor 选择运行其他东西的时间。如果虚拟机不想运行任何东西，而 hypervisor 运行其他东西，那就不算是被窃取的时间。

vmstat 输出是系统相关的，因此如果您的输出与我们展示的示例不同，您应该阅读您系统的 vmstat(8) 手册页。

如何阅读 iostat 输出

现在让我们转向 iostat。默认情况下，它显示了与 vmstat 相同的一些 CPU 使用信息。不过，我们通常只对 I/O 统计感兴趣，因此我们使用以下命令仅显示扩展设备统计信息：

$ iostat -dxk 5
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s 
sda 0.00 0.00 1060.40 3915.00 8483.20 42395.20 

avgrq-sz avgqu-sz await r_await w_await svctm %util
 20.45 3.68 0.74 0.57 0.78 0.20 98.22
1
2
3
4
5
6

与 vmstat 一样，第一个报告显示了自服务器启动以来的平均值（我们通常省略以节省空间），随后的报告显示了增量平均值。每个设备一行。

有各种选项可以显示或隐藏列。官方文档有点混乱，我们不得不深入源代码中找出到底显示了什么。以下是每列显示的内容：

rrqm/s 和 wrqm/s

每秒排队的合并读写请求数。合并意味着操作系统从队列中获取多个逻辑请求，并将它们组合成一个实际设备的单个请求。

r/s 和 w/s

每秒发送到设备的读取和写入请求数。

rkB/s 和 wkB/s

每秒读取和写入的千字节数。

avgrq-sz

请求大小（以扇区为单位）。

avgqu-sz

在设备队列中等待的请求数。

await

在磁盘队列中花费的毫秒数。

r_await 和 w_await

发送到设备的读取请求的平均时间（以毫秒为单位），分别为读取和写入。这包括请求在队列中花费的时间以及为其提供服务的时间。

svctm

服务请求所花费的毫秒数，不包括队列时间。

%util⁴

至少有一个请求处于活动状态的时间百分比。这个名字非常令人困惑。如果您熟悉排队理论中利用率的标准定义，那么这就是设备的利用率。具有多个硬盘驱动器（如 RAID 控制器）的设备应该能够支持比 1 更高的并发性，但是%util永远不会超过 100%，除非在计算中存在四舍五入误差。因此，与文档所说的相反，它并不是设备饱和的良好指标，除非您正在查看单个物理硬盘的特殊情况。

您可以使用输出推断有关机器 I/O 子系统的一些事实。一个重要的指标是同时服务的请求数。由于读取和写入是每秒进行的，而服务时间的单位是千分之一秒，您可以使用 Little’s law 推导出设备正在服务的并发请求数的以下公式：

concurrency = (r/s + w/s) * (svctm/1000)
1

将前面的样本数字插入并发公式中得到约 0.995 的并发性。这意味着在采样间隔期间，设备平均服务的请求少于一个。

其他有用的工具

我们展示了vmstat和iostat，因为它们是广泛可用的工具，而vmstat通常默认安装在许多类 Unix 操作系统上。然而，这些工具各有其局限性，比如单位混乱、采样间隔与操作系统更新统计数据的时间不对应，以及无法一次看到所有指标。如果这些工具不符合您的需求，您可能会对dstat或collectl感兴趣。

我们也喜欢使用mpstat来监视 CPU 统计信息；它提供了关于 CPU 如何单独运行的更好的想法，而不是将它们全部分组在一起。在诊断问题时，这有时非常重要。当您检查磁盘 I/O 使用情况时，您可能会发现blktrace也很有帮助。

Percona 编写了自己的iostat替代工具称为pt-diskstats。它是 Percona Toolkit 的一部分。它解决了一些关于iostat的抱怨，比如它如何将读取和写入汇总以及对并发性的可见性不足。它还是交互式的，通过按键驱动，因此您可以放大和缩小，更改聚合，过滤设备，显示和隐藏列。这是一个很好的方式来切分和分析磁盘统计数据的样本，即使您没有安装该工具，也可以通过简单的 shell 脚本收集磁盘活动的样本并通过电子邮件或保存以供以后分析。

最后，Linux 分析器perf是检查操作系统级别发生的情况的宝贵工具。您可以使用perf检查有关操作系统的一般信息，比如为什么内核使用 CPU 这么多。您还可以检查特定的进程 ID，从而查看 MySQL 如何与操作系统交互。检查系统性能是一个非常深入的过程，因此我们推荐 Brendan Gregg 的《Systems Performance, Second Edition》（Pearson）作为优秀的后续阅读。

总结

选择和配置 MySQL 的硬件，并为硬件配置 MySQL，并不是一门神秘的艺术。一般来说，您需要与大多数其他目的相同的技能和知识。然而，有一些 MySQL 特定的事项您应该知道。

我们通常建议大多数人在性能和成本之间找到一个良好的平衡。首先，我们喜欢使用商品服务器，有很多原因。例如，如果您的服务器出现问题，您需要将其停机以诊断问题，或者如果您只是想尝试用另一台服务器替换它作为诊断的一种形式，那么使用价值 5000 美元的服务器比使用价值 50000 美元或更高的服务器要容易得多。 MySQL 通常也更适合于商品硬件，无论是从软件本身还是从典型的工作负载来看。

MySQL 需要的四个基本资源是 CPU、内存、磁盘和网络资源。网络很少会成为严重瓶颈，但 CPU、内存和磁盘确实会。速度和数量的平衡取决于工作负载，您应该根据预算的允许程度努力实现快速和多样的平衡。你期望的并发越多，你就应该更多地依赖更多的 CPU 来适应你的工作负载。

CPU、内存和磁盘之间的关系错综复杂，一个领域的问题通常会在其他地方显现出来。在向问题投入资源之前，问问自己是否应该将资源投入到另一个问题上。如果你受到 I/O 限制，你需要更多的 I/O 容量，还是只需要更多的内存？答案取决于工作集大小，即在给定时间内最常需要的数据集。

固态设备非常适合提高服务器整体性能，现在通常应该成为数据库的标准，特别是 OLTP 工作负载。继续使用 HDD 的唯一理由是在极度预算受限的系统中或者需要大量磁盘空间的情况下，例如在数据仓库情况下需要 PB 级别的磁盘空间。

在操作系统方面，有一些关键的事项需要正确处理，主要涉及存储、网络和虚拟内存管理。如果您使用 GNU/Linux，正如大多数 MySQL 用户所做的那样，我们建议使用 XFS 文件系统，并将 swappiness 和磁盘队列调度器设置为适合服务器的值。有一些可能需要更改的网络参数，您可能希望调整其他一些参数（例如禁用 SELinux），但这些更改是个人偏好的问题。

¹ 流行的俳句：这不是 DNS。不可能是 DNS。就是 DNS。

² 参见博客文章“内存分配器对 MySQL 性能的影响”和“MySQL（或 Percona）内存使用测试”进行比较。

³ 更多信息请参见此博客文章。

⁴ 软件 RAID，如 MD/RAID，可能不会显示 RAID 阵列本身的利用率。

第五章：优化服务器设置

在本章中，我们将解释一个过程，通过这个过程你可以为你的 MySQL 服务器创建一个合适的配置文件。这是一个迂回的旅程，有许多有趣的地方和风景名胜。这些旁支旅程是必要的。确定适当配置的最短路径并不是从研究配置选项和询问应该设置哪些选项或如何更改它们开始。也不是从检查服务器行为和询问是否有任何配置选项可以改进它开始。最好从理解 MySQL 的内部机制和行为开始。然后你可以将这些知识用作如何配置 MySQL 的指南。最后，你可以将期望的配置与当前配置进行比较，并纠正任何重要且有价值的差异。

人们经常问：“对于拥有 32GB RAM 和 12 个 CPU 核心的服务器，最佳配置文件是什么？”不幸的是，事情并不那么简单。你应该根据工作负载、数据和应用程序要求来配置服务器，而不仅仅是硬件。MySQL 有许多设置可以更改，但你不应该这样做。通常最好正确配置基本设置（在大多数情况下只有少数几个是重要的），并花更多时间在模式优化、索引和查询设计上。在正确设置 MySQL 的基本配置选项之后，进一步更改的潜在收益通常很小。

另一方面，随意更改配置的潜在风险是巨大的。MySQL 的默认设置是有充分理由的。不明确地了解影响就进行更改可能导致崩溃、持续停顿或性能下降。因此，你永远不应该盲目相信像 MySQL 论坛或 Stack Overflow 这样的热门帮助网站上的某人报告的最佳配置。始终通过阅读相关手册条目并仔细测试来审查任何更改。

那么你应该做什么呢？你应该确保像 InnoDB 缓冲池和日志文件大小这样的基本设置是合适的。然后，如果你想要防止不良行为，你应该设置一些安全选项（但请注意，这些通常不会提高性能，它们只会避免问题）。然后就让其他设置保持不变。如果你遇到问题，首先要仔细诊断。如果你的问题是由服务器的某个部分造成的，而这个部分的行为可以通过配置选项进行更正，那么你可能需要进行更改。

有时候你可能还需要设置特定的配置选项，这些选项在特殊情况下可能会对性能产生显著影响。然而，这些选项不应该是基本服务器配置文件的一部分。只有在发现它们解决的具体性能问题时才应该设置它们。这就是为什么我们不建议你通过寻找需要改进的坏事来处理配置选项。如果有什么需要改进的，它应该在查询响应时间中显示出来。最好从查询和它们的响应时间开始搜索，而不是从配置选项开始。这可以为你节省大量时间并避免许多问题。

另一个节省时间和麻烦的好方法是除非你知道你不应该，否则使用默认设置。人多力量大，很多人都在使用默认设置。这使得它们成为经过最彻底测试的设置。不必要更改事物时可能会出现意外错误。

MySQL 的配置工作原理

我们将首先解释 MySQL 的配置机制，然后再讨论你应该在 MySQL 中配置什么。MySQL 通常对其配置相当宽容，但遵循这些建议可能会为你节省大量工作和时间。

首先要了解的是 MySQL 从哪里获取配置信息：从命令行参数和配置文件中的设置。在类 Unix 系统上，配置文件通常位于 /etc/my.cnf 或 /etc/mysql/my.cnf。如果您使用操作系统的启动脚本，这通常是您指定配置设置的唯一位置。如果您手动启动 MySQL，可能在运行测试安装时会这样做，您也可以在命令行上指定设置。服务器实际上会读取配置文件的内容，删除任何注释行和换行符，然后与命令行选项一起处理。

警告

您决定永久使用的任何设置都应放入全局配置文件中，而不是在命令行中指定。否则，您可能会意外地启动服务器而没有这些设置。另外，将所有配置文件放在一个地方也是个好主意，这样您可以轻松检查它们。

请确保知道服务器的配置文件位于何处！我们曾见过一些人试图使用服务器不读取的文件进行配置，例如 Debian 服务器上的 /etc/my.cnf，而这些服务器会在 /etc/mysql/my.cnf 中查找配置。有时会有文件位于多个配置，也许是因为以前的系统管理员也感到困惑。如果您不知道服务器读取哪些文件，可以询问它：

$ which mysqld
/usr/sbin/mysqld
$ */usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'*
Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf
1
2
3
4
5

配置文件采用标准的 INI 格式，并分为多个部分，每个部分以包含部分名称的方括号开头的行开始。MySQL 程序通常会读取与该程序同名的部分，许多客户端程序也会读取 client 部分，这为您提供了一个放置常见设置的地方。服务器通常会读取 mysqld 部分。请确保将设置放在文件中的正确部分，否则它们将不起作用。

语法、作用域和动态性

配置设置以全小写形式编写，单词之间用下划线或破折号分隔。以下是等效的写法，在命令行和配置文件中可能会看到这两种形式：

/usr/sbin/mysqld --auto-increment-offset=5
/usr/sbin/mysqld --auto_increment_offset=5
1
2

我们建议您选择一种风格并保持一致。这样可以更容易地在文件中搜索设置。

配置设置可以具有多个作用域。一些设置是服务器范围的（全局作用域），其他设置对每个连接都不同（会话作用域），还有一些是针对每个对象的。许多会话作用域变量都有全局等效变量，您可以将其视为默认值。如果更改会话作用域变量，则仅影响更改它的连接，并且在连接关闭时更改将丢失。以下是您应该了解的各种行为的一些示例：

max_connections 变量是全局作用域的。
sort_buffer_size 变量具有全局默认值，但您也可以为每个会话设置它。
join_buffer_size 变量具有全局默认值，并且可以为每个会话设置，但是一个查询连接多个表可能会为每个连接分配一个连接缓冲区，因此可能会有多个连接缓冲区。

除了在配置文件中设置变量外，您还可以在服务器运行时更改许多（但不是全部）变量。MySQL 将这些称为动态配置变量。以下语句展示了动态更改 sort_buffer_size 的会话和全局值的不同方法：

SET sort_buffer_size = <value>;
SET GLOBAL sort_buffer_size = <value>;
SET @@sort_buffer_size := <value>;
SET @@session.sort_buffer_size := <value>;
SET @@global.sort_buffer_size := <value>;
1
2
3
4
5

如果动态设置变量，请注意当 MySQL 关闭时这些设置将丢失。如果要保留设置，您必须更新配置文件。

提示

如果您在服务器运行时设置变量的全局值，则当前会话和任何其他现有会话的值不受影响。如果您的客户端依赖于持久性数据库连接，请记住这一点。这是因为当连接创建时，会话值是从全局值初始化的。您应该在每次更改后检查SHOW GLOBAL VARIABLES的输出，以确保它产生了预期的效果。

您还可以使用SET命令为变量分配一个特殊值：关键字DEFAULT。将此值分配给会话作用域变量会将该变量设置为相应全局作用域变量的值。这对于将会话作用域变量重置为打开连接时的值非常有用。我们建议您不要将其用于全局变量，因为它可能不会达到您想要的效果——也就是说，它不会将值设置回您启动服务器时的值，甚至不会设置为配置文件中指定的值；它将变量设置为编译时的默认值。

持久化系统变量

如果所有这些变量作用域和配置业务还不够复杂，您还必须意识到，如果重新启动 MySQL，它将恢复到配置文件中的内容，即使您使用SET GLOBAL更改全局变量。这意味着您必须管理一个配置文件和MySQL 的运行时配置，并确保它们彼此保持同步。如果您想增加服务器的max_connections，您必须在每个运行实例上发出SET GLOBAL max_connections命令，然后跟着编辑配置文件以反映您的新配置。

MySQL 8.0 引入了一个名为持久化系统变量的新功能，有助于使这个过程变得稍微简单一些。新的语法SET PERSIST现在允许您为运行时设置值一次，MySQL 将把这个设置写入磁盘，使其能够在下次重启时使用。

设置变量的副作用

动态设置变量可能会产生意想不到的副作用，比如刷新缓冲区中的脏块。在线更改哪些设置时要小心，因为这可能会导致服务器做大量工作。

有时您可以从变量的名称推断出其行为。例如，max_heap_table_size的功能就如其名：它指定了隐式内存临时表允许增长的最大大小。然而，命名约定并不完全一致，因此您不能总是通过查看名称来猜测变量的功能。

让我们看一下一些常用变量及更改它们动态的影响：

table_open_cache

设置此变量没有立即效果：效果会延迟到下次线程打开表时。当这种情况发生时，MySQL 会检查变量的值。如果值大于缓存中的表数，线程可以将新打开的表插入缓存中。如果值小于缓存中的表数，MySQL 会从缓存中删除未使用的表。

thread_cache_size

设置此变量没有立即效果：效果会延迟到下次连接关闭时。此时，MySQL 将检查缓存中是否有空间来存储线程。如果有，它会将线程缓存以便将来由另一个连接重用。如果没有，它会杀死线程而不是将其缓存。在这种情况下，缓存中的线程数以及线程缓存使用的内存量不会立即减少；只有当新连接从缓存中移除线程以使用它时，它才会减少。（MySQL 仅在连接关闭时添加线程到缓存中，并且仅在创建新连接时从缓存中删除线程。）

read_buffer_size

MySQL 不会为这个缓冲区分配任何内存，直到查询需要它，但然后它��即分配这里指定的整个内存块。

read_rnd_buffer_size

MySQL 不会为这个缓冲区分配任何内存，直到查询需要它，然后它只会分配所需的内存。（max_read_rnd_buffer_size这个名称更准确地描述了这个变量。）

官方的 MySQL 文档详细解释了这些变量的作用，这并不是一个详尽的列表。我们在这里的目标只是向你展示当你更改一些常见变量时可以期望的行为。

除非你知道这样做是正确的，否则不要全局提高每个连接设置的值。有些缓冲区即使不需要也会一次性分配，因此一个很大的全局设置可能是一个巨大的浪费。相反，当一个查询需要时，你可以提高这个值。

规划你的变量更改

在设置变量时要小心。更多并不总是更好，如果你将值设置得太高，你很容易引起问题：你可能会耗尽内存或导致服务器交换。

参考第二章，监控你的 SLOs 以确保你的更改不会影响客户体验。基准测试并不足够，因为它们不是真实的。如果你不测量服务器的实际性能，你可能会在不知情的情况下损害性能。我们看到许多情况下，有人更改了服务器的配置并认为它提高了性能，而实际上由于不同时间或不同日期的不同工作负载，服务器的性能整体上恶化了。

理想情况下，你正在使用版本控制系统跟踪对配置文件的更改。这种策略可以非常有效地将性能变化或 SLO 违规与特定配置更改相关联。只是要注意，默认情况下更改配置文件并不会做任何事情——你必须同时更改运行时设置。

在开始更改配置之前，你应该优化你的查询和模式，至少解决一些明显的问题，比如添加索引。如果你深入调整配置，然后更改查询或模式，你可能需要重新评估配置。请记住，除非你的硬件、工作负载和数据完全静态，否则你很可能需要稍后重新审视你的配置。事实上，大多数人的服务器甚至一天中的工作负载都不是完全稳定的——这意味着上午中间的“完美”配置并不适合下午中午！显然，追求神话般的“完美”配置是完全不切实际的。因此，你不需要从服务器中挤出每一丝性能；事实上，这样投入时间的回报可能非常小。我们建议你专注于优化你的高峰工作负载，然后在“足够好”的地方停下，除非你有理由相信你正在放弃重大的性能改进。

不要做什么

在开始服务器配置之前，我们想鼓励你避免一些我们发现有风险或实际上不值得努力的常见做法。警告：下面有抱怨！

你可能期望（或者认为你被期望）建立一个基准测试套件，并通过迭代更改其配置来“调整”服务器以寻找最佳设置。这通常不是我们建议大多数人做的事情。这需要很多工作和研究，而在大多数情况下潜在回报是如此之小，以至于可能是一种巨大的时间浪费。你可能最好将那些时间花在其他事情上，比如检查你的备份，监控查询计划的变化等等。

你不应该“按比率调优”。经典的“调优比率”是一个经验法则，即你的 InnoDB 缓冲池命中率应该高于某个百分比，如果命中率太低，你应该增加缓存大小。这是非常错误的建议。不管别人告诉你什么，缓存命中率与缓存是太大还是太小无关。首先，命中率取决于工作负载——有些工作负载无论缓存有多大都无法缓存，其次，缓存命中是毫无意义的，我们稍后会解释原因。有时候当缓存太小时，命中率较低，增加缓存大小会增加命中率。然而，这只是一个偶然的相关性，并不表示任何关于性能或正确缓存大小的信息。

有时候看起来正确的相关性的问题在于人们开始相信它们将永远正确。Oracle DBA 多年前就放弃了基于比率的调优，我们希望 MySQL DBA 能够效仿他们的做法。我们更加热切地希望人们不要编写“调优脚本”，将这些危险的做法编码化并传授给成千上万的人。这导致了我们下一个建议：不要使用调优脚本！互联网上有几个非常流行的调优脚本。最好还是将它们忽略掉。

我们还建议你避免使用调优这个词，我们在过去几段中大量使用了这个词。我们更倾向于使用配置或优化（只要你确实在做这个）。调优这个词让人联想到一个无纪律的新手，调整服务器然后看看发生了什么。我们在前一节中建议这种做法最好留给那些正在研究服务器内部的人。“调优”你的服务器可能是一种令人惊讶的时间浪费。

在相关主题上，搜索互联网上的配置建议并不总是一个好主意。你可以在博客、论坛等地方找到很多错误的建议。尽管许多专家在线贡献他们所知道的东西，但很难判断谁是合格的。当然，我们无法对在哪里找到真正专家给出公正的建议。但我们可以说，可信赖的、声誉良好的 MySQL 服务提供商通常比简单的互联网搜索结果更可靠，因为拥有满意客户的人可能做对了一些事情。然而，即使是他们的建议，如果没有测试和理解，应用起来也可能是危险的，因为它可能是针对一个你不理解的与你的情况不同的情况。

最后，不要相信流行的内存消耗公式——是的，就是 MySQL 自己在崩溃时打印出来的那个。（我们不会在这里重复它。）这个公式来自一个古老的时代。这不是一个可靠甚至有用的了解 MySQL 在最坏情况下可以使用多少内存的方法。你可能在互联网上看到一些关于这个公式的变体。这些同样存在缺陷，即使它们添加了原始公式没有的更多因素。事实是你无法对 MySQL 的内存消耗设定上限。它不是一个严格控制内存分配的数据库服务器。

创建一个 MySQL 配置文件

正如我们在本章开头提到的，我们没有适用于比如说一个有 4 个 CPU、16GB 内存和 12 个硬盘的服务器的“最佳配置文件”。你确实需要开发自己的配置，因为即使一个良好的起点也会根据你如何使用服务器而有很大的不同。

最小配置

我们为本书创建了一个最小的示例配置文件，你可以将其用作你自己服务器的良好起点。你必须为一些设置选择值；我们稍后会在本章解释这些设置。我们的基础文件，围绕 MySQL 8.0 构建，看起来像这样：

[mysqld]
# GENERAL
datadir                                  = /var/lib/mysql
socket                                   = /var/lib/mysql/mysql.sock
pid_file                                 = /var/lib/mysql/mysql.pid
user                                     = mysql
port                                     = 3306
# INNODB
innodb_buffer_pool_size                  = <value>
innodb_log_file_size                     = <value>
innodb_file_per_table                    = 1
innodb_flush_method                      = O_DIRECT
# LOGGING
log_error                                = /var/lib/mysql/mysql-error.log
log_slow_queries                         = /var/lib/mysql/mysql-slow.log
# OTHER
tmp_table_size                           = 32M
max_heap_table_size                      = 32M
max_connections                          = <value>
thread_cache_size                        = <value>
table_open_cache                         = <value>
open_files_limit                         = 65535
[client]
socket                                   = /var/lib/mysql/mysql.sock
port                                     = 3306

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

这与您习惯看到的可能太简单了，但实际上已经超出了许多人的需求。还有一些其他类型的配置选项，您可能也会经常使用，比如二进制日志记录；我们将在本章和其他章节中详细介绍这些内容。

我们配置的第一件事是数据的位置。我们选择了*/var/lib/mysql*，因为这是许多 Unix 变体上的常用位置。选择其他位置也没有问题；由您决定。我们将*.pid文件放在相同位置，但许多操作系统可能希望将其放在/var/run中。这也可以。我们只是需要为这些设置配置一些内容。顺便说一句，不要让套接字和.pid文件根据服务器的编译默认位置放置；各种 MySQL 版本中可能会出现一些错误，可能会导致问题。最好明确设置这些位置。（我们不建议选择不同的位置；我们只建议确保my.cnf*文件明确提到这些位置，这样在升级服务器时它们不会更改并破坏事情。）

我们还指定了mysqld应以操作系统上的mysql用户帐户运行。您需要确保此帐户存在，并且拥有数据目录和其中的所有文件。端口设置为默认的3306，但有时您可能需要更改。

在 MySQL 8.0 中，引入了一个新的配置选项，innodb_dedicated_server。此选项会检查服务器上的可用内存，并适当配置四个附加变量（innodb_buffer_pool_size、innodb_log_file_size、innodb_log_files_in_group和innodb_flush_method）以用于专用数据库服务器，这简化了计算和更改这些值。在云环境中，这可能特别有用，您可能会运行具有 128 GB RAM 的虚拟机（VM），然后重新启动以扩展到 256 GB RAM。MySQL 在这里将自动配置，您无需管理更改配置文件中的值。这通常是管理这四个设置的最佳方法。

我们示例文件中的大多数其他设置都相当容易理解，其中许多是主观判断的问题。我们将在本章的其余部分探讨其中几个。我们还将在本章后面讨论一些安全设置，这些设置可以帮助使您的服务器更加健壮，并有助于防止糟糕的数据和其他问题。我们这里不展示这些设置。

这里要解释的一个设置是open_files_limit选项。在典型的 Linux 系统上，我们将其设置为尽可能大。在现代操作系统上，打开文件句柄非常便宜。如果此设置不够大，您将看到错误 24，“打开文件过多”。

跳到最后，配置文件中的最后一节是用于mysql和mysqladmin等客户端程序的，只是让它们知道如何连接到服务器。您应该设置客户端程序的值与您为服务器选择的值相匹配。

检查 MySQL 服务器状态变量

有时，您可以使用SHOW GLOBAL STATUS的输出作为配置的输入，以帮助更好地为您的工作负载定制设置。为了获得最佳结果，最好同时查看绝对值和值随时间变化的情况，最好在高峰和低峰时间进行多次快照。您可以使用以下命令每 60 秒查看状态变量的增量变化：

$ mysqladmin extended-status -ri60
1

当我们解释各种配置设置时，我们经常会提到随时间变化的状态变量的变化。通常我们期望您检查类似我们刚刚展示的命令的输出。其他有用的工具，可以提供紧凑的状态计数器变化显示的有 Percona Toolkit 的pt-mext或pt-mysql-summary。

现在我们已经向你展示了一些基础知识，我们将带你参观一些服务器内部，并交替提供配置建议。这将为你选择适当的配置选项值提供所需的背景知识，当我们稍后返回示例配置文件时。

配置内存使用

使用innodb_dedicated_server通常会使用 50%–75%的 RAM。这至少为每个连接的内存分配、操作系统开销和其他内存设置留出了 25%。我们将在接下来的部分详细介绍每一个，并然后更详细地查看各种 MySQL 缓存的需求。

每个连接的内存需求

MySQL 需要一小部分内存来保持连接（通常与一个关联的专用线程）的打开状态。它还需要一定的内存来执行任何给定的查询。你需要为 MySQL 在高负载时段执行查询留出足够的内存。否则，你的查询将因内存不足而运行不佳或失败。

了解 MySQL 在高峰使用期间将消耗多少内存是有用的，但某些使用模式可能会意外地消耗大量内存，这使得难以预测。准备好的语句就是一个例子，因为你可以同时打开许多这样的语句。另一个例子是 InnoDB 数据字典（稍后会详细介绍）。

在尝试预测峰值内存消耗时，你不需要假设最坏情况。例如，如果你配置 MySQL 允许最多一百个连接，理论上可能同时在所有一百个连接上运行大型查询，但实际上这可能不会发生。使用许多大型临时表或复杂存储过程的查询是高每个连接内存消耗的最有可��的原因。

为操作系统保留内存

就像查询一样，你需要为操作系统保留足够的内存来完成其工作。这涉及运行任何本地监控软件、配置管理工具、定期作业等。操作系统有足够内存的最好指标是它没有主动将虚拟内存交换（分页）到磁盘。

InnoDB 缓冲池

InnoDB 缓冲池需要比其他任何东西都更多的内存，因为它通常是性能的最重要变量。InnoDB 缓冲池不仅仅缓存索引：它还保存行数据、自适应哈希索引、更改缓冲区、锁定和其他内部结构。InnoDB 还使用缓冲池来帮助延迟写入，这样它可以合并许多写入并按顺序执行它们。简而言之，InnoDB 严重依赖于缓冲池，你应该确保为其分配足够的内存。你可以使用SHOW命令的变量或诸如innotop之类的工具来监视你的 InnoDB 缓冲池的内存使用情况。

如果你没有太多数据，并且知道你的数据不会快速增长，那么你不需要为缓冲池过度分配内存。将其大小远远大于它将容纳的表和索引的大小并不真正有益。当然，提前规划一个快速增长的数据库也没有错，但有时我们会看到一个微不足道的数据量却有着巨大的缓冲池。这是不必要的。

大型缓冲池带来一些挑战，例如长时间的关闭和热身时间。如果缓冲池中有许多脏（修改的）页面，InnoDB 在关闭时可能需要很长时间，因为它会在关闭时将脏页写入数据文件。你可以强制它快速关闭，但然后它只需在重新启动时执行更多的恢复，因此实际上无法加快关闭和重新启动周期时间。如果你事先知道需要关闭的时间，可以在运行时将innodb_max_dirty_pages_pct变量更改为较低的值，等待刷新线程清理缓冲池，然后在脏页数量变少时关闭。你可以通过观察innodb_buffer_pool_pages_dirty服务器状态变量或使用innotop监视SHOW INNODB STATUS来监视脏页的数量。你还可以使用变量innodb_fast_shutdown来调整关闭的方式。

降低innodb_max_dirty_pages_pct变量的值并不能保证 InnoDB 在缓冲池中保留较少的脏页。相反，它控制了 InnoDB 停止“懒惰”的阈值。InnoDB 的默认行为是使用后台线程刷新脏页，将写操作合并在一起并按顺序执行以提高效率。这种行为被称为“懒惰”，因为它允许 InnoDB 延迟刷新缓冲池中的脏页，除非需要为其他数据使用空间。当脏页的百分比超过阈值时，InnoDB 会尽快刷新页面，以尝试保持较低的脏页计数。这些页面清理操作已经从以前的行为中得到了很大的优化，包括能够配置多个线程执行刷新。

当 MySQL 再次启动时，缓冲池缓存为空，也称为冷缓存。现在，将所有行和页面放入内存的好处都消失了。幸运的是，默认情况下，配置选项innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup一起在启动时使服务器变热。启动时的加载需要时间，但它可以比等待自然填充更快地提高服务器的性能。

线程缓存

线程缓存保存着当前没有与连接关联但准备为新连接提供服务的线程。当缓存中有一个线程且创建了新连接时，MySQL 会将线程从缓存中移除并分配给新连接。当连接关闭时，如果有空间，MySQL 会将线程放回缓存中。如果没有空间，MySQL 会销毁线程。只要 MySQL 在缓存中有空闲线程，它就可以快速响应连接请求，因为它不必为每个连接创建新线程。

thread_cache_size变量指定 MySQL 可以在缓存中保留的线程数。除非你的服务器收到许多连接请求，否则你可能不需要将其从默认值-1或自动大小更改。要检查线程缓存是否足够大，请观察Threads_created状态变量。通常我们尝试保持线程缓存足够大，以便每秒创建的新线程少于 10 个，但通常很容易将这个数字降低到每秒不到一个。

一个好的方法是观察Threads_connected变量并尝试将thread_cache_size设置为足够大以处理工作负载的典型波动。例如，如果Threads_connected通常保持在 100 到 120 之间，你可以将缓存大小设置为 20。如果保持在 500 到 700 之间，200 个线程缓存应该足够大。可以这样想：在 700 个连接时，可能没有线程在缓存中；在 500 个连接时，有 200 个缓存线程准备在负载再次增加到 700 时使用。

使线程缓存非常大可能对大多数用途来说并不是必要的，但保持较小的线程缓存并不能节省太多内存，因此这样做几乎没有什么好处。每个在线程缓存中或正在休眠的线程通常使用大约 256 KB 的内存。与连接在积极处理查询时线程可以使用的内存量相比，这并不多。一般来说，你应该保持线程缓存足够大，以便Threads_created不会经常增加。然而，如果这是一个非常大的数字（例如，成千上万的线程），你可能希望将其设置得更低，因为一些操作系统即使大多数线程处于休眠状态时也无法很好地处理非常大的线程数量。

配置 MySQL 的 I/O 行为

一些配置选项会影响 MySQL 如何将数据同步到磁盘并执行恢复操作。这些选项可能会对性能产生显著影响，因为它们涉及 I/O 操作。它们也代表了性能和数据安全之间的权衡。一般来说，确保数据立即和一致地写入磁盘是很昂贵的。如果你愿意冒磁盘写入可能不会真正到达永久存储的风险，你可以增加并发性和/或减少 I/O 等待时间，但你必须自己决定可以容忍多少风险。

InnoDB 允许你控制它的恢复方式以及如何打开和刷新其数据，这对恢复和整体性能有很大影响。InnoDB 的恢复过程是自动的，并且总是在 InnoDB 启动时运行，尽管你可以影响它采取的行动。撇开恢复不谈，假设从不崩溃或出现问题，对于 InnoDB 仍有很多配置要做。它有一个复杂的缓冲区和文件链设计用于提高性能并保证 ACID 属性，每个链的部分都是可配置的。图 5-1 说明了这些文件和缓冲区。

对于正常使用来说，需要更改的一些最重要的事项是 InnoDB 日志文件大小、InnoDB 如何刷新其日志缓冲区以及 InnoDB 如何执行 I/O。

图 5-1。InnoDB 的缓冲区和文件

InnoDB 事务日志

InnoDB 使用其日志来降低提交事务的成本。它不是在每个事务提交时将缓冲池刷新到磁盘，而是记录事务。事务对数据和索引所做的更改通常映射到表空间中的随机位置，因此将这些更改刷新到磁盘将需要随机 I/O。InnoDB 假设它正在使用传统磁盘，其中随机 I/O 比顺序 I/O 更昂贵，因为寻找正确位置并等待所需部分磁盘旋转到磁头下的时间更长。

InnoDB 使用��日志将这种随机磁盘 I/O 转换为顺序 I/O。一旦日志安全地存储在磁盘上，事务就是永久的，即使更改尚未写入数据文件。如果发生不良事件（例如断电），InnoDB 可以重放日志并恢复已提交的事务。

当然，InnoDB 最终必须将更改写入数据文件，因为日志的大小是固定的。它以循环方式写入日志：当它到达日志末尾时，它会回到开头。如果尚未将其中包含的更改应用于数据文件，它不能覆盖日志记录，因为这将擦除已提交事务的唯一永久记录。

InnoDB 使用后台线程智能地将更改刷新到数据文件。该线程可以将写入组合在一起，并使数据写入顺序以提高效率。实际上，事务日志将随机数据文件 I/O 转换为主要是顺序的日志文件和数据文件 I/O。将刷新移到后台使查询更快完成，并帮助缓冲 I/O 系统免受查询负载的波动影响。

日志文件的整体大小由innodb_log_file_size和innodb_log_files_in_group控制，对写入性能非常重要。如果您遵循我们之前的建议并使用innodb_dedicated_server，则根据系统内存量来管理这些设置。

日志缓冲区

当 InnoDB 更改任何数据时，它会将更改记录写入其保存在内存中的日志缓冲区。当缓冲区变满、事务提交或每秒一次时，InnoDB 会将缓冲区刷新到磁盘上的日志文件。增加缓冲区大小（默认为 1 MB）可以帮助减少 I/O，特别是对于大型事务。控制缓冲区大小的变量称为innodb_log_buffer_size。

通常不需要使缓冲区非常大。推荐的范围是 1-8 MB，这通常足够，除非您写入大量巨大的BLOB记录。与 InnoDB 的正常数据相比，日志条目非常紧凑。它们不是基于页面的，因此不会浪费空间一次存储整个页面。InnoDB 还尽可能地使日志条目短小。有时甚至将它们存储为几个整数，指示记录的操作类型和该操作所需的任何参数！

InnoDB 如何刷新日志缓冲区

当 InnoDB 将日志缓冲区刷新到磁盘上的日志文件时，它会使用互斥锁锁定缓冲区，将其刷新到所需点，然后将任何剩余条目移动到缓冲区的前面。当互斥锁被释放时，可能会有多个事务准备刷新其日志条目。InnoDB 使用组提交功能，可以将所有这些事务一次性提交到日志中。

必须将日志缓冲区刷新到持久存储以确保已提交的事务完全持久。如果您更关心性能而不是持久性，可以更改innodb_flush_log_at_trx_commit以控制何时以及多频繁刷新日志缓冲区。

可能的设置如下：

0

将日志缓冲区写入日志文件并每秒刷新一次日志文件，但在事务提交时不执行任何操作。

1

将日志缓冲区写入日志文件并在每次事务提交时刷新到持久存储。这是默认（也是最安全）的设置；它保证您不会丢失任何已提交的事务，除非磁盘或操作系统“伪造”刷新操作。

2

在每次提交时将日志缓冲区写入日志文件，但不要刷新它。InnoDB 每秒调度一次刷新。与0设置最重要的区别是，如果 MySQL 进程崩溃，2不会丢失任何事务。但是，如果整个服务器崩溃或断电，您仍然可能会丢失事务。

重要的是要知道将日志缓冲区写入日志文件和将日志刷新到持久存储之间的区别。在大多数操作系统中，将缓冲区写入日志只是将数据从 InnoDB 的内存缓冲区移动到操作系统的缓存中，该缓存也位于内存中。它实际上并没有将数据写入持久存储。因此，设置0和2通常会导致在崩溃或停电时最多丢失一秒钟的数据，因为数据可能仅存在于操作系统的缓存中。我们说“通常”是因为 InnoDB 会尝试无论如何每秒刷新一次日志文件到磁盘，但在某些情况下可能会丢失超过一秒钟的事务，例如刷新被阻塞时。

有时硬盘控制器或操作系统通过将数据放入另一个缓存中（例如硬盘自己的缓存）来伪造刷新。这样做更快，但非常危险，因为如果驱动器断电，数据可能仍然会丢失。这比将innodb_flush_log_at_trx_commit设置为1更糟糕，因为它可能导致数据损坏，而不仅仅是丢失事务。

将innodb_flush_log_at_trx_commit设置为除1之外的任何值可能会导致您丢失事务。但是，如果您不关心耐久性（ACID 中的 D），则可能会发现其他设置有用。也许您只想要 InnoDB 的其他一些功能，例如聚集索引、抗数据损坏和行级锁定。

高性能事务需求的最佳配置是将innodb_flush_log_at_trx_commit设置为1，并将日志文件放在具有带电池支持写缓存和固态硬盘的 RAID 卷上。这既安全又非常快速。事实上，我们敢说，任何预计要处理严重工作负载的生产数据库服务器都需要具有这种硬件。

如何打开和刷新 InnoDB 的日志文件和数据文件

innodb_flush_method选项允许您配置 InnoDB 实际与文件系统的交互方式。尽管它的名称是这样，但它也影响 InnoDB 读取数据的方式，而不仅仅是写入数据的方式。

警告

更改 InnoDB 执行 I/O 操作的方式可能会极大地影响性能，因此在更改任何内容之前，请确保您了解自己在做什么！

这是一个稍微令人困惑的选项，因为它既影响日志文件又影响数据文件，并且有时对每种类型的文件执行不同的操作。希望有一个配置选项用于日志和另一个用于数据文件，但它们被合并在一起。

如果您使用类 Unix 操作系统，并且您的 RAID 控制器具有带电池支持写缓存，我们建议您使用O_DIRECT。如果没有，无论是默认设置还是O_DIRECT都可能是最佳选择，这取决于您的应用程序。如果您选择使用我们之前提到的innodb_dedicated_server，此选项将自动为您设置。

InnoDB 表空间

InnoDB 将其数据保存在一个表空间中，这实质上是一个跨越磁盘上一个或多个文件的虚拟文件系统。InnoDB 使用表空间不仅用于存储表和索引，还用于许多其他目的。它在表空间中保存其撤销日志（重新创建旧行版本所需的信息）、更改缓冲区、双写缓冲区和其他内部结构。

配置表空间

您可以使用innodb_data_file_path配置选项指定表空间文件。所有文件都包含在由innodb_data_home_dir给定的目录中。以下是一个示例：

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G
1
2

这将创建一个 3 GB 的表空间，分为三个文件。有时人们会想知道是否可以使用多个文件将负载分布到不同的驱动器上，就像这样：

innodb_data_file_path = /disk1/ibdata1:1G;/disk2/ibdata2:1G;...
1

尽管确实将文件放置在不同目录中，这些目录在此示例中代表不同的驱动器，但 InnoDB 会将文件端对端连接起来。因此，通常你不会通过这种方式获得太多好处。InnoDB 会填满第一个文件，然后在第一个文件满时填满第二个文件，依此类推；负载并没有以你需要的方式分布以获得更高的性能。RAID 控制器是一种更智能的负载分布方式。

如果表空间空间不足而需要增长，您可以使最后一个文件自动扩展，如下所示：

...ibdata3:1G:autoextend
1

默认行为是创建一个单个 10 MB 的自动扩展文件。如果使文件自动扩展，最好设置表空间大小的上限，以防止其变得非常大，因为一旦增长，就不会缩小。例如，以下限制了自动扩展文件为 2 GB：

...ibdata3:1G:autoextend:max:2G
1

管理单个表空间可能会很麻烦，特别是如果它自动扩展并且您想要回收空间（因此，我们建议禁用自动扩展功能，或者至少设置一个合理的空间上限）。回收空间的唯一方法是转储数据，关闭 MySQL，删除所有文件，更改配置，重新启动，让 InnoDB 创建新的空文件，并恢复数据。InnoDB 对其表空间非常严格：您不能简单地删除文件或更改其大小。如果破坏了其表空间，它将拒绝启动。它对其日志文件也非常严格。如果您习惯于像 MyISAM 一样随意移动文件，要小心！

innodb_file_per_table选项允许您配置 InnoDB 使用每个表一个文件。它将数据存储在数据库目录中的tablename.ibd文件中。这样在删除表时更容易回收空间。然而，将数据放在多个文件中实际上可能导致整体浪费更多空间，因为它将单个 InnoDB 表空间中的内部碎片换成了*.ibd*文件中的浪费空间。

即使启用了innodb_file_per_table选项，您仍然需要主表空间来存储撤销日志和其他系统数据。如果不将所有数据存储在其中，则其大小会更小。

有些人喜欢使用innodb_file_per_table仅仅是因为它给您带来额外的可管理性和可见性。例如，通过检查单个文件来查找表的大小要比使用SHOW TABLE STATUS更快，后者必须执行更复杂的工作来确定为表分配了多少页。

警告

innodb_file_per_table一直存在一个阴暗面：DROP TABLE性能慢。这可能严重到足以导致整个服务器出现明显的停顿，原因有两个。

删除表会在文件系统级别取消链接（删除）文件，在某些文件系统上可能会非常慢（ext3，我们在看你）。您可以通过文件系统上的技巧缩短此过程的持续时间：将*.ibd*文件链接到一个大小为零的文件，然后手动删除文件，而不是等待 MySQL 执行此操作。

当您启用此选项时，每个表在 InnoDB 内部都有自己的表空间。事实证明，删除表空间实际上需要 InnoDB 锁定并扫描缓冲池，同时查找属于该表空间的页面，在具有大缓冲池的服务器上非常慢。如果使用innodb_buffer_pool_instances将缓冲池分成多个部分，这将得到改善。

在 MySQL 的各个版本中已经应用了几个修复程序。截至 8.0.23，这不应再是一个问题。

最终的建议是什么？我们建议您使用innodb_file_per_table并限制共享表空间的大小，以使您的生活更轻松。如果遇到任何使这变得痛苦的情况，如前所述，请考虑我们建议的其中一种修复方法。

旧的行版本和表空间

在写入密集的环境中，InnoDB 的表空间可能会变得非常大。如果事务保持打开状态很长时间（即使它们没有执行任何工作），并且它们使用默认的REPEATABLE READ事务隔离级别，InnoDB 将无法删除旧的行版本，因为未提交的事务仍需要能够查看它们。InnoDB 将旧版本存储在表空间中，因此随着更新更多数据，它将继续增长。清除过程是多线程的，但如果您遇到清除滞后问题（innodb_purge_threads和innodb_purge_batch_size），可能需要对工作负载进行调整。

SHOW INNODB STATUS的输出可以帮助您准确定位问题。查看TRANSACTIONS部分中的历史列表长度；它显示了撤销日志的大小：

------------
TRANSACTIONS
------------
Trx id counter 1081043769321
Purge done for trx's n:o < 1081041974531 undo n:o < 0 state: running but idle
History list length 697068
1
2
3
4
5
6

如果您有一个大的撤销日志，并且您的表空间因此而增长，您可以强制 MySQL 减慢速度，以便 InnoDB 的清除线程跟得上。这听起来可能不那么吸引人，但没有其他选择。否则，InnoDB 将继续写入数据并填满您的磁盘，直到磁盘耗尽空间或表空间达到您定义的限制。

要限制写入速度，将innodb_max_purge_lag变量设置为非0值。此值表示在 InnoDB 开始延迟更新数据的进一步查询之前，可以等待清除的最大事务数。您需要了解您的工作负载以决定一个好的值。举个例子，如果您的平均事务影响 1 KB 的行，并且您的表空间可以容忍 100 MB 的未清除行，您可以将该值设置为100000。

请记住，未清除的行版本会影响所有查询，因为它们实际上会使您的表和索引变得更大。如果清除线程无法跟上，性能可能会下降。设置innodb_max_purge_lag变量也会降低性能，但这是两害相权取其轻的选择。

其他 I/O 配置选项

sync_binlog选项控制 MySQL 将二进制日志刷新到磁盘的方式。其默认值为1，这意味着 MySQL 将执行刷新并保持二进制日志持久和安全。这是推荐的设置，我们警告您不要将其设置为其他任何值。

如果您不将sync_binlog设置为1，很可能会导致崩溃使您的二进制日志与事务数据不同步。这很容易破坏复制并使恢复变得不可能，特别是如果您的数据库正在使用全局事务 ID（更多信息请参见第九章）。保持此设置为1提供的安全性远远超过产生的 I/O 性能惩罚。

我们在第四章中更深入地讨论了 RAID，但在这里值得重申，具有设置为使用写回策略的带电池支持写缓存的高质量 RAID 控制器可以处理成千上万次写入，并仍然为您提供持久性存储。数据被写入一个带电池的快速缓存中，因此即使系统断电，数据也会存活。当电源恢复时，RAID 控制器将从缓存中将数据写入磁盘，然后使磁盘可供使用。因此，具有足够大的带电池支持写缓存的良好 RAID 控制器可以显着提高性能，并且是非常值得投资的。当然，固态存储也是目前推荐的解决方案，可以显著提高 I/O 性能。

配置 MySQL 并发性

当您在高并发工作负载中运行 MySQL 时，您可能会遇到在其他情况下不会遇到的瓶颈。本节解释了如何在发生这些问题时检测这些问题，并如何在这些工作负载下获得最佳性能。

如果您在 InnoDB 并发性方面遇到问题，并且您的 MySQL 版本低于 5.7，解决方案通常是升级服务器。旧版本仍存在许多高并发性可扩展性挑战。所有排队在全局互斥体上，如缓冲池互斥体，服务器实际上几乎停滞不前。如果您升级到较新版本的 MySQL，大多数情况下不需要限制并发性。

如果发现自己遇到了这个瓶颈，最好的选择是对数据进行分片。如果分片不是可行的解决方案，可能需要限制并发性。InnoDB 有自己的“线程调度器”，控制着线程如何进入其内核以访问数据以及它们在内核内部可以做什么。限制并发性的最基本方法是使用innodb_thread_concurrency变量，它限制了同时可以在内核中的线程数量。值为0表示线程数量没有限制。如果在较旧的 MySQL 版本中遇到 InnoDB 并发问题，那么这个变量是最重要的一个需要配置的。

MySQL 的在线文档提供了这里配置的最佳指南。您将不得不进行实验，找到适合您系统的最佳值，但我们建议从将innodb_thread_concurrency设置为可用 CPU 核心数量开始，然后根据需要进行调整。

如果已经有超过允许数量的线程在内核中，线程就无法进入内核。InnoDB 使用两阶段过程尝试让线程尽可能高效地进入。两阶段策略减少了由操作系统调度程序引起的上下文切换开销。线程首先休眠innodb_thread_sleep_delay微秒，然后再次尝试。如果仍然无法进入，它将进入等待线程队列，并让出给操作系统。

第一阶段的默认睡眠时间为 10,000 微秒。在高并发环境下，当 CPU 未充分利用且有大量线程处于“进入队列前休眠”状态时，更改此值可能有所帮助。如果有大量小查询，那么默认值可能过大，因为它会增加查询延迟。

一旦线程进入内核，它就有一定数量的“票”，让它可以“免费”地重新进入内核，而无需进行任何并发检查。这限制了它在必须重新排队与其他等待线程之前可以完成的工作量。innodb_concurrency_tickets选项控制票的数量。除非有大量运行时间极长的查询，否则很少需要更改。票据是按查询而不是按事务授予的。一旦查询完成，未使用的票据将被丢弃。

除了缓冲池和其他结构中的瓶颈外，在提交阶段还存在另一个并发瓶颈，这主要是由于刷新操作而导致的 I/O 绑定。innodb_commit_concurrency变量控制着同时可以提交的线程数量。如果即使将innodb_thread_concurrency设置为较低值时仍然存在大量线程抖动，配置此选项可能会有所帮助。

安全设置

在基本配置设置完成后，您可能希望启用一些使服务器更安全和可靠的设置。其中一些会影响性能，因为安全性和可靠性通常更昂贵。但有些只是明智的：它们防止插入荒谬数据到服务器中。还有一些在日常运营中没有影响，但可以防止在边缘情况下发生糟糕的事情。

让我们首先看一些通用服务器行为的有用选项集：

max_connect_errors

如果您��网络出现问题一小段时间，存在应用程序或配置错误，或者有其他问题导致连接在短时间内无法成功完成，客户端可能会被阻塞，并且无法再次连接，直到刷新主机缓存。此选项的默认设置（100）太小，以至于这个问题可能会太容易发生。您可能希望增加它，实际上，如果您知道服务器已充分防范了暴力攻击，您可以将其设置得非常大，以有效地禁用由于连接错误而阻止主机的功能。但是，如果启用了skip_name_resolve，max_connect_errors选项将不起作用，因为其行为取决于主机缓存，而skip_name_resolve已禁用了主机缓存。

max_connections

此设置就像一个紧急刹车，防止您的服务器被应用程序的连接激增所淹没。如果应用程序表现不佳或服务器遇到问题，如停顿，可能会打开大量新连接。但如果无法执行查询，那么打开连接是没有用的，因此被拒绝并显示“连接过多”错误是一种快速失败和廉价失败的方式。

将max_connections设置得足够高，以容纳您认为会经历的常规负载以及一个安全裕度，以便登录和管理服务器。例如，如果您认为在正常运作中会有大约 300 个连接，您可能会将其设置为 500 左右。如果您不知道会有多少连接，500 也不是一个不合理的起点。默认值为 151，但对许多应用程序来说这是不够的。

还要注意可能导致连接限制的意外情况。例如，如果重新启动应用程序服务器，它可能不会干净地关闭其连接，而 MySQL 可能不会意识到这些连接已关闭。当应用程序服务器重新启动并尝试打开到数据库的连接时，可能会因尚未超时的死连接而被拒绝。如果您不使用持久连接，且您的应用程序没有正常断开连接，这也可能发生。服务器将保留连接直到达到 TCP 超时或者在最坏的情况下，直到使用wait_timeout配置的秒数。

随着时间的推移，观察max_used_connections状态变量。这是一个高水位标记，显示服务器是否在某个时间点出现了连接激增。如果达到max_connections，很有可能至少有一个客户端被拒绝过。

skip_name_resolve

此设置禁用了另一个与网络和身份验证相关的陷阱：DNS 查找。DNS 是 MySQL 连接过程中的一个弱点。当您连接到服务器时，默认情况下会尝试确定您连接的主机名，并将其用作身份验证凭据的一部分（也就是说，您的凭据是您的用户名、主机名和密码，而不仅仅是用户名和密码）。但要验证您的主机名，服务器需要执行一个正向确认的反向 DNS 查找（或“双重反向 DNS 查找”），在接受连接之前需要进行反向和正向 DNS 查找。这一切都很好，直到 DNS 开始出现问题，这在某个时间点几乎是肯定的。当发生这种情况时，一切都会积累起来，最终连接会超时。为了防止这种情况发生，我们强烈建议您设置此选项，该选项在身份验证期间禁用 DNS 查找。但是，如果您这样做，您将需要将所有基于主机名的授权转换为使用 IP 地址、通配符或特殊主机名“localhost”，因为基于主机名的帐户将被禁用。

sql_mode

此设置可以接受多种修改服务器行为的选项。我们不建议仅仅出于好玩而更改这些设置；最好让 MySQL 保持 MySQL 的大部分特性，不要试图使其像其他数据库服务器一样运行。（许多客户端和 GUI 工具期望 MySQL 具有自己的 SQL 风格，例如，如果您将其更改为使用更符合 ANSI 标准的 SQL，可能会导致某些功能出现问题。）但是，其中几个设置非常有用，某些情况下可能值得考虑。过去，MySQL 通常对sql_mode非常宽松，但在后续版本中变得更加严格。

但是，请注意，对于现有应用程序更改这些设置可能不是一个好主意，因为这样做可能会使服务器与应用程序的期望不兼容。例如，人们很常见地无意中编写查询，引用了GROUP BY子句中不存在的列或使用聚合函数，因此，如果您想启用ONLY_FULL_GROUP_BY选项，最好先在开发或分段服务器上执行，只有在确保一切正常运行后才在生产环境中部署。

此外，在计划升级数据库时，请务必检查默认sql_mode的更改。对此变量的更改可能与您现有的应用程序不兼容，您需要提前进行测试。我们在附录 A 中更详细地讨论升级问题。

sysdate_is_now

这是另一个可能与应用程序期望不兼容的设置。但是，如果您不明确希望SYSDATE()函数具有非确定性行为，这可能会破坏复制并使来自备份的时间点恢复不可靠，您可能希望启用此选项并使其行为确定性。

read_only和super_read_only

read_only选项防止非特权用户在副本上进行更改，副本应该仅通过复制接收更改，而不是来自应用程序。我们强烈建议将副本设置为只读模式。

还有一个更为严格的只读选项，super_read_only，即使具有SUPER特权的用户也无法写入数据。启用此选项后，唯一可以向数据库写入更改的是复制。我们还强烈建议启用super_read_only。这将防止您意外使用管理员帐户向只读副本写入数据，使其不同步。

高级 InnoDB 设置

一些 InnoDB 选项对服务器性能非常重要，还有一些安全选项：

innodb_autoinc_lock_mode

此选项控制 InnoDB 如何生成自增主键值，这在某些情况下可能成为瓶颈，例如高并发插入。如果有许多事务在等待自增锁（您可以在SHOW ENGINE INNODB STATUS中看到此信息），则应该调查此设置。我们不会重复手册对选项及其行为的解释。

innodb_buffer_pool_instances

此设置在 MySQL 5.5 及更高版本中将缓冲池分成多个段，可能是改善 MySQL 在具有高并发工作负载的多核机器上的可伸缩性的最重要方法之一。多个缓冲池将工作负载分区，以便一些全局互斥锁不会成为如此热点争用点。

innodb_io_capacity

InnoDB 过去被硬编码为假定其在能够进行一百次 I/O 操作的单个硬盘上运行。这是一个糟糕的默认设置。现在您可以告知 InnoDB 可用的 I/O 容量。有时 InnoDB 需要将此设置设置得非常高（例如在极快的存储设备上，如 PCIe 闪存设备上设置为数万次）以稳定地刷新脏页，原因相当复杂，难以解释。⁴

innodb_read_io_threads和innodb_write_io_threads

这些选项控制了可用于 I/O 操作的后台线程数量。在 MySQL 的最新版本中，默认设置为四个读线程和四个写线程，对于许多服务器来说已经足够了，尤其是自 MySQL 5.5 以来提供了本机异步 I/O。如果您有许多硬盘和高并发工作负载，并且发现线程难以跟上，您可以增加线程数量，或者您可以简单地将它们设置为您用于 I/O 的物理磁盘数量（即使它们位于 RAID 控制器后面）。

innodb_strict_mode

此设置使 InnoDB 在某些情况下（尤其是无效或可能危险的CREATE TABLE选项）抛出错误而不是警告。如果您启用此选项，请务必检查所有的CREATE TABLE选项，因为它可能不允许您创建一些以前可以的表。有时它有点悲观和过于限制性。您不希望在尝试恢复备份时才发现这一点。

innodb_old_blocks_time

InnoDB 有一个两部分��冲池 LRU 列表，旨在防止临时查询驱逐长期多次使用的页面。一次性查询（例如 mysqldump 发出的查询）通常会将页面带入缓冲池 LRU 列表，从中读取行，并继续下一个页面。理论上，两部分 LRU 列表将防止此页面驱逐需要长期存在的页面，将其放入“年轻”子列表中，并在多次访问后才将其移至“老”子列表。但默认情况下，InnoDB 未配置为防止这种情况发生，因为页面有多行，因此多次访问以读取行将导致其立即移至“老”子列表，对需要长寿命的页面施加压力。此变量指定页面从 LRU 列表的“年轻”部分移至“老”部分之前必须经过的毫秒数。默认设置为0，将其设置为一个小值，例如1000（一秒），在我们的基准测试中已被证明非常有效。

摘要

在您完成本章后，您应该拥有比默认配置好得多的服务器配置。您的服务器应该快速稳定，除非遇到异常情况，否则您不需要调整配置。

总结一下，我们建议您从我们的示例配置文件开始，为您的服务器和工作负载设置基本选项，并根据需要添加安全选项。这确实是您需要做的全部。

如果您正在运行专用数据库服务器，那么您可以设置的最佳选项是innodb_dedicated_server，它处理了您 90% 的性能配置。如果您无法使用此选项，则最重要的选项是这两个：

innodb_buffer_pool_size
innodb_log_file_size

恭喜您——您刚刚解决了我们见过的绝大多数真实世界配置问题！

我们还提出了很多关于不要做什么的建议。其中最重要的是不要“调整”您的服务器，也不要使用比率、公式或“调整脚本”作为设置配置变量的依据。

¹ 例如，如果关闭 MySQL 的耐久性设置，它可以运行得非常快，但也会在崩溃时使您的数据容易丢失。

² 如果您还不相信“按比率调整”是不好的，请阅读 优化 Oracle 性能 一书，作者是 Cary Millsap 和 Jeff Holt（O’Reilly）。他们甚至在附录中专门讨论了这个主题，并提供了一个工具，可以人为生成任何您希望的缓存命中率，无论您的系统表现如何糟糕！当然，这都是为了说明比率是多么无用。

³ 请注意，MySQL 的各个版本会移除、弃用和更改一些选项；请查看文档以获取详细信息。

⁴ 有关后续阅读，请参阅 Percona 博客文章“给你的 SSD 一些爱—减少 innodb_io_capacity_max”，“Percona Server for MySQL 中的 InnoDB 刷新实践”，以及“针对写入密集型工作负载调整 MySQL/InnoDB 刷新”。

第六章：模式设计与管理

良好的逻辑和物理设计是高性能的基石，您必须为您将运行的特定查询设计模式。这通常涉及权衡。例如，反规范化的模式可以加快某些类型的查询，但会减慢其他查询。添加计数器和汇总表是优化查询的好方法，但维护它们可能很昂贵。MySQL 的特定功能和实现细节在很大程度上影响这一点。

同样，您的模式将随着时间的推移而发展—这是由于您了解如何存储和访问数据以及您的业务需求随时间变化的结果。这意味着您应该计划模式更改作为频繁事件。在本章后面，我们将指导您如何避免这一活动成为组织的运营瓶颈。

本章节以及接下来专注于索引的章节，涵盖了 MySQL 特定的模式设计部分。我们假设您知道如何设计数据库，因此这不是一个介绍性的章节，甚至不是一个关于数据库设计的高级章节。作为一章关于 MySQL 数据库设计的章节，它关于在使用 MySQL 而不是其他关系数据库管理系统（RDBMS）设计数据库时有何不同。如果您需要学习数据库设计的基础知识，我们建议阅读 Clare Churcher 的书籍Beginning Database Design（Apress）。

本章是接下来两章的准备。在这三章中��我们将探讨逻辑设计、物理设计和查询执行的互动。这需要一个全局的方法以及对细节的关注。您需要了解整个系统，以了解每个部分如何影响其他部分。在阅读关于索引的第七章和关于查询优化的第八章之后，您可能会发现重新阅读本章很有用。许多讨论的主题不能孤立考虑。

选择最佳数据类型

MySQL 支持各种各样的数据类型，选择正确的类型来存储您的数据对于获得良好的性能至关重要。以下简单的准则可以帮助您做出更好的选择，无论您存储的是什么类型的数据：

通常情况下，较小的通常更好

一般来说，尽量使用可以正确存储和表示您的数据的最小数据类型。较小的数据类型通常更快，因为它们在磁盘、内存和 CPU 缓存中占用的空间更少。它们通常也需要更少的 CPU 周期来处理。

但是，请确保不要低估您需要存储的值的范围，因为在模式的多个位置增加数据类型范围可能是一个痛苦且耗时的操作。如果您对使用哪种数据类型最好感到犹豫，请选择您认为不会超出的最小数据类型。（如果系统不是非常繁忙或存储的数据不多，或者如果您处于设计过程的早期阶段，您可以稍后轻松更改。）

简单就是好

处理简单数据类型通常需要较少的 CPU 周期。例如，整数比字符更便宜，因为字符集和排序规则使字符比较复杂。以下是两个例子：您应该将日期和时间存储在 MySQL 的内置类型中，而不是作为字符串，您应该使用整数存储 IP 地址。我们稍后会进一步讨论这些主题。

尽量避免NULL

许多表包括可空列，即使应用程序不需要存储NULL（值的缺失），仅仅因为这是默认值。通常最好将列指定为NOT NULL，除非您打算在其中存储NULL。对于引用可空列的查询，MySQL 更难优化，因为它们使索引、索引统计信息和值比较变得更加复杂。可空列使用更多的存储空间，并且需要 MySQL 内部的特殊处理。将NULL列更改为NOT NULL的性能改进通常很小，因此除非知道它们会引起问题，否则不要将其视为现有模式中要查找和更改的优先事项。

决定为给定列使用什么数据类型的第一步是确定适当的一般类型类别：数字、字符串、时间戳。这通常很简单，但我们提到了一些选择不直观的特殊情况。

下一步是选择具体类型。MySQL 的许多数据类型可以存储相同类型的数据，但在它们可以存储的值范围、允许的精度或所需的物理空间（在磁盘和内存中）方面有所不同。一些数据类型还具有特殊的行为或属性。

例如，DATETIME和TIMESTAMP列可以存储相同类型的数据：日期和时间，精确到一秒。然而，TIMESTAMP仅使用一半的存储空间，具有时区感知能力，并具有特殊的自动更新功能。另一方面，它的可允许值范围要小得多，有时其特殊功能可能成为一种障碍。

我们在这里讨论基本数据类型。MySQL 支持许多别名以实现兼容性，例如INTEGER（映射到INT）、BOOL（映射到TINYINT）和NUMERIC（映射到DECIMAL）。这些只是别名。它们可能会让人困惑，但不会影响性能。如果您使用别名数据类型创建表，然后检查SHOW CREATE TABLE，您会看到 MySQL 报告基本类型，而不是您使用的别名。

整数

有两种数字：整数和实数（带有小数部分的数字）。如果您要存储整数，请使用整数类型之一：TINYINT、SMALLINT、MEDIUMINT、INT或BIGINT。它们分别需要 8、16、24、32 和 64 位的存储空间。它们可以存储从−2^((N – 1))到 2^((N – 1)) − 1 的值，其中N是它们使用的存储空间的位数。

整数类型可以选择具有UNSIGNED属性，该属性不允许负值，并且可以将您可以存储的正值的上限大约加倍。例如，TINYINT UNSIGNED可以存储范围从 0 到 255 的值，而不是从−128 到 127。

有符号和无符号类型使用相同的存储空间并具有相同的性能，因此使用适合您数据范围的类型。

您的选择决定了 MySQL 如何存储数据，无论是在内存中还是在磁盘上。然而，整数计算通常使用 64 位的BIGINT整数。（有一些聚合函数的例外，它们使用DECIMAL或DOUBLE执行计算。）

MySQL 允许您为整数类型指定“宽度”，例如INT(11)。对于大多数应用程序来说，这是没有意义的：它不限制合法值的范围，而只是指定 MySQL 交互工具（如命令行客户端）为显示目的保留的字符数。对于存储和计算目的，INT(1)与INT(20)是相同的。

实数

实数是具有小数部分的数字。但它们不仅仅适用于小数；您还可以使用DECIMAL存储太大以至于不适合BIGINT的整数。MySQL 支持精确和不精确类型。

FLOAT 和 DOUBLE 类型支持使用标准浮点数运算进行近似计算。如果您需要准确了解浮点数结果是如何计算的，您需要研究您平台的浮点数实现。

您可以通过几种方式指定浮点列的所需精度，这可能会导致 MySQL 在存储值时选择不同的数据类型或在存储值时对其进行四舍五入。这些精度限定符是非标准的，因此我们建议您指定您想要的类型，但不指定精度。

浮点类型通常使用比DECIMAL更少的空间来存储相同范围的值。FLOAT列使用 4 个字节的存储空间。DOUBLE使用 8 个字节，具有比FLOAT更高的精度和更大的值范围。与整数一样，您只是选择存储类型；MySQL 在浮点类型的内部计算中使用DOUBLE。

由于额外的空间要求和计算成本，仅当您需要对分数进行精确计算时才应使用DECIMAL，例如，当存储财务数据时。但在某些高交易量情况下，实际上更合理的是使用BIGINT，并将数据存储为您需要处理的最小货币分数的某个倍数。假设您需要将财务数据存储到千分之一美分。您可以将所有美元金额乘以一百万，并将结果存储在BIGINT中，避免浮点存储的不精确性和精确DECIMAL数学的成本。

字符串类型

MySQL 支持多种字符串数据类型，每种类型都有许多变体。每个字符串列都可以有自己的字符集和该字符集的排序规则，或者排序规则。

`VARCHAR` 和 `CHAR` 类型

两种主要的字符串类型是VARCHAR和CHAR，它们存储字符值。不幸的是，很难准确解释这些值在磁盘和内存中是如何存储的，因为实现取决于存储引擎。我们假设您正在使用 InnoDB；如果不是，请阅读您存储引擎的文档。

让我们看看VARCHAR和CHAR值通常是如何存储在磁盘上的。请注意，存储引擎可能会以不同于内存中存储CHAR或VARCHAR值的方式来存储该值在磁盘上的方式，并且服务器在从存储引擎检索值时可能会将该值转换为另一种存储格式。以下是这两种类型的一般比较：

VARCHAR

VARCHAR 存储可变长度的字符字符串，是最常见的字符串数据类型。它可能需要比固定长度类型更少的存储空间，因为它只使用所需的空间（即，存储较短值时使用的空间较少）。

VARCHAR 使用 1 或 2 个额外字节来记录值的长度：如果列的最大长度为 255 字节或更少，则使用 1 个字节，如果超过 255 字节，则使用 2 个字节。假设使用latin1字符集，VARCHAR(10)将使用最多 11 个字节的存储空间。VARCHAR(1000)最多可以使用 1,002 个字节，因为它需要 2 个字节来存储长度信息。

VARCHAR 有助于性能，因为它节省空间。但是，由于行是可变长度的，当您更新它们时，它们可能会增长，这可能会导致额外的工作。如果一行增长并且不再适合其原始位置，则行为取决于存储引擎。例如，InnoDB 可能需要拆分页面以将行放入其中。其他存储引擎可能根本不会在原地更新数据。

当最大列长度远大于平均长度时，很少更新字段，因此碎片化不是问题时，以及使用复杂字符集（如 UTF-8）时，通常值得使用VARCHAR。

对于 InnoDB 来说情况就比较棘手了，它可以将长VARCHAR值存储为 BLOB。我们稍后会讨论这个问题。

CHAR

CHAR 是固定长度的：MySQL 总是为指定数量的字符分配足够的空间。在存储 CHAR 值时，MySQL 会移除任何尾随空格。根据需要填充值以进行比较。

如果您希望存储非常短的字符串或所有值几乎相同长度，则 CHAR 是一个不错的选择。例如，对于用户密码的 MD5 值，它们始终是相同长度，CHAR 是一个不错的选择。对于经常更改的数据，CHAR 也比 VARCHAR 更好，因为固定长度的行不容易出现碎片化。对于非常短的列，CHAR 也比 VARCHAR 更有效率；一个设计为仅容纳 Y 和 N 值的 CHAR(1) 在单字节字符集中只会使用 1 个字节，但 VARCHAR(1) 会使用 2 个字节，因为长度字节。

这种行为可能有点令人困惑，因此我们将通过一个示例来说明。首先，我们创建一个具有单个 CHAR(10) 列的表，并将一些值存储在其中：

mysql> CREATE TABLE char_test( char_col CHAR(10));
mysql> INSERT INTO char_test(char_col) VALUES
    -> ('string1'), (' string2'), ('string3 ');

1
2
3
4

当我们检索这些值时，尾随空格已被去除：

mysql> SELECT CONCAT("'", char_col, "'") FROM char_test;
+----------------------------+
| CONCAT("'", char_col, "'") |
+----------------------------+
| 'string1'                  |
| ' string2'                 |
| 'string3'                  |
+----------------------------+
1
2
3
4
5
6
7
8

如果我们将相同的值存储在 VARCHAR(10) 列中，我们在检索时会得到以下结果，其中 string3 上的尾随空格未被移除：

mysql> SELECT CONCAT("'", varchar_col, "'") FROM varchar_test;
+-------------------------------+
| CONCAT("'", varchar_col, "'") |
+-------------------------------+
| 'string1'                     |
| ' string2'                    |
| 'string3 '                    |
+-------------------------------+
1
2
3
4
5
6
7
8

CHAR 和 VARCHAR 的兄弟类型是 BINARY 和 VARBINARY，它们存储二进制字符串。二进制字符串与传统字符串非常相似，但它们存储的是字节而不是字符。填充也不同：MySQL 使用 \0（零字节）而不是空格填充 BINARY 值，并且在检索时不会去除填充值。

当您需要存储二进制数据并希望 MySQL 将值作为字节而不是字符进行比较时，这些类型非常有用。按字节进行比较的优势不仅仅是大小写不敏感的问题。MySQL 实际上是逐个字节比较 BINARY 字符串，根据每个字节的数值进行比较。因此，二进制比较比字符比较要简单得多，因此更快。

BLOB 和 TEXT 类型

BLOB 和 TEXT 是设计用于分别存储大量数据的字符串数据类型，作为二进制或字符字符串。

实际上，它们各自是数据类型系列：字符类型有 TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT，而二进制类型有 TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。BLOB 是 SMALLBLOB 的同义词，TEXT 是 SMALLTEXT 的同义词。

与所有其他数据类型不同，MySQL 将每个 BLOB 和 TEXT 值视为具有自己身份的对象。存储引擎通常会对它们进行特殊存储；当它们很大时，InnoDB 可能会为它们使用单独的“外部”存储区域。每个值在行中需要占用 1 到 4 个字节的存储空间，并且在外部存储中需要足够的空间来实际保存该值。

BLOB 和 TEXT 族之间唯一的区别是，BLOB 类型存储没有排序规则或字符集的二进制数据，而 TEXT 类型具有字符集和排序规则。

MySQL 对 BLOB 和 TEXT 列的排序与其他类型不同：它只对这些列的前 max_sort_length 字节进行排序，而不是对整个字符串进行排序。如果需要按照前几个字符进行排序，可以减少 max_sort_length 服务器变量。

MySQL 无法对这些数据类型的完整长度进行索引，也无法使用索引进行排序。

使用 ENUM 而不是字符串类型

有时您可以使用 ENUM 列代替传统的字符串类型。ENUM 列可以存储一组预定义的不同字符串值。MySQL 将它们非常紧凑地存储在 1 或 2 个字节中，具体取决于列表中值的数量。它将每个值内部存储为表示其在字段定义列表中位置的整数。以下是一个示例：

mysql> CREATE TABLE enum_test(
    -> e ENUM('fish', 'apple', 'dog') NOT NULL
    -> );
mysql> INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple');

1
2
3
4
5

这三行实际上存储的是整数，而不是字符串。您可以在数字上下文中检索它们，看到值的双重性质：

mysql> SELECT e + 0 FROM enum_test;
+-------+
| e + 0 |
+-------+
|     1 |
|     3 |
|     2 |
+-------+
1
2
3
4
5
6
7
8

如果您为 ENUM 常量指定数字，例如 ENUM('1', '2', '3')，这种二义性可能会让人困惑。我们建议您不要这样做。

另一个令人惊讶的是，ENUM 字段按内部整数值排序，而不是按字符串本身排序：

mysql> SELECT e FROM enum_test ORDER BY e;
+-------+
| e     |
+-------+
| fish  |
| apple |
| dog   |
+-------+
1
2
3
4
5
6
7
8

您可以通过按照希望排序的顺序指定 ENUM 成员来解决此问题。您还可以在查询中明确指定排序顺序使用 FIELD()，但这会阻止 MySQL 使用索引进行排序：

mysql> SELECT e FROM enum_test ORDER BY FIELD(e, 'apple', 'dog', 'fish');
+-------+
| e     |
+-------+
| apple |
| dog   |
| fish  |
+-------+
1
2
3
4
5
6
7
8

如果我们按字母顺序定义值，我们就不需要这样做了。

因为 MySQL 将每个值存储为整数并必须进行查找以将其转换为其字符串表示形式，所以 ENUM 列具有一些开销。通常，它们的较小大小可以抵消这种开销，但并非总是如此。特别是，将 CHAR 或 VARCHAR 列连接到 ENUM 列可能比连接到另一个 CHAR 或 VARCHAR 列慢。

为了说明，我们对我们的一个应用程序中的表执行了 MySQL 执行此类连接的速度基准测试。该表具有相当宽的主键：

CREATE TABLE webservicecalls (
 day date NOT NULL,
 account smallint NOT NULL,
 service varchar(10) NOT NULL,
 method varchar(50) NOT NULL,
 calls int NOT NULL,
 items int NOT NULL,
 time float NOT NULL,
 cost decimal(9,5) NOT NULL,
 updated datetime,
 PRIMARY KEY (day, account, service, method)
) ENGINE=InnoDB;
1
2
3
4
5
6
7
8
9
10
11
12

该表包含约 110,000 行，仅约 10 MB，因此完全适合内存。service 列包含 5 个不同的值，平均长度为 4 个字符，而 method 列包含 71 个值，平均长度为 20 个字符。

我们复制了此表，并将 service 和 method 列转换为 ENUM，如下所示：

CREATE TABLE webservicecalls_enum (
 ... omitted ...
 service ENUM(...values omitted...) NOT NULL,
 method ENUM(...values omitted...) NOT NULL,
 ... omitted ...
) ENGINE=InnoDB;
1
2
3
4
5
6

然后，我们通过主键列测量了连接表的性能。以下是我们使用的查询：

mysql> SELECT SQL_NO_CACHE COUNT(*)
    -> FROM webservicecalls
    -> JOIN webservicecalls USING(day, account, service, method);

1
2
3
4

我们修改了此查询以连接不同组合的 VARCHAR 和 ENUM 列。表 6-1 显示了结果。³

表 6-1. 连接 VARCHAR 和 ENUM 列的速度

测试	每秒查询数
`VARCHAR` 连接到 `VARCHAR`	2.6
`VARCHAR` 连接到 `ENUM`	1.7
`ENUM` 连接到 `VARCHAR`	1.8
`ENUM` 连接到 `ENUM`	3.5

在将列转换为 ENUM 后，连接速度更快，但将 ENUM 列连接到 VARCHAR 列则较慢。在这种情况下，转换这些列看起来是一个好主意，只要它们不必连接到 VARCHAR 列。在设计中，通常使用“查找表”与整数主键来避免在连接中使用基于字符的值。

然而，将列转换为 ENUM 还有另一个好处：根据 SHOW TABLE STATUS 中的 Data_length 列，将这两列转换为 ENUM 使表的大小减小了约三分之一。在某些情况下，即使 ENUM 列必须连接到 VARCHAR 列，这也可能是有益的。此外，转换后的主键本身大小仅为转换前的一半。由于这是一个 InnoDB 表，如果此表上有其他索引，减小主键大小也会使它们变得更小。

警告

虽然 ENUM 类型在存储值方面非常高效，但更改可以在 ENUM 中的有效值总是需要模式更改。如果您尚未拥有像我们稍后在本章中描述的自动化模式更改的强大系统，这种操作需求可能会成为一个主要不便，如果您的 ENUM 经常更改。我们稍后还会提到模式设计中的“太多 ENUM”反模式。

日期和时间类型

MySQL 有许多类型用于各种日期和时间值，例如 YEAR 和 DATE。MySQL 可以存储的时间的最细粒度是微秒。大多数时间类型没有替代方案，因此没有哪一个是最佳选择的问题。唯一的问题是当您需要同时存储日期和时间时该怎么办。MySQL 为此目的提供了两种非常相似的数据类型：DATETIME 和 TIMESTAMP。对于许多应用程序，任何一个都可以工作，但在某些情况下，一个比另一个更好。让我们来看一下：

DATETIME

这种类型可以存储大范围的值，从公元 1000 年到公元 9999 年，精度为一微秒。它将日期和时间打包成一个整数，格式为 YYYYMMDDHHMMSS，与时区无关。这使用了 8 个字节的存储空间。

默认情况下，MySQL 以可排序、明确的格式显示DATETIME值，例如 2008-01-16 22:37:08。这是表示日期和时间的 ANSI 标准方式。

TIMESTAMP

如其名称所示，TIMESTAMP类型存储了自 1970 年 1 月 1 日格林尼治时间（GMT）午夜以来经过的秒数，与 Unix 时间戳相同。TIMESTAMP只使用 4 个字节的存储空间，因此其范围比DATETIME要小得多：从 1970 年到 2038 年 1 月 19 日。MySQL 提供了FROM_UNIXTIME()和UNIX_TIMESTAMP()函数来将 Unix 时间戳转换为日期，反之亦然。

TIMESTAMP显示的值也取决于时区。MySQL 服务器、操作系统和客户端连接都有时区设置。

因此，存储值0的TIMESTAMP实际上显示为 1969-12-31 19:00:00 在东部标准时间（EST），它与 GMT 相差五个小时。值得强调的是：如果你从多个时区存储或访问数据，TIMESTAMP和DATETIME的行为将会有很大的不同。前者保留相对于使用的时区的值，而后者保留日期和时间的文本表示。

TIMESTAMP还具有DATETIME没有的特殊属性。默认情况下，当你插入一行而没有为第一个TIMESTAMP列指定值时，MySQL 会将第一个TIMESTAMP列设置为当前时间。MySQL 还默认情况下在更新行时更新第一个TIMESTAMP列的值，除非你在UPDATE语句中明确赋值。你可以为任何TIMESTAMP列配置插入和更新行为。最后，TIMESTAMP列默认为NOT NULL，这与其他每种数据类型都不同。

位压缩数据类型

MySQL 有一些存储类型，使用值内的单个位来紧凑存储数据。所有这些类型在技术上都是字符串类型，无论底层存储格式和操作如何：

BIT

你可以使用BIT列在单个列中存储一个或多个真/假值。BIT(1)定义一个包含单个位的字段，BIT(2)存储 2 位，依此类推；BIT列的最大长度为 64 位。InnoDB 将每个列存储为足以包含位的最小整数类型，因此你不会节省任何存储空间。

MySQL 将BIT视为字符串类型，而不是数值类型。当你检索一个BIT(1)值时，结果是一个字符串，但内容是二进制值 0 或 1，而不是 ASCII 值“0”或“1”。但是，如果你在数值上下文中检索值，结果将是位字符串转换为的数字。如果你将值b'00111001'（这是 57 的二进制等价值）存储到一个BIT(8)列中并检索它，你将得到包含字符代码 57 的字符串。这恰好是 ASCII 字符代码“9”。但在数值上下文中，你将得到值57：

mysql> CREATE TABLE bittest(a bit(8));
mysql> INSERT INTO bittest VALUES(b'00111001');
mysql> SELECT a, a + 0 FROM bittest;
+------+-------+
| a    | a + 0 |
+------+-------+
| 9    |    57 |
+------+-------+
1
2
3
4
5
6
7
8

这可能会非常令人困惑，因此我们建议您谨慎使用BIT。对于大多数应用程序，我们认为最好避免使用这种类型。

如果你想在一个存储空间中存储一个真/假值，另一个选项是创建一个可空的CHAR(0)列。这个列可以存储值的缺失（NULL）或零长度值（空字符串）。这在实践中是可行的，但可能会让其他人在使用数据库中的数据时感到晦涩，使编写查询变得困难。除非你非常专注于节省空间，否则我们仍建议使用TINYINT。

SET

如果您需要存储许多真/假值，请考虑将许多列合并为一个列，使用 MySQL 的原生SET数据类型，MySQL 在内部表示为一组位的紧凑集合。它使用存储效率高，MySQL 有函数如FIND_IN_SET()和FIELD()，使其在查询中易于使用。

整数列上的位运算

一个替代SET的方法是使用整数作为一组位的紧凑集合。例如，您可以在TINYINT中打包 8 位，并使用位运算符进行操作。您可以通过在应用程序代码中为每个位定义命名常量来简化此过程。

与SET相比，这种方法的主要优势在于您可以在不使用ALTER TABLE的情况下更改字段表示的“枚举”。缺点是您的查询更难编写和理解（当第 5 位设置时意味着什么？）。有些人习惯于位操作，有些人则不习惯，因此您是否想尝试这种技术在很大程度上取决于个人口味。

一个紧凑位的示例应用是存储权限的访问控制列表（ACL）。每个位或SET元素代表一个值，例如CAN_READ、CAN_WRITE或CAN_DELETE。如果您使用SET列，您将让 MySQL 在列定义中存储位到值的映射；如果您使用整数列，您将在应用程序代码中存储映射。以下是使用SET列的查询示例：

mysql> CREATE TABLE acl (
    -> perms SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL
    -> );
mysql> INSERT INTO acl(perms) VALUES ('CAN_READ,CAN_DELETE');
mysql> SELECT perms FROM acl WHERE FIND_IN_SET('CAN_READ', perms);
+---------------------+
| perms               |
+---------------------+
| CAN_READ,CAN_DELETE |
+---------------------+
1
2
3
4
5
6
7
8
9
10

如果您使用整数，可以将该示例写成如下形式：

mysql> SET @CAN_READ := 1 << 0,
    -> @CAN_WRITE := 1 << 1,
    -> @CAN_DELETE := 1 << 2;
mysql> CREATE TABLE acl (
    -> perms TINYINT UNSIGNED NOT NULL DEFAULT 0
    -> );
mysql> INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);
mysql> SELECT perms FROM acl WHERE perms & @CAN_READ;
+-------+
| perms |
+-------+
| 5     |
+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13

我们使用变量来定义值，但您可以在代码中使用常量代替。

JSON 数据

越来越普遍地使用 JSON 作为系统之间交换数据的格式。MySQL 有一个原生的 JSON 数据类型，使得直接在表内部操作 JSON 结构的部分变得容易。纯粹主义者可能会建议在数据库中存储原始 JSON 是一种反模式，因为理想情况下，模式是 JSON 中字段的表示。新手可能会看到 JSON 数据类型，并通过避免创建和管理独立字段来看到一条捷径。哪种方法更好在很大程度上是主观的，但我们将客观地通过呈现一个示例用例并比较查询速度和数据大小来进行比较。

我们的示例数据是由 NASA 提供的发现的 202 个近地小行星和彗��的列表。测试是在一个四核、16 GB RAM 虚拟机上的 MySQL 8.0.22 上进行的。数据示例：

 [
   {
      "designation":"419880 (2011 AH37)",
      "discovery_date":"2011-01-07T00:00:00.000",
      "h_mag":"19.7",
      "moid_au":"0.035",
      "q_au_1":"0.84",
      "q_au_2":"4.26",
      "period_yr":"4.06",
      "i_deg":"9.65",
      "pha":"Y",
      "orbit_class":"Apollo"
   }
]
1
2
3
4
5
6
7
8
9
10
11
12
13
14

这些数据代表了一个指定，发现日期，以及关于实体的收集的数据，包括数字和文本字段。

首先，我们将 JSON 中的数据集转换为每个条目一行。这导致了一个看起来相对简单的模式：

mysql> DESC asteroids_json;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| json_data | json | YES  |     | NULL    |       |
+-----------+------+------+-----+---------+-------+
1
2
3
4
5
6

其次，我们将此 JSON 数据转换为列，使用适当的数据类型进行转换。这导致了以下模式：

mysql> DESC asteroids_sql;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| designation    | varchar(30) | YES  |     | NULL    |       |
| discovery_date | date        | YES  |     | NULL    |       |
| h_mag          | float       | YES  |     | NULL    |       |
| moid_au        | float       | YES  |     | NULL    |       |
| q_au_1         | float       | YES  |     | NULL    |       |
| q_au_2         | float       | YES  |     | NULL    |       |
| period_yr      | float       | YES  |     | NULL    |       |
| i_deg          | float       | YES  |     | NULL    |       |
| pha            | char(3)     | YES  |     | NULL    |       |
| orbit_class    | varchar(30) | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

第一个比较是数据大小：

mysql> SHOW TABLE STATUS\G
*************************** 1\. row ***************************
 Name: asteroids_json
 Engine: InnoDB
 Version: 10
 Row_format: Dynamic
 Rows: 202
 Avg_row_length: 405
 Data_length: 81920
 Max_data_length: 0
 Index_length: 0

*************************** 2\. row ***************************
 Name: asteroids_sql
 Engine: InnoDB
 Version: 10
 Row_format: Dynamic
 Rows: 202
 Avg_row_length: 243
 Data_length: 49152
 Max_data_length: 0
 Index_length: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

我们的 SQL 版本使用了三个 16 KB 页面，而我们的 JSON 版本使用了五个 16 KB 页面。这并不令人感到意外。JSON 数据类型将使用更多空间来存储用于定义 JSON 的额外字符（大括号、方括号、冒号等）以及空格。在这个小例子中，通过将 JSON 转换为特定数据类型，数据存储的大小可以得到改善。

可能存在数据大小不那么重要的有效用例。这两者之间的查询延迟如何？

要在 SQL 中选择单个列的所有内容，我们的语法很简单：

SELECT designation FROM asteroids_sql;
1

在我们第一次运行此查询时，未被 InnoDB 缓冲池缓存，我们得到了 1.14 毫秒（ms）的结果。第二次执行，将其放入内存中，我们得到了 0.44 毫秒。

对于 JSON，我们能够访问 JSON 结构内的字段：

SELECT json_data->'$.designation' FROM asteroids_json
1

同样，我们的第一次执行，未缓存，执行时间为 1.13 毫秒。后续执行时间约为 0.80 毫秒。在这个执行速度下，我们预计会有一定的变化——我们在一个虚拟机环境中谈论数百微秒的差异。在我们看来，这两个查询都执行得相当快，尽管值得注意的是 JSON 查询仍然比 SQL 查询长大约两倍。

针对特定行的访问呢？对于单行查找，我们利用使用索引：

ALTER TABLE asteroids_sql ADD INDEX ( designation );
1

当我们进行单行查找时，我们的 SQL 版本运行时间为 0.33 毫秒，而我们的 JSON 版本运行时间为 0.58 毫秒，给予 SQL 版本优势。这很容易解释：我们的索引允许 InnoDB 返回 1 行而不是 202 行。

将索引查询与全表扫描进行比较是不公平的。为了公平竞争，我们需要使用生成列功能提取指定，并创建针对该虚拟生成列的索引：

ALTER TABLE asteroids_json ADD COLUMN designation VARCHAR(30) GENERATED ALWAYS AS
(json_data->"$.designation"), ADD INDEX ( designation );
1
2

这给我们的 JSON 表上的模式看起来像这样：

mysql> DESC asteroids_json;
+-------------+-------------+------+-----+---------+-------------------+
| Field       | Type        | Null | Key | Default | Extra             |
+-------------+-------------+------+-----+---------+-------------------+
| json_data   | json        | YES  |     | NULL    |                   |
| designation | varchar(30) | YES  | MUL | NULL    | VIRTUAL GENERATED |
+-------------+-------------+------+-----+---------+-------------------+
1
2
3
4
5
6
7

我们的模式现在从json_data列生成一个虚拟列用于指定，并对其进行索引。现在，我们重新运行我们的单行查找，使用索引列而不是 JSON 列路径运算符(->)。由于字段数据在 JSON 中被引用，我们需要在 SQL 中也引用它：

SELECT * FROM asteroids_json WHERE designation='"(2010 GW62)"';
1

这个查询在 0.4 毫秒内执行，与我们的 SQL 版本 0.33 毫秒相当接近。

从我们之前的简单测试案例中，使用的表空间量似乎是为什么你会使用 SQL 列而不是存储原始 JSON 文档的主要原因。速度仍然比 SQL 列更快。总的来说，选择使用本机 SQL 还是 JSON 取决于在数据库中存储 JSON 的便利性是否超过性能。如果你每天访问这些数据数百万次或数十亿次，速度差异将会累积。

选择标识符

一般来说，标识符是指引用行的方式，通常也是使其唯一的方式。例如，如果你有一个关于用户的表，你可能想为每个用户分配一个数字 ID 或一个唯一的用户名。这个字段可能是你的PRIMARY KEY的一部分或全部。

选择标识符列的良好数据类型非常重要。你更有可能将这些列与其他值进行比较（例如，在连接中），并将它们用于查找而不是其他列。你还可能在其他表中将它们用作外键，因此当你为标识符列选择数据类型时，你可能也在相关表中选择该类型。（正如我们在本章前面演示的那样，最好在相关表中使用相同的数据类型，因为你可能会用它们进行连接。）

在选择标识符列的类型时，你需要考虑的不仅是存储类型，还有 MySQL 如何在该类型上执行计算和比较。例如，MySQL 在内部将ENUM和SET类型存储为整数，但在字符串上下文中进行比较时将它们转换为字符串。

一旦选择了类型，请确保在所有相关表中使用相同的类型。类型应完全匹配，包括UNSIGNED等属性。⁵混合不同的数据类型可能会导致性能问题��即使没有问题，隐式类型转换在比较过程中也可能导致难以发现的错误。这些错误甚至可能在很久之后才出现，当你忘记你正在比较不同的数据类型时。

选择可以容纳所需值范围的最小大小，并在必要时留出未来增长的空间。例如，如果你有一个存储美国州名的state_id列，你不需要成千上万或数百万的值，所以不要使用INT。一个TINYINT应该足够，而且比较小 3 个字节。如果你在其他表中将这个值用作外键，3 个字节可能会产生很大的差异。这里我们给出一些建议。

整数类型

整数通常是标识符的最佳选择，因为它们快速且可以与AUTO_INCREMENT一起使用。AUTO_INCREMENT是一个为每一行生成新整数类型的列属性。例如，一个计费系统可能需要为每个客户生成一个新的发票。使用AUTO_INCREMENT意味着生成的第一张发票是 1，第二张是 2，依此类推。请注意，你应该确保为你预期的数据增长选择正确的整数大小。有不止一个关于由于意外耗尽整数而导致系统停机的故事。

ENUM 和 SET

ENUM和SET类型通常不适合作为标识符的选择，尽管它们可以用于包含状态或“类型”值的静态“定义表”。ENUM和SET列适合保存像订单状态或产品类型这样的信息。

举个例子，如果你使用ENUM字段来定义产品的类型，你可能希望有一个主键为相同ENUM字段的查找表。在这种情况下，你会想要将ENUM用作标识符，但对于大多数情况，你应该避免这样做。

字符串类型

如果可能的话，避免使用字符串类型作为标识符，因为它们占用大量空间，通常比整数类型慢。

你还应该非常小心完全“随机”的字符串，比如由MD5()、SHA1()或UUID()生成的字符串。你用它们生成的每个新值都会以任意方式分布在一个大空间中，这可能会减慢INSERT和某些类型的SELECT查询：⁶

它们会减慢INSERT查询，因为插入的值必须放在索引中的随机位置。这会导致页面分裂、随机磁盘访问以及聚集索引碎片化对于聚集存储引擎。
它们会减慢SELECT查询，因为逻辑上相邻的行在磁盘和内存中会被广泛分散。
随机值会导致缓存在所有类型的查询中表现不佳，因为它们破坏了引用局部性，这是缓存工作的方式。如果整个数据集都是“热点”，那么在内存中缓存任何特定部分的数据都没有优势，如果工作集不适合内存，缓存将有很多刷新和未命中。

如果你存储通用唯一标识符（UUID）值，你应该去掉破折号，或者更好的是，使用UNHEX()将 UUID 值转换为 16 字节数字，并将其存储在BINARY(16)列中。你可以使用HEX()函数以十六进制格式检索值。

特殊数据类型

有些数据类型与可用的内置类型不直接对应。一个很好的例子是 IPv4 地址。人们经常使用VARCHAR(15)列来存储 IP 地址。然而，它们实际上是无符号 32 位整数，而不是字符串。点分四进制表示法只是一种让人类更容易阅读的写法。你应该将 IP 地址存储为无符号整数。MySQL 提供了INET_ATON()和INET_NTOA()函数来在这两种表示法之间转换。使用无符号 32 位整数，空间使用量从VARCHAR(15)的约 16 字节缩减到 4 字节。如果你担心数据库中的可读性，并且不想继续使用函数查看行数据，记住 MySQL 有视图，你可以使用它们更轻松地查看数据。

MySQL 中的模式设计陷阱

尽管有普遍的好坏设计原则，但也存在由于 MySQL 的实现方式而引起的问题，这意味着你也可能犯 MySQL 特定的错误。本节讨论了我们在 MySQL 模式设计中观察到的问题。这可能有助于你避免这些错误，并选择与 MySQL 特定实现更好配合的替代方案。

太多列

MySQL 的存储引擎 API 通过在行缓冲格式中在服务器和存储引擎之间复制行；然后服务器将缓冲区解码为列。将行缓冲区转换为具有解码列的行数据结构可能是昂贵的。InnoDB 的行格式总是需要转换的。这种转换的成本取决于列的数量。当我们调查一个具有极宽表（数百列）的客户的高 CPU 消耗问题时，我们发现这可能会变得昂贵，尽管实际上只使用了少数列。如果您计划使用数百列，请注意服务器的性能特性将有所不同。

太多的连接

所谓的实体-属性-值（EAV）设计模式是一个经典的普遍糟糕的设计模式，特别在 MySQL 中效果不佳。MySQL 对于每个连接有 61 个表的限制，而 EAV 数据库需要许多自连接。我们看到许多 EAV 数据库最终超过了这个限制。然而，即使比 61 少得多的连接，规划和优化查询的成本对于 MySQL 来说可能会成为问题。作为一个粗略的经验法则，如果您需要查询以非常快的速度和高并发性执行，最好每个查询有十几个或更少的表。

万能的 ENUM

谨防过度使用ENUM。这是我们看到的一个例子：

CREATE TABLE ... (
 country enum(','0','1','2',...,'31')
1
2

模式中大量使用了这种模式。在任何具有枚举值类型的数据库中，这可能是一个值得怀疑的设计决定，因为它实际上应该是一个整数，可以作为“字典”或“查找”表的外键。

伪装的 ENUM

ENUM允许列保存来自一组定义值中的一个值。SET允许列保存来自一组定义值中的一个或多个值。有时这些可能很容易混淆。这里有一个例子：

CREATE TABLE ...(
 is_default set('Y','N') NOT NULL default 'N'
1
2

那几乎肯定应该是一个ENUM而不是一个SET，假设它不能同时为真和假。

NULL 并非在这里发明

我们之前提到避免使用NULL的好处，确实，我们建议在可能的情况下考虑替代方案。即使您需要在表中存储“无值”事实时，也许您并不需要使用NULL。也许您可以使用零、一个特殊值或空字符串代替。

然而，您也可以走向极端。当您需要表示未知值时，不要太害怕使用NULL。在某些情况下，使用NULL比使用神奇的常量更好。从受限类型的域中选择一个值，比如使用−1 表示未知整数，可能会使您的代码变得复杂，引入错误，并且总体上会搞得一团糟。处理NULL并不总是容易的，但通常比其他替代方案更好。

这是我们经常看到的一个例子：

CREATE TABLE ... (
 dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
1
2

那个虚假的全零值可能会引起很多问题。（您可以配置 MySQL 的SQL_MODE来禁止无意义的日期，这对于尚未创建满是错误数据的新应用程序来说是一个特别好的做法。）

在相关主题上，MySQL 确实对NULL进行索引，而 Oracle 不会在索引中包含非值。

现在我们已经讨论了很多关于数据类型、如何选择它们以及不应该做什么的实用建议，让我们继续讨论另一个好的、迭代的模式设计：模式管理。

模式管理

运行模式更改是数据库工程师必须承担的最常见任务之一。当你开始运行数十甚至数百个具有不同业务背景和不断发展功能的数据库实例时，你希望小心处理这些模式更改不会成为整个组织的瓶颈，但仍然安全地进行，并且不会干扰运营。本节将介绍如何将模式更改管理视为“数据存储平台”的一部分，应该指导这一策略的核心价值观，你可以引入哪些工具来实现这一策略，以及如何将其与更大的软件交付生命周期结合起来。

模式管理作为数据存储平台的一部分

如果你与任何快速增长组织的工程领导人交谈，你会发现工程师速度和从特性设计到在生产环境中运行的时间是他们优化事项的首要任务。在这种情况下，你在规划规模化管理模式时的任务是不允许模式管理成为一个手动过程，这会成为整个工程组织进展的瓶颈，只有一个或几个人。

为成功设置合作团队

随着组织中依赖 MySQL 实例的团队数量增加，你希望始终成为这些团队成功的推动者，而不是他们需要通过才能完成工作的门槛。这也适用于模式更改，这意味着你希望创建一条路径来部署模式更改，而不是“只有数据库团队才能做到”。

集成模式管理与持续集成

在我们讨论了一些能够实现规模化模式管理的工具之后，我们将谈论如何将它们与 CI 管道集成。但现在我们想强调的是，如果你从这样一个前提开始，即模式更改将由特性团队而不仅仅是数据库团队来管理，那么你需要尽可能接近这些团队已经部署代码更改的工作流程。科学已经证明，将模式管理视为代码部署的团队经历了更积极的特性交付过程，并看到团队速度的提高。我们将讨论能够支持这种迭代的工具，考虑到软件交付实践。

模式更改的源代码控制

我们都在部署代码时使用源代码控制，对吧？那么为什么不也用于数据库模式应该是什么样子呢？在规模化模式管理的第一步是确保你有支持和跟踪所做更改的源代码控制。这不仅仅是一件好事™，而且在许多情况下，你的合规团队要求这样做，正如你将在第十三章中看到的。让我们来看看一些能够迭代数据库模式的工具。

注意

对于你的组织最大的价值，使用工程团队用于代码部署的相同 CI 工具。

付费选项

过去几年，作为企业工具的数据库模式管理领域发展迅速，特别是对于 MySQL 安装的支持增加。如果你正在寻找一个现成的解决方案来帮助你的组织管理模式更改，这里有一些你应该考虑的事项：

成本

成本模型各不相同，因此如果你选择的解决方案会按目标（要管理的模式）收费，那么你应该小心，因为这可能会很快累积起来。

在线模式管理

截至目前，付费解决方案（如Flyway）尚无明确的路径以非阻塞方式为您运行模式更改，尽管其竞争对手Liquibase为 Percona 的在线模式更改提供了一个得到良好支持的插件。您需要了解每个供应商为您做出的权衡，并了解这些权衡对您的可用性意味着什么，特别是如果您计划使用这些供应商来管理大型数据库（磁盘上有多个 TB）的模式更改。

开箱即用的集成

大多数这些工具都对您公司内部软件使用的语言以及为与现有软件交付流程集成提供的挂钩做出了假设。如果您的公司是高度多语言的，或者正在更改主要软件语言，这可能会排除一些供应商。我们将在下一节中介绍在实施模式源控制管理时需要“自己动手”时该怎么做。

使用开源

如果购买付费工具超出了您的能力范围，或者如果您有正当理由认为当前的解决方案都不适合您的组织，您可以使用现有的开源工具和您组织的 CI 管道来实现相同的结果。

一个显著的开源解决方案，用于在多个环境中通过版本控制管理模式更改的是Skeema。Skeema 本身不会在生产环境中为您运行模式更改——我们将很快介绍如何做到这一点——但它是一个很好的工具，用于跟踪每个数据库集群中的源代码控制存储库中的更改以及跨多个环境。其 CLI 实现在与您选择的 CI 解决方案集成时提供了很大的灵活性。如何直接将 Skeema 与您的 CI 解决方案集成将需要考虑 CI 解决方案具有的功能。Twilio Sendgrid 团队的这篇博客文章解释了他们如何将 Skeema 与 Buildkite 集成，以实现寻求管理其数据库更改的功能团队的自主权。

请注意，无论这个解决方案如何与您的 CI 集成，它还需要访问所有环境（包括生产环境）来运行模式更改。这也意味着与您的安全团队合作，以确保您正在创建正确的访问控制，以实现使用持续集成自动化模式部署模式的好处。

注意

如果您已经在使用 Vitess 扩展您的数据库基础设施的道路上，您应该知道 Vitess 也会为您管理模式更改。确保检查文档的特定部分。

在过去几年中，具有自动化和合规性思维的跨环境管理模式更改领域已经显著增长。以下是您在做出选择时的一些最终收获：

尽可能接近现有的软件部署工具和工作流程。您希望这对您更大的工程组织来说是熟悉的。
使用一个可以集成基本模式更改的基本检查的工具，以确保满足一些基本要求。如果新表不使用正确的字符集或者如果您决定不允许外键，则您的解决方案应自动失败拉取请求。
如果您所在的组织是多语言并且正在快速增长，请确保您不会意外引入人为瓶颈，例如一个存储库用于所有数据库和所有模式更改。请记住这里的目标是工程团队速度。

在生产环境中运行模式更改

现在我们已经介绍了跟踪和管理为您的组织部署模式更改的选项，让我们讨论如何在生产环境中运行这些更改，而不会影响您的数据库的正常运行时间或依赖于它们的服务。

本地 DDL 语句

MySQL 在 5.6 版本中引入了非阻塞模式的模式更改，但在该主要版本中，该功能带有一些注意事项，实际上只能用于非常特定的模式更改类型。

到 8.0 版本成为 GA 时，MySQL 中对本机 DDL 的支持大大扩展，尽管仍不是普遍的。更改主键、更改字符集、打开逐表加密以及添加或删除外键都是您仍然无法使用INPLACE alter 本地进行的模式更改的示例。⁷我们强烈建议您通过文档熟悉使用INPLACE或INSTANT算法所允许的更改，这是在 MySQL 中进行模式更改而无需停机的首选本地方式。

然而，即使您需要的更改在 8.0 及更高版本中得到了技术上的支持，如果正在更改的表非常庞大，如果 InnoDB 内部保留的表更改日志文件太大，您可能会遇到回滚，从而撤销数小时或数天的工作。您可能需要使用外部工具的另一个原因是，如果您强烈希望使用节流机制控制表更改发生的速度。这是您可以通过即将讨论的外部工具来管理的事项。

使用外部工具运行模式更改

如果您尚无法运行最新且最强大的 MySQL 版本，并且具有所有模式更改的灵活性，您仍然可以将 CI 工具与可用的开源工具结合使用，在不影响服务的情况下自动在生产环境中运行模式更改。实现此目标的两个主要选项是 Percona 的pt-online-schema-change和 GitHub 的gh-ost。两者的文档都包含了您学习如何安装和使用工具所需的所有信息，因此我们将重点讨论如何选择使用哪种工具，应该考虑的主要权衡以及如何提高在生产中作为自动化模式部署流程的一部分使用任一工具的安全性。

警告

需要注意的一点是：任何运行模式更改的外部工具都需要制作您正在更改的表的完整副本。该工具仅使该过程影响较小，不需要破坏性写锁定，但只有 MySQL 中的本机 DDL 才能在不进行完整表复制的情况下更改表模式。

pt-online-schema-change的主要吸引力在于其稳定性以及在 MySQL 社区中的长期使用。它主要利用触发器来实现对各种大小的表进行模式更改，对数据库可用性的影响非常小，当切换到新表版本时。但其核心设计也伴随着权衡。在学习如何使用pt-online-schema-change来支持您的模式部署流程时，请记住以下几点：

触发器有限制

在 MySQL 8.0 之前，您不能在同一表上有多个具有相同操作的触发器。这意味着什么？如果您有一个名为sales的表，并且您已经需要在其上维护一个插入时间触发器，MySQL 在 8.0 之前不允许在该表上添加另一个插入触发器。如果尝试对其运行pt-online-schema-change模式更改，则当尝试添加所需的触发器时，该工具将产生错误。尽管我们通常强烈反对将表触发器作为业务逻辑的一部分，但仍会有情况下，遗留选择会创建约束，并且这将成为您选择模式更改机制时的权衡计算的一部分。

触发器会影响性能

Percona 进行了一些优秀的基准测试，显示即使在表上定义触发器也会产生性能影响。对于大多数安装来说，这种性能惩罚可能是看不见的，但如果您恰好在非常高的每秒事务吞吐率下运行数据库实例，您可能需要更仔细地观察 pt-online-schema-change 引入的触发器的影响，并调整以更保守地中止。

运行并发迁移

由于其使用触发器以及在 MySQL 8.0 之前触发器的限制，您会发现无法使用 pt-online-schema-change 在同一张表上运行多个模式更改。这可能最初是一个小小的不便，但如果将该工具整合到一个完全自动化的模式迁移流水线中，它可能会成为团队的瓶颈。

外键约束

尽管该工具在存在外键的情况下对模式更改有一定程度的支持，但您需要仔细阅读文档，并确定哪种权衡对您的数据和事务吞吐量影响最小。

gh-ost 是 GitHub 的数据工程团队专门为管理模式更改过程而创建的解决方案，旨在在不使用触发器的情况下管理模式更改过程，同时也不会对服务产生影响。在表复制阶段不使用触发器跟踪更改，而是作为副本连接到您的集群副本之一，并将基于行的复制日志作为更改日志消耗。

使用 gh-ost 进行模式更改时需要仔细考虑的一件事是您的现有数据库是否使用外键。虽然 pt-online-schema-change 试图支持父表或子表在外键关系中的模式更改，但这是一个复杂的选择，充满了权衡。（我们牺牲一致性来保证运行时间吗？还是冒一些可能不一致的风险？）另一方面，如果您要修改的表中存在外键，gh-ost 大多数情况下会为您做出选择并完全退出。作为 gh-ost 的主要贡献者，Shlomi Noach 在一篇长篇但非常有用的博文中解释了使用外键和在线模式更改工具的环境，这些工具最终仍然是外部于数据库引擎，这种权衡很难理解，他建议如果您还需要在线模式更改，则根本不要使用外键。

如果您和您的团队对此任务还很陌生，并且正在为组织的模式更改 CI 铺平道路，我们认为 gh-ost 是更好的解决方案，只要您也有纪律不引入外键。考虑到它使用二进制日志而不是触发器来跟踪更改，我们认为它是更安全的选择，您不必担心触发器的性能损失，它更加不受您运行的 MySQL 版本的影响（即使在某些情况下它甚至可以与基于语句的复制一起工作），并且它已经在大规模部署中得到验证。

什么时候 pt-online-schema-change 是首选选项？如果您运行一些旧数据库，其中已经存在外键并且删除它们是一个困难的提议，您会发现 pt-online-schema-change 尝试更广泛地支持外键，但您必须承担选择对数据完整性和运行时间最安全的选项的认知负担。此外，gh-ost 利用二进制日志来执行其工作，因此如果由于某种原因该工具无法访问这些日志，则 pt-online-schema-change 仍然是一个可行的选择。

理想情况下，总有一天我们都可以在 MySQL 中本地执行在线模式更改，但那一天还没有到来。在那之前，开源生态系统在使模式更改变得更容易自动化方面已经取得了长足的进步。让我们讨论如何将所有这些工具组合在一起，为模式更改的完全成熟的 CI/CD 流水线。

用于模式更改的 CI/CD 流水线

现在我们已经涵盖了许多工具，从帮助管理模式定义版本控制的工具到在生产中进行更改并最小化停机时间的工具，您可以看到我们拥有完整的模式更改持续集成和部署的部件，这可以消除您组织中工程师生产力的一个巨大瓶颈。让我们把它整合起来：

组织您的模式源控制

首先，您必须通过将每个数据库集群的模式定义分开存储在一个存储库中来开始。如果这里的目标是为不同团队以不同速度运行其更改提供灵活性，那么将所有数据库的所有模式定义合并到一个存储库中是没有意义的。这种分离还允许每个团队在存储库中定义不同的 linting 检查。一些团队可能需要非常特定的字符集和排序规则，而其他团队可能对默认设置感到满意。这里合作伙伴团队的灵活性至关重要。

确保记录工程团队成员如何从他们的笔记本电脑上的模式更改到在所有环境上运行并在进入生产之前运行测试的工作流程。在这里，拉取请求模型可以非常有用，帮助每个团队定义请求模式更改时要运行的测试，以自动化方式在推广并在更多环境或生产中运行更改之前。

安全性的基线配置

为您选择的在线模式更改工具定义基线配置。您是为依赖您提供灵活、可扩展但也安全解决方案的合作伙伴团队提供工具的团队。在考虑如何实现在线模式更改工具时，可能是时候对需要成为测试模式更改拉取请求的模式设计考虑发表意见了。例如，如果您决定更喜欢gh-ost的安全性和无触发器设计，这意味着您必须成为一个不包含外键的数据库平台。不去讨论这种选择的权衡，如果最终决定“消灭外键”，那么您应该确保这一点被编码到您如何在预提交挂钩或您的 Skeema 存储库中测试模式更改的方式中，以便避免在环境层次结构中引入不良模式更改。同样，您应该为您的在线模式更改工具决定一个基本配置，为生产中的更改提供基本安全网。您可能希望在这种配置中引入的示例包括最大的 MySQL 线程运行或最大允许的系统负载。存储库模板可以是一个强大的工具，使正确的事情成为任何功能团队创建新数据库并希望跟踪和管理模式更改的存储库时的简单事情。

每个团队的管道灵活性

当您在存储库中为每个数据库组织模式定义时，您允许每个拥有该数据库的团队决定其管道应该是自动化还是人为管理的最大灵活性。一个团队可能仍处于新产品迭代阶段，只要定义的测试通过，他们就可以自动推广模式拉取请求。另一个团队可能拥有更加关键的数据库，并需要更加谨慎的方法，更倾向于在 CI 系统可以将其推广到下一个环境之前，要求操作员批准拉取请求。

当您设计组织如何实现可扩展的模式更改部署时，保持目标在眼前：速度与安全性相结合，使您不断增长的工程组织在从想法到生产功能的公司移动中，数据库工程团队不成为瓶颈。

总结

良好的模式设计是相当普遍的，但当然 MySQL 有特殊的实现细节需要考虑。简而言之，尽可能保持事物简单和小型是个好主意。MySQL 喜欢简单，与您的数据库一起工作的人也会喜欢。请记住以下准则：

设计时避免极端情况，比如会导致查询非常复杂或表格有大量列的模式。（一个“oodles”介于 scad 和 gazillion 之间。）
使用小型、简单、适当的数据类型，并避免使用NULL，除非它实际上是模拟数据现实的正确方式。
尽量使用相同的数据类型来存储相似或相关的值，特别是如果它们将用于连接条件。
注意变长字符串，可能会导致对临时表和排序进行悲观的全长内存分配。
如果可以的话，尽量使用整数作为标识符。
避免使用传统的 MySQL 特性，比如为浮点数指定精度或为整数指定显示宽度。
对ENUM和SET要小心。它们很方便，但有时可能会被滥用，而且有时会很棘手。最好避免使用BIT。

数据库设计是一门科学。如果您非常关注数据库设计，请考虑使用专门的源材料。⁸

还要记住，您的模式将随着业务需求和从用户那里学到的东西而发展，这意味着具有管理模式更改的健壮软件生命周期是使这种演变对您的组织安全和可扩展的关键部分。

¹ 记住长度是以字符为单位指定的，而不是字节。多字节字符集可能需要超过 1 个字节来存储每个字符。

² 如果值在检索后必须保持不变，请小心处理BINARY类型。MySQL 会用\0填充到所需长度。

³ 时间是用于相对比较的，因为 CPU、内存和其他硬件的速度随时间变化。

⁴ TIMESTAMP行为的规则复杂，并且在各个 MySQL 版本中有所更改，因此您应该验证您得到的行为是否符合您的要求。通常在对TIMESTAMP列进行更改后，检查SHOW CREATE TABLE的输出是个好主意。

⁵ 如果使用 InnoDB 存储引擎，除非数据类型完全匹配，否则可能无法创建外键。由此产生的错误消息“ERROR 1005 (HY000): Can’t create table”可能会令人困惑，具体情况取决于上下文，关于此问题的问题经常出现在 MySQL 邮件列表上。（奇怪的是，你可以在长度不同的VARCHAR列之间创建外键。）

⁶ 另一方面，对于一些有很多写入者的非常大的表，这种伪随机值实际上可以帮助消除“热点”。

⁷ 查看MySQL 文档以获取更多信息。

⁸ 想要深入阅读的话，可以考虑阅读迈克尔·J·赫尔南德斯（Pearson）的Database Design for Mere Mortals。

第七章：高性能索引

索引（在 MySQL 中也称为键）是存储引擎用来快速查找行的数据结构。它们还具有几个其他有益的属性，我们将在本章中探讨。

索引对于良好的性能至关重要，并随着数据量的增长变得更加重要。小型、负载轻的数据库通常即使没有适当的索引也能表现良好，但随着数据集的增长，性能可能会迅速下降。¹不幸的是，索引经常被遗忘或误解，因此糟糕的索引是真实世界性能问题的主要原因之一。这就是为什么我们将这些材料放在书的前面，甚至比我们讨论查询优化更早。

索引优化可能是提高查询性能的最有效方法。索引可以将性能提高数个数量级，而最佳索引有时可以比仅仅“好”的索引提高大约两个数量级的性能。创建真正最佳的索引通常需要你重写查询，因此本章和下一章密切相关。

本章依赖于使用示例数据库，比如来自 MySQL 网站的Sakila 示例数据库。Sakila 是一个模拟租赁商店的示例数据库，包含演员、电影、客户等。

索引基础知识

理解 MySQL 中索引工作原理最简单的方法是将其想象成一本书的索引。要查找书中讨论特定主题的位置，你查看索引，它告诉你该术语出现的页码。

在 MySQL 中，存储引擎以类似的方式使用索引。它在索引的数据结构中搜索一个值。当找到匹配时，它可以找到包含匹配的行。假设你运行以下查询：

SELECT first_name FROM sakila.actor WHERE actor_id = 5;
1

actor_id列上有一个索引，因此 MySQL 将使用该索引查找actor_id为5的行。换句话说，它在索引中查找值并返回包含指定值的任何行。

索引包含表中一个或多个列的值。如果索引多于一列，列的顺序非常重要，因为 MySQL 只能在索引的最左前缀上高效搜索。在两列上创建索引与创建两个单独的单列索引不同，你将看到。

索引类型

有许多类型的索引，每种类型都设计用于不同的目的。索引是在存储引擎层实现的，而不是在服务器层。因此，它们没有标准化：在每个引擎中，索引的工作方式略有不同，并非所有引擎都支持所有类型的索引。即使多个引擎支持相同的索引类型，它们在内部可能以不同的方式实现。鉴于本书假定你在所有表中使用 InnoDB 作为引擎，我们将专门讨论 InnoDB 中的索引实现。

话虽如此，让我们看看 MySQL 目前支持的两种最常用的索引类型，它们的优点和缺点。

B-tree 索引

当人们谈论索引而没有提及类型时，他们可能指的是B-tree 索引，它通常使用 B-tree 数据结构来存储其数据。² MySQL 的大多数存储引擎支持这种索引类型。

我们将这些索引称为B-tree，因为这是 MySQL 在CREATE TABLE和其他语句中使用的术语。然而，存储引擎可能在内部使用不同的存储结构。例如，NDB Cluster 存储引擎使用 T-tree 数据结构来存储这些索引，即使它们被标记为BTREE，而 InnoDB 使用 B+树。这些结构和算法的变化超出了本书的范围。

B 树的一般思想是所有值按顺序存储，每个叶子页距离根节点的距离相同。图 7-1 显示了 B 树索引的抽象表示，大致对应于 InnoDB 的索引工作原理。

图 7-1。建立在 B 树（技术上是 B+树）结构上的索引

B 树索引加快了数据访问，因为存储引擎不必扫描整个表以找到所需的数据。相反，它从根节点开始（在此图中未显示）。根节点中的插槽保存指向子节点的指针，存储引擎遵循这些指针。它通过查看节点页中的值来找到正确的指针，这些值定义了子节点中值的上限和下限。最终，存储引擎要么确定所需值不存在，要么成功到达叶子页。

叶子页很特殊，因为它们指向索引数据而不是指向其他页面的指针。（不同的存储引擎有不同类型的“指针”指向数据。）我们的示例只显示了一个节点页及其叶子页，但根和叶子之间可能有许多级别的节点页。树的深度取决于表的大小。

因为 B 树按顺序存储索引列，所以它们对于搜索数据范围很有用。例如，对于文本字段的索引，沿着树向下移动会按字母顺序经过值，因此查找“名字以 I 至 K 开头的所有人”是高效的。

假设你有以下表格：

CREATE TABLE People (
 last_name varchar(50) not null,
 first_name varchar(50) not null,
 dob date not null,
 key(last_name, first_name, dob)
);
1
2
3
4
5
6

索引将包含表中每行的last_name、first_name和dob列的值。图 7-2 说明了索引如何排列存储的数据。

请注意，索引根据在CREATE TABLE语句中给出的列的顺序对值进行排序。看看最后两个条目：有两个名字相同但出生日期不同的人，它们按出生日期排序。

图 7-2。B 树（技术上是 B+树）索引的示例条目

自适应哈希索引

InnoDB 存储引擎具有一种称为自适应哈希索引的特殊功能。当 InnoDB 注意到某些索引值被频繁访问时，它会在 B 树索引之上在内存中为它们构建一个哈希索引。这使其 B 树索引具有哈希索引的某些属性，例如非常快速的哈希查找。这个过程是完全自动的，您无法控制或配置它，尽管您可以完全禁用自适应哈希索引。

可以使用 B 树索引的查询类型

B 树索引适用于通过完整键值、键范围或键前缀进行查找。只有在查找使用索引的最左前缀时才有用。³我们在前一节中展示的索引将对以下类型的查询有用：

匹配完整值

完整键值的匹配指定了索引中所有列的值。例如，此索引可以帮助您找到一个名为 Cuba Allen 且出生于 1960-01-01 的人。

匹配最左前缀

此索引可以帮助您找到所有姓 Allen 的人。这仅使用索引中的第一列。

匹配列前缀

您可以匹配列值的第一部分。此索引可以帮助您找到所有��J 开头的人。这仅使用索引中的第一列。

匹配一系列值

此索引可以帮助您找到姓 Allen 和 Barrymore 之间的人。这也仅使用第一列。

精确匹配一部分并在另一部分上匹配范围

此索引可以帮助您找到姓 Allen 且名字以 K 开头（Kim，Karl 等）的所有人。这是对last_name的精确匹配和对first_name的范围查询。

仅索引查询

B 树索引通常可以支持仅索引查询，这些查询仅访问索引，而不访问行存储。我们在“覆盖索引”中讨论了这种优化。

因为树的节点是排序的，它们可以用于查找值和ORDER BY查询（按排序顺序查找值）。一般来说，如果 B 树可以帮助你以特定方式查找行，它也可以帮助你按照相同的标准对行进行排序。因此，我们的索引将有助于满足我们刚刚列出的所有类型查找的ORDER BY子句。

这里是 B 树索引的一些限制：

如果查找不是从索引列的最左侧开始的话，它们就没有用处。例如，这个索引无法帮助你找到所有名为 Bill 的人或所有出生于特定日期的人，因为这些列不是索引中的最左侧列。同样，你无法使用索引找到姓氏以特定字母结尾的人。
你不能跳过索引中的列，也就是��，你无法找到所有姓氏为 Smith 且出生于特定日期的人。如果不为first_name列指定值，MySQL 只能使用索引的第一列。
存储引擎无法优化访问第一个范围条件右侧的任何列。例如，如果你的查询是 WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，索引访问将仅使用索引中的前两列，因为LIKE是一个范围条件（服务器可以将其余列用于其他目的）。对于具有有限值的列，通常可以通过指定相等条件而不是范围条件来解决这个问题。

现在你知道为什么我们说列顺序非常重要：这些限制都与列顺序有关。为了获得最佳性能，您可能需要以不同顺序创建相同列的索引以满足您的查询。

一些限制并非是 B 树索引固有的，而是 MySQL 查询优化器和存储引擎使用索引的结果。其中一些限制可能会在未来被移除。

全文索引

FULLTEXT是一种特殊类型的索引，它在文本中查找关键词，而不是直接将值与索引中的值进行比较。全文搜索与其他类型的匹配完全不同。它有许多微妙之处，如停用词、词干、复数形式和布尔搜索。它更类似于搜索引擎的工作方式，而不是简单的WHERE参数匹配。

在同一列上拥有全文索引并不会消除对该列的 B 树索引的价值。全文索引用于MATCH AGAINST操作，而不是普通的WHERE子句操作。

索引的好处

索引使服务器能够快速导航到表中所需的位置，但这并不是它们的全部用途。正如你现在可能已经了解的那样，索引还有几个额外的好处，这些好处基于用于创建它们的数据结构的属性。

B 树索引是你将使用的最常见类型，通过按排序顺序存储数据，MySQL 可以利用它来处理带有ORDER BY和GROUP BY子句的查询。由于数据是预先排序的，B 树索引还将相关值存储在一起。最后，索引实际上存储了值的副本，因此某些查询可以仅从索引中满足。这些属性带来了三个主要好处：

索引减少了服务器需要检查的数据量。
索引帮助服务器避免排序和临时表。
索引将随机 I/O 转换为顺序 I/O。

这个主题真的值得一本整书。对于那些想深入了解的人，我们推荐 Tapio Lahdenmaki 和 Mike Leach 的关系数据库索引设计和优化器（Wiley）。它解释了如何计算索引的成本和收益，如何估计查询速度，以及如何确定索引是否比提供的好处更昂贵。

Lahdenmaki 和 Leach 的书还介绍了一个三星系统，用于评估索引对查询的适用性。如果索引将相关行相邻放置，则获得一颗星，如果其行按查询所需的顺序排序，则获得第二颗星，如果包含查询所需的所有列，则获得最后一颗星。我们将在本章中回顾这些原则。

高性能的索引策略

创建正确的索引并正确使用它们对于良好的查询性能至关重要。我们介绍了不同类型的索引并探讨了它们的优势和劣势。现在让我们看看如何真正发挥索引的力量。

有许多有效选择和使用索引的方法，因为有许多特殊情况的优化和专门的行为。确定何时使用以及评估选择的性能影响是您随着时间学会的技能。接下来的章节将帮助您了解如何有效使用索引。

前缀索引和索引选择性

您通常可以通过索引前几个字符而不是整个值来节省空间并获得良好的性能。这使得您的索引使用的空间更少，但也使它们的选择性更低。索引选择性是索引值的不同值数（基数）与表中总行数（#T）的比率，范围从 1/#T到 1。高度选择性的索引很好，因为它让 MySQL 在查找匹配项时过滤更多行。唯一索引的选择性为 1，这是最好的选择。

列的前缀通常具有足够的选择性以提供良好的性能。如果您正在为BLOB或TEXT列，或者非常长的VARCHAR列建立索引，或者必须定义前缀索引，因为 MySQL 不允许索引其完整长度。

诀窍在于选择一个足够长以提供良好选择性但又足够短以节省空间的前缀。前缀应该足够长，使索引几乎与如果您对整个列进行索引时一样有用。换句话说，您希望前缀的基数接近完整列的基数。

要确定一个好的前缀长度，找到最频繁出现的值并与最频繁前缀列表进行比较。在 Sakila 示例数据库中没有一个好的表来展示这一点，所以我们从city表中派生一个，这样我们就有足够的数据可以使用：

CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city;
-- Repeat the next statement five times:
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city_demo;
-- Now randomize the distribution (inefficiently but conveniently):
UPDATE sakila.city_demo
 SET city = (SELECT city FROM sakila.city ORDER BY RAND() LIMIT 1);
1
2
3
4
5
6
7

现在我们有一个示例数据集。结果并不真实分布，并且我们使用了RAND()，所以你的结果会有所不同，但这对这个练习并不重要。首先，我们找到出现频率最高的城市：

mysql> SELECT COUNT(*) AS c, city
    -> FROM sakila.city_demo 
    -> GROUP BY city ORDER BY c DESC LIMIT 10;
+-----+----------------+
| c  | city            |
+-----+----------------+
| 65 | London          |
| 49 | Hiroshima       |
| 48 | Teboksary       |
| 48 | Pak Kret        |
| 48 | Yaound          |
| 47 | Tel Aviv-Jaffa  |
| 47 | Shimoga         |
| 45 | Cabuyao         |
| 45 | Callao          |
| 45 | Bislig          |
+-----+----------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

注意每个值大约有 45 到 65 次出现。现在我们找到最频繁出现的城市名前缀，从三个字母的前缀开始：

mysql> SELECT COUNT(*) AS c, LEFT(city, 3) AS pref
    -> FROM sakila.city_demo GROUP BY pref ORDER BY cc DESC LIMIT 10;
+-----+------+
| c   | pref |
+-----+------+
| 483 | San  |
| 195 | Cha  |
| 177 | Tan  |
| 167 | Sou  |
| 163 | al-  |
| 163 | Sal  |
| 146 | Shi  |
| 136 | Hal  |
| 130 | Val  |
| 129 | Bat  |
+-----+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

每个前缀的出现次数更多，因此唯一前缀比唯一完整城市名称要少得多。这个想法是增加前缀长度，直到前缀几乎与列的完整长度一样具有选择性。一点实验表明7是一个不错的值：

mysql> SELECT COUNT(*) AS c, LEFT(city, 7) AS pref
    -> FROM sakila.city_demo GROUP BY pref ORDER BY c DESC LIMIT 10;
+-----+---------+
| c   | pref    |
+-----+---------+
| 70  | Santiag |
| 68  | San Fel |
| 65  | London  |
| 61  | Valle d |
| 49  | Hiroshi |
| 48  | Teboksa |
| 48  | Pak Kre |
| 48  | Yaound  |
| 47  | Tel Avi |
| 47  | Shimoga |
+-----+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

另一种计算好前缀长度的方法是计算完整列的选择性，并尝试使前缀的选择性接近该值。以下是如何找到完整列的选择性：

mysql> SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;
+-------------------------------+
| COUNT(DISTINCT city)/COUNT(*) |
+-------------------------------+
|                        0.0312 |
+-------------------------------+
1
2
3
4
5
6

如果我们的目标选择性接近 0.031，那么平均而言，前缀将是相当不错的（不过这里有一个警告）。在一个查询中评估许多不同长度是可能的，这对于非常大的表格非常有用。以下是如何在一个查询中找到几个前缀长度的选择性：

mysql> SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
    -> COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
    -> COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
    -> COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
    -> COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
    -> FROM sakila.city_demo;
+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   |
+--------+--------+--------+--------+--------+
| 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 |
+--------+--------+--------+--------+--------+
1
2
3
4
5
6
7
8
9
10
11

这个查询显示增加前缀长度会导致随着接近七个字符的逐渐减小的改善。

仅仅看平均选择性并不是一个好主意。警告是最坏情况选择性也很重要。平均选择性可能会让您认为四或五个字符的前缀已经足够好了，但如果您的数据非常不均匀，那可能是一个陷阱。如果您使用值4查看最常见城市名称前缀的出现次数，您将清楚地看到这种不均匀性：

mysql> SELECT COUNT(*) AS c, LEFT(city, 4) AS pref
    -> FROM sakila.city_demo GROUP BY pref ORDER BY c DESC LIMIT 5;
+-----+------+
| c   | pref |
+-----+------+
| 205 | San  |
| 200 | Sant |
| 135 | Sout |
| 104 | Chan |
|  91 | Toul |
+-----+------+
1
2
3
4
5
6
7
8
9
10
11

使用四个字符，最频繁的前缀出现的频率要比最频繁的全长值要高得多。也就是说，这些值的选择性低于平均选择性。如果您的数据集比这个随机生成的样本更真实，您可能会看到这种效果更加明显。例如，在真实世界的城市名称上构建一个四字符前缀索引将导致以“San”和“New”开头的城市的选择性非常糟糕，而这样的城市有很多。

现在我们已经找到了样例数据的一个好值，以下是如何在列上创建前缀索引的方法：

ALTER TABLE sakila.city_demo ADD KEY (city(7));
1

前缀索引可以使索引变得更小更快，但它们也有缺点：MySQL 无法将前缀索引用于ORDER BY或GROUP BY查询，也无法将其用作覆盖索引。

我们发现前缀索引有益的一个常见情况是当使用长十六进制标识符时。我们在上一章讨论了存储这些标识符的更有效技术，但如果您使用的是无法修改的打包解决方案呢？我们经常看到这种情况发生在 vBulletin 和其他使用 MySQL 存储网站会话的应用程序上，这些应用程序以长十六进制字符串为键。在前八个字符左右添加索引通常会显著提升性能，而且对应用程序完全透明。

多列索引

多列索引经常被误解。常见的错误是单独为许多或所有列建立索引，或者以错误的顺序为列建立索引。

我们将在下一节讨论列顺序。第一个错误，单独为许多列建立索引，在SHOW CREATE TABLE中有一个独特的标志：

CREATE TABLE t (
 c1 INT,
 c2 INT,
 c3 INT,
 KEY(c1),
 KEY(c2),
 KEY(c3)
);
1
2
3
4
5
6
7
8

这种索引策略通常是因为人们给出模糊但听起来权威的建议，比如“在WHERE子句中出现的列上创建索引”。这个建议是非常错误的。它最多会导致一星级索引。这些索引可能比真正最佳的索引慢几个数量级。有时，当您无法设计一个三星级索引时，最好忽略WHERE子句，关注最佳行顺序或创建一个覆盖索引。

对许多列单独建立索引对于大多数查询并不能帮助 MySQL 提高性能。当 MySQL 使用一种称为索引合并的策略时，它可以在使用多个索引来定位所需行的单个表时稍微应对这种索引不良的表。它可以同时扫描这两个索引并合并结果。算法有三种变体：OR条件的并集，AND条件的交集，以及两者的组合的并集。以下查询使用了两个索引扫描的并集，您可以通过检查Extra列看到：

mysql> EXPLAIN SELECT film_id, actor_id FROM sakila.film_actor
    -> WHERE actor_id = 1 OR film_id = 1\G
*************************** 1\. row ***************************
 id: 1
 select_type: SIMPLE
 table: film_actor
 partitions: NULL
 type: index_merge
 possible_keys: PRIMARY,idx_fk_film_id
 key: PRIMARY,idx_fk_film_id
 key_len: 2,2
 ref: NULL
 rows: 29
 filtered: 100.00
 Extra: Using union(PRIMARY,idx_fk_film_id); Using where
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

MySQL 可以在复杂查询中使用这种技术，因此您可能会在某些查询的Extra列中看到嵌套操作。

索引合并策略有时效果非常好，但更常见的情况是实际上表现出一个索引不良的表：

当服务器交集索引（通常用于AND条件）时，通常意味着您需要一个包含所有相关列的单个索引，而不是需要组合的多个索引。
当服务器联合索引（通常用于OR条件）时，有时算法的缓冲、排序和合并操作会使用大量的 CPU 和内存资源。特别是如果并非所有索引都非常具有选择性，那么扫描将返回大量行给合并操作。
请记住，优化器不考虑这个成本-它仅优化随机页面读取的数量。这可能使其“低估”查询的成本，实际上可能比纯表扫描运行得更慢。密集的内存和 CPU 使用也倾向于影响并发查询，但在单独运行查询时您不会看到这种效果。有时，使用UNION子句重写这样的查询更为优化。

当你在EXPLAIN中看到索引合并时，应该检查查询和表结构，看看这是否真的是你能得到的最佳结果。你可以通过optimizer_switch选项或变量禁用索引合并。你也可以使用IGNORE INDEX。

选择一个好的列顺序

我们看到的最常见的混淆原因之一是索引中列的顺序。正确的顺序取决于将使用索引的查询，并且您必须考虑如何选择索引顺序，使得行以一种有利于查询的方式排序和分组。

多列 B 树索引中的列顺序意味着索引首先按最左边的列排序，然后按下一列排序，依此类推。因此，索引可以以正向或反向顺序扫描，以满足与列顺序完全匹配的ORDER BY、GROUP BY和DISTINCT子句的查询。

因此，在多列索引中，列顺序至关重要。列顺序要么使索引能够获得 Lahdenmaki 和 Leach 的三星系统中的“星星”（请参见本章前面的“索引的好处”了解更多关于三星系统的信息）。我们将在本章的其余部分展示许多示例，说明这是如何工作的。

有一个选择列顺序的古老经验法则：在索引中首先放置最具选择性的列。这个建议有多有用呢？在某些情况下可能有帮助，但通常比避免随机 I/O 和排序要不重要得多，综合考虑所有事情。（具体情况各不相同，因此没有一刀切的规则。这一点就应该告诉你，这个经验法则可能比你想象的要不重要。）

在没有考虑排序或分组的情况下，将最具选择性的列放在前面可能是一个好主意，因此索引的目的仅仅是优化WHERE查找。在这种情况下，设计索引以尽快过滤出行可能确实有效，因此对于只在WHERE子句中指定索引前缀的查询，它更具选择性。然而，这不仅取决于列的选择性（总体基数），还取决于您用于查找行的实际值-值的分布。这与我们为选择良好的前缀长度而探讨的相同类型的考虑是一样的。您可能实际上需要选择列顺序，使其对您将运行的大多数查询具有尽可能高的选择性。

让我们以以下查询为例：

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;
1

您应该在(staff_id, customer_id)上创建索引，还是应该颠倒列顺序？我们可以运行一些快速查询来帮助检查表中值的分布，并确定哪一列具有更高的选择性。让我们将查询转换为计算WHERE子句中每个谓词的基数：

mysql> SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment\G
*************************** 1\. row ***************************
 SUM(staff_id = 2): 7992
SUM(customer_id = 584): 30
1
2
3
4

根据经验法则，我们应该将customer_id放在索引的第一位，因为谓词在表中匹配的行数较少。然后我们可以再次运行查询，看看staff_id在由特定客户 ID 选择的行范围内的选择性如何：

mysql> SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584\G
*************************** 1\. row ***************************
SUM(staff_id = 2): 17
1
2
3

使用这种技术要小心，因为结果取决于为所选查询提供的具体常数。如果为这个查询优化了索引，而其他查询表现不佳，服务器的性能可能会受到影响，或者某些查询可能会运行不稳定。

如果你正在使用来自工具（如pt-query-digest）报告的“最差”样本查询，这种技术可以是查看对你的查询和数据最有帮助的索引的有效方法。但如果你没有具体的样本要运行，也许最好使用旧的经验法则，即全面查看基数，而不仅仅是一个查询：

mysql> SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
    -> COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
    -> COUNT(*)
    -> FROM payment\G
*************************** 1\. row ***************************
 staff_id_selectivity: 0.0001
 customer_id_selectivity: 0.0373
 COUNT(*): 16049
1
2
3
4
5
6
7
8

customer_id的选择性更高，所以答案是将该列放在索引的第一位：

ALTER TABLE payment ADD KEY(customer_id, staff_id);
1

与前缀索引一样，问题通常出现在具有高于正常基数的特殊值上。例如，我们曾看到应用程序将未登录的用户视为“访客”用户，在会话表和其他记录用户活动的地方，这些用户获得特殊的用户 ID。涉及该用户 ID 的查询可能会与其他查询表现非常不同，因为通常有很多未登录的会话。有时系统帐户也会引起类似的问题。一个应用程序有一个神奇的管理帐户，不是真实用户，它与整个网站的每个用户“成为朋友”，以便发送状态通知和其他消息。该用户庞大的朋友列表导致网站性能严重问题。

这实际上是相当典型的。任何异常值，即使不是应用程序管理中糟糕决策的产物，都可能引发问题。真正拥有大量朋友、照片、状态消息等用户可能会和虚假用户一样令人头疼。

这是我们曾在产品论坛上看到的一个真实例子，用户在那里交流关于产品的故事和经验。这种特定形式的查询运行非常缓慢：

SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE
FROM Message
WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)
ORDER BY priority DESC, modifiedDate DESC
1
2
3
4

这个查询似乎没有一个很好的索引，所以客户要求我们看看是否可以改进。EXPLAIN如下：

 id: 1
 select_type: SIMPLE
 table: Message
 type: ref
 key: ix_groupId_userId
 key_len: 18
 ref: const,const
 rows: 1251162
 Extra: Using where
1
2
3
4
5
6
7
8
9

MySQL 为这个查询选择的索引是(groupId, userId)，如果我们没有关于列基数的信息，这似乎是一个相当不错的选择。然而，当我们查看匹配该用户 ID 和群组 ID 的行数时，情况就有所不同：

mysql> SELECT COUNT(*), SUM(groupId = 10137),
    -> SUM(userId = 1288826), SUM(anonymous = 0)
    -> FROM Message\G
*************************** 1\. row ***************************
 count(*): 4142217
 sum(groupId = 10137): 4092654
 sum(userId = 1288826): 1288496
 sum(anonymous = 0): 4141934
1
2
3
4
5
6
7
8

这个群组实际上拥有表中的几乎每一行，用户有 130 万行数据——在这种情况下，根本没有办法使用索引！这是因为数据是从另一个应用程序迁移过来的，所有消息都被分配给了管理用户和群组作为导入过程的一部分。解决这个问题的方法是更改应用程序代码，以识别这个特殊情况的用户 ID 和群组 ID，并且不为该用户发出这个查询。

这个小故事的寓意是，经验法则和启发式方法可能很有用，但你必须小心，不要假设平均情况下的性能代表特殊情况下的性能。特殊情况可能破坏整个应用程序的性能。

最后，尽管选择性和基数的经验法则很有趣，但其他因素——如排序、分组以及查询的WHERE子句中是否存在范围条件——可能对查询性能产生更大的影响。

聚集索引

聚集索引⁵并不是一种单独的索引类型。相反，它们是一种数据存储方法。具体细节在不同的实现中有所不同，但 InnoDB 的聚集索引实际上将 B 树索引和行一起存储在同一结构中。

当表具有聚集索引时，其行实际上存储在索引的叶页中。术语聚集指的是具有相邻键值的行存储在彼此附近。⁶ 每个表只能有一个聚集索引，因为不能同时将行存储在两个位置。（但是，覆盖索引允许您模拟多个聚集索引；稍后详细介绍。）

因为存储引擎负责实现索引，而不是所有存储引擎都支持聚集索引。在本节中，我们专注于 InnoDB，但我们讨论的原则可能至少部分适用于任何支持聚集索引的存储引擎，无论是现在还是将来。

图 7-3 显示了聚集索引中记录的布局。请注意，叶页包含完整的行，但节点页仅包含索引列。在这种情况下，索引列包含整数值。

图 7-3. 聚集索引数据布局

一些数据库服务器允许您选择要聚集的索引，但在撰写本文时，MySQL 的内置存储引擎都不支持此功能。InnoDB 通过主键对数据进行聚集。这意味着图 7-3 中的“索引列”是主键列。

如果您没有定义主键，InnoDB 将尝试使用唯一的非空索引。如果没有这样的索引，InnoDB 将为您定义一个隐藏的主键，然后在其上进行聚集。隐藏主键的缺点是，这些主键的递增值在使用隐藏主键的所有表之间共享，导致共享键的互斥争用增加。

数据聚集具有一些非常重要的优势：

您可以将相关数据保持在一起。例如，在实现邮箱时，您可以按user_id进行聚集，这样您可以通过仅从磁盘检索几个页面来检索单个用户的所有消息。如果不使用聚集，每个消息可能需要自己的磁盘 I/O。
数据访问速度很快。聚集索引在一个 B 树中同时保存索引和数据，因此从聚集索引中检索行通常比在非聚集索引中进行相似查找更快。
使用覆盖索引的查询可以使用叶节点中包含的主键值。

如果您设计表和查询以利用这些优势，这些优势可以极大地提高性能。但是，聚集索引也有一些缺点：

对于 I/O 密集型工作负载，聚集提供了最大的改进。如果数据适合内存，那么访问数据的顺序实际上并不重要，因此聚集并不会带来太多好处。
插入速度严重依赖插入顺序。按照主键顺序插入行是将数据加载到 InnoDB 表中的最快方法。如果没有按照主键顺序加载行，加载大量数据后可能需要使用OPTIMIZE TABLE重��组织表。
更新聚集索引列的成本很高，因为它迫使 InnoDB 将每个更新的行移动到新位置。
基于聚集索引构建的表在插入新行或更新行的主键以移动行时会受到页拆分的影响。当行的键值决定必须将行放入一个已满数据的页面时，就会发生页拆分。存储引擎必须将页面拆分为两个以容纳该行。页拆分可能导致表在磁盘上使用更多空间。
对于全表扫描，聚集表可能会较慢，特别是如果行的密度较低或由于页拆分而存储非顺序。
二级（非聚集）索引可能比您预期的要大，因为它们的叶节点包含了引用行的主键列。
二级索引访问需要两次索引查找而不是一次。

最后一点可能有点令人困惑。为什么辅助索引需要两次索引查找？答案在于辅助索引存储的“行指针”的性质。记住，叶节点不存储指向引用行物理位置的指针；相反，它存储行的主键值。

这意味着要从辅助索引中找到一行，存储引擎首先找到辅助索引中的叶节点，然后使用存储在那里的主键值导航主键并找到行。这是双重工作：两次 B 树导航而不是一次。⁷ 在 InnoDB 中，自适应哈希索引（前面在“B 树索引”中提到）可以帮助减少这种惩罚。

InnoDB 的数据布局

为了更好地理解聚簇索引，让我们看看 InnoDB 如何布局以下表：

CREATE TABLE layout_test (
 col1 int NOT NULL,
 col2 int NOT NULL,
 PRIMARY KEY(col1),
 KEY(col2)
);
1
2
3
4
5
6

假设表填充了主键值 1 到 10,000，以随机顺序插入，然后使用OPTIMIZE TABLE进行优化。换句话说，数据在磁盘上被最佳地排列，但行可能是随机顺序的。col2的值在 1 到 100 之间随机分配，因此存在大量重复。

InnoDB 将表存储如图 7-4 所示。

图 7-4。`layout_test`表的 InnoDB 主键布局

聚簇索引中的每个叶节点包含主键值、事务 ID 和回滚指针，InnoDB 用于事务和 MVCC 目的，以及其余列（在本例中为col2）。如果主键在列前缀上，InnoDB 会将完整列值与其余列一起包括在内。

InnoDB 的辅助索引叶节点包含主键值，这些值作为指向行的“指针”。这种策略减少了在行移动或数据页拆分时维护辅助索引所需的工作量。使用行的主键值作为指针使索引变大，但这意味着 InnoDB 可以移动行而无需更新指向它的指针。

图 7-5 展示了示例表的col2索引。每个叶节点包含索引列（在本例中只有col2），然后是主键值（col1）。

图 7-5。`layout_test`表的 InnoDB 辅助索引布局

这些图表展示了 B 树叶节点，但我们有意省略了关于非叶节点的细节。InnoDB 的非叶 B 树节点每个包含索引列，以及指向下一层节点的指针（可能是另一个非叶节点或叶节点）。这适用于所有 B 树索引，包括聚簇和辅助索引。

在 InnoDB 中按主键顺序插入行

如果您正在使用 InnoDB 并且不需要任何特定的聚簇，最好定义一个代理键，这是一个主键，其值不是从应用程序数据派生的。通常使用AUTO_INCREMENT列是最简单的方法。这将确保行按顺序插入，并且将为使用主键进行连接提供更好的性能。

最好避免随机（非顺序且分布在大量值集上）的聚簇键，特别是对于 I/O 密集型工作负载。例如，使用 UUID 值在性能方面是一个糟糕的选择：它使聚簇索引插入变得随机，这是最坏的情况，并且不提供任何有用的数据聚类。

为了演示，我们对两种情况进行了基准测试。第一种是插入到具有整数 ID 的userinfo表中，定义如下：

CREATE TABLE userinfo (
 id int unsigned NOT NULL AUTO_INCREMENT,
 name varchar(64) NOT NULL DEFAULT '',
 email varchar(64) NOT NULL DEFAULT '',
 password varchar(64) NOT NULL DEFAULT '',
 dob date DEFAULT NULL,
 address varchar(255) NOT NULL DEFAULT '',
 city varchar(64) NOT NULL DEFAULT '',
 state_id tinyint unsigned NOT NULL DEFAULT '0',
 zip varchar(8) NOT NULL DEFAULT '',
 country_id smallint unsigned NOT NULL DEFAULT '0',
 gender ('M','F')NOT NULL DEFAULT 'M',
 account_type varchar(32) NOT NULL DEFAULT '',
 verified tinyint NOT NULL DEFAULT '0',
 allow_mail tinyint unsigned NOT NULL DEFAULT '0',
 parrent_account int unsigned NOT NULL DEFAULT '0',
 closest_airport varchar(3) NOT NULL DEFAULT '',
 PRIMARY KEY (id),
 UNIQUE KEY email (email),
 KEY country_id (country_id),
 KEY state_id (state_id),
 KEY state_id_2 (state_id,city,address)
) ENGINE=InnoDB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

注意自增整数主键。⁸

第二种情况是一个名为userinfo_uuid的表。它与userinfo表相同，只是其主键是 UUID 而不是整数：

CREATE TABLE userinfo_uuid (
 uuid varchar(36) NOT NULL,
 ...
1
2
3

我们对两种表设计进行了基准测试。首先，在具有足够内存容纳索引的服务器上向两个表中各插入一百万条记录。接下来，我们向相同的表中插入三癃万行，这使得索引比服务器的内存还要大。表 7-1 比较了基准测试结果。

表 7-1. 向 InnoDB 表中插入行的基准测试结果

表	行数	时间（秒）	索引大小（MB）
`userinfo`	1,000,000	137	342
`userinfo_uuid`	1,000,000	180	544
`userinfo`	3,000,000	1233	1036
`userinfo_uuid`	3,000,000	4525	1707

注意，使用 UUID 主键插入行不仅需要更长的时间，而且生成的索引要大得多。部分原因是由于较大的主键，但也有一部分无疑是由于页面分裂和随之产生的碎片化。

为了了解为什么这样做很重要，让我们看看当我们向第一个表插入数据时索引发生了什么。图 7-6 显示插入填充一页，然后继续在第二页上。

图 7-6. 向聚簇索引插入顺序索引值

如图 7-6 所示，InnoDB 将每个记录紧跟在前一个记录之后存储，因为主键值是顺序的。当页面达到其最大填充因子时（InnoDB 的初始填充因子仅为 15/16，以留出空间供以后修改），下一个记录将进入新页面。一旦数据以这种顺序方式加载，主键页面将几乎满载有顺序记录，这是非常理想的。（但是，次要索引页面可能不太可能有所不同。）

与将数据插入具有 UUID 聚簇索引的第二个表时发生的情况形成对比，如图 7-7 所示。

图 7-7. 向聚簇索引插入非顺序值

因为每个新行不一定比前一个具有更大的主键值，InnoDB 不能总是将新行放在索引末尾。它必须找到适当的位置放置行，通常在现有数据的中间附近，并为其腾出空间。这会导致大量额外工作，并导致次优化的数据布局。以下是缺点摘要：

目标页面可能已被刷新到磁盘并从缓存中移除，或者可能根本没有被放入缓存中，此时 InnoDB 必须找到它并从磁盘中读取它，然后才能插入新行。这会导致大量随机 I/O。
当插入是无序的时，InnoDB 经常需要分裂页面以为新行腾出空间。这需要移动大量数据，并修改至少三个页面，而不是一个。
由于分裂，页面变得稀疏且不规则填充，因此最终数据是碎片化的。

在向聚簇索引加载这样的随机值后，您可能需要执行 OPTIMIZE TABLE 来重建表并最佳地填充页面。

故事的寓意是，在使用 InnoDB 时，您应该努力按主键顺序插入数据，并尽量使用一个为每个新行提供单调递增值的聚簇键。

覆盖索引

一个常见的建议是为查询的WHERE子句创建索引，但这只是故事的一部分。索引需要为整个查询而不仅仅是WHERE子句而设计。索引确实是一种高效查找行的方法，但 MySQL 也可以使用索引检索列的数据，因此不必读取整行。毕竟，索引的叶节点包含它们索引的值；当读取索引可以提供您想要的数据时，为什么要读取行呢？包含（或“覆盖”）满足查询所需的所有数据的索引称为覆盖索引。重要的是要注意，只有 B 树索引可以用于覆盖索引。

覆盖索引可以是一个非常强大的工具，可以显著提高性能。考虑仅读取索引而不是数据的好处：

索引条目通常比完整行大小小得多，因此如果仅读取索引，MySQL 可以访问的数据量明显较少。这对于缓存工作负载非常重要，因为大部分响应时间来自数据的复制。对于 I/O 受限的工作负载也很有帮助，因为索引比数据小，更适合内存。
索引按其索引值排序（至少在页面内），因此 I/O 受限的范围访问将需要比从随机磁盘位置获取每行更少的 I/O。您甚至可以通过OPTIMIZE表来获得完全排序的索引，这将使简单的范围查询使用完全顺序的索引访问。
覆盖索引对 InnoDB 表格特别有帮助，因为 InnoDB 使用聚簇索引。InnoDB 的次要索引在其叶节点上保存行的主键值。因此，覆盖查询的次要索引避免了在主键中进行另一个索引查找。

在所有这些情况下，从索引中满足查询通常比查找行要便宜得多。

当您发出一个被索引覆盖的查询（索引覆盖查询）时，在EXPLAIN中的Extra列中会看到“Using index”。例如，sakila.inventory表在(store_id, film_id)上有一个多列索引。MySQL 可以使用此索引进行仅访问这两列的查询，例如以下查询：

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
*************************** 1\. row ***************************
 id: 1
 select_type: SIMPLE
 table: inventory
 partitions: NULL
 type: index
 possible_keys: NULL
 key: idx_store_id_film_id
 key_len: 3
 ref: NULL
 rows: 4581
 filtered: 100.00
 Extra: Using index
1
2
3
4
5
6
7
8
9
10
11
12
13
14

在大多数存储引擎中，索引只能覆盖访问索引的一部分的列的查询。然而，InnoDB 实际上可以进一步优化这一点。回想一下，InnoDB 的次要索引在其叶节点上保存主键值。这意味着 InnoDB 的次要索引实际上有“额外的列”，InnoDB 可以用来覆盖查询。

例如，sakila.actor表使用 InnoDB，并在last_name上有一个索引，因此索引可以覆盖检索主键列actor_id的查询，即使该列在技术上不是索引的一部分：

mysql> EXPLAIN SELECT actor_id, last_name
    -> FROM sakila.actor WHERE last_name = 'HOPPER'\G
*************************** 1\. row ***************************
 id: 1
 select_type: SIMPLE
 table: actor
 partitions: NULL
 type: ref
 possible_keys: idx_actor_last_name
 key: idx_actor_last_name
 key_len: 182
 ref: const
 rows: 2
 filtered: 100.00
 Extra: Using index
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

用于排序的索引扫描

MySQL 有两种产生有序结果的方式：它可以使用排序操作，或者可以按顺序扫描索引。您可以通过查看EXPLAIN中type列中的“index”来判断 MySQL 是否计划扫描索引。（不要将此与Extra列中的“Using index”混淆。）

扫描索引本身很快，因为它只需要从一个索引条目移动到下一个。然而，如果 MySQL 没有使用索引来覆盖查询，它将不得不查找在索引中找到的每一行。这基本上是随机 I/O，因此按索引顺序读取数据通常比顺序表扫描慢，尤其是对于 I/O 受限的工作负载。

MySQL 可以同时用同一个索引进行排序和查找行。如果可能的话，最好设计您的索引，使其同时对这两个任务有用。

仅当索引的顺序与ORDER BY子句完全相同且所有列都按相同方向（升序或降序）排序时，按索引对结果排序才有效。¹⁰ 如果查询连接多个表，则仅当ORDER BY子句中的所有列都引用第一个表时才有效。ORDER BY子句也具有与查找查询相同的限制：它需要形成索引的最左前缀。在所有其他情况下，MySQL 使用排序。

如果索引的前导列有常量，那么ORDER BY子句不必指定索引的最左前缀。如果WHERE子句或JOIN子句为这些列指定了常量，它们可以“填补”索引中的空缺。

例如，标准 Sakila 示例数据库中的rental表具有一个索引（rental_date、inventory_id、customer_id）：

CREATE TABLE rental (
 ...
 PRIMARY KEY (rental_id),
 UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
 KEY idx_fk_inventory_id (inventory_id),
 KEY idx_fk_customer_id (customer_id),
 KEY idx_fk_staff_id (staff_id),
 ...
);
1
2
3
4
5
6
7
8
9

MySQL 使用rental_date索引来排序以下查询，从EXPLAIN中看到没有filesort¹¹：

mysql> EXPLAIN SELECT rental_id, staff_id FROM sakila.rental
    -> WHERE rental_date = '2005-05-25'
    -> ORDER BY inventory_id, customer_id\G
*************************** 1\. row ***************************
 type: ref
 possible_keys: rental_date
 key: rental_date
 rows: 1
 Extra: Using where
1
2
3
4
5
6
7
8
9

这个例子可以工作，即使ORDER BY子句本身不是索引的最左前缀，因为我们为索引的第一列指定了相等条件。

这里有一些更多可以使用索引进行排序的查询。这个例子有效，因为查询为索引的第一列提供了一个常量，并指定了第二列的ORDER BY。这两者一起形成了索引的最左前缀：

... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;
1

以下查询也有效¹²，因为ORDER BY中的两列是索引的最左前缀：

... WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id;
1

以下是一些无法使用索引进行排序的查询。

此查询使用两种不同的排序方向，但索引的列都是按升序排序的：

... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;
1

在这里，ORDER BY引用了不在索引中的列：

... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;
1

在这里，WHERE和ORDER BY不构成索引的最左前缀：

... WHERE rental_date = '2005-05-25' ORDER BY customer_id;
1

此查询在第一列上有一个范围条件，因此 MySQL 不使用索引的其余部分：

... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;
1

在这里，inventory_id列有多个相等。对于排序而言，这基本上与范围相同：

... WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY 
customer_id;
1
2

这里有一个例子，MySQL 理论上可以使用索引来排序连接，但实际上并没有这样做，因为优化器将film_actor表放在连接的第二个位置：

mysql> EXPLAIN SELECT actor_id, title FROM sakila.film_actor
    -> INNER JOIN sakila.film USING(film_id) ORDER BY actor_id\G
+------------+----------------------------------------------+
| table      | Extra                                        |
+------------+----------------------------------------------+
| film       | Using index; Using temporary; Using filesort |
| film_actor | Using index                                  |
+------------+----------------------------------------------+
1
2
3
4
5
6
7
8

按索引排序最重要的用途之一是具有ORDER BY和LIMIT子句的查询。

冗余和重复的索引

不幸的是，MySQL 允许您在同一列上创建重复索引。这样做只会返回一个警告，并不会阻止您这样做。MySQL 必须单独维护每个重复索引，并且在优化查询时查询优化器将考虑它们中的每一个。这可能会影响性能，还会浪费磁盘空间。

重复索引是在相同顺序的相同列集上创建的相同类型的索引。您应该尽量避免创建它们，并在发现它们时将其删除。

有时您可能会在不知情的情况下创建重复索引。例如，看看以下代码：

CREATE TABLE test (
 ID INT NOT NULL PRIMARY KEY,
 A INT NOT NULL,
 B INT NOT NULL,
 UNIQUE(ID),
 INDEX(ID)
) ENGINE=InnoDB;
1
2
3
4
5
6
7

一个经验不足的用户可能会认为这标识了列作为主键的角色，添加了UNIQUE约束，并添加了一个索引供查询使用。实际上，MySQL 使用索引实现UNIQUE约束和PRIMARY KEY约束，因此实际上在同一列上创建了三个索引！除非您希望在同一列上有不同类型的索引以满足不同类型的查询，否则通常没有理由这样做。¹³

多余的索引与重复的索引有些不同。如果在(A, B)上有一个索引，那么在(A)上有另一个索引将是多余的，因为它是第一个索引的前缀。也就是说，(A, B)上的索引也可以作为仅在(A)上的索引使用。（这种冗余类型仅适用于 B 树索引。）然而，在(B, A)上的索引不会是多余的，(B)上的索引也不会是多余的，因为B不是(A, B)的最左前缀。此外，不管它们覆盖哪些列，不同类型的索引（如全文索引）都不会对 B 树索引产生冗余。

多余的索引通常出现在人们向表中添加索引时。例如，有人可能在(A, B)上添加索引，而不是扩展现有的(A)索引以覆盖(A, B)。另一种可能发生的情况是将索引更改为覆盖(A, ID)。ID列是主键，因此已经包含在内。

在大多数情况下，您不希望有多余的索引，为了避免它们，您应该扩展现有的索引而不是添加新的。但是，有时您会因性能原因需要多余的索引。扩展现有索引可能会使其变得更大，并降低某些查询的性能。

例如，如果您在整数列上建立了一个索引，并将其扩展为长VARCHAR列，它可能会变得明显较慢。特别是如果您的查询使用索引作为覆盖索引时，情况尤其如此。

考虑以下的userinfo表：

CREATE TABLE userinfo (
 id int unsigned NOT NULL AUTO_INCREMENT,
 name varchar(64) NOT NULL DEFAULT '',
 email varchar(64) NOT NULL DEFAULT '',
 password varchar(64) NOT NULL DEFAULT '',
 dob date DEFAULT NULL,
 address varchar(255) NOT NULL DEFAULT '',
 city varchar(64) NOT NULL DEFAULT '',
 state_id tinyint unsigned NOT NULL DEFAULT '0',
 zip varchar(8) NOT NULL DEFAULT '',
 country_id smallint unsigned NOT NULL DEFAULT '0',
 account_type varchar(32) NOT NULL DEFAULT '',
 verified tinyint NOT NULL DEFAULT '0',
 allow_mail tinyint unsigned NOT NULL DEFAULT '0',
 parrent_account int unsigned NOT NULL DEFAULT '0',
 closest_airport varchar(3) NOT NULL DEFAULT '',
 PRIMARY KEY (id),
 UNIQUE KEY email (email),
 KEY country_id (country_id),
 KEY state_id (state_id)
) ENGINE=InnoDB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

这个表包含一百万行，对于每个state_id，大约有 20,000 条记录。有一个在state_id上的索引，对于以下查询很有用。我们将这个查询称为 Q1：

SELECT count(*) FROM userinfo WHERE state_id=5;
1

一个简单的基准测试显示，该查询的执行速率接近每秒 115 次查询（QPS）。我们还有一个相关查询，不仅仅是计算行数，而是检索几列。这是 Q2：

SELECT state_id, city, address FROM userinfo WHERE state_id=5;
1

对于这个查询，结果小于 10 QPS。¹⁴ 提高其性能的简单解决方案是将索引扩展到（state_id、city、address），这样索引将覆盖查询：

ALTER TABLE userinfo DROP KEY state_id,
ADD KEY state_id_2 (state_id, city, address);
1
2

扩展索引后，Q2 运行更快，但 Q1 运行更慢。如果我们真的关心让两个查询都快速，我们应该保留两个索引，即使单列索引是多余的。表 7-2 显示了两个查询和索引策略的详细结果。

表 7-2. 使用不同索引策略的SELECT查询的 QPS 基准结果

	`仅 state_id`	`仅 state_id_2`	`state_id 和 state_id_2 都有`
查询 1	108.55	100.33	107.97
查询 2	12.12	28.04	28.06

拥有两个索引的缺点是维护成本。表 7-3 显示了向表中插入一百万行需要多长时间。

表 7-3. 使用不同索引策略插入一百万行的速度

	`仅 state_id`	`state_id 和 state_id_2 都有`
InnoDB，足够的内存容纳两个索引	80 秒	136 秒

如您所见，向具有更多索引的表中插入新行会更慢。这在一般情况下是正确的：添加新索引可能会对INSERT、UPDATE和DELETE操作产生性能影响，特别是如果新索引导致内存限制。

处理多余和重复索引的解决方案很简单，就是删除它们，但首先您需要识别它们。您可以针对INFORMATION_SCHEMA表编写各种复杂的查询，但也有更简单的技术。您可以使用 Percona Toolkit 附带的pt-duplicate-key-checker工具，该工具分析表结构并建议重复或多余的索引。

在确定哪些索引适合删除或扩展时要小心。请记住，在 InnoDB 中，我们示例表中列(A)上的索引实际上等同于(A, ID)上的索引，因为主键附加到辅助索引叶节点。如果你有一个查询，比如WHERE A = 5 ORDER BY ID，那么索引将非常有帮助。但是如果你将索引扩展到(A, B)，那么它实际上变成了(A, B, ID)，查询将开始对查询的ORDER BY部分使用文件排序。最好使用诸如 Percona Toolkit 的pt-upgrade之类的工具仔细验证你计划的更改。

对于这两种情况，在删除索引之前考虑使用 MySQL 8.0 的隐形索引功能。通过这个功能，你可以发出一个ALTER TABLE语句将一个索引标记为隐形，这意味着优化器在规划查询时会忽略它。如果你发现即将删除的索引很重要，你可以很容易地将其重新设为可见，而无需重新创建索引。

未使用的索引

除了重复和冗余的索引，你可能有一些服务器根本不使用的索引。这些只是多余的负担，你应该考虑删除它们。¹⁵

识别未使用的索引的最佳方法是使用performance_schema和sys，我们在第三章中详细介绍了这两个功能。sys模式创建了一个table_io_waits_summary_by_index_usage表的视图，可以轻松告诉我们哪些索引未使用：

mysql> SELECT * FROM sys.schema_unused_indexes;
+---------------+---------------+-----------------------------+
| object_schema | object_name   | index_name                  |
+---------------+---------------+-----------------------------+
| sakila        | actor         | idx_actor_last_name         |
| sakila        | address       | idx_fk_city_id              |
| sakila        | address       | idx_location                |
| sakila        | payment       | fk_payment_rental           |
.. trimmed for brevity ..
1
2
3
4
5
6
7
8
9

索引和表维护

一旦你创建了具有适当数据类型和添加索引的表，你的��作并没有结束：你仍需要维护表和索引以确保它们性能良好。表维护的三个主要目标是查找和修复损坏、维护准确的索引统计信息和减少碎片化。

查找和修复表损坏

表可能遇到的最糟糕的情况就是损坏。所有存储引擎都可能由于硬件问题或 MySQL 或操作系统中的内部错误而遇到索引损坏，尽管在 InnoDB 中很少遇到这种情况。

损坏的索引可能导致查询返回不正确的结果，在没有重复值的情况下引发重复键错误，甚至导致锁定和崩溃。如果你遇到奇怪的行为，比如一个你认为不应该发生的错误，运行CHECK TABLE查看表是否损坏。（请注意，某些存储引擎不支持此命令，而其他支持多个选项以指定他们检查表的彻底程度。）CHECK TABLE通常可以捕捉到大多数表和索引错误。

你可以使用REPAIR TABLE命令修复损坏的表，但并非所有存储引擎都支持此功能。在这些情况下，你可以执行一个“no-op” ALTER，比如修改表以使用当前使用的相同存储引擎。以下是一个针对 InnoDB 表的示例：

ALTER TABLE <table> ENGINE=INNODB;
1

或者，你可以导出数据并重新加载。但是，如果损坏发生在系统区域或表的“行数据”区域而不是索引中，你可能无法使用任何这些选项。在这种情况下，你可能需要从备份中恢复表，或尝试从损坏的文件中恢复数据。

如果您在 InnoDB 存储引擎中遇到损坏，那么肯定出了严重问题，您需要立即进行调查。InnoDB 不应该出现损坏。其设计使其对损坏非常有韧性。损坏表明可能存在硬件问题，如内存或磁盘故障（可能性较大），管理员错误，例如在 MySQL 外部操纵数据库文件（可能性较大），或者 InnoDB 错误（可能性较小）。通常的原因是诸如尝试使用rsync进行备份之类的错误。没有任何查询您可以执行——没有一个——是您应该避免的，因为它会损坏 InnoDB 的数据。没有任何隐藏的枪指向您的脚。如果通过针对 InnoDB 执行查询来损坏 InnoDB 的数据，那么 InnoDB 中存在错误，并且这绝不是您的错。

如果您遇到数据损坏，最重要的是尝试确定为什么会发生损坏；不要仅仅修复数据，否则损坏可能会再次出现。您可以通过使用innodb_force_recovery参数将 InnoDB 置于强制恢复模式来修复数据；有关详细信息，请参阅 MySQL 手册。

更新索引统计信息

当存储引擎向优化器提供关于查询可能检查的行数的不精确信息，或者当查询计划过于复杂以至于无法准确知道各个阶段将匹配多少行时，优化器将使用索引统计信息来估计行数。MySQL 的优化器是基于成本的，主要成本指标是查询将访问的数据量。如果统计信息从未生成过或者已经过时，优化器可能会做出错误的决定。解决方案是运行ANALYZE TABLE，这将重新生成统计信息。

您可以使用SHOW INDEX FROM命令检查索引的基数。例如：

mysql> SHOW INDEX FROM sakila.actor\G
*************************** 1\. row ***************************
 Table: actor
 Non_unique: 0
 Key_name: PRIMARY
 Seq_in_index: 1
 Column_name: actor_id
 Collation: A
 Cardinality: 200
 Sub_part: NULL
 Packed: NULL
 Null:
 Index_type: BTREE
 Comment:
*************************** 2\. row ***************************
 Table: actor
 Non_unique: 1
 Key_name: idx_actor_last_name
 Seq_in_index: 1
 Column_name: last_name
 Collation: A
 Cardinality: 200
 Sub_part: NULL
 Packed: NULL
 Null:
 Index_type: BTREE
 Comment:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

这个命令提供了相当多的索引信息，MySQL 手册详细解释了这些信息。不过，我们想要提醒您关注Cardinality列。这显示存储引擎估计索引中有多少个不同的值。您也可以从INFORMATION_SCHEMA.STATISTICS表中获取这些数据。例如，您可以编写针对INFORMATION_SCHEMA表的查询，以查找具有非常低选择性的索引。但是请注意，在具有大量数据的服务器上，这些元数据表可能会给服务器带来很大的负载。

值得进一步探索 InnoDB 的统计信息。这些统计信息是通过对索引中的几个随机页面进行抽样生成的，并假设其余索引看起来类似。抽样的页面数量由innodb_stats_sample_pages变量控制。将其设置为大于默认值 8 的值理论上可以帮助生成更具代表性的索引统计信息，特别是在非常大的表上，但效果可能有所不同。

当首次打开表时，运行ANALYZE TABLE时以及表的大小发生显著变化时，InnoDB 会为索引计算统计信息。

InnoDB 还会为针对一些INFORMATION_SCHEMA表的查询、SHOW TABLE STATUS和SHOW INDEX查询以及当 MySQL 命令行客户端启用自动完成时计算统计信息。在具有大量数据或 I/O 速度慢的大型服务器上，这实际上可能成为一个相当严重的问题。导致抽样发生的客户端程序或监控工具可能会在服务器上造成大量锁定和重负载，同时让用户因启动时间过长而感到沮丧。而且，您无法观察索引统计信息而不更改它们，因为SHOW INDEX会更新统计信息。您可以禁用innodb_stats_on_metadata选项以避免所有这些问题。

减少索引和数据碎片化

B-tree 索引可能会变得碎片化，这可能会降低性能。碎片化的索引可能在磁盘上填充不良和/或非顺序。

按设计，B 树索引需要随机磁盘访问以“潜入”到叶子页面，因此随机访问是规则，而不是例外。然而，如果叶子页面是物理上连续且紧凑的，它们仍然可以表现得更好。如果不是这样，我们说它们是碎片化，范围扫描或完整索引扫描可能慢几倍。这对于索引覆盖查询尤为重要。

表的数据存储也可能变得碎片化。然而，数据存储碎片化比索引碎片化更复杂。数据碎片化有三种类型：

行碎片化

当行在多个位置以多个部分存储时，就会发生这种类型的碎片化。即使查询只需要从索引中获取一行，行碎片化也会降低性能。

行内碎片化

当逻辑上连续的页面或行在磁盘上不按顺序存储时，就会发生这种类型的碎片化。这会影响全表扫描和聚集索引范围扫描等操作，这些操作通常受益于磁盘上的顺序数据布局。

空闲空间碎片化

当数据页面中有大量空白空间时，就会发生这种类型的碎片化。这会导致服务器读取许多不需要的数据，这是一种浪费。

要对数据进行碎片整理，可以运行OPTIMIZE TABLE或转储并重新加载数据。这些方法适用于大多数存储引擎。对于不支持OPTIMIZE TABLE的存储引擎，可以使用一个空操作的ALTER TABLE重建表。只需将表更改为当前使用的引擎：

ALTER TABLE <table> ENGINE=<engine>;
1

摘要

正如你所看到的，索引是一个复杂的话题！MySQL 和存储引擎访问数据的方式以及索引的属性使索引成为影响数据访问的非常强大和灵活的工具，无论是在磁盘上还是在内存中。

大多数情况下，你会使用 B 树索引与 MySQL。其他类型的索引更适合特殊用途，当你应该使用它们以及它们如何提高查询响应时间时，通常是显而易见的。在本章中我们不再详细讨论它们，但最后回顾一下 B 树索引的属性和用途是值得的。

在选择索引并编写查询以利用它们时，请牢记以下三个原则：

单行访问速度较慢，尤其是在基于磁盘的存储中。（固态硬盘在随机 I/O 方面更快，但这一点仍然成立。）如果服务器从存储中读取一块数据，然后只访问其中的一行，那么就浪费了很多工作。最好的方法是读取包含你需要的许多行的块。
按顺序访问行范围是快速的，有两个原因。首先，顺序 I/O 不需要磁盘寻道，因此比随机 I/O 快，尤其是在基于磁盘的存储中。其次，如果服务器可以按照你需要的顺序读取数据，就不需要执行任何后续工作来对其进行排序，而且GROUP BY查询也不需要对行进行排序和分组以计算聚合。
仅索引访问是快速的。如果一个索引包含查询所需的所有列，存储引擎就不需要通过查找表中的行来找到其他列。这避免了大量的单行访问，正如我们从第一点所知道的那样，这是缓慢的。

总之，尽量选择索引并编写查询，以避免单行查找，利用数据的固有顺序避免排序操作，并利用仅索引访问。这对应于 Lahdenmaki 和 Leach 在本章开头提到的三星级评级系统。

要为针对表的每个查询创建完美的索引将是很好的。不幸的是，有时这将需要一个不切实际的大量索引，而有时根本无法为给定查询创建三星级索引（例如，如果查询按两列排序，一列升序，另一列降序）。在这些情况下，您必须尽力而为或追求替代策略，如去规范化或摘要表。

能够理解索引工作原理并根据这种理解选择索引非常重要，而不是根据经验法则或启发式法则，如“在多列索引中将最具选择性的列放在前面”或“应该为WHERE子句中出现的所有列建立索引”。

如何知道您的模式是否已经足够好地进行了索引？像往常一样，我们建议您从响应时间的角度来提出问题。查找那些要么花费太长时间要么对服务器负载贡献过大的查询。检查需要关注的查询的模式、SQL 和索引结构。确定查询是否需要检查太多行，执行检索后排序或使用临时表，访问随机 I/O 的数据，或者查找完整行以检索未包含在索引中的列。

如果发现某个查询无法从索引的所有可能优势中受益，请查看是否可以创建更好的索引以提高性能。如果不能，也许可以重写查询，使其能够使用已经存在或可以创建的索引。这就是下一章要讨论的内容。

¹ SSD 具有不同的性能特征，我们在第四章中进行了介绍。索引原则仍然成立，但我们试图避免的惩罚在 SSD 上并不像在传统驱动器上那么大。

² 许多存储引擎实际上使用 B+树索引，其中每个叶节点包含指向下一个节点的链接，以便通过节点快速进行范围遍历。请参考计算机科学文献以获取 B 树索引的详细解释。

³ 这是特定于 MySQL 的，甚至是特定于版本的。其他一些数据库可以使用非前导索引部分，尽管使用完整前缀通常更有效。MySQL 可能会在未来提供此选项；我们将在本章后面展示解决方法。

⁴ MySQL 的优化器是一个非常神秘而强大的设备，其神秘性仅次于其强大性。由于它计算最佳查询计划的方式，您应该依靠在自己的查询和工作负载中使用EXPLAIN来确定最优策略。

⁵ Oracle 用户将熟悉术语索引组织表，其含义相同。

⁶ 这并不总是正确的，您马上就会看到。

⁷ 非聚集索引设计并不总是能够提供单操作行查找。当一行发生变化时，它可能不再适合原始位置，因此您可能会在表中得到碎片化的行或“转发地址”，这两者都会导致更多的工作来查找行。

⁸ 值得指出的是，这是一个真实的表，具有辅助索引和许多列。如果我们移��这些并仅对主键性能进行基准测试，差异将更大。

⁹ 在Extra列中找到“Using index”可能会与type列中的“index”混淆。然而，它们完全不同。type列与覆盖索引无关；它显示查询的访问类型，或者查询将如何查找行。MySQL 手册将其称为连接类型。

¹⁰ 如果需要按不同方向排序，有时候一个小技巧是存储一个反转或取反的值。

¹¹ MySQL 将其称为“filesort”，但并非总是在文件系统上使用文件。只有在内存无法对数据进行排序时才会访问磁盘。

¹² 我们应该注意，虽然这可以使用索引进行排序，在我们的测试中，8.0.25 版本的优化器直到我们使用FORCE INDEX FOR ORDER BY条件才使用索引——这是另一个提醒，优化器可能不会按照你的期望执行操作，你应该始终使用EXPLAIN进行检查。

¹³ 如果是不同类型的索引，索引并不一定是重复的；通常有很好的理由同时拥有KEY(col)和FULLTEXT KEY(col)。

¹⁴ 我们在这里使用了一个内存示例。当表变得更大且工作负载变得 I/O 受限时，数字之间的差异将会更大。使用覆盖索引后，COUNT()查询通常会快上一百倍甚至更多。

¹⁵ 一些索引充当唯一约束，因此即使某个索引未用于查询，也可能用于防止重复值。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Monodyee/article/detail/601619

高性能 MySQL 第四版（GPT 重译）（二）

第四章：操作系统和硬件优化

什么限制了 MySQL 的性能？

如何为 MySQL 选择 CPU

平衡内存和磁盘资源

缓存、读取和写入

你的工作集是什么？

固态存储

闪存存储概述

垃圾回收

RAID 性能优化

RAID 故障、恢复和监控

RAID 配置和缓存

RAID 条带块大小

RAID 缓存

网络配置

选择文件系统

选择磁盘队列调度程序

内存和交换

操作系统状态

如何阅读 vmstat 输出

如何阅读 iostat 输出

其他有用的工具

总结

第五章：优化服务器设置

MySQL 的配置工作原理

警告

语法、作用域和动态性

提示

持久化系统变量

设置变量的副作用

规划你的变量更改

不要做什么

创建一个 MySQL 配置文件

最小配置

检查 MySQL 服务器状态变量

配置内存使用

每个连接的内存需求

为操作系统保留内存

InnoDB 缓冲池

线程缓存

配置 MySQL 的 I/O 行为

图 5-1。InnoDB 的缓冲区和文件

InnoDB 事务日志

日志缓冲区

InnoDB 如何刷新日志缓冲区

如何打开和刷新 InnoDB 的日志文件和数据文件

警告

InnoDB 表空间

配置表空间

警告

旧的行版本和表空间

其他 I/O 配置选项

配置 MySQL 并发性

安全设置

高级 InnoDB 设置

摘要

第六章：模式设计与管理

选择最佳数据类型

整数

实数

字符串类型

VARCHAR 和 CHAR 类型

BLOB 和 TEXT 类型

使用 ENUM 而不是字符串类型

警告

日期和时间类型

位压缩数据类型

JSON 数据

选择标识符

整数类型

ENUM 和 SET

字符串类型

特殊数据类型

MySQL 中的模式设计陷阱

太多列

太多的连接

万能的 ENUM

伪装的 ENUM

NULL 并非在这里发明

`VARCHAR` 和 `CHAR` 类型

图 7-4。`layout_test`表的 InnoDB 主键布局

图 7-5。`layout_test`表的 InnoDB 辅助索引布局