MySQL8-查询性能调优教程-八-MySQL8 查询性能调优教程（八）二十一、事务事务是报表的老大哥。它们将多个更

MySQL8 查询性能调优教程（八）

原文：MySQL 8 Query Performance Tuning

协议：CC BY-NC-SA 4.0

二十一、事务

事务是报表的老大哥。它们将多个更改组合在一起，无论是在单个语句中还是在几个语句中，因此它们作为一个单元被应用或放弃。大多数情况下，事务只是事后的想法，只是在需要将几个语句一起应用时才考虑。这不是考虑事务的好方法。它们对于确保数据完整性非常重要，如果使用不当，会导致严重的性能问题。

本章通过回顾事务对锁和性能的影响，开始讨论为什么需要从性能的角度认真对待事务。本章的其余部分集中于分析事务，首先使用信息模式中的INNODB_TRX表，然后是 InnoDB 监控器、InnoDB 指标，最后是性能模式。

事务的影响

如果您将事务视为用于分组查询的容器，那么事务可能看起来是一个简单的概念。然而，重要的是要理解，因为事务为查询组提供原子性，所以事务活动的时间越长，与查询相关联的资源被占用的时间就越长，并且事务中完成的工作越多，需要的资源就越多。提交事务之前一直在使用的查询使用了哪些资源？主要的两个是锁和撤销日志。

Tip

InnoDB 支持比读写事务开销更低的只读事务。对于自动提交的单语句事务，InnoDB 将尝试自动确定该语句是否是只读的。对于多语句事务，可以在启动时明确指定它是只读事务:START TRANSACTION READ ONLY;

锁

当查询执行时，它获取锁，并且当您使用默认的事务隔离级别–REPEATABLE READ时，所有的锁都被保留，直到事务被提交。当您使用READ COMMITTED事务隔离级别时，一些锁可能会被释放，但至少那些涉及已更改记录的锁会被保留。锁本身就是一种资源，但是它也需要内存来存储关于锁的信息。对于正常的工作负载来说，您可能不认为这有什么了不起，但是巨大的事务最终会使用如此多的内存，以至于事务失败，并出现ER_LOCK_TABLE_FULL错误:

ERROR: 1206: The total number of locks exceeds the lock table size

从错误日志中记录的警告消息可以看出(更简短地说)，锁所需的内存来自缓冲池。因此，持有的锁越多、时间越长，可用于缓存数据和索引的内存就越少。

Caution

因为使用了所有的锁内存而中止一个事务是四重打击。首先，更新足够多的行以使用足够多的锁内存来触发错误需要一些时间。那项工作被浪费了。第二，由于所需更改的数量，回滚事务可能需要很长时间。第三，当锁内存被使用时，InnoDB 实际上处于只读模式(一些小的事务是可能的)，并且直到回滚完成后锁内存才被释放。第四，缓冲池中几乎没有空间来缓存数据和索引。

该错误之前，错误日志中有一条警告，指出超过 67%的缓冲池用于锁或自适应哈希索引:

2019-07-06T03:23:04.345256Z 10 [Warning] [MY-011958] [InnoDB] Over 67 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Check that your transactions do not set too many row locks. Your buffer pool size is 7 MB. Maybe you should make the buffer pool bigger?. Starting the InnoDB Monitor to print diagnostics, including lock heap and hash index sizes.

该警告之后是 InnoDB monitor 的定期重复输出，因此您可以确定哪些事务是罪魁祸首。事务的 InnoDB monitor 输出将在“InnoDB Monitor”部分讨论。

一种在事务中经常被忽略的锁类型是元数据锁。当一个语句查询一个表时，会获取一个共享的元数据锁，并且该元数据锁会一直保持到事务结束。当一个表上有一个元数据锁时，任何连接都不能对该表执行任何 DDL 语句——包括OPTIMIZE TABLE。如果一个 DDL 语句被一个长时间运行的事务阻塞，它将依次阻塞所有使用该表的新查询。第 22 章将展示一个调查此类问题的例子，包括使用本章中的一些方法。

当事务处于活动状态时，锁被持有。但是，即使事务已经通过撤消日志完成，它仍然会产生影响。

撤消日志

如果您选择回滚事务，则还必须根据需要存储事务期间所做的更改。这很容易理解。更令人惊讶的是，即使一个事务没有进行任何更改，也会使来自其他事务的撤销信息保留下来。当事务需要读视图(一致快照)时会发生这种情况，当使用REPEATABLE READ事务隔离级别时，在事务持续期间就是这种情况。读取视图意味着无论其他事务是否更改数据，事务都将返回与事务开始时间相对应的行数据。为了能够实现这一点，有必要保留在事务生命周期中发生变化的行的旧值。具有读视图的长时间运行的事务是导致巨大撤销日志的最常见原因，在 MySQL 5.7 和更早版本中，这可能意味着ibdata1文件变大了。(在 MySQL 8 中，撤消日志总是存储在单独的可以被截断的撤消表空间中。)

Tip

READ COMMITTED事务隔离级别不太容易出现大的撤销日志，因为读取视图只在查询期间维护。

撤销日志活动部分的大小在历史列表长度中测量。历史列表长度是尚未清除撤消日志的已提交事务的数量。这意味着您不能使用历史列表长度来衡量行更改的总量。它告诉您的是在执行查询时必须考虑的变更链表中有多少个旧行单元(每个事务一个单元)。这个链表越长，找到每一行的正确版本的代价就越大。最后，如果您有一个很大的历史列表，它会严重影响所有查询的性能。

Note

历史列表长度的问题是使用逻辑备份工具创建大型数据库备份的最大问题之一，例如使用单个事务获得一致备份的mysqlpump和mysqldump。如果在备份过程中提交了许多事务，备份可能会导致历史列表变得非常长。

什么构成了一个大的历史列表长度？这方面没有严格的规则，只是越小越好。通常，当列表有几千到一百万个事务时，性能问题就开始出现了，但是当历史列表很长时，它成为瓶颈的点取决于撤消日志中提交的事务和工作负载。

当不再需要最旧的部件时，InnoDB 会在后台自动清除历史列表。有两个选项可以控制清洗，也有两个选项可以影响清洗无法进行时会发生什么。这些选项包括

innodb_purge_batch_size : 每批清除的撤消日志页数。该批次在清除线程之间划分。该选项不应在生产系统上更改。默认值为 300，有效值介于 1 和 5000 之间。
innodb_purge_threads : 并行使用的清除线程数。如果数据更改跨越多个表，那么更高的并行度会很有用。另一方面，如果所有更改都集中在少数几个表上，则首选低值。更改清除线程的数量需要重启 MySQL。默认值为 4，有效值介于 1 和 32 之间。
innodb_max_purge_lag : 当历史列表长度大于innodb_max_purge_lag的值时，会给更改数据的操作增加一个延迟，以降低历史列表的增长速度，但代价是语句延迟增加。默认值为 0，这意味着永远不会添加延迟。有效值为 0–4294967295。
innodb_max_purge_lag_delay : 当历史列表长度大于innodb_max_purge_lag时，可以添加到 DML 查询的最大延迟。

通常没有必要更改这些设置；但是，在特殊情况下，它可能是有用的。如果清除线程跟不上，您可以尝试根据被修改的表的数量来更改清除线程的数量；修改的表越多，清除线程就越有用。当您更改清除线程的数量时，从更改前的基线开始监控效果非常重要，这样您就可以看到更改是否带来了改进。

最大清除延迟选项可用于降低修改数据的 DML 语句的速度。当写入仅限于特定的连接，并且延迟不会导致创建额外的写入线程以保持相同的吞吐量时，此功能非常有用。

您如何监控事务有多长时间，锁使用了多少内存，以及历史列表有多长？您可以使用信息模式、InnoDB 监控器和性能模式来获取这些信息。

INNODB_TRX(消歧义)

信息模式中的INNODB_TRX表是关于 InnoDB 事务的最专门的信息源。它包括诸如事务何时开始、修改了多少行以及持有多少锁之类的信息。INNODB_TRX表也被sys.innodb_lock_waits视图用来提供一些关于锁等待问题中所涉及的事务的信息。表 21-1 汇总了表中的栏目。

表 21-1

信息架构中的列。INNODB_TRX 表

列/数据类型

描述

| | --- | --- | | trx_id``varchar(18) | 事务记录 id。这在引用事务或与 InnoDB 监控器的输出进行比较时非常有用。否则，id 应该被视为纯内部的，没有任何意义。该 id 仅分配给已修改数据或锁定行的事务；仅执行只读SELECT语句的事务将有一个伪 id，如 421124985258256，如果事务开始修改或锁定记录，该 id 将会改变。 | | trx_state``varchar(13) | 事务的状态。这可以是RUNNING、LOCK WAIT、ROLLING BACK和COMMITTING中的一个。 | | trx_started``datetime | 使用系统时区启动事务的时间。 | | trx_requested_lock_id``varchar(105) | 当trx_state为LOCK WAIT时，该列显示事务正在等待的锁的 id。 | | trx_wait_started``datetime | 当trx_state为LOCK WAIT时，该列使用系统时区显示锁定等待开始的时间。 | | trx_weight``bigint unsigned | 根据修改的行数和持有的锁数，衡量事务完成了多少工作。这是用于确定在死锁情况下回滚哪个事务的权重。重量越大，做功越多。 | | trx_mysql_thread_id``bigint unsigned | 执行事务的连接的连接 id(与性能模式threads表中的PROCESSLIST_ID列相同)。 | | trx_query``varchar(1024) | 事务当前执行的查询。如果事务空闲，则查询为NULL。 | | trx_operation_state``varchar(64) | 事务执行的当前操作。即使查询正在执行，这也可能是NULL。 | | trx_tables_in_use``bigint unsigned | 事务使用的表的数量。 | | trx_tables_locked``bigint unsigned | 事务持有行锁的表的数量。 | | trx_lock_structs``bigint unsigned | 事务创建的锁结构的数量。 | | trx_lock_memory_bytes``bigint unsigned | 事务持有的锁使用的内存量(以字节为单位)。 | | trx_rows_locked``bigint unsigned | 事务持有的记录锁的数量。虽然被称为行锁，但它也包括索引锁。 | | trx_rows_modified``bigint unsigned | 事务修改的行数。 | | trx_concurrency_tickets``bigint unsigned | 当innodb_thread_concurrency不为 0 时，在事务必须允许另一个事务执行工作之前，会给该事务分配innodb_concurrency_tickets个可以使用的票证。一张票对应于访问一行。这一栏显示还剩多少票。 | | trx_isolation_level``varchar(16) | 用于事务的事务隔离级别。 | | trx_unique_checks``int | 连接是否启用了unique_checks变量。 | | trx_foreign_key_checks``int | 连接是否启用了foreign_key_checks变量。 | | trx_last_foreign_key_error``varchar(256) | 事务遇到的最后一个(如果有)外键错误的错误消息。 | | trx_adaptive_hash_latched``int | 事务是否锁定了自适应哈希索引的一部分。总共有innodb_adaptive_hash_index_parts个零件。该列实际上是一个布尔值。 | | trx_adaptive_hash_timeout``bigint unsigned | 是否在多个查询中保持对自适应哈希索引的锁定。如果自适应散列索引只有一部分，并且没有争用，那么超时倒计时，当超时达到 0 时，锁被释放。当存在争用或有多个部分时，每次查询后锁总是被释放，超时值为 0。 | | trx_is_read_only``int | 该事务是否为只读事务。通过显式声明，事务可以是只读的，或者对于启用了autocommit的单语句事务，InnoDB 可以检测到查询将只读取数据。 | | trx_autocommit_non_locking``int | 当事务是单语句非锁定的SELECT并且autocommit选项被启用时，该列被设置为 1。当这个列和trx_is_read_only都为 1 时，InnoDB 可以优化事务以减少开销。 |

从INNODB_TRX表中获得的信息可以确定哪些事务具有最大的影响。清单 21-1 显示了两个事务返回信息的例子。

mysql> SELECT *
         FROM information_schema.INNODB_TRX\G
*************************** 1\. row ***************************
                    trx_id: 5897
                 trx_state: RUNNING
               trx_started: 2019-07-06 11:11:12
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 4552416
       trx_mysql_thread_id: 10
                 trx_query: UPDATE db1.t1 SET val1 = 4
       trx_operation_state: updating or deleting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 7919
     trx_lock_memory_bytes: 1417424
           trx_rows_locked: 4552415
         trx_rows_modified: 4544497
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2\. row ***************************
                    trx_id: 421624759431440
                 trx_state: RUNNING
               trx_started: 2019-07-06 11:46:55
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 8
                 trx_query: SELECT COUNT(*) FROM db1.t1
       trx_operation_state: counting records
         trx_tables_in_use: 1
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 1
trx_autocommit_non_locking: 1
2 rows in set (0.0023 sec)

Listing 21-1Example output of the INNODB_TRX table

第一行显示了修改数据的事务示例。在检索信息时，已经修改了 4，544，497 行，并且还有一点记录锁。您还可以看到事务仍然在主动执行一个查询(一个UPDATE语句)。

第二行是在启用了autocommit的情况下执行的SELECT语句的示例。由于启用了自动提交，事务中只能有一个语句(显式的START TRANSACTION禁用自动提交)。trx_query列显示它是一个没有任何锁定子句的SELECT COUNT(*)查询，因此它是一个只读语句。这意味着 InnoDB 可以跳过一些事情，比如为事务准备锁定和撤销信息，从而减少事务的开销。trx_autocommit_non_locking列被设置为 1 以反映这一点。

您应该担心哪些事务取决于系统上的预期工作负载。如果您有一个 OLAP 工作负载，预计会有相对长时间运行的SELECT查询。对于纯粹的 OLTP 工作负载，任何运行时间超过几秒钟并修改多行的事务都可能是出现问题的迹象。例如，要查找超过一分钟的事务，可以使用以下查询:

SELECT *
  FROM information_schema.INNODB_TRX
 WHERE trx_started < NOW() - INTERVAL 1 MINUTE;

与INNODB_TRX表相关的是 InnoDB 监控器中的事务列表。

InnoDB 监控器

InnoDB monitor 是 InnoDB information 的一种瑞士军刀，也包含有关事务的信息。InnoDB 监控器输出中的TRANSACTIONS部分专用于事务信息。该信息不仅包括事务列表，还包括历史列表长度。清单 21-2 显示了 InnoDB monitor 的一个摘录，其中的事务部分的示例取自INNODB_TRX表的上一个输出之后。

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1\. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2019-07-06 11:46:58 0x7f7728f69700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
...
------------
TRANSACTIONS
------------
Trx id counter 5898
Purge done for trx's n:o < 5894 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421624759429712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421624759428848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5897, ACTIVE 2146 sec updating or deleting
mysql tables in use 1, locked 1
7923 lock struct(s), heap size 1417424, 4554508 row lock(s), undo log entries 4546586
MySQL thread id 10, OS thread handle 140149617817344, query id 25 localhost 127.0.0.1 root updating
UPDATE db1.t1 SET val1 = 4

Listing 21-2Transaction information from the InnoDB monitor

TRANSACTIONS部分的顶部显示了事务 id 计数器的当前值，后面是已经从撤销日志中清除的信息。它显示事务 id 小于 5894 的撤消日志已被清除。该清除越晚，历史列表的长度(在该部分的第三行)就越长。从 InnoDB monitor 输出中读取历史列表长度是获取历史列表长度的传统方法。在下一节中，将展示如何在用于监控目的时以更好的方式获取值。

该部分的其余部分是事务列表。注意，虽然输出是用与在INNODB_TRX中找到的相同的两个活动事务生成的，但是事务列表只包括一个活动事务(用于UPDATE语句的事务)。在 MySQL 5.7 和更高版本中，只读非锁定事务不包括在 InnoDB monitor 事务列表中。因此，如果需要包含所有活动的事务，最好使用INNODB_TRX表。

如前所述，还有一种方法可以获得历史列表的长度。为此，您需要使用 InnoDB 指标。

INNODB_METRICS 和 sys.metrics

InnoDB monitor 报告对于数据库管理员了解 InnoDB 中正在发生的事情非常有用，但是对于监控来说，它的用处就没有那么大了，因为它需要进行解析，以监控可以使用的方式获取数据。在本章的前面，您已经看到了如何从information_schema.INNODB_TRX表中获得关于事务的信息，但是像历史列表长度这样的指标又如何呢？

InnoDB 指标系统包括几个指标，在information_schema.INNODB_METRICS视图中显示关于事务的信息。这些指标都位于事务子系统中。清单 21-3 显示了一个事务度量的列表，无论它们是否默认启用，以及一个简短的注释来解释度量的内容。

mysql> SELECT NAME, COUNT, STATUS, COMMENT
         FROM information_schema.INNODB_METRICS
        WHERE SUBSYSTEM = 'transaction'\G
*************************** 1\. row ***************************
   NAME: trx_rw_commits
  COUNT: 0
 STATUS: disabled
COMMENT: Number of read-write transactions  committed
*************************** 2\. row ***************************
   NAME: trx_ro_commits
  COUNT: 0
 STATUS: disabled
COMMENT: Number of read-only transactions committed
*************************** 3\. row ***************************
   NAME: trx_nl_ro_commits
  COUNT: 0
 STATUS: disabled
COMMENT: Number of non-locking auto-commit read-only transactions committed
*************************** 4\. row ***************************
   NAME: trx_commits_insert_update
  COUNT: 0
 STATUS: disabled
COMMENT: Number of transactions committed with inserts and updates
*************************** 5\. row ***************************
   NAME: trx_rollbacks
  COUNT: 0
 STATUS: disabled
COMMENT: Number of transactions rolled back
*************************** 6\. row ***************************
   NAME: trx_rollbacks_savepoint
  COUNT: 0
 STATUS: disabled
COMMENT: Number of transactions rolled back to savepoint
*************************** 7\. row ***************************
   NAME: trx_rollback_active
  COUNT: 0
 STATUS: disabled
COMMENT: Number of resurrected active transactions rolled back
*************************** 8\. row ***************************
   NAME: trx_active_transactions
  COUNT: 0
 STATUS: disabled
COMMENT: Number of active transactions
*************************** 9\. row ***************************
   NAME: trx_on_log_no_waits
  COUNT: 0
 STATUS: disabled
COMMENT: Waits for redo during transaction commits
*************************** 10\. row ***************************
   NAME: trx_on_log_waits
  COUNT: 0
 STATUS: disabled
COMMENT: Waits for redo during transaction commits
*************************** 11\. row ***************************
   NAME: trx_on_log_wait_loops

  COUNT: 0
 STATUS: disabled
COMMENT: Waits for redo during transaction commits
*************************** 12\. row ***************************
   NAME: trx_rseg_history_len
  COUNT: 45
 STATUS: enabled
COMMENT: Length of the TRX_RSEG_HISTORY list
*************************** 13\. row ***************************
   NAME: trx_undo_slots_used
  COUNT: 0
 STATUS: disabled
COMMENT: Number of undo slots used
*************************** 14\. row ***************************
   NAME: trx_undo_slots_cached
  COUNT: 0
 STATUS: disabled
COMMENT: Number of undo slots cached
*************************** 15\. row ***************************
   NAME: trx_rseg_current_size
  COUNT: 0
 STATUS: disabled
COMMENT: Current rollback segment size in pages
15 rows in set (0.0403 sec)

Listing 21-3InnoDB metrics related to transactions

这些指标中最重要的是trx_rseg_history_len，它是历史列表长度。这也是默认情况下启用的唯一指标。与提交和回滚相关的指标可用于确定您拥有多少读写、只读和非锁定只读事务，以及它们提交和回滚的频率。许多回滚表明存在问题。如果您怀疑重做日志是一个瓶颈，那么可以使用trx_on_log_%指标来衡量在事务提交期间有多少事务在等待重做日志。

Tip

使用innodb_monitor_enable选项启用 InnoDB 指标，使用innodb_monitor_disable禁用它们。这可以动态完成。

查询 InnoDB 指标的另一种方便的方法是使用sys.metrics视图，其中也包括全局状态变量。清单 21-4 展示了一个使用sys.metrics视图获取当前值以及指标是否启用的例子。

mysql> SELECT Variable_name AS Name,
              Variable_value AS Value,
              Enabled
         FROM sys.metrics
        WHERE Type = 'InnoDB Metrics - transaction';
+---------------------------+-------+---------+
| Name                      | Value | Enabled |
+---------------------------+-------+---------+
| trx_active_transactions   | 0     | NO      |
| trx_commits_insert_update | 0     | NO      |
| trx_nl_ro_commits         | 0     | NO      |
| trx_on_log_no_waits       | 0     | NO      |
| trx_on_log_wait_loops     | 0     | NO      |
| trx_on_log_waits          | 0     | NO      |
| trx_ro_commits            | 0     | NO      |
| trx_rollback_active       | 0     | NO      |
| trx_rollbacks             | 0     | NO      |
| trx_rollbacks_savepoint   | 0     | NO      |
| trx_rseg_current_size     | 0     | NO      |
| trx_rseg_history_len      | 45    | YES     |
| trx_rw_commits            | 0     | NO      |
| trx_undo_slots_cached     | 0     | NO      |
| trx_undo_slots_used       | 0     | NO      |
+---------------------------+-------+---------+
15 rows in set (0.0152 sec)

Listing 21-4Using the sys.metrics view to get the transaction metrics

这表明历史列表长度为 45，这是一个很低的值，因此撤销日志几乎没有开销。其余指标被禁用。

迄今为止，关于事务信息的讨论一直是关于所有事务或单个事务的汇总统计数据。如果您想更深入地了解事务完成了什么工作，您需要使用性能模式。

性能模式事务

性能模式支持 MySQL 5.7 和更高版本中的事务监控，并且在 MySQL 8 中默认启用。在性能模式中，除了与 XA 事务和保存点相关的事务细节之外，没有多少事务细节是不能从信息模式中的INNODB_TRX表获得的。但是，Performance Schema 事务事件的优势在于，您可以将它们与其他事件类型(如语句)相结合，以获取有关事务所做工作的信息。这是本节的主要重点。此外，性能模式提供了带有聚合统计信息的汇总表。

事务事件及其声明

性能模式中用于调查事务的主要表是事务事件表。有三个表格记录当前或最近的事务:events_transactions_current、events_transactions_history和events_transactions_history_long。它们具有表 21-2 中总结的列。

表 21-2

非汇总事务事件表的列

列/数据类型

描述

| | --- | --- | | THREAD_ID``bigint unsigned | 执行事务的连接的性能架构线程 id。 | | EVENT_ID``bigint unsigned | 事件的事件 id。您可以使用事件 id 对线程的事件进行排序，或者将事件 id 作为外键与事件表之间的线程 id 一起使用。 | | END_EVENT_ID``bigint unsigned | 事务完成时的事件 id。如果事件 id 为NULL，则事务仍在进行。 | | EVENT_NAME``varchar(128) | 事务事件名称。目前，该列的值始终为transaction。 | | STATE``enum | 事务的状态。可能的值有ACTIVE、COMMITTED,和ROLLED BACK。 | | TRX_ID``bigint unsigned | 这是当前未使用的，将始终是NULL。 | | GTID``varchar(64) | 事务记录的 GTID。当自动确定 GTID 时(通常)，返回AUTOMATIC。这与执行事务的连接的gtid_next变量相同。 | | XID_FORMAT_ID``int | 对于 XA 事务，格式 id。 | | XID_GTRID``varchar(130) | 对于 XA 事务，是 gtrid 值。 | | XID_BQUAL``varchar(130) | 对于 XA 事务，bqual 值。 | | XA_STATE``varchar(64) | 对于 XA 事务，是事务的状态。这可以是ACTIVE、IDLE、PREPARED、ROLLED BACK或COMMITTED。 | | SOURCE``varchar(64) | 记录事件的源代码文件和行号。 | | TIMER_START``bigint unsigned | 事件开始的时间，以皮秒为单位。 | | TIMER_END``bigint unsigned | 事件完成的时间，以皮秒为单位。如果事务尚未完成，则该值对应于当前时间。 | | TIMER_WAIT``bigint unsigned | 执行事件所用的总时间(皮秒)。如果事件尚未完成，则该值对应于事务处于活动状态的时间。 | | ACCESS_MODE``enum | 事务处于只读(READ ONLY)还是读写(READ WRITE)模式。 | | ISOLATION_LEVEL``varchar(64) | 事务的事务隔离级别。 | | AUTOCOMMIT``enum | 事务是否基于autocommit选项自动提交，以及显式事务是否已经开始。可能的值是NO和YES。 | | NUMBER_OF_SAVEPOINTS``bigint unsigned | 事务中创建的保存点数。 | | NUMBER_OF_ROLLBACK_TO_SAVEPOINT``bigint unsigned | 事务回滚到保存点的次数。 | | NUMBER_OF_RELEASE_SAVEPOINT``bigint unsigned | 事务释放保存点的次数。 | | OBJECT_INSTANCE_BEGIN``bigint unsigned | 该字段目前未被使用，并且总是被设置为NULL。 | | NESTING_EVENT_ID``bigint unsigned | 触发事务的事件的事件 id。 | | NESTING_EVENT_TYPE``enum | 触发事务的事件的事件类型。 |

如果您正在处理 XA 事务，那么当您需要恢复一个事务时，事务事件表是非常有用的，因为格式 id、gtrid 和 bqual 值可以直接从表中获得，这与必须解析输出的XA RECOVER语句不同。同样，如果您使用保存点，您可以获得保存点使用情况的统计数据。除此之外，该信息与INNODB_TRX表中的信息非常相似。

举一个使用events_transactions_current表的例子，您可以启动两个事务。第一个事务是更新几个城市人口的普通事务:

START TRANSACTION;
UPDATE world.city SET Population = 5200000 WHERE ID = 130;
UPDATE world.city SET Population = 4900000 WHERE ID = 131;
UPDATE world.city SET Population = 2400000 WHERE ID = 132;
UPDATE world.city SET Population = 2000000 WHERE ID = 133;

第二个事务是 XA 事务:

XA START 'abc', 'def', 1;
UPDATE world.city SET Population = 900000 WHERE ID = 3805;

清单 21-5 显示了events_transactions_current表的输出示例，列出了当前活动的事务。

mysql> SELECT *
         FROM performance_schema.events_transactions_current
        WHERE STATE = 'ACTIVE'\G
*************************** 1\. row ***************************
                      THREAD_ID: 54
                       EVENT_ID: 39
                   END_EVENT_ID: NULL
                     EVENT_NAME: transaction
                          STATE: ACTIVE
                         TRX_ID: NULL
                           GTID: AUTOMATIC
                  XID_FORMAT_ID: NULL
                      XID_GTRID: NULL
                      XID_BQUAL: NULL
                       XA_STATE: NULL
                         SOURCE: transaction.cc:219
                    TIMER_START: 488967975158077184
                      TIMER_END: 489085567376530432
                     TIMER_WAIT: 117592218453248
                    ACCESS_MODE: READ WRITE
                ISOLATION_LEVEL: REPEATABLE READ
                     AUTOCOMMIT: NO
           NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
    NUMBER_OF_RELEASE_SAVEPOINT: 0
          OBJECT_INSTANCE_BEGIN: NULL
               NESTING_EVENT_ID: 38
             NESTING_EVENT_TYPE: STATEMENT
*************************** 2\. row ***************************
                      THREAD_ID: 57
                       EVENT_ID: 10
                   END_EVENT_ID: NULL
                     EVENT_NAME: transaction
                          STATE: ACTIVE
                         TRX_ID: NULL
                           GTID: AUTOMATIC

                  XID_FORMAT_ID: 1
                      XID_GTRID: abc
                      XID_BQUAL: def
                       XA_STATE: ACTIVE
                         SOURCE: transaction.cc:219
                    TIMER_START: 488977176010232448
                      TIMER_END: 489085567391481984
                     TIMER_WAIT: 108391381249536
                    ACCESS_MODE: READ WRITE
                ISOLATION_LEVEL: REPEATABLE READ
                     AUTOCOMMIT: NO
           NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
    NUMBER_OF_RELEASE_SAVEPOINT: 0
          OBJECT_INSTANCE_BEGIN: NULL
               NESTING_EVENT_ID: 9
             NESTING_EVENT_TYPE: STATEMENT
2 rows in set (0.0007 sec)

Listing 21-5Using the events_transactions_current table

第 1 行中的事务是常规事务，而第 2 行中的事务是 XA 事务。两个事务都是由一个语句启动的，这可以从嵌套事件类型中看出。如果想找到触发事务的语句，可以使用它来查询events_statements_history表，如下所示

mysql> SELECT SQL_TEXT
         FROM performance_schema.events_statements_history
        WHERE THREAD_ID = 54
              AND EVENT_ID = 38\G
*************************** 1\. row ***************************
SQL_TEXT: START TRANSACTION
1 row in set (0.0009 sec)

这表明由THREAD_ID = 54执行的事务是使用START TRANSACTION语句开始的。因为events_statements_history表只包括连接的最后十条语句，所以不能保证启动事务的语句仍然在历史表中。当autocommit被禁用时，如果您正在查看一个单语句事务或第一条语句(当它仍在执行时),您将需要查询events_statements_current表。

事务和语句之间的关系也是相反的。给定一个事务事件 id 和线程 id，您可以使用语句事件历史和当前表来查询为该事务执行的最后十条语句。清单 21-6 显示了THREAD_ID = 54和事务EVENT_ID = 39的示例(来自清单 21-5 的第 1 行)，其中包含了开始事务的语句和后续语句。

mysql> SET @thread_id = 54,
           @event_id = 39,
           @nesting_event_id = 38;

mysql> SELECT EVENT_ID, SQL_TEXT,
              FORMAT_PICO_TIME(TIMER_WAIT) AS Latency,
              IF(END_EVENT_ID IS NULL, 'YES', 'NO') AS IsCurrent
         FROM ((SELECT EVENT_ID, END_EVENT_ID,
                       TIMER_WAIT,
                       SQL_TEXT, NESTING_EVENT_ID,
                       NESTING_EVENT_TYPE
                  FROM performance_schema.events_statements_current
                 WHERE THREAD_ID = @thread_id
               ) UNION (
                SELECT EVENT_ID, END_EVENT_ID,
                       TIMER_WAIT,
                       SQL_TEXT, NESTING_EVENT_ID,
                       NESTING_EVENT_TYPE
                  FROM performance_schema.events_statements_history
                 WHERE THREAD_ID = @thread_id
               )
              ) events
        WHERE (NESTING_EVENT_TYPE = 'TRANSACTION'
               AND NESTING_EVENT_ID = @event_id)
              OR EVENT_ID = @nesting_event_id
        ORDER BY EVENT_ID DESC\G
*************************** 1\. row ***************************
 EVENT_ID: 43
 SQL_TEXT: UPDATE city SET Population = 2000000 WHERE ID = 133
  Latency: 291.01 us
IsCurrent: NO
*************************** 2\. row ***************************
 EVENT_ID: 42
 SQL_TEXT: UPDATE city SET Population = 2400000 WHERE ID = 132
  Latency: 367.59 us

IsCurrent: NO
*************************** 3\. row ***************************
 EVENT_ID: 41
 SQL_TEXT: UPDATE city SET Population = 4900000 WHERE ID = 131
  Latency: 361.03 us
IsCurrent: NO
*************************** 4\. row ***************************
 EVENT_ID: 40
 SQL_TEXT: UPDATE city SET Population = 5200000 WHERE ID = 130
  Latency: 399.32 us
IsCurrent: NO
*************************** 5\. row ***************************
 EVENT_ID: 38
 SQL_TEXT: START TRANSACTION
  Latency: 97.37 us
IsCurrent: NO
9 rows in set (0.0012 sec)

Listing 21-6Finding the last ten statements executed in a transaction

子查询(一个派生表)从events_statements_current和events_statements_history表中找到线程的所有语句事件。有必要包括当前事件，因为可能有正在进行的事务报表。通过作为事务的子事务或事务的嵌套事件来过滤语句(EVENT_ID = 38)。这将包括从启动事务的语句开始的所有语句。如果有正在进行的陈述，则最多有 11 个陈述，否则最多有 10 个。

END_EVENT_ID用于确定语句当前是否正在执行，使用EVENT_ID对语句进行反向排序，因此最新的语句在第 1 行，最老的(START TRANSACTION语句)在第 5 行。

这种类型的查询不仅对调查仍在执行查询的事务有用。当您遇到一个空闲事务，并且想知道该事务在被放弃之前做了什么时，它也非常有用。寻找活动事务的另一种相关方法是使用sys.session视图，该视图使用events_transactions_current表来包含每个连接的事务状态信息。清单 21-7 显示了一个查询活动事务的例子，不包括执行查询的连接行。

mysql> SELECT *
         FROM sys.session
        WHERE trx_state = 'ACTIVE'
              AND conn_id <> CONNECTION_ID()\G
*************************** 1\. row ***************************
                thd_id: 54
               conn_id: 16
                  user: mysqlx/worker
                    db: world
               command: Sleep
                 state: NULL
                  time: 690
     current_statement: UPDATE world.city SET Population = 2000000 WHERE ID = 133
     statement_latency: NULL
              progress: NULL
          lock_latency: 281.76 ms
         rows_examined: 341
             rows_sent: 341
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: UPDATE world.city SET Population = 2000000 WHERE ID = 133
last_statement_latency: 391.80 ms

        current_memory: 2.35 MiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 11.49 m
             trx_state: ACTIVE
        trx_autocommit: NO
                   pid: 23376
          program_name: mysqlsh
*************************** 2\. row ***************************
                thd_id: 57
               conn_id: 18
                  user: mysqlx/worker
                    db: world
               command: Sleep
                 state: NULL
                  time: 598
     current_statement: UPDATE world.city SET Population = 900000 WHERE ID = 3805
     statement_latency: NULL
              progress: NULL
          lock_latency: 104.00 us
         rows_examined: 1
             rows_sent: 0
         rows_affected: 1
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: UPDATE world.city SET Population = 900000 WHERE ID = 3805
last_statement_latency: 40.21 ms
        current_memory: 344.76 KiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 11.32 m
             trx_state: ACTIVE
        trx_autocommit: NO
                   pid: 25836
          program_name: mysqlsh
2 rows in set (0.0781 sec)

Listing 21-7Finding active transactions with sys.session

这表明第一行中的事务已经活动了 11 分钟以上，并且距离上次执行查询已经过去了 690 秒(11.5 分钟)(您的值会有所不同)。last_statement可以用来确定连接执行的最后一个查询。这是一个被放弃的事务的例子，它阻止了 InnoDB 清除它的撤销日志。放弃事务的最常见原因是数据库管理员交互地启动了一个事务，然后分心了，或者是autocommit被禁用了，没有意识到一个事务已经启动了。

Caution

如果您禁用了autocommit，请始终注意在工作结束时提交或回滚。一些连接器默认禁用autocommit,所以请注意您的应用可能没有使用服务器默认设置。

您可以回滚事务以避免更改任何数据。对于第一笔(正常)事务:

mysql> ROLLBACK;
Query OK, 0 rows affected (0.0841 sec)

对于 XA 事务:

mysql> XA END 'abc', 'def', 1;
Query OK, 0 rows affected (0.0003 sec)

mysql> XA ROLLBACK 'abc', 'def', 1;
Query OK, 0 rows affected (0.0759 sec)

性能模式表对分析事务有用的另一种方式是使用汇总表来获得聚合数据。

事务汇总表

与可以用来获得所执行语句的报告的语句汇总表一样，也可以使用事务汇总表来分析事务的使用情况。虽然它们不像它们的对应物那样有用，但是它们确实提供了对以不同方式使用事务的连接和账户的洞察。

共有五个事务摘要表，可以按帐户、主机、线程或用户对数据进行全局分组。所有摘要也按事件名称分组，但由于目前只有一个事务事件(transaction)，所以它是一个零操作。这些桌子是

events_transactions_summary_global_by_event_name : 汇总所有事务。该表中只有一行。
events_transactions_summary_by_account_by_event_name : 按用户名和主机名分组的事务。
events_transactions_summary_by_host_by_event_name : 按账户主机名分组的事务。
events_transactions_summary_by_thread_by_event_name : 按线程分组的事务。仅包括当前存在的线程。
events_transactions_summary_by_user_by_event_name : 按账户用户名部分分组的事件。

每个表都包括对事务统计信息进行分组的列和三组列:总计、读写事务和只读事务。对于这三组列中的每一组，都有事务总数以及总延迟、最小延迟、平均延迟和最大延迟。清单 21-8 显示了来自events_transactions_summary_global_by_event_name表的数据示例。

mysql> SELECT *
         FROM performance_schema.events_transactions_summary_global_by_event_name\G
*************************** 1\. row ***************************
          EVENT_NAME: transaction
          COUNT_STAR: 1274
      SUM_TIMER_WAIT: 13091950115512576
      MIN_TIMER_WAIT: 7293440
      AVG_TIMER_WAIT: 10276255661056
      MAX_TIMER_WAIT: 11777025727144832
    COUNT_READ_WRITE: 1273
SUM_TIMER_READ_WRITE: 13078918924805888
MIN_TIMER_READ_WRITE: 7293440
AVG_TIMER_READ_WRITE: 10274091697408
MAX_TIMER_READ_WRITE: 11777025727144832
     COUNT_READ_ONLY: 1
 SUM_TIMER_READ_ONLY: 13031190706688
 MIN_TIMER_READ_ONLY: 13031190706688
 AVG_TIMER_READ_ONLY: 13031190706688
 MAX_TIMER_READ_ONLY: 13031190706688
1 row in set (0.0005 sec)

Listing 21-8The events_transactions_summary_global_by_event_name table

当您研究输出中有多少事务，尤其是读写事务时，您可能会感到惊讶。请记住，在查询 InnoDB 表时，即使您没有明确指定事务，所有事情都是事务。因此，即使一个简单的查询单行的SELECT语句也算作一个事务。关于读写事务和只读事务之间的分布，只有当您显式地以只读方式启动事务时，性能模式才会将其视为只读:

START TRANSACTION READ ONLY;

当 InnoDB 确定自动提交的单语句事务可以被视为只读事务时，它仍然会计入性能模式中的读写统计数据。

摘要

事务是数据库中的一个重要概念。它们有助于确保您可以将更改作为一个单元应用到几行，并且可以选择是应用更改还是回滚更改。

本章一开始讨论了为什么了解如何使用事务很重要。虽然它们本身可以被认为是更改的容器，但锁会一直保持到事务被提交或回滚，并且它们可以阻止撤消日志被清除。锁和大量撤消日志都会影响查询的性能，即使它们不是在导致大量锁或大量撤消日志的事务中执行的。锁使用来自缓冲池的内存，因此可用于缓存数据和索引的内存较少。根据历史列表长度来衡量，大量的撤销日志意味着在 InnoDB 执行语句时必须考虑更多的行版本。

本章的其余部分讨论了如何分析正在进行的和过去的事务。信息模式中的INNODB_TRX表是正在进行的事务的最佳信息源。InnoDB monitor 和 InnoDB metrics 对此进行了补充。对于 XA 事务和使用保存点的事务，或者当您需要调查哪些语句作为事务的一部分被执行时，您需要使用性能模式事务事件表。性能模式还包括一些汇总表，您可以使用这些表来获得关于谁在读写和只读事务上花费时间的更多信息。

锁在事务讨论中扮演了重要的角色。下一章将展示如何分析一系列的锁问题。

二十二、诊断锁的争用

在第 18 章中，你被介绍到了 MySQL 中的锁的世界。如果你还没有读过第 18 章的话，强烈建议你现在就去读，因为这一章是紧密相关的。如果你已经读过一段时间了，你甚至可能想刷新一下你的记忆。锁定问题是性能问题的常见原因之一，其影响可能非常严重。在最坏的情况下，查询可能会失败，连接会堆积起来，因此无法建立新的连接。因此，了解如何调查锁定问题并修复这些问题非常重要。

本章将讨论四类锁问题:

清空锁
元数据和模式锁
记录级锁，包括间隙锁
僵局

每一类锁都使用不同的技术来确定锁争用的原因。当您阅读示例时，您应该记住，可以使用类似的技术来调查与示例不完全匹配的锁问题。

对于每个锁类别，讨论分为六个部分:

**症状:**这描述了您如何识别遇到了这种锁定问题。
**原因:**您遇到这种锁定问题的根本原因。这与第 18 章中对锁的一般性讨论有关。
**设置:**这包括设置锁定问题的步骤，如果你想自己尝试的话。因为锁争用需要多个连接，所以提示符，例如Connection 1>，用于告诉哪个连接应该用于哪个语句。如果您希望在调查过程中获得的信息不会比在真实案例中获得的更多，那么您可以跳过这一部分，在完成调查后再回头查看。
**调查:**调查的细节。这利用了第 18 章的“监控锁”一节。
**解决方案:**如何解决即时锁定问题，从而最大限度地减少由此导致的停机。
**预防:**讨论如何减少遇到问题的机会。这与第 18 章中的“减少锁定问题”一节密切相关。

说得够多了，首先要讨论的锁类别是齐平锁。

清空锁

MySQL 中常见的锁问题之一是刷新锁。当这个问题发生时，用户通常会抱怨查询没有返回，监控可能会显示查询越积越多，最终 MySQL 将耗尽连接。关于刷新锁的问题有时也是最难调查的锁问题之一。

症状

flush lock 问题的主要症状是数据库陷入停滞，所有使用部分或全部表的新查询都要等待 flush lock。要寻找的迹象包括:

新查询的查询状态是“等待表刷新”这可能发生在所有新查询中，也可能只发生在访问特定表的查询中。
越来越多的连接被创建。
最终，由于 MySQL 失去连接，新的连接会失败。新连接收到的错误为ER_CON_COUNT_ERROR:使用经典 MySQL 协议(默认端口 3306)时“错误 1040 (HY000):连接过多”或使用 X 协议(默认端口 33060)时“MySQL 错误 5011:无法打开会话”。
至少有一个查询的运行时间晚于最早的刷新锁请求。
进程列表中可能会有一个FLUSH TABLES语句，但并不总是这样。
当FLUSH TABLES语句等待lock_wait_timeout时，出现ER_LOCK_WAIT_TIMEOUT错误:ERROR: 1205: Lock wait timeout exceeded; try restarting transaction。因为lock_wait_timeout的默认值是 365 天，所以只有在超时时间减少的情况下，这种情况才有可能发生。
如果您使用默认模式集连接到mysql命令行客户端，那么在您到达提示符之前，连接可能会挂起。如果在连接打开的情况下更改默认模式，也会发生同样的情况。

Tip

如果您使用禁用收集自动完成信息的-A选项启动客户端，则不会出现mysql命令行客户端阻塞的问题。更好的解决方案是使用 MySQL Shell，它以一种不会因刷新锁而阻塞的方式获取自动完成信息。

如果您看到这些症状，是时候了解是什么导致了锁定问题。

原因

当一个连接请求刷新一个表时，它要求关闭对该表的所有引用，这意味着没有活动查询可以使用该表。因此，当刷新请求到达时，它必须等待所有使用要刷新的表的查询完成。请注意，除非您明确指定要刷新哪些表，否则必须完成的只是查询，而不是整个事务。显然，所有表都被刷新的情况是最严重的，例如由于FLUSH TABLES WITH READ LOCK，因为这意味着所有活动查询必须在 flush 语句可以继续之前完成。

当等待刷新锁成为一个问题时，这意味着有一个或多个查询阻止了FLUSH TABLES语句获得刷新锁。由于FLUSH TABLES语句需要一个排他锁，因此它会阻止后续查询获取它们需要的共享锁。

在备份过程需要刷新所有表并获得读锁以创建一致备份的情况下，此问题经常出现。

当FLUSH TABLES语句超时或被终止，但后续查询没有继续进行时，可能会出现一种特殊情况。出现这种情况是因为低级表定义缓存(TDC)版本锁没有被释放。这种情况可能会引起混淆，因为后续查询仍在等待表刷新的原因并不明显。

设置

将要调查的锁定情况涉及三个连接(不包括用于调查的连接)。第一个连接执行慢速查询，第二个连接使用读锁刷新所有表，最后一个连接执行快速查询。这些声明是

Connection 1> SELECT city.*, SLEEP(180) FROM world.city WHERE ID = 130;

Connection 2> FLUSH TABLES WITH READ LOCK;

Connection 3> SELECT * FROM world.city WHERE ID = 3805;

在第一个查询中使用SLEEP(180)意味着您有三分钟(180 秒)的时间来执行另外两个查询并执行调查。如果你想要更长的时间，你可以增加睡眠的持续时间。你现在可以开始调查了。

调查

对刷新锁的调查要求您查看实例上运行的查询列表。与其他锁争用不同，没有性能模式表或 InnoDB monitor 报告可用于直接查询阻塞查询。

清单 22-1 显示了使用sys.session视图的输出示例。使用获取查询列表的替代方法将产生类似的结果。线程和连接 id 以及语句延迟会有所不同。

mysql> SELECT thd_id, conn_id, state,
              current_statement,
              statement_latency
         FROM sys.session
        WHERE command = 'Query'\G
*************************** 1\. row ***************************
           thd_id: 30
          conn_id: 9
            state: User sleep
current_statement: SELECT city.*, SLEEP(180) FROM city WHERE ID = 130
statement_latency: 49.97 s
*************************** 2\. row ***************************
           thd_id: 53
          conn_id: 14
            state: Waiting for table flush
current_statement: FLUSH TABLES WITH READ LOCK
statement_latency: 44.48 s
*************************** 3\. row ***************************
           thd_id: 51
          conn_id: 13
            state: Waiting for table flush
current_statement: SELECT * FROM world.city WHERE ID = 3805
statement_latency: 41.93 s
*************************** 4\. row ***************************
           thd_id: 29
          conn_id: 8
            state: NULL
current_statement: SELECT thd_id, conn_id, state, ... ession WHERE command = 'Query'
statement_latency: 56.13 ms
4 rows in set (0.0644 sec)

Listing 22-1Investigating flush lock contention using sys.session

输出中有四个查询。默认情况下，sys.session和sys.processlist视图根据执行时间以降序对查询进行排序。这使得调查类似围绕刷新锁的争用这样的问题变得容易，在查找原因时，查询时间是要考虑的主要因素。

您开始寻找FLUSH TABLES语句(稍后将讨论没有FLUSH TABLES语句的情况)。在这种情况下，那就是thd_id = 53(第二排)。注意，FLUSH语句的状态是“等待表刷新”然后查找已经运行了较长时间的查询。在这种情况下，只有一个查询:带有thd_id = 30的查询。这是阻止FLUSH TABLES WITH READ LOCK完成的查询。通常，可能有不止一个查询。

剩下的两个查询是被FLUSH TABLES WITH READ LOCK阻塞的查询和获取输出的查询。前三个查询一起构成了一个长时间运行的查询阻塞一个FLUSH TABLES语句的典型例子，该语句又阻塞了其他查询。

您还可以从 MySQL Workbench 获取进程列表，在某些情况下，还可以从您的监控解决方案中获取。图 22-1 展示了如何从 MySQL Workbench 获取进程列表。

img/484666_1_En_22_Fig1_HTML.jpg

图 22-1

显示 MySQL Workbench 中的客户端连接

要在 MySQL Workbench 中获得进程列表报告，请在屏幕左侧的导航器窗格中选择管理下的客户端连接项。您不能选择要包括哪些列，并且为了使文本可读，屏幕截图中只包括报告的一部分。 Id 列对应sys.session输出中的conn_id，而线程(最右边一列)对应thd_id。完整的截图作为figure_22_1_workbench_flush_lock.png收录在本书的 GitHub 知识库中。

图 22-2 显示了来自 MySQL 企业监控器(MEM)的进程报告的例子。

img/484666_1_En_22_Fig2_HTML.jpg

图 22-2

MEM 冲水闸调查流程报告

在各个实例的指标菜单项下可以找到进程报告。您可以选择要在输出中包含的列。在本书的 GitHub 知识库中可以找到一个包含更多细节的报告示例figure_22_2_mem_flush_lock.png。

类似 MySQL Workbench 和 MySQL Enterprise Monitor 中的报告的一个优点是，它们使用现有的连接来创建报告。在锁问题导致所有连接都被使用的情况下，使用监控解决方案获得查询列表是非常宝贵的。

如前所述，FLUSH TABLES语句可能并不总是出现在查询列表中。仍然有查询等待刷新表的原因是低级 TDC 版本锁。调查的原则保持不变，但它似乎令人困惑。清单 22-2 展示了这样一个例子，使用相同的设置，但是在调查之前杀死执行 flush 语句的连接(Ctrl+C 可以在 MySQL Shell 中用于执行FLUSH TABLES WITH READ LOCK的连接)。

mysql> SELECT thd_id, conn_id, state,
              current_statement,
              statement_latency
         FROM sys.session
        WHERE command = 'Query'\G
*************************** 1\. row ***************************
           thd_id: 30
          conn_id: 9
            state: User sleep
current_statement: SELECT *, SLEEP(180) FROM city WHERE ID = 130
statement_latency: 24.16 s
*************************** 2\. row ***************************
           thd_id: 51
          conn_id: 13
            state: Waiting for table flush
current_statement: SELECT * FROM world.city WHERE ID = 3805
statement_latency: 20.20 s
*************************** 3\. row ***************************
           thd_id: 29
          conn_id: 8
            state: NULL
current_statement: SELECT thd_id, conn_id, state, ... ession WHERE command = 'Query'
statement_latency: 47.02 ms
3 rows in set (0.0548 sec)

Listing 22-2Flush lock contention without a FLUSH TABLES statement

这种情况与前一种情况相同，只是没有了FLUSH TABLES语句。在这种情况下，查找等待时间最长且状态为“等待表刷新”的查询运行时间超过该查询等待时间的查询会阻止 TDC 版本锁被释放。在这种情况下，这意味着thd_id = 30是阻塞查询。

一旦您确定了问题和涉及的主要查询，您需要决定如何处理该问题。

解决方案

解决这个问题有两个层次。首先，您需要解决查询不执行的直接问题。其次，你需要努力避免将来出现这种问题。本小节将讨论即时解决方案，下一小节将考虑如何减少问题发生的几率。

要解决眼前的问题，您可以选择等待查询完成或开始终止查询。如果您可以在刷新锁争用正在进行时重定向应用以使用另一个实例，那么通过让长时间运行的查询完成，您也许能够让这种情况自行解决。如果在那些正在运行或等待的查询中有数据更改查询，在这种情况下，您确实需要考虑在所有查询完成后，它是否会使系统保持一致的状态。一种选择是以只读模式继续在不同的实例上执行读取查询。

如果您决定终止查询，您可以尝试终止FLUSH TABLES语句。如果这行得通，这是最简单的解决方案。然而，正如所讨论的那样，这并不总是有帮助的，在这种情况下，唯一的解决方案是终止那些阻止FLUSH TABLES语句完成的查询。如果长时间运行的查询看起来像失控的查询，并且执行它们的应用/客户端不再等待它们，那么您可能想要杀死它们，而不是试图首先杀死FLUSH TABLES语句。

在终止查询时，一个重要的考虑因素是有多少数据被更改。对于一个纯粹的SELECT查询(不涉及存储的例程)，那总是没什么，从所做工作的角度来看，杀死它是安全的。然而，对于INSERT、UPDATE、DELETE和类似的查询，如果查询被终止，则更改的数据必须回滚。回滚更改通常比一开始就进行更改需要更长的时间，所以如果有很多更改，请准备好等待很长时间才能回滚。您可以使用information_schema.INNODB_TRX表，通过查看trx_rows_modified列来估计完成的工作量。如果有大量工作要回滚，通常最好让查询完成。

Caution

当 DML 语句被终止时，它所做的工作必须回滚。回滚通常比创建变更花费更长的时间，有时甚至更长。如果你考虑终止一个长时间运行的 DML 语句，你需要考虑到这一点。

当然，最理想的情况是完全防止问题发生。

预防

刷新锁争用的发生是因为长时间运行的查询和一个FLUSH TABLES语句的组合。因此，为了防止这个问题，你需要看看你能做些什么来避免这两种情况同时出现。

查找、分析和处理长时间运行的查询将在整本书的其他章节中讨论。一个特别有趣的选项是为查询设置超时。使用max_execution_time系统变量和MAX_EXECUTION_TIME(N)优化器提示的SELECT语句支持这一点，这是防止失控查询的一个好方法。一些连接器还支持超时查询。

Tip

为了避免长时间运行的SELECT查询，您可以配置max_execution_time选项或者设置MAX_EXECUTION_TIME(N)优化器提示。这将使SELECT语句在指定的时间段后超时，并有助于防止类似刷新锁等待的问题。

无法阻止某些长时间运行的查询。这可能是一项报告作业、构建缓存表或其他必须访问大量数据的任务。在这种情况下，您能做的最好的事情就是尽量避免它们运行，同时也有必要刷新表。一种选择是将长时间运行的查询安排在不同于需要刷新表的时间运行。另一种选择是让长时间运行的查询在不同于需要刷新表的作业的实例上运行。

需要刷新表的一个常见任务是进行备份。在 MySQL 8 中，可以通过使用备份和日志锁来避免这个问题。例如，MySQL Enterprise Backup (MEB)在版本 8.0.16 和更高版本中执行此操作，因此 InnoDB 表永远不会被刷新。或者，您可以在使用率较低的时段执行备份，这样潜在的冲突会更低，或者您甚至可以在系统处于只读模式时执行备份，从而完全避免FLUSH TABLES WITH READ LOCK。

另一种经常引起混淆的锁类型是元数据锁。

元数据和模式锁

在 MySQL 5.7 和更早的版本中，元数据锁经常是混淆的来源。问题是谁持有元数据锁并不明显。在 MySQL 5.7 中，元数据锁的检测被添加到性能模式中，而在 MySQL 8.0 中，它是默认启用的。启用该工具后，就可以很容易地确定是谁阻塞了试图获取锁的连接。

症状

元数据锁争用的症状类似于刷新锁争用的症状。在典型的情况下，会有一个长时间运行的查询或事务、一个等待元数据锁定的 DDL 语句，以及可能堆积起来的查询。要注意的症状如下:

DDL 语句和其他可能的查询都停留在“等待表元数据锁定”状态。
查询可能会堆积如山。等待中的查询都使用同一个表。(如果有多个表的 DDL 语句在等待元数据锁，则可能有不止一组查询在等待。)
当 DDL 语句已经等待lock_wait_timeout时，出现一个ER_LOCK_WAIT_TIMEOUT错误:ERROR: 1205: Lock wait timeout exceeded; try restarting transaction。由于lock_wait_timeout的默认值是 365 天，只有在超时时间减少的情况下，这种情况才有可能发生。
有一个长时间运行的查询或长时间运行的事务。在后一种情况下，事务可能处于空闲状态，或者正在执行一个不使用 DDL 语句所作用的表的查询。

使这种情况变得潜在混乱的是最后一点:可能没有任何长时间运行的查询是导致锁问题的明确候选。那么元数据锁争用的原因是什么呢？

原因

请记住，元数据锁的存在是为了保护模式定义(以及与显式锁一起使用)。只要事务处于活动状态，模式保护就会一直存在，因此当事务查询表时，元数据锁定将持续到事务结束。因此，您可能看不到任何长时间运行的查询。事实上，持有元数据锁的事务可能根本不做任何事情。

简而言之，元数据锁的存在是因为一个或多个连接可能依赖于给定表的模式不变，或者它们已经使用LOCK TABLES或FLUSH TABLES WITH READ LOCK语句显式锁定了该表。

设置

元数据锁的示例调查使用了三个连接，就像前面的示例一样。第一个连接正在进行事务处理，第二个连接尝试向事务处理使用的表添加索引，第三个连接尝试对同一个表执行查询。这些查询是

Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)

Connection 1> SELECT * FROM world.city WHERE ID = 3805\G *************************** 1\. row ***************************
         ID: 3805
       Name: San Francisco
CountryCode: USA
   District: California
 Population: 776733
1 row in set (0.0006 sec)

Connection 1> SELECT Code, Name FROM world.country WHERE Code = 'USA'\G
*************************** 1\. row ***************************
Code: USA
Name: United States
1 row in set (0.0020 sec)

Connection 2> ALTER TABLE world.city ADD INDEX (Name);

Connection 3> SELECT * FROM world.city WHERE ID = 130;

此时，可以开始调查了。这种情况不会自行解决(除非你的lock_wait_timeout值很低，或者你准备等一年)，所以你有足够的时间。当您想要解决这个阻塞时，您可以开始终止连接 2 中的ALTER TABLE语句，以避免修改world.city表。然后提交或回滚连接 1 中的事务。

调查

如果启用了wait/lock/metadata/sql/mdl性能模式工具(MySQL 8 中的默认设置)，那么调查元数据锁定问题就很简单了。您可以使用性能模式中的metadata_locks表来列出授予的和挂起的锁。然而，获得锁情况摘要的一个更简单的方法是使用sys模式中的schema_table_lock_waits视图。

作为一个例子，考虑在清单 22-3 中可以看到的元数据锁定等待问题，其中涉及三个连接。选择了WHERE子句，以便只包含该调查感兴趣的行。

mysql> SELECT thd_id, conn_id, state,
              current_statement,
              statement_latency
         FROM sys.session
        WHERE command = 'Query' OR trx_state = 'ACTIVE'\G
*************************** 1\. row ***************************
           thd_id: 30
          conn_id: 9
            state: NULL
current_statement: SELECT Code, Name FROM world.country WHERE Code = 'USA'
statement_latency: NULL
*************************** 2\. row ***************************
           thd_id: 7130
          conn_id: 7090
            state: Waiting for table metadata lock
current_statement: ALTER TABLE world.city ADD INDEX (Name)
statement_latency: 19.92 m
*************************** 3\. row ***************************
           thd_id: 51
          conn_id: 13
            state: Waiting for table metadata lock
current_statement: SELECT * FROM world.city WHERE ID = 130
statement_latency: 19.78 m
*************************** 4\. row ***************************
           thd_id: 107
          conn_id: 46
            state: NULL
current_statement: SELECT thd_id, conn_id, state, ... Query' OR trx_state = 'ACTIVE'
statement_latency: 56.77 ms
3 rows in set (0.0629 sec)

Listing 22-3A metadata lock wait issue

两个连接正在等待元数据锁定(在world.city表上)。包括第三个连接(conn_id = 9)，它是空闲的，这可以从语句延迟的NULL中看出(在 8.0.18 之前的一些版本中，您也可以看到当前语句是NULL)。在这种情况下，查询列表仅限于具有活动查询或活动事务的查询，但通常您会从完整的进程列表开始。然而，为了便于关注重要的部分，输出被过滤。

一旦您知道存在元数据锁定问题，您可以使用sys.schema_table_lock_waits视图来获取关于锁定争用的信息。清单 22-4 显示了与刚才讨论的进程列表相对应的输出示例。

mysql> SELECT *
         FROM sys.schema_table_lock_waits\G
*************************** 1\. row ***************************
               object_schema: world
                 object_name: city
           waiting_thread_id: 7130
                 waiting_pid: 7090
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: ALTER TABLE world.city ADD INDEX (Name)
          waiting_query_secs: 1219
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 7130
                blocking_pid: 7090
            blocking_account: root@localhost
          blocking_lock_type: SHARED_UPGRADABLE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 7090
sql_kill_blocking_connection: KILL 7090
*************************** 2\. row ***************************
               object_schema: world
                 object_name: city
           waiting_thread_id: 51
                 waiting_pid: 13
             waiting_account: root@localhost
           waiting_lock_type: SHARED_READ
       waiting_lock_duration: TRANSACTION
               waiting_query: SELECT * FROM world.city WHERE ID = 130
          waiting_query_secs: 1210
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 7130
                blocking_pid: 7090
            blocking_account: root@localhost
          blocking_lock_type: SHARED_UPGRADABLE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 7090
sql_kill_blocking_connection: KILL 7090
*************************** 3\. row ***************************
               object_schema: world
                 object_name: city
           waiting_thread_id: 7130
                 waiting_pid: 7090
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: ALTER TABLE world.city ADD INDEX (Name)
          waiting_query_secs: 1219
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 30
                blocking_pid: 9
            blocking_account: root@localhost
          blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 9
sql_kill_blocking_connection: KILL 9
*************************** 4\. row ***************************
               object_schema: world
                 object_name: city
           waiting_thread_id: 51
                 waiting_pid: 13
             waiting_account: root@localhost
           waiting_lock_type: SHARED_READ
       waiting_lock_duration: TRANSACTION
               waiting_query: SELECT * FROM world.city WHERE ID = 130
          waiting_query_secs: 1210
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 30
                blocking_pid: 9
            blocking_account: root@localhost
          blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 9
sql_kill_blocking_connection: KILL 9
4 rows in set (0.0024 sec)

Listing 22-4Finding metadata lock contention

输出显示有四种查询等待和阻塞的情况。这可能令人惊讶，但它确实发生了，因为涉及到几个锁，并且有一系列等待。每一行都是一对等待和阻塞连接。输出使用“pid”作为进程列表 id，这与早期输出中使用的连接 id 相同。这些信息包括锁是什么、等待连接的详细信息、阻塞连接的详细信息以及可用于终止阻塞查询或连接的两个查询。

第一行显示了等待自身的进程列表 id 7090。这听起来像是一个僵局，但事实并非如此。原因是ALTER TABLE首先获取了一个可以升级的共享锁，然后试图获取正在等待的独占锁。因为没有关于哪个现有锁实际上阻塞了新锁的明确信息，所以该信息最终被包括在内。

第二行显示SELECT语句正在等待进程列表 id 7090，即ALTER TABLE。这就是当 DDL 语句需要一个独占锁时，连接会开始堆积的原因，所以它会阻塞对共享锁的请求。

第三和第四行揭示了锁争用的潜在问题。进程列表 id 9 阻塞了其他两个连接，这表明这是阻塞 DDL 语句的罪魁祸首。因此，当您调查类似这样的问题时，请查找正在等待被另一个连接阻塞的独占元数据锁的连接。如果输出中有大量的行，您还可以查找导致最多阻塞的连接，并以此为起点。清单 22-5 展示了如何做到这一点的例子。

mysql> SELECT *
         FROM sys.schema_table_lock_waits
        WHERE waiting_lock_type = 'EXCLUSIVE'
              AND waiting_pid <> blocking_pid\G
*************************** 1\. row ***************************
               object_schema: world
                 object_name: city
           waiting_thread_id: 7130
                 waiting_pid: 7090
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: ALTER TABLE world.city ADD INDEX (Name)
          waiting_query_secs: 4906
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 30
                blocking_pid: 9
            blocking_account: root@localhost
          blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 9
sql_kill_blocking_connection: KILL 9
1 row in set (0.0056 sec)

mysql> SELECT blocking_pid, COUNT(*)
         FROM sys.schema_table_lock_waits
        WHERE waiting_pid <> blocking_pid
        GROUP BY blocking_pid
        ORDER BY COUNT(*) DESC;
+--------------+----------+
| blocking_pid | COUNT(*) |
+--------------+----------+
|            9 |        2 |
|         7090 |        1 |
+--------------+----------+
2 rows in set (0.0028 sec)

Listing 22-5Looking for the connection causing the metadata lock block

第一个查询寻找对独占元数据锁的等待，其中阻塞进程列表 id 不是它本身。在这种情况下，这会立即导致主块争用。第二个查询确定每个进程列表 id 触发的阻塞查询的数量。这可能不像这个例子中显示的那么简单，但是使用这里显示的查询将有助于缩小锁争用的范围。

一旦确定了锁争用的来源，就需要确定事务正在做什么。在这种情况下，锁争用的根源是连接 9。回到进程列表输出，您可以看到在这种情况下它没有做任何事情:

*************************** 1\. row ***************************
           thd_id: 30
          conn_id: 9
            state: NULL
current_statement: SELECT Code, Name FROM world.country WHERE Code = 'USA'
statement_latency: NULL

这个连接做了什么来获取元数据锁？没有涉及world.city表的当前语句这一事实表明该连接有一个活动的事务打开。在这种情况下，事务是空闲的(如statement_latency = NULL所示)，但也可能有一个与world.city表上的元数据锁无关的查询正在执行。无论哪种情况，您都需要确定事务在当前状态之前正在做什么。为此，您可以使用性能模式和信息模式。清单 22-6 展示了一个调查事务状态和最近历史的例子。

mysql> SELECT *
         FROM information_schema.INNODB_TRX
        WHERE trx_mysql_thread_id = 9\G
*************************** 1\. row ***************************
                    trx_id: 283529000061592
                 trx_state: RUNNING
               trx_started: 2019-06-15 13:22:29
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 9
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.0006 sec)

mysql> SELECT *
         FROM performance_schema.events_transactions_current
        WHERE THREAD_ID = 30\G
*************************** 1\. row ***************************
                      THREAD_ID: 30
                       EVENT_ID: 113
                   END_EVENT_ID: NULL
                     EVENT_NAME: transaction
                          STATE: ACTIVE
                         TRX_ID: NULL
                           GTID: AUTOMATIC
                  XID_FORMAT_ID: NULL
                      XID_GTRID: NULL
                      XID_BQUAL: NULL
                       XA_STATE: NULL
                         SOURCE: transaction.cc:219
                    TIMER_START: 12849615560172160
                      TIMER_END: 18599491723543808
                     TIMER_WAIT: 5749876163371648
                    ACCESS_MODE: READ WRITE
                ISOLATION_LEVEL: REPEATABLE READ
                     AUTOCOMMIT: NO
           NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
    NUMBER_OF_RELEASE_SAVEPOINT: 0
          OBJECT_INSTANCE_BEGIN: NULL
               NESTING_EVENT_ID: 112
             NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.0008 sec)

mysql> SELECT EVENT_ID, CURRENT_SCHEMA,
              SQL_TEXT
         FROM performance_schema.events_statements_history
        WHERE THREAD_ID = 30
              AND NESTING_EVENT_ID = 113
              AND NESTING_EVENT_TYPE = 'TRANSACTION'\G
*************************** 1\. row ***************************
      EVENT_ID: 114
CURRENT_SCHEMA: world
      SQL_TEXT: SELECT * FROM world.city WHERE ID = 3805
*************************** 2\. row ***************************
      EVENT_ID: 115
CURRENT_SCHEMA: world
      SQL_TEXT: SELECT * FROM world.country WHERE Code = 'USA'
2 rows in set (0.0036 sec)

mysql> SELECT ATTR_NAME, ATTR_VALUE
         FROM performance_schema.session_connect_attrs
        WHERE PROCESSLIST_ID = 9;
+-----------------+------------+
| ATTR_NAME       | ATTR_VALUE |
+-----------------+------------+
| _pid            | 23256      |
| program_name    | mysqlsh    |
| _client_name    | libmysql   |
| _thread         | 20164      |
| _client_version | 8.0.18     |
| _os             | Win64      |
| _platform       | x86_64     |
+-----------------+------------+
7 rows in set (0.0006 sec)

Listing 22-6Investigating a transaction

第一个查询使用信息模式中的INNODB_TRX表。例如，它显示事务开始的时间，因此您可以确定它已经活动了多长时间。如果决定回滚事务，那么trx_rows_modified列对于了解事务更改了多少数据也很有用。注意，InnoDB 所谓的 MySQL 线程 id(trx_mysql_thread_id列)实际上是连接 id。

第二个查询使用性能模式中的events_transactions_current表来获取更多的事务信息。您可以使用TIMER_WAIT列来确定事务的年龄。该值以皮秒为单位，因此使用FORMAT_PICO_TIME()函数可以更容易地理解该值:

mysql> SELECT FORMAT_PICO_TIME(5749876163371648) AS Age;
+--------+
| Age    |
+--------+
| 1.60 h |
+--------+
1 row in set (0.0003 sec)

如果您使用的是 MySQL 8.0.15 或更早版本，请使用sys.format_time()函数。

第三个查询使用events_statements_history表来查找之前在事务中执行的查询。NESTING_EVENT_ID列被设置为来自events_transactions_current表输出的EVENT_ID的值，而NESTING_EVENT_TYPE列被设置为匹配一个事务。这确保了只返回正在进行的事务的子事件。结果由EVENT_ID(语句的)排序，按照执行的顺序得到语句。默认情况下，events_statements_history表将包含最多十个最新的连接查询。

在这个例子中，调查显示事务执行了两个查询:一个从world.city表中选择，另一个从world.country表中选择。这是导致元数据锁争用的第一个查询。

第四个查询使用session_connect_attrs表来查找连接提交的属性。并非所有客户端和连接器都提交属性，或者它们可能被禁用，因此这些信息并不总是可用。当属性可用时，它们有助于找出违规事务是从哪里执行的。在这个例子中，您可以看到连接来自 MySQL Shell ( mysqlsh)。如果您想提交一个空闲事务，这可能很有用。

解决方案

对于元数据锁争用，您基本上有两种选择来解决问题:完成阻塞事务或终止 DDL 语句。要完成阻塞事务，您需要提交或回滚它。如果您终止连接，将触发事务回滚，因此您需要考虑需要回滚多少工作。为了提交事务，您必须找到执行连接的位置，并以这种方式提交它。您不能提交由不同连接拥有的事务。

终止 DDL 语句将允许其他查询继续进行，但从长远来看，如果锁被一个已放弃但仍处于活动状态的事务持有，这并不能解决问题。对于持有元数据锁的被放弃的事务，可以选择终止 DDL 语句和与被放弃的事务的连接。这样可以避免 DDL 语句在事务回滚时继续阻塞后续查询。回滚完成后，您可以重试 DDL 语句。

预防

避免元数据锁争用的关键是避免长时间运行的事务，同时您需要为事务使用的表执行 DDL 语句。例如，当您知道没有长时间运行的事务时，可以执行 DDL 语句。您还可以将lock_wait_timeout选项设置为一个较低的值，这将使 DDL 语句在lock_wait_timeout秒后放弃。虽然这不能避免锁问题，但它通过避免 DDL 语句停止其他查询的执行来缓解这个问题。然后，您可以找到根本原因，而不必担心大部分应用无法工作。

您还可以致力于缩短事务的活动时间。如果不要求所有操作都作为一个原子单元来执行，一种选择是将一个大的事务分成几个较小的事务。您还应该确保在事务处于活动状态时，您没有进行交互工作、文件 I/O、向最终用户传输数据等，从而确保事务不会保持不必要的长时间打开。

长时间运行事务的一个常见原因是应用或客户端根本不提交或回滚事务。禁用autocommit选项时，这种情况尤其容易发生。当autocommit被禁用时，任何查询——即使是普通的只读SELECT语句——都会在没有活动事务的情况下启动一个新事务。这意味着一个看似无辜的查询可能会启动一个事务，如果开发者不知道autocommit被禁用，那么开发者可能不会考虑显式结束事务。在 MySQL Server 中默认情况下，autocommit设置是启用的，但是一些连接器默认情况下禁用它。

关于研究元数据锁的讨论到此结束。下一级锁是记录锁。

记录级锁

记录锁争用是最常遇到的，但通常也是最不具干扰性的，因为默认的锁等待超时只有 50 秒，所以不存在查询堆积的可能性。也就是说，在某些情况下——正如将要展示的那样——记录锁会导致 MySQL 嘎然而止。本节将研究 InnoDB 记录锁的一般问题，以及更详细的锁等待超时问题。对死锁细节的研究将推迟到下一节。

症状

InnoDB 记录锁争用的症状通常非常微妙，不容易识别。在严重的情况下，您会得到锁等待超时或死锁错误，但在许多情况下，可能没有直接的症状。更确切地说，症状是查询比正常情况下慢。这可能从慢几分之一秒到慢很多秒不等。

对于存在锁等待超时的情况，您将看到类似于以下示例中的ER_LOCK_WAIT_TIMEOUT错误:

ERROR: 1205: Lock wait timeout exceeded; try restarting transaction

当查询比没有锁争用时要慢时，最有可能检测到问题的方法是通过监控，要么使用类似于 MySQL Enterprise Monitor 中的查询分析器，要么使用sys.innodb_lock_waits视图检测锁争用。图 22-3 显示了查询分析器中的一个查询示例。在讨论记录锁争用的调查时，将使用sys模式视图。该图在本书的 GitHub 知识库中以figure_22_3_quan.png的形式提供。

img/484666_1_En_22_Fig3_HTML.jpg

图 22-3

查询分析器中检测到的锁争用示例

在图中，请注意查询的延迟图是如何在接近周期结束时增加，然后又突然下降的。规范化查询的右侧还有一个红色图标，该图标表示查询返回了错误。在这种情况下，错误是锁等待超时，但是从图中看不到。规范化查询左侧的环形图表还显示了一个红色区域，指示查询的查询响应时间索引有时被认为很差。顶部的大图显示了一个小的下降，表明实例中有足够多的问题导致实例的性能普遍下降。

还有几个实例级指标显示实例发生了多少锁定。这对于监控一段时间内的一般锁争用非常有用。清单 22-7 使用sys.metrics视图显示了可用的指标。

mysql> SELECT Variable_name,
              Variable_value AS Value,
              Enabled
         FROM sys.metrics
        WHERE Variable_name LIKE 'innodb_row_lock%'
              OR Type = 'InnoDB Metrics - lock';
+-------------------------------+--------+---------+
| Variable_name                 | Value  | Enabled |
+-------------------------------+--------+---------+
| innodb_row_lock_current_waits | 0      | YES     |
| innodb_row_lock_time          | 595876 | YES     |
| innodb_row_lock_time_avg      | 1683   | YES     |
| innodb_row_lock_time_max      | 51531  | YES     |
| innodb_row_lock_waits         | 354    | YES     |
| lock_deadlocks                | 0      | YES     |
| lock_rec_lock_created         | 0      | NO      |
| lock_rec_lock_removed         | 0      | NO      |
| lock_rec_lock_requests        | 0      | NO      |
| lock_rec_lock_waits           | 0      | NO      |
| lock_rec_locks                | 0      | NO      |
| lock_row_lock_current_waits   | 0      | YES     |
| lock_table_lock_created       | 0      | NO      |
| lock_table_lock_removed       | 0      | NO      |
| lock_table_lock_waits         | 0      | NO      |
| lock_table_locks              | 0      | NO      |
| lock_timeouts                 | 1      | YES     |
+-------------------------------+--------+---------+
17 rows in set (0.0203 sec)

Listing 22-7InnoDB lock metrics

对于这个讨论，innodb_row_lock_%和lock_timeouts指标是最有趣的。三个时间变量以毫秒为单位。可以看到，有一个锁等待超时，这本身并不一定是一个问题。您还可以看到有 354 种情况下锁不能被立即授予(innodb_row_lock_waits)，并且等待时间超过 51 秒(innodb_row_lock_time_max)。当锁争用的总体水平增加时，您将看到这些指标也在增加。

甚至比手动监控指标更好的是，确保您的监控解决方案记录指标，并可以在时间序列图中绘制它们。图 22-4 显示了针对图 22-3 中发现的同一事件绘制的指标示例。

img/484666_1_En_22_Fig4_HTML.jpg

图 22-4

InnoDB 行锁指标的时间序列图

图表显示了锁定的总体增加。锁等待的数量有两个阶段，随着锁等待的增加，然后再次下降。行锁定时间图显示了类似的模式。这是间歇性锁定问题的典型迹象。

原因

InnoDB 在行数据、索引记录、间隙和插入意图锁上使用共享锁和排他锁。当有两个事务试图以冲突的方式访问数据时，一个查询将不得不等待，直到所需的锁可用。简而言之，可以同时允许两个对共享锁的请求，但是一旦有了独占锁，任何连接都不能在同一个记录上获得锁。

由于排他锁最有可能导致锁争用，因此通常 DML 查询会更改导致 InnoDB 记录锁争用的数据。另一个来源是SELECT语句通过添加FOR SHARE(或LOCK IN SHARE MODE或FOR UPDATE子句来进行抢先锁定。

设置

这个示例只需要两个连接来设置正在研究的场景，第一个连接有一个正在进行的事务，第二个连接试图更新第一个连接持有锁的行。因为等待 InnoDB 锁的默认超时是 50 秒，所以您可以选择增加第二个连接的超时时间，这将会阻塞，以便您有更多的时间来执行调查。设置是

Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)

Connection 1> UPDATE world.city
                 SET Population = 5000000
               WHERE ID = 130;
Query OK, 1 row affected (0.0005 sec)

Rows matched: 1  Changed: 1  Warnings: 0

Connection 2> SET SESSION innodb_lock_wait_timeout = 300;
Query OK, 0 rows affected (0.0003 sec)

Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)

Connection 2> UPDATE world.city SET Population = Population * 1.10 WHERE CountryCode = 'AUS';

在本例中，连接 2 的锁定等待超时设置为 300 秒。Connection 2 的START TRANSACTION不是必需的，但是允许您在完成后回滚两个事务，以避免对数据进行更改。

调查

调查记录锁与调查元数据锁非常相似。您可以查询性能模式中的data_locks和data_lock_waits表，它们将分别显示原始锁数据和挂起的锁。还有一个sys.innodb_lock_waits视图，它查询两个表来寻找一个被另一个阻塞的锁对。

Note

MySQL 8 中新增了data_locks和data_lock_waits表。在 MySQL 5.7 和更早的版本中，信息模式中有两个相似的表，分别名为INNODB_LOCKS和INNODB_LOCK_WAITS。使用innodb_lock_waits视图的一个优点是它在不同的 MySQL 版本上工作是一样的(但是在 MySQL 8 中有一些额外的信息)。

在大多数情况下，使用innodb_lock_waits视图开始调查是最容易的，并且只在需要时深入性能模式表。清单 22-8 显示了锁等待情况下innodb_lock_waits的输出示例。

mysql> SELECT * FROM sys.innodb_lock_waits\G
*************************** 1\. row ***************************
                wait_started: 2019-06-15 18:37:42
                    wait_age: 00:00:02
               wait_age_secs: 2
                locked_table: `world`.`city`
         locked_table_schema: world
           locked_table_name: city
      locked_table_partition: NULL
   locked_table_subpartition: NULL
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 3317978
         waiting_trx_started: 2019-06-15 18:37:42
             waiting_trx_age: 00:00:02
     waiting_trx_rows_locked: 2
   waiting_trx_rows_modified: 0
                 waiting_pid: 4172
               waiting_query: UPDATE city SET Population = P ... 1.10 WHERE CountryCode = 'AUS'
             waiting_lock_id: 1999758099664:525:6:131:1999728339632
           waiting_lock_mode: X,REC_NOT_GAP
             blocking_trx_id: 3317977
                blocking_pid: 9
              blocking_query: NULL
            blocking_lock_id: 1999758097920:525:6:131:1999728329336
          blocking_lock_mode: X,REC_NOT_GAP
        blocking_trx_started: 2019-06-15 18:37:40
            blocking_trx_age: 00:00:04
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 9
sql_kill_blocking_connection: KILL 9
1 row in set (0.0145 sec)

Listing 22-8Retrieving lock information from the innodb_lock_waits view

根据列名的前缀，输出中的列可以分为五个部分。这些群体是

wait_: 这些列显示了锁等待时间的一些一般信息。
locked_: 这些列显示了从模式到索引以及锁类型的锁定内容。
waiting_: 这些列显示等待授予锁的事务的详细信息，包括查询和请求的锁模式。
blocking_: 这些列显示了阻塞锁请求的事务的详细信息。注意，在这个例子中，阻塞查询是NULL。这意味着在生成输出时事务是空闲的。即使列出了阻塞查询，该查询也可能与存在争用的锁没有任何关系——除了该查询是由持有锁的同一事务执行的。
sql_kill_: 这两列提供了可用于终止阻塞查询或连接的KILL查询。

Note

列blocking_query是阻塞事务当前执行的查询(如果有的话)。这并不意味着查询本身必然会导致锁请求阻塞。

blocking_query列为NULL的情况是常见情况。这意味着阻塞事务当前没有执行查询。这可能是因为它在两个查询之间。如果这段时间很长，则表明应用正在做理想情况下应该在事务之外完成的工作。更常见的情况是，事务没有执行查询，因为它被遗忘了，要么是在交互会话中，人们忘记了结束事务，要么是在应用流中，不能确保事务被提交或回滚。

解决方案

解决方案取决于锁等待的程度。如果有几个查询的锁等待时间很短，那么让受影响的查询等待锁变得可用也是可以接受的。请记住，锁是为了确保数据的完整性，所以锁本身不是问题。只有当锁对性能造成重大影响或者导致查询失败到无法重试的程度时，锁才会成为问题。

如果锁定情况持续很长时间——特别是如果阻塞事务已经被放弃——您可以考虑终止阻塞事务。和往常一样，如果阻塞事务执行了大量工作，您需要考虑回滚可能会花费大量时间。

对于由于锁等待超时错误而失败的查询，应用应该重试它们。请记住，默认情况下，锁等待超时仅回滚超时发生时正在执行的查询。事务的其余部分与查询前一样。因此，处理超时失败可能会使未完成的事务带有自己的锁，这可能会导致进一步的锁问题。是只回滚查询还是回滚整个事务由innodb_rollback_on_timeout选项控制。

Caution

处理锁等待超时是非常重要的，否则它可能会使事务带有未释放的锁。如果发生这种情况，其他事务可能无法获得它们需要的锁。

预防

防止重大的记录级锁争用主要遵循第 18 章的“减少锁定问题”一节中讨论的指南。概括一下讨论，减少锁等待争用的方法主要是减少事务的大小和持续时间，使用索引来减少被访问的记录的数量，并可能将事务隔离级别切换到READ COMMITTED来更早地释放锁并减少间隙锁的数量。

僵局

数据库管理员最担心的锁问题之一是死锁。这一部分是因为它的名字，另一部分是因为它们不像讨论的其他锁问题那样总是会导致错误。然而，与其他锁定问题相比，没有什么特别担心死锁的。相反，它们导致错误意味着您能更快地知道它们，并且锁问题会自行解决。

症状

症状很明显。死锁的受害者收到一个错误，并且lock_deadlocks InnoDB 度量增加。将返回给 InnoDB 选择作为受害者的事务的错误是ER_LOCK_DEADLOCK:

ERROR: 1213: Deadlock found when trying to get lock; try restarting transaction

这个指标对于观察死锁发生的频率非常有用。跟踪lock_deadlocks的值的一种简便方法是使用sys.metrics视图:

mysql> SELECT *
         FROM sys.metrics
        WHERE Variable_name = 'lock_deadlocks'\G
*************************** 1\. row ***************************
 Variable_name: lock_deadlocks
Variable_value: 42
          Type: InnoDB Metrics - lock
       Enabled: YES
1 row in set (0.0087 sec)

您还可以检查 InnoDB 监控器输出中的LATEST DETECTED DEADLOCK部分，例如，通过执行SHOW ENGINE INNODB STATUS.这将显示上一次死锁发生的时间，因此您可以使用它来判断死锁发生的频率。如果您启用了innodb_print_all_deadlocks选项，错误锁将有许多死锁信息的输出。在讨论了死锁的原因和设置之后，将在“调查”中详细介绍死锁的 InnoDB 监控器输出。

原因

死锁是由两个或多个事务以不同的顺序获得锁引起的。每个事务最终都持有另一个事务需要的锁。该锁可以是记录锁、间隙锁、谓词锁或插入意图锁。图 22-5 显示了一个触发死锁的循环依赖的例子。

img/484666_1_En_22_Fig5_HTML.png

图 22-5

触发死锁的循环锁依赖关系

图中显示的死锁是由于表主键上的两个记录锁造成的。这是可能发生的最简单的死锁之一。如图所示，在调查死锁时，循环可能比这更复杂。

设置

本例使用了两个连接，但这一次两个连接都在连接 1 阻塞之前进行了更改，直到连接 2 因出错而回滚其更改。连接 1 用 10%更新澳大利亚及其城市的人口，而连接 2 用达尔文市的人口更新澳大利亚人口并添加城市。这些声明是

Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0001 sec)

Connection 1> UPDATE world.city SET Population = Population * 1.10 WHERE CountryCode = 'AUS';
Query OK, 14 rows affected (0.0010 sec)

Rows matched: 14  Changed: 14  Warnings: 0

Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)

Connection 2> UPDATE world.country SET Population = Population + 146000 WHERE Code = 'AUS';
Query OK, 1 row affected (0.0317 sec)

Rows matched: 1  Changed: 1  Warnings: 0

-- Blocks
Connection 1> UPDATE world.country SET Population = Population * 1.1 WHERE Code = 'AUS';

Connection 2> INSERT INTO world.city VALUES (4080, 'Darwin', 'AUS', 'Northern Territory', 146000);
ERROR: 1213: Deadlock found when trying to get lock; try restarting transaction

Connection 2> ROLLBACK;
Query OK, 0 rows affected (0.0003 sec)

Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.3301 sec)

关键是这两个事务都更新了city和country表，但是顺序相反。设置通过显式回滚这两个事务来完成，以确保表保持不变。

调查

分析死锁的主要工具是 InnoDB monitor 输出中有关最新检测到的死锁的信息部分。如果您启用了innodb_print_all_deadlocks选项(默认情况下为OFF，您还可以从错误日志中获得死锁信息；然而，信息是相同的，因此它不改变分析。

死锁信息包含描述死锁和结果的四个部分。这些零件是

当死锁发生时。
死锁中涉及的第一个事务的信息。
死锁所涉及的第二个事务的信息。
哪个事务被回滚。当innodb_print_all_deadlocks启用时，该信息不包括在错误日志中。

两个事务的编号是任意的，主要目的是能够引用一个事务或另一个事务。包含事务信息的两个部分是最重要的部分。它们包括事务处于活动状态的时间长度、关于事务大小的一些统计信息(根据所使用的锁和撤销日志条目等)、正在阻塞等待锁的查询，以及关于死锁中所涉及的锁的信息。

锁信息不像使用data_locks和data_lock_waits表以及sys.innodb_lock_waits视图时那么容易解释。然而，一旦你尝试进行几次分析，这并不太难。

Tip

在测试系统中故意创建一些死锁，并研究由此产生的死锁信息。然后通过信息来确定死锁发生的原因。因为您知道查询，所以更容易解释锁数据。

对于这个死锁调查，考虑清单 22-9 中显示的 InnoDB 监控器的死锁部分。清单相当长，行也很宽，所以信息也可以在本书的 GitHub 存储库中作为listing_22_9_deadlock.txt获得，所以您可以在自己选择的文本编辑器中打开输出。

mysql> SHOW ENGINE INNODB STATUS\G
...
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-11-06 18:29:07 0x4b78
*** (1) TRANSACTION:
TRANSACTION 6260, ACTIVE 62 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 30 row lock(s), undo log entries 14
MySQL thread id 61, OS thread handle 22592, query id 39059 localhost ::1 root updating
UPDATE world.country SET Population = Population * 1.1 WHERE Code = 'AUS'

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 160 page no 14 n bits 1368 index CountryCode of table `world`.`city` trx id 6260 lock_mode X locks gap before rec
Record lock, heap no 652 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 415554; asc AUT;;
 1: len 4; hex 800005f3; asc     ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 161 page no 5 n bits 128 index PRIMARY of table `world`.`country` trx id 6260 lock_mode X locks rec but not gap waiting
Record lock, heap no 16 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
 0: len 3; hex 415553; asc AUS;;
 1: len 6; hex 000000001875; asc      u;;
 2: len 7; hex 0200000122066e; asc     " n;;
 3: len 30; hex 4175737472616c6961202020202020202020202020202020202020202020; asc Australia                     ; (total 52 bytes);
 4: len 1; hex 05; asc  ;;
 5: len 26; hex 4175737472616c696120616e64204e6577205a65616c616e6420; asc Australia and New Zealand ;;
 6: len 4; hex 483eec4a; asc H> J;;
 7: len 2; hex 876d; asc  m;;
 8: len 4; hex 812267c0; asc  "g ;;
 9: len 4; hex 9a999f42; asc    B;;
 10: len 4; hex c079ab48; asc  y H;;
 11: len 4; hex e0d9bf48; asc    H;;
 12: len 30; hex 4175737472616c6961202020202020202020202020202020202020202020; asc Australia                     ; (total 45 bytes);
 13: len 30; hex 436f6e737469747574696f6e616c204d6f6e61726368792c204665646572;asc Constitutional Monarchy, Feder; (total 45 bytes);
 14: len 30; hex 456c69736162657468204949202020202020202020202020202020202020; asc Elisabeth II                  ; (total 60 bytes);
 15: len 4; hex 80000087; asc     ;;
 16: len 2; hex 4155; asc AU;;

*** (2) TRANSACTION:
TRANSACTION 6261, ACTIVE 37 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 62, OS thread handle 2044, query id 39060 localhost ::1 root update
INSERT INTO world.city VALUES (4080, 'Darwin', 'AUS', 'Northern Territory', 146000)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 161 page no 5 n bits 128 index PRIMARY of table `world`.`country` trx id 6261 lock_mode X locks rec but not gap
Record lock, heap no 16 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
 0: len 3; hex 415553; asc AUS;;
 1: len 6; hex 000000001875; asc      u;;
 2: len 7; hex 0200000122066e; asc     " n;;
 3: len 30; hex 4175737472616c6961202020202020202020202020202020202020202020; asc Australia                     ; (total 52 bytes);
 4: len 1; hex 05; asc  ;;
 5: len 26; hex 4175737472616c696120616e64204e6577205a65616c616e6420; asc Australia and New Zealand ;;
 6: len 4; hex 483eec4a; asc H> J;;
 7: len 2; hex 876d; asc  m;;
 8: len 4; hex 812267c0; asc  "g ;;
 9: len 4; hex 9a999f42; asc    B;;
 10: len 4; hex c079ab48; asc  y H;;
 11: len 4; hex e0d9bf48; asc    H;;
 12: len 30; hex 4175737472616c6961202020202020202020202020202020202020202020; asc Australia                     ; (total 45 bytes);
 13: len 30; hex 436f6e737469747574696f6e616c204d6f6e61726368792c204665646572; asc Constitutional Monarchy, Feder; (total 45 bytes);
 14: len 30; hex 456c69736162657468204949202020202020202020202020202020202020; asc Elisabeth II                  ; (total 60 bytes);
 15: len 4; hex 80000087; asc     ;;
 16: len 2; hex 4155; asc AU;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 160 page no 14 n bits 1368 index CountryCode of table `world`.`city` trx id 6261 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 652 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 415554; asc AUT;;
 1: len 4; hex 800005f3; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

Listing 22-9Example of a detected deadlock

死锁发生在 2019 年 11 月 6 日，服务器时区 18:29:07。您可以使用此信息来查看该信息是否与用户报告的死锁相同。

有趣的部分是两个事务的信息。您可以看到，事务 1 正在用Code = 'AUS'更新国家的人口:

UPDATE world.country SET Population = Population * 1.1 WHERE Code = 'AUS'

事务 2 试图插入一个新的城市:

INSERT INTO world.city VALUES (4080, 'Darwin', 'AUS', 'Northern Territory', 146000)

这是一个死锁涉及多个表的情况。虽然这两个查询在不同的表上工作，但它本身并不能证明涉及到更多的查询，因为外键可以触发一个查询在两个表上获取锁。不过在本例中，Code列是country表的主键，唯一涉及的外键是从city表的CountryCode列到country表的Code列(显示这是留给使用world示例数据库的读者的一个练习)。所以两个查询不太可能自己死锁。

Note

死锁输出来自 MySQL 8.0.18，它向输出添加了额外的信息。本讨论仅使用了以前版本中也提供的信息。但是，如果您仍在使用早期版本，升级将使调查死锁变得更加容易。

接下来要观察的是正在等待什么锁。事务 1 等待对country表的主键的排他锁:

RECORD LOCKS space id 161 page no 5 n bits 128 index PRIMARY of table `world`.`country` trx id 6260 lock_mode X locks rec but not gap waiting

主键的值可以在该信息后面的信息中找到。由于 InnoDB 包含了与记录相关的所有信息，这看起来有点让人不知所措。因为它是主键记录，所以包含整行。这有助于理解行中的数据，特别是如果主键本身不包含这些信息，但是当您第一次看到它时，可能会感到困惑。country表的主键是表的第一列，所以它是记录信息的第一行，包含锁请求的主键的值:

 0: len 3; hex 415553; asc AUS;;

InnoDB 以十六进制表示法包含该值，但也试图将其解码为一个字符串，因此这里很明显该值是“AUS”，这并不奇怪，因为它也在查询的WHERE子句中。这并不总是那么明显，所以您应该总是确认锁输出的值。您还可以从信息中看到，该列在索引中是按升序排序的。

事务 2 等待对city表的CountryCode索引的插入意图锁:

RECORD LOCKS space id 160 page no 14 n bits 1368 index CountryCode of table `world`.`city` trx id 6261 lock_mode X locks gap before rec insert intention waiting

您可以看到锁定请求在记录之前包含一个间隙。在这种情况下，锁信息更简单，因为CountryCode索引中只有两列，即CountryCode列和主键(ID列)，因为CountryCode索引是非唯一的二级索引。该索引实际上是(CountryCode, ID)，记录前的差距值如下:

 0: len 3; hex 415554; asc AUT;;
 1: len 4; hex 800005f3; asc     ;;

这表明CountryCode的值是“AUT ”,这并不奇怪，因为当按字母升序排序时，它是“AUS”之后的下一个值。ID列的值是十六进制值 0x5f3，十进制值是 1523。如果您查询带有CountryCode = AUT的城市，并按照CountryCode索引的顺序对它们进行排序，您可以看到ID = 1523是找到的第一个城市:

mysql> SELECT *
         FROM world.city
        WHERE CountryCode = 'AUT'
        ORDER BY CountryCode, ID
        LIMIT 1;
+------+------+-------------+----------+------------+
| ID   | Name | CountryCode | District | Population |
+------+------+-------------+----------+------------+
| 1523 | Wien | AUT         | Wien     |    1608144 |
+------+------+-------------+----------+------------+
1 row in set (0.0006 sec)

目前为止一切顺利。因为事务正在等待这些锁，所以当然可以推断出另一个事务持有锁。在 8.0.18 及更高版本中，InnoDB 包含了两个事务持有的锁的完整列表；在早期版本中，InnoDB 只为其中一个事务显式地包含这个查询，所以您需要确定事务还执行了哪些其他查询。

根据现有的信息，你可以做出一些有根据的猜测。例如，INSERT语句被CountryCode索引上的间隙锁阻塞。使用条件CountryCode = 'AUS'的查询就是一个使用该间隙锁的查询示例。死锁信息还包括关于拥有事务的两个连接的信息，这些信息可能对您有所帮助:

MySQL thread id 61, OS thread handle 22592, query id 39059 localhost ::1 root updating

MySQL thread id 62, OS thread handle 2044, query id 39060 localhost ::1 root update

您可以看到这两个连接都是使用root@localhost帐户建立的。如果您确保每个应用和角色有不同的用户，该帐户可以帮助您缩小执行事务的用户范围。

如果连接仍然存在，您还可以使用性能模式中的events_statements_history表来查找连接执行的最新查询。这可能不是死锁所涉及的那些人，这取决于该连接是否被用于更多的查询，但是仍然可以提供该连接用途的线索。如果连接不再存在，原则上您可以在events_statements_history_long表中找到查询，但是您需要将“MySQL 线程 id”(连接 ID)映射到 Performance Schema 线程 ID，这是很难做到的。另外，events_statements_history_long消费者在默认情况下是不启用的。

在这种特殊情况下，两个连接仍然存在，除了回滚事务之外，它们没有做任何事情。清单 22-10 展示了如何找到事务中涉及的查询。请注意，查询可能会返回比这里显示的更多的行，这取决于您使用的客户端以及在连接中执行的其他查询。

mysql> SELECT SQL_TEXT, NESTING_EVENT_ID,
              NESTING_EVENT_TYPE
         FROM performance_schema.events_statements_history
        WHERE THREAD_ID = PS_THREAD_ID(61)
        ORDER BY EVENT_ID\G
*************************** 1\. row ***************************
          SQL_TEXT: START TRANSACTION
  NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
*************************** 2\. row ***************************
          SQL_TEXT: UPDATE world.city SET Population = Population * 1.10 WHERE CountryCode = 'AUS'
  NESTING_EVENT_ID: 37
NESTING_EVENT_TYPE: TRANSACTION
*************************** 3\. row ***************************
          SQL_TEXT: UPDATE world.country SET Population = Population * 1.1 WHERE Code = 'AUS'
  NESTING_EVENT_ID: 37
NESTING_EVENT_TYPE: TRANSACTION
*************************** 4\. row ***************************
          SQL_TEXT: ROLLBACK
  NESTING_EVENT_ID: 37
NESTING_EVENT_TYPE: TRANSACTION
4 rows in set (0.0007 sec)

mysql> SELECT SQL_TEXT, MYSQL_ERRNO,
              NESTING_EVENT_ID,
              NESTING_EVENT_TYPE
         FROM performance_schema.events_statements_history
        WHERE THREAD_ID = PS_THREAD_ID(62)
        ORDER BY EVENT_ID\G
*************************** 1\. row ***************************
          SQL_TEXT: START TRANSACTION
       MYSQL_ERRNO: 0
  NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
*************************** 2\. row ***************************
          SQL_TEXT: UPDATE world.country SET Population = Population + 146000 WHERE Code = 'AUS'
       MYSQL_ERRNO: 0
  NESTING_EVENT_ID: 810
NESTING_EVENT_TYPE: TRANSACTION
*************************** 3\. row ***************************
          SQL_TEXT: INSERT INTO world.city VALUES (4080, 'Darwin', 'AUS', 'Northern Territory', 146000)
       MYSQL_ERRNO: 1213
  NESTING_EVENT_ID: 810
NESTING_EVENT_TYPE: TRANSACTION
*************************** 4\. row ***************************
          SQL_TEXT: SHOW WARNINGS
       MYSQL_ERRNO: 0
  NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
*************************** 5\. row ***************************
          SQL_TEXT: ROLLBACK
       MYSQL_ERRNO: 0
  NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
10 rows in set (0.0009 sec)

Listing 22-10Finding the queries involved in the deadlock

注意，对于连接 id 62(第二个事务)，包含了 MySQL 错误号，第三行将其设置为 1213——这是一个死锁。当遇到错误时，MySQL Shell 自动执行一个SHOW WARNINGS语句，即第 4 行中的语句。还要注意，嵌套事件是事务 2 的ROLLBACK的NULL，而不是事务 1 的ROLLBACK。这是因为死锁触发了整个事务的回滚(所以事务 2 的ROLLBACK没有做任何事情)。

死锁是由事务 1 首先更新city表的填充，然后更新country表的填充触发的。事务 2 首先更新了country表的人口，然后试图将一个新的城市插入到city表中。这是两个工作流以不同顺序更新记录的典型例子，因此容易出现死锁。

总结调查，它包括两个步骤:

分析来自 InnoDB 的死锁信息，以确定死锁中涉及的锁，并获得尽可能多的关于连接的信息。
使用其他来源(如性能模式)来查找有关事务中查询的更多信息。通常有必要分析应用以获得查询列表。

现在您已经知道是什么触发了死锁，那么解决这个问题需要什么呢？

解决方案

死锁是最容易解决的锁情况，因为 InnoDB 会自动选择一个事务作为受害者并回滚它。在前面讨论的死锁中，事务 2 被选为受害者，这可以从死锁输出中看出:

*** WE ROLL BACK TRANSACTION (2)

这意味着对于事务 1，没有什么可做的。事务 2 回滚后，事务 1 可以继续并完成其工作。

对于事务 2，InnoDB 已经回滚了整个事务，所以您需要做的就是重试该事务。记住再次执行所有查询，而不是依赖第一次尝试时返回的值；否则，您可能会使用过时的值。

Tip

时刻准备处理死锁和锁等待超时。对于死锁或当事务在锁等待超时后回滚时，请重试整个事务。对于仅回滚查询的锁等待超时，重试查询可能会增加延迟。

如果死锁相对很少发生，您实际上不需要做更多的事情。死锁是生活中的现实，所以不要因为遇到一些死锁而惊慌。如果死锁造成了重大影响，您需要考虑进行一些更改来防止某些死锁。

预防

减少死锁与减少记录锁争用非常相似，只是在整个应用中以相同的顺序获取锁非常重要。建议再次阅读第 18 章中的“减少锁定问题”一节。减少死锁的要点是减少锁的数量和持有锁的时间，并按照相同的顺序使用锁:

通过将大型事务分成几个较小的事务，并添加索引以减少锁的数量，来减少每个事务所做的工作。
如果事务隔离级别适合于您的应用来减少锁的数量和它们被持有的时间，那么可以考虑使用它。
确保事务只在尽可能短的时间内保持开放。
以相同的顺序访问记录，如果需要的话，可以通过执行SELECT ... FOR UPDATE或SELECT ... FOR SHARE查询来抢占锁。

关于如何研究锁的讨论到此结束。您可能会遇到与本章中讨论的情况不完全匹配的锁情况；然而，调查这些问题的技术是相似的。

摘要

本章向您展示了如何使用 MySQL 中的可用资源来研究与锁相关的问题。本章包括了调查四种不同类型的锁问题的例子:刷新锁、元数据锁、记录锁和死锁。每个问题类型使用 MySQL 的不同特性，包括进程列表、性能模式中的锁表和 InnoDB monitor 输出。

还有许多其他类型的锁会导致锁等待问题。本章中讨论的方法对于调查由其他锁类型引起的问题也大有帮助。最后，成为研究锁的专家的唯一方法是经验，但是本章的技术提供了一个很好的起点。

关于查询分析的第五部分到此结束。第六部分是关于改进查询的，首先讨论如何通过配置来提高性能。

二十三、配置

在本书的第四部分中，有几个影响 MySQL 行为的配置选项的例子。这些选项包括字符集和排序规则的选择、如何创建索引统计信息、优化器应该如何工作等等。还有其他直接或间接影响查询性能的选项。本章将考虑其他地方没有涉及到的最常用的选项，以及配置 MySQL 时的一些一般注意事项。

本章从一些关于更改配置的“最佳实践”开始。接下来的部分是关于 InnoDB、查询缓冲区和内部临时表的。

最佳实践

当您着手进行配置更改时，有必要记住一些原则，这些原则可以让您更成功地进行配置更改。将讨论的最佳实践包括以下内容:

警惕最佳实践。
使用监控来验证效果。
一次更改一个选项。
做出相对较小的增量变化。
越少越好。
请确保您了解该选项的作用。
考虑一下副作用。

最佳实践列表的第一项是对最佳实践保持警惕，这听起来可能有点保守。意思是，当你看到一些建议时，你不应该直接跳到前面去应用。

没有两个系统是完全相同的，因此，虽然一个建议通常可能是好的，但您仍然需要考虑它是否也适用于您的系统。另一个问题是查看适用于旧版本 MySQL 或 8 GiB 内存太多的时候的建议。如果你用谷歌搜索一些设置，你可能会看到很多年前写的推荐。类似地，一段时间前对您的系统有效的建议可能会由于应用工作负载的变化而不再有效。最后，即使某个建议会提高系统的性能，也可能会有副作用，比如丢失已提交的更改的风险，这是您无法接受的。

Tip

警惕最佳实践的建议也适用于本书中的建议。始终考虑它们如何应用于您的系统。

那么，您应该如何处理配置变更呢？应用第 2 章中描述的原则。图 23-1 概括了这些步骤。

img/484666_1_En_23_Fig1_HTML.jpg

图 23-1

性能调整生命周期

您首先定义问题是什么，然后通过您的监控系统或通过定时查询或类似方式收集基线。基线也可以是可观测量的组合。然后你可以定义优化的目标。很重要的一点是，你要定义什么是足够好的，否则你永远也做不完。接下来的步骤是确定原因，并据此找到解决方案。最后，您实施解决方案，并通过与基线进行比较来验证效果。如果问题没有解决，或者你已经确定了多个问题，你可以重新开始。

监控在这个过程中非常重要，因为它既用于定义问题，收集基线，又用于验证效果。如果跳过这些步骤，您就不知道您的解决方案是否有效，也不知道它是否会影响其他查询。

当你决定一个解决方案时，尽可能小的改变。这既适用于您打开旋钮的配置选项的数量，也适用于您旋转旋钮的程度。如果您一次更改多个选项，您将无法衡量每个更改的效果。例如，两个变化可能会相互抵消，所以当其中一个变化非常有效，而另一个使情况变得更糟时，您认为解决方案不起作用。

配置选项通常也有一个最佳点。如果设置太小，选项所代表的功能就不能充分发挥作用。如果设置太大，特性的开销会变得比好处更糟。在这两者之间，您可以在开销有限的情况下获得特性优势的最佳组合。如图 23-2 所示。

img/484666_1_En_23_Fig2_HTML.png

图 23-2

期权价值和业绩之间的典型关系

通过微小的增量变化，你可以最大化找到最佳点的机会。

这与下一点有关:小往往更好。例如，仅仅因为您有足够的内存来增加每个查询或每个连接的缓冲区，并不意味着增加缓冲区大小会使查询更快。这当然取决于这一原则适用范围的选择。对于 InnoDB 缓冲池的大小，最好使用相对较大的缓冲区，因为它有助于减少磁盘 I/O 并从内存中提供数据。关于缓冲池需要记住的一个关键点是，内存分配只在 MySQL 启动和动态增加缓冲池大小时才会发生。但是，对于像连接缓冲区这样的缓冲区，可能会为单个查询分配多次，分配缓冲区的巨大开销可能会成为一个问题。这将在“查询缓冲区”一节中进一步讨论在所有情况下，对于与资源相关的选项，您需要记住分配给一个功能的资源不能用于其他功能。

“越少越好”的概念既适用于配置选项的最佳值，也适用于您要优化的选项数量。您在配置文件中设置的选项越多，您的配置文件就变得越混乱，并且越难保持对已更改内容及其原因的概述。(这也有助于按功能对设置进行分组，例如，将所有 InnoDB 设置放在一起。)如果您习惯于包含设置为默认值的选项，最好还是不要包含这些选项，因为包含这些选项意味着您将错过对默认值的更改，这些更改是作为优化默认配置的一部分来实现的，以反映 MySQL 内部的更改或标准硬件的更改。

Note

在 MySQL 5.6 和更高版本中，已经做了大量工作来改进 MySQL 配置选项的默认值。基于开发团队的测试以及 MySQL 支持团队、客户和社区成员的反馈，这些变化主要发生在主要版本之间。

建议开始时设置尽可能少的选项。您很可能想要设置 InnoDB 缓冲池、重做日志以及可能的表缓存的大小。您可能还希望设置一些路径和端口，并且可能要求启用一些功能，如全局事务标识符(GTIDs)或组复制。除此之外，只根据观察做出改变。

Tip

从最小配置开始，只设置 InnoDB 缓冲池和重做日志、路径和端口的大小，并启用所需的功能。否则，仅根据观察结果进行配置更改。

列表中的最后两点是相关的:确保您理解选项的作用，并考虑副作用。了解该选项的作用有助于您确定该选项对您的案例是否有用，以及该选项可能具有哪些其他效果。作为一个例子，考虑sync_binlog选项。这表明二进制日志的更新应该多久同步到磁盘。在 MySQL 8 中，默认情况下是每次提交时都进行同步，这对于同步性能较差的磁盘来说会显著影响查询性能。因此，很容易将sync_binlog设置为 0，这将禁用强制同步；但是，副作用可以接受吗？如果您不同步这些更改，那么它们只会存在于内存中，直到有其他东西(例如内存被其他人使用)强制进行同步。这意味着如果 MySQL 崩溃，那么更改就会丢失，如果您有一个副本，您将不得不重建它。这可以接受吗？

即使您可以接受潜在的丢失二进制日志事件，使用sync_binlog = 0还有一个更微妙的副作用。仅仅因为事务提交时没有发生同步并不意味着它永远不会发生。二进制日志的最大大小是 1gb(max_binlog_size选项)加上最后一个事务的大小，旋转二进制日志意味着旧的二进制日志被刷新到磁盘。如今，这通常意味着 MySQL 将结束写 1 GiB，然后一次全部刷新。即使在高速磁盘上，写出一千兆字节的数据也需要相当长的时间。与此同时，MySQL 不能执行任何提交，因此任何发出提交的连接(无论是隐式的还是显式的)都将停止，直到同步完成。这可能会让人感到意外，而且拖延的时间可能会长到让最终用户(可能是客户)感到不安。本书的作者已经看到了在几秒到半分钟的范围内由二进制日志旋转引起的提交延迟。简而言之，sync_binlog = 0提供了总体最高的吞吐量和平均提交延迟，但是sync_binlog = 1提供了最佳的数据安全性和最可预测的提交延迟。

本章的其余部分提供了一些与查询调优相关的选项的建议，这些选项最常需要更改。

InnoDB 概述

假设所有涉及表的查询都与 InnoDB 存储引擎交互，那么花些时间查看 InnoDB 参数的配置是很重要的。这些包括 InnoDB 缓冲池的大小和重做日志的大小——这两个配置需要针对大多数生产系统进行调整。

在讨论配置选项之前，有必要回顾一下数据如何在表空间和缓冲池之间流动，以及如何通过重做日志系统返回表空间。图 23-3 显示了该流程的简单概述。

img/484666_1_En_23_Fig3_HTML.jpg

图 23-3

InnoDB 数据流

当查询请求数据时，总是从缓冲池中读取数据。如果数据不在缓冲池中，就从表空间中获取。InnoDB 将缓冲池分为两部分:旧块子列表和新块子列表。数据总是被读入整页的旧块子列表的头(顶部)。如果再次需要来自同一页面的数据，该数据将被移动到新块子列表中。这两个子列表都使用最近最少使用的 (LRU)原则来确定在需要为新页面腾出空间时应该删除哪些页面。页面从旧块子列表的缓冲池中被逐出。由于新页面在被提升到新的块子列表之前会在旧的块子列表中花费时间，这意味着如果一个页面被使用过一次，但随后没有被使用，那么它将很快被再次从缓冲池中清除。这可以防止大型的罕见扫描(如备份)污染缓冲池。

当查询更新更改时，更改被写入内存中的日志缓冲区，并从那里写入，稍后刷新到至少由两个文件组成的重做日志。重做日志文件以循环的方式使用，所以写操作从一个文件的开头开始，然后填满该文件，当该文件填满时，InnoDB 继续处理下一个文件。这些文件的大小和数量是固定的。当日志到达最后一个文件的末尾时，InnoDB 会返回到第一个文件的开头。

这些更改还被写回缓冲池，并被标记为脏，直到它们可以被刷新到表空间文件。InnoDB 使用双写缓冲区来确保在崩溃的情况下可以检测到写操作是否成功。双写缓冲区是必要的，因为大多数文件系统不保证原子写入，因为 InnoDB 页面大于文件系统块大小。在撰写本文时，唯一可以安全禁用双写缓冲区的文件系统是 ZFS。

Caution

即使文件系统应该处理 InnoDB 页面的原子写入，它在实践中也可能不起作用。这方面的一个例子是启用了日志功能的 EXT4 文件系统，它在理论上应该是安全的，没有双写缓冲区，但实际上可能会导致数据损坏。

下一节将讨论的配置选项围绕数据的生命周期。

InnoDB 缓冲池

InnoDB 缓冲池是 InnoDB 缓存数据和索引的地方。由于所有数据请求都要经过缓冲池，从性能角度来看，它自然成为 MySQL 的一个非常重要的部分。这里将讨论缓冲池的几个重要参数。

表 23-1 总结了与缓冲池相关的配置选项，您很可能需要更改这些选项来优化查询性能。

表 23-1

缓冲池的重要配置选项

选项名称

缺省值

| | --- | --- | --- | | innodb_buffer_pool_size | 128 兆字节 | InnoDB 缓冲池的总大小。 | | innodb_buffer_pool_instances | 自动调整大小 | 缓冲池分成多少部分。如果总大小小于 1gb，默认值为 1，否则为 8。对于 32 位 Windows，缺省值为 1.3 GiB 以下的 1；否则，每个实例为 128 MiB。最大实例数为 64。 | | innodb_buffer_pool_dump_pct | Twenty-five | 转储缓冲池内容(备份缓冲池内容)时，缓冲池中最近使用的页面所占的百分比。 | | innodb_old_blocks_time | One thousand | 在新的页面读取将其提升到新的块子列表之前，该页面必须在旧的块子列表中驻留多长时间(毫秒)。 | | innodb_old_blocks_pct | Thirty-seven | 旧块子列表占整个缓冲池的百分比应该有多大。 | | innodb_io_capacity | Two hundred | 在非紧急情况下，允许 InnoDB 每秒进行多少次 I/O 操作。 | | innodb_io_capacity_max | Two thousand | 在紧急情况下，允许 InnoDB 每秒进行多少次 I/O 操作。 | | innodb_flush_method | unbuffered或者fsync | InnoDB 用于将更改写入磁盘的方法。在 Microsoft Windows 上默认为unbuffered,在 Linux/Unix 上默认为fsync。 |

这些选项将在本节的剩余部分更详细地讨论，从与缓冲池大小相关的选项开始。

Note

选项key_buffer_size与缓存 InnoDB 索引无关。该选项在 MySQL 早期获得了它的名字，当时 MyISAM 存储引擎是主要的存储引擎，所以不需要在选项前面加上前缀mysiam。除非使用 MyISAM 表，否则没有理由配置key_buffer_size。

缓冲池大小

这些选项中最重要的是缓冲池的大小。128 MiB 的缺省大小很适合在您的笔记本电脑上设置一个测试实例，而不会耗尽它的内存(这也是为什么缺省值这么小的原因)，但是对于生产系统，您很可能希望分配更多的内存。您可以从增加大小中受益，直到您的工作数据集适合缓冲池。工作数据集是执行查询所需的数据。通常，这是整个数据集的子集，因为一些数据是不活动的，例如，因为它涉及过去的事件。

Tip

如果您有一个大的缓冲池并且启用了核心转储，那么禁用innodb_buffer_pool_in_core_file选项以避免在发生核心转储时转储整个缓冲池。该选项在 MySQL 8.0.14 和更高版本中可用。

您可以使用下面的公式获得缓冲池命中率——即不从磁盘读取而直接从缓冲池完成页面请求的频率: $Hit\ Rate=100-\left(\frac{Innodb\_ pages\_ read}{Innodb\_ buffer\_ pool\_ read\_ requests}\right)$ 。两个变量Innodb_pages_read和Innodb_buffer_pool_read_requests是状态变量。清单 23-1 展示了一个如何计算缓冲池命中率的例子。

mysql> SELECT Variable_name, Variable_value
         FROM sys.metrics
        WHERE Variable_name IN
                ('Innodb_pages_read',
                 'Innodb_buffer_pool_read_requests')\G
*************************** 1\. row ***************************
 Variable_name: innodb_buffer_pool_read_requests
Variable_value: 141319
*************************** 2\. row ***************************
 Variable_name: innodb_pages_read
Variable_value: 1028
2 rows in set (0.0089 sec)

mysql> SELECT 100 - (100 * 1028/141319) AS HitRate;
+---------+
| HitRate |
+---------+
| 99.2726 |
+---------+
1 row in set (0.0003 sec)

Listing 23-1Calculating the buffer pool hit rate

在这个例子中，99.3%的页面请求都是从缓冲池中完成的。这个数字适用于所有缓冲池实例。如果要确定给定期间的命中率，需要收集期间开始和结束时状态变量的值，并在计算中使用它们之间的差值。您还可以从信息模式中的INNODB_BUFFER_POOL_STATS视图或 InnoDB 监控器中获取速率。在这两种情况下，比率都是按照每千个请求返回的。清单 23-2 展示了这样的例子。您需要确保已经执行了一些查询来生成一些缓冲池活动，以获得有意义的结果。

mysql> SELECT POOL_ID, NUMBER_PAGES_READ,
              NUMBER_PAGES_GET, HIT_RATE FROM information_schema.INNODB_BUFFER_POOL_STATS\G
*************************** 1\. row ***************************
          POOL_ID: 0
NUMBER_PAGES_READ: 1028
 NUMBER_PAGES_GET: 141319
         HIT_RATE: 1000
1 row in set (0.0004 sec)

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1\. row ***************************
  Type: InnoDB
  Name:
Status:
=================================================
2019-07-20 19:33:12 0x7550 INNODB MONITOR OUTPUT
=================================================
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 536469
Buffer pool size   8192
Free buffers       6984
Database pages     1190
Old database pages 428
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 38, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1028, created 237, written 1065
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1190, unzip_LRU len: 0
I/O sum[6]:cur[0], unzip sum[0]:cur[0]
...

Listing 23-2Getting the buffer pool hit rate directly from InnoDB

重要的是要认识到，InnoDB 直接返回的命中率是自上次检索缓冲池统计数据以来的一段时间内的命中率，并且是针对每个缓冲池实例的。如果你想完全控制某个时间段的命中率，你需要自己计算，要么使用状态变量，要么使用INNODB_BUFFER_POOL_STATS视图中的NUMBER_PAGES_READ和NUMBER_PAGES_GET。

您的目标应该是让缓冲池命中率尽可能接近 100%或 1000/1000。也就是说，在某些情况下，这是不可能的，因为数据量不可能适合内存。在这种情况下，缓冲池命中率仍然很有用，因为它允许您监控缓冲池随时间推移的有效性，并与一般的查询统计数据进行比较。如果缓冲池命中率随着查询性能的下降而开始下降，那么您应该考虑做一些准备，以便可以增加缓冲池的大小。

缓冲池实例

MySQL 从版本 5.5 开始就支持多个缓冲池实例。引入它的原因是典型的数据库工作负载有越来越多的查询并行运行，每台主机的 CPU 越来越多。这导致在访问缓冲池中的数据时出现互斥争用。

减少争用的解决方案之一是允许将缓冲池分成多个实例，每个实例使用不同的互斥体。实例的数量由innodb_buffer_pool_instances选项控制。用innodb_buffer_pool_size指定的缓冲池总量在实例之间平均分配。除了在 32 位 Windows 上，默认情况下，小于 1gb 的缓冲池有一个实例。对于较大的缓冲池，缺省值是八个实例。最大实例数为 64。

对于单线程工作负载，最佳方案是将所有内存放在一个缓冲池中。您的工作负载越并行，越多的额外实例有助于减少争用。增加缓冲池数量的确切效果取决于并行查询对存储在不同页面中的数据的请求程度。如果所有请求都是针对不同页面的，那么您可以从增加实例的数量和并发查询的数量中获益。如果所有查询都在同一个页面中请求数据，那么使用更多实例并没有什么好处。一般来说，注意不要让每个缓冲池实例太小。如果没有监控数据来证明，对于至少 8gb 大的缓冲池，允许每个实例为 1gb 或更大。

转储缓冲池

数据库重启的一个常见问题是，在缓存预热之前，缓存暂时无法正常工作。这可能导致非常差的查询性能和最终用户满意度。对此的解决方案是在关机时在缓冲池中存储一个最常用页面的列表，并在重启后立即将这些页面读入缓冲池，即使还没有查询请求它们。

默认情况下，这个特性是启用的，要考虑的主要问题是要在转储中包含多少缓冲池。这是由innodb_buffer_pool_dump_pct选项控制的，该选项接受要包含的页面百分比。默认值为 25%。这些页面是从新块子列表的头部读取的，因此包含的是最近使用的页面。

转储只包括对应该读取的页面的引用，所以转储的大小大约是每页 8 个字节。如果您有一个 128 GiB 的缓冲池，并且正在使用 16 个 KiB 页面，那么缓冲池中有 8，388，608 个页面。如果对缓冲池转储使用默认值 25%,那么转储大约为 16 MiB。转储存储在数据目录中的文件ib_buffer_pool中。

Tip

当您通过复制表空间文件创建备份(物理或原始备份)时，也要备份ib_buffer_pool文件。您可以使用innodb_buffer_pool_dump_now选项创建最近使用的页面的新副本。例如，这是由 MySQL 企业备份自动完成的。然而，对于逻辑备份(数据导出为 SQL 或 CSV 文件)，ib_buffer_pool文件没有用。

如果在重新启动后遇到查询速度慢的问题，可以考虑增加innodb_buffer_pool_dump_pct以在转储中包含更大部分的缓冲池。增加该选项的主要缺点是，随着更多的页面引用被导出，关闭需要更长的时间，ib_buffer_pool文件变得更大，重启后加载页面需要更长的时间。将页面加载回缓冲池是在后台进行的，但是如果包含更多的页面，那么在将所有最重要的页面恢复到缓冲池中之前，可能需要更长的时间。

旧块子列表

如果您有一个大于缓冲池的数据集，一个潜在的问题是大型扫描可能会拉入仅用于该扫描的数据，然后在很长一段时间内不再使用。当这种情况发生时，您会面临更频繁使用的数据被从缓冲池中排除的风险，并且需要这些数据的查询会受到影响，直到扫描完成并且恢复了平衡。由mysqlpump和mysqldump进行的逻辑备份就是触发该问题的作业的很好例子。备份过程需要扫描所有数据，但在下次备份之前不再需要这些数据。

为了避免这个问题，缓冲池被分成两个子列表:新的和旧的块子列表。当从表空间中读取页面时，它们首先被“隔离”在旧块子列表中，只有当页面在缓冲池中的时间超过innodb_old_blocks_time毫秒并被再次使用时，它才会被移动到新块子列表中。这有助于防止缓冲池扫描，因为单个表扫描只会快速连续地读取一页中的行，然后不会再次使用该页。这使得 InnoDB 可以在扫描完成后自由地删除页面。

innodb_old_blocks_time的默认值是 1000 毫秒，对于大多数工作负载来说，这足以避免扫描污染缓冲池。如果您有正在进行扫描的作业，其中该作业在短时间(但长于一秒)后再次返回到相同的行，那么如果您不希望后续访问将页面提升到新的块子列表，则可以考虑增加innodb_old_blocks_time。

旧块子列表的大小由innodb_old_blocks_pct选项设置，该选项指定应该用于旧块子列表的缓冲池的百分比。默认使用 37%。如果您有一个大的缓冲池，您可能想要减少innodb_old_blocks_pct以避免新加载的页面占用太多的缓冲池。旧块子列表的最佳大小还取决于将临时页面加载到缓冲池中的速率。

您可以监控新旧块子列表的使用情况，类似于如何找到命中率。清单 23-3 显示了使用INNODB_BUFFER_POOL_STATS视图和 InnoDB 监控器的示例输出。

mysql> SELECT PAGES_MADE_YOUNG,
              PAGES_NOT_MADE_YOUNG,
              PAGES_MADE_YOUNG_RATE,
              PAGES_MADE_NOT_YOUNG_RATE,
              YOUNG_MAKE_PER_THOUSAND_GETS,
              NOT_YOUNG_MAKE_PER_THOUSAND_GETS
         FROM information_schema.INNODB_BUFFER_POOL_STATS\G
*************************** 1\. row ***************************
                PAGES_MADE_YOUNG: 98
            PAGES_NOT_MADE_YOUNG: 354
           PAGES_MADE_YOUNG_RATE: 0.00000000383894451752074
       PAGES_MADE_NOT_YOUNG_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 2
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 10
1 row in set (0.0005 sec)

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1\. row ***************************
  Type: InnoDB
  Name:
Status:
===============================================
2019-07-21 12:06:49 0x964 INNODB MONITOR OUTPUT
===============================================
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 463009
Buffer pool size   8192
Free buffers       6974
Database pages     1210
Old database pages 426
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 98, not young 354
0.00 youngs/s, 0.00 non-youngs/s
Pages read 996, created 223, written 430
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 10 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1210, unzip_LRU len: 0
I/O sum[217]:cur[0], unzip sum[0]:cur[0]
...

Listing 23-3Obtaining information about the new and old blocks sublists

Pages make young表示位于旧块子列表中的页面被移动到新块子列表中。一个页面不年轻意味着它停留在旧块子列表中。这两个速率列是自上次获取数据以来的每秒速率。每千页获取数是每千页请求中保留在旧块子列表中的年轻页数；这也是自上次报告以来的情况。

您可能需要配置旧块子列表的一个可能迹象是扫描进行时缓冲池命中率下降。如果使页面年轻的比率很高，并且同时有大量扫描，则应该考虑增加innodb_old_blocks_time以防止后续读取使页面年轻。或者，考虑减少innodb_old_blocks_pct以在旧块子列表中经过较短时间后从扫描中驱逐页面。

反之亦然，如果您扫描的次数很少，并且页面停留在旧块子列表中(非年轻的 making stats 很高)，那么您应该考虑减少innodb_old_blocks_time以更快地提升页面，或者增加innodb_old_blocks_pct以允许页面在被驱逐之前在旧块子列表中保留更长时间。

刷新页面

InnoDB 需要平衡将更改合并到表空间文件的难度。如果它太懒惰，重做日志最终会满，需要强制刷新，但是如果它太努力，会影响系统其他部分的性能。不用说，得到正确的等式是复杂的。除了在崩溃恢复期间或在恢复物理备份(如使用 MySQL Enterprise Backup 创建的备份)之后，合并是通过将脏页从缓冲池刷新到表空间文件来完成的。

在最近的 MySQL 版本中，只要有足够的重做日志，您通常不需要做太多事情，因为 InnoDB 使用的自适应刷新算法擅长取得良好的平衡。主要有三个选项需要考虑:两个用于设置系统的 I/O 容量，一个用于设置刷新方法。

I/O 容量的两个选项是innodb_io_capacity和innodb_io_capacity_max。innodb_io_capacity选项在正常刷新更改时使用，应该设置为 InnoDB 每秒允许使用的 I/O 操作数。在实践中，不太容易知道使用什么值。默认值为 200，大致相当于低端 SSD。通常高端存储可以受益于将容量设置为几千。最好从一个相对较低的值开始，如果您的监控显示刷新落后并且有备用 I/O 容量，则增加该值。

Note

innodb_io_capacity和innodb_io_capacity_max选项不仅用于确定 InnoDB 将脏页刷新到表空间文件的速度。还包括其他 I/O 活动，如从更改缓冲区合并数据。

innodb_io_capacity_max选项告知如果冲洗落后，允许 InnoDB 多用力。默认值是最小值 2000 和两倍的值innodb_io_capacity。在大多数情况下，默认值工作良好，但如果您有一个低端磁盘，您应该考虑将设置减少到 1000 以下。如果您遇到异步刷新(这将在重做日志中讨论),并且您的监控显示 InnoDB 没有使用足够的 I/O 容量，请增加innodb_io_capacity_max的值。

Caution

将 I/O 容量设置得太高会严重影响系统的性能。

脏页的刷新可以通过几种方式来执行，例如，使用操作系统 I/O 缓存或避免它。这由innodb_flush_method选项控制。在 Microsoft Windows 上，您可以在值unbuffered(默认和推荐)和normal之间进行选择。在支持以下值的 Linux 和 Unix 上，选择更加困难:

fsync : 这是默认值。InnoDB 使用fsync()系统调用。数据也将缓存在操作系统 I/O 缓存中。
O_DSYNC : InnoDB 在打开重做日志文件(同步写入)时使用O_SYNC选项，并对数据文件使用fsync。之所以用O_SYNC代替O_DSYNC，是因为O_DSYNC已经被证明太不安全，所以用O_SYNC代替。
O_DIRECT : 这和fsync类似，但是绕过了操作系统 I/O 缓存。它只适用于表空间文件。
O_DIRECT_NO_FSYNC : 除了跳过fsync()系统调用之外，与O_DIRECT相同。由于 EXT4 和 XFS 文件系统中的错误，在 MySQL 8.0.14 实现这些错误的解决方案之前，使用这种方法是不安全的。如果重做日志文件与表空间文件位于不同的文件系统上，那么应该使用O_DIRECT而不是O_DIRECT_NO_FSYNC。在大多数生产系统中，这是最好的选择。

此外，有几个实验性的刷新方法应该只用于性能测试。 ¹ 这里不涉及这些实验方法。

哪种冲洗方法将提供最佳性能是非常复杂的。由于 InnoDB 自己缓存数据，并且比操作系统做得更好(因为 InnoDB 知道数据是如何使用的)，很自然地认为O_DIRECT选项中的一个会工作得最好。这也是通常的情况；但是，生活更复杂，在某些情况下，fsync更快。因此，您需要在您的系统上进行测试，以确定哪种冲洗方法效果最好。还有一点就是，在不重启操作系统的情况下重启 MySQL 时，如果使用fsync flush 方法，那么 InnoDB 在第一次读取数据时就可以受益于 I/O 缓存。

在数据流的另一端是重做日志。

重做日志

重做日志用于保存提交的更改，同时提供顺序 I/O 以尽可能提高性能。为了提高性能，在将更改写入日志文件之前，会先将其写入内存中的日志缓冲区。

然后，后台进程通过双写缓冲区将缓冲池中的更改合并到表空间中。尚未合并到表空间文件中的页面不能从缓冲池中收回，因为它们被认为是脏的。页面是脏的意味着它的内容与表空间中的相同页面不同，因此在合并更改之前，不允许 InnoDB 从表空间中读取页面。

表 23-2 总结了重做日志相关的配置选项，您很可能需要更改这些选项来优化查询性能。

表 23-2

重做日志的重要配置选项

选项名称

缺省值

| | --- | --- | --- | | innodb_log_buffer_size | 16 兆字节 | 日志缓冲区的大小，重做日志事件在写入磁盘上的重做日志文件之前存储在内存中。 | | innodb_log_file_size | 48 兆字节 | 重做日志中每个文件的大小。 | | innodb_log_files_in_group | Two | 重做日志中的文件数。必须至少有两个文件。 |

本节的剩余部分将介绍这些选项。

日志缓冲区

日志缓冲区是一个内存缓冲区，InnoDB 使用它来缓冲重做日志事件，然后将它们写入磁盘。这允许事务将更改保留在内存中，直到缓冲区满或更改被提交。日志缓冲区的默认大小是 16 MiB。

如果有大型事务或大量较小的并发事务，建议增加日志缓冲区的大小。使用innodb_log_buffer_size选项设置日志缓冲区的大小。在 MySQL 8 中(与旧版本不同)，可以动态改变大小。理想情况下，缓冲区应该足够大，这样 InnoDB 只需在提交更改时写出更改；然而，这当然应该与内存的其他用途进行权衡。如果单个事务在缓冲区中有大量更改，也会降低提交速度，因为此时所有数据都必须写入重做日志，所以对于非常大的日志缓冲区大小，这是另一个需要考虑的问题。

一旦日志缓冲区已满或事务被提交，重做日志事件将被写入重做日志文件。

日志文件

重做日志的大小是固定的，由许多文件(至少两个)组成，每个文件的大小都相同。配置重做日志时的主要考虑是确保它们足够大，不会变得“满”实际上，满意味着触发异步刷新时容量的 75%。异步刷新会阻塞触发刷新的线程，而原则上其他线程可以继续工作。在实践中，异步刷新是如此凶猛，以至于它通常会导致系统突然停止。还有一个同步刷新，它在容量达到 90%时触发，并阻塞所有线程。

您可以通过两个选项innodb_log_file_size和innodb_log_files_in_group来控制尺寸。总重做日志大小是这两个值的乘积。建议将文件大小设置为 1–2 GiB，并调整文件数量，以获得最少两个文件的所需总大小。不让每个重做日志文件变得非常大的原因是它们被缓冲在操作系统 I/O 缓存中(即使使用innodb_flush_method = O_DIRECT)，文件越大，重做日志就越有可能使用 I/O 缓存中的大量内存。重做日志的总大小不允许超过 512 GiB，最多可以有 100 个文件。

Note

重做日志越大，可以存储的尚未从缓冲池刷新到表空间的更改就越多。这可能会增加崩溃时的恢复时间以及执行正常关机所需的时间。

确定重做日志有多大的最佳方法是通过监控解决方案来监控日志随着时间的推移有多满。图 23-4 显示了显示重做日志文件的 I/O 速率和通过检查点延迟测量的重做日志使用情况的图表示例。如果您想创建类似的东西，您需要执行密集的写工作日志；employees数据库对此很有用。具体需要什么取决于硬件、配置、哪些其他进程使用这些资源等等。

img/484666_1_En_23_Fig4_HTML.jpg

图 23-4

重做日志的时间序列图表

确保重做日志中没有检查点的部分没有 75%的标记。在本例中，最高峰值出现在重做日志的 96 MiB(14:37)中的大约 73 MiB 处，这意味着几乎 76%的重做日志用于脏页。这意味着在那段时间有一个异步刷新，这会影响当时运行的查询。您可以使用重做日志文件的 I/O 率来了解文件系统对重做日志进行 I/O 的压力有多大。

手动检查当前重做日志使用情况的最佳方式是启用log_lsn_current和log_lsn_last_checkpoint InnoDB 指标，它们允许您查询当前日志序列号和创建最后一个检查点时的日志序列号。检查点延迟占总重做日志的百分比计算为 $Lag\ Pct=100\ast \frac{\log \_ lsn\_ last\_ checkpoint-\log \_ lsn\_ current}{\#\log file s\ast \log file\ size}$ 。

您可以从information_schema或sys.metrics视图的INNODB_METRICS表中获取当前值。或者，也可以从 InnoDB 监控器的LOG部分获取日志序列号，而不管指标是否已启用。清单 23-4 展示了一个使用这些资源确定检查点延迟的例子。

mysql> SET GLOBAL innodb_monitor_enable = 'log_lsn_current',
           GLOBAL innodb_monitor_enable = 'log_lsn_last_checkpoint';
Query OK, 0 rows affected (0.0004 sec)

mysql> SELECT *
         FROM sys.metrics
        WHERE Variable_name IN ('log_lsn_current',
                                'log_lsn_last_checkpoint')\G
*************************** 1\. row ***************************
 Variable_name: log_lsn_current
Variable_value: 1678918975
          Type: InnoDB Metrics - log
       Enabled: YES
*************************** 2\. row ***************************
 Variable_name: log_lsn_last_checkpoint
Variable_value: 1641343518
          Type: InnoDB Metrics - log
       Enabled: YES
2 rows in set (0.0078 sec)

mysql> SELECT ROUND(
                100 * (
                  (SELECT COUNT
                     FROM information_schema.INNODB_METRICS
                    WHERE NAME = 'log_lsn_current')
                - (SELECT COUNT
                     FROM information_schema.INNODB_METRICS
                    WHERE NAME = 'log_lsn_last_checkpoint')
                ) / (@@global.innodb_log_file_size
                     * @@global.innodb_log_files_in_group
              ), 2) AS LogUsagePct;
+-------------+
| LogUsagePct |
+-------------+
|       39.25 |
+-------------+
1 row in set (0.0202 sec)

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1\. row ***************************
  Type: InnoDB
  Name:
Status:
===============================================
2019-07-21 17:04:09 0x964 INNODB MONITOR OUTPUT
===============================================
...
---
LOG
---
Log sequence number          1704842995
Log buffer assigned up to    1704842995
Log buffer completed up to   1704842235
Log written up to            1704842235
Log flushed up to            1696214896
Added dirty pages up to      1704827409
Pages flushed up to          1668546370
Last checkpoint at           1665659636
5360916 log i/o's done, 23651.73 log i/o's/second
...

Listing 23-4Querying the redo log usage

首先启用所需的 InnoDB 指标。启用它们的开销非常小，所以让它们保持启用状态是没问题的。然后从sys.metrics视图中查询指标值，然后使用INNODB_METRICS表直接计算滞后。最后，日志序列号也可以在 InnoDB monitor 输出中找到。日志序列号变化非常快，因此即使您快速连续地查询它们，如果有任何工作正在进行，它们也会发生变化。这些值反映了 InnoDB 中已经完成的工作量(以字节为单位),因此它们在任何两个系统上都是不同的。

并行查询执行

从 MySQL 8.0.14 开始，InnoDB 对并行执行查询的支持变得有限。这是通过使用多个读取线程对聚集索引或分区执行扫描来实现的。在 8.0.17 中，实现得到了很大的改进，这也是本文考虑的内容。

并行扫描会根据将要扫描的索引子树的数量自动进行。通过设置innodb_parallel_read_threads选项，您可以配置 InnoDB 可以为所有连接的并行执行创建的最大线程数。这些线程是作为后台线程创建的，只在需要时才出现。如果所有并行线程都在使用中，InnoDB 将恢复单线程执行任何额外的查询，直到线程再次可用。

从 MySQL 8.0.18 开始，并行扫描用于没有任何过滤条件的SELECT COUNT(*)(允许多个表)以及由CHECK TABLE执行的两次扫描中的第二次。

通过查找名为thread/innodb/parallel_read_thread的线程，您可以从performance_schema.threads表中看到并行线程的当前使用情况。如果您想尝试这个特性，您可以在 MySQL Shell 中使用 Python 模式来继续计算employees.salaries表中的行数:

Py> for i in range(100): session.run_sql('SELECT COUNT(*) FROM employees.salaries')

带有innodb_parallel_read_threads = 4(默认)的performance_schema.threads的输出示例如下

mysql> SELECT THREAD_ID, TYPE, THREAD_OS_ID
         FROM performance_schema.threads
        WHERE NAME = 'thread/innodb/parallel_read_thread';
+-----------+------------+--------------+
| THREAD_ID | TYPE       | THREAD_OS_ID |
+-----------+------------+--------------+
|        91 | BACKGROUND |        12488 |
|        92 | BACKGROUND |         5232 |
|        93 | BACKGROUND |        13836 |
|        94 | BACKGROUND |        24376 |
+-----------+------------+--------------+
4 rows in set (0.0005 sec)

您可以尝试使用较小的表，比如在world数据库中的表，并查看后台线程数量的差异。

如果您看到所有配置的读取线程大部分时间都在使用，并且您有空闲的 CPU，那么您可以考虑增加innodb_parallel_read_threads的值。支持的最大值是 256。记住为单线程查询留下足够的 CPU 资源。

如果您看到信号量等待，并且对 CPU 的监控表明在存在许多并行读取线程的情况下存在 CPU 资源争用，那么您可以考虑减少innodb_parallel_read_threads来降低查询的并行性。

查询缓冲区

MySQL 在查询执行期间使用几个缓冲区。这些包括存储连接中使用的列值、用于排序的缓冲区等等。人们很容易认为这些缓冲越多越好，但通常情况并非如此。相反，往往越少越好。本节讨论为什么会这样。

当 MySQL 需要为查询或查询的一部分使用缓冲区时，有几个因素决定了对查询的影响。这些因素包括以下内容:

对于所需的工作，缓冲区是否足够大？
内存够吗？
分配缓冲区的成本是多少？

如果缓冲区不够大，算法就不能以最佳状态运行，因为需要更多的迭代，或者需要溢出到磁盘。但是，在某些情况下，缓冲区的配置值用作最小大小，而不是最大大小。例如，大小由join_buffer_size设置的连接缓冲区就是这种情况。最小大小始终是分配的，如果在使用它进行连接时，它不足以容纳单行中所需的列，那么它将根据需要进行扩展。

关于记忆的问题也很相关。大概 MySQL 崩溃最常见的原因就是操作系统内存不足，操作系统杀死了 MySQL。对于单个查询来说，各种缓冲区所需的内存量看起来并不多，但是如果您将所有并发执行的查询相乘，并加上空闲连接和全局分配所需的内存，您可能会突然发现内存不足。这也可能导致交换，这是一个主要的性能杀手。

最后一点对大多数人来说更令人惊讶。分配内存是有成本的，通常你需要的内存越多，每字节的成本就越高。例如，在 Linux 上，分配方法的改变有不同的阈值。这些阈值取决于 Linux 发行版，但可能是 256 KiB 和 2 MiB。如果超过其中一个阈值，分配方法就会变得更加昂贵。这是选项join_buffer_size、sort_buffer_size和read_rnd_buffer_size的默认值为 256 KiB 的部分原因。这意味着有时缓冲区太小会更好，因为优化缓冲区大小的好处不足以提高性能来补偿分配更多内存的开销。

Tip

缓冲区的分配是需要改进的地方之一，因此在某些情况下，升级可以让您使用更大的缓冲区，而没有传统的缺点。例如，在 MySQL 8.0.12 和更高版本中，使用了排序缓冲区的新算法。这意味着在 Linux/Unix 上，对于 Windows 上的非并发排序，内存是以增量方式分配的，这使得为sort_buffer_size设置一个较大的值更安全。尽管如此，您仍然需要考虑单个查询允许使用多少内存。

结论是，最好保守地使用在查询期间分配的缓冲区。保持较小的全局设置(默认值是一个很好的起点),并且只在您可以证明增加设置会带来显著改善的查询中增加设置。

内部临时表

当一个查询需要存储一个子查询的结果，组合UNION语句的结果，等等，它使用一个内部临时表。MySQL 8 采用了新的TempTable存储引擎，当在内存中保存表时，它大大优于以前版本中使用的MEMORY引擎，因为它支持可变宽度列(从 8.0.13 版本开始支持 blob 和 text 列)。此外，TempTable引擎支持使用 mmap 溢出到磁盘，因此如果表不适合内存，可以避免存储引擎转换。

对于 MySQL 8 中的内部临时表，主要有两个设置需要考虑:TempTable引擎允许使用多少内存，以及如果需要溢出到磁盘时会发生什么。

您可以使用temptable_max_ram选项配置内部临时表使用的最大内存量。这是一个全局设置，默认为 1 GiB。这些内存由所有需要内部临时表的查询共享，因此很容易限制总的内存使用量。temptable_max_ram选项可以动态设置。

如果内存不足，有必要开始在磁盘上存储临时表。如何完成由 8.0.16 版本中引入的temptable_use_mmap选项控制。默认值是ON，这意味着TempTable引擎为磁盘上的数据分配空间，作为内存映射的临时文件。这也是 8.0.16 之前使用的方法。如果该值设置为OFF，则使用 InnoDB 磁盘上的内部临时表。除非内存映射文件出现问题，否则建议使用默认设置。

您可以使用memory/temptable/physical_ram和memory/temptable/physical_disk性能模式事件来监控TempTable的内存使用情况。物理 RAM 事件显示了TempTable引擎内存部分的内存使用情况，而物理磁盘事件显示了temptable_use_mmap = ON时的内存映射部分。清单 23-5 展示了查询两个内存事件的内存使用情况的三个例子。

mysql> SELECT *
         FROM sys.memory_global_by_current_bytes
        WHERE event_name
                IN ('memory/temptable/physical_ram',
                    'memory/temptable/physical_disk')\G
*************************** 1\. row ***************************
       event_name: memory/temptable/physical_ram
    current_count: 14
    current_alloc: 71.00 MiB
current_avg_alloc: 5.07 MiB
       high_count: 15
       high_alloc: 135.00 MiB
   high_avg_alloc: 9.00 MiB
*************************** 2\. row ***************************
       event_name: memory/temptable/physical_disk
    current_count: 1
    current_alloc: 64.00 MiB
current_avg_alloc: 64.00 MiB
       high_count: 1
       high_alloc: 64.00 MiB
   high_avg_alloc: 64.00 MiB
2 rows in set (0.0012 sec)

mysql> SELECT *
         FROM performance_schema.memory_summary_global_by_event_name
        WHERE EVENT_NAME
                IN ('memory/temptable/physical_ram',
                    'memory/temptable/physical_disk')\G
*************************** 1\. row ***************************
                  EVENT_NAME: memory/temptable/physical_disk
                 COUNT_ALLOC: 2

                  COUNT_FREE: 1
   SUM_NUMBER_OF_BYTES_ALLOC: 134217728
    SUM_NUMBER_OF_BYTES_FREE: 67108864
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 1
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 67108864
   HIGH_NUMBER_OF_BYTES_USED: 67108864
*************************** 2\. row ***************************
                  EVENT_NAME: memory/temptable/physical_ram
                 COUNT_ALLOC: 27
                  COUNT_FREE: 13
   SUM_NUMBER_OF_BYTES_ALLOC: 273678336
    SUM_NUMBER_OF_BYTES_FREE: 199229440
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 14
             HIGH_COUNT_USED: 15
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 74448896
   HIGH_NUMBER_OF_BYTES_USED: 141557760
2 rows in set (0.0004 sec)

mysql> SELECT *
         FROM performance_schema.memory_summary_by_thread_by_event_name
        WHERE EVENT_NAME
                IN ('memory/temptable/physical_ram',
                    'memory/temptable/physical_disk')
          AND COUNT_ALLOC > 0\G
*************************** 1\. row ***************************
                   THREAD_ID: 29
                  EVENT_NAME: memory/temptable/physical_disk
                 COUNT_ALLOC: 2
                  COUNT_FREE: 1
   SUM_NUMBER_OF_BYTES_ALLOC: 134217728
    SUM_NUMBER_OF_BYTES_FREE: 67108864
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 1
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 67108864
   HIGH_NUMBER_OF_BYTES_USED: 67108864
1 row in set (0.0098 sec)

Listing 23-5Querying the TempTable memory usage

前两个查询请求全局使用，而第三个查询请求每个线程的使用。第一个查询使用了sys.memory_global_by_current_bytes视图，该视图返回当时具有大于 0 的current_alloc的事件。这表明TempTable引擎正在使用中，一部分数据已经使用内存映射文件溢出到磁盘。第二个查询使用性能模式，即使当前没有为其分配内存，也将始终返回这两个事件的数据。第三个查询显示了哪些线程已经分配了TempTable内存。由于TempTable溢出的实现方式，使用性能模式不可能看到哪些线程在磁盘上有文件。

摘要

本章介绍了配置 MySQL 实例的一般注意事项以及最常需要调整的选项。当您考虑对配置进行更改时，最重要的是您要考虑为什么要进行更改，它应该解决什么问题，为什么它会解决这个问题，并且您要确认它是否有效。您可以通过一次对一个选项进行少量的增量更改来最好地确认这一点。

最有可能受益于非默认值的三个选项是用于设置 InnoDB 缓冲池大小的innodb_buffer_pool_size，以及用于设置重做日志大小的innodb_log_file_size和innodb_log_files_in_group选项。讨论的其他 InnoDB 选项控制缓冲池实例的数量、转储时包含多少缓冲池、旧块子列表、如何刷新页面以及重做日志缓冲区的大小。

在 MySQL8.0.14 和更高版本中，支持并行执行一些查询。您可以使用innodb_parallel_read_threads选项限制并行度，从 8.0.17 开始，该选项指定 InnoDB 将跨所有连接创建的最大并行线程总数。并行执行线程被视为后台线程，仅在并行执行查询时存在。

您的查询也可能受益于更大的每个查询缓冲区，但您必须小心，因为较大的值不一定比较小的值更好。建议对这些缓冲区使用缺省值，只有在测试证明有明显好处的情况下才增加它们。

最后，讨论了内部临时表。在 MySQL 8 中，它们使用TempTable引擎，当达到全局最大内存使用量时，该引擎支持溢出到磁盘。将内部临时表存储在磁盘上时，也可以将其转换为 InnoDB。

下一章将探讨如何改变查询以获得更好的性能。

Footnotes [1](#Fn1_source)

https://dev.mysql.com/doc/refman/en/innodb-parameters.html#sysvar_innodb_flush_method