MySQL8-查询性能调优教程-六-MySQL8 查询性能调优教程（六）十八、锁定理论和监控与上一章讨论的优化器一起

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

原文：MySQL 8 Query Performance Tuning

协议：CC BY-NC-SA 4.0

十八、锁定理论和监控

与上一章讨论的优化器一起，锁可能是查询优化中最复杂的主题。当锁显示出它们最糟糕的一面时，即使是最好的锁专家也会头发变白。然而，不要绝望。本章将向你介绍你需要的大多数锁的知识，甚至更多。在你读完这一章之后，你应该能够开始研究锁，并利用它获得更多的知识。

本章开始讨论为什么需要锁以及锁的访问级别。本章最大的部分介绍了 MySQL 中最常见的锁。本章的另一半讨论为什么锁请求可能失败，如何减少锁的影响，以及如何监控锁。

Note

大多数例子都包含了再现输出的重要部分的语句(某些数据在本质上会因情况而异)。因为锁定的有趣部分通常包括多个连接，所以查询提示被设置为在重要时指示哪个连接用于哪个查询。例如，Connection 1>意味着查询应该由您的第一个连接执行。

为什么需要锁？

这似乎是一个不需要锁定数据库的完美世界。但是价格会很高，只有少数用例可以使用该数据库，对于 MySQL 这样的通用数据库来说是不可能的。如果没有锁定，就不能有任何并发性。想象一下，只允许一个到数据库的连接(你可以说它本身是一个锁，因此系统不是无锁的)——这对大多数应用来说不是很有用。

Note

通常，MySQL 中所谓的锁实际上是一个锁请求，它可以处于授权或挂起状态。

当您有几个连接同时执行查询时，您需要某种方法来确保这些连接不会互相妨碍。这就是锁进入画面的地方。您可以将锁想象成道路交通中的交通信号，它控制资源的访问以避免事故。在道路交叉路口，要保证两车不交叉，不发生碰撞。在数据库中，有必要确保两个查询对数据的访问不冲突。

由于控制进入十字路口有不同的级别——让行、停车标志和交通灯——数据库中有不同的锁类型。

锁定访问级别

锁访问级别决定了给定锁允许哪种类型的访问。它有时也被称为锁类型，但是因为它可能与锁粒度混淆，所以这里使用术语锁访问级别。

本质上有两种访问级别:共享或独占。访问级别顾名思义。共享锁允许其他连接也获得共享锁。这是最宽松的锁访问级别。独占锁只允许一个连接获得锁。共享锁也称为读锁，排他锁也称为写锁。

MySQL 还有一个叫做意向锁的概念，它指定了事务的意向。意向锁可以是共享的，也可以是排他的。在下一节讨论 MySQL 中的主要锁粒度级别时，将更详细地讨论意向锁。

锁定粒度

MySQL 使用一系列不同的锁粒度(也称为锁类型)来控制对数据的访问。通过使用不同的锁粒度，在最大程度上允许对数据的并发访问。本节将介绍 MySQL 使用的主要粒度级别。

用户级锁

用户级锁是应用可以用来保护的显式锁类型，例如，工作流。它们不常使用，但是对于一些需要序列化访问的复杂任务来说，它们会很有用。所有用户锁都是排他锁，使用最长 64 个字符的名称获得。

您可以使用一组函数来操作用户级锁:

GET_LOCK(name, timeout) : 通过指定锁的名称获得锁。第二个参数是以秒为单位的超时；如果在这段时间内没有获得锁，该函数将返回 0。如果获得了锁，返回值为 1。如果超时为负，该函数将无限期等待锁变为可用。
IS_FREE_LOCK(name) : 检查命名锁是否可用。如果锁可用，函数返回 1，如果锁不可用，函数返回 0。
IS_USED_LOCK(name) : 这是IS_FREE_LOCK()功能的反义词。如果锁在使用中(不可用)，该函数返回持有锁的连接的连接 id，如果锁不在使用中(可用)，则返回NULL。
RELEASE_ALL_LOCKS() : 释放连接持有的所有用户级锁。返回值是释放的锁的数量。
RELEASE_LOCK(name) : 用提供的名字解锁。如果锁被释放，返回值为 1；如果锁存在但不属于连接，返回值为 0；如果锁不存在，返回值为NULL。

通过多次调用GET_LOCK()可以获得多个锁。如果这样做，请注意确保所有用户以相同的顺序获得锁，否则可能会发生死锁。如果发生死锁，将返回一个ER_USER_LOCK_DEADLOCK错误(错误代码 3058)。清单 18-1 中显示了一个例子。

-- Connection 1
Connection 1> SELECT GET_LOCK('my_lock_1', -1);
+---------------------------+
| GET_LOCK('my_lock_1', -1) |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.0100 sec)

-- Connection 2
Connection 2> SELECT GET_LOCK('my_lock_2', -1);
+---------------------------+
| GET_LOCK('my_lock_2', -1) |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.0006 sec)

Connection 2> SELECT GET_LOCK('my_lock_1', -1);

-- Connection 1

Connection 1> SELECT GET_LOCK('my_lock_2', -1);
ERROR: 3058: Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.

Listing 18-1A deadlock for user-level locks

当连接 2 试图获取my_lock_1锁时，该语句将被阻塞，直到连接 1 试图获取触发死锁的my_lock_2锁。如果您获得多个锁，您应该准备好处理死锁。请注意，对于用户级锁，死锁不会触发事务回滚。

被授予和挂起的用户级锁可以在performance_schema.metadata_locks表中找到，其中OBJECT_TYPE列设置为USER LEVEL LOCK，如清单 18-2 所示。列出的锁假设您离开了清单 18-1 中的死锁被触发时的系统。请注意，有些值如OBJECT_INSTANCE_BEGIN会因您而异。

mysql> SELECT *
         FROM performance_schema.metadata_locks
        WHERE OBJECT_TYPE = 'USER LEVEL LOCK'\G
*************************** 1\. row ***************************
          OBJECT_TYPE: USER LEVEL LOCK
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: my_lock_1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2600542870816
            LOCK_TYPE: EXCLUSIVE
        LOCK_DURATION: EXPLICIT
          LOCK_STATUS: GRANTED
               SOURCE: item_func.cc:4840
      OWNER_THREAD_ID: 76
       OWNER_EVENT_ID: 33
*************************** 2\. row ***************************
          OBJECT_TYPE: USER LEVEL LOCK
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: my_lock_2
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2600542868896
            LOCK_TYPE: EXCLUSIVE
        LOCK_DURATION: EXPLICIT
          LOCK_STATUS: GRANTED
               SOURCE: item_func.cc:4840
      OWNER_THREAD_ID: 62
       OWNER_EVENT_ID: 25
*************************** 3\. row ***************************
          OBJECT_TYPE: USER LEVEL LOCK
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: my_lock_1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2600542870336
            LOCK_TYPE: EXCLUSIVE
        LOCK_DURATION: EXPLICIT
          LOCK_STATUS: PENDING
               SOURCE: item_func.cc:4840
      OWNER_THREAD_ID: 62
       OWNER_EVENT_ID: 26

3 rows in set (0.0086 sec)

Listing 18-2Listing user-level locks

用户级锁的OBJECT_TYPE是USER LEVEL LOCK，锁的持续时间是EXPLICIT，因为这取决于用户或应用是否再次释放锁。在第 1 行中，具有性能模式线程 id 76 的连接已经被授予了my_lock_1锁，而在第 3 行中，线程 id 62 正在等待它被授予。线程 id 62 也具有包含在行 2 中的授权锁。

下一级锁涉及非数据表级锁。首先要讨论的是冲水锁。

清空锁

大多数参与备份的人都熟悉刷新锁。它是在使用FLUSH TABLES语句时获取的，并持续整个语句期间，除非您添加了WITH READ LOCK,在这种情况下，共享(读)锁将被持有，直到该锁被显式释放。在ANALYZE TABLE语句的结尾也会触发隐式的表刷新。刷新锁是一个表级锁。用FLUSH TABLES WITH READ LOCK获取的读锁将在后面的显式锁中讨论。

刷新锁的锁问题的一个常见原因是长时间运行的查询。只要存在打开表的查询，一个FLUSH TABLES语句就不能刷新表。这意味着，如果在一个长时间运行的查询使用一个或多个被刷新的表时执行一个FLUSH TABLES语句，那么FLUSH TABLES语句将阻塞所有其他需要这些表的语句，直到锁的情况得到解决。

嵌入式锁受lock_wait_timeout设置的影响。如果获得锁的时间超过lock_wait_timeout秒，MySQL 将放弃锁。如果FLUSH TABLES声明被扼杀，同样适用。然而，由于 MySQL 的内部原因，在长时间运行的查询完成之前，一个称为表定义缓存(TDC)版本锁的较低级别的锁不能总是被释放。 ¹ 这意味着确保锁问题得到解决的唯一方法是终止长时间运行的查询，但是要注意，如果查询已经更改了许多行，回滚查询可能需要很长时间。

当围绕刷新锁存在锁争用时，FLUSH TABLES语句和随后启动的查询都将状态设置为“等待表刷新”清单 18-3 展示了一个包含三个查询的例子。为了自己重现这个场景，开始执行三个查询，将提示设置为Connection N>，其中N为 1、2 或 3，代表三个不同的连接。针对sys.session的查询在第四个连接中完成。所有查询都必须在第一个查询完成之前执行(需要三分钟)。

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

-- Connection 2
Connection 2> FLUSH TABLES world.city;

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

-- Connection 4
Connection 4> SELECT thd_id, conn_id, state,
                     current_statement
                FROM sys.session
               WHERE current_statement IS NOT NULL
                     AND thd_id <> PS_CURRENT_THREAD_ID()\G
*************************** 1\. row ***************************
           thd_id: 61
          conn_id: 21
            state: User sleep
current_statement: SELECT *, SLEEP(180) FROM world.city WHERE ID = 130
*************************** 2\. row ***************************
           thd_id: 62
          conn_id: 22
            state: Waiting for table flush
current_statement: FLUSH TABLES world.city
*************************** 3\. row ***************************
           thd_id: 64
          conn_id: 23
            state: Waiting for table flush
current_statement: SELECT * FROM world.city WHERE ID = 201
3 rows in set (0.0598 sec)

Listing 18-3Example of waiting for a flush lock

该示例使用了sys.session视图；使用performance_schema.threads和SHOW PROCESSLIST可以获得类似的结果。为了减少输出以仅包括与刷新锁讨论相关的查询，当前线程和没有正在进行的查询的线程被过滤掉。

与conn_id = 21的连接正在执行一个使用world.city表的慢速查询(使用了一个SLEEP(180)来确保它花费很长时间)。同时，conn_id = 22为world.city表执行了一条FLUSH TABLES语句。因为第一个查询仍然打开着表(一旦查询完成，它就会被释放)，所以FLUSH TABLES语句最终会等待表刷新锁。最后，conn_id = 23试图查询表，因此必须等待FLUSH TABLES语句。

另一种非数据表锁是元数据锁。

元数据锁

元数据锁是 MySQL 中较新的锁类型之一。它们是在 MySQL 5.5 中引入的，它们的目的是保护模式，因此当查询或事务依赖于模式不变时，它不会被改变。元数据锁在表级别工作，但是它们应该被视为独立于表锁的锁类型，因为它们不保护表中的数据。

语句和 DML 查询使用共享元数据锁，而 DDL 语句使用排他锁。当第一次使用表时，连接获取表上的元数据锁，并保持该锁直到事务结束。当持有元数据锁时，不允许其他连接更改表的模式定义。但是，执行SELECT语句和 DML 语句的其他连接不受限制。通常，关于元数据锁的最大问题是阻止 DDL 语句开始工作的空闲事务。

如果遇到关于元数据锁定的冲突，您会看到进程列表中的查询状态设置为“等待表元数据锁定”清单 18-4 中显示了一个包括要设置的查询的例子。

-- Connection 1
Connection 1> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              21 |
+-----------------+
1 row in set (0.0003 sec)

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

Connection 1> SELECT * FROM world.city WHERE ID = 130\G
*************************** 1\. row ***************************
         ID: 130
       Name: Sydney
CountryCode: AUS
   District: New South Wales
 Population: 3276207
1 row in set (0.0005 sec)

-- Connection 2
Connection 2> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              22 |
+-----------------+
1 row in set (0.0003 sec)

Connection 2> OPTIMIZE TABLE world.city;

-- Connection 3
Connection 3> SELECT thd_id, conn_id, state,
                     current_statement,
                     last_statement
                FROM sys.session
               WHERE conn_id IN (21, 22)\G
*************************** 1\. row ***************************
           thd_id: 61
          conn_id: 21
            state: NULL

current_statement: SELECT * FROM world.city WHERE ID = 130
   last_statement: SELECT * FROM world.city WHERE ID = 130
*************************** 2\. row ***************************
           thd_id: 62

          conn_id: 22
            state: Waiting for table metadata lock
current_statement: OPTIMIZE TABLE world.city
   last_statement: NULL
2 rows in set (0.0549 sec)

Listing 18-4Example of waiting for table metadata lock

在本例中，与conn_id = 21的连接有一个正在进行的事务，并且在前一条语句中查询了world.city表(本例中的当前语句与下一条语句执行之前不会被清除的语句相同)。当事务仍然活跃时，conn_id = 22已经执行了一个OPTIMIZE TABLE语句，该语句现在正在等待元数据锁定。(是的，OPTIMIZE TABLE并没有改变模式定义，但是它作为 DDL 语句仍然受到元数据锁定的影响。)

当导致元数据锁定的是当前或最后一条语句时，这是很方便的。在更一般的情况下，您可以使用将OBJECT_TYPE列设置为TABLE的performance_schema.metadata_locks表来查找授予的和挂起的元数据锁。清单 18-5 显示了一个使用与前一个例子相同的设置的被授予和挂起的元数据锁的例子。第 22 章详细介绍了元数据锁的研究。

-- Connection 3
Connection 3> SELECT *
                FROM performance_schema.metadata_locks
               WHERE OBJECT_SCHEMA = 'world'
                     AND OBJECT_NAME = 'city'\G
*************************** 1\. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: world
          OBJECT_NAME: city
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2195760373456
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6014
      OWNER_THREAD_ID: 61
       OWNER_EVENT_ID: 53
*************************** 2\. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: world
          OBJECT_NAME: city
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2194784109632
            LOCK_TYPE: SHARED_NO_READ_WRITE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: PENDING
               SOURCE: sql_parse.cc:6014
      OWNER_THREAD_ID: 62
       OWNER_EVENT_ID: 26
2 rows in set (0.0007 sec)

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

Listing 18-5Example of metadata locks

在这个例子中，由于一个正在进行的事务，线程 id 61(与来自sys.session输出的conn_id = 22相同)拥有一个对world.city表的共享读锁，而线程 id 62 正在等待一个锁，因为它试图在这个表上执行一个 DDL 语句。

元数据锁的一个特例是用LOCK TABLES语句显式获取的锁。

显式表锁

使用LOCK TABLES和FLUSH TABLES WITH READ LOCK语句获取显式表锁。使用LOCK TABLES语句，可以获取共享锁或独占锁；FLUSH TABLES WITH READ LOCK总是使用共享锁。这些表被锁定，直到用UNLOCK TABLES语句显式释放它们。当FLUSH TABLES WITH READ LOCK在没有列出任何表的情况下被执行时，全局读锁(即，影响所有表)被获取。虽然这些锁也保护数据，但在 MySQL 中它们被视为元数据锁。

除了与备份相关的带读锁的刷新表之外，显式表锁并不经常与 InnoDB 一起使用，因为 InnoDB 复杂的锁特性在大多数情况下都优于自己处理锁。但是，如果您真的需要锁定整个表，显式锁会很有用，因为 MySQL 检查它们非常便宜。

在world.country和world.countrylanguage表上获取显式读锁并在world.city表上获取写锁的连接示例如下

mysql> LOCK TABLES world.country READ,
                   world.countrylanguage READ,
                   world.city WRITE;
Query OK, 0 rows affected (0.0500 sec)

当您使用显式锁时，只允许您根据请求的锁来使用您已经锁定的表。这意味着，如果您获取一个读锁并试图写入表(ER_TABLE_NOT_LOCKED_FOR_WRITE)，或者如果您试图使用一个没有获取锁(ER_TABLE_NOT_LOCKED)的表，您将会得到一个错误，例如:

mysql> UPDATE world.country
          SET Population = Population + 1
        WHERE Code = 'AUS';
ERROR: 1099: Table 'country' was locked with a READ lock and can't be updated

mysql> SELECT *
         FROM sakila.film
        WHERE film_id = 1;
ERROR: 1100: Table 'film' was not locked with LOCK TABLES

因为显式锁被认为是元数据锁，所以performance_schema.metadata_locks表中的症状和信息与隐式元数据锁相同。

另一种隐式处理的表级锁被称为表锁。

隐式表锁

当查询一个表时，MySQL 采用隐式表锁。除了刷新、元数据和显式锁之外，表锁对 InnoDB 表没有太大的作用，因为 InnoDB 使用记录锁来允许对表的并发访问，只要事务不修改相同的行(粗略地说——如下一小节所示——还有更多内容)。

然而，InnoDB 确实在表级别使用了意向锁的概念。由于您在研究锁问题时可能会遇到这些问题，因此有必要熟悉一下它们。正如在锁访问级别的讨论中提到的，意图锁标记了事务的意图。如果您使用一个显式的LOCK TABLES语句，该表将被您所请求的访问级别直接锁定。

对于由事务获取的锁，首先获取一个意向锁，然后如果需要的话可以升级它。为了获得共享锁，事务首先获取意向共享锁，然后获取共享锁。类似地，对于排他锁，首先采用意图排他锁。意向锁定的一些示例如下:

一个SELECT ... FOR SHARE语句在被查询的表上获取一个意向共享锁。SELECT ... LOCK IN SHARE MODE语法是同义词。
一个SELECT ... FOR UPDATE语句在被查询的表上获取一个意向排他锁。
一个 DML 语句(不包括SELECT)在修改后的表上获取一个意向排他锁。如果修改了外键列，就会在父表上获得一个意向共享锁。

两个意向锁总是互相兼容的。这意味着即使一个事务有一个意向排他锁，它也不会阻止另一个事务获取一个意向锁。但是，它将阻止另一个事务将其意向锁升级为完全锁。表 18-1 显示了锁类型之间的兼容性。共享锁表示为 S，排他锁表示为 x。意向锁以 I 为前缀，因此 IS 是意向共享锁，IX 是意向排他锁。

表 18-1

InnoDB 锁兼容性

| |

独占(X)

意图排他(九)

共享的

共享意向(IS)

| | --- | --- | --- | --- | --- | | 独占(X) | -什么 | -什么 | -什么 | -什么 | | 意图排他(IX) | -什么 | ✔ | -什么 | ✔ | | 共享 | -什么 | -什么 | ✔ | ✔ | | 意向共享(是) | -什么 | ✔ | ✔ | ✔ |

在该表中，复选标记表示这两种锁兼容，而叉号表示这两种锁相互冲突。唯一的意向冲突锁是独占锁和共享锁。排他锁与所有其他锁冲突，包括两种意向锁类型。共享锁只与排他锁和意图排他锁冲突。

为什么意向锁甚至是必要的？它们允许 InnoDB 在不阻塞兼容操作的情况下按顺序解决锁定请求。细节超出了本次讨论的范围。重要的是你知道意向锁的存在，所以当你看到它们时，你知道它们来自哪里。

可以在LOCK_TYPE列设置为TABLE的performance_schema.data_locks表中找到表级锁。清单 18-6 展示了一个意向共享锁的例子。

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

Connection 1> SELECT *
                FROM world.city
               WHERE ID = 130
                 FOR SHARE;
Query OK, 1 row affected (0.0010 sec)

-- Connection 2
Connection 2> SELECT *
                FROM performance_schema.data_locks
               WHERE LOCK_TYPE = 'TABLE'\G
*************************** 1\. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2195098223824:1720:2195068346872
ENGINE_TRANSACTION_ID: 283670074934480
            THREAD_ID: 61
             EVENT_ID: 81
        OBJECT_SCHEMA: world
          OBJECT_NAME: city
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2195068346872
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
1 row in set (0.0354 sec)

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

Listing 18-6Example of an InnoDB intention shared lock

这显示了一个在world.city表上的意向共享锁。注意，ENGINE被设置为INNODB，而LOCK_DATA为NULL。如果执行相同的查询，ENGINE_LOCK_ID、ENGINE_TRANSACTION_ID和OBJECT_INSTANCE_BEGIN列的值将会不同。

如前所述，InnoDB 的主要访问级别保护是在记录级别，所以让我们看看那些。

记录锁

记录锁通常被称为行锁；但是，它不仅仅是行上的锁，因为它还包括索引和间隙锁。当谈到 InnoDB 锁时，这些通常是指的锁。它们是细粒度的锁，旨在锁定最少量的数据，同时仍然确保数据的完整性。

记录锁可以是共享的，也可以是排他的，并且只影响事务访问的行和索引。排他锁的持续时间通常是有例外的事务，例如，被删除标记的记录用于在INSERT INTO ... ON DUPLICATE KEY和REPLACE语句中进行唯一性检查。对于共享锁，持续时间可能取决于事务隔离级别，如“减少锁定问题”一节中的“事务隔离级别”所述

可以使用performance_schema.data_locks表找到记录锁，该表也用于在表级别找到意图锁。清单 18-7 展示了一个使用二级索引CountryCode更新world.city表中的行的锁的例子。

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

Connection 1> UPDATE world.city
                 SET Population = Population + 1
               WHERE CountryCode = 'LUX';
Query OK, 1 row affected (0.0009 sec)

Rows matched: 1  Changed: 1  Warnings: 0

-- Connection 2
Connection 2> SELECT *
                FROM performance_schema.data_locks\G
*************************** 1\. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2195098223824:1720:2195068346872
ENGINE_TRANSACTION_ID: 117114
            THREAD_ID: 61
             EVENT_ID: 121
        OBJECT_SCHEMA: world

          OBJECT_NAME: city
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2195068346872
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2\. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2195098223824:507:30:1112:2195068344088
ENGINE_TRANSACTION_ID: 117114
            THREAD_ID: 61
             EVENT_ID: 121
        OBJECT_SCHEMA: world
          OBJECT_NAME: city
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: CountryCode
OBJECT_INSTANCE_BEGIN: 2195068344088
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'LUX', 2452
*************************** 3\. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2195098223824:507:20:113:2195068344432
ENGINE_TRANSACTION_ID: 117114
            THREAD_ID: 61
             EVENT_ID: 121
        OBJECT_SCHEMA: world
          OBJECT_NAME: city
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2195068344432
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2452
*************************** 4\. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2195098223824:507:30:1113:2195068344776
ENGINE_TRANSACTION_ID: 117114
            THREAD_ID: 61
             EVENT_ID: 121
        OBJECT_SCHEMA: world
          OBJECT_NAME: city
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: CountryCode
OBJECT_INSTANCE_BEGIN: 2195068344776
            LOCK_TYPE: RECORD

            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'LVA', 2434
4 rows in set (0.0005 sec)

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

Listing 18-7Example of InnoDB record locks

第一行是已经讨论过的意图排他表锁。第二行是值(' LUX '，2452)在CountryCode索引上的 next-key 锁(更简短)，其中' LUX '是在WHERE子句中使用的国家代码，2452 是添加到非唯一二级索引的主键 id。带有ID = 2452的城市是唯一匹配WHERE子句的城市，主键记录(行本身)显示在输出的第三行。锁定模式是X,REC_NOT_GAP，这意味着它是记录上的排他锁，而不是间隙上的排他锁。

什么是差距？输出的第四行显示了一个示例。间隙锁如此重要，以至于关于间隙锁的讨论被分成单独的部分。

间隙锁、下一键锁和谓词锁

间隙锁保护两条记录之间的空间。这可以在聚集索引的行中，也可以在辅助索引中。在索引页中的第一条记录之前和最后一条记录之后，分别有称为下确界记录和上确界记录的伪记录。间隙锁通常是最容易引起混淆的锁类型。研究锁问题的经验是熟悉它们的最好方法。

考虑前面示例中的查询:

UPDATE world.city
   SET Population = Population + 1
 WHERE CountryCode = 'LUX';

该查询更改所有带有CountryCode = 'LUX'的城市的人口。如果在事务的更新和提交之间插入一个新的城市，会发生什么情况？如果UPDATE和INSERT语句提交的顺序与它们执行的顺序相同，一切都没问题。但是，如果以相反的顺序提交更改，结果将会不一致，因为预计插入的行也将被更新。

这就是间隙锁发挥作用的地方。它保护插入新记录(包括从不同位置移动的记录)的空间，因此在持有间隙锁的事务完成之前，它不会被更改。如果您查看清单 18-7 中示例的输出中第四行的最后几列，您可以看到一个间隙锁的示例:

           INDEX_NAME: CountryCode
OBJECT_INSTANCE_BEGIN: 2195068344776
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'LVA', 2434

这是值(' LVA '，2434)的CountryCode索引上的独占间隙锁。由于该查询请求更新所有将CountryCode设置为“LUX”的行，间隙锁确保没有为“LUX”国家代码插入新行。国家代码“LVA”是CountryCode索引中的下一个值，因此“勒克司”和“LVA”之间的差距受到独占锁的保护。另一方面，用CountryCode = 'LVA'插入新城市还是有可能的。在某些地方，这被称为“记录前间隙”,这样更容易理解间隙锁是如何工作的。

当您使用READ COMMITTED事务隔离级别而不是REPEATABLE READ或SERIALIZABLE时，间隙锁被采用的程度要小得多。这将在“减少锁定问题”一节中的“事务隔离级别”中进一步讨论

与间隙锁相关的是下一键锁和谓词锁。下一键锁是记录锁和记录前间隙上的间隙锁的组合。这实际上是 InnoDB 中的默认锁类型，因此在锁输出中您只会看到它是S和X。在本小节和上一小节讨论的示例中，值(' LUX '，2452)的CountryCode索引上的锁及其之前的间隙是下一个键锁的示例。来自performance_schema.data_locks表的清单 18-7 中输出的相关部分是

*************************** 2\. row ***************************
           INDEX_NAME: CountryCode
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'LUX', 2452
*************************** 3\. row ***************************
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2452
*************************** 4\. row ***************************
           INDEX_NAME: CountryCode
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'LVA', 2434

概括一下，第 2 行是下一个键锁，第 3 行是主键(行)上的记录锁，第 4 行是“LUX”和“LVA”之间的间隙锁(或者是 LVA 之前的间隙锁)。

谓词锁类似于间隙锁，但它适用于无法进行绝对排序的空间索引，因此间隙锁没有意义。对于REPEATABLE READ和SERIALIZABLE事务隔离级别中的空间索引，InnoDB 在用于查询的最小边界矩形(MBR)上创建了一个谓词锁，而不是间隙锁。这将通过防止对最小边界矩形内的数据进行更改来实现一致的读取。

您应该知道的与记录相关的最后一种锁类型是插入意图锁。

插入意向锁

请记住，对于表锁，InnoDB 有意向锁，决定事务是以共享还是独占的方式使用表。类似地，InnoDB 在记录级别有插入意图锁。InnoDB 使用这些锁——顾名思义——和INSERT语句向其他事务发出信号。因此，锁是在一个尚未创建的记录上(因此它是一个间隙锁)，而不是在一个现有的记录上。使用插入意图锁有助于提高执行插入的并发性。

您不太可能在锁输出中看到插入意图锁，除非一个INSERT语句正在等待一个锁被授予。您可以通过在另一个事务中创建一个间隙锁来阻止INSERT语句完成，从而强制出现这种情况。清单 18-8 中的例子在连接 1 中创建了一个间隙锁，然后在连接 2 中试图插入一个与间隙锁冲突的行。最后，在第三个连接中，检索锁信息。

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

Connection 1> SELECT *
                FROM world.city
               WHERE ID > 4079
                 FOR UPDATE;
Empty set (0.0009 sec)

-- Connection 2
Connection 2> SELECT PS_CURRENT_THREAD_ID();
+------------------------+
| PS_CURRENT_THREAD_ID() |
+------------------------+
|                     62 |
+------------------------+
1 row in set (0.0003 sec)

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

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

-- Connection 3
Connection 3> SELECT *
                FROM performance_schema.data_locks
               WHERE THREAD_ID = 62\G
*************************** 1\. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2195098220336:1720:2195068326968
ENGINE_TRANSACTION_ID: 117144
            THREAD_ID: 62
             EVENT_ID: 119
        OBJECT_SCHEMA: world

          OBJECT_NAME: city
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2195068326968
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2\. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2195098220336:507:29:1:2195068320072
ENGINE_TRANSACTION_ID: 117144
            THREAD_ID: 62
             EVENT_ID: 119
        OBJECT_SCHEMA: world
          OBJECT_NAME: city
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2195068320072
            LOCK_TYPE: RECORD
            LOCK_MODE: X,INSERT_INTENTION
          LOCK_STATUS: WAITING
            LOCK_DATA: supremum pseudo-record
2 rows in set (0.0005 sec)

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

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

Listing 18-8Example of an insert intention lock

连接 2 的性能模式线程 id 为 62，因此在连接 3 中，可以只查询该线程并排除连接 1 获取的锁。注意对于RECORD锁，锁模式包括INSERT_INTENTION——插入意图锁。在这种情况下，锁定的数据是上确界伪记录，但根据具体情况，它也可以是主键的值。如果您还记得下一个键锁的讨论，那么X意味着下一个键锁，但是这是一个特例，因为锁是在上确界伪记录上，并且不可能锁定它，所以实际上它只是上确界伪记录之前的间隙上的间隙锁。

插入数据时需要注意的另一个锁是自动增量锁。

自动增量锁

当您将数据插入到具有自动递增计数器的表中时，有必要保护计数器，以便保证两个事务获得唯一的值。如果对二进制日志使用基于语句的日志记录，则会有进一步的限制，因为在重播语句时，将为除第一行之外的所有行重新创建自动增量值。

InnoDB 支持三种锁定模式，因此您可以根据需要调整锁定量。使用innodb_autoinc_lock_mode选项选择锁定模式，该选项取值为 0、1 和 2，MySQL 8 中的默认值为 2。它需要重新启动 MySQL 来改变这个值。表 18-2 中总结了这些值的含义。

表 18-2

innodb_autoinc_lock_mode 选项的支持值

价值

方式

描述

| | --- | --- | --- | | Zero | 传统的 | MySQL 5.0 及更早版本的锁定行为。锁一直保持到语句结束，所以值是以可重复的连续顺序赋值的。 | | one | 连续的 | 对于查询开始时行数已知的INSERT语句，所需数量的自动增量值被分配在一个轻量级互斥体下，并且避免了自动增量锁。对于行数未知的语句，自动增量锁被获取并保持到语句结束。这是 MySQL 5.7 和更早版本的默认设置。 | | Two | 插入纸 | 自动增量锁永远不会被占用，并发插入的自动增量值可能是交错的。只有当二进制记录被禁用或binlog_format被设置为ROW时，该模式才是安全的。它是 MySQL 8 中的默认值。 |

innodb_autoinc_lock_mode值越高，锁定越少。为此付出的代价是增加自动增量值序列中的间隙数量，以及innodb_autoinc_lock_mode = 2交错值的可能性。除非不能使用基于行的二进制日志记录，或者对连续的自动增量值有特殊需求，否则建议使用值 2。

对用户级锁、元数据锁和数据级锁的讨论到此结束。您应该知道一些与备份相关的其他锁。

备用锁

备份锁是实例级锁；也就是说，它影响整个系统。它是 MySQL 8 中引入的新锁。备份锁防止可能导致备份不一致的语句，同时仍然允许其他语句与备份同时执行。被阻止的语句包括

创建、重命名或删除文件的语句。这包括CREATE TABLE、CREATE TABLESPACE、RENAME TABLE和DROP TABLE语句。
CREATE USER、ALTER USER、DROP USER、GRANT等账户管理报表。
不将其更改记录到重做日志中的 DDL 语句。例如，这包括添加索引。

用LOCK INSTANCE FOR BACKUP语句创建备份锁，用UNLOCK INSTANCE语句释放锁。执行LOCK INSTANCE FOR BACKUP需要BACKUP_ADMIN权限。获取备份锁并再次释放它的一个示例是

mysql> LOCK INSTANCE FOR BACKUP;
Query OK, 0 rows affected (0.00 sec)

mysql> UNLOCK INSTANCE;
Query OK, 0 rows affected (0.00 sec)

Note

在编写时，使用 X 协议(通过用mysqlx_port指定的端口或用mysqlx_socket指定的套接字连接)时，不允许获取备份锁并释放它。尝试这样做将返回一个ER_PLUGGABLE_PROTOCOL_COMMAND_NOT_SUPPORTED错误:ERROR: 3130: Command not supported by pluggable protocols。

此外，与备份锁冲突的语句也会使用备份锁。由于 DDL 语句有时由几个步骤组成，例如，在新文件中重建一个表并重命名文件，备份锁可以在这些步骤之间释放，以避免阻塞LOCK INSTANCE FOR BACKUP超过必要的时间。

备份锁可以在performance_schema.metadata_locks表中找到，其中OBJECT_TYPE列设置为BACKUP LOCK。清单 18-9 显示了一个查询等待LOCK INSTANCE FOR BACKUP持有的备份锁的例子。

-- Connection 1
Connection 1> LOCK INSTANCE FOR BACKUP;
Query OK, 0 rows affected (0.00 sec)

-- Connection 2
Connection 2> OPTIMIZE TABLE world.city;

-- Connection 3
Connection 3> SELECT *
                FROM performance_schema.metadata_locks
               WHERE OBJECT_TYPE = 'BACKUP LOCK'\G
*************************** 1\. row ***************************
          OBJECT_TYPE: BACKUP LOCK
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2520402231312
            LOCK_TYPE: SHARED
        LOCK_DURATION: EXPLICIT
          LOCK_STATUS: GRANTED
               SOURCE: sql_backup_lock.cc:101
      OWNER_THREAD_ID: 49
       OWNER_EVENT_ID: 8
*************************** 2\. row ***************************
          OBJECT_TYPE: BACKUP LOCK
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2520403183328
            LOCK_TYPE: INTENTION_EXCLUSIVE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: PENDING
               SOURCE: sql_base.cc:5400
      OWNER_THREAD_ID: 60
       OWNER_EVENT_ID: 19
2 rows in set (0.0007 sec)

-- Connection 1
Connection 1> UNLOCK INSTANCE;
Query OK, 0 rows affected (0.00 sec)

Listing 18-9Example of a conflict for the backup lock

在本例中，线程 id 为 49 的连接拥有备份锁，而线程 id 为 60 的连接正在等待它。注意LOCK INSTANCE FOR BACKUP持有一个共享锁，而 DDL 语句请求一个意向排他锁。

与备份锁相关的是日志锁，它的引入也是为了减少备份过程中的锁定。

日志锁

创建备份时，您通常希望包括与备份一致的日志位置的相关信息。在 MySQL 5.7 和更早的版本中，在获取这些信息时需要全局读锁。在 MySQL 8 中，引入了日志锁，允许您在不使用全局读锁的情况下读取 InnoDB 的信息，如执行的全局事务标识符(GTIDs)、二进制日志位置和日志序列号(LSN)。

日志锁防止对日志相关信息进行更改的操作。实际上，这意味着提交、FLUSH LOGS等等。日志锁是通过查询performance_schema.log_status表隐式获取的。它需要BACKUP_ADMIN特权来访问表。清单 18-10 显示了log_status表的输出示例。

mysql> SELECT *
         FROM performance_schema.log_status\G
*************************** 1\. row ***************************
    SERVER_UUID: 59e3f95b-e0d6-11e8-94e8-ace2d35785be
          LOCAL: {"gtid_executed": "59e3f95b-e0d6-11e8-94e8-ace2d35785be:1-5343", "binary_log_file": "mysql-bin.000033", "binary_log_position": 3874615}
    REPLICATION: {"channels": []}
STORAGE_ENGINES: {"InnoDB": {"LSN": 7888992157, "LSN_checkpoint": 7888992157}}
1 row in set (0.0004 sec)

Listing 18-10Example output of the log_status table

对 MySQL 中主要锁类型的回顾到此结束。当一个查询请求一个锁，但是不能被授予时会发生什么？让我们考虑一下。

无法获得锁

锁的整体思想是限制对对象或记录的访问，以避免并发执行的冲突操作。这意味着有时锁不能被授予。那种情况下会发生什么？这取决于请求的锁和环境。元数据锁(包括显式请求的表锁)操作超时。InnoDB 记录锁支持超时和显式死锁检测。

Note

确定两个锁是否相互兼容非常复杂。这变得特别有趣，因为这种关系是不对称的，也就是说，一个锁可以在另一个锁存在时被允许，但反之则不行。例如，插入意图锁必须等待间隙锁，但是间隙锁不必等待插入意图锁。另一个例子(缺乏传递性)是间隙加记录锁必须等待仅记录锁，插入意图锁必须等待间隙加记录锁，但是插入意图锁不需要等待仅记录锁。

使用数据库时，无法获得锁是不可避免的，理解这一点很重要。原则上，您可以使用非常粗粒度的锁并避免失败的锁，除非超时——这就是 MyISAM 存储引擎在写入并发性非常差的情况下所做的事情。然而，在实践中，考虑到写工作负载的高并发性，细粒度锁是首选，这也引入了死锁的可能性。

结论是，您应该始终让您的应用准备好重试获取锁或优雅地失败。无论是显式锁还是隐式锁，这都适用。

Tip

总是准备好处理失败以获得锁。无法获得锁并不是一个灾难性的错误，通常不应该被认为是一个 bug。也就是说，正如“减少锁定问题”一节中所讨论的，在开发应用时，有一些减少锁争用的技术值得考虑。

本章的其余部分将讨论表级超时、记录级超时和 InnoDB 死锁的细节。

元数据和备份锁等待超时

当您请求刷新、元数据或备份锁时，获取锁的尝试将在lock_wait_timeout秒后超时。默认超时是 31536000 秒(365 天)。您可以在全局和会话范围内动态设置lock_wait_timeout选项，这允许您根据给定流程的特定需求调整超时。

当超时发生时，语句失败，出现错误ER_LOCK_WAIT_TIMEOUT(错误号 1205)。例如:

mysql> LOCK TABLES world.city WRITE;
ERROR: 1205: Lock wait timeout exceeded; try restarting transaction

lock_wait_timeout选项的推荐设置取决于应用的要求。使用较小的值来防止锁请求长时间阻塞其他查询可能是一个优势。这通常需要您实现对锁请求失败的处理，例如，通过重试该语句。另一方面，较大的值有助于避免重试该语句。对于FLUSH TABLES语句，还要记住它与低级表定义缓存(TDC)版本锁交互，这可能意味着放弃该语句不允许后续查询继续进行。在这种情况下，最好为lock_wait_timeout设置一个较高的值，以便更清楚地了解锁的关系。

InnoDB 锁等待超时

当查询请求 InnoDB 中的记录级锁时，它会超时，类似于刷新、元数据和备份锁的超时。由于记录级锁争用比表级锁争用更常见，并且记录级锁增加了死锁的可能性，因此超时默认为 50 秒。它可以使用innodb_lock_wait_timeout选项进行设置，该选项可以针对全局和会话范围进行设置。

当超时发生时，查询失败，并出现ER_LOCK_WAIT_TIMEOUT错误(错误号 1205 ),就像表级锁超时一样。清单 18-11 展示了一个发生 InnoDB 锁等待超时的例子。

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

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

Rows matched: 1  Changed: 1  Warnings: 0

-- Connection 2
Connection 2> SET SESSION innodb_lock_wait_timeout = 3;
Query OK, 0 rows affected (0.0004 sec)

Connection 2> UPDATE world.city
                 SET Population = Population + 1
               WHERE ID = 130;
ERROR: 1205: Lock wait timeout exceeded; try restarting transaction

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

Listing 18-11Example of an InnoDB lock wait timeout

在本例中，连接 2 的锁等待超时设置为 3 秒，因此没有必要等待通常的 50 秒超时。

当超时发生时，innodb_rollback_on_timeout选项定义了事务完成的工作有多少被回滚。当innodb_rollback_on_timeout被禁用时(默认)，只有触发超时的语句被回滚。启用该选项后，整个事务将回滚。innodb_rollback_on_timeout选项只能在全局级别配置，并且需要重启才能更改值。

Caution

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

一般情况下，建议将 InnoDB 记录级锁的超时值保持在较低水平。通常，最好降低默认值 50 秒。允许查询等待锁的时间越长，其他锁请求受影响的可能性就越大，这也可能导致其他查询停止。这也使得死锁更有可能发生。如果您禁用死锁检测(接下来将讨论)，您应该为innodb_lock_wait_timeout使用一个非常小的值，比如一秒或两秒，因为您将使用超时来检测死锁。如果没有死锁检测，也建议启用innodb_rollback_on_timeout选项。

僵局

死锁听起来是一个非常可怕的概念，但是你不应该让这个名字吓住你。就像锁等待超时一样，死锁是高并发数据库世界中的现实。它真正的意思是锁请求之间存在循环关系。解决僵局的唯一方法是强制放弃其中一个请求。从这个意义上说，死锁与锁等待超时没有什么不同。事实上，您可以禁用死锁检测，在这种情况下，其中一个锁将以锁等待超时结束。

那么，如果不是真正需要的话，为什么会有死锁呢？因为当锁请求之间存在循环关系时会出现死锁，所以 InnoDB 可以在循环完成后立即检测到死锁。这允许 InnoDB 立即告诉用户发生了死锁，而不必等待锁等待超时。告知发生了死锁也是有用的，因为这通常提供了改进应用中数据访问的机会。因此，您应该将死锁视为朋友，而不是敌人。图 18-1 显示了两个事务查询一个导致死锁的表的例子。

img/484666_1_En_18_Fig1_HTML.png

图 18-1

导致死锁的两个事务的示例

在本例中，事务 1 首先用ID = 130更新行，然后用ID = 3805更新行。在此期间，事务 2 首先用ID = 3805更新行，然后用ID = 130更新行。这意味着当事务 1 试图更新ID = 3805时，事务 2 已经锁定了该行。事务 2 也无法继续，因为它无法锁定ID = 130，因为事务 1 已经持有该锁。这是一个简单死锁的典型例子。图 18-2 中也显示了环锁关系。

img/484666_1_En_18_Fig2_HTML.png

图 18-2

导致死锁的锁的循环关系

在该图中，事务 1 和事务 2 持有哪个锁，请求哪个锁，以及如果没有干预，冲突如何永远无法解决，这一点很清楚。这使得它有资格成为一个僵局。

在现实世界中，死锁往往更加复杂。在这里讨论的例子中，只涉及到主键记录锁。一般来说，通常还包括二级钥匙、间隙锁和其他可能的锁类型。也可能涉及两个以上的事务。然而，原则是一样的。

Note

对于两个事务中的每一个，即使只有一个查询，也会发生死锁。如果一个查询按升序读取记录，而另一个按降序读取记录，则可能会出现死锁。

当死锁发生时，InnoDB 选择“工作最少”的事务成为受害者。您可以检查information_schema.INNODB_TRX视图中的trx_weight列，查看 InnoDB 使用的权重(完成的工作越多，权重越高)。实际上，这意味着持有最少锁的事务将被回滚。当这种情况发生时，事务中被选作牺牲品的查询失败，并返回错误ER_LOCK_DEADLOCK(错误代码 1213)，事务被回滚以释放尽可能多的锁。清单 18-12 中显示了一个发生死锁的例子。

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

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

Rows matched: 1  Changed: 1  Warnings: 0

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

Connection 2> UPDATE world.city
                 SET Population = Population + 1
               WHERE ID = 3805;
Query OK, 1 row affected (0.0006 sec)

Rows matched: 1  Changed: 1  Warnings: 0

Connection 2> UPDATE world.city
                 SET Population = Population + 1
               WHERE ID = 130;

-- Connection 1
Connection 1> UPDATE world.city
                 SET Population = Population + 1
               WHERE ID = 3805;
ERROR: 1213: Deadlock found when trying to get lock; try restarting transaction

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

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

Listing 18-12Example of a deadlock

在大多数情况下，自动死锁检测对于避免查询延迟过长时间是非常有用的。不过死锁检测不是免费的。对于具有非常高的查询并发性的 MySQL 实例，查找死锁的成本会变得很高，您最好禁用死锁检测，这是通过将innodb_deadlock_detect选项设置为OFF来完成的。也就是说，在 MySQL 8.0.18 和更高版本中，死锁检测被移到了一个专用的后台线程中，从而提高了性能。

如果您确实禁用了死锁检测，建议将innodb_lock_wait_timeout设置为一个非常低的值，比如一秒钟，以便快速检测锁争用。此外，启用innodb_rollback_on_timeout选项以确保锁被释放。

既然您已经了解了锁是如何工作的以及锁请求是如何失败的，那么您需要考虑如何减少锁的影响。

减少锁定问题

当您编写应用并为其数据和访问设计模式时，记住锁是很重要的。减少锁定的策略包括添加索引、更改事务隔离级别和抢先锁定。

Tip

不要被优化锁冲昏了头脑。如果只是偶尔遇到锁等待超时和死锁，通常最好重试查询或事务，而不是花时间来避免这个问题。多频繁取决于您的工作负载，但是对于许多应用来说，每小时重试几次不是问题。

事务规模和年龄

减少锁问题的一个重要策略是保持您的事务较小，并避免使事务打开的时间超过必要时间的延迟。锁问题最常见的原因是事务修改了大量的行，或者事务的活动时间超过了必要的时间。

事务的大小是事务所做的工作量，尤其是它占用的锁的数量，但是事务执行所花费的时间也很重要。正如本讨论中的一些其他主题将会提到的，您可以通过索引和事务隔离级别来部分地降低影响。然而，记住总体结果也很重要。如果您需要修改许多行，问问自己是否可以将工作分成更小的批，或者要求所有工作都在同一个事务中完成。也可以将一些准备工作分离出来，在主事务之外完成。

事务的持续时间也很重要。一个常见的问题是使用autocommit = 0的连接。每次在没有活动事务的情况下执行一个查询(包括SELECT)时，都会启动一个新的事务，直到执行一个显式的COMMIT或ROLLBACK(或者连接关闭)，事务才会完成。一些连接器默认禁用自动提交，因此您可能在没有意识到的情况下使用这种模式，这可能会错误地让事务打开几个小时。

Tip

启用autocommit选项，除非您有特定的理由禁用它。当您启用自动提交时，InnoDB 还可以为许多SELECT查询检测出它是一个只读事务，并减少查询的开销。

另一个缺陷是在事务活动时启动事务并在应用中执行缓慢的操作。这可以是发送回用户的数据、交互式提示或文件 I/O。确保在 MySQL 中没有打开活动事务时执行这些缓慢的操作。

索引

索引减少了访问给定行所需的工作量。这样，索引是减少锁定的一个很好的工具，因为只有在执行查询时访问的记录才会被锁定。

考虑一个简单的例子，在world.city表中查询名为 Sydney 的城市:

START TRANSACTION;

SELECT *
  FROM world.city
 WHERE Name = 'Sydney'
   FOR SHARE;

FOR SHARE选项用于强制查询对读取的记录使用共享锁。默认情况下,Name列上没有索引，因此查询将执行全表扫描来查找结果中需要的行。如果没有索引，则有 4103 个记录锁(有些是重复的):

mysql> SELECT INDEX_NAME, LOCK_TYPE,
              LOCK_MODE, COUNT(*)
         FROM performance_schema.data_locks
        WHERE OBJECT_SCHEMA = 'world'
              AND OBJECT_NAME = 'city'
        GROUP BY INDEX_NAME, LOCK_TYPE, LOCK_MODE;
+------------+-----------+-----------+----------+
| INDEX_NAME | LOCK_TYPE | LOCK_MODE | COUNT(*) |
+------------+-----------+-----------+----------+
| NULL       | TABLE     | IS        |        1 |
| PRIMARY    | RECORD    | S         |     4103 |
+------------+-----------+-----------+----------+
2 rows in set (0.0210 sec)

如果在Name列上添加一个索引，锁计数将减少到总共三个记录锁:

mysql> SELECT INDEX_NAME, LOCK_TYPE,
              LOCK_MODE, COUNT(*)
         FROM performance_schema.data_locks
        WHERE OBJECT_SCHEMA = 'world'
              AND OBJECT_NAME = 'city'
        GROUP BY INDEX_NAME, LOCK_TYPE, LOCK_MODE;
+------------+-----------+---------------+----------+
| INDEX_NAME | LOCK_TYPE | LOCK_MODE     | COUNT(*) |
+------------+-----------+---------------+----------+
| NULL       | TABLE     | IS            |        1 |
| Name       | RECORD    | S             |        1 |
| PRIMARY    | RECORD    | S,REC_NOT_GAP |        1 |
| Name       | RECORD    | S,GAP         |        1 |
+------------+-----------+---------------+----------+
4 rows in set (0.0005 sec)

另一方面，更多的索引提供了更多访问相同行的方法，这可能会增加死锁的数量。

记录访问顺序

确保您尽可能多地以相同的顺序访问不同事务的记录。在本章前面讨论的死锁示例中，导致死锁的原因是两个事务以相反的顺序访问行。如果它们以相同的顺序访问这些行，就不会出现死锁。当您访问不同表中的记录时，这也适用。

确保相同的访问顺序绝非易事。当您执行连接并且优化器为两个查询决定不同的连接顺序时，甚至可能发生不同的访问顺序。如果不同的连接顺序导致过多的锁问题，您可以考虑使用第 17 章中描述的优化器提示来告诉优化器改变连接顺序，但是在这种情况下，您当然也应该考虑查询性能。

事务隔离级别

InnoDB 支持几种事务隔离级别。不同的隔离级别有不同的锁需求:特别是REPEATABLE READ和SERIALIZABLE比READ COMMITTED需要更多的锁。

READ COMMITTED事务隔离级别可以从两个方面帮助解决锁定问题。使用的间隙锁要少得多，并且在 DML 语句期间被访问但未被修改的行在语句完成后会再次释放它们的锁。对于REPEATABLE READ和SERIALIZABLE，锁仅在事务结束时释放。

Note

人们常说READ COMMITTED事务隔离级别不采用间隙锁。这是一个神话，是不正确的。虽然使用的间隙锁要少得多，但仍然需要一些。例如，这包括 InnoDB 在更新时执行页面分割。(页面分割将在第 25 章中讨论。)

考虑一个例子，其中使用CountryCode列将查询限制在一个国家，名为 Sydney 的城市的人口发生了变化。这可以通过以下查询来完成:

START TRANSACTION;

UPDATE world.city
   SET Population = 5000000
 WHERE Name = 'Sydney'
       AND CountryCode = 'AUS';

在Name列上没有索引，但是在CountryCode上有一个。所以更新需要扫描部分CountryCode索引。清单 18-13 展示了一个在REPEATABLE READ事务隔离级别执行查询的例子。

-- Connection 1
Connection 1> SET transaction_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.0003 sec)

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

Connection 1> UPDATE world.city
                 SET Population = 5000000
               WHERE Name = 'Sydney'
                 AND CountryCode = 'AUS';
Query OK, 1 row affected (0.0005 sec)

Rows matched: 1  Changed: 1  Warnings: 0

-- Connection 2
Connection 2> SELECT INDEX_NAME, LOCK_TYPE,
                     LOCK_MODE, COUNT(*)
                FROM performance_schema.data_locks
               WHERE OBJECT_SCHEMA = 'world'
                     AND OBJECT_NAME = 'city'
               GROUP BY INDEX_NAME, LOCK_TYPE, LOCK_MODE;
+-------------+-----------+---------------+----------+
| INDEX_NAME  | LOCK_TYPE | LOCK_MODE     | COUNT(*) |
+-------------+-----------+---------------+----------+
| NULL        | TABLE     | IX            |        1 |
| CountryCode | RECORD    | X             |       14 |
| PRIMARY     | RECORD    | X,REC_NOT_GAP |       14 |
| CountryCode | RECORD    | X,GAP         |        1 |
+-------------+-----------+---------------+----------+
4 rows in set (0.0007 sec)

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

Listing 18-13The locks held in the REPEATABLE READ transaction isolation level

在每个CountryCode索引和主键上有 14 个记录锁，在CountryCode索引上有一个间隙锁。将这与在清单 18-14 中所示的READ COMMITTED事务隔离级别中执行查询后持有的锁进行比较。

-- Connection 1
Connection 1> SET transaction_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.0003 sec)

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

Connection 1> UPDATE world.city
                 SET Population = 5000000
               WHERE Name = 'Sydney'
                 AND CountryCode = 'AUS';
Query OK, 1 row affected (0.0005 sec)

Rows matched: 1  Changed: 1  Warnings: 0

-- Connection 2
Connection 2> SELECT INDEX_NAME, LOCK_TYPE,
                     LOCK_MODE, COUNT(*)
                FROM performance_schema.data_locks
               WHERE OBJECT_SCHEMA = 'world'
                     AND OBJECT_NAME = 'city'
               GROUP BY INDEX_NAME, LOCK_TYPE, LOCK_MODE;
+-------------+-----------+---------------+----------+
| INDEX_NAME  | LOCK_TYPE | LOCK_MODE     | COUNT(*) |
+-------------+-----------+---------------+----------+
| NULL        | TABLE     | IX            |        1 |
| CountryCode | RECORD    | X,REC_NOT_GAP |        1 |
| PRIMARY     | RECORD    | X,REC_NOT_GAP |        1 |
+-------------+-----------+---------------+----------+
3 rows in set (0.0006 sec)

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

Listing 18-14The locks held in the READ-COMMITTED transaction isolation level

在这里，记录锁减少为每个索引和主键上的一个锁。没有间隙锁。

并非所有工作负载都可以使用READ COMMITTED事务隔离级别。如果您必须让SELECT语句在同一事务中多次执行时返回相同的结果，或者让不同的查询对应于同一时间快照，您必须使用REPEATABLE READ或SERIALIZABLE。但是，在许多情况下，降低隔离级别是一个选项，您可以为不同的事务选择不同的隔离级别。如果您正在从 Oracle DB 迁移应用，那么您已经在使用READ COMMITTED，并且您也可以在 MySQL 中使用它。

抢先锁定

将讨论的最后一个策略是抢先锁定。如果您有一个执行多个查询的复杂事务，在某些情况下，执行一个SELECT ... FOR UPDATE或SELECT ... FOR SHARE查询来锁定您知道在事务中稍后会用到的记录可能是一种优势。另一个有用的情况是，确保对于不同的任务以相同的顺序访问行。

抢先锁定对于减少死锁的频率特别有效。一个缺点是，你最终会持有更长时间的锁。总的来说，抢占式锁定是一种应该谨慎使用的策略，但是在正确的情况下，它可以有效地防止死锁。

本章的最后一个主题是回顾如何监控锁。

监控锁

已经有几个查询持有的锁的信息的例子。本节将回顾已经提到的资源，并介绍一些额外的资源。第 22 章将通过展示调查锁问题的例子对此进行深入探讨。监控选项可以分为四组:性能模式、sys模式、状态指标和 InnoDB 锁监控。

性能模式

性能模式包含除死锁之外的大多数可用锁信息的来源。您不仅可以直接使用性能模式中的锁信息；它还用于sys模式中两个与锁相关的视图。

这些信息可通过四个表格获得:

data_locks : 该表包含 InnoDB 级别的表和锁记录的详细信息。它显示当前持有的或待定的所有锁。
data_lock_waits : 与data_locks表一样，它显示了与 InnoDB 相关的锁，但是只显示那些等待被授予关于哪些线程阻塞了请求的信息的锁。
metadata_locks : 该表包含关于用户级锁、元数据锁等的信息。要记录信息，必须启用wait/lock/metadata/sql/mdl性能模式工具(在 MySQL 8 中默认启用)。OBJECT_TYPE列显示持有哪种锁。
table_handles : 该表保存了关于哪些表锁当前有效的信息。必须启用wait/lock/table/sql/handler性能模式仪器才能记录数据(这是默认设置)。与其他表格相比，此表格的使用频率较低。

metadata_locks表是最通用的表，它支持从全局读锁到低级锁(如访问控制列表(ACL ))的各种锁。表 18-3 按字母顺序总结了OBJECT_TYPE列的可能值，并简要说明了每个值代表的锁。

表 18-3

performance _ schema . metadata _ locks 表中的对象类型

对象类型

描述

| | --- | --- | | ACL_CACHE | 用于访问控制列表(ACL)缓存。 | | BACKUP_LOCK | 备用锁。 | | CHECK_CONSTRAINT | 对于CHECK约束的名称。 | | COLUMN_STATISTICS | 用于直方图和其他列统计。 | | COMMIT | 用于阻止提交。它与全局读锁相关。 | | EVENT | 对于存储的事件。 | | FOREIGN_KEY | 对于外键名。 | | GLOBAL | 用于全局读锁(由FLUSH TABLES WITH READ LOCK触发)。 | | FUNCTION | 对于存储函数。 | | LOCKING_SERVICE | 对于使用锁定服务接口获取的锁。 | | PROCEDURE | 对于存储过程。 | | RESOURCE_GROUPS | 对于资源组。 | | SCHEMA | 对于模式/数据库。这些类似于表的元数据锁，只是它们是用于模式的。 | | SRID | 用于空间参考系统。 | | TABLE | 对于表和视图。这包括本章中讨论的元数据锁。 | | TABLESPACE | 对于表空间。 | | TRIGGER | For 触发器(在表上)。 | | USER_LEVEL_LOCK | 用于用户级锁。 |

性能模式表中的数据是原始锁数据。通常，当您调查锁问题或监控锁问题时，确定是否有锁等待更有意义。对于这些信息，您需要使用sys模式。

sys 架构

sys模式有两个视图，获取性能模式表中的信息并返回锁对，其中一个锁由于另一个锁而不能被授予。因此，它们显示了锁等待的问题所在。这两个视图是innodb_lock_waits和schema_table_lock_waits。

innodb_lock_waits视图使用性能模式中的data_locks和data_lock_waits视图返回 InnoDB 记录锁的所有锁等待情况。它显示诸如连接试图获取什么锁以及涉及哪些连接和查询之类的信息。如果您需要没有格式的信息，视图也以x$innodb_lock_waits的形式存在。

schema_table_lock_waits视图以类似的方式工作，但是使用metadata_locks表返回与模式对象相关的锁等待。该信息在x$schema_table_lock_waits视图中也是无格式的。

第 22 章包含了使用两种视图来调查锁问题的例子。

状态计数器和 InnoDB 指标

有几个状态计数器和 InnoDB 指标提供关于锁定的信息。这些主要用于全局(实例)级别，对于检测锁问题的总体增加非常有用。一起监控所有这些指标的一个好方法是使用sys.metrics视图。清单 18-15 展示了一个检索指标的例子。

mysql> SELECT Variable_name,
              Variable_value AS Value,
              Enabled
         FROM sys.metrics
        WHERE Variable_name LIKE 'innodb_row_lock%'
              OR Variable_name LIKE 'Table_locks%'
              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     |
| table_locks_immediate         | 4194   | YES     |
| table_locks_waited            | 0      | YES     |
| lock_deadlocks                | 1      | 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     |
+-------------------------------+--------+---------+
19 rows in set (0.0076 sec)

Listing 18-15Lock metrics

如您所见，默认情况下，并非所有指标都是启用的。未启用的可使用第 7 章中讨论的innodb_monitor_enable选项启用。innodb_row_lock_%、lock_deadlocks和lock_timeouts度量是最有趣的。行锁指标显示了当前有多少锁正在等待，并统计了等待获取 InnoDB 记录锁所花费的时间(毫秒)。lock_deadlocks和lock_timeouts指标分别显示遇到的死锁和锁等待超时的数量。

InnoDB 锁监控器和死锁记录

InnoDB 很久以前就有了自己的锁监控器，锁信息在 InnoDB 监控器输出中返回。默认情况下，InnoDB 监控器包含关于最新死锁以及锁等待中涉及的锁的信息。通过启用innodb_status_output_locks选项(默认禁用)，将列出所有锁；这类似于性能模式data_locks表中的内容。

为了演示死锁和事务信息，您可以从清单 18-12 中创建死锁，并创建一个新的正在进行的事务，该事务通过world.city表中的主键更新了一行:

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

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

Rows matched: 1  Changed: 1  Warnings: 0

使用SHOW ENGINE INNODB STATUS语句生成 InnoDB 锁监控器输出。清单 18-16 显示了启用所有锁定信息并生成监控器输出的示例。完整的 InnoDB monitor 输出也可以从本书的 GitHub 资源库的文件listing_18_16.txt中获得。

mysql> SET GLOBAL innodb_status_output_locks = ON;
Query OK, 0 rows affected (0.0022 sec)

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1\. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2019-11-04 17:04:48 0x6e88 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 51 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 170 srv_active, 0 srv_shutdown, 62448 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 138
OS WAIT ARRAY INFO: signal count 133
RW-shared spins 1, rounds 1, OS waits 0
RW-excl spins 109, rounds 1182, OS waits 34
RW-sx spins 24, rounds 591, OS waits 18
Spin rounds per wait: 1.00 RW-shared, 10.84 RW-excl, 24.63 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-11-03 19:41:43 0x4b78
*** (1) TRANSACTION:
TRANSACTION 5585, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 37, OS thread handle 28296, query id 21071 localhost ::1 root updating
UPDATE world.city
                 SET Population = Population + 1
               WHERE ID = 130

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 159 page no 28 n bits 248 index PRIMARY of table `world`.`city` trx id 5585 lock_mode X locks rec but not gap
Record lock, heap no 26 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000edd; asc     ;;
 1: len 6; hex 0000000015d1; asc       ;;
 2: len 7; hex 01000000f51aa6; asc        ;;
 3: len 30; hex 53616e204672616e636973636f2020202020202020202020202020202020; asc San Francisco                 ; (total 35 bytes);
 4: len 3; hex 555341; asc USA;;

 5: len 20; hex 43616c69666f726e696120202020202020202020; asc California          ;;
 6: len 4; hex 800bda1e; asc     ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
...
------------
TRANSACTIONS
------------
Trx id counter 5662
Purge done for trx's n:o < 5661 undo n:o < 0 state: running but idle
History list length 11
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284075292758256, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284075292756560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284075292755712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5661, ACTIVE 60 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 40, OS thread handle 2044, query id 26453 localhost ::1 root
TABLE LOCK table `world`.`city` trx id 5661 lock mode IX
RECORD LOCKS space id 160 page no 7 n bits 248 index PRIMARY of table `world`.`city` trx id 5661 lock_mode X locks rec but not gap
Record lock, heap no 41 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000082; asc     ;;
 1: len 6; hex 00000000161d; asc       ;;
 2: len 7; hex 01000001790a72; asc     y r;;
 3: len 30; hex 5379646e6579202020202020202020202020202020202020202020202020; asc Sydney                        ; (total 35 bytes);
 4: len 3; hex 415553; asc AUS;;
 5: len 20; hex 4e657720536f7574682057616c65732020202020; asc New South Wales     ;;
 6: len 4; hex 8031fdb0; asc  1  ;;
...

Listing 18-16The InnoDB monitor output

靠近顶部的部分是LATEST DETECTED DEADLOCK部分，它包括最近一次死锁所涉及的事务和锁的详细信息以及它发生的时间。如果自 MySQL 最后一次重启以来没有发生死锁，则省略这一节。第 22 章将包括一个调查死锁的例子。

Note

InnoDB 监控器输出中的 deadlock 部分仅包含涉及 InnoDB 记录锁的死锁信息。对于涉及用户级锁的死锁，没有等效的信息。

输出再往下一点，是列出 InnoDB 事务的部分TRANSACTIONS。请注意，不持有任何锁的事务(例如，纯SELECT查询)不包括在内。在这个例子中，在world.city表上有一个意向排他锁，在主键等于 130 的行上有一个排他锁(第一个字段的记录锁信息中的 80000082 表示值为 0x82 的行，它与十进制表示法中的 130 相同)。

Tip

如今，InnoDB 监控器输出中的锁定信息最好从performance_schema.data_locks和performance_schema.data_lock_waits表中获取。然而，死锁信息仍然非常有用。

您可以请求每隔 15 秒将监控器输出转储到stderr。您可以通过启用innodb_status_output选项来启用转储。请注意，输出非常大，所以如果启用它，请做好错误日志快速增长的准备。InnoDB monitor 输出也很容易隐藏关于更严重问题的消息。

如果您想确保记录所有死锁，您可以启用innodb_print_all_deadlocks选项。这导致每次发生死锁时，InnoDB monitor 输出中的死锁信息都会打印到错误日志中。如果您需要调查死锁，这可能是有用的，但是建议您仅在需要时启用它，以避免错误日志变得非常大并可能隐藏其他问题。

Caution

如果启用 InnoDB 监控器的常规输出或关于所有死锁的信息，请小心。这些信息很容易隐藏错误日志中记录的重要消息。

摘要

锁是一个庞大而复杂的话题。希望这一章已经帮助你了解了为什么需要锁以及各种锁。

这一章开始询问为什么需要锁。没有锁，对模式和数据进行并发访问是不安全的。打个比方，数据库锁的工作方式与交通信号灯和停车标志在交通中的工作方式相同。它规范了对数据的访问，因此事务可以确保不会与另一个事务发生冲突而导致不一致的结果。

数据有两种访问级别:共享访问(也称为读访问)和独占访问(也称为写访问)。这些访问级别适用于各种锁粒度，从全局读锁到记录锁和间隙锁。此外，InnoDB 在表级别使用意向共享锁和意向排他锁。

努力减少应用需要的锁的数量并减少所需锁的影响是很重要的。减少锁问题的策略本质上可以归结为在事务中做尽可能少的工作，方法是使用索引，将大型事务分成较小的事务，并尽可能短时间地持有锁。对于应用中的不同任务，尝试以相同的顺序访问数据也很重要；否则，可能会出现不必要的死锁。

本章的最后一部分介绍了性能模式、sys模式、状态指标和 InnoDB 监控器中的锁监控选项。大多数监控最好使用性能模式表和sys模式视图来完成。例外情况是死锁，此时 InnoDB 监控器仍然是最佳选择。

这是第四部分的结论。现在是时候让查询分析变得更加实用了，首先要找到适合优化的查询。

Footnotes [1](#Fn1_source)

https://bugs.mysql.com/bug.php?id=44884

十九、为优化寻找候选查询

当您遇到性能问题时，第一步是确定是什么导致了它。表现差可能有几个原因，所以在寻找原因时要保持开放的心态。本章的重点是找到可能导致性能下降的查询，或者在将来负载和数据量增加时可能成为问题的查询。尽管如此，正如在第 1 章中所讨论的，你需要考虑你的系统的所有方面，通常它可能是导致问题的因素的组合。

本章介绍了查询性能相关信息的各种来源。首先，将讨论性能模式。性能模式是本章中讨论的许多其他特性的基础。其次，sys模式的视图以及语句性能分析器特性都包括在内。第三，展示了如何使用 MySQL Workbench 为前两节中讨论的几个报告获得图形用户界面。第四，讨论了监控对于寻找优化候选的重要性。虽然本节使用 MySQL Enterprise Monitor 作为讨论的基础，但是这些原则适用于一般的监控，因此即使您使用不同的监控解决方案，也鼓励您阅读本节。第五个也是最后一个是慢速查询日志，它是查找慢速查询的传统工具。

Note

本章包括几个带有输出的例子。通常，对于包含计时和其他不确定数据的值，相同示例的输出会有所不同。

由于锁争用而性能不佳的查询将不包括在内；相反，第 22 章详细介绍了如何调查锁问题。事务包含在第 21 章中。

性能模式

性能模式是查询性能信息的金矿。这使得在讨论如何找到作为优化候选的查询时，它成为显而易见的起点。您可能最终会使用一些构建在性能模式之上的方法，但是仍然鼓励您很好地理解底层的表，这样您就知道如何访问原始数据并生成您自己的定制报告。

本节将首先讨论如何获取有关语句和预准备语句的信息，然后讨论表和文件 I/O，最后展示如何找出导致错误的原因和错误。

语句事件表

使用基于语句事件的性能模式表是寻找优化候选查询的最直接的方法。这些表将允许您获得关于在实例上执行的查询的非常详细的信息。需要注意的一点是，作为预处理语句执行的查询不包括在语句表中。

有几个包含语句信息的表。这些是

events_statements_current : 当前正在执行的语句或对空闲连接最新执行的查询。执行存储程序时，每个连接可能有多行。
events_statements_history : 每个连接的最后语句。每个连接的语句数量上限为performance_schema_events_statements_history_size(默认为 10)。当连接关闭时，连接的语句将被删除。
events_statements_history_long : 对实例的最新查询，不管是哪个连接执行的。该表还包括来自已关闭连接的语句。默认情况下，此表的使用者是禁用的。行数上限为performance_schema_events_statements_history_long_size(默认为 10000)。
events_statements_summary_by_digest : 将报表统计按默认模式分组并摘要。稍后将详细讨论该表。
events_statements_summary_by_account_by_event_name : 按账户和事件名称分组的报表统计。事件名显示执行的是哪种语句，例如，statement/sql/select表示直接执行的SELECT语句(不通过存储程序执行)。
events_statements_summary_by_host_by_event_name : 按账户主机名和事件名分组的报表统计。
events_statements_summary_by_program : 按执行语句的存储程序(事件、函数、过程、表或触发器)分组的语句统计信息。这有助于找到执行最多工作的存储程序。
events_statements_summary_by_thread_by_event_name : 按线程和事件名称分组的语句统计。仅包括当前连接的线程。
events_statements_summary_by_user_by_event_name : 按账户用户名和事件名称分组的报表统计。
events_statements_summary_global_by_event_name : 按事件名称分组的报表统计。
events_statements_histogram_by_digest : 按默认模式分组的直方图统计和摘要。
events_statements_histogram_global : 直方图统计，其中所有查询都聚集在一个直方图中。
threads : 实例中所有线程的信息，包括后台线程和前台线程。您可以使用此表代替SHOW PROCESSLIST命令。除了进程列表信息之外，还有显示线程是否被检测、操作系统线程 id 等等的列。

除了两个直方图表和threads表，所有列出的表都有相似的列。最常使用的表是events_statements_summary_by_digest，所以它将作为讨论的基础。events_statements_summary_by_digest表实质上是一份报告，报告了自从上次重置该表(通常是在重启 MySQL 时)以来在实例上执行的所有查询。查询按其摘要和执行时使用的默认模式进行分组。表中各列汇总在表 19-1 中。

表 19-1

events_statements_summary_by_digest表中的列

列名

描述

| | --- | --- | | SCHEMA_NAME | 执行查询时作为默认架构的架构。如果没有默认模式，则值为NULL。 | | DIGEST | 规范化查询的摘要。在 MySQL 8 中，这是一个 sha256 哈希。 | | DIGEST_TEXT | 规范化的查询。 | | COUNT_STAR | 查询已执行的次数。 | | SUM_TIMER_WAIT | 执行查询所花费的总时间。请注意，在执行 30 周多一点的时间后，该值会溢出。 | | MIN_TIMER_WAIT | 执行查询的最快速度。 | | AVG_TIMER_WAIT | 平均执行时间。这与SUM_TIMER_WAIT/COUNT_STAR相同，除非SUM_TIMER_WAIT已经溢出。 | | MAX_TIMER_WAIT | 执行查询的最慢速度。 | | SUM_LOCK_TIME | 等待表锁所花费的总时间。 | | SUM_ERRORS | 执行查询时遇到的错误总数。 | | SUM_WARNINGS | 执行查询时遇到的警告总数。 | | SUM_ROWS_AFFECTED | 查询已修改的总行数。 | | SUM_ROWS_SENT | 已返回(发送)到客户端的总行数。 | | SUM_ROWS_EXAMINED | 查询已检查的总行数。 | | SUM_CREATED_TMP_DISK_TABLES | 查询已创建的磁盘上内部临时表的总数。 | | SUM_CREATED_TMP_TABLES | 由查询创建的内部临时表(无论是在内存中还是在磁盘上创建的)的总数。 | | SUM_SELECT_FULL_JOIN | 由于没有联接条件的索引或联接条件，已执行全表扫描的联接总数。这与增加Select_full_join状态变量是一样的。 | | SUM_SELECT_FULL_RANGE_JOIN | 使用全范围搜索的联接总数。这与增加Select_full_range_join状态变量是一样的。 | | SUM_SELECT_RANGE | 查询使用范围搜索的总次数。这与增加Select_range状态变量是一样的。 | | SUM_SELECT_RANGE_CHECK | 查询的联接总数，其中联接没有在每行之后检查索引使用情况的索引。这与增加Select_range_check状态变量是一样的。 | | SUM_SELECT_SCAN | 查询对联接中的第一个表执行全表扫描的总次数。这与增加Select_scan状态变量是一样的。 | | SUM_SORT_MERGE_PASSES | 为对查询结果进行排序而完成的排序合并传递的总数。这与增加Sort_merge_passes状态变量是一样的。 | | SUM_SORT_RANGE | 使用范围进行排序的总次数。这与增加Sort_range状态变量是一样的。 | | SUM_SORT_ROWS | 已排序的总行数。这与增加Sort_rows状态变量是一样的。 | | SUM_SORT_SCAN | 通过扫描表进行排序的总次数。这与增加Sort_scan状态变量是一样的。 | | SUM_NO_INDEX_USED | 未使用索引来执行查询的总次数。 | | SUM_NO_GOOD_INDEX_USED | 使用无效索引的总次数。这意味着EXPLAIN输出中的Extra列包含“为每个记录检查的范围” | | FIRST_SEEN | 查询第一次出现的时间。当表被截断时，第一个看到的值也被重置。 | | LAST_SEEN | 上次看到查询的时间。 | | QUANTILE_95 | 查询延迟的第 95 个百分位数。也就是说，95%的查询在给定的时间或更短的时间内完成。 | | QUANTILE_99 | 查询延迟的第 99 个百分位数。 | | QUANTILE_999 | 查询延迟的第 99.9 个百分点。 | | QUERY_SAMPLE_TEXT | 规范化前的查询示例。您可以使用它来获取查询的查询执行计划。 | | QUERY_SAMPLE_SEEN | 看到示例查询的时间。 | | QUERY_SAMPLE_TIMER_WAIT | 示例查询的执行时间。 |

(SCHEMA_NAME，DIGEST)上有一个唯一的索引，用于对数据进行分组。表中最多可以有performance_schema_digests_size(动态调整大小，但通常默认为 10000)行。当插入最后一行时，schema 和 digest 都被设置为NULL，并且该行被用作一个总括行。每次使用捕获所有行时，Performance_schema_digest_lost状态变量就会递增。使用events_statements_current、events_statements_history和events_statements_history_long表，该表中汇总的信息也可用于单个查询。

Tip

由于数据是按SCHEMA_NAME、DIGEST分组的，所以当应用一致地设置默认模式(例如，MySQL Shell 中的\use world或--schema命令行选项，或者您使用的客户端/连接器中的等效选项)时，您可以充分利用events_statements_summary_by_digest表。要么永远设置，要么永远不设置。同样，如果在引用表时有时包含模式名，有时不包含，那么相同的查询将被视为两个不同的摘要。

两组列需要更多的解释，分位数列和查询样本列。分位数列的值是基于摘要的直方图统计确定的。基本上，如果您获取给定摘要和默认模式的events_statements_histogram_by_digest表，并转到具有 95%查询执行的存储桶，那么该存储桶用于确定第 95 个百分位数。直方图表格将在稍后讨论。

对于样本查询信息，如果满足三个条件中的至少一个，则样本查询被替换:

对于给定的默认模式，这是第一次遇到摘要。
摘要和模式的新出现具有比当前用作样本查询的查询更高的TIMER_WAIT值(即，它更慢)。
如果performance_schema_max_digest_sample_age选项的值大于 0，并且当前样本查询超过了performance_schema_max_digest_sample_age秒。

performance_schema_max_digest_sample_age的值默认为 60 秒，如果您每分钟都监控events_statements_summary_by_digest表，这就很好了。这样，监控代理将能够在每一分钟的时间间隔内获得最慢的查询，并获得最慢查询的完整历史记录。如果您的监控间隔更长，请考虑增加performance_schema_max_digest_sample_age的值。

从列的列表中可以看出，有很多机会可以查询满足某些要求的语句。诀窍是查询重要的东西。什么是重要取决于具体情况，因此不可能给出适用于所有情况的具体问题。例如，如果您从监控中知道大量内部临时表使用内存或磁盘存在问题，那么SUM_CREATED_TMP_DISK_TABLES和SUM_CREATED_TMP_TABLES列是过滤的良好候选。

一些条件是普遍感兴趣的。可能需要进一步调查的一些情况包括

与发送回客户端的行数或被修改的行数相比，检查的行数较多。这可能表明索引使用不当。
没有使用索引或没有使用好的索引的总和很高。这可能意味着查询可以从新索引或重写查询中受益。
完全连接的数量很大。这表明要么需要一个索引，要么缺少一个连接条件。
范围检查的次数很多。这可能意味着您需要更改查询中表的索引。
如果分位数延迟在接近更高的分位数时表现出严重的下降，这可能表明您有时在及时解决查询方面存在问题。这可能是由于实例通常过载、锁问题、某些条件触发了不良查询计划或其他原因。
在磁盘中创建的内部临时表的数量很大。这可能意味着您需要考虑哪些索引用于排序和分组，内部临时表允许的内存量，或者其他可能会阻止将内部临时表写入磁盘或首先创建内部临时表的更改。
排序合并的数量很大。这可能意味着该查询可以受益于更大的排序缓冲区。
执行死刑的数量很大。这并不意味着查询有任何问题，但是查询执行得越频繁，查询的改进效果就越好。在某些情况下，高执行计数也可能是由不必要的查询执行造成的。
错误或警告的数量很高。虽然这可能不会影响性能，但它表明有问题。请注意，有些查询总是会生成警告，例如,EXPLAIN,因为它使用警告来返回附加信息。

Caution

如果您仍在使用 MySQL 5.7，请小心增加sort_buffer_size的值，因为即使它减少了排序合并的次数，也会降低性能。在 MySQL 8 中，排序缓冲区得到了改进，更大的缓冲区的性能下降要少得多。不过，不要增加超过你需要的大小。

您应该知道，仅仅因为一个查询满足这些条件中的一个，并不意味着有什么要改变的。例如，考虑一个从表中聚合数据的查询。该查询可能会检查表的大部分，但只返回几行。在没有有意义的索引可以帮助的情况下，它甚至可能需要全表扫描。从检查的行数和发送的行数之比的角度来看，该查询的性能会很差，并且可能 no 索引计数器正在递增。然而，查询可以很好地完成返回所需结果所需的最少量的工作。如果您确定查询是一个性能问题，您将需要找到一个不同于添加索引的解决方案；例如，您可以在非高峰时段执行查询并缓存结果，或者您可以有一个单独的实例来执行类似这样的查询。

清单 19-1 展示了一个例子，它找到了默认模式和语句摘要的组合，这种组合在events_statements_summary_by_digest表被最后一次重置后被执行了最多次。

mysql> SELECT ∗
         FROM performance_schema.events_statements_summary_by_digest
        ORDER BY COUNT_STAR DESC
        LIMIT 1\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
                SCHEMA_NAME: world
                     DIGEST: b49cb8f3db720a96fb29da86437bd7809ef30463fac88e85ed4f851f96dcaa30
                DIGEST_TEXT: SELECT ∗ FROM `city` WHERE NAME = ?
                 COUNT_STAR: 102349
             SUM_TIMER_WAIT: 138758688272512
             MIN_TIMER_WAIT: 1098756736
             AVG_TIMER_WAIT: 1355485824
             MAX_TIMER_WAIT: 19321416576
              SUM_LOCK_TIME: 5125624000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 132349
          SUM_ROWS_EXAMINED: 417481571
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 102349
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 102349
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2019-06-22 10:25:18.260657
                  LAST_SEEN: 2019-06-22 10:30:12.225425
                QUANTILE_95: 2089296130
                QUANTILE_99: 2884031503
               QUANTILE_999: 3630780547
          QUERY_SAMPLE_TEXT: SELECT ∗ FROM city WHERE Name = 'San José'
          QUERY_SAMPLE_SEEN: 2019-06-22 10:29:56.81501
    QUERY_SAMPLE_TIMER_WAIT: 19321416576
1 row in set (0.0019 sec)

Listing 19-1Using the events_statements_summary_by_digest table

输出显示，按名称查询world模式中的city表是执行次数最多的查询。您应该将值COUNT_STAR与其他查询进行比较，以了解与其他查询相比，该查询的执行频率。在这个例子中，您可以看到查询平均每次执行返回 1.3 行，但是检查了 4079 行。这意味着查询对返回的每一行检查 3000 多行。由于这是一个经常执行的查询，这表明用于过滤的Name列需要一个索引。输出的底部显示了一个查询的实例，您可以使用下一章中描述的EXPLAIN来分析查询执行计划。

如上所述，MySQL 还维护语句的直方图统计。有两种直方图表格可用:events_statements_histogram_by_digest和events_statements_histogram_global。两者的区别在于，前者将直方图信息按默认模式和摘要进行分组，而后者包含分组在一起的所有查询信息。直方图信息有助于确定查询延迟的分布，类似于针对events_statements_summary_by_digest表中的分位数列所讨论的信息，但粒度更细。这些表是自动管理的。

如前所述，预处理语句不包括在语句事件表中。相反，你需要使用prepared_statements_instances表。

准备好的发言摘要

准备好的语句有助于加速在连接中重用的查询的执行。例如，如果您的应用一直使用相同的连接，那么您可以准备应用使用的语句，然后在需要时执行准备好的语句。

准备好的语句使用占位符，因此您只需在准备查询时提交查询的模板。这样，您可以为每次执行提交不同的参数。当以这种方式使用时，准备好的语句充当应用可以使用给定执行所需的参数的语句目录。

清单 19-2 显示了一个通过 SQL 接口使用准备好的语句的简单例子。在应用中，您通常会使用一个以更透明的方式处理准备好的语句的连接器。例如，对于 MySQL 连接器/Python，您告诉它您想要使用准备好的语句，连接器会在您第一次执行它时自动为您准备好语句。尽管基本原理是相同的。

mysql> SET @sql = 'SELECT ∗ FROM world.city WHERE ID = ?';
Query OK, 0 rows affected (0.0002 sec)

mysql> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.0080 sec)

Statement prepared

mysql> SET @val = 130;
Query OK, 0 rows affected (0.0003 sec)

mysql> EXECUTE stmt USING @val\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
         ID: 130
       Name: Sydney
CountryCode: AUS
   District: New South Wales
 Population: 3276207
1 row in set (0.0023 sec)

mysql> SET @val = 3805;
Query OK, 0 rows affected (0.0003 sec)

mysql> EXECUTE stmt USING @val\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
         ID: 3805
       Name: San Francisco
CountryCode: USA
   District: California
 Population: 776733
1 row in set (0.0004 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.0003 sec)

Listing 19-2Example of using prepared statements

SQL 接口使用用户变量将语句和值传递给 MySQL。第一步，准备报表；然后可以根据需要多次使用它来传递查询所需的参数。最后，释放准备好的语句。

当您想调查预准备语句的性能时，可以使用prepared_statements_instances表。该信息类似于events_statements_summary_by_digest表中的信息。清单 19-3 显示了清单 19-2 中使用的预准备语句的输出示例。

mysql> SELECT ∗
         FROM performance_schema.prepared_statements_instances\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
      OBJECT_INSTANCE_BEGIN: 1999818114352
               STATEMENT_ID: 1
             STATEMENT_NAME: stmt
                   SQL_TEXT: SELECT ∗ FROM world.city WHERE ID = ?
            OWNER_THREAD_ID: 87543
             OWNER_EVENT_ID: 20012
          OWNER_OBJECT_TYPE: NULL
        OWNER_OBJECT_SCHEMA: NULL
          OWNER_OBJECT_NAME: NULL
              TIMER_PREPARE: 369412736
            COUNT_REPREPARE: 0
              COUNT_EXECUTE: 2
          SUM_TIMER_EXECUTE: 521116288
          MIN_TIMER_EXECUTE: 247612288
          AVG_TIMER_EXECUTE: 260375808
          MAX_TIMER_EXECUTE: 273504000
              SUM_LOCK_TIME: 163000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 2
          SUM_ROWS_EXAMINED: 2
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
1 row in set (0.0008 sec)

Listing 19-3Using the prepared_statements_instances table

与 events 语句表的主要区别在于，没有分位数统计和查询示例，主键是OBJECT_INSTANCE_BEGIN——即准备好的语句的内存地址，而不是默认模式和摘要中的唯一键。事实上，prepared_statements_instances表中甚至没有提到默认模式和摘要。

正如主键是预准备语句的内存地址所暗示的那样，只有当预准备语句存在时，才维护预准备语句统计信息。因此，当由于连接关闭而显式或隐式释放语句时，统计信息将被清除。

对语句统计的讨论到此结束。还有更高级别的统计信息，如表 I/O 摘要。

表 I/O 摘要

性能模式中的表 I/O 信息经常被误解。表 I/O 摘要中提到的 I/O 是与表相关的输入输出的一般概念。因此，它不是指磁盘 I/O。相反，它是衡量表有多忙的一个通用指标。也就是说，一个表的磁盘 I/O 越多，花在表 I/O 上的时间也就越多。

有两个性能模式表包含表 I/O 的延迟统计信息:

table_io_waits_summary_by_table : 包含读取、写入、提取、插入和更新 I/O 详细信息的表的聚集信息
table_io_waits_summary_by_index_usage : 与table_io_waits_summary_by_table表中的信息相同，除了统计数据是按索引或无索引。

这些表格允许您详细了解表格的使用情况以及各种操作所用的时间。有七组活动，它们都有总延迟、最小延迟、平均延迟和最大延迟以及操作数。表 19-2 显示了基于列名的组。

表 19-2

表和索引 I/O 统计信息的延迟组

组

列

描述

| | --- | --- | --- | | 全部的 | COUNT_STAR``SUM_TIMER_WAIT``MIN_TIMER_WAIT``AVG_TIMER_WAIT``MAX_TIMER_WAIT | 整个表或索引的统计信息。 | | 读 | COUNT_READ``SUM_TIMER_READ``MIN_TIMER_READ``AVG_TIMER_READ``MAX_TIMER_READ | 所有读取操作的聚合统计信息。目前只有一个读取操作 fetch，因此读取统计信息将与读取统计信息相同。 | | 写 | COUNT_WRITE``SUM_TIMER_WRITE``MIN_TIMER_WRITE``AVG_TIMER_WRITE``MAX_TIMER_WRITE | 所有写操作的聚合统计信息。写操作是插入、更新和删除。 | | 取得 | COUNT_FETCH``SUM_TIMER_FETCH``MIN_TIMER_FETCH``AVG_TIMER_FETCH``MAX_TIMER_FETCH | 获取记录的统计数据。这不被称为“select”的原因是记录可能被提取用于其他目的，而不是用于SELECT语句。 | | 插入 | COUNT_INSERT``SUM_TIMER_INSERT``MIN_TIMER_INSERT``AVG_TIMER_INSERT``MAX_TIMER_INSERT | 插入记录的统计数据。 | | 更新 | COUNT_UPDATE``SUM_TIMER_UPDATE``MIN_TIMER_UPDATE``AVG_TIMER_UPDATE``MAX_TIMER_UPDATE | 更新记录的统计数据。 | | 删除 | COUNT_DELETE``SUM_TIMER_DELETE``MIN_TIMER_DELETE``AVG_TIMER_DELETE``MAX_TIMER_DELETE | 删除记录的统计数据。 |

在列表 19-4 中可以看到table_io_waits_summary_by_table表中这些列的信息示例。

mysql> SELECT ∗
         FROM performance_schema.table_io_waits_summary_by_table
        WHERE OBJECT_SCHEMA = 'world'
              AND OBJECT_NAME = 'city'\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
     OBJECT_TYPE: TABLE
   OBJECT_SCHEMA: world
     OBJECT_NAME: city
      COUNT_STAR: 418058733
  SUM_TIMER_WAIT: 125987200409940
  MIN_TIMER_WAIT: 1082952
  AVG_TIMER_WAIT: 301176
  MAX_TIMER_WAIT: 43045491156
      COUNT_READ: 417770654
  SUM_TIMER_READ: 122703207563448
  MIN_TIMER_READ: 1082952
  AVG_TIMER_READ: 293700
  MAX_TIMER_READ: 19644079288
     COUNT_WRITE: 288079
 SUM_TIMER_WRITE: 3283992846492
 MIN_TIMER_WRITE: 1937352
 AVG_TIMER_WRITE: 11399476
 MAX_TIMER_WRITE: 43045491156

     COUNT_FETCH: 417770654
 SUM_TIMER_FETCH: 122703207563448
 MIN_TIMER_FETCH: 1082952
 AVG_TIMER_FETCH: 293700
 MAX_TIMER_FETCH: 19644079288
    COUNT_INSERT: 4079
SUM_TIMER_INSERT: 209027413892
MIN_TIMER_INSERT: 10467468
AVG_TIMER_INSERT: 51244420
MAX_TIMER_INSERT: 31759300408
    COUNT_UPDATE: 284000
SUM_TIMER_UPDATE: 3074965432600
MIN_TIMER_UPDATE: 1937352
AVG_TIMER_UPDATE: 10827028
MAX_TIMER_UPDATE: 43045491156
    COUNT_DELETE: 0
SUM_TIMER_DELETE: 0
MIN_TIMER_DELETE: 0
AVG_TIMER_DELETE: 0
MAX_TIMER_DELETE: 0
1 row in set (0.0015 sec)

Listing 19-4Example of using the table_io_waits_summary_by_table table

在此输出中，除了没有删除行之外，该表有广泛的用途。还可以看出，大部分时间花在读取数据上(总共 125987200409940 皮秒中的 122703207563448 皮秒——或 97%)。

清单 19-5 显示了同一个表的输出，但是使用了table_io_waits_summary_by_index_usage表。usage 列与table_io_waits_summary_by_table表中的相同，在本例中已经被省略了，以强调两个表之间的区别。如果前面的例子中有任何额外的索引，那么将会返回更多的行。

mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA,
              OBJECT_NAME, INDEX_NAME,
              COUNT_STAR
         FROM performance_schema.table_io_waits_summary_by_index_usage
        WHERE OBJECT_SCHEMA = 'world'
              AND OBJECT_NAME = 'city'\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
  OBJECT_TYPE: TABLE
OBJECT_SCHEMA: world
  OBJECT_NAME: city
   INDEX_NAME: PRIMARY
   COUNT_STAR: 20004
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 2\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
  OBJECT_TYPE: TABLE
OBJECT_SCHEMA: world
  OBJECT_NAME: city
   INDEX_NAME: CountryCode
   COUNT_STAR: 549000
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 3\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
  OBJECT_TYPE: TABLE
OBJECT_SCHEMA: world
  OBJECT_NAME: city
   INDEX_NAME: NULL
   COUNT_STAR: 417489729
3 rows in set (0.0017 sec)

Listing 19-5Example of using the table_io_waits_summary_by_index_usage table

考虑一下COUNT_STAR的三个值。如果您将这些相加，20004+549000+417489729 = 418058733，您将得到与table_io_waits_summary_by_table表中的COUNT_STAR相同的值。这个例子显示了相同的数据，但是分布在city表上的两个索引和NULL索引中，这意味着没有使用任何索引。这使得table_io_waits_summary_by_index_usage表对于估计索引的有用性以及是否对表执行表扫描非常有用。

花一分钟时间考虑获取、插入、更新和删除计数器何时增加以及针对哪些索引是有用的。考虑一下world.city表，它在ID列中有一个主键，在CountryCode列中有一个辅助索引。这意味着您可以根据使用的索引或缺少的索引设置三种类型的过滤器:

**按主键:**使用主键定位行，例如WHERE ID = 130
**通过二级索引:**使用CountryCode索引定位行，例如WHERE CountryCode = 'AUS'
**无索引:**使用全表扫描来定位行，例如WHERE Name = 'Sydney'

表 19-3 显示了将三个示例WHERE子句中的每一个与SELECT、UPDATE或DELETE语句一起使用以及执行INSERT语句的矩阵。INSERT语句没有WHERE子句，所以有点不同。对于每个受影响的索引，都会列出读取和写入的次数。行列显示每个语句返回或受影响的行数。

表 19-3

各种查询对表 I/O 计数器的影响

查询/索引

行

读

写

| | --- | --- | --- | --- | | SELECT按主键PRIMARY | one | FETCH: 1 | | | SELECT按二级指标CountryCode | Fourteen | FETCH: 14 | | | SELECT按无索引NULL | one | FETCH: 4079 | | | UPDATE按主键PRIMARY | one | FETCH: 1 | UPDATE: 1 | | UPDATE按二级指标CountryCode | Fourteen | FETCH: 15 | UPDATE: 14 | | UPDATE按无索引PRIMARY``NULL | one | FETCH: 4080 | UPDATE: 1 | | DELETE按主键PRIMARY | one | FETCH: 1 | DELETE: 1 | | DELETE按二级指标CountryCode | Fourteen | FETCH: 15 | DELETE: 14 | | DELETE按无索引PRIMARY``NULL | one | FETCH: 4080 | DELETE: 1 | | INSERT``NULL | one | | INSERT: 1 |

从该表中可以看出，对于UPDATE和DELETE语句，即使它们是写语句，仍然存在读。原因是，在更改行之前，仍然必须定位这些行。另一个观察结果是，当使用辅助索引或无索引来更新或删除行时，读取的记录会比符合条件的记录多一条。最后，插入一行算作非索引操作。

What to Make of the I/O Latencies?

当您看到一个显示 I/O 延迟峰值的监控图时——无论是表 I/O 还是文件 I/O——很容易得出存在问题的结论。在你这样做之前，后退一步，考虑一下这些数据意味着什么。

从性能模式来看，I/O 延迟的增加既不是好事，也不是坏事。这是事实。这意味着有东西在做 I/O，如果有一个峰值，这意味着在此期间有比平常更多的 I/O，否则你不能从事件本身得出结论。

使用这些数据的一个更有用的方法是万一报告了一个问题。这可能是系统管理员报告磁盘利用率为 100%,或者终端用户报告系统运行缓慢。然后，你可以去看看发生了什么。如果磁盘 I/O 在那个时间点异常高，那么这可能是相关的，您可以从那里继续您的调查。另一方面，如果 I/O 正常，那么高利用率可能是由 MySQL 之外的另一个进程引起的，或者磁盘阵列中的一个磁盘正在重建，或者类似的情况。

使用table_io_waits_summary_by_table和table_io_waits_summary_by_index_usage表中的信息，您可以确定哪些表最常用于各种工作负载。例如，如果您有一个写操作特别繁忙的表，您可能想考虑将其表空间移动到一个更快的磁盘上。在做出这样的决定之前，您还应该考虑实际的文件 I/O。

文件输入输出

与刚才讨论的表 I/O 不同，文件 I/O 统计是针对 MySQL 使用的各种文件所涉及的实际磁盘 I/O 的。这是对表 I/O 信息的很好补充。

有三个性能模式表可用于获取 MySQL 实例的文件 I/O 信息:

events_waits_summary_global_by_event_name : 这是按事件名称分组的汇总表。通过查询以wait/io/file/开头的事件名称，您可以获得按 I/O 类型分组的 I/O 统计信息。例如，读写二进制日志文件导致的 I/O 使用单个事件(wait/io/file/sql/binlog)。注意，设置为wait/io/table/sql/handler的事件对应于刚刚讨论过的表 I/O；通过包含表 I/O，您可以很容易地比较花在文件 I/O 上的时间和花在表 I/O 上的时间
file_summary_by_event_name : 这类似于events_waits_summary_global_by_event_name表，但是只包括文件 I/O，并且事件分为读取、写入和其他。
file_summary_by_instance : 这是一个按实际文件分组的汇总表，事件分为读取、写入和杂项。例如，对于二进制日志，每个二进制日志文件有一行。

这三个表都很有用，您需要根据您要查找的信息在它们之间进行选择。例如，如果您想要文件类型的集合，events_waits_summary_global_by_event_name和file_summary_by_event_name表是更好的选择，而研究单个文件的 I/O，file_summary_by_instance表更有用。

file_summary_by_event_name和file_summary_by_instance表将事件分为读取、写入和其他事件。读和写很容易理解。杂项 I/O 是指所有不读写的东西。这包括但不限于创建、打开、关闭、删除、刷新和获取文件的元数据。所有杂项操作都不涉及数据传输，因此没有杂项字节计数器。

清单 19-6 显示了events_waits_summary_global_by_event_name表中可用数据的一个例子。该查询查找花费在 I/O 上的总时间最多的事件

mysql> SELECT ∗
         FROM performance_schema.events_waits_summary_global_by_event_name
        WHERE EVENT_NAME LIKE 'wait/io/file/%'
        ORDER BY SUM_TIMER_WAIT DESC
        LIMIT 1\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
    EVENT_NAME: wait/io/file/innodb/innodb_log_file
    COUNT_STAR: 58175
SUM_TIMER_WAIT: 20199487047180
MIN_TIMER_WAIT: 5341780
AVG_TIMER_WAIT: 347219260
MAX_TIMER_WAIT: 18754862132
1 row in set (0.0031 sec)

Listing 19-6The file I/O event spending the most time overall

这表明对于这个实例，最活跃的事件是 InnoDB 重做日志文件。这是一个非常典型的结果。每个事件都有相应的乐器。默认情况下，所有文件等待 I/O 事件都是启用的。一个特别有趣的事件是针对 InnoDB 表空间文件的 I/O 的wait/io/file/innodb/innodb_data_file。

events_waits_summary_global_by_event_name表的一个缺点是所有花费在 I/O 上的时间都被聚集到总计数器中，而不是读取和写入中。也只有计时可用。如果您使用file_summary_by_event_name表，您可以获得更多的细节。

清单 19-7 显示了在前面的例子中找到的 InnoDB 重做日志 I/O 事件的file_summary_by_event_name表的例子。

mysql> SELECT ∗
         FROM performance_schema.file_summary_by_event_name
        WHERE EVENT_NAME =
                  'wait/io/file/innodb/innodb_log_file'\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
               EVENT_NAME: wait/io/file/innodb/innodb_log_file
               COUNT_STAR: 58175
           SUM_TIMER_WAIT: 20199487047180
           MIN_TIMER_WAIT: 5341780
           AVG_TIMER_WAIT: 347219260
           MAX_TIMER_WAIT: 18754862132
               COUNT_READ: 8
           SUM_TIMER_READ: 778174704
           MIN_TIMER_READ: 5341780
           AVG_TIMER_READ: 97271660
           MAX_TIMER_READ: 409998080
 SUM_NUMBER_OF_BYTES_READ: 70656
              COUNT_WRITE: 33672
          SUM_TIMER_WRITE: 870804229376
          MIN_TIMER_WRITE: 7867956
          AVG_TIMER_WRITE: 25861264
          MAX_TIMER_WRITE: 14021439496
SUM_NUMBER_OF_BYTES_WRITE: 61617664
               COUNT_MISC: 24495
           SUM_TIMER_MISC: 19327904643100
           MIN_TIMER_MISC: 12479224
           AVG_TIMER_MISC: 789054776
           MAX_TIMER_MISC: 18754862132
1 row in set (0.0005 sec)

Listing 19-7The I/O statistics for the InnoDB redo log

请注意，当查询events_waits_summary_global_by_event_name表时，SUM_TIMER_WAIT和具有总聚集的其他列具有相同的值。(因为 I/O 经常在后台发生，所以即使在比较两个表之间不执行查询，情况也不会总是这样。)通过将 I/O 分为读取、写入和杂项，您可以更好地了解实例上的 I/O 工作负载。

如果您想要单个文件的统计数据，您需要使用file_summary_by_instance表。清单 19-8 显示了微软 Windows 上world.city表的表空间文件的一个例子。请注意，有四个反斜杠表示路径中的一个反斜杠。

mysql> SELECT ∗
         FROM performance_schema.file_summary_by_instance
        WHERE FILE_NAME LIKE '%\\\\world\\\\city.ibd'\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
                FILE_NAME: C:\ProgramData\MySQL\MySQL Server 8.0\Data\world\city.ibd
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
    OBJECT_INSTANCE_BEGIN: 1999746796608
               COUNT_STAR: 380
           SUM_TIMER_WAIT: 325377148780
           MIN_TIMER_WAIT: 12277372
           AVG_TIMER_WAIT: 856255472
           MAX_TIMER_WAIT: 10778110040
               COUNT_READ: 147
           SUM_TIMER_READ: 144057058960
           MIN_TIMER_READ: 85527220
           AVG_TIMER_READ: 979979712
           MAX_TIMER_READ: 7624205292
 SUM_NUMBER_OF_BYTES_READ: 2408448
              COUNT_WRITE: 125
          SUM_TIMER_WRITE: 21938183516
          MIN_TIMER_WRITE: 12277372
          AVG_TIMER_WRITE: 175505152
          MAX_TIMER_WRITE: 5113313440
SUM_NUMBER_OF_BYTES_WRITE: 2146304
               COUNT_MISC: 108
           SUM_TIMER_MISC: 159381906304
           MIN_TIMER_MISC: 160612960
           AVG_TIMER_MISC: 1475758128
           MAX_TIMER_MISC: 10778110040
1 row in set (0.0007 sec)

Listing 19-8The file I/O for the world.city tablespace file

您可以看到，事件名称表明它是一个 InnoDB 表空间文件，I/O 被分为读取、写入和杂项。对于读取和写入，还包括总字节数。

要考虑的最后一组性能模式表是错误汇总表。

错误汇总表

虽然错误与查询调优没有直接关系，但错误确实表明有问题。导致错误的查询仍然会使用资源，但是当错误发生时，这一切都是徒劳的。因此，错误会给系统增加不必要的负载，从而间接影响查询性能。还有一些与性能更直接相关的错误，比如由于未能获得锁而导致的错误。

性能模式中有五个表，对不同分组遇到的错误进行分组。这些桌子是

events_errors_summary_by_account_by_error
events_errors_summary_by_host_by_error
events_errors_summary_by_thread_by_error
events_errors_summary_by_user_by_error
events_errors_summary_global_by_error

表名是不言自明的。您可以使用这些表来确定是谁在执行触发错误的查询，并将其与语句事件表(例如events_statements_summary_by_digest)结合起来，以了解是谁触发了错误以及错误是针对哪些语句的。清单 19-9 显示了一个按账户分组查询死锁发生次数的例子。

mysql> SELECT ∗
         FROM performance_schema.events_errors_summary_by_account_by_error
        WHERE ERROR_NAME = 'ER_LOCK_DEADLOCK'\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
             USER: NULL
             HOST: NULL
     ERROR_NUMBER: 1213
       ERROR_NAME: ER_LOCK_DEADLOCK
        SQL_STATE: 40001
 SUM_ERROR_RAISED: 0
SUM_ERROR_HANDLED: 0
       FIRST_SEEN: NULL
        LAST_SEEN: NULL
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 2\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
             USER: root
             HOST: localhost
     ERROR_NUMBER: 1213
       ERROR_NAME: ER_LOCK_DEADLOCK
        SQL_STATE: 40001
 SUM_ERROR_RAISED: 2
SUM_ERROR_HANDLED: 0
       FIRST_SEEN: 2019-06-16 10:58:05
        LAST_SEEN: 2019-06-16 11:07:29
2 rows in set (0.0105 sec)

Listing 19-9Using the events_errors_summary_by_account_by_error table

这表明root@localhost帐户出现了两次死锁，但都没有得到处理。用户和主机为NULL的第一行代表后台线程。

Tip

你可以在 https://dev.mysql.com/doc/refman/en/server-error-reference.html 从 MySQL 参考手册中获得错误号和名称以及 SQL 状态。

性能模式的讨论到此结束。如果您觉得性能模式表太多了，尝试使用它们是一个好主意，例如，在一个空闲的测试系统上执行一些查询，这样您就知道会发生什么。另一个选择是使用sys模式，这使得基于性能模式的报告更容易开始。

sys 架构

sys模式的主要目标之一是简化基于性能模式的报告创建。这包括可用于寻找优化候选的报告。本节中讨论的所有报告都可以通过直接查询性能模式表来生成；然而,sys模式提供了可以随意使用的报告，并设置了格式，使人们更容易阅读数据。

本节中讨论的报告是使用性能模式表作为视图创建的，本章前面已经介绍了其中的大部分内容。根据视图是否可用于查找语句或什么使用 I/O，将视图分为不同的类别。本节的最后部分将展示如何使用statement_performance_analyzer()过程来查找在监控窗口期间执行的语句。

语句视图

语句视图使查询按主机或用户分组的语句变得简单，并且可以找到匹配某些条件的语句，例如它使用全表扫描。除非另有说明，视图使用events_statements_summary_by_digest性能模式表。表 19-4 中列出了可用的视图。

表 19-4

该语句视图

视角

描述

| | --- | --- | | host_summary_by_statement_latency | 这个视图使用events_statements_summary_by_host_by_event_name表为每个主机名返回一行，为后台线程返回一行。每一行都包含语句的高级统计信息，如总延迟、发送的行数等。这些行按总等待时间降序排列。 | | host_summary_by_statement_type | 这个视图使用与host_summary_by_statement_latency视图相同的性能模式表，但是除了主机名之外，它还包括语句类型。这些行首先按主机名升序排序，然后按总延迟降序排序。 | | innodb_lock_waits | 该视图显示正在进行的 InnoDB 行锁等待。它使用data_locks和data_lock_waits表格。该视图在第 22 章中用于调查锁定问题。 | | schema_table_lock_waits | 该视图显示正在进行的元数据和用户锁等待。它使用metadata_locks表。该视图在第 22 章中用于调查锁定问题。 | | session | 该视图返回一个基于threads和events_statements_current表的高级进程列表，以及来自其他性能模式表的一些附加信息。该视图包括活动连接的当前语句和空闲连接的最后执行的语句。根据进程列表时间和前一条语句的持续时间，以降序返回这些行。session的观点对于理解现在正在发生的事情特别有用。 | | statement_analysis | 该视图是按总延迟降序排序的events_statements_summary_by_digest表的格式化版本。 | | statements_with_errors_or_warnings | 该视图返回导致错误或警告的语句。这些行按错误数和警告数降序排列。 | | statements_with_full_table_scans | 该视图返回包含全表扫描的语句。这些行首先按未使用索引的次数百分比排序，然后按总延迟排序，两者都按降序排序。 | | statements_with_runtimes_in_95th_percentile | 该视图返回位于events_statements_summary_by_digest表中所有查询的第 95 个百分位数的语句。这些行按平均延迟降序排列。 | | statements_with_sorting | 该视图返回对结果中的行进行排序的语句。这些行按总等待时间降序排列。 | | statements_with_temp_tables | 该视图返回使用内部临时表的语句。这些行按磁盘上内部临时表和内存中内部临时表的数量降序排列。 | | user_summary_by_statement_latency | 这个视图类似于host_summary_by_statement_latency视图，只是它按用户名分组。该视图基于events_statements_summary_by_user_by_event_name表。 | | user_summary_by_statement_type | 该视图与user_summary_by_statement_latency视图相同，除了还包括语句类型。 |

查询视图和直接使用底层性能模式表之间的主要区别在于，您不需要添加过滤器，并且数据被格式化以使人们更容易阅读。这使得在调查性能问题时使用sys模式视图作为特别报告变得很容易。

Tip

请记住，这些视图也可以带有前缀x$，例如x$statement_analysis。如果您想要在格式化的列上添加额外的过滤器，改变排序，或者类似的事情，带有x$前缀的视图不会添加使它们变得更好的格式。

在清单 19-10 中可以看到一个使用视图的例子，其中statement_analysis视图用于查找自从性能模式表上次重置以来总体使用时间最多的语句。

mysql> SELECT ∗
         FROM sys.statement_analysis
        LIMIT 1\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
            query: UPDATE `world` . `city` SET `Population` = ? WHERE `ID` = ?
               db: world
        full_scan:
       exec_count: 3744
        err_count: 3
       warn_count: 0
    total_latency: 9.70 m
      max_latency: 51.53 s
      avg_latency: 155.46 ms
     lock_latency: 599.31 ms
        rows_sent: 0
    rows_sent_avg: 0
    rows_examined: 3741
rows_examined_avg: 1
    rows_affected: 3741
rows_affected_avg: 1
       tmp_tables: 0
  tmp_disk_tables: 0
      rows_sorted: 0
sort_merge_passes: 0
           digest: 8f3799ba6b1f47fc2d76f018eaafb6ef8a9d743a7dbe5e558e37371408a1ad5e
       first_seen: 2019-06-15 17:30:13.674383
        last_seen: 2019-06-15 17:52:42.881701
1 row in set (0.0028 sec)

Listing 19-10Finding the query using the most time executing

视图已经按总延迟降序排序，因此没有必要向查询添加任何排序。如果您回想一下本章前面使用events_statements_summary_by_digest性能模式表的示例，返回的信息是相似的，但是延迟更容易阅读，因为以皮秒为单位的值已经转换为 0 到 1000 之间的值。摘要也包括在内，因此如果需要，您可以使用它来查找关于该语句的更多信息。

其他视图也包含有用的信息。这是留给读者的一个练习，让他们查询系统上的视图并探索结果。

表 I/O 视图

表 I/O 的sys模式视图可用于查找关于表和索引使用情况的信息。这包括查找未使用的索引和执行全表扫描的表。

基于表 I/O 的视图都以schema_作为名称前缀。这些视图包括表 19-5 中汇总的视图。

表 19-5

表 I/O 视图

视角

描述

| | --- | --- | | schema_index_statistics | 该视图包括索引名不是NULL的table_io_waits_summary_by_index_usage表的所有行。这些行按总等待时间降序排列。该视图显示了每个索引用于选择、插入、更新和删除数据的量。 | | schema_table_statistics | 该视图结合了来自table_io_waits_summary_by_table和file_summary_by_instance表的数据，以返回表 I/O 和与表相关的文件 I/O。文件 I/O 统计信息只包括它们自己的表空间中的表。这些行按总表 I/O 延迟降序排列。 | | schema_table_statistics_with_buffer | 该视图与schema_table_statistics视图相同，除了它还包括来自innodb_buffer_page信息模式表的缓冲池使用信息。要知道查询innodb_buffer_page表会有很大的开销，最好用在测试系统上。 | | schema_tables_with_full_table_scans | 该视图在table_io_waits_summary_by_index_usage表中查询索引名为NULL的行，即没有使用索引的行，并包括读取计数大于 0 的行。在这些表中，有些行是在不使用索引的情况下读取的，即通过全表扫描。这些行按读取的总行数降序排列。 | | schema_unused_indexes | 这个视图也使用了table_io_waits_summary_by_index_usage表，但是包含了没有被索引读取的行，并且该索引不是主键或者唯一索引。mysql模式中的表被排除在外，因为您不应该更改任何这些表的定义。这些表根据模式和表名按字母顺序排序。 |

通常这些视图与其他视图和表格结合使用。例如，您可能会发现 CPU 使用率非常高。高 CPU 使用率的一个典型原因是大型表扫描，因此您可能会查看schema_tables_with_full_table_scans视图，发现一个或多个表通过表扫描返回了大量的行。然后继续查询statements_with_full_table_scans视图，查找使用该表而不使用索引的语句。

如前所述，schema_table_statistics视图结合了表 I/O 统计和文件 I/O 统计。也有纯粹看文件 I/O 的视图。

文件 I/O 视图

探索文件 I/O 使用情况的视图遵循与按主机名或用户名分组的语句视图相同的模式。一旦确定磁盘 I/O 是一个瓶颈，这些视图最适合用来确定是什么导致了 I/O。然后，您可以向后查找相关的表。从这里，您可以确定是否可以使用表来优化查询，或者是否需要增加 I/O 容量。

文件 I/O 包括表 19-6 中的视图。

表 19-6

文件 I/O 视图

视角

描述

| | --- | --- | | host_summary_by_file_io | 该视图使用events_waits_summary_by_host_by_event_name表，并按帐户主机名对文件 I/O 等待事件进行分组。这些行按总等待时间降序排列。 | | host_summary_by_file_io_type | 该视图与host_summary_by_file_io视图相同，只是它还包括文件 I/O 的事件名称。各行按主机名排序，然后按总延迟降序排序。 | | io_by_thread_by_latency | 该视图使用events_waits_summary_by_thread_by_event_name表返回按线程分组的文件 I/O 统计信息，其中的行按总延迟降序排列。线程包括后台线程，后台线程是导致大部分写 I/O 的线程。 | | io_global_by_file_by_bytes | 该视图使用file_summary_by_instance表返回每个文件的读写操作次数和 I/O 量(以字节为单位)。这些行按读取和写入 I/O 的总量(以字节为单位)降序排列。 | | io_global_by_file_by_latency | 该视图与io_global_by_file_by_bytes视图相同，只是它报告了 I/O 延迟。 | | io_global_by_wait_by_bytes | 该视图类似于io_global_by_file_by_bytes视图，除了它按 I/O 事件名称而不是文件名分组，并且它使用file_summary_by_event_name表。 | | io_global_by_wait_by_latency | 该视图与io_global_by_wait_by_bytes视图相同，只是它报告了 I/O 延迟。 | | user_summary_by_file_io | 该视图与host_summary_by_file_io视图相同，只是它使用了events_waits_summary_by_user_by_event_name表，并按用户名而不是主机名分组。 | | user_summary_by_file_io_type | 该视图与user_summary_by_file_io视图相同，只是它还包括文件 I/O 的事件名称。各行按用户名排序，然后按总延迟降序排序。 |

这些视图使用起来很简单，但是仍然值得看几个例子来展示与它们相关的一些细节。清单 19-11 显示了一个背景和前景线程的io_by_thread_by_latency视图的例子。基于测试系统上可用的线程来选择线程 id。

mysql> SELECT ∗
         FROM sys.io_by_thread_by_latency
        WHERE THREAD_ID IN (19, 87543)\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
          user: log_flusher_thread
         total: 24489
 total_latency: 19.33 s
   min_latency: 56.39 us
   avg_latency: 789.23 us
   max_latency: 18.75 ms
     thread_id: 19
processlist_id: NULL
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 2\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
          user: root@localhost
         total: 40683
 total_latency: 15.48 s
   min_latency: 5.27 us
   avg_latency: 353.57 us
   max_latency: 262.23 ms
     thread_id: 87543
processlist_id: 87542
2 rows in set (0.0066 sec)

Listing 19-11Example of using the io_by_thread_by_latency view

示例中需要注意的主要事情是用户名。在第 1 行中，有一个后台线程的例子，在这种情况下，线程名称的最后一部分(使用/作为分隔符)被用作用户名。在第 2 行中，它是一个前台线程，用户是帐户的用户名和主机名，它们之间有一个@符号。这些行还包括有关性能模式线程 id 和进程列表 id(连接 id)的信息，因此您可以使用它们来查找有关线程的更多信息。

另一个例子如清单 19-12 所示，用于io_global_by_file_by_bytes视图。

mysql> SELECT ∗
         FROM sys.io_global_by_file_by_bytes
        LIMIT 1\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
         file: @@datadir\undo_001
   count_read: 15889
   total_read: 248.31 MiB
     avg_read: 16.00 KiB
  count_write: 15149
total_written: 236.70 MiB
    avg_write: 16.00 KiB
        total: 485.02 MiB
    write_pct: 48.80
1 row in set (0.0028 sec)

Listing 19-12Example of using the io_global_by_file_by_bytes view

注意这里文件名的路径是如何使用@@datadir的。这是格式的一部分，sys模式使用它来使文件的位置一目了然。数据量也被缩放。

到目前为止已经讨论过的sys模式视图都报告了自从相应的性能模式表最后一次重置以来记录的统计数据。通常，性能问题只是间歇性地出现，在这种情况下，您需要确定在此期间发生了什么。这就是您需要语句性能分析器的地方。

语句性能分析器

语句性能分析器允许您获取events_statements_summary_by_digest表的两个快照，并在一个通常直接使用events_statements_summary_by_digest表的视图中使用这两个快照之间的增量。例如，这对于确定在高峰负载期间执行哪些查询非常有用。

使用statement_performance_analyzer()程序创建快照并执行分析。它需要三个参数，如表 19-7 所示。

表 19-7

语句性能分析器()过程的参数

争吵

有效值

描述

| | --- | --- | --- | | action | Snapshot``Overall``Delta``create_tmp``create_table``save``cleanup | 您希望过程执行的操作。稍后将更详细地讨论这些操作。 | | table | <schema>.<table> | 此参数用于需要表名的操作。格式必须是schema.table或表格名称本身。无论哪种情况，都不要使用反斜线。架构或表名中不允许有点。 | | views | with_runtimes_in_95th_percentile``analysis``with_errors_or_warnings``with_full_table_scans``with_sorting``with_temp_tables习俗 | 用于生成报告的视图名称。允许指定多个视图。除了自定义视图之外的所有视图都在使用sys模式中的一个语句视图。对于定制视图，定制视图的视图名称是使用statement_performance_analyzer.view sys模式配置选项指定的。 |

该操作指定您希望该过程执行的操作。在生成语句执行报告的工作流程的不同阶段使用不同的操作。支持的动作在表 19-8 中列出。

表 19-8

语句性能分析器()过程的操作

行动

描述

| | --- | --- | | snapshot | 这将创建一个events_statements_summary_by_digest表的快照，除非给出了table参数，在这种情况下，所提供的表的内容将被用作快照。快照存储在sys模式中一个名为tmp_digests的临时表中。 | | overall | 这将基于带有table参数的表中的内容创建一个报告。如果将 table 参数设置为NOW()，则摘要表的当前内容将用于创建新的快照。如果将表参数设置为NULL，将使用当前快照。 | | delta | 这将使用带有table参数的表和现有快照，基于两个快照之间的差异创建一个报告。这个操作创建了一个临时表sys.tmp_digests_delta。本节稍后将展示此操作的一个示例。 | | create_table | 用table参数给出的名称创建一个常规用户表。该表可用于存储使用save动作的快照。 | | create_tmp | 用参数table给出的名称创建一个临时表。该表可用于存储使用save动作的快照。 | | save | 将现有快照保存到由table参数指定的表中。 | | cleanup | 删除已用于快照和增量计算的临时表。使用create_table和create_tmp动作创建的表格不会被删除。 |

该过程对于创建两个快照并计算它们之间的增量特别有用。执行增量分析的工作流程如下:

创建一个临时表来存储初始快照。这是通过使用create_tmp动作来完成的。
使用snapshot动作创建初始快照。
使用save动作将第 1 步中的初始快照保存到临时表中。
等待应该收集数据的持续时间。
使用snapshot动作创建新的快照。
使用带有一个或多个视图的delta动作来生成报告。
使用cleanup动作进行清理。

在您知道哪些查询已经执行的受控环境中尝试该过程会很有用。这样，您就知道生成的输出中会出现什么。该示例将使用一个名为monitor的模式来存储初始快照:

mysql> CREATE SCHEMA monitor;

当在第二个连接中进行监控时，您将需要执行一些查询。我们鼓励您尝试一些自己的查询。如果您想重现示例中的输出，您可以使用 MySQL Shell 并开始(在开始监控之前)将语言模式更改为 Python，并将默认模式设置为world:

\py
\use world

清单 19-13 中显示了将执行该示例的九个查询的 Python 代码。您可以在 MySQL Shell 中执行代码。该代码也可以从本书的 GitHub 库中的文件listing_19_13.py中获得。

queries = [
    ("SELECT * FROM `city` WHERE `ID` = ?", [130, 3805]),
    ("SELECT * FROM `city` WHERE `CountryCode` = ?", ['AUS', 'CHN', 'IND']),
    ("SELECT * FROM `country` WHERE CODE = ?", ['DEU', 'GBR', 'BRA', 'USA']),
]

for query in queries:
    sql = query[0]
    parameters = query[1]
    for param in parameters:
        result = session.run_sql(sql, (param,))

Listing 19-13Python code for example queries for statement analysis

具有占位符的查询被定义为元组的列表，该列表具有用于该查询的值，作为元组中的第二个元素。如果您想执行更多的查询，这允许您快速添加更多的查询和值。查询在查询和参数的双循环中执行。当您将代码粘贴到 MySQL Shell 中时，用两行新代码来结束它，告诉 MySQL Shell 多行代码块已经完成。

清单 19-14 展示了在两个快照之间创建一个大约一分钟的报告的例子。该示例使用基于报告的sys.statement_analysis的analysis视图。由于这本书的页数限制，不允许报告被很好地显示，步骤和报告的完整输出可以在这本书的 GitHub 库的listing_19_14_statement_analysis.txt文件中找到。报告中查询的顺序可能会有所不同，因为这取决于执行查询所需的时间，并且统计数据也会有所不同。

mysql> CALL sys.ps_setup_disable_thread(CONNECTION_ID());
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.0012 sec)

Query OK, 0 rows affected (0.0012 sec)

mysql> CALL sys.statement_performance_analyzer(
              'create_tmp', 'monitor._tmp_ini', NULL);
Query OK, 0 rows affected (0.0028 sec)

mysql> CALL sys.statement_performance_analyzer(
              'snapshot', NULL, NULL);
Query OK, 0 rows affected (0.0065 sec)

mysql> CALL sys.statement_performance_analyzer(
              'save', 'monitor._tmp_ini', NULL);
Query OK, 0 rows affected (0.0017 sec)

-- Execute your queries or the Python code in Listing 19-13
-- in a second connection while the SLEEP(60) is executing.
mysql> DO SLEEP(60);
Query OK, 0 rows affected (1 min 0.0064 sec)

mysql> CALL sys.statement_performance_analyzer(
              'snapshot', NULL, NULL);
Query OK, 0 rows affected (0.0041 sec)

mysql> CALL sys.statement_performance_analyzer(
              'delta', 'monitor._tmp_ini',
              'analysis');
+------------------------------------------+
| Next Output                              |
+------------------------------------------+
| Top 100 Queries Ordered by Total Latency |
+------------------------------------------+
1 row in set (0.0049 sec)

+----------------------------------------------+-------+...
| query                                        | db    |...
+----------------------------------------------+-------+...
| SELECT * FROM `city` WHERE `CountryCode` = ? | world |...
| SELECT * FROM `country` WHERE CODE = ?       | world |...
| SELECT * FROM `city` WHERE `ID` = ?          | world |...
+----------------------------------------------+-------+...
3 rows in set (0.0049 sec)

Query OK, 0 rows affected (0.0049 sec)

mysql> CALL sys.statement_performance_analyzer(
              'cleanup', NULL, NULL);
Query OK, 0 rows affected (0.0018 sec)

mysql> DROP TEMPORARY TABLE monitor._tmp_ini;
Query OK, 0 rows affected (0.0007 sec)

mysql> CALL sys.ps_setup_enable_thread(CONNECTION_ID());
+------------------+
| summary          |

+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.0015 sec)

Query OK, 0 rows affected (0.0015 sec)

Listing 19-14Using the statement_performance_analyzer() procedure

在示例的开头和结尾使用ps_setup_disable_thread()和ps_setup_enable_thread()过程是为了禁用执行分析的线程的性能模式检测，然后在分析完成时启用检测。通过禁用检测，分析执行的查询不会包含在报告中。这在一个繁忙的系统上并不重要，但是在只有几个查询的测试中非常有用。

对于分析本身，会创建一个临时表，以便创建快照并保存到其中。之后，收集数据一分钟，然后创建新的快照，并生成报告。最后的步骤是清理用于分析的临时表。注意，临时表monitor._tmp_ini并没有被cleanup动作清理，因为它是由create_tmp动作显式创建的。

报告输出显示在监控期间执行了三条语句。在现实世界中，通常会有更多的查询，默认情况下，报告仅限于前 100 个查询。您可以配置报告中可以包含多少个查询以及一些其他设置。这是通过使用支持以下设置的sys模式配置机制来完成的:

debug : 当选项设置为ON时，产生调试输出。默认是OFF。
statement_performance_analyzer.limit : 报表中包含的最大报表数。默认值为 100。
statement_performance_analyzer.view :与custom视图一起使用的视图。

Tip

sys模式选项既可以在sys.sys_config表中设置，也可以通过在选项名称前添加@sys.作为用户变量。比如debug变成了@sys.debug。

到目前为止，一直假设通过显式地对模式视图执行查询来直接使用sys模式视图。但是这并不是你使用它们的唯一方式；这些视图也可以通过 MySQL Workbench 获得。

MySQL 工作台

如果您更喜欢使用图形用户界面而不是命令行界面，MySQL Workbench 是很好的选择。MySQL Workbench 不仅允许您执行自己的查询；它还附带了几个特性来帮助您管理和监控实例。出于讨论的目的，主要关注的是性能报告和客户端连接报告。

这两个报告都可以通过 MySQL Workbench 窗口左侧的导航器来访问。一旦连接到 MySQL，导航器就可用了。图 19-1 突出显示了这些报告。

img/484666_1_En_19_Fig1_HTML.jpg

图 19-1

访问客户端连接和性能报告

本节的其余部分将更详细地讨论这两种类型的报告。

绩效报告

MySQL Workbench 中的性能报告是研究实例中发生的事情的一个很好的方式。因为性能报告是基于sys模式视图的，所以可用的信息将与通过sys模式视图时讨论的信息相同。

通过连接到您想要调查的实例并从导航器的 PERFORMANCE 部分选择 Performance Reports ，您可以获得性能报告。您可以访问大多数报告，这些报告也可以使用sys模式直接生成。图 19-2 显示了如何选择您感兴趣的报告。

img/484666_1_En_19_Fig2_HTML.jpg

图 19-2

选择性能报告

报表示例如图 19-3 所示，其中已经执行了报表统计报表。这与您使用sys.statement_analysis视图得到的报告相同。在本书的 GitHub 存储库中的文件figure_19_3_performance_report.png中可以看到包含所有列的报告示例。

img/484666_1_En_19_Fig3_HTML.jpg

图 19-3

报表统计性能报告

性能报告的一个优点是它们使用无格式的视图定义，因此您可以使用 GUI 更改排序。通过单击要作为排序依据的列的列标题，可以更改排序。每次单击列标题时，顺序都会在升序和降序之间切换。

在报告的底部，有一些按钮可以帮助您使用报告。导出… 按钮允许您将报告结果保存为 CSV 文件。复制选中的按钮将标题和选中的行以 CSV 格式复制到内存中。复制查询按钮复制用于报告的查询。这允许您编辑查询并手动执行它。对于图 19-3 中的报表，返回的查询是select ∗ from sys.x$statement_analysis``。最后一个按钮是右侧的刷新按钮，再次执行报告。

没有基于sys.session视图的性能报告。相反，您需要使用客户端连接报告。

客户端连接报告

如果您想要获得当前连接到实例的连接列表，您需要使用客户端连接报告。它不像sys.session视图包含那么多信息，但是它包含了最基本的数据。该报告基于性能模式中的threads表，此外，如果可能的话，还包括程序名。

图 19-4 显示了报告最左边栏的例子。要查看专栏的完整列表，请查看该书的 GitHub 资源库中的文件figure_19_4_client_connections.png。

img/484666_1_En_19_Fig4_HTML.jpg

图 19-4

客户端连接报告

如果您已经打开了客户端连接报告或其中一个性能报告，您可以重新使用该连接来获取客户端连接报告。如果所有的连接都用完了，并且您需要得到一个关于连接正在做什么的报告，那么这将是非常有用的。客户端连接报告还允许您通过选择查询并使用报告右下角的一个终止按钮来终止查询或连接。

虽然 MySQL Workbench 对于调查性能问题非常有用，但它主要是针对特定的调查。为了进行适当的监控，您需要一个完整的监控解决方案。

MySQL 企业监控器

当您需要调查性能问题时，无论您是对用户投诉做出反应还是主动寻求改进，都没有什么可以替代功能齐全的监控解决方案。本节将基于 MySQL 企业监控器(MEM)进行讨论。其他监控解决方案可能提供类似的功能。

本节将讨论三个特性。第一个是查询分析器，然后是时间序列图，最后是临时报告，如进程和锁等待报告。在调查问题时，您应该结合使用各种指标。例如，如果您有一个高磁盘 I/O 使用率的报告，那么可以找到显示磁盘 I/O 的时间序列图，并确定 I/O 是如何以及何时发展的。然后，您可以使用查询分析器来调查在此期间执行了哪些查询。如果问题仍然存在，可以使用诸如进程报告或其他临时报告之类的报告来查看发生了什么。

查询分析器

当您需要调查性能问题时，MySQL Enterprise Monitor 中的查询分析器是最重要的地方之一。MySQL Enterprise Monitor 使用性能模式中的events_statements_summary_by_digest表定期收集已经执行的查询。然后，它比较连续的输出，以确定自上次数据收集以来的统计数据。这与您在使用sys模式中的语句性能分析器的示例中看到的类似，只是这是自动发生的，并且与收集的其余数据集成在一起。

通过选择左侧菜单中的查询选项，进入查询分析器，如图 19-5 所示。

img/484666_1_En_19_Fig5_HTML.jpg

图 19-5

访问查询分析器

打开查询分析器后，默认情况下，查询响应时间索引(QRTi)图位于顶部，查询列表位于下方。默认时间范围是过去的一个小时。您可以选择显示另一个图形或更改图形的数量。带有查询响应时间索引的默认图表值得考虑。

查询响应时间索引是衡量单个查询或一组查询性能的指标。它是使用 Apdex(应用性能索引)公式计算的。 ¹

**最优:**当查询的执行时间少于为定义最优性能而设置的阈值时。默认阈值为 100 ms，该阈值可以配置。绿色用于最佳时间范围。
**可接受:**当查询的执行时间超过最佳时间范围的阈值，但小于阈值的四倍时。这个框架使用黄色。
**不可接受:**当查询比最优阈值的阈值慢四倍时。这个框架使用红色。

查询响应时间索引并不能完美地衡量实例的执行情况，但是对于各种查询的响应时间间隔大致相同的系统，它可以很好地反映系统或查询在不同时间的执行情况。如果您混合了非常快速的 OLTP 查询和慢速的 OLAP 查询，那么这不是一个很好的性能指标。

如果您在图中发现了一些有趣的东西，您可以选择该时间段，并将其用作过滤查询的新时间范围。图形右上角还有配置视图按钮，可用于设置图形和查询的时间范围、显示哪些图形、查询的过滤器等等。

查询列表是您需要用来查看实际查询的。查询示例如图 19-6 所示。

img/484666_1_En_19_Fig6_HTML.jpg

图 19-6

查询分析器中查询的概述

这些信息是高层次的，旨在帮助您缩小在给定时间段内需要仔细查看的候选查询的范围。在这个例子中，您可以看到按名称查找城市的查询已经执行了将近 160，000 次。您应该问的第一个问题是，执行这个查询的次数是否合理。这可能是意料之中的，但是高执行计数也可能是 runway 进程不断重复执行相同查询的标志，或者是您需要为查询实现缓存的标志。您还可以从绿色的圆环图中看到，所有执行都处于查询响应时间索引的最佳时间范围内。

查询区域右上角的图标，就在三个垂直点的左边，显示 MySQL Enterprise Monitor 已经标记了这个查询。要了解图标的含义，请将鼠标悬停在图标上。本例中的图标表示查询正在进行全表扫描。因此，尽管查询响应时间索引对于查询来说看起来不错，但还是值得更仔细地研究一下查询。是否可以接受进行全表扫描取决于几个因素，例如表中的行数和查询的执行频率。您还可以看到，查询延迟图在图的右端显示延迟增加，表明性能正在下降。

如果您想要更详细地调查某个查询，请单击查询区域右上角的三个垂直点，这将允许您转到该查询的详细信息屏幕。图 19-7 显示了一个查询细节的例子。该书的 GitHub 知识库中的文件figure_19_7_mem_query_details.png中提供了全尺寸截图。

img/484666_1_En_19_Fig7_HTML.jpg

图 19-7

来自查询分析器的查询详细信息

详细信息包括性能模式摘要中可用的指标。在这里，您可以看到检查的行数确实比返回的行数多得多，因此值得进一步研究是否需要索引。这些图表展示了查询执行随时间的发展。

底部是实际查询执行延迟的示例。在这种情况下，包括两个执行。第一个是图表左边的红圈。第二个是右下角的蓝绿色标记。颜色象征每次执行的查询响应时间索引。此图仅在events_statements_history_long消费者启用时可用。

查询分析器非常适合研究查询，但是要获得活动的更高层次的摘要，您需要使用时间序列图。

时间序列图表

当谈到监控系统时，人们通常会想到时间序列图。它们对于理解系统的整体负载和发现随时间的变化非常重要。然而，他们通常不善于找到问题的根本原因。为此，您需要分析查询或生成临时报告来查看问题的发生。

当您查看时间序列图时，您需要考虑一些事情；否则，你可能会得出错误的结论，并在没有问题的时候宣布进入紧急状态。首先，您需要知道图中的指标意味着什么，就像本章前面讨论的 I/O 延迟意味着什么一样。其次，请记住，指标的变化本身并不意味着有问题。这只是意味着活动发生了变化。如果您开始执行更多的查询，因为您进入了一天或一年中的高峰期，数据库活动增加是很自然的事情，反之亦然。类似地，如果您实现了一个新特性，比如在应用的开始屏幕上添加一个元素，那么预计也会增加所执行的工作量。第三，注意不要只考虑单一的图形。如果你只看监测数据而不考虑其他数据，很容易得出错误的结论。

如果你看一下图 19-8 ，这里有一个数据库和系统利用率变化的一段时间内的几个时间序列图的例子。

img/484666_1_En_19_Fig8_HTML.jpg

图 19-8

时间序列图表

如果您查看这些图表，可以看到最上面的图表中的 CPU 利用率突然增加，并在 80%以上达到峰值。为什么会这样，这是一件坏事吗？数据库查询图显示每秒钟的语句数同时增加，InnoDB 行细节图中读取的行数也增加。所以 CPU 使用率很可能是由查询活动增加引起的。从那里，您可以转到查询分析器并调查哪些查询正在运行。

可以从图表中去掉几个其他的点。如果你看一下 x 轴，这个图表只覆盖了六分钟的数据。注意不要在很短的时间内得出结论，因为这可能不代表系统的真实状态。另外就是记得看数据的尺度。是的，CPU 使用率和 InnoDB 事务锁内存会突然增加，但这是从 0 开始的。系统有多少个 CPU？如果您有 96 个 CPU，那么使用一个 CPU 的 80%真的不算什么，但是如果您在一个单 CPU 虚拟机上，那么您的扩展空间就更少了。对于事务锁内存，如果将 y 轴考虑在内，您可以看到“峰值”仅为锁内存的 1 KiB——因此不必担心。

有时您需要调查正在发生的问题，在这种情况下，时间序列图和查询分析器可能无法提供您需要的信息。在这种情况下，您需要特别报告。

临时报告

MySQL Enterprise Monitor 中提供了几个特别报告。其他监控解决方案可能有类似的或其他的报告。这些报告类似于本章前面讨论的sys模式报告中的信息。通过监控解决方案访问即席报告的一个优点是，如果应用使用了所有可用的连接，您可以重用这些连接，并且它提供了一个图形用户界面来操作报告。

这些报告包括获取进程列表、锁信息、模式统计信息等等的能力。每个视图相当于一个sys模式视图。在编写本报告时，存在以下报告:

**表统计:**该报告根据总延迟、提取的行数、更新的行数等显示每个表的使用量。它相当于schema_table_statistics视图。
**用户统计:**该报告显示每个用户名的活动。它相当于user_summary视图。
**内存使用:**该报告显示每种内存类型的内存使用情况。它相当于memory_global_by_current_bytes视图。
数据库文件 I/O: 该报告显示磁盘 I/O 使用情况。报告有三个选项:按文件分组，相当于io_global_by_file_by_latency视图；按等待(I/O)类型分组，相当于io_global_by_wait_by_latency视图；按线程分组，相当于io_by_thread_by_latency视图。按等待类型分组增加了与 I/O 相关的时间序列图。
**InnoDB 缓冲池:**该报告显示了哪些数据存储在 InnoDB 缓冲池中。它基于innodb_buffer_page信息模式表。由于查询该报告的信息会有很大的开销，所以建议只在测试系统上使用该报告。
**进程:**该报告显示了 MySQL 中当前存在的前台和后台线程。它使用与session视图相同的sys.processlist视图，除了它还包括后台线程。
**锁等待:**该报告有两个选项。您可以获得 InnoDB 锁等待(innodb_lock_waits视图)或元数据锁(schema_table_lock_waits视图)的报告。

使用报告的原理是相同的，因此只显示两个示例。第一个在图 19-9 中，InnoDB 锁等待情况显示在锁等待报告中。

img/484666_1_En_19_Fig9_HTML.jpg

图 19-9

InnoDB 行锁等待报告

报告以分页模式显示行，您可以通过单击列标题来更改排序。更改顺序不会重新加载数据。如果需要重新加载数据，使用截图顶部的重新加载按钮。

您还可以操作报告中可用的列。右上角有一个按钮，用于选择您希望在报告中显示的列。图 19-10 中的截图显示了一个如何选择要显示的列的例子。

img/484666_1_En_19_Fig10_HTML.jpg

图 19-10

选择要包含在报告中的列

当您切换是否包括列时，报告会立即更新，而无需重新加载报告。这意味着对于像锁等待这样的间歇性问题，您可以在不丢失正在查看的数据的情况下操作报告。如果通过拖动列标题来更改列的顺序，情况也是如此。

有几个报告可以在标准的基于列的输出和树形视图之间进行选择。对于 InnoDB 缓冲池报告，treemap 视图是唯一支持的格式。treemap 输出使用面积基于值的矩形，因此如果一个矩形的面积是另一个矩形的两倍，这意味着值也是两倍大。这有助于可视化数据。

图 19-11 显示了数据库中表的总插入延迟的树形视图示例。在这个例子中，只有三个表具有足够大的总插入延迟部分来绘制矩形。

img/484666_1_En_19_Fig11_HTML.jpg

图 19-11

总插入延迟的树形视图

当您查看 treemap 视图时，您可以立即看到将数据插入到city表所花费的时间比其他表要多得多。

即席查询都处理报告执行时的状态。另一方面，查询分析器和时间序列图处理过去发生的事情。另一个显示过去发生了什么的工具是慢速查询日志。

慢速查询日志

慢速查询日志是一个可靠的旧工具，用于查找性能不佳的查询和调查 MySQL 中过去的问题。在性能模式有如此多的选项来查询速度慢、不使用索引或满足其他标准的语句的今天，这似乎是不必要的。然而，慢速查询日志有一个主要优点，那就是它是持久化的，所以即使在 MySQL 重新启动后，您也可以回过头来使用它。

Tip

默认情况下，不启用慢速查询日志。您可以使用slow_query_log选项启用和禁用它。该日志还可以在不重启 MySQL 的情况下动态启用和禁用。

使用慢速查询日志有两种基本模式。如果您知道问题是何时发生的，那么您可以检查日志中当时的慢速查询。一种情况是，由于锁问题，查询堆积如山，而您知道问题何时结束。然后，您可以在日志中找到该时间，并查找第一个执行时间足够长的查询，这是堆积问题的一部分；该查询很可能导致与在该时间点前后完成的其他一些查询的堆积。

另一种使用模式是使用mysqldumpslow实用程序来创建慢速查询的集合。这使查询正常化，类似于性能模式所做的，因此相似的查询将聚集它们的统计信息。这种模式非常适合查找可能导致系统繁忙的查询。

您可以使用-s选项选择聚合查询的排序依据。您可以使用总计数(c排序值)来查找执行次数最多的查询。查询执行得越频繁，优化查询就越有好处。您可以以类似的方式使用总执行时间(t)。如果用户抱怨响应时间慢，平均执行时间(at)对排序很有用。如果您怀疑某些查询因为缺少筛选条件而返回过多的行，您可以根据它们返回的行数对查询进行排序(r表示总行数，ar表示平均行数)。通常，将排序选项与-r选项结合起来以颠倒顺序，而将-t选项结合起来以只包含前 N 个查询是很有用的。这样，就可以更容易地关注影响最大的查询。

您还需要记住，默认情况下，慢速查询日志不会记录所有查询，因此您无法像使用性能模式那样深入了解工作负载。您需要通过更改long_query_time配置选项来调整认为查询缓慢的阈值。可以为会话更改该选项，因此，如果预期执行时间有显著变化，可以设置全局值以匹配大多数查询，并为执行偏离正常查询的连接更改每个会话的值。如果您需要调查涉及 DDL 语句的问题，您需要确保启用了log_slow_admin_statements选项。

Caution

慢速查询日志的开销比性能模式大。当只记录几个缓慢的查询时，开销通常可以忽略不计，但是如果记录许多查询，开销可能会很大。不要通过将long_query_time设置为 0 来记录所有查询，除非是在测试系统上或在一段短时间内。

您分析mysqldumpslow报告的方式与分析性能模式和sys模式的方式非常相似，因此它将留给读者一个练习，让读者从您的系统中生成报告，并使用它们来查找候选查询以进行进一步优化。

摘要

本章探讨了可用于查找要优化的候选查询的资源。还讨论了如何寻找资源利用率，以了解在什么时间有工作负载将系统推向极限。最需要关注的是当时正在运行的查询，尽管您应该注意那些做了过多工作的查询。

讨论从性能模式开始，并考虑了哪些信息是可用的以及如何使用它。特别是在寻找可能有性能问题的查询时，events_statements_summary_by_digest表是一座金矿。然而，你不应该仅仅局限于查询。您还应该考虑表和文件 I/O，以及查询是否会导致错误。这些错误可能包括锁等待超时和死锁。

sys模式提供了一系列现成的报告，可以用来查找信息。这些报告基于性能模式，但它们包括过滤器、排序和格式，使报告易于使用，特别是在调查问题时作为临时报告。还展示了如何使用语句性能分析器来创建在感兴趣的时间段内运行的查询的报告。

MySQL Workbench 提供了基于sys模式视图的性能报告和基于性能模式中的threads表的客户端连接报告。这些功能允许您通过图形用户界面制作临时报告，从而可以轻松地更改数据的排序和导航报告。

监控是保持系统良好运行和调查性能问题的最重要工具之一。MySQL Enterprise Monitor 被用作监控讨论的基础。特别是查询分析器功能对于确定哪些查询对系统影响最大非常有用，但它应该与时间序列图结合使用，以了解系统的整体状态。您还可以创建即席查询，例如，用于调查正在发生的问题。

最后，您不应该忘记慢速查询日志，它比性能模式语句表有优势，因为它保存了慢速查询的记录。这使得调查重启前发生的问题成为可能。慢速查询日志还记录查询完成的时间，这在用户报告系统在某个时间很慢时很有用。

当您找到一个想要进一步研究的查询时，您会怎么做？第一步是分析它，这将在下一章讨论。

Footnotes [1](#Fn1_source)

https://en.wikipedia.org/wiki/Apdex