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

高性能 MySQL 第四版（GPT 重译）（二）(1)https://developer.aliyun.com/article/1484284

配置 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

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

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

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

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

...ibdata3:1G:autoextend

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

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

管理单个表空间可能会很麻烦，特别是如果它自动扩展并且您想要回收空间（因此，我们建议禁用自动扩展功能，或者至少设置一个合理的空间上限）。回收空间的唯一方法是转储数据，关闭 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

如果您有一个大的撤销日志，并且您的表空间因此而增长，您可以强制 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 ');

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

mysql> SELECT CONCAT("'", char_col, "'") FROM char_test;
+----------------------------+
| CONCAT("'", char_col, "'") |
+----------------------------+
| 'string1'                  |
| ' string2'                 |
| 'string3'                  |
+----------------------------+

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

mysql> SELECT CONCAT("'", varchar_col, "'") FROM varchar_test;
+-------------------------------+
| CONCAT("'", varchar_col, "'") |
+-------------------------------+
| 'string1'                     |
| ' string2'                    |
| 'string3 '                    |
+-------------------------------+

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');

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

mysql> SELECT e + 0 FROM enum_test;
+-------+
| e + 0 |
+-------+
|     1 |
|     3 |
|     2 |
+-------+

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

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

mysql> SELECT e FROM enum_test ORDER BY e;
+-------+
| e     |
+-------+
| fish  |
| apple |
| dog   |
+-------+

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

mysql> SELECT e FROM enum_test ORDER BY FIELD(e, 'apple', 'dog', 'fish');
+-------+
| e     |
+-------+
| apple |
| dog   |
| fish  |
+-------+

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

因为 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;

该表包含约 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;

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

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

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

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

在将列转换为 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 |
+------+-------+

这可能会非常令人困惑，因此我们建议您谨慎使用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 |
+---------------------+

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

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     |
+-------+

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

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"
   }
]

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

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

mysql> DESC asteroids_json;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| json_data | json | YES  |     | NULL    |       |
+-----------+------+------+-----+---------+-------+

其次，我们将此 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    |       |
+----------------+-------------+------+-----+---------+-------+

第一个比较是数据大小：

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

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

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

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

SELECT designation FROM asteroids_sql;

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

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

SELECT json_data->'$.designation' FROM asteroids_json

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

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

ALTER TABLE asteroids_sql ADD INDEX ( designation );

高性能 MySQL 第四版（GPT 重译）（二）(3)https://developer.aliyun.com/article/1484317