MySQL8-中文参考-十六-

MySQL8 中文参考（十六）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

8.6 MySQL 企业加密

原文：dev.mysql.com/doc/refman/8.0/en/enterprise-encryption.html

8.6.1 MySQL 企业加密安装和升级

8.6.2 配置 MySQL 企业加密

8.6.3 MySQL 企业加密用法和示例

8.6.4 MySQL 企业加密功能参考

8.6.5 MySQL 企业加密组件功能描述

8.6.6 MySQL 企业加密传统功能描述

注意

MySQL 企业加密是 MySQL 企业版中包含的扩展，这是一个商业产品。要了解更多关于商业产品的信息，请访问www.mysql.com/products/。

MySQL 企业版包括一组加密函数，这些函数在 SQL 级别暴露了 OpenSSL 的功能。这些函数使企业应用程序能够执行以下操作：

• 使用公钥非对称加密实现额外的数据保护

• 创建公钥、私钥和数字签名

• 执行非对称加密和解密

• 使用加密哈希进行数字签名和数据验证和验证

在 MySQL 8.0.30 之前的版本中，这些功能基于openssl_udf共享库。从 MySQL 8.0.30 开始，它们由 MySQL 组件component_enterprise_encryption提供。

8.6.1 MySQL Enterprise Encryption 安装和升级

原文：dev.mysql.com/doc/refman/8.0/en/enterprise-encryption-installation.html

在 MySQL 8.0.30 之前的版本中，MySQL Enterprise Encryption 提供的函数是通过单独创建它们而安装的，基于openssl_udf共享库。从 MySQL 8.0.30 开始，这些函数由 MySQL 组件component_enterprise_encryption提供，安装组件会安装所有函数。从该版本开始，openssl_udf共享库中的函数已被弃用，您应该升级到组件。

• 从 MySQL 8.0.30 安装

• 到 MySQL 8.0.29 安装

• 升级 MySQL Enterprise Encryption

从 MySQL 8.0.30 安装

从 MySQL 8.0.30 开始，MySQL Enterprise Encryption 的函数由 MySQL 组件component_enterprise_encryption提供，而不是从openssl_udf共享库安装。如果您正在从早期版本升级到 MySQL 8.0.30，其中使用了 MySQL Enterprise Encryption，您创建的函数仍然可用并受支持。但是，这些旧版函数从此版本开始已被弃用，建议您安装组件。组件函数向后兼容。有关升级信息，请参见升级 MySQL Enterprise Encryption。

如果您正在升级，在安装组件之前，请使用DROP FUNCTION语句卸载旧版函数：

DROP FUNCTION asymmetric_decrypt;
DROP FUNCTION asymmetric_derive;
DROP FUNCTION asymmetric_encrypt;
DROP FUNCTION asymmetric_sign;
DROP FUNCTION asymmetric_verify;
DROP FUNCTION create_asymmetric_priv_key;
DROP FUNCTION create_asymmetric_pub_key;
DROP FUNCTION create_dh_parameters;
DROP FUNCTION create_digest;

函数名称必须以小写指定。这些语句需要对mysql数据库的DROP权限。

要安装组件，请发出一个INSTALL COMPONENT语句：

INSTALL COMPONENT "file://component_enterprise_encryption";

INSTALL COMPONENT需要mysql.component系统表的INSERT权限，因为它会向该表添加一行以注册组件。要验证组件是否已安装，请发出：

SELECT * FROM mysql.component;

在mysql.component中列出的组件在启动序列期间由加载器服务加载。

如果您需要卸载组件，请发出一个UNINSTALL COMPONENT语句：

UNINSTALL COMPONENT "file://component_enterprise_encryption";

查看更多详细信息，请参见第 7.5.1 节，“安装和卸载组件”。

安装组件会安装所有函数，因此您无需像在 MySQL 8.0.30 之前那样使用CREATE FUNCTION语句创建它们。卸载组件会卸载所有函数。

安装组件后，如果您希望组件函数支持解密和验证在 MySQL 8.0.30 之前生成的遗留函数产生的内容，请将组件的系统变量enterprise_encryption.rsa_support_legacy_padding设置为ON。此外，如果您希望更改组件函数生成的 RSA 密钥的最大长度允许值，请使用组件的系统变量enterprise_encryption.maximum_rsa_key_size设置一个适当的最大值。有关配置信息，请参见第 8.6.2 节，“配置 MySQL 企业加密”。

安装到 MySQL 8.0.29

在 MySQL 8.0.29 之前，MySQL 企业加密函数位于安装在插件目录（由plugin_dir系统变量命名的目录）中的可加载函数库文件中。函数库基本名称为openssl_udf，后缀取决于平台。例如，在 Linux 或 Windows 上的文件名分别为openssl_udf.so或openssl_udf.dll。

要从openssl_udf共享库文件安装函数，请使用CREATE FUNCTION语句。要从库中加载所有函数，请使用以下一组语句，根据需要调整文件名后缀：

CREATE FUNCTION asymmetric_decrypt RETURNS STRING
  SONAME 'openssl_udf.so';
CREATE FUNCTION asymmetric_derive RETURNS STRING
  SONAME 'openssl_udf.so';
CREATE FUNCTION asymmetric_encrypt RETURNS STRING
  SONAME 'openssl_udf.so';
CREATE FUNCTION asymmetric_sign RETURNS STRING
  SONAME 'openssl_udf.so';
CREATE FUNCTION asymmetric_verify RETURNS INTEGER
  SONAME 'openssl_udf.so';
CREATE FUNCTION create_asymmetric_priv_key RETURNS STRING
  SONAME 'openssl_udf.so';
CREATE FUNCTION create_asymmetric_pub_key RETURNS STRING
  SONAME 'openssl_udf.so';
CREATE FUNCTION create_dh_parameters RETURNS STRING
  SONAME 'openssl_udf.so';
CREATE FUNCTION create_digest RETURNS STRING
  SONAME 'openssl_udf.so';

安装后，函数将在服务器重新启动时保持安装状态。如果需要卸载函数，请使用DROP FUNCTION语句：

DROP FUNCTION asymmetric_decrypt;
DROP FUNCTION asymmetric_derive;
DROP FUNCTION asymmetric_encrypt;
DROP FUNCTION asymmetric_sign;
DROP FUNCTION asymmetric_verify;
DROP FUNCTION create_asymmetric_priv_key;
DROP FUNCTION create_asymmetric_pub_key;
DROP FUNCTION create_dh_parameters;
DROP FUNCTION create_digest;

在CREATE FUNCTION和DROP FUNCTION语句中，函数名称必须以小写指定。这与在函数调用时使用的方式不同，对于函数调用时，您可以使用任何大小写。

CREATE FUNCTION和DROP FUNCTION语句分别需要mysql数据库的INSERT和DROP权限。

openssl_udf共享库提供的函数允许最小密钥大小为 1024 位。您可以使用MYSQL_OPENSSL_UDF_RSA_BITS_THRESHOLD、MYSQL_OPENSSL_UDF_DSA_BITS_THRESHOLD和MYSQL_OPENSSL_UDF_DH_BITS_THRESHOLD环境变量设置最大密钥大小，如第 8.6.2 节，“配置 MySQL 企业加密”中所述。如果不设置最大密钥大小，RSA 算法的上限为 16384，DSA 算法的上限为 10000，由 OpenSSL 指定。

升级 MySQL 企业加密

如果您从使用openssl_udf共享库提供的函数的较早版本升级到 MySQL 8.0.30 或更高版本，则您创建的函数仍然可用并受支持。但是，这些传统函数从 MySQL 8.0.30 开始已被弃用，建议您安装 MySQL 企业加密组件component_enterprise_encryption。

在升级之前，在安装组件之前，您必须使用DROP FUNCTION语句卸载传统函数。有关如何执行此操作的说明，请参见从 MySQL 8.0.30 安装。

组件函数向后兼容：

• RSA 公钥和私钥由传统函数生成，可以与组件函数一起使用。

• 使用传统函数加密的数据可以由组件函数解密。

• 由传统函数创建的签名可以使用组件函数进行验证。

为了使组件函数支持由传统函数生成的内容的解密和验证，您必须将系统变量enterprise_encryption.rsa_support_legacy_padding设置为ON（默认为OFF）。有关配置信息，请参见第 8.6.2 节，“配置 MySQL 企业加密”。

由于组件函数使用的填充和密钥格式与当前标准不同，传统函数无法处理由组件函数创建的加密数据、公钥和签名。

component_enterprise_encryption 组件提供的新函数在行为和支持方面与 openssl_udf 共享库提供的旧版函数有一些差异。其中最重要的是：

• 旧版函数支持较旧的 DSA 算法和 Diffie-Hellman 密钥交换方法。组件函数仅使用普遍首选的 RSA 算法。

• 对于旧版函数，最小 RSA 密钥大小低于当前最佳实践。组件函数遵循当前最佳实践的最小 RSA 密钥大小。

• 旧版函数仅支持 SHA2 用于摘要，并要求摘要用于签名。组件函数还支持 SHA3 用于摘要（前提是使用 OpenSSL 1.1.1），并且不要求摘要用于签名，尽管它们支持。

• asymmetric_encrypt() 旧版函数支持使用私钥进行加密。asymmetric_encrypt() 组件函数仅接受公钥。建议您在旧版函数中也仅使用公钥进行加密。

• create_dh_parameters() 和 asymmetric_derive() 用于 Diffie-Hellman 密钥交换方法的旧版函数不在 component_enterprise_encryption 组件中提供。

表 1 总结了由 openssl_udf 共享库提供的旧版函数与 MySQL 8.0.30 中 component_enterprise_encryption 组件提供的函数之间在支持和操作方面的技术差异。

表 8.48 MySQL 企业加密函数

能力	旧版函数（到 MySQL 8.0.29）	组件函数（从 MySQL 8.0.30）
加密方法	RSA, DSA, Diffie-Hellman (DH)	仅限 RSA
用于加密的密钥	私钥或公钥	仅限公钥
RSA 密钥格式	PKCS #1 v1.5	PKCS #8
最小 RSA 密钥大小	1024 位	2048 位
最大 RSA 密钥大小限制	使用环境变量 MYSQL_OPENSSL_UDF_RSA_BITS_THRESHOLD 设置，默认限制为算法最大 16384	使用系统变量 enterprise_encryption.maximum_rsa_key_size 设置，默认限制为 4096
摘要算法	SHA2	SHA2, SHA3 (需要使用 OpenSSL 1.1.1)
签名	需要摘要	支持摘要但不需要，可以使用任意长度的任意字符串
输出填充	RSAES-PKCS1-v1_5	RSAES-OAEP
签名填充	RSASSA-PKCS1-v1_5	RSASSA-PSS

8.6.2 配置 MySQL 企业加密

原文：dev.mysql.com/doc/refman/8.0/en/enterprise-encryption-configuring.html

MySQL 企业加密允许您限制密钥的长度，以满足您的安全要求，同时平衡资源使用。您还可以配置 MySQL 8.0.30 提供的component_enterprise_encryption组件的函数，以支持解密和验证由遗留openssl_udf共享库函数生成的内容。

组件函数对遗留函数的解密支持

默认情况下，MySQL 8.0.30 提供的component_enterprise_encryption组件的函数不会解密由早期版本中openssl_udf共享库提供的遗留函数生成的加密文本，或验证签名。组件函数假定加密文本使用 RSAES-OAEP 填充方案，签名使用 RSASSA-PSS 签名方案。然而，遗留函数生成的加密文本使用 RSAES-PKCS1-v1_5 填充方案，遗留函数生成的签名使用 RSASSA-PKCS1-v1_5 签名方案。

如果您希望组件函数支持 MySQL 8.0.30 之前的遗留函数生成的内容，请将组件的系统变量enterprise_encryption.rsa_support_legacy_padding设置为ON。该系统变量在安装组件时可用。当您将其设置为ON时，组件函数首先尝试解密或验证内容，假设其具有其正常方案。如果这种方式不起作用，它们还会尝试解密或验证内容，假设其具有遗留函数使用的方案。这种行为不是默认设置，因为它会增加处理无法解密或验证的内容所需的时间。如果您不处理由遗留函数生成的内容，请将系统变量保持默认设置为OFF。

密钥长度限制

随着密钥长度的增加，MySQL 企业加密的密钥生成函数所需的 CPU 资源量也会增加。对于一些安装来说，如果应用程序频繁生成过长的密钥，这可能导致不可接受的 CPU 使用率。

OpenSSL 指定所有密钥的最小长度为 1024 位。OpenSSL 还指定 RSA 密钥的最大长度为 16384 位，DSA 密钥为 10000 位，DH 密钥为 10000 位。

从 MySQL 8.0.30 开始，component_enterprise_encryption 组件提供的函数对于 RSA 密钥有更高的最小长度要求，为 2048 位，这符合当前最佳实践的最小密钥长度。组件的系统变量 enterprise_encryption.maximum_rsa_key_size 指定了最大密钥长度，默认为 4096 位。您可以更改此值以允许 OpenSSL 允许的最大长度，即 16384 位。

对于 MySQL 8.0.30 之前的版本，openssl_udf 共享库提供的传统函数默认使用 OpenSSL 的最小和最大限制。如果最大值过高，您可以使用以下系统变量指定较低的最大密钥长度：

• MYSQL_OPENSSL_UDF_DSA_BITS_THRESHOLD：create_asymmetric_priv_key() 的最大 DSA 密钥长度（以位为单位）。此变量的最小值和最大值分别为 1024 和 10000。

• MYSQL_OPENSSL_UDF_RSA_BITS_THRESHOLD：create_asymmetric_priv_key() 的最大 RSA 密钥长度（以位为单位）。此变量的最小值和最大值分别为 1024 和 16384。

• MYSQL_OPENSSL_UDF_DH_BITS_THRESHOLD：create_dh_parameters() 的最大密钥长度（以位为单位）。此变量的最小值和最大值分别为 1024 和 10000。

要使用这些环境变量中的任何一个，请在启动服务器的进程的环境中设置它们。如果设置了这些变量，它们的值将优先于 OpenSSL 强制的最大密钥长度。例如，要为 create_asymmetric_priv_key() 的 DSA 和 RSA 密钥设置最大密钥长度为 4096 位，请设置这些变量：

export MYSQL_OPENSSL_UDF_DSA_BITS_THRESHOLD=4096
export MYSQL_OPENSSL_UDF_RSA_BITS_THRESHOLD=4096

本示例使用 Bourne shell 语法。其他 shell 的语法可能有所不同。

8.6.3 MySQL 企业加密用法和示例

原文：dev.mysql.com/doc/refman/8.0/en/enterprise-encryption-usage.html

在应用程序中使用 MySQL 企业加密时，调用适合所需操作的函数。本节演示了如何执行一些代表性任务。

在 MySQL 8.0.30 之前的版本中，MySQL 企业加密的函数基于openssl_udf共享库。从 MySQL 8.0.30 开始，这些函数由 MySQL 组件component_enterprise_encryption提供。在某些情况下，组件函数的行为与openssl_udf提供的旧版函数的行为不同。有关差异的列表，请参见升级 MySQL 企业加密。有关每个组件函数行为的详细信息，请参见第 8.6.4 节，“MySQL 企业加密函数参考”。

如果安装了旧版函数，然后升级到 MySQL 8.0.30 或更高版本，那么您创建的函数仍然可用，受支持，并且继续以相同方式工作。但是，从 MySQL 8.0.30 开始，它们已被弃用，并建议安装 MySQL 企业加密组件component_enterprise_encryption。有关升级说明，请参见从 MySQL 8.0.30 安装。

选择密钥长度和加密算法时，需要考虑以下一般因素：

• 私钥和公钥的加密强度随着密钥大小的增加而增加，但密钥生成的时间也会增加。

• 对于旧版函数，生成 DH 密钥比 RSA 或 DSA 密钥花费的时间更长。从 MySQL 8.0.30 开始，组件函数仅支持 RSA 密钥。

• 非对称加密函数消耗的资源比对称函数多。它们适用于加密少量数据以及创建和验证签名。对于加密大量数据，对称加密函数更快。MySQL 服务器提供了AES_ENCRYPT()和AES_DECRYPT()函数用于对称加密。

可以在运行时创建密钥字符串值，并使用SET、SELECT或INSERT将其存储到变量或表中。此示例适用于组件函数和旧版函数。

SET @priv1 = create_asymmetric_priv_key('RSA', 2048);
SELECT create_asymmetric_priv_key('RSA', 2048) INTO @priv2;
INSERT INTO t (key_col) VALUES(create_asymmetric_priv_key('RSA', 1024));

存储在文件中的密钥字符串值可以由具有FILE权限的用户使用LOAD_FILE()函数读取。摘要和签名字符串可以类似地处理。

• 创建私钥/公钥对

• 使用公钥加密数据和私钥解密数据

• 从字符串生成摘要

• 使用密钥对生成摘要

创建私钥/公钥对

这个示例适用于组件功能和传统功能：

-- Encryption algorithm
SET @algo = 'RSA';
-- Key length in bits; make larger for stronger keys
SET @key_len = 2048;

-- Create private key
SET @priv = create_asymmetric_priv_key(@algo, @key_len);
-- Derive corresponding public key from private key, using same algorithm
SET @pub = create_asymmetric_pub_key(@algo, @priv);

您可以使用密钥对来加密和解密数据，或者用来签名和验证数据。

使用公钥加密数据和私钥解密数据

这个示例适用于组件功能和传统功能。在这两种情况下，密钥对的成员必须是 RSA 密钥：

SET @ciphertext = asymmetric_encrypt(@algo, 'My secret text', @pub);
SET @plaintext = asymmetric_decrypt(@algo, @ciphertext, @priv);

从字符串生成摘要

这个示例适用于组件功能和传统功能：

-- Digest type
SET @dig_type = 'SHA512';

-- Generate digest string
SET @dig = create_digest(@dig_type, 'My text to digest');

使用密钥对生成摘要

密钥对可用于签署数据，然后验证签名是否与摘要匹配。这个示例适用于组件功能和传统功能：

-- Encryption algorithm; keys must
-- have been created using same algorithm
SET @algo = 'RSA';
–- Digest algorithm to sign the data
SET @dig_type = 'SHA512';

-- Generate signature for digest and verify signature against digest
SET @sig = asymmetric_sign(@algo, @dig, @priv, @dig_type);
-- Verify signature against digest
SET @verf = asymmetric_verify(@algo, @dig, @sig, @pub, @dig_type);

对于传统功能，签名需要一个摘要。对于组件功能，签名不需要摘要，并且可以使用任何数据字符串。这些功能中的摘要类型指的是用于签署数据的算法，而不是用于创建签名的原始输入的算法。这个示例是针对组件功能的：

-- Encryption algorithm; keys must
-- have been created using same algorithm
SET @algo = 'RSA';
–- Arbitrary text string for signature
SET @text = repeat('j', 256);
–- Digest algorithm to sign the data
SET @dig_type = 'SHA512';

-- Generate signature for digest and verify signature against digest
SET @sig = asymmetric_sign(@algo, @text, @priv, @dig_type);
-- Verify signature against digest
SET @verf = asymmetric_verify(@algo, @text, @sig, @pub, @dig_type);

8.6.4 MySQL Enterprise Encryption Function Reference

原文：dev.mysql.com/doc/refman/8.0/en/enterprise-encryption-function-reference.html

在 MySQL 8.0.30 及之后的版本中，MySQL Enterprise Encryption 的函数由 MySQL 组件component_enterprise_encryption提供。有关它们的描述，请参见第 8.6.5 节，“MySQL Enterprise Encryption Component Function Descriptions”。

在 MySQL 8.0.30 之前的版本中，MySQL Enterprise Encryption 的函数基于openssl_udf共享库。如果已安装这些函数，则它们在后续版本中仍然可用，但已被弃用。有关它们的描述，请参见第 8.6.6 节，“MySQL Enterprise Encryption Legacy Function Descriptions”。

有关升级到由 MySQL 组件component_enterprise_encryption提供的新组件函数的信息，以及传统函数与组件函数之间行为差异的列表，请参见升级 MySQL Enterprise Encryption。

8.6.5 MySQL Enterprise Encryption 组件功能描述

原文：dev.mysql.com/doc/refman/8.0/en/enterprise-encryption-functions.html

从 MySQL 8.0.30 版本开始，MySQL Enterprise Encryption 的功能由 MySQL 组件 component_enterprise_encryption 提供。本参考描述了这些功能。

有关升级到由 MySQL 组件 component_enterprise_encryption 提供的新组件功能以及传统功能与组件功能之间行为差异的列表，请参阅 升级 MySQL Enterprise Encryption。

在 MySQL 8.0.30 之前基于 openssl_udf 共享库的传统功能的参考是 第 8.6.6 节，“MySQL Enterprise Encryption 传统功能描述”。

MySQL Enterprise Encryption 功能具有以下一般特性：

• 对于错误类型的参数或不正确数量的参数，每个函数都会返回错误。

• 如果参数不适合允许函数执行请求的操作，则返回适当的 NULL 或 0。例如，如果函数不支持指定的算法，密钥长度过短或过长，或者预期为 PEM 格式密钥字符串的字符串不是有效密钥。

• 底层 SSL 库负责随机性初始化。

组件功能仅支持 RSA 加密算法。

更多示例和讨论，请参阅 第 8.6.3 节，“MySQL Enterprise Encryption 使用和示例”。

• asymmetric_decrypt(*algorithm*, *data_str*, *priv_key_str*)

  使用给定算法和密钥字符串解密加密字符串，并将结果明文作为二进制字符串返回。如果解密失败，则结果为 NULL。

  对于在 MySQL 8.0.29 之前使用的此函数的传统版本，请参阅 第 8.6.6 节，“MySQL Enterprise Encryption 传统功能描述”。

  默认情况下，component_enterprise_encryption 函数假定加密文本使用 RSAES-OAEP 填充方案。如果系统变量 enterprise_encryption.rsa_support_legacy_padding 设置为 ON（默认为 OFF），则该函数支持通过遗留的 openssl_udf 共享库函数加密的内容的解密。当设置为 ON 时，该函数还支持 RSAES-PKCS1-v1_5 填充方案，这是遗留的 openssl_udf 共享库函数使用的填充方案。当设置为 OFF 时，无法解密由遗留函数加密的内容，对于这样的内容，函数返回空输出。

  algorithm 是用于创建密钥的加密算法。支持的算法值为 'RSA'。

  data_str 是要解密的加密字符串，该字符串是使用 asymmetric_encrypt() 加密的。

  priv_key_str 是一个有效的 PEM 编码的 RSA 私钥。为了成功解密，密钥字符串必须对应于与 asymmetric_encrypt() 一起使用的公钥字符串，以产生加密字符串。asymmetric_encrypt() 组件函数仅支持使用公钥进行加密，因此解密需要使用相应的私钥。

  有关用法示例，请参阅 asymmetric_encrypt() 的描述。

• asymmetric_encrypt(*algorithm*, *data_str*, *pub_key_str*)

  使用给定的算法和密钥字符串加密字符串，并将结果密文作为二进制字符串返回。如果加密失败，则结果为 NULL。

  对于 MySQL 8.0.29 之前使用的旧版本函数，请参阅 Section 8.6.6, “MySQL Enterprise Encryption Legacy Function Descriptions”。

  algorithm 是用于创建密钥的加密算法。支持的算法值为 'RSA'。

  data_str 是要加密的字符串。此字符串的长度不能大于字节中的密钥字符串长度减去 42（用于填充）。

  pub_key_str 是一个有效的 PEM 编码的 RSA 公钥。asymmetric_encrypt() 组件函数仅支持使用公钥进行加密。

  要恢复原始未加密字符串，请将加密字符串传递给 asymmetric_decrypt()，以及用于加密的密钥对的另一部分，如下例所示：

  -- Generate private/public key pair
  SET @priv = create_asymmetric_priv_key('RSA', 2048);
  SET @pub = create_asymmetric_pub_key('RSA', @priv);
  
  -- Encrypt using public key, decrypt using private key
  SET @ciphertext = asymmetric_encrypt('RSA', 'The quick brown fox', @pub);
  SET @plaintext = asymmetric_decrypt('RSA', @ciphertext, @priv);
  

  假设：

  SET @s = a string to be encrypted
  SET @priv = a valid private RSA key string in PEM format
  SET @pub = the corresponding public RSA key string in PEM format
  

  然后这些身份关系成立：

  asymmetric_decrypt('RSA', asymmetric_encrypt('RSA', @s, @pub), @priv) = @s
  

• asymmetric_sign(*algorithm*, *text*, *priv_key_str*, *digest_type*)

  使用私钥签署摘要字符串或数据字符串，并将签名作为二进制字符串返回。如果签名失败，则结果为 NULL。

  对于在 MySQL 8.0.29 之前使用的此函数的旧版本，请参见 第 8.6.6 节，“MySQL 企业加密遗留函数描述”。

  algorithm 是用于创建密钥的加密算法。支持的算法值为 'RSA'。

  text 是数据字符串或摘要字符串。该函数接受摘要但不要求摘要，因为它还能处理任意长度的数据字符串。摘要字符串可以通过调用 create_digest() 生成。

  priv_key_str 是用于签署摘要字符串的私钥字符串。它必须是有效的 PEM 编码的 RSA 私钥。

  digest_type 是用于签署数据的算法。当使用 OpenSSL 1.0.1 时，支持的 digest_type 值为 'SHA224'、'SHA256'、'SHA384' 和 'SHA512'。如果使用 OpenSSL 1.1.1，则还可用额外的 digest_type 值 'SHA3-224'、'SHA3-256'、'SHA3-384' 和 'SHA3-512'。

  有关用法示例，请参阅 asymmetric_verify() 的描述。

• asymmetric_verify(*algorithm*, *text*, *sig_str*, *pub_key_str*, *digest_type*)

  验证签名字符串是否与摘要字符串匹配，并返回 1 或 0 表示验证成功或失败。如果验证失败，则结果为 NULL。

  对于在 MySQL 8.0.29 之前使用的此函数的旧版本，请参见 第 8.6.6 节，“MySQL 企业加密遗留函数描述”。

  默认情况下，component_enterprise_encryption 函数假定签名使用 RSASSA-PSS 签名方案。如果系统变量 enterprise_encryption.rsa_support_legacy_padding 设置为 ON（默认为 OFF），该函数支持验证由旧版 openssl_udf 共享库函数生成的签名。当设置为 ON 时，该函数还支持 RSASSA-PKCS1-v1_5 签名方案，如旧版 openssl_udf 共享库函数所使用的。当设置为 OFF 时，无法验证由旧版函数生成的签名，并且对于这种内容，函数返回空输出。

  algorithm 是用于创建密钥的加密算法。支持的算法值为 'RSA'。

  text 是数据字符串或摘要字符串。该组件函数接受摘要但不需要它们，因为它还能处理任意长度的数据字符串。可以通过调用 create_digest() 来生成摘要字符串。

  sig_str 是要验证的签名字符串。可以通过调用 asymmetric_sign() 来生成签名字符串。

  pub_key_str 是签名者的公钥字符串。它对应于传递给 asymmetric_sign() 以生成签名字符串的私钥。它必须是有效的 PEM 编码的 RSA 公钥。

  digest_type 是用于签署数据的算法。在使用 OpenSSL 1.0.1 时，支持的 digest_type 值为 'SHA224'、'SHA256'、'SHA384' 和 'SHA512'。如果使用 OpenSSL 1.1.1，则还可以使用额外的 digest_type 值 'SHA3-224'、'SHA3-256'、'SHA3-384' 和 'SHA3-512'。

  -- Set the encryption algorithm and digest type
  SET @algo = 'RSA';
  SET @dig_type = 'SHA512';
  
  -- Create private/public key pair
  SET @priv = create_asymmetric_priv_key(@algo, 2048);
  SET @pub = create_asymmetric_pub_key(@algo, @priv);
  
  -- Generate digest from string
  SET @dig = create_digest(@dig_type, 'The quick brown fox');
  
  -- Generate signature for digest and verify signature against digest
  SET @sig = asymmetric_sign(@algo, @dig, @priv, @dig_type);
  SET @verf = asymmetric_verify(@algo, @dig, @sig, @pub, @dig_type);
  

• create_asymmetric_priv_key(*algorithm*, *key_length*)

  使用给定的算法和密钥长度创建私钥，并以 PEM 格式的二进制字符串形式返回密钥。密钥采用 PKCS #8 格式。如果密钥生成失败，则结果为 NULL。

  对于 MySQL 8.0.29 之前使用的旧版本此函数，请参阅 Section 8.6.6, “MySQL Enterprise Encryption Legacy Function Descriptions”。

  algorithm 是用于创建密钥的加密算法。支持的算法值为 'RSA'。

  key_length 是密钥长度（以位为单位）。如果超过最大允许的密钥长度或指定少于最小值，则密钥生成失败，结果为 null 输出。密钥长度的最小允许值为 2048 位。最大允许的密钥长度是 enterprise_encryption.maximum_rsa_key_size 系统变量的值，默认为 4096。最大设置为 16384，这是 RSA 算法允许的最大密钥长度。请参阅 Section 8.6.2, “Configuring MySQL Enterprise Encryption”。

  注意

  生成更长的密钥可能会消耗大量 CPU 资源。使用 enterprise_encryption.maximum_rsa_key_size 系统变量限制密钥长度，可以在提供足够安全性的同时平衡资源使用。

  此示例创建一个 2048 位的 RSA 私钥，然后从私钥派生公钥：

  SET @priv = create_asymmetric_priv_key('RSA', 2048);
  SET @pub = create_asymmetric_pub_key('RSA', @priv);
  

• create_asymmetric_pub_key(*algorithm*, *priv_key_str*)

  使用给定算法从给定私钥派生公钥，并以 PEM 格式的二进制字符串返回该密钥。密钥采用 PKCS #8 格式。如果密钥派生失败，则结果为 NULL。

  对于 MySQL 8.0.29 之前使用的旧版函数，请参阅 Section 8.6.6, “MySQL Enterprise Encryption Legacy Function Descriptions”。

  algorithm 是用于创建密钥的加密算法。支持的算法值为 'RSA'。

  priv_key_str 是有效的 PEM 编码的 RSA 私钥。

  有关用法示例，请参阅 create_asymmetric_priv_key() 的描述。

• create_digest(*digest_type*, *str*)

  使用给定摘要类型从给定字符串创建摘要，并将摘要作为二进制字符串返回。如果摘要生成失败，则结果为 NULL。

  对于 MySQL 8.0.29 之前使用的旧版函数，请参阅 Section 8.6.6, “MySQL Enterprise Encryption Legacy Function Descriptions”。

  生成的摘要字符串适用于与 asymmetric_sign() 和 asymmetric_verify() 一起使用。这些函数的组件版本接受摘要但不需要它们，因为它们能够处理任意长度的数据。

  digest_type 是用于生成摘要字符串的摘要算法。当使用 OpenSSL 1.0.1 时，支持的 digest_type 值为 'SHA224'、'SHA256'、'SHA384' 和 'SHA512'。如果使用 OpenSSL 1.1.1，则还可使用额外的 digest_type 值 'SHA3-224'、'SHA3-256'、'SHA3-384' 和 'SHA3-512'。

  str 是要生成摘要的非空数据字符串。

  SET @dig = create_digest('SHA512', 'The quick brown fox');
  

8.6.6 MySQL 企业加密传统函数描述

原文：dev.mysql.com/doc/refman/8.0/en/enterprise-encryption-functions-legacy.html

在 MySQL 8.0.30 之前的版本中，MySQL 企业加密的函数基于openssl_udf共享库。本参考描述了这些函数。如果已安装这些函数，则它们在后续版本中仍然可用，但已被弃用。

有关升级到由 MySQL 组件component_enterprise_encryption提供的新组件函数的信息，以及传统函数与组件函数之间的行为差异列表，请参阅升级 MySQL 企业加密。

组件函数的参考资料是第 8.6.5 节，“MySQL 企业加密组件函数描述”。

MySQL 企业加密函数具有以下一般特征：

• 对于错误类型或不正确数量的参数，每个函数都会返回错误。

• 如果参数不适合允许函数执行请求的操作，则它会返回适当的NULL或 0。例如，如果函数不支持指定的算法、密钥长度过短或过长，或者期望为 PEM 格式密钥字符串的字符串不是有效密钥。

• 底层 SSL 库负责随机性初始化。

几个传统函数接受加密算法参数。以下表总结了每个函数支持的算法。

表 8.49 函数支持的算法

函数	支持的算法
asymmetric_decrypt()	RSA
asymmetric_derive()	DH
asymmetric_encrypt()	RSA
asymmetric_sign()	RSA, DSA
asymmetric_verify()	RSA, DSA
create_asymmetric_priv_key()	RSA, DSA, DH
create_asymmetric_pub_key()	RSA, DSA, DH
create_dh_parameters()	DH

注意

尽管您可以使用 RSA、DSA 或 DH 加密算法之一创建密钥，但其他接受密钥参数的传统函数可能仅接受特定类型的密钥。例如，asymmetric_encrypt()和asymmetric_decrypt()仅接受 RSA 密钥。

更多示例和讨论，请参阅第 8.6.3 节，“MySQL 企业加密用法和示例”。

• asymmetric_decrypt(*algorithm*, *crypt_str*, *key_str*)

  使用给定算法和密钥字符串解密加密字符串，并将结果明文作为二进制字符串返回。如果解密失败，则结果为NULL。

  openssl_udf 共享库函数无法解密由 MySQL 8.0.30 中提供的 component_enterprise_encryption 函数生成的内容。

  algorithm 是用于创建密钥的加密算法。支持的算法值为'RSA'。

  crypt_str 是要解密的加密字符串，该字符串是使用asymmetric_encrypt()加密的。

  key_str 是有效的 PEM 编码 RSA 公钥或私钥。为了成功解密，密钥字符串必须对应于与asymmetric_encrypt()一起使用的公钥或私钥字符串，以生成加密字符串。

  有关用法示例，请参阅asymmetric_encrypt()的描述。

• asymmetric_derive(*pub_key_str*, *priv_key_str*)

  使用一方的私钥和另一方的公钥派生对称密钥，并将生成的密钥作为二进制字符串返回。如果密钥派生失败，则结果为NULL。

  pub_key_str 和 priv_key_str 是使用 DH 算法创建的有效 PEM 编码密钥字符串。

  假设您有两对公钥和私钥：

  SET @dhp = create_dh_parameters(1024);
  SET @priv1 = create_asymmetric_priv_key('DH', @dhp);
  SET @pub1 = create_asymmetric_pub_key('DH', @priv1);
  SET @priv2 = create_asymmetric_priv_key('DH', @dhp);
  SET @pub2 = create_asymmetric_pub_key('DH', @priv2);
  

  假设进一步假设您使用一对密钥的私钥和另一对密钥的公钥来创建对称密钥字符串。那么这个对称密钥的身份关系成立：

  asymmetric_derive(@pub1, @priv2) = asymmetric_derive(@pub2, @priv1)
  

  此示例需要 DH 私钥/公钥作为输入，使用共享的对称密钥创建。通过将密钥长度传递给create_dh_parameters()来创建密钥，然后将密钥作为“密钥长度”传递给create_asymmetric_priv_key()。

  -- Generate DH shared symmetric secret
  SET @dhp = create_dh_parameters(1024);
  -- Generate DH key pairs
  SET @algo = 'DH';
  SET @priv1 = create_asymmetric_priv_key(@algo, @dhp);
  SET @pub1 = create_asymmetric_pub_key(@algo, @priv1);
  SET @priv2 = create_asymmetric_priv_key(@algo, @dhp);
  SET @pub2 = create_asymmetric_pub_key(@algo, @priv2);
  
  -- Generate symmetric key using public key of first party,
  -- private key of second party
  SET @sym1 = asymmetric_derive(@pub1, @priv2);
  
  -- Or use public key of second party, private key of first party
  SET @sym2 = asymmetric_derive(@pub2, @priv1);
  

• asymmetric_encrypt(*algorithm*, *str*, *key_str*)

  使用给定的算法和密钥字符串加密字符串，并将生成的密文作为二进制字符串返回。如果加密失败，则结果为NULL。

  algorithm 是用于创建密钥的加密算法。支持的算法值为'RSA'。

  str 是要加密的字符串。此字符串的长度不能大于密钥字符串长度（以字节为单位），减去 11（用于填充）。

  key_str 是有效的 PEM 编码 RSA 公钥或私钥。

  要恢复原始未加密的字符串，将加密字符串与用于加密的密钥对的另一部分一起传递给asymmetric_decrypt()，如下例所示：

  -- Generate private/public key pair
  SET @priv = create_asymmetric_priv_key('RSA', 1024);
  SET @pub = create_asymmetric_pub_key('RSA', @priv);
  
  -- Encrypt using private key, decrypt using public key
  SET @ciphertext = asymmetric_encrypt('RSA', 'The quick brown fox', @priv);
  SET @plaintext = asymmetric_decrypt('RSA', @ciphertext, @pub);
  
  -- Encrypt using public key, decrypt using private key
  SET @ciphertext = asymmetric_encrypt('RSA', 'The quick brown fox', @pub);
  SET @plaintext = asymmetric_decrypt('RSA', @ciphertext, @priv);
  

  假设：

  SET @s = a string to be encrypted
  SET @priv = a valid private RSA key string in PEM format
  SET @pub = the corresponding public RSA key string in PEM format
  

  那么这些身份关系成立：

  asymmetric_decrypt('RSA', asymmetric_encrypt('RSA', @s, @priv), @pub) = @s
  asymmetric_decrypt('RSA', asymmetric_encrypt('RSA', @s, @pub), @priv) = @s
  

• asymmetric_sign(*algorithm*, *digest_str*, *priv_key_str*, *digest_type*)

  使用私钥字符串对摘要字符串进行签名，并将签名作为二进制字符串返回。如果签名失败，则结果为NULL。

  algorithm 是用于创建密钥的加密算法。支持的算法值为'RSA'和'DSA'。

  digest_str 是摘要字符串。摘要字符串可以通过调用create_digest()生成。

  priv_key_str 是用于签署摘要字符串的私钥字符串。它可以是有效的 PEM 编码的 RSA 私钥或 DSA 私钥。

  digest_type 是用于签署数据的算法。支持的digest_type值为'SHA224'、'SHA256'、'SHA384'和'SHA512'。

  有关用法示例，请参阅asymmetric_verify()的描述。

• asymmetric_verify(*algorithm*, *digest_str*, *sig_str*, *pub_key_str*, *digest_type*)

  验证签名字符串是否与摘要字符串匹配，并返回 1 或 0 以指示验证成功或失败。如果验证失败，则结果为NULL。

  openssl_udf共享库函数无法验证由 MySQL 8.0.30 提供的component_enterprise_encryption函数生成的内容。

  algorithm 是用于创建密钥的加密算法。支持的算法值为'RSA'和'DSA'。

  digest_str 是摘要字符串。需要一个摘要字符串，可以通过调用create_digest()生成。

  sig_str 是要验证的签名字符串。可以通过调用asymmetric_sign()生成签名字符串。

  pub_key_str 是签名者的公钥字符串。它对应于传递给asymmetric_sign()以生成签名字符串的私钥。它必须是有效的 PEM 编码的 RSA 公钥或 DSA 公钥。

  digest_type 是用于签署数据的算法。支持的digest_type值为'SHA224'、'SHA256'、'SHA384'和'SHA512'。

  -- Set the encryption algorithm and digest type
  SET @algo = 'RSA';
  SET @dig_type = 'SHA224';
  
  -- Create private/public key pair
  SET @priv = create_asymmetric_priv_key(@algo, 1024);
  SET @pub = create_asymmetric_pub_key(@algo, @priv);
  
  -- Generate digest from string
  SET @dig = create_digest(@dig_type, 'The quick brown fox');
  
  -- Generate signature for digest and verify signature against digest
  SET @sig = asymmetric_sign(@algo, @dig, @priv, @dig_type);
  SET @verf = asymmetric_verify(@algo, @dig, @sig, @pub, @dig_type);
  

• create_asymmetric_priv_key(*algorithm*, {*key_len*|*dh_secret*})

  使用给定的算法和密钥长度或 DH 密钥创建私钥，并以 PEM 格式的二进制字符串形式返回密钥。密钥采用 PKCS #1 格式。如果密钥生成失败，则结果为NULL。

  algorithm 是用于创建密钥的加密算法。支持的算法值为'RSA'、'DSA'和'DH'。

  key_len 是 RSA 和 DSA 密钥的位数。如果超过最大允许的密钥长度或指定小于最小值，密钥生成将失败，结果为 null 输出。RSA 算法的最小允许密钥长度为 1,024 位，RSA 算法的最大允许密钥长度为 16,384 位，DSA 算法的最大允许密钥长度为 10,000 位。这些密钥长度限制是 OpenSSL 强加的约束。服务器管理员可以通过设置 MYSQL_OPENSSL_UDF_RSA_BITS_THRESHOLD、MYSQL_OPENSSL_UDF_DSA_BITS_THRESHOLD 和 MYSQL_OPENSSL_UDF_DH_BITS_THRESHOLD 环境变量来对最大密钥长度施加额外限制。参见 Section 8.6.2, “配置 MySQL 企业加密”。

  注意

  生成更长的密钥可能会消耗大量 CPU 资源。通过使用环境变量限制密钥长度，您可以在满足安全需求的同时平衡资源使用。

  dh_secret 是一个共享的 DH 密钥，必须传递给 DH 密钥而不是密钥长度。要创建这个密钥，请将密钥长度传递给 create_dh_parameters()。

  此示例创建了一个 2,048 位的 DSA 私钥，然后从私钥派生出一个公钥：

  SET @priv = create_asymmetric_priv_key('DSA', 2048);
  SET @pub = create_asymmetric_pub_key('DSA', @priv);
  

  有关显示 DH 密钥生成的示例，请参阅 asymmetric_derive() 的描述。

• create_asymmetric_pub_key(*algorithm*, *priv_key_str*)

  使用给定算法从给定私钥派生公钥，并以 PEM 格式的二进制字符串返回密钥。密钥采用 PKCS #1 格式。如果密钥派生失败，则结果为 NULL。

  algorithm 是用于创建密钥的加密算法。支持的算法值为 'RSA'、'DSA' 和 'DH'。

  priv_key_str 是一个有效的 PEM 编码的 RSA、DSA 或 DH 私钥。

  有关用法示例，请参阅 create_asymmetric_priv_key() 的描述。

• create_dh_parameters(*key_len*)

  创建一个用于生成 DH 私钥/公钥对的共享密钥，并返回一个二进制字符串，可以传递给 create_asymmetric_priv_key()。如果密钥生成失败，则结果为 NULL。

  key_len 是密钥长度。位数最小和最大的密钥长度分别为 1,024 和 10,000。这些密钥长度限制是 OpenSSL 强加的约束。服务器管理员可以通过设置 MYSQL_OPENSSL_UDF_RSA_BITS_THRESHOLD、MYSQL_OPENSSL_UDF_DSA_BITS_THRESHOLD 和 MYSQL_OPENSSL_UDF_DH_BITS_THRESHOLD 环境变量来对最大密钥长度施加额外限制。参见 Section 8.6.2, “配置 MySQL 企业加密”。

  有关如何使用返回值生成对称密钥的示例，请参阅asymmetric_derive()的描述。

  SET @dhp = create_dh_parameters(1024);
  

• create_digest(*digest_type*, *str*)

  使用给定的字符串和摘要类型创建摘要，并将摘要作为二进制字符串返回。如果摘要生成失败，则结果为NULL。

  生成的摘要字符串适用于与asymmetric_sign()和asymmetric_verify()一起使用。这些函数需要一个摘要。

  digest_type是用于生成摘要字符串的摘要算法。支持的digest_type值为'SHA224'、'SHA256'、'SHA384'和'SHA512'。

  str是要生成摘要的非空数据字符串。

  SET @dig = create_digest('SHA512', 'The quick brown fox');
  

8.7 SELinux

原文：dev.mysql.com/doc/refman/8.0/en/selinux.html

8.7.1 检查 SELinux 是否已启用

8.7.2 更改 SELinux 模式

8.7.3 MySQL 服务器 SELinux 策略

8.7.4 SELinux 文件上下文

8.7.5 SELinux TCP 端口上下文

8.7.6 SELinux 故障排除

安全增强型 Linux（SELinux）是一个强制访问控制（MAC）系统，通过为每个系统对象应用一个称为SELinux 上下文的安全标签来实现访问权限。SELinux 策略模块使用 SELinux 上下文来定义进程、文件、端口和其他系统对象之间如何相互交互的规则。只有在策略规则允许的情况下，系统对象之间的交互才被允许。

SELinux 上下文（应用于系统对象的标签）具有以下字段：user、role、type 和 security level。最常用于定义进程如何与其他系统对象交互的规则的是类型信息，而不是整个 SELinux 上下文。例如，MySQL SELinux 策略模块使用 type 信息定义策略规则。

您可以使用操作系统命令（如 ls 和 ps）以 -Z 选项查看 SELinux 上下文。假设 SELinux 已启用并且 MySQL 服务器正在运行，则以下命令显示 mysqld 进程和 MySQL 数据目录的 SELinux 上下文：

mysqld 进程：

$> ps -eZ | grep mysqld
system_u:system_r:mysqld_t:s0    5924 ?        00:00:03 mysqld

MySQL 数据目录：

$> cd /var/lib
$> ls -Z | grep mysql
system_u:object_r:mysqld_db_t:s0 mysql

其中：

• system_u 是用于系统进程和对象的 SELinux 用户标识。

• system_r 是用于系统进程的 SELinux 角色。

• objects_r 是用于系统对象的 SELinux 角色。

• mysqld_t 是与 mysqld 进程关联的类型。

• mysqld_db_t 是与 MySQL 数据目录及其文件关联的类型。

• s0 是安全级别。

有关解释 SELinux 上下文的更多信息，请参考您发行版的 SELinux 文档。

8.7.1 检查 SELinux 是否已启用

原文：dev.mysql.com/doc/refman/8.0/en/selinux-checking.html

在一些 Linux 发行版上，默认启用 SELinux，包括 Oracle Linux、RHEL、CentOS 和 Fedora。使用 sestatus 命令来确定您的发行版是否已启用 SELinux：

$> sestatus
SELinux status:                 enabled
SELinuxfs mount:                /sys/fs/selinux
SELinux root directory:         /etc/selinux
Loaded policy name:             targeted
Current mode:                   enforcing
Mode from config file:          enforcing
Policy MLS status:              enabled
Policy deny_unknown status:     allowed
Memory protection checking:     actual (secure)
Max kernel policy version:      31

如果 SELinux 已禁用或找不到 sestatus 命令，请在启用 SELinux 之前参考您的发行版 SELinux 文档以获取指导。

8.7.2 更改 SELinux 模式

原文：dev.mysql.com/doc/refman/8.0/en/selinux-mode.html

SELinux 支持强制执行、宽容和禁用模式。强制执行模式是默认模式。宽容模式允许在强制执行模式下不允许的操作，并将这些操作记录到 SELinux 审计日志中。宽容模式通常用于制定策略或故障排除。在禁用模式下，策略不会被执行，系统对象不会应用上下文，这使得以后启用 SELinux 变得困难。

要查看当前的 SELinux 模式，请使用之前提到的 sestatus 命令或 getenforce 实用程序。

$> getenforce
Enforcing

要更改 SELinux 模式，请使用 setenforce 实用程序：

$> setenforce 0
$> getenforce
Permissive

$> setenforce 1
$> getenforce
Enforcing

使用 setenforce 进行的更改在重新启动系统时会丢失。要永久更改 SELinux 模式，请编辑 /etc/selinux/config 文件并重新启动系统。

8.7.3 MySQL 服务器 SELinux 策略

原文：dev.mysql.com/doc/refman/8.0/en/selinux-policies.html

MySQL 服务器 SELinux 策略模块通常默认安装。您可以使用semodule -l命令查看已安装的模块。MySQL 服务器 SELinux 策略模块包括：

• mysqld_selinux

• mysqld_safe_selinux

有关 MySQL 服务器 SELinux 策略模块的信息，请参考 SELinux 手册页面。手册页面提供有关与 MySQL 服务相关的类型和布尔值的信息。手册页面的命名格式为*service-name*_selinux。

man mysqld_selinux

如果没有 SELinux 手册页面，请参考您的发行版 SELinux 文档，了解如何使用sepolicy manpage工具生成手册页面的信息。

8.7.4 SELinux 文件上下文

原文：dev.mysql.com/doc/refman/8.0/en/selinux-file-context.html

MySQL 服务器从许多文件中读取和写入。如果这些文件的 SELinux 上下文未正确设置，可能会拒绝对文件的访问。

接下来的说明使用 semanage 二进制文件来管理文件上下文；在 RHEL 上，它是 policycoreutils-python-utils 包的一部分：

yum install -y policycoreutils-python-utils

安装 semanage 二进制文件后，您可以使用 semanage 和 fcontext 选项列出 MySQL 文件上下文。

semanage fcontext -l | grep -i mysql

设置 MySQL 数据目录上下文

默认数据目录位置是 /var/lib/mysql/；使用的 SELinux 上下文是 mysqld_db_t。

如果您编辑配置文件以使用不同位置作为数据目录，或者用于数据目录中通常的任何文件（例如二进制日志），则可能需要为新位置设置上下文。例如：

semanage fcontext -a -t mysqld_db_t "/path/to/my/custom/datadir(/.*)?"
restorecon -Rv /path/to/my/custom/datadir

semanage fcontext -a -t mysqld_db_t "/path/to/my/custom/logdir(/.*)?"
restorecon -Rv /path/to/my/custom/logdir

设置 MySQL 错误日志文件上下文

RedHat RPM 的默认位置是 /var/log/mysqld.log；使用的 SELinux 上下文类型是 mysqld_log_t。

如果您编辑配置文件以使用不同位置，则可能需要为新位置设置上下文。例如：

semanage fcontext -a -t mysqld_log_t "/path/to/my/custom/error.log"
restorecon -Rv /path/to/my/custom/error.log

设置 PID 文件上下文

PID 文件的默认位置是 /var/run/mysqld/mysqld.pid；使用的 SELinux 上下文类型是 mysqld_var_run_t。

如果您编辑配置文件以使用不同位置，则可能需要为新位置设置上下文。例如：

semanage fcontext -a -t mysqld_var_run_t "/path/to/my/custom/pidfile/directory/.*?"
restorecon -Rv /path/to/my/custom/pidfile/directory

设置 Unix 域套接字上下文

Unix 域套接字的默认位置是 /var/lib/mysql/mysql.sock；使用的 SELinux 上下文类型是 mysqld_var_run_t。

如果您编辑配置文件以使用不同位置，则可能需要为新位置设置上下文。例如：

semanage fcontext -a -t mysqld_var_run_t "/path/to/my/custom/mysql\.sock"
restorecon -Rv /path/to/my/custom/mysql.sock

设置 secure_file_priv 目录上下文

对于自 MySQL 5.6.34、5.7.16 和 8.0.11 版本以来的 MySQL 版本。

安装 MySQL Server RPM 会创建一个 /var/lib/mysql-files/ 目录，但不会为其设置 SELinux 上下文。/var/lib/mysql-files/ 目录旨在用于诸如 SELECT ... INTO OUTFILE 等操作。

如果您通过设置 secure_file_priv 启用了对此目录的使用，则可能需要这样设置上下文：

semanage fcontext -a -t mysqld_db_t "/var/lib/mysql-files/(/.*)?"
restorecon -Rv /var/lib/mysql-files

如果您使用了不同位置，请编辑此路径。出于安全目的，此目录永远不应在数据目录内。

有关此变量的更多信息，请参阅secure_file_priv 文档。

8.7.5 SELinux TCP 端口上下文

原文：dev.mysql.com/doc/refman/8.0/en/selinux-context-tcp-port.html

8.7.5.1 设置 mysqld 的 TCP 端口上下文

8.7.5.2 设置 MySQL 功能端口的 TCP 端口上下文

接下来的说明将使用semanage二进制文件来管理端口上下文；在 RHEL 上，它是policycoreutils-python-utils软件包的一部分。

yum install -y policycoreutils-python-utils

安装完semanage二进制文件后，您可以使用semanage的port选项列出使用mysqld_port_t上下文定义的端口。

$> semanage port -l | grep mysqld
mysqld_port_t                  tcp      1186, 3306, 63132-63164

原文：dev.mysql.com/doc/refman/8.0/en/selinux-context-mysqld-tcp-port.html

8.7.5.1 为 mysqld 设置 TCP 端口上下文

默认的 TCP 端口mysqld是3306；而使用的 SELinux 上下文类型是mysqld_port_t。

如果您配置mysqld使用不同的 TCP port，您可能需要为新端口设置上下文。例如，为非默认端口（如端口 3307）定义 SELinux 上下文：

semanage port -a -t mysqld_port_t -p tcp 3307

确认端口已添加：

$> semanage port -l | grep mysqld
mysqld_port_t                  tcp      3307, 1186, 3306, 63132-63164

原文：dev.mysql.com/doc/refman/8.0/en/selinux-context-mysql-feature-ports.html

8.7.5.2 设置 MySQL 功能的 TCP 端口上下文

如果启用了某些 MySQL 功能，您可能需要为这些功能使用的额外端口设置 SELinux TCP 端口上下文。如果 MySQL 功能使用的端口没有正确的 SELinux 上下文，这些功能可能无法正常运行。

以下各节描述了如何为 MySQL 功能设置端口上下文。通常，可以使用相同的方法为任何 MySQL 功能设置端口上下文。有关 MySQL 功能使用的端口信息，请参考 MySQL 端口参考。

从 MySQL 8.0.14 到 MySQL 8.0.17，必须将 mysql_connect_any SELinux 布尔值设置为 ON。从 MySQL 8.0.18 开始，不再需要或建议启用 mysql_connect_any。

setsebool -P mysql_connect_any=ON

设置 Group Replication 的 TCP 端口上下文

如果启用了 SELinux，您必须为 Group Replication 使用的通信端口设置端口上下文，该端口由 group_replication_local_address 变量定义。mysqld 必须能够绑定到 Group Replication 通信端口并在那里监听。InnoDB Cluster 依赖于 Group Replication，因此这同样适用于集群中使用的实例。要查看当前由 MySQL 使用的端口，请执行：

semanage port -l | grep mysqld

假设 Group Replication 通信端口为 33061，请通过以下方式设置端口上下文：

semanage port -a -t mysqld_port_t -p tcp 33061

设置 Document Store 的 TCP 端口上下文

如果启用了 SELinux，您必须为 X Plugin 使用的通信端口设置端口上下文，该端口由 mysqlx_port 变量定义。mysqld 必须能够绑定到 X Plugin 通信端口并在那里监听。

假设 X Plugin 通信端口为 33060，请通过以下方式设置端口上下文：

semanage port -a -t mysqld_port_t -p tcp 33060

设置 MySQL Router 的 TCP 端口上下文

如果启用了 SELinux，您必须为 MySQL Router 使用的通信端口设置端口上下文。假设 MySQL Router 使用的额外通信端口是默认的 6446、6447、64460 和 64470，在每个实例上通过以下方式设置端口上下文：

semanage port -a -t mysqld_port_t -p tcp 6446
semanage port -a -t mysqld_port_t -p tcp 6447
semanage port -a -t mysqld_port_t -p tcp 64460
semanage port -a -t mysqld_port_t -p tcp 64470

8.7.6 故障排除 SELinux

原文：dev.mysql.com/doc/refman/8.0/en/selinux-troubleshooting.html

诊断 SELinux 通常涉及将 SELinux 置于宽容模式，重新运行有问题的操作，在 SELinux 审计日志中检查访问拒绝消息，并在问题解决后将 SELinux 放回强制模式。

为了避免使用 setenforce 将整个系统置于宽容模式，您可以通过使用 semanage 命令将其 SELinux 域（mysqld_t）置于宽容模式，只允许 MySQL 服务以宽容模式运行：

semanage permissive -a mysqld_t

在完成故障排除后，请使用以下命令将 mysqld_t 域放回强制模式：

semanage permissive -d mysqld_t

SELinux 将拒绝操作的日志写入 /var/log/audit/audit.log。您可以通过搜索“denied”消息来检查拒绝。

grep "denied" /var/log/audit/audit.log

以下部分描述了可能遇到与 SELinux 相关问题的几个常见领域。

文件上下文

如果 MySQL 目录或文件具有不正确的 SELinux 上下文，则可能会拒绝访问。如果 MySQL 配置为从非默认目录或文件读取或写入，则可能会出现此问题。例如，如果您配置 MySQL 使用非默认数据目录，则该目录可能没有预期的 SELinux 上下文。

尝试在具有无效 SELinux 上下文的非默认数据目录上启动 MySQL 服务会导致以下启动失败。

$> systemctl start mysql.service
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.

在这种情况下，将“拒绝”消息记录到 /var/log/audit/audit.log 中：

$> grep "denied" /var/log/audit/audit.log
type=AVC msg=audit(1587133719.786:194): avc:  denied  { write } for  pid=7133 comm="mysqld"
name="mysql" dev="dm-0" ino=51347078 scontext=system_u:system_r:mysqld_t:s0
tcontext=unconfined_u:object_r:default_t:s0 tclass=dir permissive=0

有关为 MySQL 目录和文件设置正确的 SELinux 上下文的信息，请参见 第 8.7.4 节，“SELinux 文件上下文”。

端口访问

SELinux 期望诸如 MySQL 服务器之类的服务使用特定端口。更改端口而不更新 SELinux 策略可能会导致服务失败。

mysqld_port_t 端口类型定义了 MySQL 监听的端口。如果将 MySQL 服务器配置为使用非默认端口，例如端口 3307，并且不更新策略以反映更改，则 MySQL 服务无法启动：

$> systemctl start mysqld.service
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.

在这种情况下，拒绝消息被记录到 /var/log/audit/audit.log 中：

$> grep "denied" /var/log/audit/audit.log
type=AVC msg=audit(1587134375.845:198): avc:  denied  { name_bind } for  pid=7340
comm="mysqld" src=3307 scontext=system_u:system_r:mysqld_t:s0
tcontext=system_u:object_r:unreserved_port_t:s0 tclass=tcp_socket permissive=0

有关为 MySQL 设置正确的 SELinux 端口上下文的信息，请参见 第 8.7.5 节，“SELinux TCP 端口上下文”。启用使用未定义所需上下文的端口的 MySQL 功能时，可能会出现类似的端口访问问题。有关更多信息，请参见 第 8.7.5.2 节，“为 MySQL 功能设置 TCP 端口上下文”。

应用程序更改

SELinux 可能不知道应用程序的更改。例如，新版本、应用程序扩展或新功能可能以 SELinux 不允许的方式访问系统资源，导致访问被拒绝。在这种情况下，您可以使用audit2allow实用程序创建自定义策略以允许必要的访问。创建自定义策略的典型方法是将 SELinux 模式更改为宽容模式，识别 SELinux 审计日志中的访问拒绝消息，并使用audit2allow实用程序创建自定义策略以允许访问。

有关使用audit2allow实用程序的信息，请参考您发行版的 SELinux 文档。

如果您遇到 MySQL 的访问问题，认为应该由标准 MySQL SELinux 策略模块处理，请在您发行版的错误跟踪系统中提交错误报告。

8.8 FIPS 支持

原文：dev.mysql.com/doc/refman/8.0/en/fips-mode.html

MySQL 支持 FIPS 模式，如果使用 OpenSSL 3.0 或 OpenSSL 1.0.2 进行编译，并且在运行时可用 OpenSSL 库和 FIPS 对象模块。

服务器端的 FIPS 模式适用于服务器执行的加密操作。这包括复制（源/副本和组复制）和运行在服务器内部的 X 插件。FIPS 模式还适用于客户端尝试连接到服务器。

以下各节描述了 FIPS 模式以及如何在 MySQL 中利用它：

• FIPS 概述

• MySQL 中 FIPS 模式的系统要求

• 在 MySQL 中配置 FIPS 模式

FIPS 概述

联邦信息处理标准 140-2（FIPS 140-2）描述了一种安全标准，联邦（美国政府）机构可能要求用于保护敏感或有价值信息的加密模块。要被视为适用于此类联邦用途，加密模块必须获得 FIPS 140-2 认证。如果旨在保护敏感数据的系统缺乏适当的 FIPS 140-2 证书，联邦机构将无法购买该系统。

诸如 OpenSSL 之类的产品可以在 FIPS 模式下使用，尽管 OpenSSL 库本身未经 FIPS 验证。相反，OpenSSL 库与 OpenSSL FIPS 对象模块一起使用，以使基于 OpenSSL 的应用程序能够在 FIPS 模式下运行。

对于 FIPS 及其在 OpenSSL 中的实现的一般信息，这些参考资料可能会有所帮助：

• 国家标准与技术研究所 FIPS PUB 140-2

• OpenSSL FIPS 140-2 安全策略

• fips_module 手册页面

重要

FIPS 模式对加密操作施加条件，例如对可接受的加密算法的限制或对更长密钥长度的要求。对于 OpenSSL，确切的 FIPS 行为取决于 OpenSSL 版本。

MySQL 中 FIPS 模式的系统要求

要使 MySQL 支持 FIPS 模式，必须满足以下系统要求：

• 在构建时，MySQL 必须使用 OpenSSL 进行编译。如果编译使用与 OpenSSL 不同的 SSL 库，则无法在 MySQL 中使用 FIPS 模式。

  此外，MySQL 必须使用经过 FIPS 认证的 OpenSSL 版本进行编译。OpenSSL 1.0.2 和 OpenSSL 3.0 已经获得认证，但 OpenSSL 1.1.1 没有。最近版本的 MySQL 的二进制发行版在某些平台上使用 OpenSSL 3.0.9 编译，这意味着它们未经 FIPS 认证。这会导致根据系统和 MySQL 配置的不同而产生可用 MySQL 功能的权衡：

  • 使用具有 OpenSSL 1.0.2 和所需的 FIPS 对象模块的系统。在这种情况下，如果您使用使用 OpenSSL 1.0.2 编译的二进制发行版，或者使用 OpenSSL 1.0.2 从源代码编译 MySQL，则可以为 MySQL 启用 FIPS 模式。但是，在这种情况下，您不能使用需要 OpenSSL 1.1.1 的 TLSv1.3 协议或密码套件。此外，您正在使用于 2019 年底达到生命周期终点的 OpenSSL 版本。

  • 使用具有 OpenSSL 1.1.1 或更高版本的系统。在这种情况下，您可以使用二进制包安装 MySQL，并且可以使用 TLSv1.3 协议和密码套件，以及其他已支持的 TLS 协议。但是，您不能为 MySQL 启用 FIPS 模式。

  • 使用具有 OpenSSL 3.0 和所需的 FIPS 对象模块的系统。在这种情况下，如果您使用使用 OpenSSL 3.0 编译的二进制发行版，或者使用 OpenSSL 3.0 从源代码编译 MySQL，则可以为 MySQL 启用 FIPS 模式。可以直接通过 OpenSSL 3.0 配置文件处理 FIPS 模式，而不是使用服务器端系统变量和客户端选项（自 MySQL 8.0.34 起已弃用）。当使用 OpenSSL 3.0 编译 MySQL，并且在运行时可用 OpenSSL 库和 FIPS 对象模块时，服务器会读取 OpenSSL 配置文件，并尊重设置使用 FIPS 提供程序的首选项。

    有关升级到 OpenSSL 3.0 的一般信息，请参阅 OpenSSL 3.0 迁移指南。

• 在运行时，必须将 OpenSSL 库和 OpenSSL FIPS 对象模块作为共享（动态链接）对象可用。可以构建静态链接的 OpenSSL 对象，但 MySQL 无法使用它们。

FIPS 模式已在 EL7 上对 MySQL 进行了测试，但可能也适用于其他系统。

如果您的平台或操作系统提供 OpenSSL FIPS 对象模块，则可以使用它。否则，您可以从源代码构建 OpenSSL 库和 FIPS 对象模块。请参阅 fips_module 手册中的说明（参见 FIPS 概述）。

在 MySQL 中配置 FIPS 模式

注意

从 MySQL 8.0.34 开始，本节中描述的服务器端和客户端选项已弃用。

MySQL 在服务器端和客户端启用 FIPS 模式的控制：

• ssl_fips_mode 系统变量控制服务器是否在 FIPS 模式下运行。

• --ssl-fips-mode客户端选项控制特定 MySQL 客户端是否在 FIPS 模式下运行。

ssl_fips_mode系统变量和--ssl-fips-mode客户端选项允许这些值：

• OFF：禁用 FIPS 模式。

• ON：启用 FIPS 模式。

• STRICT：启用“严格” FIPS 模式。

在服务器端，数值型ssl_fips_mode的值为 0、1 和 2 分别等同于OFF、ON和STRICT。

重要

一般来说，STRICT比ON施加更多限制，但 MySQL 本身除了向 OpenSSL 指定 FIPS 模式值外，没有 FIPS 特定的代码。对于ON或STRICT的 FIPS 模式的确切行为取决于 OpenSSL 版本。有关详细信息，请参考fips_module手册页（参见 FIPS 概述）。

注意

如果 OpenSSL FIPS 对象模块不可用，则ssl_fips_mode和--ssl-fips-mode的唯一允许值为OFF。尝试将 FIPS 模式设置为其他值会导致错误。

服务器端的 FIPS 模式适用于服务器执行的加密操作。这包括复制（源/副本和组复制）和运行在服务器内部的 X 插件。

FIPS 模式也适用于客户端尝试连接到服务器的情况。当启用时，在客户端或服务器端，它限制了可以选择的支持加密密码。然而，启用 FIPS 模式并不要求必须使用加密连接，或者必须加密用户凭据。例如，如果启用了 FIPS 模式，就需要更强的加密算法。特别是，MD5 被限制，因此尝试使用像RC4-MD5这样的加密密码建立加密连接是行不通的。但是，FIPS 模式并不阻止建立非加密连接。 （为此，您可以为特定用户帐户使用CREATE USER或ALTER USER的REQUIRE子句，或设置require_secure_transport系统变量以影响所有帐户。）

第九章 备份和恢复

原文：dev.mysql.com/doc/refman/8.0/en/backup-and-recovery.html

目录

9.1 备份和恢复类型

9.2 数据库备份方法

9.3 备份和恢复策略示例

9.3.1 制定备份策略

9.3.2 使用备份进行恢复

9.3.3 备份策略摘要

9.4 使用 mysqldump 进行备份

9.4.1 使用 mysqldump 以 SQL 格式导出数据

9.4.2 重新加载 SQL 格式备份

9.4.3 使用 mysqldump 以分隔文本格式导出数据

9.4.4 重新加载分隔文本格式备份

9.4.5 mysqldump 提示

9.5 时间点（增量）恢复

9.5.1 使用二进制日志进行时间点恢复

9.5.2 使用事件位置进行时间点恢复

9.6 MyISAM 表维护和崩溃恢复

9.6.1 使用 myisamchk 进行崩溃恢复

9.6.2 如何检查 MyISAM 表中的错误

9.6.3 如何修复 MyISAM 表

9.6.4 MyISAM 表优化

9.6.5 设置 MyISAM 表维护计划

重要的是备份您的数据库，以便在出现问题时可以恢复数据并重新运行，例如系统崩溃、硬件故障或用户错误删除数据。在升级 MySQL 安装之前备份也是必不可少的保障，它们可以用于将 MySQL 安装转移到另一个系统或设置复制服务器。

MySQL 提供了多种备份策略供您选择，以选择最适合您安装要求的方法。本章讨论了几个备份和恢复主题，您应该熟悉：

• 备份类型：逻辑备份与物理备份，完整备份与增量备份等。

• 创建备份的方法。

• 包括时间点恢复在内的恢复方法。

• 备份调度，压缩和加密。

• 表维护，以便恢复损坏的表。

其他资源

与备份或维护数据可用性相关的资源包括以下内容：

• MySQL Enterprise Edition 的客户可以使用 MySQL Enterprise Backup 产品进行备份。有关 MySQL Enterprise Backup 产品的概述，请参阅 第 32.1 节，“MySQL Enterprise Backup 概述”。

• 专门讨论备份问题的论坛位于forums.mysql.com/list.php?28。

• mysqldump的详细信息可以在第六章，MySQL 程序中找到。

• 此处描述的 SQL 语句的语法在第十五章，SQL 语句中给出。

• 有关InnoDB备份程序的更多信息，请参见第 17.18.1 节，“InnoDB 备份”。

• 复制功能使您能够在多个服务器上保持相同的数据。这有几个好处，比如可以将客户端查询负载分布到多个服务器上，即使某个服务器被下线或失败，数据也是可用的，并且可以通过使用副本进行备份而不影响源数据。参见第十九章，复制。

• MySQL InnoDB 集群是一组产品，它们共同提供高可用性解决方案。可以配置一组 MySQL 服务器以使用 MySQL Shell 创建集群。服务器集群具有一个称为主服务器的单一源，充当读写源。多个次要服务器是源的副本。至少需要三个服务器才能创建高可用性集群。客户端应用程序通过 MySQL Router 连接到主服务器。如果主服务器失败，一个次要服务器将自动晋升为主服务器角色，并且 MySQL Router 将请求路由到新的主服务器。

• NDB 集群提供了适用于分布式计算环境的高可用性、高冗余性版本的 MySQL。请参见第二十五章，MySQL NDB 集群 8.0，其中提供了关于 MySQL NDB 集群 8.0 的信息。

9.1 备份和恢复类型

原文：dev.mysql.com/doc/refman/8.0/en/backup-types.html

本节描述了不同类型备份的特点。

物理（原始）与逻辑备份

物理备份由存储数据库内容的目录和文件的原始副本组成。这种备份适用于需要在出现问题时快速恢复的大型重要数据库。

逻辑备份保存的信息表示为逻辑数据库结构（CREATE DATABASE，CREATE TABLE语句）和内容（INSERT语句或分隔文本文件）。这种类型的备份适用于数据量较小的情况，您可能需要编辑数据值或表结构，或者在不同的机器架构上重新创建数据。

物理备份方法具有以下特点：

• 备份由数据库目录和文件的精确副本组成。通常这是 MySQL 数据目录的全部或部分副本。

• 物理备份方法比逻辑备份更快，因为它们只涉及文件复制而不涉及转换。

• 输出比逻辑备份更紧凑。

• 由于备份速度和紧凑性对于繁忙、重要的数据库至关重要，MySQL Enterprise Backup 产品执行物理备份。有关 MySQL Enterprise Backup 产品的概述，请参见第 32.1 节，“MySQL Enterprise Backup 概述”。

• 备份和恢复的粒度范围从整个数据目录的级别到单个文件的级别。这可能或可能不提供表级粒度，这取决于存储引擎。例如，InnoDB表可以分别存储在单独的文件中，或与其他InnoDB表共享文件存储；每个MyISAM表对应于一组文件。

• 除了数据库，备份还可以包括任何相关文件，如日志或配置文件。

• 通过这种方式备份MEMORY表的数据有些棘手，因为它们的内容不存储在磁盘上。（MySQL Enterprise Backup 产品具有一个功能，可以在备份过程中检索MEMORY表的数据。）

• 备份只能在具有相同或类似硬件特征的其他机器之间进行移植。

• 可以在 MySQL 服务器未运行时执行备份。如果服务器正在运行，则需要执行适当的锁定，以确保服务器在备份过程中不更改数据库内容。对于需要的表，MySQL Enterprise Backup 会���动执行此锁定。

• 物理备份工具包括 MySQL Enterprise Backup 的mysqlbackup用于InnoDB或任何其他表，或文件系统级别的命令（如cp，scp，tar，rsync）用于MyISAM表。

• 恢复时：

  • MySQL Enterprise Backup 可以恢复备份的InnoDB和其他表。

  • ndb_restore 用于恢复NDB表。

  • 在文件系统级别复制的文件可以使用文件系统命令复制回其原始位置。

逻辑备份方法具有以下特点：

• 备份是通过查询 MySQL 服务器获取数据库结构和内容信息完成的。

• 备份比物理方法慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。如果输出写在客户端端，服务器还必须将其发送给备份程序。

• 输出比物理备份大，特别是以文本格式保存时。

• 备份和恢复的粒度可在服务器级别（所有数据库）、数据库级别（特定数据库中的所有表）或表级别进行。这对于存储引擎都是适用的。

• 备份不包括日志或配置文件，或其他不属于数据库的数据库相关文件。

• 存储在逻辑格式中的备份是与机器无关且高度可移植的。

• 逻辑备份是在 MySQL 服务器运行时执行的。服务器不会被停机。

• 逻辑备份工具包括mysqldump程序和SELECT ... INTO OUTFILE语句。这些适用于任何存储引擎，甚至MEMORY。

• 要恢复逻辑备份，可以使用mysql客户端处理 SQL 格式的转储文件。要加载分隔文本文件，请使用LOAD DATA语句或mysqlimport客户端。

在线与离线备份

在线备份是在 MySQL 服务器运行时进行的，以便从服务器获取数据库信息。离线备份是在服务器停止时进行的。这种区别也可以描述为“热”与“冷”备份；“温暖”备份是指服务器保持运行但被锁定以防止修改数据，同时您可以在外部访问数据库文件。

在线备份方法具有以下特点：

• 备份对其他客户端的干扰较小，这些客户端可以在备份期间连接到 MySQL 服务器，并根据需要执行的操作访问数据。

• 必须谨慎地施加适当的锁定，以防止发生会损害备份完整性的数据修改。MySQL Enterprise Backup 产品会自动执行此类锁定。

离线备份方法具有以下特点：

• 客户端可能受到不利影响，因为服务器在备份期间不可用。因此，这种备份通常是从可以脱机而不影响可用性的副本中进行的。

• 备份过程更简单，因为不会受到客户端活动的干扰。

在恢复操作中也存在在线和离线的区别，并且具有类似的特征。然而，客户端更有可能受到在线恢复的影响，而不是在线备份，因为恢复需要更强的锁定。在备份期间，客户端可能能够读取数据，而在恢复数据时，客户端必须被阻止访问数据，因为恢复会修改数据而不仅仅是读取数据。

本地与远程备份

本地备份是在运行 MySQL 服务器的同一主机上执行的，而远程备份是从不同主机执行的。对于某些类型的备份，即使输出是在服务器本地写入的，备份也可以从远程主机启动。

• mysqldump可以连接到本地或远程服务器。对于 SQL 输出（CREATE和INSERT语句），可以进行本地或远程转储，并在客户端生成输出。对于分隔文本输出（使用--tab选项），数据文件将在服务器主机上创建。

• SELECT ... INTO OUTFILE可以从本地或远程客户端主机启动，但输出文件将在服务器主机上创建。

• 物理备份方法通常在 MySQL 服务器主机上本地启动，以便服务器可以脱机，尽管复制文件的目的地可能是远程的。

快照备份

一些文件系统实现允许进行“快照”操作。这些提供了文件系统在特定时间点的逻辑副本，而无需对整个文件系统进行物理复制。（例如，实现可能使用写时复制技术，因此只需要复制快照时间后修改的文件系统的部分。）MySQL 本身不提供进行文件系统快照的功能。可以通过第三方解决方案（如 Veritas、LVM 或 ZFS）来实现。

完全与增量备份

完全备份包括在特定时间点上由 MySQL 服务器管理的所有数据。增量备份包括在给定时间段内对数据所做的更改（从一个时间点到另一个时间点）。MySQL 有不同的方法来执行完全备份，比如在本节中早些时候描述的那些。通过启用服务器的二进制日志，可以实现增量备份，服务器使用该日志记录数据更改。

完全备份与时间点（增量）恢复

完全恢复会从完全备份中恢复所有数据。这将使服务器实例恢复到备份时的状态。如果该状态不够及时，可以在完全恢复后恢复自完全备份以来制作的增量备份，将服务器带到更加及时的状态。

增量恢复是在给定时间段内恢复所做更改。这也被称为按时间点恢复，因为它使服务器的状态保持到给定时间。按时间点恢复基于二进制日志，通常在从备份文件进行完全恢复后进行，将服务器恢复到备份时的状态。然后，二进制日志文件中写入的数据更改将作为增量恢复应用，重新执行数据修改，将服务器带到所需的时间点。

表维护

如果表损坏，数据完整性可能会受到损害。对于InnoDB表，这不是一个典型问题。要检查MyISAM表并在发现问题时修复它们的程序，请参阅第 9.6 节，“MyISAM 表维护和崩溃恢复”。

备份调度、压缩和加密

备份调度对于自动化备份程序非常有价值。备份输出的压缩可以减少空间需求，输出的加密可以提供更好的安全性，防止未经授权访问备份数据。MySQL 本身不提供这些功能。MySQL 企业备份产品可以压缩InnoDB备份，使用文件系统工具可以实现备份输出的压缩或加密。也可以使用其他第三方解决方案。

9.2 数据库备份方法

原文：dev.mysql.com/doc/refman/8.0/en/backup-methods.html

这一部分总结了一些制作备份的一般方法。

使用 MySQL 企业备份进行热备份

MySQL 企业版的客户可以使用 MySQL 企业备份产品对整个实例或选定的数据库、表或两者进行物理备份。该产品包括增量备份和压缩备份的功能。备份物理数据库文件比逻辑技术（如mysqldump命令）快得多。InnoDB表使用热备份机制进行复制。（理想情况下，InnoDB表应该占据大部分数据。）其他存储引擎的表使用温备份机制进行复制。有关 MySQL 企业备份产品的概述，请参见第 32.1 节，“MySQL 企业备份概述”。

使用 mysqldump 进行备份

mysqldump程序可以进行备份。它可以备份所有类型的表。（参见第 9.4 节，“使用 mysqldump 进行备份”。）

对于InnoDB表，可以使用--single-transaction选项在不锁定表的情况下执行在线备份，使用mysqldump。参见第 9.3.1 节，“建立备份策略”。

通过复制表文件进行备份

可以通过复制表文件（*.MYD、*.MYI文件和相关的*.sdi文件）备份 MyISAM 表。为了获得一致的备份，停止服务器或锁定并刷新相关表：

FLUSH TABLES *tbl_list* WITH READ LOCK;

您只需要一个读锁；这使得其他客户端可以继续查询表，同时您可以复制数据库目录中的文件。刷新是必要的，以确保在开始备份之前将所有活动索引页写入磁盘。参见第 15.3.6 节，“LOCK TABLES 和 UNLOCK TABLES 语句”和第 15.7.8.3 节，“FLUSH 语句”。

您还可以通过简单地复制表文件来创建二进制备份，只要服务器没有更新任何内容。（但请注意，如果您的数据库包含InnoDB表，则表文件复制方法不起作用。此外，即使服务器没有活动更新数据，InnoDB可能仍然在内存中缓存修改的数据，而未刷新到磁盘。）

有关此备份方法的示例，请参考 Section 15.2.6, “IMPORT TABLE Statement”中的导出和导入示例。

创建分隔文本文件备份

要创建包含表数据的文本文件，可以使用SELECT * INTO OUTFILE '*file_name*' FROM *tbl_name*。该文件在 MySQL 服务器主机上创建，而不是客户端主机。对于此语句，输出文件不能已经存在，因为允许文件被覆盖构成安全风险。请参阅 Section 15.2.13, “SELECT Statement”。此方法适用于任何类型的数据文件，但仅保存表数据，而不保存表结构。

另一种创建文本数据文件（以及包含用于备份表的CREATE TABLE语句的文件）的方法是使用mysqldump与--tab选项。请参阅 Section 9.4.3, “Dumping Data in Delimited-Text Format with mysqldump”。

要重新加载分隔文本数据文件，请使用LOAD DATA或mysqlimport。

通过启用二进制日志进行增量备份

MySQL 支持使用二进制日志进行增量备份。二进制日志文件提供了您需要的信息，以便复制在您执行备份之后对数据库所做的更改。因此，为了允许服务器恢复到某个时间点，必须在其上启用二进制日志记录，这是 MySQL 8.0 的默认设置；请参阅 Section 7.4.4, “The Binary Log”。

当你想要进行增量备份（包含自上次完整或增量备份以来发生的所有更改）时，你应该通过使用FLUSH LOGS来旋转二进制日志。完成这一步后，你需要将从上次完整或增量备份时刻到倒数第二个时刻的所有二进制日志复制到备份位置。这些二进制日志就是增量备份；在恢复时，你需要按照第 9.5 节，“时间点（增量）恢复” Recovery")中的说明应用它们。下次进行完整备份时，你也应该使用FLUSH LOGS或mysqldump --flush-logs来旋转二进制日志。参见第 6.5.4 节，“mysqldump — 数据库备份程序”。

使用副本进行备份

如果在备份时服务器出现性能问题，一个有助于解决问题的策略是设置复制并在副本上执行备份，而不是在源服务器上执行。参见第 19.4.1 节，“使用复制进行备份”。

如果你正在备份一个副本，你应该在备份副本数据库时备份其连接元数据存储库和应用程序元数据存储库（参见第 19.2.4 节，“中继日志和复制元数据存储库”），无论你选择哪种备份方法，这些信息总是需要的，以便在恢复副本数据后恢复复制。如果你的副本正在复制LOAD DATA语句，你还应该备份副本用于此目的的任何SQL_LOAD-*文件。副本需要这些文件来恢复任何中断的LOAD DATA操作的复制。此目录的位置是系统变量replica_load_tmpdir（从 MySQL 8.0.26 开始）或slave_load_tmpdir（MySQL 8.0.26 之前）的值。如果服务器未使用该变量启动，则目录位置是系统变量tmpdir的值。

恢复损坏的表

如果必须恢复已损坏的 MyISAM 表，请尝试首先使用 REPAIR TABLE 或 myisamchk -r 进行恢复。在 99.9% 的情况下，这应该有效。如果 myisamchk 失败，请参阅 第 9.6 节，“MyISAM 表维护和崩溃恢复”。

使用文件系统快照进行备份。

如果你使用的是 Veritas 文件系统，可以这样进行备份：

1. 从客户端程序执行 FLUSH TABLES WITH READ LOCK。

2. 从另一个 shell 中执行 mount vxfs snapshot。

3. 从第一个客户端执行 UNLOCK TABLES。

4. 从快照中复制文件。

5. 卸载快照。

其他文件系统（如 LVM 或 ZFS）可能具有类似的快照功能。

9.3 备份和恢复策略示例

原文：dev.mysql.com/doc/refman/8.0/en/backup-strategy-example.html

9.3.1 建立备份策略

9.3.2 使用备份进行恢复

9.3.3 备份策略摘要

本节讨论了一种备份执行程序，使您能够在几种崩溃后恢复数据：

• 操作系统崩溃

• 电源故障

• 文件系统崩溃

• 硬件问题（硬盘、主板等）

示例命令不包括像--user和--password这样的选项，用于mysqldump和mysql客户端程序。您应根据需要包含这些选项，以使客户端程序能够连接到 MySQL 服务器。

假设数据存储在支持事务和自动崩溃恢复的InnoDB存储引擎中。同时假设 MySQL 服务器在崩溃时处于负载状态。如果不是这样，就永远不需要恢复。

对于操作系统崩溃或电源故障的情况，我们可以假设 MySQL 的磁盘数据在重新启动后是可用的。由于崩溃，InnoDB数据文件可能不包含一致的数据，但InnoDB会读取其日志，并在其中找到未刷新到数据文件的待处理已提交和未提交事务列表。InnoDB会自动回滚那些未提交的事务，并将已提交的事务刷新到其数据文件中。关于此恢复过程的信息通过 MySQL 错误日志传达给用户。以下是一个示例日志摘录：

InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

对于文件系统崩溃或硬件问题的情况，我们可以假设在重新启动后 MySQL 磁盘数据不可用。这意味着 MySQL 无法成功启动，因为一些磁盘数据块不再可读。在这种情况下，需要重新格式化磁盘，安装新的磁盘，或者以其他方式纠正潜在问题。然后需要从备份中恢复我们的 MySQL 数据，这意味着备份必须已经制作好。为确保情况如此，设计并实施一个备份策略。

9.3.1 建立备份策略

原文：dev.mysql.com/doc/refman/8.0/en/backup-policy.html

为了有用，备份必须定期安排。在 MySQL 中可以使用几种工具进行完整备份（在某个时间点的数据快照）。例如，MySQL 企业版备份可以执行整个实例的物理备份，并进行优化以最小化开销并在备份InnoDB数据文件时避免中断；mysqldump提供在线逻辑备份。本讨论使用mysqldump。

假设我们在星期日下午 1 点，负载较低时，使用以下命令对所有数据库中的所有InnoDB表进行完全备份：

$> mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql

由mysqldump生成的.sql文件包含一组 SQL INSERT语句，可用于在以后重新加载转储的表。

此备份操作在转储开始时（使用FLUSH TABLES WITH READ LOCK）获取所有表的全局读锁。一旦获得此锁，就会读取二进制日志坐标并释放锁。如果在发出FLUSH语句时正在运行长时间的更新语句，则备份操作可能会停顿，直到这些语句完成。之后，转储变为无锁状态，不会干扰表的读写操作。

之前假设要备份的表是InnoDB表，因此--single-transaction使用一致性读取，并保证mysqldump看到的数据不会更改。（其他客户端对InnoDB表所做的更改不会被mysqldump进程看到。）如果备份操作包括非事务表，一致性要求在备份期间它们不会更改。例如，在mysql数据库中的MyISAM表，备份期间不能对 MySQL 帐户进行管理更改。

完整备份是必要的，但并不总是方便创建。它们产生大型备份文件并需要时间生成。从优化的角度来看，每次连续的完整备份都包含所有数据，即使是自上次完整备份以来未更改的部分。更有效的方法是进行初始完整备份，然后进行增量备份。增量备份更小，生成时间更短。权衡之处在于，在恢复时，你不能仅通过重新加载完整备份来恢复数据。你还必须处理增量备份以恢复增量更改。

要进行增量备份，我们需要保存增量更改。在 MySQL 中，这些更改在二进制日志中表示，因此 MySQL 服务器应始终使用--log-bin选项启动以启用该日志。启用二进制日志记录后，服务器在更新数据时将每个数据更改写入文件。查看运行了一些天的 MySQL 服务器的数据目录，我们会发现这些 MySQL 二进制日志文件：

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

每次重新启动时，MySQL 服务器都会使用序列中的下一个数字创建一个新的二进制日志文件。在服务器运行时，您还可以告诉它通过发出FLUSH LOGS SQL 语句或使用mysqladmin flush-logs命令手动关闭当前的二进制日志文件并开始一个新的。mysqldump还有一个选项来刷新日志。数据目录中的.index文件包含目录中所有 MySQL 二进制日志的列表。

MySQL 二进制日志对于恢复很重要，因为它们形成了增量备份集。如果确保在进行完整备份时刷新日志，那么之后创建的二进制日志文件将包含自备份以来进行的所有数据更改。让我们稍微修改之前的mysqldump命令，以便在完整备份时刷新 MySQL 二进制日志，并使转储文件包含新当前二进制日志的名称：

$> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases > backup_sunday_1_PM.sql

执行此命令后，数据目录包含一个新的二进制日志文件，gbichot2-bin.000007，因为--flush-logs选项导致服务器刷新其日志。--master-data选项导致mysqldump将二进制日志信息写入其输出，因此生成的.sql转储文件包含这些行：

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

因为mysqldump命令进行了完整备份，这些行意味着两件事：

• 转储文件包含在写入gbichot2-bin.000007二进制日志文件或更高版本之前所做的所有更改。

• 备份后记录的所有数据更改都不包含在转储文件中，但包含在gbichot2-bin.000007二进制日志文件或更高版本中。

在星期一下午 1 点，我们可以通过刷新日志来开始一个新的二进制日志文件进行增量备份。例如，执行mysqladmin flush-logs命令会创建gbichot2-bin.000008。在星期日下午 1 点全量备份和星期一下午 1 点之间的所有更改都写入gbichot2-bin.000007。这个增量备份很重要，所以最好将其复制到一个安全的地方。（例如，将其备份到磁带或 DVD 上，或将其复制到另一台机器上。）在星期二下午 1 点，执行另一个mysqladmin flush-logs命令。在星期一下午 1 点和星期二下午 1 点之间的所有更改都写入gbichot2-bin.000008（也应该将其复制到安全的地方）。

MySQL 二进制日志占用磁盘空间。为了释放空间，定期清理它们。一种方法是删除不再需要的二进制日志，例如当我们进行全量备份时：

$> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases --delete-master-logs > backup_sunday_1_PM.sql

注意

使用mysqldump --delete-master-logs删除 MySQL 二进制日志可能会很危险，如果您的服务器是一个复制源服务器，因为副本可能尚未完全处理二进制日志的内容。PURGE BINARY LOGS语句的描述解释了在删除 MySQL 二进制日志之前应该验证的内容。请参阅 Section 15.4.1.1, “PURGE BINARY LOGS Statement”。

9.3.2 使用备份进行恢复

原文：dev.mysql.com/doc/refman/8.0/en/recovery-from-backups.html

现在，假设我们在周三早上 8 点发生了灾难性的意外退出，需要从备份中恢复。为了恢复，首先我们恢复我们拥有的最后一个完整备份（即周日下午 1 点的备份）。完整备份文件只是一组 SQL 语句，因此恢复它非常容易：

$> mysql < backup_sunday_1_PM.sql

此时，数据已恢复到周日下午 1 点的状态。要恢复自那时以来所做的更改，我们必须使用增量备份；也就是说，gbichot2-bin.000007和gbichot2-bin.000008二进制日志文件。如有必要，从备份位置获取文件，然后像这样处理它们的内容：

$> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

现在，我们已将数据恢复到周二下午 1 点的状态，但仍然缺少从那天到崩溃日期的更改。为了不丢失它们，我们需要让 MySQL 服务器将其 MySQL 二进制日志存储到一个安全位置（RAID 磁盘，SAN，...），与存储数据文件的位置不同，以便这些日志不在被破坏的磁盘上。（也就是说，我们可以使用--log-bin选项启动服务器，指定一个与数据目录所在的物理设备不同的位置。这样，即使包含目录的设备丢失，日志也是安全的。）如果我们这样做了，我们将手头上有gbichot2-bin.000009文件（以及任何后续文件），我们可以使用mysqlbinlog和mysql应用它们，恢复最近的数据更改，直到崩溃时刻，而无需丢失：

$> mysqlbinlog gbichot2-bin.000009 ... | mysql

有关使用mysqlbinlog处理二进制日志文件的更多信息，请参阅第 9.5 节，“时间点（增量）恢复” Recovery")。

9.3.3 备份策略摘要

原文：dev.mysql.com/doc/refman/8.0/en/backup-strategy-summary.html

在操作系统崩溃或断电的情况下，InnoDB本身会完成所有数据恢复的工作。但为了确保您能安心入睡，请遵守以下准则：

• 始终以启用二进制日志记录的方式运行 MySQL 服务器（这是 MySQL 8.0 的默认设置）。如果您有这样的安全介质，这种技术也可以用于磁盘负载平衡（从而提高性能）。

• 做定期完整备份，使用之前在第 9.3.1 节，“建立备份策略”中展示的mysqldump命令，进行在线、非阻塞备份。

• 通过使用FLUSH LOGS或mysqladmin flush-logs来进行定期增量备份。

9.4 使用 mysqldump 进行备份

dev.mysql.com/doc/refman/8.0/en/using-mysqldump.html

9.4.1 使用 mysqldump 以 SQL 格式导出数据

9.4.2 重新加载 SQL 格式备份

9.4.3 使用 mysqldump 以分隔文本格式导出数据

9.4.4 重新加载分隔文本格式备份

9.4.5 mysqldump 技巧

提示

考虑使用 MySQL Shell dump 工具，提供多线程并行导出、文件压缩、进度信息显示，以及云功能，如 Oracle Cloud Infrastructure Object Storage 流式传输，以及 MySQL HeatWave Service 兼容性检查和修改。导出的数据可以轻松导入到 MySQL Server 实例或 MySQL HeatWave Service DB System 中，使用 MySQL Shell load dump 工具。MySQL Shell 的安装说明可在这里找到。

本节描述了如何使用mysqldump生成导出文件，以及如何重新加载导出文件。导出文件可以以多种方式使用：

• 作为备份，以便在数据丢失时进行数据恢复。

• 作为设置副本的数据源。

• 作为实验的数据源：

  • 复制数据库的副本，可以在不更改原始数据的情况下使用。

  • 用于测试潜在的升级不兼容性。

mysqldump生成两种类型的输出，取决于是否给出--tab选项：

• 没有--tab选项时，mysqldump将 SQL 语句写入标准输出。这些输出包括用于创建导出对象（数据库、表、存储过程等）的CREATE语句，以及用于将数据加载到表中的INSERT语句。输出可以保存在文件中，并使用mysql稍后重新加载，以重新创建导出的对象。有选项可修改 SQL 语句的格式，并控制导出哪些对象。

• 使用--tab，mysqldump为每个转储的表生成两个输出文件。服务器将一个文件写为制表符分隔的文本，每行一个表行。该文件在输出目录中命名为*tbl_name*.txt。服务器还向mysqldump发送一个CREATE TABLE语句，该语句被写入一个名为*tbl_name*.sql的文件中。

9.4.1 使用 mysqldump 以 SQL 格式转储数据

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-sql-format.html

本节描述如何使用mysqldump创建 SQL 格式的转储文件。有关重新加载此类转储文件的信息，请参见 Section 9.4.2, “Reloading SQL-Format Backups”。

默认情况下，mysqldump将信息写入标准输出作为 SQL 语句。您可以将输出保存在文件中：

$> mysqldump [*arguments*] > *file_name*

要转储所有数据库，请使用--all-databases选项调用mysqldump：

$> mysqldump --all-databases > dump.sql

要仅转储特定数据库，请在命令行上命名它们并使用--databases选项：

$> mysqldump --databases db1 db2 db3 > dump.sql

--databases选项导致命令行上的所有名称被视为数据库名称。没有此选项，mysqldump将第一个名称视为数据库名称，后续名称视为表名称。

使用--all-databases或--databases，mysqldump在转储输出之前为每个数据库写入CREATE DATABASE和USE语句。这确保在重新加载转储文件时，如果数据库不存在，则创建每个数据库并将其设置为默认数据库，因此数据库内容将加载到与其来源相同的数据库中。如果要导致转储文件在重新创建数据库之前强制删除每个数据库，请同时使用--add-drop-database选项。在这种情况下，mysqldump在每个CREATE DATABASE语句之前写入一个DROP DATABASE语句。

要转储单个数据库，请在命令行上命名它：

$> mysqldump --databases test > dump.sql

在单个数据库的情况下，可以省略--databases选项：

$> mysqldump test > dump.sql

两个前述命令之间的区别在于，没有--databases，转储输出不包含CREATE DATABASE或USE语句。这有几个影响：

• 当重新加载转储文件时，必须指定一个默认数据库名称，以便服务器知道要重新加载哪个数据库。

• 对于重新加载，可以指定与原始名称不同的数据库名称，这使您可以将数据重新加载到不同的数据库中。

• 如果要重新加载的数据库不存在，必须首先创建它。

• 因为输出不包含CREATE DATABASE语句，--add-drop-database选项没有效果。如果使用它，将不会生成DROP DATABASE语句。

为了仅从数据库中导出特定表，需要在命令行中跟随数据库名称命名这些表：

$> mysqldump test t1 t3 t7 > dump.sql

默认情况下，如果在创建转储文件的服务器上使用了 GTIDs（gtid_mode=ON），mysqldump在输出中包含一个SET @@GLOBAL.gtid_purged语句，将源服务器上的gtid_executed集合中的 GTIDs 添加到目标服务器上的gtid_purged集合中。如果仅转储特定数据库或表，重要的是要注意由mysqldump包含的值包括源服务器上gtid_executed集合中的所有事务的 GTIDs，即使这些事务更改了数据库的被抑制部分，或者服务器上未包含在部分转储中的其他数据库。如果您只在目标服务器上重放一个部分转储文件，额外的 GTIDs 不会对该服务器的未来操作造成任何问题。但是，如果在目标服务器上重放包含相同 GTIDs 的第二个转储文件（例如，来自同一源服务器的另一个部分转储），第二个转储文件中的任何SET @@GLOBAL.gtid_purged语句将失败。为避免此问题，要么将mysqldump选项--set-gtid-purged设置为OFF或COMMENTED，以在输出第二个转储文件时不包含活动的SET @@GLOBAL.gtid_purged语句，要么在重放转储文件之前手动删除该语句。

9.4.2 重新加载 SQL 格式备份

原文：dev.mysql.com/doc/refman/8.0/en/reloading-sql-format-dumps.html

要重新加载由mysqldump编写的包含 SQL 语句的转储文件，请将其用作mysql客户端的输入。如果转储文件是由mysqldump使用--all-databases或--databases选项创建的，则其中包含CREATE DATABASE和USE语句，因此无需指定默认数据库来加载数据：

$> mysql < dump.sql

或者，从mysql内部使用source命令：

mysql> source dump.sql

如果文件是一个不包含CREATE DATABASE和USE语句的单个数据库转储文件，请首先创建数据库（如果需要）：

$> mysqladmin create db1

然后在加载转储文件时指定数据库名称：

$> mysql db1 < dump.sql

或者，从mysql内部创建数据库，将其选择为默认数据库，并加载转储文件：

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

注意

对于 Windows PowerShell 用户：由于"<"字符在 PowerShell 中保留供将来使用，因此需要另一种方法，例如使用引号cmd.exe /c "mysql < dump.sql"。

9.4.3 使用 mysqldump 以分隔文本格式转储数据

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-delimited-text.html

本节描述了如何使用mysqldump创建分隔文本转储文件。有关重新加载此类转储文件的信息，请参见 Section 9.4.4, “Reloading Delimited-Text Format Backups”。

如果使用--tab=*dir_name*选项调用mysqldump，它将使用dir_name作为输出目录，并在该目录中单独转储表，每个表使用两个文件。表名是这些文件的基本名称。对于名为t1的表，文件名为t1.sql和t1.txt。.sql文件包含表的CREATE TABLE语句。.txt文件包含表数据，每行一个表行。

以下命令将db1数据库的内容转储到/tmp数据库中：

$> mysqldump --tab=/tmp db1

包含表数据的.txt文件由服务器编写，因此它们由用于运行服务器的系统帐户拥有。服务器使用SELECT ... INTO OUTFILE来写入文件，因此您必须具有FILE权限才能执行此操作，如果给定的.txt文件已经存在，则会发生错误。

服务器将转储表的CREATE定义发送给mysqldump，后者将它们写入.sql文件。因此，这些文件的所有者是执行mysqldump的用户。

最好只在本地服务器上使用--tab进行转储。如果在远程服务器上使用它，那么--tab目录必须同时存在于本地和远程主机上，并且.txt文件由服务器在远程目录（在服务器主机上）中写入，而.sql文件由mysqldump在本地目录（在客户端主机上）中写入。

对于mysqldump --tab，服务器默认将表数据写入.txt文件，每行一个行，列值之间用制表符分隔，列值周围没有引号，换行符作为行终止符。（这些是与SELECT ... INTO OUTFILE相同的默认值。）

为了使数据文件以不同格式编写，mysqldump支持以下选项：

• --fields-terminated-by=*str*

  用于分隔列值的字符串（默认：制表符）。

• --fields-enclosed-by=*char*

  用于封装列值的字符（默认：无字符）。

• --fields-optionally-enclosed-by=*char*

  用于封装非数字列值的字符（默认：无字符）。

• --fields-escaped-by=*char*

  用于转义特殊字符的字符（默认：不转义）。

• --lines-terminated-by=*str*

  行终止字符串（默认：换行符）。

根据您为这些选项中的任何一个指定的值，可能需要在命令行上适当地引用或转义该值。或者，使用十六进制表示值。假设您希望mysqldump在双引号内引用列值。为此，请将双引号指定为--fields-enclosed-by选项的值。但是，此字符通常对命令解释器特殊，必须特殊处理。例如，在 Unix 上，您可以这样引用双引号：

--fields-enclosed-by='"'

在任何平台上，您都可以以十六进制指定值：

--fields-enclosed-by=0x22

通常会一起使用几个数据格式选项。例如，要以逗号分隔值格式转储表，并以回车/换行对（\r\n）终止行，请使用以下命令（在一行上输入）：

$> mysqldump --tab=/tmp --fields-terminated-by=,
         --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1

如果您使用任何数据格式选项来转储表数据，则在以后重新加载数据文件时，需要指定相同的格式，以确保正确解释文件内容。

9.4.4 重新加载分隔文本格式备份

原文：dev.mysql.com/doc/refman/8.0/en/reloading-delimited-text-dumps.html

对于使用mysqldump --tab生成的备份，每个表在输出目录中由一个包含表的CREATE TABLE语句的.sql文件和一个包含表数据的.txt文件表示。要重新加载表，首先进入输出目录。然后使用mysql处理.sql文件以创建空表，并处理.txt文件以将数据加载到表中：

$> mysql db1 < t1.sql
$> mysqlimport db1 t1.txt

除了使用mysqlimport加载数据文件之外，还可以在mysql客户端内部使用LOAD DATA语句：

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

如果在最初转储表时使用了任何数据格式化选项mysqldump，则必须在使用mysqlimport或LOAD DATA时使用相同的选项，以确保正确解释数据文件内容：

$> mysqlimport --fields-terminated-by=,
         --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt

或：

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1
       FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"'
       LINES TERMINATED BY '\r\n';

9.4.5 mysqldump 提示

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-tips.html

9.4.5.1 复制数据库

9.4.5.2 从一个服务器复制数据库到另一个服务器

9.4.5.3 转储存储程序

9.4.5.4 分别转储表定义和内容

9.4.5.5 使用 mysqldump 测试升级不兼容性

本节介绍了使用mysqldump解决特定问题的技术：

• 如何复制数据库

• 如何从一个服务器复制数据库到另一个服务器

• 如何转储存储程序（存储过程和函数，触发器和事件）

• 如何分别转储定义和数据

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-copying-database.html

9.4.5.1 复制数据库

$> mysqldump db1 > dump.sql
$> mysqladmin create db2
$> mysql db2 < dump.sql

在 mysqldump 命令行中不要使用 --databases，因为这会导致在转储文件中包含 USE db1，这会覆盖在 mysql 命令行上命名 db2 的效果。

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-copying-to-other-server.html

9.4.5.2 从一个服务器复制数据库到另一个服务器

在服务器 1 上：

$> mysqldump --databases db1 > dump.sql

将转储文件从服务器 1 复制到服务器 2。

在服务器 2 上：

$> mysql < dump.sql

使用--databases选项与mysqldump命令行一起使用，导致转储文件包含CREATE DATABASE和USE语句，如果数据库存在则创建数据库，并将其设置为重新加载数据的默认数据库。

或者，您可以从mysqldump命令中省略--databases。然后，您需要在服务器 2 上创建数据库（如果需要），并在重新加载转储文件时将其指定为默认数据库。

在服务器 1 上：

$> mysqldump db1 > dump.sql

在服务器 2 上：

$> mysqladmin create db1
$> mysql db1 < dump.sql

在这种情况下，您可以指定不同的数据库名称，因此从mysqldump命令中省略--databases使您能够从一个数据库转储数据并加载到另一个数据库中。

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-stored-programs.html

9.4.5.3 存储程序的导出

控制mysqldump如何处理存储程序（存储过程和函数，触发器和事件）的几个选项：

• --events: 导出事件调度器事件

• --routines: 导出存储过程和函数

• --triggers: 为表导出触发器

--triggers选项默认启用，因此在导出表时，它们将附带任何触发器。其他选项默认禁用，必须明确指定以导出相应的对象。要明确禁用这些选项中的任何一个，请使用其跳过形式：--skip-events，--skip-routines，或--skip-triggers。

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-definition-data-dumps.html

9.4.5.4 分别转储表定义和内容

--no-data选项告诉mysqldump不要转储表数据，导致转储文件仅包含创建表的语句。相反，--no-create-info选项告诉mysqldump从输出中抑制CREATE语句，使转储文件仅包含表数据。

例如，要分别为test数据库转储表定义和数据，请使用以下命令：

$> mysqldump --no-data test > dump-defs.sql
$> mysqldump --no-create-info test > dump-data.sql

对于仅定义的转储，请添加--routines和--events选项，以包括存储过程和事件定义：

$> mysqldump --no-data --routines --events test > dump-defs.sql

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-upgrade-testing.html

9.4.5.5 使用 mysqldump 进行升级不兼容性测试

在考虑升级 MySQL 时，明智的做法是将新版本单独安装在当前生产版本之外。然后，您可以从生产服务器中转储数据库和数据库对象定义，然后加载到新服务器中以验证它们是否被正确处理。（这也对降级测试很有用。）

在生产服务器上：

$> mysqldump --all-databases --no-data --routines --events > dump-defs.sql

在升级后的服务器上：

$> mysql < dump-defs.sql

因为转储文件不包含表数据，所以可以快速处理。这使您能够在等待长时间的数据加载操作时发现潜在的不兼容性。在处理转储文件时查找警告或错误。

在确认定义已经正确处理后，转储数据并尝试加载到升级后的服务器中。

在生产服务器上：

$> mysqldump --all-databases --no-create-info > dump-data.sql

在升级后的服务器上：

$> mysql < dump-data.sql

现在检查表内容并运行一些测试查询。

9.5 时间点（增量）恢复

原文：dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html

9.5.1 使用二进制日志进行时间点恢复

9.5.2 使用事件位置进行时间点恢复

时间点恢复指的是恢复数据更改直到特定时间点。通常，在恢复将服务器恢复到备份制作时的状态的完整备份之后执行此类型的恢复。（完整备份可以通过多种方式进行，比如第 9.2 节“数据库备份方法”中列出的方式。）然后，时间点恢复从完整备份的时间逐步将服务器更新到更近的时间点。

9.5.1 使用二进制日志进行时间点恢复

原文：dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery-binlog.html

本节解释了使用二进制日志执行时间点恢复的一般思路。下一节，第 9.5.2 节，“使用事件位置进行时间点恢复”，通过示例详细解释了操作。

注意

本节和下一节中的许多示例使用mysql客户端来处理mysqlbinlog生成的二进制日志输出。如果您的二进制日志包含\0（空）字符，则除非使用--binary-mode选项调用，否则mysql无法解析该输出。

时间点恢复的信息来源是在完全备份操作之后生成的一组二进制日志文件。因此，为了允许服务器恢复到某个时间点，必须在其上启用二进制日志记录，这是 MySQL 8.0 的默认设置（参见 第 7.4.4 节，“二进制日志”）。

要从二进制日志中恢复数据，必须知道当前二进制日志文件的名称和位置。默认情况下，服务器在数据目录中创建二进制日志文件，但可以使用--log-bin选项指定路径名以将文件放在不同位置。要查看所有二进制日志文件的列表，请使用以下语句：

mysql> SHOW BINARY LOGS;

要确定当前二进制日志文件的名称，请执行以下语句：

mysql> SHOW MASTER STATUS;

mysqlbinlog 实用程序将二进制日志文件中的事件从二进制格式转换为文本，以便查看或应用。mysqlbinlog 有选项可根据事件时间或日志中事件位置选择二进制日志的部分。请参阅 第 6.6.9 节，“mysqlbinlog — 用于处理二进制日志文件的实用程序”。

从二进制日志应用事件会导致它们所代表的数据修改重新执行。这使得可以恢复给定时间段的数据更改。要从二进制日志应用事件，请使用mysql客户端处理mysqlbinlog输出：

$> mysqlbinlog *binlog_files* | mysql -u root -p

如果二进制日志文件已加密，从 MySQL 8.0.14 开始可以执行此操作，mysqlbinlog 无法像上面的示例那样直接读取它们，但可以使用 --read-from-remote-server (-R) 选项从服务器读取它们。例如：

$> mysqlbinlog --read-from-remote-server --host=*host_name* --port=3306  --user=root --password --ssl-mode=required  *binlog_files* | mysql -u root -p

在这里，选项 --ssl-mode=required 被用来确保从二进制日志文件中传输的数据在传输过程中受到保护，因为它以未加密的格式发送给 mysqlbinlog。

重要

VERIFY_CA 和 VERIFY_IDENTITY 比 REQUIRED 更好的选择作为 SSL 模式，因为它们有助于防止中间人攻击。要实施这些设置之一，您必须首先确保服务器的 CA 证书可靠地提供给所有在您的环境中使用它的客户端，否则将导致可用性问题。请参阅加密连接的命令选项。

查看日志内容在需要确定事件时间或位置以选择执行事件之前的部分日志内容时很有用。要查看日志中的事件，请将 mysqlbinlog 输出发送到分页程序：

$> mysqlbinlog *binlog_files* | more

或者，将输出保存在文件中，并在文本编辑器中查看文件：

$> mysqlbinlog *binlog_files* > tmpfile
$> ... *edit tmpfile* ...

编辑文件后，应用内容如下：

$> mysql -u root -p < tmpfile

如果您有多个要应用于 MySQL 服务器的二进制日志，则可以使用单个连接来应用要处理的所有二进制日志文件的内容。以下是一种方法：

$> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

另一种方法是将整个日志写入单个文件，然后处理该文件：

$> mysqlbinlog binlog.000001 >  /tmp/statements.sql
$> mysqlbinlog binlog.000002 >> /tmp/statements.sql
$> mysql -u root -p -e "source /tmp/statements.sql"

9.5.2 使用事件位置进行时间点恢复

原文：dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery-positions.html

最后一节，第 9.5.1 节，“使用二进制日志进行时间点恢复”，解释了使用二进制日志执行时间点恢复的一般思路。该部分通过示例详细解释了操作。

例如，假设在 2020 年 3 月 11 日 20:06:00 左右执行了一个删除表的 SQL 语句。您可以执行时间点恢复，将服务器恢复到表删除之前的状态。以下是一些实现这一目标的示例步骤：

1. 恢复在感兴趣时间点之前创建的最后一个完整备份（我们的示例中是 2020 年 3 月 11 日 20:06:00 的t[p]）。完成后，记录已恢复服务器的二进制日志位置以供以后使用，并重新启动服务器。

   注意

   虽然 InnoDB 在恢复和服务器重新启动后也会显示最后一个恢复的二进制日志位置，但这不是获取恢复结束日志位置的可靠方法，因为在显示位置之后可能发生了 DDL 事件和非 InnoDB 更改。您的备份和恢复工具应为您提供用于恢复的最后一个二进制日志位置：例如，如果您正在使用mysqlbinlog执行任务，请检查二进制日志重放的停止位置；如果您正在使用 MySQL 企业版备份，则最后一个二进制日志位置已保存在您的备份中。请参阅时间点恢复。

2. 找到与您想要恢复数据库的时间点对应的精确二进制日志事件位置。在我们的示例中，假设我们知道表删除发生的大致时间（t[p]），我们可以通过使用mysqlbinlog实用程序来检查该时间周围的日志内容，找到日志位置。使用--start-datetime和--stop-datetime选项来指定围绕t[p]的短时间段，然后在输出中查找事件。例如：

   $> mysqlbinlog --start-datetime="2020-03-11 20:05:00" \
                      --stop-datetime="2020-03-11 20:08:00" --verbose \
            /var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE"
   
   /*!80014 SET @@session.original_server_version=80019*//*!*/;
   /*!80014 SET @@session.immediate_server_version=80019*//*!*/;
   SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
   # at 232
   #200311 20:06:20 server id 1  end_log_pos 355 CRC32 0x2fc1e5ea 	Query	thread_id=16	exec_time=0	error_code=0
   SET TIMESTAMP=1583971580/*!*/;
   SET @@session.pseudo_thread_id=16/*!*/;
   SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
   SET @@session.sql_mode=1168113696/*!*/;
   SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
   /*!\C utf8mb4 *//*!*/;
   SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
   SET @@session.lc_time_names=0/*!*/;
   SET @@session.collation_database=DEFAULT/*!*/;
   /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
   DROP TABLE `pets`.`cats` /* generated by server */
   /*!*/;
   # at 355
   #200311 20:07:48 server id 1  end_log_pos 434 CRC32 0x123d65df 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1583971668462467	immediate_commit_timestamp=1583971668462467	transaction_length=473
   # original_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)
   # immediate_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)
   /*!80001 SET @@session.original_commit_timestamp=1583971668462467*//*!*/;
   /*!80014 SET @@session.original_server_version=80019*//*!*/;
   /*!80014 SET @@session.immediate_server_version=80019*//*!*/;
   SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
   # at 434
   #200311 20:07:48 server id 1  end_log_pos 828 CRC32 0x57fac9ac 	Query	thread_id=16	exec_time=0	error_code=0	Xid = 217
   use `pets`/*!*/;
   SET TIMESTAMP=1583971668/*!*/;
   /*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
   CREATE TABLE dogs
   

   从mysqlbinlog的输出中，可以在二进制日志的段中找到DROP TABLE pets.cats``语句，该语句位于# at 232和# at 355之间，这意味着该语句发生在日志位置 232 之后，并且在DROP TABLE语句之后的位置 355 处。

   注意

   仅使用--start-datetime和--stop-datetime选项来帮助您找到感兴趣的实际事件位置。不建议使用这两个选项来指定要应用的二进制日志段的范围：使用这些选项时更容易错过二进制日志事件。请改用--start-position和--stop-position。

3. 将二进制日志文件中的事件应用到服务器上，从您在第 1 步中找到的日志位置开始（假设为 155），直到您在第 2 步中找到的在您感兴趣的时间点之前的位置（即 232）：

   $> mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 \
            | mysql -u root -p
   

   该命令从起始位置恢复所有事务，直到停止位置之前。因为mysqlbinlog的输出在每个记录的 SQL 语句之前包含SET TIMESTAMP语句，恢复的数据和相关的 MySQL 日志反映了事务执行的原始时间。

   您的数据库现在已经恢复到感兴趣的时间点t[p]，就在表pets.cats被删除之前。

4. 完成了点时间恢复后，如果您还想重新执行您感兴趣的时间点之后的所有语句，请再次使用mysqlbinlog来将t[p]之后的所有事件应用到服务器上。我们在第 2 步中指出，在我们想要跳过的语句之后，日志位于位置 355；我们可以将其用于--start-position选项，以便包括位置之后的任何语句：

   $> mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456 \
            | mysql -u root -p
   

   您的数据库已经恢复到二进制日志文件中记录的最新语句，但跳过了选定的事件。

9.6 MyISAM 表维护和崩溃恢复

原文：dev.mysql.com/doc/refman/8.0/en/myisam-table-maintenance.html

9.6.1 使用 myisamchk 进行崩溃恢复

9.6.2 如何检查 MyISAM 表中的错误

9.6.3 如何修复 MyISAM 表

9.6.4 MyISAM 表优化

9.6.5 设置 MyISAM 表维护计划

本节讨论如何使用myisamchk来检查或修复MyISAM表（用于存储数据和索引的.MYD和.MYI文件的表）。有关一般myisamchk背景，请参阅 Section 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”。其他表修复信息可在 Section 3.14, “Rebuilding or Repairing Tables or Indexes”找到。

您可以使用myisamchk来检查、修复或优化数据库表。以下各节描述了如何执行这些操作以及如何设置表维护计划。有关使用myisamchk获取有关您的表的信息，请参阅 Section 6.6.4.5, “Obtaining Table Information with myisamchk”。

即使使用myisamchk进行表修复是相当安全的，但在进行修复或任何可能对表进行大量更改的维护操作之前，始终最好先备份数据。

myisamchk影响索引的操作可能导致MyISAM FULLTEXT索引使用与 MySQL 服务器使用的值不兼容的全文参数进行重建。为避免此问题，请遵循 Section 6.6.4.1, “myisamchk General Options”中的指南。

MyISAM表维护也可以使用执行类似于myisamchk的操作的 SQL 语句来完成：

• 要检查MyISAM表，请使用CHECK TABLE。

• 要修复MyISAM表，请使用REPAIR TABLE。

• 要优化MyISAM表，请使用OPTIMIZE TABLE。

• 要分析MyISAM表，请使用ANALYZE TABLE。

有关这些语句的更多信息，请参阅 Section 15.7.3, “Table Maintenance Statements”。

这些语句可以直接使用，也可以通过mysqlcheck客户端程序使用。这些语句相对于myisamchk的一个优点是服务器完成所有工作。使用myisamchk时，您必须确保服务器不同时使用表，以避免myisamchk和服务器之间发生不必要的交互。

9.6.1 使用 myisamchk 进行崩溃恢复

原文：dev.mysql.com/doc/refman/8.0/en/myisam-crash-recovery.html

这一部分描述了如何检查和处理 MySQL 数据库中的数据损坏。如果您的表经常损坏，您应该尝试找出原因。请参阅第 B.3.3.3 节，“如果 MySQL 经常崩溃该怎么办”。

有关MyISAM表如何变得损坏的解释，请参阅第 18.2.4 节，“MyISAM 表问题”。

如果您使用外部锁定禁用（默认情况下）运行mysqld，您不能可靠地使用myisamchk来检查一个表，当mysqld正在使用相同的表时。如果您可以确定没有人可以在您运行myisamchk检查表时使用mysqld访问表，您只需在开始检查表之前执行mysqladmin flush-tables。如果您无法保证这一点，您必须在检查表时停止mysqld。如果您运行myisamchk来检查mysqld同时正在更新的表，即使表没有损坏，您可能会收到警告。

如果服务器启用了外部锁定，您可以随时使用myisamchk来检查表。在这种情况下，如果服务器尝试更新一个正在使用的表，服务器会等待myisamchk完成后才继续。

如果您使用myisamchk来修复或优化表，您必须始终确保mysqld服务器未使用该表（如果禁用外部锁定也适用）。如果不停止mysqld，您至少应在运行myisamchk之前执行mysqladmin flush-tables。如果服务器和myisamchk同时访问表，您的表可能会损坏。

在执行崩溃恢复时，重要的是要理解数据库中每个MyISAM表tbl_name对应于数据库目录中的三个文件，如下表所示。

文件	目的
*tbl_name*.MYD	数据文件
*tbl_name*.MYI	索引文件

这三种文件类型中的每一种都可能以各种方式损坏，但问题最常发生在数据文件和索引文件中。

myisamchk 通过逐行创建.MYD数据文件的副本来工作。它通过删除旧的.MYD文件并将新文件重命名为原始文件名来结束修复阶段。如果使用--quick，myisamchk 不会创建临时的.MYD文件，而是假设.MYD文件是正确的，并且仅生成一个新的索引文件而不触及.MYD文件。这是安全的，因为myisamchk会自动检测.MYD文件是否损坏，并在损坏时中止修复。您还可以两次指定--quick选项给myisamchk。在这种情况下，myisamchk不会在某些错误（如重复键错误）上中止，而是尝试通过修改.MYD文件来解决这些错误。通常只有在磁盘空间不足以执行正常修复时，才有必要使用两个--quick选项。在这种情况下，您至少应在运行myisamchk之前备份表。

9.6.2 如何检查 MyISAM 表中的错误

原文：dev.mysql.com/doc/refman/8.0/en/myisam-check.html

要检查一个MyISAM表，请使用以下命令：

• myisamchk tbl_name

  这可以找到 99.99%的所有错误。它无法找到仅涉及数据文件的损坏（这是非常不寻常的）。如果要检查一个表，通常应该运行myisamchk而不带选项或带上-s（静默）选项。

• myisamchk -m tbl_name

  这可以找到 99.999%的所有错误。它首先检查所有索引条目是否有错误，然后逐行阅读。它为行中所有键值计算校验和，并验证校验和是否与索引树中键的校验和匹配。

• myisamchk -e tbl_name

  这会对所有数据进行完整彻底的检查（-e表示“扩展检查”）。它对每一行的每个键进行检查读取，以验证它们确实指向正确的行。对于具有许多索引的大表，这可能需要很长时间。通常，myisamchk在找到第一个错误后就会停止。如果要获取更多信息，可以添加-v（详细）选项。这会导致myisamchk继续进行，最多检查 20 个错误。

• myisamchk -e -i tbl_name

  这类似于先前的命令，但-i选项告诉myisamchk打印额外的统计信息。

在大多数情况下，只需简单运行myisamchk命令，除了表名之外不带其他参数就足以检查一个表。

9.6.3 如何修复 MyISAM 表

原文：dev.mysql.com/doc/refman/8.0/en/myisam-repair.html

本节讨论了如何在MyISAM表（扩展名为.MYI和.MYD）上使用myisamchk。

您还可以使用CHECK TABLE和REPAIR TABLE语句来检查和修复MyISAM表。请参阅 Section 15.7.3.2, “CHECK TABLE Statement”和 Section 15.7.3.5, “REPAIR TABLE Statement”。

损坏表的症状包括意外中止的查询和可观察到的错误，例如：

• 找不到文件*tbl_name*.MYI（错误代码：nnn）

• 文件意外结束

• 记录文件已崩溃

• 从表处理程序得到错误nnn

要获取有关错误的更多信息，请运行perror nnn，其中nnn是错误编号。以下示例显示如何使用perror查找指示表存在问题的最常见错误编号的含义：

$> perror 126 127 132 134 135 136 141 144 145
MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired

请注意，错误 135（记录文件中没有更多空间）和错误 136（索引文件中没有更多空间）不是简单修复的错误。在这种情况下，您必须使用ALTER TABLE来增加MAX_ROWS和AVG_ROW_LENGTH表选项值：

ALTER TABLE *tbl_name* MAX_ROWS=*xxx* AVG_ROW_LENGTH=*yyy*;

如果您不知道当前的表选项值，请使用SHOW CREATE TABLE。

对于其他错误，您必须修复您的表。myisamchk通常可以检测和修复大多数出现的问题。

修复过程包括最多三个阶段，这里进行描述。在开始之前，您应该切换到数据库目录并检查表文件的权限。在 Unix 上，请确保它们可被mysqld运行的用户（以及您，因为您需要访问正在检查的文件）读取。如果您需要修改文件，它们也必须可被您写入。

本节适用于表检查失败的情况（例如 Section 9.6.2, “How to Check MyISAM Tables for Errors”中描述的情况），或者您想要使用myisamchk提供的扩展功能。

用于表维护的myisamchk选项在第 6.6.4 节“myisamchk — MyISAM Table-Maintenance Utility”中有描述。myisamchk还有一些变量，您可以设置以控制可能改善性能的内存分配。请参阅第 6.6.4.6 节“myisamchk 内存使用”。

如果要从命令行修复表，必须先停止mysqld服务器。请注意，当在远程服务器上执行mysqladmin shutdown时，mysqld服务器在mysqladmin返回后仍可用一段时间，直到所有语句处理停止并所有索引更改已刷新到磁盘。

第一阶段：检查您的表

运行myisamchk *.MYI或myisamchk -e *.MYI如果时间充裕。使用-s（静默）选项以抑制不必要的信息。

如果mysqld服务器已停止，应使用--update-state选项告诉myisamchk将表标记为“已检查”。

只需修复myisamchk报告错误的那些表。对于这样的表，继续进行第二阶段。

如果在检查时遇到意外错误（例如内存不足错误），或者myisamchk崩溃，请转到第三阶段。

第二阶段：简单安全修复

首先尝试myisamchk -r -q tbl_name（-r -q表示“快速恢复模式”）。这将尝试修复索引文件而不触及数据文件。如果数据文件包含应有的所有内容，并且删除链接指向数据文件内的正确位置，则应该可以工作，并且表已修复。开始修复下一个表。否则，请使用以下过程：

1. 在继续之前备份数据文件。

2. 使用myisamchk -r tbl_name（-r表示“恢复模式”）。这将从数据文件中删除不正确的行和已删除的行，并重建索引文件。

3. 如果前面的步骤失败，请使用myisamchk --safe-recover tbl_name。安全恢复模式使用一种旧的恢复方法，处理一些常规恢复模式无法处理的情况（但速度较慢）。

注意

如果您希望修复操作更快完成，应将sort_buffer_size和key_buffer_size变量的值分别设置为可用内存的约 25%，当运行myisamchk时。

如果在修复过程中遇到意外错误（如内存不足错误），或者myisamchk崩溃，请进入第三阶段。

第三阶段：困难修复

只有在索引文件中的第一个 16KB 块被破坏或包含不正确信息，或者索引文件丢失时，才应该达到这个阶段。在这种情况下，需要创建一个新的索引文件。操作如下：

1. 将数据文件移至安全位置。

2. 使用表描述文件创建新的（空）数据和索引文件：

   $> mysql *db_name*
   

   mysql> SET autocommit=1;
   mysql> TRUNCATE TABLE *tbl_name*;
   mysql> quit
   

3. 将旧数据文件复制回新创建的数据文件。（不要只是将旧文件移回新文件。您希望保留一份副本以防出现问题。）

重要提示

如果您正在使用复制功能，应在执行上述过程之前停止复制，因为这涉及文件系统操作，而 MySQL 不会记录这些操作。

返回到第二阶段。myisamchk -r -q应该可以工作。（这不应该是一个无限循环。）

您还可以使用REPAIR TABLE *tbl_name* USE_FRM SQL 语句，该语句会自动执行整个过程。由于服务器在使用REPAIR TABLE时会完成所有工作，因此不会出现工具与服务器之间的意外交互。请参阅 Section 15.7.3.5, “REPAIR TABLE Statement”。

9.6.4 MyISAM 表优化

原文：dev.mysql.com/doc/refman/8.0/en/myisam-optimization.html

为了合并碎片化的行并消除由于删除或更新行而导致的空间浪费，以恢复模式运行myisamchk：

$> myisamchk -r *tbl_name*

你可以通过使用OPTIMIZE TABLE SQL 语句以相同的方式优化表。OPTIMIZE TABLE 进行表修复和关键分析，并对索引树进行排序，以使关键查找更快。使用OPTIMIZE TABLE时，没有可能发生工具与服务器之间的不良交互，因为当您使用OPTIMIZE TABLE时，服务器会完成所有工作。参见 Section 15.7.3.4, “OPTIMIZE TABLE Statement”。

myisamchk 还有许多其他选项，可用于提高表的性能：

• --analyze 或 -a：执行关键分布分析。这通过使连接优化器更好地选择连接表的顺序和应该使用的索引来提高连接性能。

• --sort-index 或 -S：对索引块进行排序。这样可以优化查找并使使用索引的表扫描更快。

• --sort-records=*index_num* 或 -R *index_num*：根据给定的索引对数据行进行排序。这样可以使您的数据更加局部化，并可能加快使用该索引的基于范围的SELECT和ORDER BY操作。

有关所有可用选项的完整描述，请参见 Section 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”。

9.6.5 设置 MyISAM 表维护计划

原文：dev.mysql.com/doc/refman/8.0/en/myisam-maintenance-schedule.html

定期执行表检查是一个好主意，而不是等到问题发生时再处理。检查和修复MyISAM表的一种方法是使用CHECK TABLE和REPAIR TABLE语句。参见第 15.7.3 节，“表维护语句”。

另一种检查表的方法是使用myisamchk。为了维护目的，您可以使用myisamchk -s。-s 选项（简写为--silent）会导致myisamchk以静默模式运行，仅在发生错误时打印消息。

启用自动的MyISAM表检查也是一个好主意。例如，每当机器在更新过程中重新启动时，通常需要在进一步使用之前检查可能受影响的每个表。（这些是“预期崩溃的表”）。要使服务器自动检查MyISAM表，请使用设置myisam_recover_options系统变量来启动。参见第 7.1.8 节，“服务器系统变量”。

在正常系统运行期间，您还应定期检查您的表。例如，您可以运行一个cron作业，每周检查一次重要的表，可以在crontab文件中使用以下行：

35 0 * * 0 */path/to/myisamchk* --fast --silent */path/to/datadir*/*/*.MYI

这将打印出有关崩溃表的信息，以便您可以根据需要检查和修复它们。

首先，在所有在过去 24 小时内更新过的表上每晚执行myisamchk -s。当您发现问题很少发生时，您可以将检查频率减少到每周一次或类似频率。

通常，MySQL 表需要很少的维护。如果您对具有动态大小行（具有VARCHAR、BLOB或TEXT列的MyISAM表进行了许多更新，或者有许多已删除行的表，您可能希望不时地对表进行碎片整理/回收空间。您可以通过对相关表使用OPTIMIZE TABLE来实现这一点。或者，如果您可以暂停mysqld服务器一段时间，切换到数据目录并在服务器停止时使用以下命令：

$> myisamchk -r -s --sort-index --myisam_sort_buffer_size=16M */*.MYI

第十章 优化

原文：dev.mysql.com/doc/refman/8.0/en/optimization.html

目录

10.1 优化概述

10.2 优化 SQL 语句

10.2.1 优化 SELECT 语句

10.2.2 优化子查询、派生表、视图引用和公共表表达式

10.2.3 优化 INFORMATION_SCHEMA 查询

10.2.4 优化性能模式查询

10.2.5 优化数据更改语句

10.2.6 优化数据库权限

10.2.7 其他优化技巧

10.3 优化和索引

10.3.1 MySQL 如何使用索引

10.3.2 主键优化

10.3.3 空间索引优化

10.3.4 外键优化

10.3.5 列索引

10.3.6 多列索引

10.3.7 验证索引使用

10.3.8 InnoDB 和 MyISAM 索引统计收集

10.3.9 B-Tree 和 Hash 索引比较

10.3.10 索引扩展的使用

10.3.11 生成列索引优化器使用

10.3.12 隐藏索引

10.3.13 降序索引

10.3.14 从 TIMESTAMP 列进行索引查找

10.4 优化数据库结构

10.4.1 优化数据大小

10.4.2 优化 MySQL 数据类型

10.4.3 优化多表查询

10.4.4 MySQL 中内部临时表的使用

10.4.5 数据库和表数量限制

10.4.6 表大小限制

10.4.7 表列数和行大小限制

10.5 优化 InnoDB 表

10.5.1 优化 InnoDB 表的存储布局

10.5.2 优化 InnoDB 事务管理

10.5.3 优化 InnoDB 只读事务

10.5.4 优化 InnoDB 重做日志记录

10.5.5 InnoDB 表的批量数据加载

10.5.6 优化 InnoDB 查询

10.5.7 优化 InnoDB DDL 操作

10.5.8 优化 InnoDB 磁盘 I/O

10.5.9 优化 InnoDB 配置变量

10.5.10 为具有多个表的系统优化 InnoDB

10.6 为 MyISAM 表优化

10.6.1 优化 MyISAM 查询

10.6.2 MyISAM 表的批量数据加载

10.6.3 优化 REPAIR TABLE 语句

10.7 为 MEMORY 表优化

10.8 理解查询执行计划

10.8.1 使用 EXPLAIN 优化查询

10.8.2 EXPLAIN 输出格式

10.8.3 扩展 EXPLAIN 输出格式

10.8.4 获取命名连接的执行计划信息

10.8.5 估算查询性能

10.9 控制查询优化器

10.9.1 控制查询计划评估

10.9.2 可切换的优化

10.9.3 优化器提示

10.9.4 索引提示

10.9.5 优化器成本模型

10.9.6 优化器统计信息

10.10 缓冲和缓存

10.10.1 InnoDB 缓冲池优化

10.10.2 MyISAM 键缓存

10.10.3 缓存准备语句和存储程序

10.11 优化锁定操作

10.11.1 内部锁定方法

10.11.2 表锁定问题

10.11.3 并发插入

10.11.4 元数据锁定

10.11.5 外部锁定

10.12 优化 MySQL 服务器

10.12.1 优化磁盘 I/O

10.12.2 使用符号链接

10.12.3 优化内存使用

10.13 测量性能（基准测试）

10.13.1 测量表达式和函数的速度

10.13.2 使用自己的基准

10.13.3 使用 performance_schema 测量性能

10.14 检查服务器线程（进程）信息

10.14.1 访问进程列表

10.14.2 线程命令值

10.14.3 一般线程状态

10.14.4 复制源线程状态

10.14.5 复制 I/O（接收器）线程状态

10.14.6 复制 SQL 线程状态

10.14.7 复制连接线程状态

10.14.8 NDB 集群线程状态

10.14.9 事件调度器线程状态

本章介绍了如何优化 MySQL 性能并提供了示例。优化涉及在多个级别配置、调整和衡量性能。根据您的工作角色（开发人员、数据库管理员或两者兼而有之），您可能会在单个 SQL 语句、整个应用程序、单个数据库服务器或多个网络化数据库服务器的级别进行优化。有时您可以积极主动地提前规划性能，而其他时候您可能需要在出现问题后排查配置或代码问题。优化 CPU 和内存使用也可以提高可伸缩性，使数据库能够处理更多负载而不会变慢。

10.1 优化概述

原文：dev.mysql.com/doc/refman/8.0/en/optimize-overview.html

数据库性能取决于数据库级别的几个因素，例如表、查询和配置设置。这些软件构造在硬件级别导致 CPU 和 I/O 操作，您必须尽量减少并尽可能高效地执行。在处理数据库性能时，您首先学习软件方面的高级规则和指南，并使用挂钟时间来衡量性能。随着您成为专家，您将了解更多内部发生的事情，并开始测量诸如 CPU 周期和 I/O 操作之类的内容。

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

• 在数据库级别进行优化

• 在硬件级别进行优化

• 平衡可移植性和性能

在数据库级别进行优化

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

• 表是否结构良好？特别是，列是否具有正确的数据类型，每个表是否具有适合工作类型的适当列？例如，频繁更新的应用程序通常有许多列较少的表，而分析大量数据的应用程序通常有少量列较多的表。

• 是否有正确的索引以使查询高效？

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

  注意

  InnoDB 是新表的默认存储引擎。实际上，高级的 InnoDB 性能特性意味着 InnoDB 表通常比简单的 MyISAM 表性能更好，特别是对于繁忙的数据库。

• 每个表是否使用适当的行格式？此选择还取决于表使用的存储引擎。特别是，压缩表使用更少的磁盘空间，因此需要更少的磁盘 I/O 来读取和写入数据。压缩适用于所有类型的工作负载，包括 InnoDB 表和只读 MyISAM 表。

• 应用程序是否使用适当的锁定策略？例如，尽可能允许共享访问，以便数据库操作可以并发运行，并在适当时请求独占访问，以便关键操作获得最高优先级。再次强调，存储引擎的选择非常重要。InnoDB存储引擎处理大部分锁定问题而无需你的参与，从而实现数据库更好的并发性，并减少对代码的实验和调整量。

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

在硬件级别进行优化

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

• 磁盘寻道。磁盘找到一段数据需要时间。现代磁盘，这个平均时间通常低于 10ms，因此理论上我们可以每秒做大约 100 次寻道。这个时间随着新磁盘的推出而缓慢改善，对于单个表来说很难优化。优化寻道时间的方法是将数据分布到多个磁盘上。

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

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

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

平衡便携性和性能

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

10.2 优化 SQL 语句

原文：dev.mysql.com/doc/refman/8.0/en/statement-optimization.html

10.2.1 优化 SELECT 语句

10.2.2 优化子查询、派生表、视图引用和公共表达式

10.2.3 优化 INFORMATION_SCHEMA 查询

10.2.4 优化 Performance Schema 查询

10.2.5 优化数据更改语句

10.2.6 优化数据库权限

10.2.7 其他优化技巧

数据库应用程序的核心逻辑是通过 SQL 语句执行的，无论是直接通过解释器发出还是通过 API 在后台提交。本节中的调优指南有助于加快各种 MySQL 应用程序的速度。这些指南涵盖了读取和写入数据的 SQL 操作，一般 SQL 操作的后台开销，以及在特定场景中使用的操作，如数据库监控。

10.2.1 优化 SELECT 语句

原文：dev.mysql.com/doc/refman/8.0/en/select-optimization.html

10.2.1.1 WHERE 子句优化

10.2.1.2 范围优化

10.2.1.3 索引合并优化

10.2.1.4 哈希连接优化

10.2.1.5 引擎条件下推优化

10.2.1.6 索引条件下推优化

10.2.1.7 嵌套循环连接算法

10.2.1.8 嵌套连接优化

10.2.1.9 外连接优化

10.2.1.10 外连接简化

10.2.1.11 多范围读取优化

10.2.1.12 块嵌套循环和批量键访问连接

10.2.1.13 条件过滤

10.2.1.14 常量折叠优化

10.2.1.15 IS NULL 优化

10.2.1.16 ORDER BY 优化

10.2.1.17 GROUP BY 优化

10.2.1.18 DISTINCT 优化

10.2.1.19 LIMIT 查询优化

10.2.1.20 函数调用优化

10.2.1.21 窗口函数优化

10.2.1.22 行构造表达式优化

10.2.1.23 避免全表扫描

查询以SELECT语句的形式在数据库中执行所有查找操作。调优这些语句是首要任务，无论是为了实现动态网页的亚秒响应时间，还是为了缩短生成大型隔夜报告的时间。

除了SELECT语句外，对查询的调优技术也适用于诸如CREATE TABLE...AS SELECT、INSERT INTO...SELECT以及DELETE语句中的WHERE子句。这些语句有额外的性能考虑，因为它们将写操作与面向读取的查询操作结合在一起。

NDB Cluster 支持连接下推优化，其中符合条件的连接完整地发送到 NDB Cluster 数据节点，可以在这些节点之间分发并并行执行。有关此优化的更多信息，请参阅 Conditions for NDB pushdown joins。

优化查询的主要考虑因素包括：

• 要使慢SELECT ... WHERE查询更快，首先要检查的是是否可以添加索引。在WHERE子句中设置索引，以加快评估、过滤和最终检索结果。为了避免浪费磁盘空间，构建一小组索引，可以加速应用程序中使用的许多相关查询。

  索引对于引用不同表的查询特别重要，使用诸如连接和外键等功能。您可以使用EXPLAIN语句确定哪些索引用于SELECT。请参阅 Section 10.3.1，“MySQL 如何使用索引”和 Section 10.8.1，“使用 EXPLAIN 优化查询”。

• 隔离和调整查询的任何部分，例如需要大量时间的函数调用。根据查询的结构，函数可能会为结果集中的每一行调用一次，甚至为表中的每一行调用一次，从而极大地放大任何低效性。

• 尽量减少查询中的全表扫描次数，特别是对于大表。

• 使用ANALYZE TABLE语句定期更新表统计信息，以便优化器具有构建高效执行计划所需的信息。

• 了解针对每个表的存储引擎特定的调整技术、索引技术和配置参数。InnoDB和MyISAM都有一套指导原则，用于启用和维持查询的高性能。有关详细信息，请参阅 Section 10.5.6，“优化 InnoDB 查询”和 Section 10.6.1，“优化 MyISAM 查询”。

• 您可以使用 Section 10.5.3，“优化 InnoDB 只读事务”中的技术，为InnoDB表优化单个查询事务。

• 避免以使其难以理解的方式转换查询，特别是如果优化器自动执行了一些相同的转换。

• 如果性能问题不容易通过基本准则之一解决，请通过阅读EXPLAIN计划的内部细节并调整您的索引、WHERE子句、连接子句等来调查特定查询。 （当您达到一定的专业水平时，阅读EXPLAIN计划可能是每个查询的第一步。）

• 调整 MySQL 用于缓存的内存区域的大小和属性。通过有效利用InnoDB 缓冲池、MyISAM 关键字缓存和 MySQL 查询缓存，重复查询会更快运行，因为第二次及以后的结果是从内存中检索的。

• 即使使用缓存内存区域运行快速查询，您仍然可以进一步优化，使其需要更少的缓存内存，从而使您的应用程序更具可扩展性。可扩展性意味着您的应用程序可以处理更多同时用户、更大请求等，而不会出现性能大幅下降。

• 处理锁定问题，其中您的查询速度可能会受到其他会话同时访问表的影响。

原文：dev.mysql.com/doc/refman/8.0/en/where-optimization.html

10.2.1.1 WHERE 子句优化

本节讨论了用于处理WHERE子句的优化。示例使用SELECT语句，但相同的优化也适用于DELETE和UPDATE语句中的WHERE子句。

注意

由于 MySQL 优化器的工作正在进行中，MySQL 执行的并非所有优化都在此处记录。

您可能会尝试重写查询以加快算术运算速度，但会牺牲可读性。由于 MySQL 自动执行类似的优化，您通常可以避免这项工作，并将查询保留在更易理解和可维护的形式中。MySQL 执行的一些优化如下：

• 删除不必要的括号：

   ((a AND b) AND c OR (((a AND b) AND (c AND d))))
  -> (a AND b AND c) OR (a AND b AND c AND d)
  

• 常量折叠：

   (a<b AND b=c) AND a=5
  -> b>5 AND b=c AND a=5
  

• 常量条件移除：

   (b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
  -> b=5 OR b=6
  

  在 MySQL 8.0.14 及更高版本中，这是在准备阶段而不是在优化阶段进行的，这有助于简化连接。有关更多信息和示例，请参见 Section 10.2.1.9, “Outer Join Optimization”。

• 用于索引的常量表达式仅评估一次。

• 从 MySQL 8.0.16 开始，将检查和折叠或删除数值类型列与常量值的比较，以处理无效或超出范围的值：

  # CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);
    SELECT * FROM t WHERE c ≪ 256;
  -≫ SELECT * FROM t WHERE 1;
  

  更多信息请参见 Section 10.2.1.14, “Constant-Folding Optimization”。

• 在没有WHERE的单个表上的COUNT(*)直接从MyISAM和MEMORY表的表信息中检索。当与仅一个表一起使用时，对于任何NOT NULL表达式也会执行此操作。

• 早期检测无效常量表达式。MySQL 快速检测到一些SELECT语句是不可能的，并且不返回任何行。

• 如果您不使用GROUP BY或聚合函数（COUNT(), MIN()等），HAVING将与WHERE合并。

• 对于连接中的每个表，构建一个更简单的WHERE以获得快速的表WHERE评估，并尽快跳过行。

• 在查询中的任何其他表之前首先读取所有常量表。常量表包括以下内容：

  • 空表或具有一行的表。

  • 在PRIMARY KEY或UNIQUE索引上使用WHERE子句的表，其中所有索引部分与常量表达式进行比较并定义为NOT NULL。

  所有以下表都被用作常量表：

  SELECT * FROM t WHERE *primary_key*=1;
  SELECT * FROM t1,t2
    WHERE t1.*primary_key*=1 AND t2.*primary_key*=t1.id;
  

• 通过尝试所有可能性来找到连接表的最佳连接组合。如果ORDER BY和GROUP BY子句中的所有列来自同一张表，那么在连接时首选该表。

• 如果存在ORDER BY子句和不同的GROUP BY子句，或者如果ORDER BY或GROUP BY包含来自连接队列中第一张表以外的表的列，那么会创建一个临时表。

• 如果使用SQL_SMALL_RESULT修饰符，MySQL 会使用内存临时表。

• 每个表索引都会被查询，除非优化器认为使用表扫描更有效，否则会使用最佳索引。曾经，基于最佳索引是否跨越表的 30%以上而使用扫描，但现在不再根据固定百分比来决定使用索引还是扫描。优化器现在更加复杂，根据诸如表大小、行数和 I/O 块大小等额外因素来估计。

• 在某些情况下，MySQL 可以从索引中读取行，甚至无需查阅数据文件。如果从索引中使用的所有列都是数字型的，则仅使用索引树来解析查询。

• 在输出每一行之前，不符合HAVING子句的行会被跳过。

一些查询的示例非常快速：

SELECT COUNT(*) FROM *tbl_name*;

SELECT MIN(*key_part1*),MAX(*key_part1*) FROM *tbl_name*;

SELECT MAX(*key_part2*) FROM *tbl_name*
  WHERE *key_part1*=*constant*;

SELECT ... FROM *tbl_name*
  ORDER BY *key_part1*,*key_part2*,... LIMIT 10;

SELECT ... FROM *tbl_name*
  ORDER BY *key_part1* DESC, *key_part2* DESC, ... LIMIT 10;

MySQL 仅使用索引树解析以下查询，假设索引列是数字型的：

SELECT *key_part1*,*key_part2* FROM *tbl_name* WHERE *key_part1*=*val*;

SELECT COUNT(*) FROM *tbl_name*
  WHERE *key_part1*=*val1* AND *key_part2*=*val2*;

SELECT MAX(*key_part2*) FROM *tbl_name* GROUP BY *key_part1*;

以下查询使用索引检索按排序顺序排列的行，而无需单独的排序过程：

SELECT ... FROM *tbl_name*
  ORDER BY *key_part1*,*key_part2*,... ;

SELECT ... FROM *tbl_name*
  ORDER BY *key_part1* DESC, *key_part2* DESC, ... ;

原文：dev.mysql.com/doc/refman/8.0/en/range-optimization.html

10.2.1.2 范围优化

range 访问方法使用单个索引检索包含在一个或多个索引值区间内的表行子集。它可用于单部分或多部分索引。以下部分描述了优化器在何种条件下使用范围访问。

• 单部分索引的范围访问方法

• 多部分索引的范围访问方法

• 多值比较的等值范围优化

• 跳过扫描范围访问方法

• 行构造表达式的范围优化

• 限制范围优化的内存使用

单部分索引的范围访问方法

对于单部分索引，索引值区间可以通过WHERE子句中对应的条件方便地表示，表示为范围条件而不是“区间”。

单部分索引的范围条件定义如下：

• 对于BTREE和HASH索引，当使用=、<=>、IN()、IS NULL或IS NOT NULL运算符进行键部分与常量值的比较时，为范围条件。

• 此外，对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或<>运算符进行键部分与常量值的比较，或者对于LIKE比较，如果LIKE的参数是不以通配符字符开头的常量字符串，则为范围条件。

• 对于所有索引类型，多个范围条件与OR或AND组合形成一个范围条件。

在前述描述中，“常量值”指以下之一：

• 来自查询字符串的一个常量

• 来自相同连接的const或system表的列

• 一个无关子查询的结果

• 由前述类型的子表达式完全组成的任何表达式

以下是一些在WHERE子句中具有范围条件的查询示例：

SELECT * FROM t1
  WHERE *key_col* > 1
  AND *key_col* < 10;

SELECT * FROM t1
  WHERE *key_col* = 1
  OR *key_col* IN (15,18,20);

SELECT * FROM t1
  WHERE *key_col* LIKE 'ab%'
  OR *key_col* BETWEEN 'bar' AND 'foo';

优化器常量传播阶段可能会将一些非常量值转换为常量。

MySQL 尝试从WHERE子句中提取每个可能索引的范围条件。在提取过程中，无法用于构建范围条件的条件被丢弃，产生重叠范围的条件被合并，产生空范围的条件被移除。

考虑以下语句，其中key1是一个索引列，而nonkey不是索引列：

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');

对key1的提取过程如下：

1. 从原始WHERE子句开始：

   (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
   (key1 < 'bar' AND nonkey = 4) OR
   (key1 < 'uux' AND key1 > 'z')
   

2. 移除nonkey = 4和key1 LIKE '%b'，因为它们不能用于范围扫描。正确的做法是用TRUE替换它们，这样在进行范围扫描时不会错过任何匹配的行。将它们替换为TRUE得到：

   (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
   (key1 < 'bar' AND TRUE) OR
   (key1 < 'uux' AND key1 > 'z')
   

3. 合并始终为真或假的条件：

   • (key1 LIKE 'abcde%' OR TRUE)始终为真

   • (key1 < 'uux' AND key1 > 'z')始终为假

   将这些条件替换为常量得到：

   (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
   

   移除不必要的TRUE和FALSE常量得到：

   (key1 < 'abc') OR (key1 < 'bar')
   

4. 将重叠的区间合并为一个得到用于范围扫描的最终条件：

   (key1 < 'bar')
   

一般来说（并且如前面的示例所示），用于范围扫描的条件比WHERE子句要宽松。MySQL 执行额外检查以过滤出满足范围条件但不满足完整WHERE子句的行。

范围条件提取算法可以处理任意深度的嵌套AND/OR结构，并且其输出不依赖于条件在WHERE子句中出现的顺序。

MySQL 不支持合并多个范围用于空间索引的range访问方法。为了解决这个限制，可以使用具有相同SELECT语句的UNION，只是将每个空间谓词放在不同的SELECT中。

用于多部分索引的范围访问方法

多部分索引上的范围条件是单部分索引的范围条件的扩展。多部分索引上的范围条件将索引行限制在一个或多个键元组区间内。键元组区间是在索引上使用的键元组集合上定义的，使用索引的排序。

例如，考虑一个定义为key1(*key_part1*, *key_part2*, *key_part3*)的多部分索引，以及按关键顺序列出的以下一组关键元组：

*key_part1*  *key_part2*  *key_part3*
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

条件*key_part1* = 1定义了这个区间：

(1,-inf,-inf) <= (*key_part1*,*key_part2*,*key_part3*) < (1,+inf,+inf)

该区间涵盖了前述数据集中的第 4、5 和 6 个元组，并可被范围访问方法使用。

相比之下，条件*key_part3* = 'abc'并未定义单个区间，因此无法被范围访问方法使用。

以下描述更详细地说明了多部分索引的范围条件如何工作。

• 对于HASH索引，每个包含相同值的区间都可以使用。这意味着区间只能针对以下形式的条件生成：

   *key_part1* *cmp* *const1*
  AND *key_part2* *cmp* *const2*
  AND ...
  AND *key_partN* *cmp* *constN*;
  

  在这里，const1、const2、…是常量，cmp是=、<=>或IS NULL比较运算符之一，条件涵盖了所有索引部分。（也就是说，有N个条件，每个条件对应一个N部分索引的部分。）例如，以下是三部分HASH索引的范围条件：

  *key_part1* = 1 AND *key_part2* IS NULL AND *key_part3* = 'foo'
  

  有关什么被视为常量的定义，请参阅单部分索引的范围访问方法。

• 对于BTREE索引，一个区间可能适用于与AND组合的条件，其中每个条件将一个关键部分与常量值使用=、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN或LIKE '*pattern*'进行比较（其中'*pattern*'不以通配符开头）。只要能够确定包含所有符合条件的行的单个关键元组（或者如果使用<>或!=则为两个区间），就可以使用区间。

  优化器尝试使用额外的键部分来确定区间，只要比较运算符是=、<=>或IS NULL。如果运算符是>、<、>=、<=、!=、<>、BETWEEN或LIKE，优化器会使用它，但不考虑更多的键部分。对于以下表达式，优化器从第一个比较中使用=。它还从第二个比较中使用>=，但不考虑更多的键部分，并且不使用第三个比较来构建区间：

  *key_part1* = 'foo' AND *key_part2* >= 10 AND *key_part3* > 10
  

  单个区间是：

  ('foo',10,-inf) < (*key_part1*,*key_part2*,*key_part3*) < ('foo',+inf,+inf)
  

  创建的区间可能包含比初始条件更多的行。例如，前面的区间包括值('foo', 11, 0)，这不符合原始条件。

• 如果涵盖区间内的行集的条件与OR组合，它们形成一个涵盖其区间并集内的行集的条件。如果条件与AND组合，它们形成一个涵盖其区间交集内的行集的条件。例如，对于两部分索引上的此条件：

  (*key_part1* = 1 AND *key_part2* < 2) OR (*key_part1* > 5)
  

  区间是：

  (1,-inf) < (*key_part1*,*key_part2*) < (1,2)
  (5,-inf) < (*key_part1*,*key_part2*)
  

  在此示例中，第一行的区间使用一个键部分作为左边界，两个键部分作为右边界。第二行的区间仅使用一个键部分。EXPLAIN输出中的key_len列指示使用的键前缀的最大长度。

  在某些情况下，key_len可能表明使用了一个键部分，但这可能不是您期望的。假设key_part1和key_part2可以是NULL。然后，key_len列显示以下条件的两个键部分长度：

  *key_part1* >= 1 AND *key_part2* < 2
  

  但实际上，条件被转换为这样：

  *key_part1* >= 1 AND *key_part2* IS NOT NULL
  

有关如何对单部分索引上的范围条件执行优化以组合或消除区间的描述，请参见单部分索引的范围访问方法。类似的步骤也适用于多部分索引上的范围条件。

多值比较的等值范围优化

考虑以下表达式，其中col_name是一个索引列：

*col_name* IN(*val1*, ..., *valN*)
*col_name* = *val1* OR ... OR *col_name* = *valN*

如果 col_name 等于多个值中的任何一个，则每个表达式为真。这些比较是相等范围比较（其中“范围”是单个值）。优化器估计读取符合相等范围比较条件的行的成本如下：

• 如果 col_name 上有唯一索引，则每个范围的行估计值为 1，因为最多只有一行可以具有给定值。

• 否则，col_name 上的任何索引都是非唯一的，优化器可以通过对索引或索引统计数据的深入来估计每个范围的行数。

使用索引深入，优化器在每个范围的两端进行深入，并将范围内的行数作为估计值。例如，表达式 *col_name* IN (10, 20, 30) 有三个相等范围，优化器每个范围进行两次深入以生成行估计值。每对深入提供给定值的行数的估计值。

索引深入提供准确的行估计值，但随着表达式中比较值的增加，优化器生成行估计值的时间也会增加。使用索引统计数据比索引深入的准确性低，但允许更快地为大型值列表进行行估计。

eq_range_index_dive_limit 系统变量允许您配置优化器在何时从一种行估计策略切换到另一种。要允许使用索引深入进行多达 N 个相等范围的比较，请将 eq_range_index_dive_limit 设置为 N + 1. 要禁用统计数据的使用并始终使用索引深入，无论 N 如何，请将 eq_range_index_dive_limit 设置为 0。

要更新表索引统计数据以获得最佳估计值，请使用 ANALYZE TABLE。

在 MySQL 8.0 之前，除了使用 eq_range_index_dive_limit 系统变量外，没有跳过使用索引深入来估计索引有用性的方法。在 MySQL 8.0 中，对于满足以下所有条件的查询，可以跳过索引深入估计：

• 查询针对单个表，而不是多个表的连接。

• 存在单索引 FORCE INDEX 索引提示。这样做的想法是，如果强制使用索引，则执行对索引的深入所带来的额外开销没有任何好处。

• 索引是非唯一的，不是 FULLTEXT 索引。

• 没有子查询。

• 没有 DISTINCT、GROUP BY 或 ORDER BY 子句。

对于 EXPLAIN FOR CONNECTION，如果跳过索引深入，则输出如下更改：

• 对于传统输出，rows 和 filtered 值为 NULL。

• 对于 JSON 输出，rows_examined_per_scan和rows_produced_per_join不会出现，skip_index_dive_due_to_force为true，成本计算不准确。

没���FOR CONNECTION，当索引潜水被跳过时，EXPLAIN输出不会改变。

执行一个查询，其中索引潜水被跳过后，信息模式OPTIMIZER_TRACE表中的相应行包含一个index_dives_for_range_access值为skipped_due_to_force_index。

跳过扫描范围访问方法

考虑以下情景：

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

要执行这个查询，MySQL 可以选择一个索引扫描来获取所有行（索引包括所有要选择的列），然后应用WHERE子句中的f2 > 40条件来生成最终结果集。

范围扫描比完整索引扫描更有效，但在这种情况下无法使用，因为对第一个索引列f1没有条件。然而，从 MySQL 8.0.13 开始，优化器可以执行多个范围扫描，每个值f1一个，使用一种称为 Skip Scan 的方法，类似于 Loose Index Scan（参见 Section 10.2.1.17, “GROUP BY Optimization”）：

1. 在第一个索引部分f1（索引前缀）的不同值之间跳过。

2. 对剩余索引部分上的f2 > 40条件的每个不同前缀值执行子范围扫描。

对于之前显示的数据集，算法的操作如下：

1. 获取第一个关键部分的第一个不同值（f1 = 1）。

2. 基于第一个和第二个关键部分构建范围（f1 = 1 AND f2 > 40）。

3. 执行一个范围扫描。

4. 获取第一个关键部分的下一个不同值（f1 = 2）。

5. 基于第一个和第二个关键部分构建范围（f1 = 2 AND f2 > 40）。

6. 执行一个范围扫描。

使用这种策略可以减少访问的行数，因为 MySQL 跳过了不符合每个构建范围的行。这种 Skip Scan 访问方法适用于以下条件：

• 表 T 至少有一个复合索引，其关键部分形式为([A_1, ..., A_k,] B_1, ..., B_m, C [, D_1, ..., D_n])。关键部分 A 和 D 可能为空，但 B 和 C 必须非空。

• 查询仅引用一个表。

• 查询不使用GROUP BY或DISTINCT。

• 查询仅引用索引中的列。

• A_1, ..., A_k上的谓词必须是等式谓词，且它们必须是常量。这包括IN()运算符。

• 查询必须是一个连接查询；也就是说，是OR条件的AND：(*cond1*(*key_part1*) OR *cond2*(*key_part1*)) AND (*cond1*(*key_part2*) OR ...) AND ...

• 必须对 C 列有一个范围条件。

• 允许对 D 列的条件。D 列上的条件必须与 C 列上的范围条件一起。

在EXPLAIN输出中指示使用跳过扫描如下：

• 在Extra列中使用Using index for skip scan表示使用了宽松索引跳过扫描访问方法。

• 如果索引可以用于跳过扫描，索引应该在possible_keys列中可见。

在优化器跟踪输出中，使用"skip scan"元素指示使用跳过扫描：

"skip_scan_range": {
  "type": "skip_scan",
  "index": *index_used_for_skip_scan*,
  "key_parts_used_for_access": [*key_parts_used_for_access*],
  "range": [*range*]
}

您还可能会看到一个"best_skip_scan_summary"元素。如果跳过扫描被选择为最佳范围访问变体，则会写入"chosen_range_access_summary"。如果跳过扫描被选择为整体最佳访问方法，则会出现"best_access_path"元素。

使用跳过扫描取决于optimizer_switch系统变量的skip_scan标志的值。请参见 Section 10.9.2, “Switchable Optimizations”。默认情况下，此标志为on。要禁用它，请将skip_scan设置为off。

除了使用optimizer_switch系统变量来控制会话范围内优化器使用跳过扫描外，MySQL 还支持优化器提示以影响每个语句的优化器。请参见 Section 10.9.3, “Optimizer Hints”。

行构造器表达式的范围优化

优化器能够将范围扫描访问方法应用于此类查询：

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

以前，为了使用范围扫描，必须将查询编写为：

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );

为了使优化器使用范围扫描，查询必须满足以下条件：

• 仅使用IN()谓词，不使用NOT IN()。

• 在IN()谓词的左侧，行构造器仅包含列引用。

• 在IN()谓词的右侧，行构造器仅包含运行时常量，这些常量可以是文字或在执行期间绑定为常量的本地列引用。

• 在IN()谓词的右侧，存在多个行构造器。

有关优化器和行构造器的更多信息，请参见 Section 10.2.1.22, “Row Constructor Expression Optimization”

限制范围优化的内存使用

要控制范围优化器可用的内存，请使用range_optimizer_max_mem_size系统变量：

• 值为 0 表示“没有限制”。

• 当值大于 0 时，优化器会跟踪考虑范围访问方法时消耗的内存。如果即将超过指定限制，范围访问方法将被放弃，而考虑其他方法，包括全表扫描。这可能不太理想。如果发生这种情况，将出现以下警告（其中N是当前range_optimizer_max_mem_size值）：

  Warning    3170    Memory capacity of *N* bytes for
                     'range_optimizer_max_mem_size' exceeded. Range
                     optimization was not done for this query.
  

• 对于UPDATE和DELETE语句，如果优化器回退到全表扫描，并且启用了sql_safe_updates系统变量，则会发生错误而不是警告，因为实际上没有使用键来确定要修改哪些行。有关更多信息，请参见使用安全更新模式（--safe-updates）")。

对于超出可用范围优化内存并且优化器回退到不太理想计划的单个查询，增加range_optimizer_max_mem_size值可能会提高性能。

要估算处理范围表达式所需的内存量，请使用以下准则：

• 对于像下面这样的简单查询，其中有一个候选键用于范围访问方法，每个与OR结合的谓词大约使用 230 字节：

  SELECT COUNT(*) FROM t
  WHERE a=1 OR a=2 OR a=3 OR .. . a=*N*;
  

• 类似地，对于像下面这样的查询，每个与AND结合的谓词大约使用 125 字节：

  SELECT COUNT(*) FROM t
  WHERE a=1 AND b=1 AND c=1 ... *N*;
  

• 对于带有IN()谓词的查询：

  SELECT COUNT(*) FROM t
  WHERE a IN (1,2, ..., *M*) AND b IN (1,2, ..., *N*);
  

  在IN()列表中的每个文字值都算作与OR结合的谓词。如果有两个IN()列表，则与OR结合的谓词数量是每个列表中文字值数量的乘积。因此，在前述情况下，与OR结合的谓词数量为M × N。

原文：dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html

10.2.1.3 索引合并优化

索引合并访问方法通过多个range扫描检索行，并将它们的结果合并为一个。此访问方法仅合并来自单个表的索引扫描，而不是跨多个表的扫描。合并可以生成底层扫描的并集、交集或并集的交集。

可能使用索引合并的示例查询：

SELECT * FROM *tbl_name* WHERE *key1* = 10 OR *key2* = 20;

SELECT * FROM *tbl_name*
  WHERE (*key1* = 10 OR *key2* = 20) AND *non_key* = 30;

SELECT * FROM t1, t2
  WHERE (t1.*key1* IN (1,2) OR t1.*key2* LIKE '*value*%')
  AND t2.*key1* = t1.*some_col*;

SELECT * FROM t1, t2
  WHERE t1.*key1* = 1
  AND (t2.*key1* = t1.*some_col* OR t2.*key2* = t1.*some_col2*);

注意

索引合并优化算法具有以下已知限制：

• 如果您的查询具有复杂的WHERE子句，带有深层次的AND/OR嵌套，并且 MySQL 没有选择最佳计划，请尝试使用以下标识变换来分发术语：

  (*x* AND *y*) OR *z* => (*x* OR *z*) AND (*y* OR *z*)
  (*x* OR *y*) AND *z* => (*x* AND *z*) OR (*y* AND *z*)
  

• 索引合并不适用于全文索引。

在EXPLAIN输出中，索引合并方法显示为type列中的index_merge。在这种情况下，key列包含使用的索引列表，key_len包含这些索引的最长键部分的列表。

索引合并访问方法有几种算法，这些算法显示在EXPLAIN输出的Extra字段中：

• 使用 intersect(...)

• 使用 union(...)

• 使用 sort_union(...)

以下部分更详细地描述了这些算法。优化器根据各种可用选项的成本估算，在不同可能的索引合并算法和其他访问方法之间进行选择。

• 索引合并交集访问算法

• 索引合并并集访问算法

• 索引合并排序-并集访问算法

• 影响索引合并优化

索引合并交集访问算法

当WHERE子句转换为与AND组合的不同键上的几个范围条件时，并且每个条件是以下之一时，可以应用此访问算法：

• 这种形式的N部分表达式，其中索引恰好有N部分（即，所有索引部分都被覆盖）：

  *key_part1* = *const1* AND *key_part2* = *const2* ... AND *key_partN* = *constN*
  

• InnoDB表的主键上的任何范围条件。

示例：

SELECT * FROM *innodb_table*
  WHERE *primary_key* < 10 AND *key_col1* = 20;

SELECT * FROM *tbl_name*
  WHERE *key1_part1* = 1 AND *key1_part2* = 2 AND *key2* = 2;

索引合并交集算法对所有使用的索引执行同时扫描，并生成从合并索引扫描接收到的行序列的交集。

如果查询中使用的所有列都由使用的索引覆盖，则不会检索完整的表行（在这种情况下，EXPLAIN输出在Extra字段中包含Using index）。以下是这种查询的示例：

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;

如果使用的索引未覆盖查询中使用的所有列，则仅在满足所有使用键的范围条件时才检索完整行。

如果合并条件之一是InnoDB表的主键条件，则不用于检索行，而是用于过滤使用其他条件检索的行。

索引合并联合访问算法

该算法的标准与索引合并交集算法的标准类似。当表的WHERE子句转换为与OR组合的不同键上的多个范围条件，并且每个条件是以下条件之一时，该算法适用：

• 这种形式的N部分表达式，其中索引恰好有N部分（即所有索引部分都被覆盖）：

  *key_part1* = *const1* OR *key_part2* = *const2* ... OR *key_partN* = *constN*
  

• 任何InnoDB表的主键上的范围条件。

• 适用于索引合并交集算法的条件。

例子：

SELECT * FROM t1
  WHERE *key1* = 1 OR *key2* = 2 OR *key3* = 3;

SELECT * FROM *innodb_table*
  WHERE (*key1* = 1 AND *key2* = 2)
     OR (*key3* = 'foo' AND *key4* = 'bar') AND *key5* = 5;

索引合并排序联合访问算法

当WHERE子句转换为由OR组合的多个范围条件时，此访问算法适用，但索引合并联合算法不适用。

例子：

SELECT * FROM *tbl_name*
  WHERE *key_col1* < 10 OR *key_col2* < 20;

SELECT * FROM *tbl_name*
  WHERE (*key_col1* > 10 OR *key_col2* = 20) AND *nonkey_col* = 30;

排序联合算法和联合算法之间的区别在于排序联合算法必须首先获取所有行的行 ID 并对其进行排序，然后才能返回任何行。

影响索引合并优化

使用索引合并取决于index_merge、index_merge_intersection、index_merge_union和index_merge_sort_union标志的optimizer_switch系统变量的值。请参阅第 10.9.2 节，“可切换优化”。默认情况下，所有这些标志都是on。要仅启用某些算法，请将index_merge设置为off，并仅启用应允许的其他算法。

除了使用optimizer_switch系统变量来全局控制 MySQL 对索引合并算法的使用之外，MySQL 还支持优化器提示以影响每个语句的优化器。请参阅第 10.9.3 节，“优化器提示”。

原文：dev.mysql.com/doc/refman/8.0/en/hash-joins.html

10.2.1.4 哈希连接优化

默认情况下，MySQL（8.0.18 及更高版本）尽可能使用哈希连接。可以使用BNL和NO_BNL优化器提示之一，或者将block_nested_loop=on或block_nested_loop=off作为 optimizer_switch 服务器系统变量设置的一部分来控制是否使用哈希连接。

注意

MySQL 8.0.18 支持在optimizer_switch中设置hash_join标志，以及优化器提示HASH_JOIN和NO_HASH_JOIN。在 MySQL 8.0.19 及更高版本中，这些都不再起作用。

从 MySQL 8.0.18 开始，MySQL 为每个连接都具有等值连接条件的查询使用哈希连接，并且没有可以应用于任何连接条件的索引，就像这个例子一样：

SELECT *
    FROM t1
    JOIN t2
        ON t1.c1=t2.c1;

当存在一个或多个可以用于单表谓词的索引时，也可以使用哈希连接。

哈希连接通常比块嵌套循环算法更快，并且在这种情况下应该使用哈希连接，而不是在以前版本的 MySQL 中使用的块嵌套循环算法（参见块嵌套循环连接算法）。从 MySQL 8.0.20 开始，不再支持块嵌套循环，服务器在以前可能使用块嵌套循环的地方使用哈希连接。

在刚刚显示的示例和本节中的其余示例中，我们假设使用以下语句创建了三个表t1、t2和t3：

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);

你可以通过使用EXPLAIN来查看哈希连接的使用情况，就像这样：

mysql> EXPLAIN
 -> SELECT * FROM t1
 ->     JOIN t2 ON t1.c1=t2.c1\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using join buffer (hash join)

(在 MySQL 8.0.20 之前，有必要包含FORMAT=TREE选项，以查看给定连接是否使用了哈希连接。)

EXPLAIN ANALYZE还显示了使用的哈希连接的信息。

哈希连接也用于涉及多个连接的查询，只要每对表的至少一个连接条件是等值连接，就像这里显示的查询一样：

SELECT * FROM t1
    JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
    JOIN t3 ON (t2.c1 = t3.c1);

在像刚刚显示的这种情况下，使用内连接时，任何不是等值连接的额外条件在连接执行后作为过滤器应用。（对于外连接，如左连接、半连接和反连接，它们作为连接的一部分打印。）这可以在EXPLAIN的输出中看到：

mysql> EXPLAIN FORMAT=TREE
 -> SELECT *
 ->     FROM t1
 ->     JOIN t2
 ->         ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
 ->     JOIN t3
 ->         ON (t2.c1 = t3.c1)\G
*************************** 1\. row ***************************
EXPLAIN: -> Inner hash join (t3.c1 = t1.c1)  (cost=1.05 rows=1)
 -> Table scan on t3  (cost=0.35 rows=1)
 -> Hash
 -> Filter: (t1.c2 < t2.c2)  (cost=0.70 rows=1)
 -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
 -> Table scan on t2  (cost=0.35 rows=1)
 -> Hash
 -> Table scan on t1  (cost=0.35 rows=1)

正如刚刚展示的输出所示，多个哈希连接可以用于具有多个等值连接条件的连接。

在 MySQL 8.0.20 之前，如果任何一对连接的表没有至少一个等值连接条件，则无法使用哈希连接，并且会使用较慢的块嵌套循环算法。在 MySQL 8.0.20 及更高版本中，在这种情况下使用哈希连接，如下所示：

mysql> EXPLAIN FORMAT=TREE
 -> SELECT * FROM t1
 ->     JOIN t2 ON (t1.c1 = t2.c1)
 ->     JOIN t3 ON (t2.c1 < t3.c1)\G
*************************** 1\. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t3.c1)  (cost=1.05 rows=1)
 -> Inner hash join (no condition)  (cost=1.05 rows=1)
 -> Table scan on t3  (cost=0.35 rows=1)
 -> Hash
 -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
 -> Table scan on t2  (cost=0.35 rows=1)
 -> Hash
 -> Table scan on t1  (cost=0.35 rows=1)

(本节稍后提供更多示例。)

当未指定连接条件时，哈希连接也适用于笛卡尔积，如下所示：

mysql> EXPLAIN FORMAT=TREE
 -> SELECT *
 ->     FROM t1
 ->     JOIN t2
 ->     WHERE t1.c2 > 50\G
*************************** 1\. row ***************************
EXPLAIN: -> Inner hash join  (cost=0.70 rows=1)
 -> Table scan on t2  (cost=0.35 rows=1)
 -> Hash
 -> Filter: (t1.c2 > 50)  (cost=0.35 rows=1)
 -> Table scan on t1  (cost=0.35 rows=1)

在 MySQL 8.0.20 及更高版本中，连接不再需要至少包含一个等值连接条件才能使用哈希连接。这意味着可以使用哈希连接优化的查询类���包括以下列表中的查询（附有示例）：

• 内部非等值连接：

  mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\G
  *************************** 1\. row ***************************
  EXPLAIN: -> Filter: (t1.c1 < t2.c1)  (cost=4.70 rows=12)
   -> Inner hash join (no condition)  (cost=4.70 rows=12)
   -> Table scan on t2  (cost=0.08 rows=6)
   -> Hash
   -> Table scan on t1  (cost=0.85 rows=6)
  

• 半连接：

  mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 
   ->     WHERE t1.c1 IN (SELECT t2.c2 FROM t2)\G
  *************************** 1\. row ***************************
  EXPLAIN: -> Hash semijoin (t2.c2 = t1.c1)  (cost=0.70 rows=1)
   -> Table scan on t1  (cost=0.35 rows=1)
   -> Hash
   -> Table scan on t2  (cost=0.35 rows=1)
  

• 反连接：

  mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2 
   ->     WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c1)\G
  *************************** 1\. row ***************************
  EXPLAIN: -> Hash antijoin (t1.c1 = t2.c1)  (cost=0.70 rows=1)
   -> Table scan on t2  (cost=0.35 rows=1)
   -> Hash
   -> Table scan on t1  (cost=0.35 rows=1)
  
  1 row in set, 1 warning (0.00 sec)
  
  mysql> SHOW WARNINGS\G
  *************************** 1\. row ***************************
    Level: Note
     Code: 1276
  Message: Field or reference 't3.t2.c1' of SELECT #2 was resolved in SELECT #1
  

• 左外连接：

  mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1\G
  *************************** 1\. row ***************************
  EXPLAIN: -> Left hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
   -> Table scan on t1  (cost=0.35 rows=1)
   -> Hash
   -> Table scan on t2  (cost=0.35 rows=1)
  

• 右外连接（请注意，MySQL 将所有右外连接重写为左外连接）：

  mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1\G
  *************************** 1\. row ***************************
  EXPLAIN: -> Left hash join (t1.c1 = t2.c1)  (cost=0.70 rows=1)
   -> Table scan on t2  (cost=0.35 rows=1)
   -> Hash
   -> Table scan on t1  (cost=0.35 rows=1)
  

默认情况下，MySQL 8.0.18 及更高版本在可能的情况下始终使用哈希连接。可以使用BNL和NO_BNL优化器提示之一来控制是否使用哈希连接。

（MySQL 8.0.18 支持hash_join=on或hash_join=off作为optimizer_switch服务器系统变量设置的一部分，以及优化器提示HASH_JOIN或NO_HASH_JOIN。在 MySQL 8.0.19 及更高版本中，这些不再起作用。）

可以使用join_buffer_size系统变量来控制哈希连接的内存使用情况；哈希连接不能使用超过此数量的内存。当哈希连接所需的内存超过可用量时，MySQL 会通过在磁盘上使用文件来处理此问题。如果发生这种情况，您应该意识到，如果哈希连接无法适应内存并且创建的文件多于为open_files_limit设置的数量，则连接可能不会成功。为避免此类问题，请进行以下更改之一：

• 增加join_buffer_size，以使哈希连接不会溢出到磁盘。

• 增加open_files_limit。

从 MySQL 8.0.18 开始，为哈希连接分配增量式连接缓冲区；因此，您可以将join_buffer_size设置得更高，而不会使小查询分配大量 RAM，但外连接会分配整个缓冲区。在 MySQL 8.0.20 及更高版本中，哈希连接也用于外连接（包括反连接和半连接），因此这不再是一个问题。

原文：dev.mysql.com/doc/refman/8.0/en/engine-condition-pushdown-optimization.html

10.2.1.5 引擎条件下推优化

这种优化提高了非索引列与常量之间直接比较的效率。在这种情况下，条件被“下推”到存储引擎进行评估。这种优化只能由NDB存储引擎使用。

对于 NDB Cluster，这种优化可以消除在集群的数据节点和发出查询的 MySQL 服务器之间发送不匹配行的需要，并且可以加快使用的查询速度，速度提高了 5 到 10 倍，超过了可以但未使用条件下推的情况。

假设一个 NDB Cluster 表定义如下：

CREATE TABLE t1 (
    a INT,
    b INT,
    KEY(a)
) ENGINE=NDB;

引擎条件下推可以与如下所示的查询一起使用，其中包括非索引列与常量之间的比较：

SELECT a, b FROM t1 WHERE b = 10;

引擎条件下推的使用可以在EXPLAIN的输出中看到：

mysql> EXPLAIN SELECT a, b FROM t1 WHERE b = 10\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where with pushed condition

然而，引擎条件下推 不能 与以下查询一起使用：

SELECT a,b FROM t1 WHERE a = 10;

引擎条件下推在这里不适用，因为列 a 上存在索引。（索引访问方法更有效，因此会选择索引访问方法而不是条件下推。）

当索引列使用 > 或 < 运算符与常量进行比较时，也可以使用引擎条件下推：

mysql> EXPLAIN SELECT a, b FROM t1 WHERE a < 2\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 2
        Extra: Using where with pushed condition

引擎条件下推的其他支持比较包括以下内容：

• *column* [NOT] LIKE *pattern*

  pattern 必须是包含要匹配的模式的字符串文字；有关语法，请参见第 14.8.1 节，“字符串比较函数和运算符”。

• *column* IS [NOT] NULL

• *column* IN (*value_list*)

  value_list 中的每个项目必须是常量、文字值。

• *column* BETWEEN *constant1* AND *constant2*

  constant1 和 constant2 必须是常量、文字值。

在前述列表中的所有情况中，条件可以转换为一个或多个列与常量之间的直接比较形式。

引擎条件下推默认启用。要在服务器启动时禁用它，请将optimizer_switch系统变量的engine_condition_pushdown标志设置为 off。例如，在 my.cnf 文件中，使用以下行：

[mysqld]
optimizer_switch=engine_condition_pushdown=off

在运行时，像这样禁用条件下推：

SET optimizer_switch='engine_condition_pushdown=off';

限制。 引擎条件下推受以下限制：

• 引擎条件下推仅受支持NDB存储引擎。

• 在 NDB 8.0.18 之前，列只能与常量或计算为常量值的表达式进行比较。在 NDB 8.0.18 及更高版本中，只要它们具有完全相同的类型，包括相同的符号、长度、字符集、精度和比例，这些都适用，列就可以相互比较。

• 用于比较的列不能是任何BLOB或TEXT类型。这个排除范围还包括JSON、BIT和ENUM列。

• 与列进行比较的字符串值必须使用与列相同的排序规则。

• 不支持直接连接；尽可能将涉及多个表的条件分开推送。使用扩展的EXPLAIN输出来确定实际被推送的条件。参见 Section 10.8.3, “Extended EXPLAIN Output Format”。

以前，引擎条件推送仅限于引用来自正在被推送条件的相同表的列值的术语。从 NDB 8.0.16 开始，还可以从查询计划中较早的表中引用推送条件的列值。这减少了 SQL 节点在连接处理期间必须处理的行数。过滤也可以在 LDM 线程中并行执行，而不是在单个mysqld进程中。这有可能显着提高查询的性能。

从 NDB 8.0.20 开始，如果在相同连接嵌套中使用的任何表上没有无法推送的条件，或者在其上依赖的连接嵌套中没有无法推送的条件，则可以推送使用扫描的外连接。对于半连接也是如此，前提是所采用的优化策略是firstMatch（参见 Section 10.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”）。

在以下两种情况下，连接算法不能与引用先前表的列结合使用：

1. 当任何被引用的先前表在连接缓冲区中时。在这种情况下，从扫描过滤表中检索的每一行都与缓冲区中的每一行匹配。这意味着在生成扫描过滤时无法从单个特定行中获取列值。

2. 当列来自推送连接中的子操作时。这是因为在生成扫描过滤时，尚未检索到连接中祖先操作引用的行。

从 NDB 8.0.27 开始，可以将连接中祖先表的列下推，前提是它们符合先前列出的要求。以下是一个使用先前创建的表 t1 的查询示例：

mysql> EXPLAIN 
 ->   SELECT * FROM t1 AS x 
 ->   LEFT JOIN t1 AS y 
 ->   ON x.a=0 AND y.b>=3\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: x
   partitions: p0,p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
*************************** 2\. row ***************************
           id: 1
  select_type: SIMPLE
        table: y
   partitions: p0,p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where; Using pushed condition (`test`.`y`.`b` >= 3); Using join buffer (hash join) 2 rows in set, 2 warnings (0.00 sec)

原文：dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

10.2.1.6 索引条件推送优化

索引条件推送（ICP）是一种优化，用于 MySQL 使用索引从表中检索行的情况。没有 ICP，存储引擎遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，MySQL 服务器评估行的WHERE条件。启用 ICP 后，如果WHERE条件的部分可以仅通过索引列进行评估，MySQL 服务器将此部分WHERE条件向下推送到存储引擎。然后存储引擎通过使用索引条目评估推送的索引条件，仅当满足条件时才从表中读取行。ICP 可以减少存储引擎必须访问基表的次数，以及 MySQL 服务器必须访问存储引擎的次数。

索引条件推送优化的适用性取决于以下条件：

• 当需要访问完整表行时，ICP 用于range、ref、eq_ref和ref_or_null访问方法。

• ICP 可用于InnoDB和MyISAM表，包括分区的InnoDB和MyISAM表。

• 对于InnoDB表，ICP 仅用于辅助索引。ICP 的目标是减少完整行读取的次数，从而减少 I/O 操作。对于InnoDB聚簇索引，完整记录已经读入InnoDB缓冲区。在这种情况下使用 ICP 不会减少 I/O。

• ICP 不支持在虚拟生成列上创建的辅助索引。InnoDB支持虚拟生成列上的辅助索引。

• 不能向下推送涉及子查询的条件。

• 不能向涉及存储函数的条件推送。存储引擎无法调用存储函数。

• 触发条件无法向下推送。（有关触发条件的信息，请参见第 10.2.2.3 节，“使用 EXISTS 策略优化子查询”。）

• （MySQL 8.0.30 及更高版本：）条件无法向包含对系统变量引用的派生表推送。

要了解这种优化如何工作，首先考虑当未使用索引条件推送优化时索引扫描的进行方式：

1. 首先通过读取索引元组获取下一行，然后使用索引元组定位并读取完整的表行。

2. 测试适用于此表的WHERE条件部分。根据测试结果接受或拒绝行。

使用索引条件下推，扫描如下进行：

1. 获取下一行的索引元组（但不是完整的表行）。

2. 测试适用于此表且仅可以使用索引列进行检查的WHERE条件的部分。如果条件不满足，则继续到下一行的索引元组。

3. 如果条件满足，使用索引元组定位并读取完整的表行。

4. 测试适用于此表的WHERE条件的剩余部分。根据测试结果接受或拒绝行。

当使用索引条件下推时，EXPLAIN输出在Extra列中显示Using index condition。它不显示Using index，因为当必须读取完整的表行时，这不适用。

假设一个表包含有关人员及其地址的信息，并且该表具有定义为INDEX (zipcode, lastname, firstname)的索引。如果我们知道一个人的zipcode值，但不确定姓氏，我们可以这样搜索：

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

MySQL 可以使用索引扫描具有zipcode='95054'的人员。第二部分（lastname LIKE '%etrunia%'）无法用于限制必须扫描的行数，因此没有索引条件下推，此查询必须检索所有具有zipcode='95054'的人员的完整表行。

使用索引条件下推，MySQL 在读取完整的表行之前检查lastname LIKE '%etrunia%'部分。这避免了读取与zipcode条件匹配但不匹配lastname条件的索引元组对应的完整行。

索引条件下推默认启用。可以通过设置optimizer_switch系统变量来控制，设置index_condition_pushdown标志：

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

参见第 10.9.2 节，“可切换优化”。

原文：dev.mysql.com/doc/refman/8.0/en/nested-loop-joins.html

10.2.1.7 嵌套循环连接算法

MySQL 使用嵌套循环算法或其变体来执行表之间的连接。

• 嵌套循环连接算法

• 块嵌套循环连接算法

嵌套循环连接算法

简单的嵌套循环连接（NLJ）算法从第一个表中逐行读取行，将每行传递给处理连接中下一个表的嵌套循环。这个过程重复进行，直到所有要连接的表都处理完为止。

假设要使用以下连接类型执行三个表t1、t2和t3之间的连接：

Table   Join Type
t1      range
t2      ref
t3      ALL

如果使用简单的 NLJ 算法，则连接的处理方式如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

因为 NLJ 算法将行逐个从外部循环传递到内部循环，所以通常会多次读取在内部循环中处理的表。

块嵌套循环连接算法

块嵌套循环（BNL）连接算法使用缓冲区来减少内部循环中读取表的次数。例如，如果将 10 行读入缓冲区并将缓冲区传递给下一个内部循环，那么内部循环中读取的每一行都可以与缓冲区中的所有 10 行进行比较。这将使内部表的读取次数减少一个数量级。

在 MySQL 8.0.18 之前，当无法使用索引时，此算法用于等值连接；在 MySQL 8.0.18 及更高版本中，在这种情况下使用哈希连接优化。从 MySQL 8.0.20 开始，MySQL 不再使用块嵌套循环，并且在以前使用块嵌套循环的所有情况下都使用哈希连接。参见第 10.2.1.4 节“哈希连接优化”。

MySQL 连接缓冲具有以下特点：

• 当连接的类型为ALL或index（换句话说，无法使用任何可能的键，并且需要进行完全扫描，无论是数据行还是索引行），或者range时，可以使用连接缓冲。连接缓冲也适用于外连接，如第 10.2.1.12 节“块嵌套循环和批量键访问连接”中所述。

• 为第一个非常量表不分配连接缓冲区，即使它的类型为ALL或index。

• 仅将连接中感兴趣的列存储在其连接缓冲区中，而不是整行。

• join_buffer_size 系统变量确定用于处理查询的每个连接缓冲区的大小。

• 为每个可以缓冲的连接分配一个缓冲区，因此一个给定的查询可能会使用多个连接缓冲区。

• 在执行连接之前分配连接缓冲区，并在查询完成后释放。

对于之前描述的 NLJ 算法的示例连接（不使用缓冲），使用连接缓冲区进行连接如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}

if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}

如果 S 是连接缓冲区中每个存储的 t1, t2 组合的大小，C 是缓冲区中组合的数量，则表 t3 被扫描的次数为：

(*S* * *C*)/join_buffer_size + 1

当 join_buffer_size 的值增加时，t3 扫描的次数会减少，直到 join_buffer_size 足够大以容纳所有先前的行组合为止。在那一点上，通过增大它不会获得速度上的提升。

原文：dev.mysql.com/doc/refman/8.0/en/nested-join-optimization.html

10.2.1.8 嵌套连接优化

表达连接的语法允许嵌套连接。以下讨论涉及第 15.2.13.2 节，“JOIN 子句”中描述的连接语法。

与 SQL 标准相比，table_factor的语法有所扩展。后者仅接受table_reference，而不是在一对括号内列出它们。如果我们将table_reference项目列表中的每个逗号视为等同于内连接，则这是一种保守的扩展。例如：

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

等同于：

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

在 MySQL 中，CROSS JOIN在语法上等同于INNER JOIN；它们可以互换使用。在标准 SQL 中，它们不等效。INNER JOIN与ON子句一起使用；否则使用CROSS JOIN。

一般来说，可以忽略仅包含内连接操作的连接表达式中的括号。考虑这个连接表达式：

t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
   ON t1.a=t2.a

删除括号并将操作分组到左侧后，该连接表达式转换为以下表达式：

(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3
    ON t2.b=t3.b OR t2.b IS NULL

然而，这两个表达式并不等效。为了看到这一点，假设表t1，t2和t3具有以下状态：

• 表t1包含行(1)，(2)

• 表t2包含行(1,101)

• 表t3包含行(101)

在这种情况下，第一个表达式返回包含行(1,1,101,101)，(2,NULL,NULL,NULL)的结果集，而第二个表达式返回行(1,1,101,101)，(2,NULL,NULL,101)：

mysql> SELECT *
       FROM t1
            LEFT JOIN
            (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
            ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+

mysql> SELECT *
       FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
            LEFT JOIN t3
            ON t2.b=t3.b OR t2.b IS NULL;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

在以下示例中，外连接操作与内连接操作一起使用：

t1 LEFT JOIN (t2, t3) ON t1.a=t2.a

该表达式无法转换为以下表达式：

t1 LEFT JOIN t2 ON t1.a=t2.a, t3

对于给定的表状态，这两个表达式返回不同的行集：

mysql> SELECT *
       FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+

mysql> SELECT *
       FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

因此，如果在具有外连接运算符的连接表达式中省略括号，可能会改变原始表达式的结果集。

更确切地说，在左外连接操作的右操作数和右连接操作的左操作数中不能忽略括号。换句话说，我们不能忽略外连接操作的内表达式的括号。其他操作数（外表的操作数）的括号可以忽略。

以下表达式：

(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)

对于任何表t1,t2,t3和任何条件P，在属性t2.b和t3.b上：

t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)

每当连接表达式（joined_table）中连接操作的执行顺序不是从左到右时，我们谈论嵌套连接。考虑以下查询：

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a
  WHERE t1.a > 1

SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
  WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1

这些查询被认为包含这些嵌套连接：

t2 LEFT JOIN t3 ON t2.b=t3.b
t2, t3

在第一个查询中，嵌套连接是通过左连接操作形成的。在第二个查询中，它是通过内连接操作形成的。

在第一个查询中，括号可以省略：连接表达式的语法结构决定了连接操作的执行顺序相同。对于第二个查询，不能省略括号，尽管这里的连接表达式可以在没有括号的情况下明确解释。在我们的扩展语法中，第二个查询中(t2, t3)的括号是必需的，尽管理论上可以在没有它们的情况下解析查询：我们仍然会对查询有一个明确的语法结构，因为LEFT JOIN和ON扮演了表达式(t2,t3)的左右定界符的角色。

前面的例子演示了这些要点：

• 对于仅涉及内连接（而不是外连接）的连接表达式，可以去掉括号并从左到右评估连接。实际上，表可以以任何顺序评估。

• 一般来说，对于外连接或混合内连接的外连接，去掉括号可能会改变结果。

具有嵌套外连接的查询以与具有内连接相同的管道方式执行。更确切地说，利用了嵌套循环连接算法的一个变体。回想一下嵌套循环连接执行查询的算法（参见第 10.2.1.7 节，“嵌套循环连接算法”）。假设一个涉及 3 个表T1,T2,T3的连接查询具有以下形式：

SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
                 INNER JOIN T3 ON P2(T2,T3)
  WHERE P(T1,T2,T3)

这里，P1(T1,T2)和P2(T3,T3)是一些连接条件（基于表达式），而P(T1,T2,T3)是关于表T1,T2,T3列的条件。

嵌套循环连接算法将以以下方式执行此查询：

FOR each row t1 in T1 {
  FOR each row t2 in T2 such that P1(t1,t2) {
    FOR each row t3 in T3 such that P2(t2,t3) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

表示通过连接行t1, t2和t3的列构造的行的符号t1||t2||t3。在以下一些示例中，表名出现的地方的NULL表示使用NULL作为该表的每列。例如，t1||t2||NULL表示通过连接行t1和t2的列，并为t3的每列使用NULL构造的行。这样的行被称为NULL-补充。

现在考虑一个具有嵌套外连接的查询：

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON P2(T2,T3))
              ON P1(T1,T2)
  WHERE P(T1,T2,T3)

对于这个查询，修改嵌套循环模式以获得：

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t2 in T2 such that P1(t1,t2) {
    BOOL f2:=FALSE;
    FOR each row t3 in T3 such that P2(t2,t3) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f2=TRUE;
      f1=TRUE;
    }
    IF (!f2) {
      IF P(t1,t2,NULL) {
        t:=t1||t2||NULL; OUTPUT t;
      }
      f1=TRUE;
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

一般来说，在外连接操作的第一个内表的嵌套循环中，引入了一个标志，在循环之前关闭，在循环之后检查。当在外表的当前行中找到来自表示内操作数的表的匹配时，该标志被打开。如果在循环周期结束时标志仍然关闭，则没有找到外表当前行的匹配。在这种情况下，行将通过为内表的列补充NULL值。结果行将传递到输出的最终检查或下一个嵌套循环中，但仅当该行满足所有嵌套外连接的连接条件时。

在示例中，以下表达式表示的外连接表被嵌入：

(T2 LEFT JOIN T3 ON P2(T2,T3))

对于带有内连接的查询，优化器可以选择不同顺序的嵌套循环，例如这样一个顺序：

FOR each row t3 in T3 {
  FOR each row t2 in T2 such that P2(t2,t3) {
    FOR each row t1 in T1 such that P1(t1,t2) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

对于带有外连接的查询，优化器只能选择外表的循环在内表的循环之前的顺序。因此，对于我们带有外连接的查询，只有一种嵌套顺序是可能的。对于以下查询，优化器评估了两种不同的嵌套。在这两种嵌套中，T1必须在外循环中处理，因为它用于外连接。T2和T3用于内连接，因此该连接必须在内循环中处理。然而，由于连接是内连接，T2和T3可以以任何顺序处理。

SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)
  WHERE P(T1,T2,T3)

一个嵌套评估T2，然后是T3：

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t2 in T2 such that P1(t1,t2) {
    FOR each row t3 in T3 such that P2(t1,t3) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f1:=TRUE
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

另一个嵌套评估T3，然后是T2：

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t3 in T3 such that P2(t1,t3) {
    FOR each row t2 in T2 such that P1(t1,t2) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f1:=TRUE
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

在讨论内连接的嵌套循环算法时，我们省略了一些细节，这些细节对查询执行性能的影响可能是巨大的。我们没有提到所谓的“推送下推”条件。假设我们的WHERE条件P(T1,T2,T3)可以用一个合取公式表示：

P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).

在这种情况下，MySQL 实际上使用以下嵌套循环算法来执行带有内连接的查询：

FOR each row t1 in T1 such that C1(t1) {
  FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2)  {
    FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

您可以看到每个连接C1(T1)，C2(T2)，C3(T3)都被推出最内部循环到最外部循环，其中可以进行评估。如果C1(T1)是一个非常严格的条件，这种条件下推可能会大大减少传递给内部循环的表T1的行数。结果，查询的执行时间可能会极大地改善。

对于带有外连接的查询，WHERE条件只有在发现当前外表行在内表中有匹配时才会被检查。因此，将条件推出内部嵌套循环的优化不能直接应用于带有外连接的查询。在这里，我们必须引入由标志保护的条件推送下推谓词，当遇到匹配时这些标志被打开。

回想一下带有外连接的这个例子：

P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)

对于那个例子，使用受保护的推送下推条件的嵌套循环算法如下：

FOR each row t1 in T1 such that C1(t1) {
  BOOL f1:=FALSE;
  FOR each row t2 in T2
      such that P1(t1,t2) AND (f1?C2(t2):TRUE) {
    BOOL f2:=FALSE;
    FOR each row t3 in T3
        such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {
      IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f2=TRUE;
      f1=TRUE;
    }
    IF (!f2) {
      IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {
        t:=t1||t2||NULL; OUTPUT t;
      }
      f1=TRUE;
    }
  }
  IF (!f1 && P(t1,NULL,NULL)) {
      t:=t1||NULL||NULL; OUTPUT t;
  }
}

一般来说，推送下推谓词可以从连接条件中提取，例如P1(T1,T2)和P(T2,T3)。在这种情况下，推送下推谓词也受到一个标志的保护，该标志防止对由相应外连接操作生成的NULL-补充行进行谓词检查。

在相同的嵌套连接中，如果由WHERE条件引起，从一个内表到另一个内表的键访问是被禁止的。

原文：dev.mysql.com/doc/refman/8.0/en/outer-join-optimization.html

10.2.1.9 外连接优化

外连接包括LEFT JOIN和RIGHT JOIN。

MySQL 实现*A* LEFT JOIN *B* *join_specification*如下：

• 表B被设置为依赖于表A和A依赖的所有表。

• 表A被设置为依赖于所有表（除了B）在LEFT JOIN条件中使用的表。

• LEFT JOIN条件用于决定如何从表B中检索行。（换句话说，WHERE子句中的任何条件都不会被使用。）

• 执行所有标准连接优化，除了一个表始终在其依赖的所有表之后读取。如果存在循环依赖关系，则会出现错误。

• 执行所有标准WHERE优化。

• 如果A中有一行与WHERE子句匹配，但B中没有一行与ON条件匹配，则会生成一个额外的B行，其中所有列均设置为NULL。

• 如果您使用LEFT JOIN查找在某个表中不存在的行，并且在WHERE部分中有以下测试：*col_name* IS NULL，其中col_name被声明为NOT NULL的列，那么 MySQL 在找到与LEFT JOIN条件匹配的一行后，停止搜索更多行（对于特定键组合）。

RIGHT JOIN的实现类似于LEFT JOIN，只是表的角色被颠倒。右连接被转换为等效的左连接，如 Section 10.2.1.10, “Outer Join Simplification”中所述。

对于LEFT JOIN，如果生成的NULL行的WHERE条件始终为假，则LEFT JOIN会转为内连接。例如，在以下查询中，如果t2.column1为NULL，则WHERE子句将为假：

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

因此，将查询转换为内连接是安全的：

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

在 MySQL 8.0.14 及更高版本中，常量文字表达式导致的平凡WHERE条件在准备期间被移除，而不是在优化的后期阶段，此时连接已经被简化。提前移除平凡条件允许优化器将外连接转换为内连接；这可以改善包含WHERE子句中包含平凡条件的外连接查询的计划，例如以下查询：

SELECT * FROM t1 LEFT JOIN t2 ON *condition_1* WHERE *condition_2* OR 0 = 1

优化器现在在准备期间看到 0 = 1 始终为假，使得OR 0 = 1多余，并将其删除，留下这样的内容：

SELECT * FROM t1 LEFT JOIN t2 ON *condition_1* where *condition_2*

现在优化器可以将查询重写为内连接，如下所示：

SELECT * FROM t1 JOIN t2 WHERE *condition_1* AND *condition_2*

现在优化器可以在表t1之前使用表t2，如果这样做可以得到更好的查询计划。要提供关于表连接顺序的提示，请使用优化器提示；参见第 10.9.3 节，“优化器提示”。或者，使用STRAIGHT_JOIN；参见第 15.2.13 节，“SELECT 语句”。然而，STRAIGHT_JOIN可能会阻止索引的使用，因为它禁用了半连接转换；参见第 10.2.2.1 节，“使用半连接转换优化 IN 和 EXISTS 子查询谓词”。

原文：dev.mysql.com/doc/refman/8.0/en/outer-join-simplification.html

10.2.1.10 外连接简化

查询的FROM子句中的表达式在许多情况下被简化。

在解析器阶段，具有右外连接操作的查询被转换为仅包含左连接操作的等效查询。在一般情况下，转换是这样执行的，即这个右连接：

(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)

变成这个等效的左连接：

(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)

所有形式为T1 INNER JOIN T2 ON P(T1,T2)的内连接表达式都被替换为列表T1,T2，P(T1,T2)作为WHERE条件的一个连接被连接（或者连接的连接条件，如果有的话）。

当优化器评估外连接操作的计划时，它只考虑那些在每个这样的操作中，外部表在内部表之前被访问的计划。优化器的选择是有限的，因为只有这样的计划才能使用嵌套循环算法执行外连接。

考虑这种形式的查询，其中R(T2)大大缩小了与表T2匹配行的数量：

SELECT * T1 FROM T1
  LEFT JOIN T2 ON P1(T1,T2)
  WHERE P(T1,T2) AND R(T2)

如果按照原样执行查询，优化器别无选择，只能在访问受限制较少的表T1之前访问受限制较多的表T2，这可能会产生一个非常低效的执行计划。

相反，如果WHERE条件被外连接操作拒绝，MySQL 会将查询转换为不包含外连接操作的查询。（也就是说，它将外连接转换为内连接。）如果条件对于为操作生成的任何NULL-补充行求值为FALSE或UNKNOWN，则称条件对于外连接操作被拒绝。

因此，对于这个外连接：

T1 LEFT JOIN T2 ON T1.A=T2.A

诸如这些条件之类的条件被拒绝，因为它们对于任何NULL-补充行（T2列设置为NULL）都不可能为真：

T2.B IS NOT NULL
T2.B > 3
T2.C <= T1.C
T2.B < 2 OR T2.C > 1

诸如这些条件之类的条件不被拒绝，因为它们可能对于一个NULL-补充行为真：

T2.B IS NULL
T1.B < 3 OR T2.B IS NOT NULL
T1.B < 3 OR T2.B > 3

检查条件是否被外连接操作拒绝的一般规则很简单：

• 它的形式是A IS NOT NULL，其中A是任何内部表的属性

• 它是一个包含对内部表的引用的谓词，当其参数之一为NULL时求值为UNKNOWN

• 它是一个包含被拒绝条件的合取作为一个连接

• 它是一个被拒绝的条件的析取

一个条件可以被一个查询中的一个外连接操作拒绝，但对另一个查询中的另一个外连接操作不被拒绝。在这个查询中，WHERE条件对于第二个外连接操作被拒绝，但对于第一个外连接操作不被拒绝：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

如果WHERE条件在查询中被外连接操作拒绝，外连接操作将被替换为内连接操作。

例如，在前面的查询中，第二个外连接被空拒绝，可以被内连接替换：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

对于原始查询，优化器仅评估与单表访问顺序T1，T2，T3兼容的计划。对于重写后的查询，它还考虑了访问顺序T3，T1，T2。

一个外连接操作的转换可能会触发另一个的转换。因此，查询：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

首先被转换为查询：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

相当于查询：

SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

剩余的外连接操作也可以被内连接替换，因为条件T3.B=T2.B被拒绝为空。这导致一个没有任何外连接的查询：

SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

有时优化器成功地替换了嵌套的外连接操作，但无法转换嵌套外连接。以下查询：

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A
  WHERE T3.C > 0

被转换为：

SELECT * FROM T1 LEFT JOIN
              (T2 INNER JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A
  WHERE T3.C > 0

只能被重写为仍然包含嵌套外连接操作的形式：

SELECT * FROM T1 LEFT JOIN
              (T2,T3)
              ON (T2.A=T1.A AND T3.B=T2.B)
  WHERE T3.C > 0

任何尝试将查询中的嵌套外连接操作转换必须考虑嵌套外连接的连接条件以及WHERE条件。在这个查询中，WHERE条件对于嵌套外连接不被拒绝为空，但是嵌套外连接的连接条件T2.A=T1.A AND T3.C=T1.C被拒绝为空：

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A AND T3.C=T1.C
  WHERE T3.D > 0 OR T1.D > 0

因此，查询可以转换为：

SELECT * FROM T1 LEFT JOIN
              (T2, T3)
              ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B
  WHERE T3.D > 0 OR T1.D > 0

原文：dev.mysql.com/doc/refman/8.0/en/mrr-optimization.html

10.2.1.11 多范围读取优化

使用二级索引进行范围扫描读取行时，当表很大且未存储在存储引擎的缓存中时，可能会导致许多随机磁盘访问。通过磁盘扫描多范围读取（MRR）优化，MySQL 尝试通过首先仅扫描索引并收集相关行的键来减少范围扫描的随机磁盘访问次数。然后对键进行排序，最后根据主键的顺序从基表中检索行。磁盘扫描 MRR 的动机是减少随机磁盘访问次数，而是实现对基表数据的更顺序扫描。

多范围读取优化提供以下好处：

• MRR 使数据行能够按顺序访问，而不是按随机顺序，基于索引元组。服务器获取满足查询条件的一组索引元组，根据数据行 ID 顺序对其进行排序，并使用排序后的元组按顺序检索数据行。这使数据访问更高效，成本更低。

• MRR 可以批量处理对需要通过索引元组访问数据行的操作的请求，例如范围索引扫描和使用索引进行等值连接的操作。MRR 遍历一系列索引范围以获取符合条件的索引元组。随着这些结果的累积，它们被用于访问相应的数据行。在开始读取数据行之前，不需要获取所有索引元组。

MRR 优化不支持在虚拟生成列上创建的二级索引。InnoDB 支持在虚拟生成列上的二级索引。

以下情景说明了何时可以优化 MRR：

情景 A：MRR 可以用于 InnoDB 和 MyISAM 表进行索引范围扫描和等值连接操作。

1. 一部分索引元组被累积在缓冲区中。

2. 缓冲区中的元组按其数据行 ID 进行排序。

3. 数据行根据排序的索引元组序列进行访问。

情景 B：MRR 可以用于 NDB 表，用于多范围索引扫描或通过属性执行等值连接。

1. 一部分范围，可能是单键范围，在提交查询的中央节点上累积在缓冲区中。

2. 范围被发送到访问数据行的执行节点。

3. 访问的行被打包成数据包发送回中央节点。

4. 收到的带有数据行的数据包被放置在缓冲区中。

5. 数据行从缓冲区中读取。

当使用 MRR 时，EXPLAIN 输出中的 Extra 列显示 Using MRR。

如果不需要访问完整的表行即可生成查询结果，则InnoDB和MyISAM不使用 MRR。如果结果可以完全基于索引元组中的信息生成（通过覆盖索引）；MRR 提供不了任何好处。

两个optimizer_switch系统变量标志提供了使用 MRR 优化的接口。mrr标志控制是否启用 MRR。如果mrr被启用（on），mrr_cost_based标志控制优化器是否尝试在使用和不使用 MRR 之间做出基于成本的选择（on），或者在可能的情况下始终使用 MRR（off）。默认情况下，mrr为on，mrr_cost_based为on。请参阅第 10.9.2 节，“可切换的优化”。

对于 MRR，存储引擎使用read_rnd_buffer_size系统变量的值作为其缓冲区可以分配多少内存的指导。引擎最多使用read_rnd_buffer_size字节，并确定在单次传递中要处理的范围数量。

原文：dev.mysql.com/doc/refman/8.0/en/bnl-bka-optimization.html

10.2.1.12 块嵌套循环和批量键访问连接

在 MySQL 中，有一个批量键访问（BKA）连接算法，它同时使用对连接表的索引访问和连接缓冲。BKA 算法支持内连接、外连接和半连接操作，包括嵌套外连接。BKA 的好处包括由于更有效的表扫描而改进的连接性能。此外，之前仅用于内连接的块嵌套循环（BNL）连接算法被扩展，可以用于外连接和半连接操作，包括嵌套外连接。

以下各节讨论了原始 BNL 算法、扩展 BNL 算法和 BKA 算法的基础连接缓冲管理。有关半连接策略的信息，请参见第 10.2.2.1 节，“使用半连接转换优化 IN 和 EXISTS 子查询谓词”

• 用于块嵌套循环和批量键访问算法的连接缓冲管理

• 用于外连接和半连接的块嵌套循环算法

• 批量键访问连接

• 块嵌套循环和批量键访问算法的优化提示

用于块嵌套循环和批量键访问算法的连接缓冲管理

MySQL 可以利用 join 缓冲来执行不仅内连接而且没有对内部表进行索引访问的外连接和半连接，这些连接出现在子查询展开之后。此外，当对内部表进行索引访问时，可以有效地使用 join 缓冲。

join 缓冲管理代码在存储感兴趣的行列的值时，稍微更有效地利用了 join 缓冲空间：如果一个行列的值是NULL，则不会为其分配额外的字节，对于VARCHAR类型的任何值，都会分配最少数量的字节。

代码支持两种类型的缓冲区，常规和增量。假设使用 join 缓冲B1连接表t1和t2，并且将此操作的结果使用 join 缓冲B2连接到表t3：

• 一个常规的 join 缓冲包含每个连接操作数的列。如果B2是一个常规的 join 缓冲，那么放入B2的每一行r由来自B1的行r1的列和来自表t3的匹配行r2的感兴趣列组成。

• 增量连接缓冲区仅包含由第二个连接操作数产生的表的行的列。也就是说，它是相对于第一个操作数缓冲区的增量。如果 B2 是一个增量连接缓冲区，它包含行 r2 的有趣列以及与 B1 中的行 r1 的链接。

增量连接缓冲区始终相对于较早连接操作的连接缓冲区是增量的，因此第一个连接操作的缓冲区始终是常规缓冲区。在刚才给出的示例中，用于连接表 t1 和 t2 的缓冲区 B1 必须是常规缓冲区。

用于连接操作的增量缓冲区的每一行仅包含要连接的表的行的有趣列。这些列与来自第一个连接操作数产生的表的匹配行的有趣列的引用一起增加。增量缓冲区中的多行可以引用相同的行 r，其列存储在先前的连接缓冲区中，只要所有这些行都匹配行 r。

增量缓冲区使得从先前连接操作中使用的缓冲区复制列的频率降低。这样可以节省缓冲区空间，因为在一般情况下，第一个连接操作数产生的行可以与第二个连接操作数产生的多行匹配。从第一个操作数复制一行的多个副本是不必要的。增量缓冲区还通过减少复制时间节省了处理时间。

在 MySQL 8.0 中，block_nested_loop 标志的 optimizer_switch 系统变量的工作方式如下：

• 在 MySQL 8.0.20 之前，它控制优化器如何使用块嵌套循环连接算法。

• 在 MySQL 8.0.18 及更高版本中，它还控制哈希连接的使用（参见 第 10.2.1.4 节，“哈希连接优化”）。

• 从 MySQL 8.0.20 开始，该标志仅控制哈希连接，不再支持块嵌套循环算法。

batched_key_access 标志控制优化器如何使用批量键访问连接算法。

默认情况下，block_nested_loop 是 on，而 batched_key_access 是 off。参见 第 10.9.2 节，“可切换优化”。也可以应用优化提示；参见 块嵌套循环和批量键访问算法的优化提示。

有关半连接策略的信息，请参见第 10.2.2.1 节，“使用半连接转换优化 IN 和 EXISTS 子查询谓词”

外连接和半连接的块嵌套循环算法

MySQL BNL 算法的原始实现已扩展以支持外连接和半连接操作（后来被哈希连接算法取代；请参见第 10.2.1.4 节，“哈希连接优化”）。

当这些操作与连接缓冲区一起执行时，放入缓冲区的每一行都附带一个匹配标志。

如果使用连接缓冲区执行外连接操作，则将第二个操作数生成的表的每一行与连接缓冲区中的每一行进行匹配检查。当找到匹配时，将形成一个新的扩展行（原始行加上第二个操作数的列），并发送给剩余的连接操作进行进一步扩展。此外，缓冲区中匹配的行的匹配标志将被启用。在检查完要连接的表的所有行之后，将扫描连接缓冲区。缓冲区中没有启用其匹配标志的每一行都将通过NULL补充（第二个操作数的每一列的NULL值）进行扩展，并发送给剩余的连接操作进行进一步扩展。

在 MySQL 8.0 中，block_nested_loop标志的optimizer_switch系统变量的工作方式如下：

• 在 MySQL 8.0.20 之前，它控制优化器如何使用块嵌套循环连接算法。

• 在 MySQL 8.0.18 及更高版本中，它还控制哈希连接的使用（请参见第 10.2.1.4 节，“哈希连接优化”）。

• 从 MySQL 8.0.20 开始，该标志仅控制哈希连接，不再支持块嵌套循环算法。

更多信息，请参见第 10.9.2 节，“可切换的优化”。也可以应用优化器提示；请参见块嵌套循环和批量键访问算法的优化器提示。

在EXPLAIN输出中，当Extra值包含Using join buffer (Block Nested Loop)且type值为ALL、index或range时，表示对表使用 BNL。

有关半连接策略的信息，请参见第 10.2.2.1 节，“使用半连接转换优化 IN 和 EXISTS 子查询谓词”

批量键访问连接

MySQL 实现了一种称为批量键访问（BKA）连接算法的表连接方法。当第二个连接操作数产生的表具有索引访问时，可以应用 BKA。与 BNL 连接算法类似，BKA 连接算法使用连接缓冲区累积连接操作的第一个操作数产生的行的有趣列。然后，BKA 算法构建用于访问要连接的表的键，以便为缓冲区中的所有行提交这些键批量给数据库引擎进行索引查找。这些键通过多范围读取（MRR）接口提交给引擎（参见第 10.2.1.11 节，“多范围读取优化”）。提交键后，MRR 引擎函数以最佳方式在索引中执行查找，获取由这些键找到的连接表的行，并开始将匹配行提供给 BKA 连接算法。每个匹配行都与连接缓冲区中的行的引用相对应。

当使用 BKA 时，join_buffer_size 的值定义了每次请求到存储引擎的键批量有多大。缓冲区越大，对连接操作的右表进行的顺序访问就越多，这可以显著提高性能。

要使用 BKA，必须将optimizer_switch 系统变量的 batched_key_access 标志设置为 on。BKA 使用 MRR，因此 mrr 标志也必须为 on。目前，对于 MRR 的成本估算过于悲观。因此，还需要将 mrr_cost_based 设置为 off 才能使用 BKA。以下设置启用了 BKA：

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

MRR 函数执行有两种情况：

• 第一种情况适用于传统基于磁盘的存储引擎，如InnoDB和MyISAM。对于这些引擎，通常将连接缓冲区中所有行的键一次性提交给 MRR 接口。引擎特定的 MRR 函数为提交的键执行索引查找，从中获取行 ID（或主键），然后通过 BKA 算法的请求逐个获取所有这些选定行 ID 的行。每行都返回一个关联引用，使得可以访问连接缓冲区中匹配的行。MRR 函数以最佳方式获取行：它们按行 ID（主键）顺序获取。这提高了性能，因为读取是按磁盘顺序而不是随机顺序进行的。

• 第二种情况适用于远程存储引擎，如NDB。 MySQL 服务器（SQL 节点）将一部分连接缓冲区中的行的键包以及它们的关联关系发送到 MySQL 集群数据节点。作为回报，SQL 节点接收到一组（或多组）匹配行及其对应的关联关系。BKA 连接算法获取这些行并构建新的连接行。然后一组新的键被发送到数据节点，返回的包中的行被用来构建新的连接行。这个过程持续进行，直到连接缓冲区中的最后一个键被发送到数据节点，并且 SQL 节点已经接收并连接了所有与这些键匹配的行。这提高了性能，因为 SQL 节点发送给数据节点的带键包数量较少，意味着在 SQL 节点和数据节点之间执行连接操作的往返次数较少。

在第一种情况下，连接缓冲区的一部分被保留用于存储通过索引查找选择的行 ID（主键），并作为参数传递给 MRR 函数。

没有专门的缓冲区用于存储从连接缓冲区构建的键。相反，构建下一个缓冲区中行的键的函数被作为参数传递给 MRR 函数。

在EXPLAIN输出中，当Extra值包含Using join buffer (Batched Key Access)，并且type值为ref或eq_ref时，表示对表使用了 BKA。

用于块嵌套循环和批量键访问算法的优化器提示

除了使用optimizer_switch系统变量来控制全局会话中优化器使用 BNL 和 BKA 算法外，MySQL 还支持优化器提示来影响每个语句的优化器。请参阅第 10.9.3 节，“优化器提示”。

要使用 BNL 或 BKA 提示为外连接的任何内部表启用连接缓冲，必须为外连接的所有内部表启用连接缓冲。

原文：dev.mysql.com/doc/refman/8.0/en/condition-filtering.html

10.2.1.13 条件过滤

在连接处理中，前缀行是从一个连接中传递到下一个连接的行。 一般来说，优化器尝试尽早将前缀计数较低的表放在连接顺序中，以避免行组合数量迅速增加。 在优化器可以使用有关从一个表中选择并传递到下一个表的行的条件的信息的程度上，它可以更准确地计算行估计并选择最佳执行计划。

没有条件过滤，表的前缀行数是基于优化器根据选择的访问方法估计的WHERE子句选择的行数。 条件过滤使优化器能够使用WHERE子句中未被访问方法考虑的其他相关条件，并因此改进其前缀行数估计。 例如，即使可能存在可用于从当前表中选择行的基于索引的访问方法，也可能存在WHERE子句中对表的其他条件进行过滤（进一步限制）的情况，以过滤传递到下一个表的符合条件的行的估计。

仅当条件对过滤估计有贡献时才会计入：

• 它指的是当前表。

• 它取决于连接序列中较早表的常量值或值。

• 它在访问方法中尚未考虑。

在EXPLAIN输出中，rows列指示所选访问方法的行估计，而filtered列反映条件过滤的效果。 filtered值以百分比表示。 最大值为 100，表示未发生任何行过滤。 从 100 减少的值表示过滤量增加。

前缀行数（从当前连接中传递到下一个连接的估计行数）是rows和filtered值的乘积。 也就是说，前缀行数是估计的行数，减去估计的过滤效果。 例如，如果rows为 1000，filtered为 20％，则条件过滤将 1000 的估计行数减少为 1000 × 20％= 1000 × .2 = 200。

考虑以下查询：

SELECT *
  FROM employee JOIN department ON employee.dept_no = department.dept_no
  WHERE employee.first_name = 'John'
  AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01';

假设数据集具有以下特征：

• employee表有 1024 行。

• department表有 12 行。

• 两个表在dept_no上都有索引。

• employee表在first_name上有一个索引。

• 8 行满足employee.first_name上的这个条件：

  employee.first_name = 'John'
  

• 150 行满足employee.hire_date上的这个条件：

  employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'
  

• 1 行同时满足这两个条件：

  employee.first_name = 'John'
  AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'
  

没有条件过滤，EXPLAIN会产生如下输出：

+----+------------+--------+------------------+---------+---------+------+----------+
| id | table      | type   | possible_keys    | key     | ref     | rows | filtered |
+----+------------+--------+------------------+---------+---------+------+----------+
| 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 100.00   |
| 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   |
+----+------------+--------+------------------+---------+---------+------+----------+

对于employee，在name索引上的访问方法选择匹配名称为'John'的 8 行。不进行过滤（filtered为 100%），因此所有行都是下一个表的前缀行：前缀行数为rows × filtered = 8 × 100% = 8。

使用条件过滤，优化器还考虑WHERE子句中未考虑的条件。在这种情况下，优化器使用启发式方法估计employee.hire_date上BETWEEN条件的过滤效果为 16.31%。因此，EXPLAIN生成类似以下输出：

+----+------------+--------+------------------+---------+---------+------+----------+
| id | table      | type   | possible_keys    | key     | ref     | rows | filtered |
+----+------------+--------+------------------+---------+---------+------+----------+
| 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 16.31    |
| 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   |
+----+------------+--------+------------------+---------+---------+------+----------+

现在前缀行数为rows × filtered = 8 × 16.31% = 1.3，更接近实际数据集。

通常，优化器不会计算最后一个连接表的条件过滤效果（前缀行数减少），因为没有下一个表可以传递行。一个例外情况是EXPLAIN：为了提供更多信息，过滤效果将计算所有连接表，包括最后一个。

要控制优化器是否考虑额外的过滤条件，请使用optimizer_switch系统变量的condition_fanout_filter标志（参见第 10.9.2 节，“可切换的优化”）。该标志默认启用，但可以禁用以抑制条件过滤（例如，如果发现特定查询在没有条件过滤的情况下性能更好）。

如果优化器高估了条件过滤的效果，性能可能会比不使用条件过滤更差。在这种情况下，可以采用以下技巧：

• 如果某列没有索引，请为其创建索引，以便优化器了解列值的分布并改进其行估计。

• 类似地，如果没有列直方图信息可用，请生成直方图（参见第 10.9.6 节，“优化器统计”）。

• 更改连接顺序。实现这一点的方法包括连接顺序优化器提示（参见第 10.9.3 节，“优化器提示”）、SELECT后紧跟STRAIGHT_JOIN，以及STRAIGHT_JOIN连接操作符。

• 禁用会话的条件过滤：

  SET optimizer_switch = 'condition_fanout_filter=off';
  

  或者，对于给定的查询，使用优化器提示：

  SELECT /*+ SET_VAR(optimizer_switch = 'condition_fanout_filter=off') */ ...
  

原文：dev.mysql.com/doc/refman/8.0/en/constant-folding-optimization.html

10.2.1.14 常量折叠优化

常量与列值之间的比较，其中常量值超出范围或与列类型不匹配，现在在查询优化期间处理一次，而不是在执行期间逐行处理。可以以这种方式处理的比较包括>, >=, <, <=, <>/!=, =, 和 <=>。

考虑以下语句创建的表：

CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);

查询SELECT * FROM t WHERE c < 256中的WHERE条件包含整数常量 256，这对于TINYINT UNSIGNED列来说超出范围。以前，这是通过将两个操作数都视为较大类型来处理的，但现在，由于c的任何允许值都小于常量，因此WHERE表达式可以折叠为WHERE 1，使查询重写为SELECT * FROM t WHERE 1。

这使得优化器可以完全删除WHERE表达式。如果列c可为空（即仅定义为TINYINT UNSIGNED），则查询将被重写如下：

SELECT * FROM t WHERE ti IS NOT NULL

对比较支持的 MySQL 列类型的常量进行折叠如下：

• 整数列类型。 整数类型与以下类型的常量进行比较，如下所述：

  • 整数值。 如果常量超出列类型的范围，比较将折叠为1或IS NOT NULL，如已经显示的那样。

    如果常量是一个范围边界，比较将折叠为=。例如（使用已经定义的相同表）：

    mysql> EXPLAIN SELECT * FROM t WHERE c >= 255;
    *************************** 1\. row ***************************
               id: 1
      select_type: SIMPLE
            table: t
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 5
         filtered: 20.00
            Extra: Using where 1 row in set, 1 warning (0.00 sec)
    
    mysql> SHOW WARNINGS;
    *************************** 1\. row ***************************
      Level: Note
       Code: 1003
    Message: /* select#1 */ select `test`.`t`.`ti` AS `ti` from `test`.`t` where (`test`.`t`.`ti` = 255) 1 row in set (0.00 sec)
    

  • 浮点或定点值。 如果常量是十进制类型之一（如DECIMAL，REAL，DOUBLE或FLOAT）并且具有非零小数部分，则不能相等；相应地折叠。对于其他比较，根据符号四舍五入到整数值，然后执行范围检查并按照已经描述的方式处理整数-整数比较。

    无法表示为DECIMAL的REAL值将四舍五入为.01 或-.01，然后作为DECIMAL处理。

  • 字符串类型。 尝试将字符串值解释为整数类型，然后将比较处理为整数值之间的比较。如果失败，则尝试将值处理为REAL。

• DECIMAL 或 REAL 列。 十进制类型与以下类型的常量进行比较，如下所述：

  • 整数值。 对列值的整数部分执行范围检查。如果没有折叠结果，将常量转换为与列值具有相同小数位数的DECIMAL，然后将其作为DECIMAL进行检查（见下文）。

  • DECIMAL 或 REAL 值。 检查溢出（即常量的整数部分是否比列的十进制类型允许的更多位数）。如果是，则折叠。

    如果常量的有效小数位数多于列的类型，截断常量。如果比较运算符是=或<>，则折叠。如果运算符是>=或<=，由于截断而调整运算符。例如，如果列的类型是DECIMAL(3,1)，SELECT * FROM t WHERE f >= 10.13变为SELECT * FROM t WHERE f > 10.1。

    如果常量的小数位数少于列的类型，将其转换为具有相同位数的常量。对于REAL值的下溢（即，小数位数太少无法表示），将常量转换为十进制 0。

  • 字符串值。 如果值可以解释为整数类型，则将其处理为整数类型。否则，尝试将其处理为REAL。

• FLOAT 或 DOUBLE 列。 FLOAT(*m*,*n*)或DOUBLE(*m*,*n*)与常量的比较处理如下：

  如果值超出列的范围，折叠。

  如果值有超过n个小数位，截断，折叠时进行补偿。对于=和<>比较，按照之前描述的折叠为TRUE、FALSE或IS [NOT] NULL；对于其他运算符，调整运算符。

  如果值有超过m个整数位，折叠。

限制。 该优化不能用于以下情况：

1. 使用BETWEEN或IN进行比较。

2. 与BIT列或使用日期或时间类型的列。

3. 在准备语句的准备阶段，尽管可以在实际执行准备语句时进行优化阶段应用。这是因为在语句准备期间，常量的值尚未知晓。

原文：dev.mysql.com/doc/refman/8.0/en/is-null-optimization.html

10.2.1.15 IS NULL 优化

MySQL 可以对 col_name IS NULL 执行与 col_name = constant_value 相同的优化。例如，MySQL 可以使用索引和范围来搜索带有 IS NULL 的 NULL。

示例:

SELECT * FROM *tbl_name* WHERE *key_col* IS NULL;

SELECT * FROM *tbl_name* WHERE *key_col* <=> NULL;

SELECT * FROM *tbl_name*
  WHERE *key_col*=*const1* OR *key_col*=*const2* OR *key_col* IS NULL;

如果 WHERE 子句包含对声明为 NOT NULL 的列的 col_name IS NULL 条件，则该表达式会被优化掉。在列可能产生 NULL 的情况下（例如，如果它来自 LEFT JOIN 的右侧表），此优化不会发生。

MySQL 也可以优化组合 *col_name* = *expr* OR *col_name* IS NULL，这种形式在解析子查询中很常见。EXPLAIN 在使用此优化时显示 ref_or_null。

此优化可以处理任何关键部分的一个 IS NULL。

一些查询示例，假设在表 t2 的列 a 和 b 上有索引：

SELECT * FROM t1 WHERE t1.a=*expr* OR t1.a IS NULL;

SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1, t2
  WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
  OR (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_null 首先对参考键进行读取，然后单独搜索具有 NULL 键值的行。

该优化只能处理一个 IS NULL 级别。在下面的查询中，MySQL 仅在表达式 (t1.a=t2.a AND t2.a IS NULL) 上使用关键查找，并不能使用列 b 上的关键部分：

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL)
  OR (t1.b=t2.b AND t2.b IS NULL);

原文：dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

10.2.1.16 ORDER BY 优化

本节描述了 MySQL 何时可以使用索引来满足ORDER BY子句，当无法使用索引时使用的filesort操作，以及优化器提供的关于ORDER BY的执行计划信息。

带有和不带有LIMIT的ORDER BY可能以不同的顺序返回行，如第 10.2.1.19 节，“LIMIT 查询优化”中讨论的那样。

• 使用索引满足 ORDER BY

• 使用 filesort 满足 ORDER BY

• 影响 ORDER BY 优化

• ORDER BY 执行计划信息可用

使用索引满足 ORDER BY

在某些情况下，MySQL 可能使用索引来满足ORDER BY子句，并避免执行filesort操作所涉及的额外排序。

只要ORDER BY与索引不完全匹配，只要索引的所有未使用部分和所有额外的ORDER BY列在WHERE子句中都是常量，索引也可以被使用。如果索引不包含查询访问的所有列，那么只有在索引访问比其他访问方法更便宜时才会使用索引。

假设在(*key_part1*, *key_part2*)上有一个索引，以下查询可能使用索引来解决ORDER BY部分。优化器是否实际这样做取决于如果索引不包含的列也必须被读取，那么读取索引是否比表扫描更有效。

• 在这个查询中，对(*key_part1*, *key_part2*)的索引使得优化器可以避免排序：

  SELECT * FROM t1
    ORDER BY *key_part1*, *key_part2*;
  

  然而，该查询使用了SELECT *，可能选择的列比key_part1和key_part2多。在这种情况下，扫描整个索引并查找表行以找到不在索引中的列可能比扫描表并对结果进行排序更昂贵。如果是这样，优化器可能不使用索引。如果SELECT *只选择索引列，那么索引会被使用，避免排序。

  如果t1是一个InnoDB表，表主键隐式地是索引的一部分，索引可以用于解决此查询的ORDER BY：

  SELECT *pk*, *key_part1*, *key_part2* FROM t1
    ORDER BY *key_part1*, *key_part2*;
  

• 在这个查询中，key_part1是常量，因此通过索引访问的所有行都按key_part2顺序排列，而且在WHERE子句足够选择性的情况下，(*key_part1*, *key_part2*)上的索引避免排序比表扫描更便宜：

  SELECT * FROM t1
    WHERE *key_part1* = *constant*
    ORDER BY *key_part2*;
  

• 在接下来的两个查询中，是否使用索引与之前显示的没有DESC的相同查询类似：

  SELECT * FROM t1
    ORDER BY *key_part1* DESC, *key_part2* DESC;
  
  SELECT * FROM t1
    WHERE *key_part1* = *constant*
    ORDER BY *key_part2* DESC;
  

• ORDER BY中的两列可以按相同方向排序（都是ASC或都是DESC），也可以按相反方向排序（一个ASC，一个DESC）。索引使用的条件是索引必须具有相同的同质性，但实际方向不必相同。

  如果查询混合使用ASC和DESC，优化器可以在索引上使用这些列，如果索引也使用相应的混合升序和降序列：

  SELECT * FROM t1
    ORDER BY *key_part1* DESC, *key_part2* ASC;
  

  如果key_part1是降序的，而key_part2是升序的，优化器可以在(key_part1, key_part2)上使用索引。如果key_part1是升序的，而key_part2是降序的，它也可以使用这些列的索引（进行反向扫描）。参见 Section 10.3.13, “Descending Indexes”。

• 在接下来的两个查询中，key_part1与一个常量进行比较。如果WHERE子句足够选择性，使得索引范围扫描比表扫描更便宜，则会使用索引：

  SELECT * FROM t1
    WHERE *key_part1* > *constant*
    ORDER BY *key_part1* ASC;
  
  SELECT * FROM t1
    WHERE *key_part1* < *constant*
    ORDER BY *key_part1* DESC;
  

• 在下一个查询中，ORDER BY没有命名key_part1，但所选的所有行都具有常量key_part1值，因此仍然可以使用索引：

  SELECT * FROM t1
    WHERE *key_part1* = *constant1* AND *key_part2* > *constant2*
    ORDER BY *key_part2*;
  

在某些情况下，MySQL 无法 使用索引解析ORDER BY，尽管仍然可以使用索引找到与WHERE子句匹配的行。例如：

• 查询在不同的索引上使用ORDER BY：

  SELECT * FROM t1 ORDER BY *key1*, *key2*;
  

• 查询在索引的非连续部分上使用ORDER BY：

  SELECT * FROM t1 WHERE *key2*=*constant* ORDER BY *key1_part1*, *key1_part3*;
  

• 用于获取行的索引与ORDER BY中使用的索引不同：

  SELECT * FROM t1 WHERE *key2*=*constant* ORDER BY *key1*;
  

• 查询使用包含除索引列名之外的项的表达式进行ORDER BY：

  SELECT * FROM t1 ORDER BY ABS(*key*);
  SELECT * FROM t1 ORDER BY -*key*;
  

• 查询涉及多个表，并且ORDER BY中的列并非全部来自用于检索行的第一个非常量表（这是EXPLAIN输出中第一个没有const连接类型的表）。

• 查询具有不同的ORDER BY和GROUP BY表达式。

• 在ORDER BY子句中只有列名的前缀上有索引。在这种情况下，索引无法完全解析排序顺序。例如，如果只对CHAR(20)列的前 10 个字节建立索引，则索引无法区分第 10 个字节之后的值，需要使用filesort。

• 索引不按顺序存储行。例如，在MEMORY表中的HASH索引是如此。

排序的索引可用性可能受到列别名的影响。假设列t1.a已建立索引。在此语句中，选择列表中的列名为a。它指的是t1.a，与ORDER BY中对a的引用一样，因此可以使用t1.a上的索引：

SELECT a FROM t1 ORDER BY a;

在此语句中，选择列表中的列名也是a，但它是别名。它指的是ABS(a)，与ORDER BY中对a的引用一样，因此无法使用t1.a上的索引：

SELECT ABS(a) AS a FROM t1 ORDER BY a;

在以下语句中，ORDER BY 指的是选择列表中不是列名的名称。但是在 t1 中有一个名为 a 的列，因此 ORDER BY 指的是 t1.a，并且可以使用在 t1.a 上的索引。（当然，生成的排序顺序可能与 ABS(a) 的顺序完全不同。）

SELECT ABS(a) AS b FROM t1 ORDER BY a;

以前（MySQL 5.7 及更低版本），在某些条件下，GROUP BY 会隐式排序。在 MySQL 8.0 中，不再发生这种情况，因此不再需要在末尾指定 ORDER BY NULL 来抑制隐式排序（如以前所做）。但是，查询结果可能与以前的 MySQL 版本不同。为了产生给定的排序顺序，请提供一个 ORDER BY 子句。

使用 filesort 满足 ORDER BY

如果无法使用索引满足 ORDER BY 子句，MySQL 执行一个 filesort 操作，读取表行并对其进行排序。filesort 在查询执行中构成额外的排序阶段。

从 MySQL 8.0.12 开始，为了获取 filesort 操作的内存，优化器根据需要逐步分配内存缓冲区，直到达到 sort_buffer_size 系统变量指示的大小，而不是像在 MySQL 8.0.12 之前那样一次性分配固定数量的 sort_buffer_size 字节。这使用户可以将 sort_buffer_size 设置为较大的值，以加快较大的排序，而不必担心小排序的过度内存使用。（在 Windows 上，对于多个并发排序，这种好处可能不会发生，因为 Windows 具有弱多线程 malloc。）

filesort 操作根据需要使用临时磁盘文件，如果结果集太大而无法放入内存中。某些类型的查询特别适合完全在内存中进行 filesort 操作。例如，优化器可以使用 filesort 来有效地处理内存中的 ORDER BY 操作，而无需临时文件，对于以下形式的查询（和子查询）：

SELECT ... FROM *single_table* ... ORDER BY *non_index_column* [DESC] LIMIT [*M*,]*N*;

这些查询在仅显示较大结果集中的几行的 Web 应用程序中很常见。例如：

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

影响 ORDER BY 优化

对于慢的 ORDER BY 查询，如果不使用 filesort，请尝试降低 max_length_for_sort_data 系统变量的值，以触发 filesort。 （设置此变量值过高的症状是高磁盘活动和低 CPU 活动的组合。）这种技术仅适用于 MySQL 8.0.20 之前。从 8.0.20 开始，由于优化器的更改使其过时且无效，max_length_for_sort_data 已被弃用。

为了提高 ORDER BY 的速度，请检查是否可以让 MySQL 使用索引而不是额外的排序阶段。如果不可能，请尝试以下策略：

• 增加sort_buffer_size变量值。理想情况下，该值应足够大，以使整个结果集适合排序缓冲区（以避免写入磁盘和合并操作）。

  请注意，存储在排序缓冲区中的列值的大小受max_sort_length系统变量值的影响。例如，如果元组存储长字符串列的值，并增加max_sort_length的值，则排序缓冲区元组的大小也会增加，可能需要您增加sort_buffer_size。

  要监视合并临时文件的合并次数，请检查Sort_merge_passes状态变量。

• 增加read_rnd_buffer_size变量值，以便一次读取更多行。

• 将tmpdir系统变量更改为指向具有大量可用空间的专用文件系统。变量值可以列出几个路径，以轮询方式使用；您可以使用此功能将负载分散到几个目录中。在 Unix 上用冒号字符（:）分隔路径，在 Windows 上用分号字符（;）分隔。这些路径应命名位于不同物理磁盘上的文件系统中的目录，而不是同一磁盘上的不同分区。

ORDER BY 执行计划信息可用

使用EXPLAIN（参见 Section 10.8.1，“使用 EXPLAIN 优化查询”输出的Extra列不包含Using filesort，则使用索引，不执行filesort。

• 如果EXPLAIN输出的Extra列包含Using filesort，则未使用索引并执行filesort。

此外，如果执行了filesort，优化器跟踪输出将包括一个filesort_summary块。例如：

"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "peak_memory_used": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

peak_memory_used 表示在排序过程中任意时间点使用的最大内存。这个值可能与 sort_buffer_size 系统变量的值一样大，但不一定。在 MySQL 8.0.12 之前，输出显示的是 sort_buffer_size，表示 sort_buffer_size 的值。（在 MySQL 8.0.12 之前，优化器总是为排序缓冲区分配 sort_buffer_size 字节。从 8.0.12 开始，优化器逐渐分配排序缓冲区内存，从一个小量开始，根据需要逐渐增加，直到 sort_buffer_size 字节。）

sort_mode 值提供有关排序缓冲区中元组内容的信息：

• <sort_key, rowid>：表示排序缓冲区元组是包含排序键值和原始表行的行 ID 的对。元组按排序键值排序，行 ID 用于从表中读取行。

• <sort_key, additional_fields>：表示排序缓冲区元组包含排序键值和查询引用的列。元组按排序键值排序，列值直接从元组中读取。

• <sort_key, packed_additional_fields>：与前一种变体类似，但附加列紧密打包在一起，而不是使用固定长度编码。

EXPLAIN 无法区分优化器是否在内存中执行 filesort。内存中的 filesort 使用可以在优化器跟踪输出中看到。查找 filesort_priority_queue_optimization。有关优化器跟踪的信息，请参阅 MySQL Internals: Tracing the Optimizer。