高性能 MySQL 第四版（GPT 重译）（二）(3)-阿里云开发者社区

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

当我们进行单行查找时，我们的 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 );

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

mysql> DESC asteroids_json;
+-------------+-------------+------+-----+---------+-------------------+
| Field       | Type        | Null | Key | Default | Extra             |
+-------------+-------------+------+-----+---------+-------------------+
| json_data   | json        | YES  |     | NULL    |                   |
| designation | varchar(30) | YES  | MUL | NULL    | VIRTUAL GENERATED |
+-------------+-------------+------+-----+---------+-------------------+

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

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

这个查询在 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')

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

伪装的 ENUM

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

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

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

NULL 并非在这里发明

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

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

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

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

那个虚假的全零值可能会引起很多问题。（您可以配置 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;

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

索引将包含表中每行的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);

现在我们有一个示例数据集。结果并不真实分布，并且我们使用了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          |
+-----+----------------+

注意每个值大约有 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  |
+-----+------+

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

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

mysql> SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;
+-------------------------------+
| COUNT(DISTINCT city)/COUNT(*) |
+-------------------------------+
|                        0.0312 |
+-------------------------------+

如果我们的目标选择性接近 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 |
+--------+--------+--------+--------+--------+

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

仅仅看平均选择性并不是一个好主意。警告是最坏情况选择性也很重要。平均选择性可能会让您认为四或五个字符的前缀已经足够好了，但如果您的数据非常不均匀，那可能是一个陷阱。如果您使用值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 |
+-----+------+

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

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

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

前缀索引可以使索引变得更小更快，但它们也有缺点：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)
);

这种索引策略通常是因为人们给出模糊但听起来权威的建议，比如“在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

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;

您应该在(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

根据经验法则，我们应该将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

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

如果你正在使用来自工具（如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

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

ALTER TABLE payment ADD KEY(customer_id, staff_id);

与前缀索引一样，问题通常出现在具有高于正常基数的特殊值上。例如，我们曾看到应用程序将未登录的用户视为“访客”用户，在会话表和其他记录用户活动的地方，这些用户获得特殊的用户 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

这个查询似乎没有一个很好的索引，所以客户要求我们看看是否可以改进。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

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

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