MySQL8-查询性能调优教程-二-MySQL8 查询性能调优教程（二）六、sys模式这个模式是马克·利斯的创意，他

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

原文：MySQL 8 Query Performance Tuning

协议：CC BY-NC-SA 4.0

六、`sys`模式

这个模式是马克·利斯的创意，他也是 MySQL 企业监控器开发团队的一员。他启动了ps_helper项目来试验监控思想，并展示性能模式能够做什么，同时使它变得更简单。该项目后来被重命名为sys模式，并被转移到 MySQL 中。从那以后，包括本书作者在内的其他几个人也做出了贡献。

sys模式适用于 MySQL Server 5.6 和更高版本。在 MySQL 5.7 中，它成为标准安装的一部分，因此您不需要做任何事情来安装或升级sys模式。从 MySQL 8.0.18 开始，sys模式源代码是 MySQL 服务器源代码的一部分。

本书通篇使用了sys模式来分析查询、锁等等。本章将给出sys模式的高级概述，包括如何配置它、格式化功能、视图如何工作以及各种助手例程。

Tip

sys模式源代码( https://github.com/mysql/mysql-server/tree/8.0/scripts/sys_schema 和旧的 MySQL 版本 https://github.com/mysql/mysql-sys/ )也是学习如何针对性能模式编写查询的有用资源。

sys 模式配置

sys模式使用自己的配置系统，因为它最初是独立于 MySQL 服务器实现的。有两种方法可以更改配置，具体取决于您是要永久更改设置还是仅针对会话更改设置。

持久化配置存储在sys_config表中，该表包括变量名、变量值、最后一次设置值的时间和用户。清单 6-1 显示了默认内容(set_time将取决于sys模式最后一次安装或升级的时间)。

mysql> SELECT * FROM sys.sys_config\G
*************************** 1\. row ***************************
variable: diagnostics.allow_i_s_tables
   value: OFF
set_time: 2019-07-13 19:19:29
  set_by: NULL
*************************** 2\. row ***************************
variable: diagnostics.include_raw
   value: OFF
set_time: 2019-07-13 19:19:29
  set_by: NULL
*************************** 3\. row ***************************
variable: ps_thread_trx_info.max_length
   value: 65535
set_time: 2019-07-13 19:19:29
  set_by: NULL
*************************** 4\. row ***************************
variable: statement_performance_analyzer.limit
   value: 100
set_time: 2019-07-13 19:19:29
  set_by: NULL
*************************** 5\. row ***************************
variable: statement_performance_analyzer.view
   value: NULL
set_time: 2019-07-13 19:19:29
  set_by: NULL
*************************** 6\. row ***************************
variable: statement_truncate_len
   value: 64
set_time: 2019-07-13 19:19:29
  set_by: NULL
6 rows in set (0.0005 sec)

Listing 6-1The sys schema persisted configuration

目前，set_by列总是为NULL，除非@sys.ignore_sys_config_triggers用户变量被设置为评估为FALSE但不是NULL的值。

您最有可能更改的选项是statement_truncate_len，它指定了sys模式将用于格式化视图中的语句的最大长度(稍后将详细介绍)。选择默认值 64 是为了增加查询视图适合控制台宽度的可能性；但是，有时候太少了，无法获得足够有用的语句信息。

您可以通过更新sys_config中的值来更新配置设置。这将保持更改并立即应用于所有连接，除非它们已经设置了自己的会话值(当使用格式化语句的sys模式中的某个东西时，这将隐式发生)。由于sys_config是一个普通的 InnoDB 表，重启 MySQL 后这个变化仍然存在。

或者，您可以仅针对该会话更改设置。这是通过获取配置变量的名称，加上sys.并将其转换为用户变量来实现的。清单 6-2 展示了使用sys_config表和一个用户变量来改变statement_truncate_len配置的例子。用format_statement()函数测试结果，这个函数是sys模式用来截断语句的。

mysql> SET @query = 'SELECT * FROM world.city INNER JOIN world.city ON country.Code = city.CountryCode';
Query OK, 0 rows affected (0.0003 sec)

mysql> SELECT sys.sys_get_config(
                  'statement_truncate_len',
                  NULL
              ) AS TruncateLen\G
*************************** 1\. row ***************************
TruncateLen: 64
1 row in set (0.0007 sec)

mysql> SELECT sys.format_statement(@query) AS Statement\G
*************************** 1\. row ***************************
Statement: SELECT * FROM world.city INNER ... ountry.Code = city.CountryCode
1 row in set (0.0019 sec)

mysql> UPDATE sys.sys_config SET value = 48 WHERE variable = 'statement_truncate_len';
Query OK, 1 row affected (0.4966 sec)

mysql> SET @sys.statement_truncate_len = NULL;
Query OK, 0 rows affected (0.0004 sec)

mysql> SELECT sys.format_statement(@query) AS Statement\G
*************************** 1\. row ***************************
Statement: SELECT * FROM world.ci ... ode = city.CountryCode
1 row in set (0.0009 sec)

mysql> SET @sys.statement_truncate_len = 96;
Query OK, 0 rows affected (0.0003 sec)

mysql> SELECT sys.format_statement(@query) AS Statement\G
*************************** 1\. row ***************************
Statement: SELECT * FROM world.city INNER JOIN world.city ON country.Code = city.CountryCode
1 row in set (0.0266 sec)

Listing 6-2Changing the sys schema configuration

首先，在用户变量@query中设置一个查询。这纯粹是为了方便，所以很容易一直引用同一个查询。sys_get_config()函数用于获取statement_truncate_len选项的当前配置值。这考虑了是否设置了@sys.statement_trauncate_len用户变量。如果所提供的选项不存在，第二个参数提供要返回的值。

format_statement()函数用于演示格式化@query中的语句，首先用默认值 64 表示statement_truncate_len，然后将sys_config更新为值 48，最后将会话的值设置为 96。注意在更新了sys_config表之后，用户变量@sys.statement_truncate_len是如何被设置为NULL的，以使 MySQL 将更新后的设置应用到会话中。

Note

一些默认情况下不在sys_config表中的sys模式特性支持一些配置选项，例如调试选项。sys模式对象( https://dev.mysql.com/doc/refman/en/sys-schema-reference.html )的文档包括支持哪些配置选项的信息。

format_statement()函数不是sys模式中唯一的格式化函数，所以让我们看看所有的函数。

格式化功能

sys模式包括四个函数，帮助您根据性能模式格式化查询的输出，使结果更容易阅读或占用更少的空间。在 MySQL 8.0.16 中，有两个函数已被弃用，因为添加了本地性能模式函数来替代它们。

表 6-1 总结了四个函数以及在format_time()和format_bytes()的情况下取代它们的新的本地函数。

表 6-1

sys模式格式化功能

系统模式功能

本地功能

描述

| | --- | --- | --- | | format_bytes() | FORMAT_BYTES() | 将字节值转换为带单位的字符串(基于 1024)。 | | format_path() | | 获取文件的路径，并用表示相应全局变量的字符串替换数据目录、临时目录等。 | | format_statement() | | 通过用省略号(...)替换语句的中间部分，将语句截断到最多由statement_truncate_len配置选项设置的字符数。 | | format_time() | FORMAT_PICO_TIME() | 将皮秒时间转换为人类可读的字符串。 |

清单 6-3 显示了一个使用格式化函数的例子，对于format_bytes()和format_time()，结果将与本地性能模式函数进行比较。

mysql> SELECT sys.format_bytes(5000) AS SysBytes,
              FORMAT_BYTES(5000) AS P_SBytes\G
*************************** 1\. row ***************************
SysBytes: 4.88 KiB
P_SBytes: 4.88 KiB
1 row in set, 1 warning (0.0015 sec)
Note (code 1585): This function 'format_bytes' has the same name as a native function

mysql> SELECT @@global.datadir AS DataDir,
              sys.format_path(
                  'D:\\MySQL\\Data_8.0.18\\ib_logfile0'
              ) AS LogFile0\G
*************************** 1\. row ***************************
 DataDir: D:\MySQL\Data_8.0.18\
LogFile0: @@datadir\ib_logfile0
1 row in set (0.0027 sec)

mysql> SELECT sys.format_statement(
                  'SELECT * FROM world.city INNER JOIN world.city ON country.Code = city.CountryCode'
              ) AS Statement\G
*************************** 1\. row ***************************
Statement: SELECT * FROM world.city INNER ... ountry.Code = city.CountryCode
1 row in set (0.0016 sec)

mysql> SELECT sys.format_time(123456789012) AS SysTime,
              FORMAT_PICO_TIME(123456789012) AS P_STime\G
*************************** 1\. row ***************************
SysTime: 123.46 ms
P_STime: 123.46 ms

1 row in set (0.0006 sec)

Listing 6-3Using the formatting functions

请注意，sys.format_bytes()的使用触发了一个警告(但仅在连接第一次使用它时),因为sys模式函数名与本机函数名相同。format_path()函数在 Microsoft Windows 上要求路径名使用反斜杠，在其他平台上使用正斜杠。format_statement()函数的结果假设statement_truncate_len选项的值已经被重置为默认值 64。

Tip

虽然format_time()和format_bytes()的sys模式实现仍然存在，但是最好使用新的本地函数，因为sys模式实现可能会在未来版本中被删除，并且本地函数会更快。

这些函数不仅本身有用，它们还被sys模式用来实现返回格式化数据的视图。因为在某些情况下需要处理未格式化的数据，所以大多数sys模式视图有两种实现，您将在下面看到。

风景

sys模式提供了许多作为预定义报告的视图。视图大多使用性能模式表，但也有一些使用信息模式。这些视图既可以方便地从性能模式中获取信息，也可以作为查询性能模式的示例。

因为视图是现成的报告，您可以作为数据库管理员或开发者使用，所以它们是用默认顺序定义的。这意味着使用视图的典型方式是做一个普通的SELECT * FROM <view name>，例如:

mysql> SELECT *
         FROM sys.schema_tables_with_full_table_scans\G
*************************** 1\. row ***************************
    object_schema: world
      object_name: city
rows_full_scanned: 4079
          latency: 269.13 ms
*************************** 2\. row ***************************
    object_schema: sys
      object_name: sys_config
rows_full_scanned: 18
          latency: 328.80 ms
2 rows in set (0.0021 sec)

结果取决于全表扫描使用了哪些表。请注意延迟是如何被格式化的，如使用FORMAT_PICO_TIME()或sys.format_time()函数。

大多数sys模式视图以两种形式存在，一种是格式化的语句、路径、字节值和计时，另一种返回原始数据。如果您在控制台查询视图并自己查看数据，格式化视图非常有用，而如果您需要处理程序中的数据或想要更改默认排序，非格式化视图会更好。MySQL Workbench 中的性能报告使用无格式视图，因此您可以在用户界面中更改排序。

您可以从名称中区分格式化视图和未格式化视图。如果一个视图包含格式，也会有一个相同名称的未格式化视图，但是名称前面会加上x$。例如，对于前面例子中使用的schema_tables_with_full_table_scans视图，无格式视图被命名为x$schema_tables_with_full_table_scans:

mysql> SELECT *
         FROM sys.x$schema_tables_with_full_table_scans\G
*************************** 1\. row ***************************
    object_schema: world
      object_name: city
rows_full_scanned: 4079
          latency: 269131954854
*************************** 2\. row ***************************
    object_schema: sys
      object_name: sys_config
rows_full_scanned: 18
          latency: 328804286013
2 rows in set (0.0017 sec)

sys模式的最后一个主题是所提供的助手函数和过程。

助手函数和过程

sys模式提供了几个实用程序，可以帮助您使用 MySQL。这些功能包括执行动态创建的查询、操作列表等。表 6-2 总结了最重要的助手功能和程序。

表 6-2

sys模式中的帮助函数和过程

例行程序名

常规类型

描述

| | --- | --- | --- | | extract_schema_from_file_name | 功能 | 从 InnoDB 表空间文件的每表文件路径中提取模式名。 | | extract_table_from_file_name | 功能 | 从每个表的 InnoDB 表空间文件的路径中提取表名。 | | list_add | 功能 | 将元素添加到列表中，除非它已经存在于列表中。例如，如果您需要更改 SQL 模式，这很有用。 | | list_drop | 功能 | 从列表中移除元素。 | | quote_identifier | 功能 | 用反斜杠()将标识符(例如，表名)括起来。 | | version_major| 功能 | 返回您正在查询的实例的主要版本。例如，对于 8.0.18，它返回 8。 | |version_minor| 功能 | 返回正在查询的实例的次要版本。例如，对于 8.0.18，它返回 0。 | |version_patch| 功能 | 返回您正在查询的实例的补丁程序发布版本。例如，对于 8.0.18，它返回 18。 | |execute_prepared_stmt| 程序 | 执行以字符串形式给出的查询。使用预处理语句执行查询，过程在执行完成后释放预处理语句。 | |table_exists` | 程序 | 返回表是否存在，如果存在，则返回它是基表、临时表还是视图。 |

这些实用程序中有几个也在sys模式中内部使用。这些例程最常见的用途是在需要动态处理数据和查询的存储程序中。

Tip

sys 模式函数和过程以例程注释的形式提供了内置帮助。您可以通过查询information_schema.ROUTINES视图的ROUTINE_COMMENT列获得帮助。

摘要

本章提供了对sys模式的简要介绍，因此当您在后面的章节中看到示例时，您会知道它是什么以及如何使用它。sys模式是一个有用的补充，它提供了现成的报告和实用程序，可以简化您的日常任务和调查。在 MySQL 5.7 和更高版本中，sys模式是一个系统模式，所以您不需要采取任何措施就可以开始使用它。

首先，讨论了sys模式配置。全局配置存储在sys.sys_config表中，如果您喜欢不同于 MySQL 安装时提供的默认值，可以更新该表。您还可以通过设置一个用户变量，在配置选项名称前加上sys.来更改会话的配置选项。

然后，sys模式格式化函数包含了添加本地性能模式函数来替代sys模式函数的情况。一些视图中还使用了格式化功能，以帮助人们更容易地阅读数据。对于使用格式化功能的视图，也有一个相应的无格式视图，名称前带有x$。

最后，讨论了几个辅助函数和过程。当您尝试动态工作时，例如执行存储过程中生成的查询时，这些功能会对您有所帮助。

下一章是关于信息模式的。

七、信息模式

当您需要优化查询时，通常需要关于模式、索引等的信息。在这种情况下，信息模式是一个很好的数据资源。本章介绍了信息模式以及它包含的视图的概述。在本书的其余部分，信息模式被多次使用。

什么是信息模式？

信息模式是几个关系数据库通用的模式，包括 MySQL，它是在 MySQL 5.0 中添加的。MySQL 基本上遵循 SQL:2003 标准的 F021 基本信息模式，进行了必要的修改以反映 MySQL 的独特特性，并添加了不属于该标准的其他视图。

Note

信息模式是虚拟的，因为其中没有存储任何数据。因此，本章将所有视图和表格都称为视图，即使SHOW CREATE TABLE将其显示为常规表格。这也符合将所有对象的表格类型设置为SYSTEM VIEW的information_schema.TABLES视图。

在 MySQL 5.5 中引入性能模式后，目标是通过信息模式使相对静态的数据(如模式信息)和属于性能模式的更不稳定的数据可用。也就是说，并不总是很清楚什么属于哪里，例如，索引统计相对不稳定，但也是模式信息的一部分。还有一些信息，比如 InnoDB 指标，由于历史原因，仍然存在于信息模式中。

因此，您可以将信息模式视为描述 MySQL 实例的数据集合。在带有关系数据字典的 MySQL 8 中，一些视图是底层数据字典表上的简单 SQL 视图。这意味着 MySQL 8 中许多信息模式查询的性能将大大优于您在旧版本中所体验到的。当查询不需要从存储引擎检索信息的架构数据时，尤其如此。

Caution

如果您仍在使用 MySQL 5.7 或更早的版本，那么在查询信息模式中的视图(如TABLES和COLUMNS视图)时要小心。如果它们包含数据的表还不在表定义缓存中，或者缓存不够大，无法容纳所有的表，那么它们可能需要很长时间。MySQL 服务器团队在博客中讨论了 MySQL 5.7 和 8 之间信息模式的性能差异的一个例子: https://mysqlserverteam.com/mysql-8-0-scaling-and-performance-of-information_schema/ 。

特权

信息模式是一个虚拟数据库，对视图的访问与其他表略有不同。所有用户都将看到information_schema模式存在，并且他们将看到所有视图。但是，查询视图的结果取决于分配给帐户的权限。例如，一个除了全局USAGE权限之外没有其他权限的帐户在查询information_schema.TABLES视图时将只能看到信息模式视图。

有些视图需要额外的权限，在这种情况下，会返回一个ER_SPECIFIC_ACCESS_DENIED_ERROR(错误号 1227)错误，并描述缺少哪个权限。例如，INNODB_METRICS视图需要PROCESS权限，因此如果没有PROCESS权限的用户查询该视图，就会出现以下错误:

mysql> SELECT *
         FROM information_schema.INNODB_METRICS;
ERROR: 1227: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

现在，是时候看看在信息模式视图中可以找到什么样的信息了。

视图

信息模式中可用的数据范围从关于系统的高级信息到低级 InnoDB 指标。本节提供了这些视图的概述，但不会详细介绍，因为从性能调优的角度来看，最重要的视图将在后面章节的相关部分中讨论。

Note

一些插件将自己的视图添加到信息模式中。这里不考虑额外的插件视图。

系统信息

信息模式中最高级别的信息涉及整个 MySQL 实例。这包括诸如哪些字符集可用以及安装了哪些插件之类的信息。

表 7-1 总结了包含系统信息的视图。

表 7-1

包含系统信息的信息架构视图

视图名称

描述

| | --- | --- | | CHARACTER_SETS | 可用的字符集。 | | COLLATIONS | 每个字符集可用的排序规则。这包括排序规则的 id，它在某些情况下(例如，在二进制日志中)用于唯一地指定排序规则和字符集。 | | COLLATION_CHARACTER_SET_APPLICABILITY | 排序规则到字符集的映射(与COLLATIONS的前两列相同)。 | | ENGINES | 已知的存储引擎以及它们是否已加载。 | | INNODB_FT_DEFAULT_STOPWORD | 在 InnoDB 表上创建全文索引时使用的默认停用词列表。 | | KEYWORDS | MySQL 中的关键字列表以及该关键字是否被保留。 | | PLUGINS | MySQL 已知的插件，包括状态。 | | RESOURCE_GROUPS | 线程用来完成其工作的资源组。资源组指定线程的优先级以及它可以使用的 CPU。 | | ST_SPATIAL_REFERENCE_SYSTEMS | 空间参考系统列表，包括包含用于指定空间列参考系统的 id 的SRS_ID列。 |

与系统相关的视图主要作为参考视图，RESOURCE_GROUPS表略有不同，因为可以添加资源组，这将在第 17 章中讨论。

例如，KEYWORDS视图在测试升级时非常有用，因为您可以使用它来验证您的模式、表、列、例程或参数名称是否与新版本中的关键字匹配。如果是这种情况，您将需要更新应用以引用标识符，如果还没有这样做的话。要查找与关键字匹配的所有列名:

SELECT TABLE_SCHEMA, TABLE_NAME,
       COLUMN_NAME, RESERVED
  FROM information_schema.COLUMNS
       INNER JOIN information_schema.KEYWORDS
          ON KEYWORDS.WORD = COLUMNS.COLUMN_NAME
 WHERE TABLE_SCHEMA NOT IN ('mysql',
                            'information_schema',
                            'performance_schema',
                            'sys'
                           )
 ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

该查询使用COLUMNS视图来查找除系统模式之外的所有列名(如果您在应用或脚本中使用它们，您可以选择包含它们)。COLUMNS视图是描述模式对象的几个视图之一。

模式信息

包含模式对象信息的视图是信息模式中最有用的视图。这些也是几个SHOW语句的来源。您可以使用视图来查找从存储例程的参数到数据库名称的所有信息。表 7-2 总结了包含模式信息的视图。

表 7-2

具有架构信息的信息架构视图

视图名称

描述

| | --- | --- | | CHECK_CONSTRAINTS | 该视图包含关于CHECK约束的信息，在 MySQL 8.0.16 和更高版本中可用。 | | COLUMN_STATISTICS | 直方图的定义，包括统计数据。对于查询性能调优来说，这是一个非常有用的视图。 | | COLUMNS | 列定义。 | | EVENTS | 存储事件的定义。 | | FILES | 关于 InnoDB 表空间文件的信息。 | | INNODB_COLUMNS | InnoDB 表中列的元数据信息。 | | INNODB_DATAFILES | 该视图将 InnoDB 表空间 id 链接到文件系统路径。 | | INNODB_FIELDS | InnoDB 索引中包含的列的元数据。 | | INNODB_FOREIGN | InnoDB 外键的元数据。 | | INNODB_FOREIGN_COLS | 列出 InnoDB 外键的子列和父列。 | | INNODB_FT_BEING_DELETED | 在针对在innodb_ft_aux_table选项中指定的 InnoDB 表的OPTIMIZE TABLE语句期间，INNODB_FT_DELETED视图的快照。 | | INNODB_FT_CONFIG | 在innodb_ft_aux_table选项中指定的 InnoDB 表上全文索引的配置信息。 | | INNODB_FT_DELETED | 从在innodb_ft_aux_table选项中指定的 InnoDB 表的全文索引中删除的行。出于性能原因，InnoDB 使用这个额外的列表来避免为每个 DML 语句更新索引本身。 | | INNODB_FT_INDEX_CACHE | 在innodb_ft_aux_table选项中指定的 InnoDB 表的全文索引中新插入的行。出于性能原因，InnoDB 使用这个额外的列表来避免为每个 DML 语句更新索引本身。 | | INNODB_FT_INDEX_TABLE | 在innodb_ft_aux_table选项中指定的 InnoDB 表的反向全文索引。 | | INNODB_INDEXES | 关于 InnoDB 表上的索引的信息。这包括内部信息，如根页面的页码和合并阈值。 | | INNODB_TABLES | InnoDB 表的元数据。 | | INNODB_TABLESPACES | InnoDB 表空间的元数据。 | | INNODB_TABLESPACES_BRIEF | 该视图将来自INNODB_TABLESPACES的SPACE、NAME、FLAG和SPACE_TYPE列与来自INNODB_DATAFILES的PATH列组合在一起，以提供 InnoDB 表空间的摘要。 | | INNODB_TABLESTATS | InnoDB 表的表统计信息。其中一些统计数据与索引统计数据同时更新；其他的是持续维护的。 | | INNODB_TEMP_TABLE_INFO | InnoDB 临时表的元数据(内部的和显式的)。 | | INNODB_VIRTUAL | InnoDB 表上虚拟生成的列的内部元数据信息。 | | KEY_COLUMN_USAGE | 关于主键、唯一键和外键的信息。 | | PARAMETERS | 关于存储函数和存储过程的参数的信息。 | | PARTITIONS | 关于表分区的信息。 | | REFERENTIAL_CONSTRAINTS | 关于外键的信息。 | | ROUTINES | 存储函数和存储过程的定义。 | | SCHEMATA | 关于模式(数据库)的信息。(从技术上讲，Schemata 是模式复数形式的正确词汇，但现在大多数人都使用模式。) | | ST_GEOMETRY_COLUMNS | 关于具有空间数据类型的列的信息。 | | STATISTICS | 索引定义和统计。谈到查询性能调整，这是最有用的视图之一。 | | TABLE_CONSTRAINTS | 主键、唯一键、外键和CHECK约束的概要。 | | TABLES | 关于表和视图及其属性的信息。 | | TABLESPACES | 此视图仅用于 NDB 集群表空间。 | | TRIGGERS | 触发器定义。 | | VIEW_ROUTINE_USAGE | 列出视图中使用的存储函数。该表是在 8.0.13 中添加的。 | | VIEW_TABLE_USAGE | 列出视图引用的表。该视图是在 8.0.13 中添加的。 | | VIEWS | 视图定义。 |

有几个视图是密切相关的，例如，列在模式中的表中，约束引用表和列。这意味着一些列名出现在几个视图中。与这些视图相关的最常用的列名是

TABLE_NAME : 用于不特定于 InnoDB 的视图中的表名。
TABLE_SCHEMA : 用于不特定于 InnoDB 的视图中的模式名。
COLUMN_NAME : 用于不特定于 InnoDB 的视图中的列名。
SPACE : 用于 InnoDB 特定视图中的表空间 id。
TABLE_ID : 用于特定于 InnoDB 的视图中，以唯一地标识表格。这也在 InnoDB 内部使用。
NAME : 特定于 InnoDB 的视图使用一个名为NAME的列来给出对象的名称，而不考虑对象的类型。

除了使用此列表中的名称之外，还有一些示例对这些列名进行了细微的修改，如在视图 KEY_COLUMN_USAGE 中，您可以找到外键描述中使用的列 REFERENCED_TABLE_SCHEMA、REFERENCED_TABLE_NAME 和 REFERENCED_COLUMN_NAME。例如，如果要使用 KEY_COLUMN_USAGE 视图来查找外键引用 sakila.film 表的表，可以使用如下查询:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME
    FROM information_schema.KEY_COLUMN_USAGE

    WHERE REFERENCED_TABLE_SCHEMA = 'sakila'
        AND REFERENCED_TABLE_NAME = 'film';
+--------------+---------------+
| TABLE_SCHEMA | TABLE_NAME    |
+--------------+---------------+
| sakila       | film_actor    |
| sakila       | film_category |
| sakila       | inventory     |
+--------------+---------------+
3 rows in set (0.0078 sec)

这表明 film_actor、film_category 和 inventory 表都有外键，其中 film 表是父表。例如，如果您查看 film_actor 的表定义:

mysql> SHOW CREATE TABLE sakila.film_actor\G
*************************** 1\. row ***************************
       Table: film_actor
Create Table: CREATE TABLE `film_actor` (
  `actor_id` smallint(5) unsigned NOT NULL,
  `film_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`),
  CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.0097 sec)

fk_film_actor_film约束引用胶片表中的film_id列。您可以将此作为查找外键完整链的起点，方法是针对KEY_COLUMN_USAGE视图对查询中返回的每个表手动执行查询，或者创建一个递归公用表表达式(CTE)。这是留给读者的一个练习。

Tip

关于在递归公用表表达式中使用KEY_COLUMN_USAGE视图查找外键依赖链的示例，请参见 https://mysql.wisborg.dk/tracking-foreign-keys 。

为了完整起见，通过外键依赖于film表的表的可视化表示可以在图 7-1 中找到。

img/484666_1_En_7_Fig1_HTML.jpg

图 7-1

来自sakila.film的外部密钥链的可视化表示

图表是使用 MySQL Workbench 的逆向工程特性创建的。

包含特定于 InnoDB 的信息的视图使用SPACE和TABLE_ID来标识表空间和表。每个表空间都有一个唯一的 id，其范围是为不同的表空间类型保留的。例如，数据字典表空间文件(<datadir>/mysql.ibd)的空间 id 为 4294967294，临时表空间的 id 为 4294967293，还原日志表空间从 4294967279 开始并递减，用户表空间从 1 开始。

包含 InnoDB 全文索引信息的视图很特殊，因为它们要求您用您想要获取信息的表的名称来设置innodb_ft_aux_table全局变量。例如，要获得sakila.film_text表的全文索引配置:

mysql> SET GLOBAL innodb_ft_aux_table = 'sakila/film_text';
Query OK, 0 rows affected (0.0685 sec)

mysql> SELECT *
         FROM information_schema.INNODB_FT_CONFIG;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 1002  |
| stopword_table_name       |       |
| use_stopword              | 1     |
+---------------------------+-------+
4 rows in set (0.0009 sec)

INNODB_FT_CONFIG视图中的值可能因您而异。

InnoDB 还包括带有与性能相关的信息的视图。这些将与其他一些与性能相关的表一起讨论。

性能信息

与性能相关的一组视图是您在性能调优工作中可能使用最多的视图，以及前一组视图中的COLUMN_STATISTICS和STATISTICS视图。带有性能相关信息的视图列于表 7-3 中。

表 7-3

具有性能相关信息的信息架构视图

视图名称

描述

| | --- | --- | | INNODB_BUFFER_PAGE | InnoDB 缓冲池中的页面列表，可用于确定当前缓存了哪些表和索引。**警告:**查询这个表的开销很大，特别是对于大型缓冲池和许多表和索引。它最适用于测试系统。 | | INNODB_BUFFER_PAGE_LRU | 关于 InnoDB 缓冲池中页面的信息，以及它们在最近最少使用(LRU)列表中的排序方式。**警告:**查询这个表的开销很大，特别是对于大型缓冲池和许多表和索引。它最适用于测试系统。 | | INNODB_BUFFER_POOL_STATS | 关于 InnoDB 缓冲池使用情况的统计信息。该信息类似于在BUFFER POOL AND MEMORY部分的SHOW ENGINE INNODB STATUS输出中可以找到的信息。这是最有用的观点之一。 | | INNODB_CACHED_INDEXES | 每个索引在 InnoDB 缓冲池中缓存的索引页数的摘要。 | | INNODB_CMP``INNODB_CMP_RESET | 与压缩的 InnoDB 表相关的操作的统计信息。 | | INNODB_CMP_PER_INDEX``INNODB_CMP_PER_INDEX_RESET | 与INNODB_CMP相同，但按索引分组。 | | INNODB_CMPMEM``INNODB_CMPMEM_RESET | 关于 InnoDB 缓冲池中压缩页面的统计信息。 | | INNODB_METRICS | 类似于全局状态变量，但特定于 InnoDB。 | | INNODB_SESSION_TEMP_TABLESPACES | 元数据包括 InnoDB 临时表空间文件的连接 id、文件路径和大小(在 MySQL 8.0.13 和更高版本中，每个会话都有自己的文件)。它可以用来将一个会话链接到一个表空间文件，如果您注意到一个文件变大了，这将非常有用。该视图是在 8.0.13 中添加的。 | | INNODB_TRX | 关于 InnoDB 事务的信息。 | | OPTIMIZER_TRACE | 当启用优化器跟踪时，可以从该视图中查询跟踪。 | | PROCESSLIST | 同SHOW PROCESSLIST。 | | PROFILING | 启用性能分析时，可以从该视图中查询性能分析统计信息。这已被否决，建议改用性能架构。 |

对于包含 InnoDB 压缩表信息的视图，以_RESET为后缀的表以增量形式返回自上次查询视图以来的操作和计时统计信息。

INNODB_METRICS视图包括类似于全局状态变量但特定于 InnoDB 的指标。度量被分组到子系统中(SUBSYSTEM列)，对于每个度量，在COMMENT列中有一个度量测量什么的描述。您可以使用全局系统变量启用、禁用和重置指标:

innodb_monitor_disable : 禁用一个或多个度量。
innodb_monitor_enable : 启用一个或多个指标。
innodb_monitor_reset : 重置一个或多个指标的计数器。
innodb_monitor_reset_all : 重置所有统计信息，包括一个或多个度量的计数器、最小值和最大值。

可以根据需要打开和关闭指标，当前状态在STATUS列中。您将指标的名称指定为innodb_monitor_enable或innodb_monitor_disable变量的值，并且可以使用%作为通配符。值all作为一个特殊值来影响所有指标。清单 7-1 展示了一个启用和使用所有匹配%cpu%的指标的例子(恰好是cpu子系统中的指标)。计数器值取决于查询时的工作负载。

mysql> SET GLOBAL innodb_monitor_enable = '%cpu%';
Query OK, 0 rows affected (0.0005 sec)

mysql> SELECT NAME, COUNT, MIN_COUNT,
              MAX_COUNT, AVG_COUNT,
              STATUS, COMMENT
         FROM information_schema.INNODB_METRICS
        WHERE NAME LIKE '%cpu%'\G
*************************** 1\. row ***************************
     NAME: module_cpu
    COUNT: 0
MIN_COUNT: NULL
MAX_COUNT: NULL
AVG_COUNT: 0
   STATUS: enabled
  COMMENT: CPU counters reflecting current usage of CPU
*************************** 2\. row ***************************
     NAME: cpu_utime_abs
    COUNT: 51
MIN_COUNT: 0
MAX_COUNT: 51
AVG_COUNT: 0.4358974358974359
   STATUS: enabled
  COMMENT: Total CPU user time spent
*************************** 3\. row ***************************
     NAME: cpu_stime_abs
    COUNT: 7
MIN_COUNT: 0
MAX_COUNT: 7
AVG_COUNT: 0.05982905982905983
   STATUS: enabled
  COMMENT: Total CPU system time spent
*************************** 4\. row ***************************
     NAME: cpu_utime_pct
    COUNT: 6
MIN_COUNT: 0
MAX_COUNT: 6
AVG_COUNT: 0.05128205128205128
   STATUS: enabled
  COMMENT: Relative CPU user time spent
*************************** 5\. row ***************************
     NAME: cpu_stime_pct

    COUNT: 0
MIN_COUNT: 0
MAX_COUNT: 0
AVG_COUNT: 0
   STATUS: enabled
  COMMENT: Relative CPU system time spent
*************************** 6\. row ***************************
     NAME: cpu_n
    COUNT: 8
MIN_COUNT: 8
MAX_COUNT: 8
AVG_COUNT: 0.06837606837606838
   STATUS: enabled
  COMMENT: Number of cpus
6 rows in set (0.0011 sec)

mysql> SET GLOBAL innodb_monitor_disable = '%cpu%';
Query OK, 0 rows affected (0.0004 sec)

Listing 7-1Using the INNODB_METRICS view

首先，使用innodb_monitor_enable变量启用指标；然后检索这些值。除了显示的值，还有一组带_RESET后缀的列，当您设置innodb_monitor_reset(仅计数器)或innodb_monitor_reset_all系统变量时，这些列会被重置。最后，指标再次被禁用。

Caution

这些指标有不同的开销，因此建议您在生产中启用指标之前先测试您的工作负载。

InnoDB 指标也包含在sys.metrics视图中，还有全局状态变量和一些其他指标，以及检索指标的时间。

其余的信息模式视图包含关于特权的信息。

特权信息

MySQL 使用分配给帐户的特权来确定哪些帐户可以访问哪些模式、表和列。确定给定帐户特权的常用方法是使用SHOW GRANTS语句，但是信息模式还包括允许您查询特权的视图。

表 7-4 总结了信息模式权限视图。视图按照从全局权限到列权限的顺序排列。

表 7-4

具有特权信息的信息模式表

表名

描述

| | --- | --- | | USER_PRIVILEGES | 全球特权。 | | SCHEMA_PRIVILEGES | 访问模式的权限。 | | TABLE_PRIVILEGES | 访问表的特权。 | | COLUMN_PRIVILEGES | 访问列的权限。 |

在所有视图中，帐户都被称为GRANTEE，其形式为'username'@'hostname'，引号始终存在。清单 7-2 展示了一个检索mysql.sys@localhost账户的特权并将其与SHOW GRANTS语句的输出进行比较的例子。

mysql> SHOW GRANTS FOR 'mysql.sys'@'localhost'\G
*************************** 1\. row ***************************
Grants for mysql.sys@localhost: GRANT USAGE ON *.* TO `mysql.sys`@`localhost`
*************************** 2\. row ***************************
Grants for mysql.sys@localhost: GRANT TRIGGER ON `sys`.* TO `mysql.sys`@`localhost`
*************************** 3\. row ***************************
Grants for mysql.sys@localhost: GRANT SELECT ON `sys`.`sys_config` TO `mysql.sys`@`localhost`
3 rows in set (0.2837 sec)

mysql> SELECT *
         FROM information_schema.USER_PRIVILEGES
        WHERE GRANTEE = '''mysql.sys''@''localhost'''\G
*************************** 1\. row ***************************
       GRANTEE: 'mysql.sys'@'localhost'
 TABLE_CATALOG: def
PRIVILEGE_TYPE: USAGE
  IS_GRANTABLE: NO
1 row in set (0.0006 sec)

mysql> SELECT *
         FROM information_schema.SCHEMA_PRIVILEGES
        WHERE GRANTEE = '''mysql.sys''@''localhost'''\G
*************************** 1\. row ***************************
       GRANTEE: 'mysql.sys'@'localhost'
 TABLE_CATALOG: def
  TABLE_SCHEMA: sys
PRIVILEGE_TYPE: TRIGGER
  IS_GRANTABLE: NO
1 row in set (0.0005 sec)

mysql> SELECT *
         FROM information_schema.TABLE_PRIVILEGES
        WHERE GRANTEE = '''mysql.sys''@''localhost'''\G
*************************** 1\. row ***************************
       GRANTEE: 'mysql.sys'@'localhost'
 TABLE_CATALOG: def
  TABLE_SCHEMA: sys
    TABLE_NAME: sys_config
PRIVILEGE_TYPE: SELECT
  IS_GRANTABLE: NO
1 row in set (0.0005 sec)

mysql> SELECT *
         FROM information_schema.COLUMN_PRIVILEGES
        WHERE GRANTEE = '''mysql.sys''@''localhost'''\G
Empty set (0.0005 sec)

Listing 7-2Using the Information Schema privilege views

注意用户名和主机名的单引号是如何被双引号转义的。

虽然带有权限信息的视图不能直接用于性能调优，但是它们对于维护稳定的系统非常有用，因为您可以使用它们来轻松识别是否有任何帐户拥有不需要的权限。

Tip

最好的做法是限制帐户只拥有他们需要的特权，不要更多。这是保证系统安全的步骤之一。

关于信息模式要考虑的最后一个主题是如何缓存与索引统计相关的数据。

索引统计数据的缓存

理解索引统计相关视图(以及等效的SHOW语句)中的信息来自哪里是很重要的。大部分数据来自 MySQL 数据字典。在 MySQL 8 中，数据字典存储在 InnoDB 表中，因此视图只是数据字典之上的普通 SQL 视图。(例如，您可以尝试执行SHOW CREATE VIEW information_schema.STATISTICS来获得STATISTICS视图的定义。)

然而，索引统计信息本身仍然来自存储引擎层，因此查询这些信息的成本相对较高。为了提高性能，统计数据被缓存在数据字典中。您可以控制在 MySQL 刷新缓存之前允许统计数据存在多长时间。这是通过默认为 86400 秒(一天)的information_schema_stats_expiry变量完成的。如果将该值设置为 0，您将始终从存储引擎获得最新的可用值；这相当于 MySQL 5.7 的行为。该变量既可以在全局范围内设置，也可以在会话范围内设置，因此，如果您正在调查一个需要查看当前统计信息的问题，例如，如果优化程序没有使用您期望的索引，您可以将该变量设置为 0。

Tip

使用information_schema_stats_expiry变量来控制索引统计信息可以在数据字典中缓存多长时间。这只是为了显示，优化器总是使用最新的统计数据。例如，将information_schema_stats_expiry设置为 0 以禁用缓存，这在调查优化器使用错误索引的问题时非常有用。您可以根据需要在全局和会话范围内更改该值。

缓存会影响表 7-5 中列出的列。显示相同数据的SHOW语句也会受到影响。

表 7-5

受information_schema_stats_expiry影响的列

视图名称

列名

描述

| | --- | --- | --- | | STATISTICS | CARDINALITY | 同一行中的列的索引部分的唯一值的估计数。 | | TABLES | AUTO_INCREMENT | 表的自动递增计数器的下一个值。 | | AVG_ROW_LENGTH | 估计的数据长度除以估计的行数。 | | CHECKSUM | 表校验和。InnoDB 不使用它，所以值是NULL。 | | CHECK_TIME | 上次检查表格的时间(CHECK TABLE)。对于分区表，InnoDB 总是返回NULL。 | | CREATE_TIME | 创建表的时间。 | | DATA_FREE | 该表所属的表空间中空闲空间量的估计值。对于 InnoDB，这是完全自由的扩展区的大小减去安全余量。 | | DATA_LENGTH | 行数据的估计大小。对于 InnoDB，它是聚集索引的大小，即聚集索引中的页数乘以页面大小。 | | INDEX_LENGTH | 辅助索引的估计大小。对于 InnoDB，这是非聚集索引中的页面总数乘以页面大小。 | | MAX_DATA_LENGTH | 数据长度的最大允许大小。InnoDB 不使用它，所以值是NULL。 | | TABLE_ROWS | 估计的行数。对于 InnoDB 表，这来自主键或聚集索引的基数。 | | UPDATE_TIME | 上次更新表空间文件的时间。对于 InnoDB 系统表空间中的表，该值为NULL。由于数据是异步写入表空间的，因此时间通常不会反映最后一条更改数据的语句的时间。 |

您可以通过对表执行ANALYZE TABLE来强制更新给定表的数据。

有时候，查询数据不会更新缓存的数据:

当缓存数据尚未过期时，也就是说，它在不到information_schema_stats_expiry秒之前被刷新
当information_schema_stats_expiry设置为 0 时
当 MySQL 或 InnoDB 以只读模式运行时，即当read_only、super_read_only、transaction_read_only或innodb_read_only模式之一被启用时。
当查询还包括来自性能模式的数据时

摘要

本章介绍了信息模式，首先讨论了什么是信息模式以及用户特权是如何工作的。本章的剩余部分介绍了标准视图和缓存的工作原理。信息模式视图可以根据它们包含的信息类型进行分组:系统、模式、性能和特权信息。

系统信息包括字符集和排序规则、资源组、关键字以及与空间数据相关的信息。这是使用参考手册的一种有用的替代方法。

模式信息是最大的一组视图，包括从模式数据到列、索引和约束的所有可用信息。这些视图以及包含度量和 InnoDB 缓冲池统计信息等信息的性能视图是性能调优中最常用的视图。与权限相关的视图不经常用于性能调优，但是它们对于帮助维护稳定的系统非常有用。

从信息模式视图中获取信息的一个常见快捷方式是使用一个SHOW语句。这些将在下一章讨论。

八、`SHOW`语句

对于数据库管理员来说,SHOW语句是 MySQL 中用来获取关于模式对象和系统上发生的事情的信息的老一套工具。虽然今天大多数信息都可以在信息模式或性能模式中找到，但是由于其简短的语法，SHOW命令仍然非常流行于交互使用。

Tip

建议查询基础信息模式视图和性能模式表。这尤其适用于对数据的非交互式访问。查询底层数据源也更加强大，因为它允许您连接到其他视图和表。

本章首先概述了SHOW语句如何与信息模式视图和性能模式表相匹配。本章的剩余部分涵盖了在信息模式和性能模式中没有视图或表的SHOW语句，包括通过SHOW ENGINE INNODB STATUS语句提供的 InnoDB monitor 输出的更深入视图获取引擎状态信息，以及获取复制和二进制日志信息。

与信息模式的关系

对于返回关于模式对象或特权的信息的SHOW语句，可以在信息模式中找到相同的信息。表 8-1 列出了从信息模式视图中获取信息的SHOW语句，以及可以在哪些视图中找到信息。

表 8-1

SHOW语句和信息模式之间的关联

SHOW语句

我的观点

| | --- | --- | --- | | CHARACTER SET | CHARACTER_SETS | | | COLLATION | COLLATIONS | | | COLUMNS | COLUMNS | | | CREATE DATABASE | SCHEMATA | | | CREATE EVENT | EVENTS | | | CREATE FUNCTION | ROUTINES | ROUTINE_TYPE = 'FUNCTION' | | CREATE PROCEDURE | ROUTINES | ROUTINE_TYPE = 'PROCEDURE' | | CREATE TABLE | TABLES | | | CREATE TRIGGER | TRIGGERS | | | CREATE VIEW | VIEWS | | | DATABASES | SCHEMATA | | | ENGINES | ENGINES | | | EVENTS | EVENTS | | | FUNCTION STATUS | ROUTINES | ROUTINE_TYPE = 'FUNCTION' | | GRANTS | COLUMN_PRIVILEGES``SCHEMA_PRIVILEGES``TABLE_PRIVILEGES``USER_PRIVILEGES | | | INDEX | STATISTICS | SHOW INDEXES和SHOW INDEXES是SHOW INDEX的同义词。 | | PLUGINS | PLUGINS | | | PROCEDURE STATUS | ROUTINES | ROUTINE_TYPE = 'PROCEDURE' | | PROCESSLIST | PROCESSLIST | 建议用performance_schema.threads代替。 | | PROFILE | PROFILING | 已弃用–请改用性能模式。 | | PROFILES | PROFILING | 已弃用–请改用性能模式。 | | TABLE STATUS | TABLES | | | TABLES | TABLES | | | TRIGGERS | TRIGGERS | |

在SHOW语句和相应的信息模式视图之间，信息并不总是相同的。在某些情况下，使用视图可以获得更多的信息，并且通常视图更加灵活。

还有几个SHOW语句可以在性能模式中找到底层数据。

与性能模式的关系

在引入性能模式之后，一些原本放在信息模式中的信息被移到了它逻辑上所属的性能模式中。这也反映在与SHOW语句的关系中，现在有几个表，如表 8-2 所示，它们从性能模式表中获取数据。

表 8-2

SHOW语句和性能模式之间的关联

SHOW语句

性能模式表

| | --- | --- | | MASTER STATUS | log_status | | SLAVE STATUS | log_status``replication_applier_configuration``replication_applier_filters``replication_applier_global_filters``replication_applier_status``replication_applier_status_by_coordinator``replication_applier_status_by_worker``replication_connection_configuration``replication_connection_status | | STATUS | global_status``session_status``events_statements_summary_global_by_event_name``events_statements_summary_by_thread_by_event_name | | VARIABLES | global_variables``session_variables |

SHOW MASTER STATUS包括将事件写入二进制日志时启用何种过滤的信息。该信息无法从性能模式中获得，因此如果您使用binlog-do-db或binlog-ignore-db选项(不推荐，因为它们会阻止时间点恢复)，那么您仍然需要使用SHOW MASTER STATUS。

在SHOW SLAVE STATUS输出中有几列在性能模式表中找不到。其中一些可以在mysql模式的slave_master_info和slave_relay_log_info表中找到(如果master_info_repository和relay_log_info_repository已经被设置为默认的TABLE)。

对于SHOW STATUS和SHOW VARIABLES，一个区别是如果没有会话值，返回会话范围值的SHOW语句将包括全局值。当查询session_status和session_variables时，只返回属于请求范围的值。此外，SHOW STATUS语句包括Com_%计数器，而当直接查询性能模式时，这些计数器对应于events_statements_summary_global_by_event_name和events_statements_summary_by_thread_by_event_name表中的事件(取决于查询的是全局范围还是会话范围)。

还有一些SHOW语句没有任何对应的表。将要讨论的第一组是发动机状态。

发动机状态

SHOW ENGINE语句可用于获取特定于存储引擎的信息。目前已经为 InnoDB、Performance_Schema 和 NDBCluster 引擎实现了该功能。对于所有三个引擎，都可以请求状态，对于 InnoDB 引擎，还可以获得互斥信息。

SHOW ENGINE PERFORMANCE_SCHEMA STATUS语句有助于获得关于性能模式的一些状态信息，包括表的大小及其内存使用情况。(内存使用量也可以从内存检测中获得。)

到目前为止，最常用的引擎状态语句是SHOW ENGINE INNODB STATUS，它提供了一个名为 InnoDB monitor report 的综合报告，其中包含一些无法从其他来源获得的信息。本节的其余部分将介绍 InnoDB monitor 报告。

Tip

您还可以通过启用系统变量innodb_status_output让 InnoDB 定期将监控器报告输出到错误日志中。当设置了innodb_status_output_locks选项时，InnoDB 监控器(无论是因为innodb_status_output = ON还是使用SHOW ENGINE INNODB STATUS生成的)包括附加的锁信息。

InnoDB monitor 报告以标题和注释开始，说明平均值涵盖的时间:

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1\. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2019-09-14 19:52:40 0x6480 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 59 seconds

报告本身分为几个部分，包括

BACKGROUND THREAD : 主后台线程完成的工作。
SEMAPHORES : 信号量统计。在争用导致长时间信号量等待的情况下，该部分是最重要的，在这种情况下，该部分可用于获取关于锁以及谁持有锁的信息。
LATEST FOREIGN KEY ERROR : 如果遇到外键错误，此部分包括错误的详细信息。否则，将省略该部分。
LATEST DETECTED DEADLOCK : 如果发生了死锁，此部分包括两个事务和导致死锁的锁的详细信息。否则，将省略该部分。
TRANSACTIONS**:**InnoDB 事务信息。只包括修改了 InnoDB 表的事务。如果启用了innodb_status_output_locks选项，将列出每个事务持有的锁；否则，它只是锁等待中涉及的锁。一般来说，最好使用information_schema.INNODB_TRX视图来查询事务信息，对于锁信息，最好使用performance_schema.DATA_LOCKS和performance_schema.DATA_LOCK_WAITS表。
FILE I/O : 关于 InnoDB 使用的 I/O 线程的信息，包括插入缓冲线程、日志线程、读取线程和写入线程。
INSERT BUFFER AND ADAPTIVE HASH INDEX : 关于更改缓冲区(以前称为插入缓冲区)和自适应散列索引的信息。
LOG : 关于重做日志的信息。
BUFFER POOL AND MEMORY : 关于 InnoDB 缓冲池的信息。该信息最好从information_schema.INNODB_BUFFER_POOL_STATS视图中获得。
INDIVIDUAL BUFFER POOL INFO : 如果innodb_buffer_pool_instances大于 1，则此部分包括单个缓冲池实例的信息，其信息与上一部分中的全局概要信息相同。否则，将省略该部分。这个信息最好从information_schema.INNODB_BUFFER_POOL_STATS的角度获得。
ROW OPERATIONS : 这个部分显示了关于 InnoDB 的各种信息，包括当前活动、主线程正在做什么，以及插入、更新、删除和读取的行活动。

在后面的章节中，当它们的内容用于分析性能或锁定问题时，将会用到其中的几个部分。

复制和二进制日志

在处理复制时,SHOW语句一直很重要。虽然性能模式复制表现在已经在很大程度上取代了SHOW SLAVE STATUS和SHOW MASTER STATUS语句，但是如果您想从 MySQL 内部查看连接了哪些副本并检查二进制日志或中继日志中的事件，那么您仍然需要使用SHOW语句。

列出二进制日志

SHOW BINARY LOGS语句对于检查存在哪些二进制日志很有用。如果您想知道二进制日志占用了多少空间，它们是否被加密，以及对于基于位置的复制，副本所需的日志是否仍然存在，这将非常有用。

输出结果的一个例子是

mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000044 |      2616 | No        |
| binlog.000045 |       886 | No        |
| binlog.000046 |       218 | No        |
| binlog.000047 |       218 | No        |
| binlog.000048 |       218 | No        |
| binlog.000049 |       575 | No        |
+---------------+-----------+-----------+
6 rows in set (0.0018 sec)

MySQL 8.0.14 中添加了Encrypted列，并支持加密二进制日志。

一般来说，文件大小会比示例中的大，因为在写入事务后，当文件大小超过max_binlog_size(默认为 1 GiB)时，二进制日志文件会自动循环。由于事务不在文件之间分割，如果您有大型事务，文件可能会变得比max_binlog_size大一些。

查看日志事件

SHOW BINLOG EVENTS和SHOW RELAYLOG EVENTS语句分别读取二进制日志和中继日志，并返回与参数匹配的事件。有四个参数，其中一个仅适用于中继日志事件:

IN: 从中读取事件的二进制日志或中继日志文件的名称。
FROM: 开始读取的字节位置。
**限制:**包含在可选偏移量中的事件数量。语法与SELECT语句相同:[offset], row_count。
**对于通道:**对于中继日志，为其读取事件的复制通道。

所有参数都是可选的。如果没有给出IN参数，则返回第一个日志中的事件。清单 8-1 中显示了使用SHOW BINLOG EVENTS的示例。如果您想尝试这个例子，您将需要替换二进制日志文件名、位置和限制。

mysql> SHOW BINLOG EVENTS IN 'binlog.000049' FROM 195 LIMIT 5\G
*************************** 1\. row ***************************
   Log_name: binlog.000049
        Pos: 195
 Event_type: Gtid
  Server_id: 1
End_log_pos: 274
       Info: SET @@SESSION.GTID_NEXT= '4d22b3e5-a54f-11e9-8bdb-ace2d35785be:603'
*************************** 2\. row ***************************
   Log_name: binlog.000049
        Pos: 274
 Event_type: Query
  Server_id: 1
End_log_pos: 372
       Info: BEGIN
*************************** 3\. row ***************************
   Log_name: binlog.000049
        Pos: 372
 Event_type: Table_map
  Server_id: 1
End_log_pos: 436
       Info: table_id: 89 (world.city)
*************************** 4\. row ***************************
   Log_name: binlog.000049
        Pos: 436
 Event_type: Update_rows
  Server_id: 1
End_log_pos: 544
       Info: table_id: 89 flags: STMT_END_F
*************************** 5\. row ***************************
   Log_name: binlog.000049
        Pos: 544
 Event_type: Xid
  Server_id: 1
End_log_pos: 575
       Info: COMMIT /* xid=44 */
5 rows in set (0.0632 sec)

Listing 8-1Using SHOW BINLOG EVENTS

这个例子说明了使用SHOW语句检查二进制和中继日志的一些局限性。结果是一个正常的查询结果集，由于文件大小通常在 1gb 左右，这意味着结果可能同样大。您可以像示例中那样只选择特定的事件，但是知道感兴趣的事件从哪里开始并不总是那么简单，而且您不能按事件类型或它们影响的表进行过滤。最后，默认的事件格式(binlog_format选项)是行格式，从结果的第三和第四行可以看出，从SHOW BINGOG EVENTS中可以看到的是事务更新了world.city表。您看不到更新了哪些行以及值是什么。

实际上，如果您可以访问文件系统，在大多数情况下最好使用 MySQL 附带的mysqlbinlog实用程序。(SHOW BINLOG EVENTS和SHOW RELAYLOG EVENTS语句在受控测试中或者当复制停止并且您想要快速检查导致错误的事件时仍然有用。)清单 8-2 中显示了使用mysqlbinlog实用程序执行前面的SHOW BINLOG EVENTS语句的等效命令。该示例还使用 verbose 标志来显示更新world.city表的基于行的事件的前后图像。

shell> mysqlbinlog -v --base64-output=decode-rows --start-position=195 --stop-position=575 binlog.000049
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 124
#190914 20:38:43 server id 1  end_log_pos 124 CRC32 0x751322a6  Start: binlog v 4, server v 8.0.18 created 190914 20:38:43 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 195
#190915 10:18:45 server id 1  end_log_pos 274 CRC32 0xe1b8b9a1  GTID    last_committed=0        sequence_number=1       rbr_only=yes    original_committed_timestamp=1568506725779031   immediate_commit_timestamp=1568506725779031     transaction_length=380
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1568506725779031 (2019-09-15 10:18:45.779031 AUS Eastern Standard Time)
# immediate_commit_timestamp=1568506725779031 (2019-09-15 10:18:45.779031 AUS Eastern Standard Time)
/*!80001 SET @@session.original_commit_timestamp=1568506725779031*//*!*/;
/*!80014 SET @@session.original_server_version=80018*//*!*/;
/*!80014 SET @@session.immediate_server_version=80018*//*!*/;
SET @@SESSION.GTID_NEXT= '4d22b3e5-a54f-11e9-8bdb-ace2d35785be:603'/*!*/;
# at 274

#190915 10:18:45 server id 1  end_log_pos 372 CRC32 0x2d716bd5  Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1568506725/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 372
#190915 10:18:45 server id 1  end_log_pos 436 CRC32 0xb62c64d7  Table_map: `world`.`city` mapped to number 89
# at 436
#190915 10:18:45 server id 1  end_log_pos 544 CRC32 0x62687b0b  Update_rows: table id 89 flags: STMT_END_F
### UPDATE `world`.`city`
### WHERE
###   @1=130
###   @2='Sydney'
###   @3='AUS'
###   @4='New South Wales'
###   @5=3276207
### SET
###   @1=130
###   @2='Sydney'
###   @3='AUS'
###   @4='New South Wales'
###   @5=3276208
# at 544
#190915 10:18:45 server id 1  end_log_pos 575 CRC32 0x149e2b5c  Xid = 44
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Listing 8-2Inspecting the binary log using the mysqlbinlog utility

-v参数请求详细模式，最多可以给出两次，以增加包含的信息量。单个-v在事件中从位置 436 开始生成带有伪查询的注释。--base64-output=decode-rows参数告诉mysqlbinlog不要在行格式中包含事件的 base64 编码版本。--start-position和--stop-position参数以字节为单位指定开始和停止偏移量。

事务中最有趣的事件是以注释# at 436开始的事件，这意味着事件从偏移量 436(以字节为单位)开始。它被写成一个伪 update 语句，其中的WHERE部分显示更改前的值，SET部分显示更新后的值。这也称为前后图像。

Note

如果使用加密的二进制日志，则不能直接使用mysqlbinlog来读取文件。一种选择是让mysqlbinlog连接到服务器并读取它们，这将返回未加密的日志。如果您使用keyring_file插件来存储加密密钥，另一个选择是使用 Python 或标准 Linux 工具来解密文件。这些方法在 https://mysql.wisborg.dk/decrypt-binary-logs 和 https://mysqlhighavailability.com/how-to-manually-decrypt-an-encrypted-binary-log-file/ 中有描述。

显示连接的副本

另一个有用的命令是要求复制源列出与其连接的所有副本。这可用于在监控工具中自动发现复制拓扑。

列出连接副本的命令是SHOW SLAVE HOSTS，例如:

mysql> SHOW SLAVE HOSTS\G
*************************** 1\. row ***************************
 Server_id: 2
      Host: replica.example.com
      Port: 3308
 Master_id: 1
Slave_UUID: 0b072c80-d759-11e9-8423-ace2d35785be
1 row in set (0.0003 sec)

如果在执行该语句时没有连接任何副本，结果将为空。Server_id和Master_id列分别是副本和源上的server_id系统变量的值。Host是用report_host选项指定的副本的主机名。类似地，Port列是副本的report_port值。最后，Slave_UUID列是副本上@@global.server_uuid的值。

剩下的唯一一组SHOW语句由各种语句组成，用于获取关于特权、用户、打开的表、警告和错误的信息。

杂项声明

有几个SHOW陈述是有用的，但是不适合到目前为止已经讨论过的任何组。它们可以用来列出可用的特权，返回帐户的CREATE USER语句，列出打开的表，以及在执行语句后列出警告或错误。这些声明总结在表 8-3 中。

表 8-3

杂项SHOW报表

SHOW语句

描述

| | --- | --- | | PRIVILEGES | 列出了可用的权限、它们适用的上下文，以及对某些权限的权限控制内容的描述。 | | CREATE USER | 返回帐户的CREATE USER语句。 | | GRANTS | 列出为当前帐户或另一个帐户分配的权限。 | | OPEN TABLES | 列出表缓存中的表、表锁或锁请求的数量，以及表的名称是否被锁定(发生在DROP TABLE或RENAME TABLE期间)。 | | WARNINGS | 列出警告和错误，如果sql_notes已启用(默认),则为最后执行的语句添加注释。 | | ERRORS | 列出最后执行的语句的错误。 |

三种最常用的杂项SHOW语句是SHOW CREATE USER、SHOW GRANTS和SHOW WARNINGS。

SHOW CREATE USER语句可用于检索账户的CREATE USER语句。这对于检查帐户的元数据很有用，无需直接查询底层的mysql.user表。允许所有用户为当前用户执行该语句。例如:

mysql> SET print_identified_with_as_hex = ON;
Query OK, 0 rows affected (0.0200 sec)

mysql> SHOW CREATE USER CURRENT_USER()\G
*************************** 1\. row ***************************
CREATE USER for root@localhost: CREATE USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS 0x24412430303524377B743F5E176E1A77494F574D216C41563934064E58364E385372734B77314E43587745314F506F59502E747079664957776F4948346B526B59467A642F30 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.0003 sec)

启用print_identified_with_as_hex变量(在 8.0.17 和更高版本中可用)来返回十六进制表示的密码摘要。当将值返回到控制台时，这是首选方法，因为摘要可能包含不可打印的字符。SHOW CREATE USER输出等同于用户的创建方式，可用于创建具有相同设置(包括密码)的新用户。

Note

只有 MySQL 8.0.17 和更高版本支持在创建用户时以十六进制表示法指定身份验证摘要。

SHOW GRANTS语句通过返回分配给帐户的特权来补充SHOW CREATE USER。默认为当前用户返回，但是如果您拥有mysql系统数据库的SELECT权限，您也可以获得分配给其他帐户的权限。例如，要列出 root@localhost 帐户的权限:

mysql> SHOW GRANTS FOR root@localhost\G
*************************** 1\. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2\. row ***************************
Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3\. row ***************************
Grants for root@localhost: GRANT PROXY ON “@” TO 'root'@'localhost' WITH GRANT OPTION
3 rows in set (0.0129 sec)

SHOW WARNINGS语句是 MySQL 中使用最少的语句之一。如果 MySQL 遇到问题但能够继续，它将生成一个警告，但在其他情况下完成语句的执行。虽然语句完成时没有错误，但警告可能是更大问题的迹象，最佳实践是始终检查警告，并力求在应用执行的查询中不出现警告。

Note

MySQL Shell 不支持SHOW WARNINGS语句，因为如果启用了\W模式(默认)，它将自动获取警告，否则不会提供警告。然而，该语句在传统的mysql命令行客户端和一些连接器(如 MySQL Connector/Python)中仍然有用。

清单 8-3 展示了一个例子，其中SHOW WARNINGS与传统的mysql命令行客户端一起使用来识别模式定义和数据不匹配。

mysql> SELECT @@sql_mode\G
*************************** 1\. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.0004 sec)

mysql> SET sql_mode = sys.list_drop(
                          @@sql_mode,
                          'STRICT_TRANS_TABLES'
                      );
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1\. row ***************************
  Level: Warning
   Code: 3135
Message: 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
1 row in set (0.00 sec)

mysql> UPDATE world.city
          SET Population = Population/0
        WHERE ID = 130;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 2

mysql> SHOW WARNINGS\G
*************************** 1\. row ***************************
  Level: Warning
   Code: 1365
Message: Division by 0
*************************** 2\. row ***************************
  Level: Warning
   Code: 1048
Message: Column 'Population' cannot be null
2 rows in set (0.00 sec)

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

Listing 8-3Using SHOW WARNINGS to identify problems

这个例子从 MySQL 8 中默认设置的 SQL 模式开始。首先，使用sys.list_drop()函数更改 SQL 模式，删除触发警告的STRICT_TRANS_TABLES模式，因为禁用严格模式应该与其他模式一起完成，因为它们将在以后合并在一起。然后更新了world.city表中一个城市的人口，但是计算结果是除以 0，这触发了两个警告。一个警告是针对未定义的除以 0，所以 MySQL 使用了一个NULL值，这导致了第二个警告，因为Population列是一个NOT NULL列。结果是分配给城市的人口数为 0，这可能不是应用中所预期的。这也说明了为什么启用严格 SQL 模式很重要，因为这会使除以零成为错误并阻止更新。

Caution

不要禁用STRICT_TRANS_TABLES SQL 模式，因为它更有可能导致表中出现无效数据。

摘要

本章介绍了在信息模式和性能模式实现之前的SHOW语句。现在，在信息模式和性能模式中使用底层数据源通常更好。前两节给出了SHOW语句和数据源之间的映射。

还有一些SHOW语句返回无法通过其他来源访问的数据。一个常用的特性是通过SHOW ENGINE INNODB STATUS语句从 InnoDB 获得的 InnoDB monitor 报告。该报告分为几个部分，其中一些将在调查性能和锁定问题时使用。

还有一些关于复制和二进制日志的语句非常有用。其中最常用的语句是SHOW BINARY LOGS,它列出了 MySQL 已知的该实例的二进制日志。这些信息包括日志的大小以及日志是否加密。您还可以在二进制日志或中继日志中列出事件，但实际上mysqlbinlog实用程序通常是更好的选择。

最后，涵盖了一组杂七杂八的SHOW语句。其中最常用的三个是:SHOW CREATE USER显示可用于重新创建用户的语句,SHOW GRANTS返回分配给用户的权限,SHOW WARNINGS列出上次执行查询时出现的错误、警告和默认注释。检查警告是执行查询时经常被忽略的一个方面，因为警告可能表明查询结果不是您所期望的。建议始终检查警告并启用STRICT_TRANS_TABLES SQL 模式。

关于信息源的最后一章是关于慢速查询日志的。

九、慢速查询日志

在能够从性能模式中获取查询统计信息之前，慢速查询日志是查找可进行优化的查询的主要信息源。即使在今天，缓慢的查询日志也不应该被完全抛弃。

与性能模式中的语句摘要信息相比，慢速查询日志有三个主要优势。记录的查询是持久化的，所以您可以在 MySQL 重新启动后查看信息，查询被记录了时间戳，并且实际的查询被记录。由于这些原因，慢速查询日志通常与性能模式一起使用。

Tip

像 MySQL Enterprise Monitor(https://dev.mysql.com/doc/mysql-monitor/en/mem-qanal-using.html)这样的监控解决方案可以克服性能模式的这些限制，所以如果您有一个包含详细查询信息的监控解决方案，您就不太可能需要缓慢的查询日志。

缓慢的查询日志也有缺点。开销高于性能模式，因为查询被写入纯文本文件，并且在写入事件时没有并发支持。对查询日志的支持也很有限(您可以将慢速查询日志存储在一个表中，但这有其自身的缺点)，这使得在调查过程中使用它不太实际。

本章将介绍如何配置慢速查询日志、原始日志事件的外观，以及如何使用mysqldumpslow(在 Microsoft Windows 上为mysqldumpslow.pl)脚本来聚合日志。

配置

有几个选项可用于配置慢速查询日志以及记录哪些查询。由于启用日志的开销随着记录的查询数量的增加而增加，所以配置良好的慢速查询日志非常重要。记录“恰到好处”的查询也使得识别感兴趣的查询变得更加容易。

默认情况下不启用慢速查询日志，当启用该日志时，默认情况下只记录直接在本地实例上执行的非管理查询，并且该查询的执行时间超过 10 秒。表 9-1 总结了可用于微调该行为的配置选项。该信息包括默认值以及该选项是用于全局范围还是会话范围，或者两者都用。选项按字母顺序列出。

表 9-1

慢速查询日志的配置选项

选项/默认值/范围

描述

| | --- | --- | | min_examined_row_limit默认值:0 范围:全局，会话 | 只有检查的行数超过该值的查询才会被记录。这在允许记录所有执行全扫描的查询时特别有用。 | | log_output默认:FILE范围:全球 | 控制慢速查询日志和常规查询日志是记录到文件、表或两者中，还是根本不记录。 | | log_queries_not_using_indexes默认:OFF范围:全球 | 启用后，所有执行全表或索引扫描的查询都会被记录下来，不管它们需要多长时间。 | | log_short_format默认:OFF范围:全球 | 启用时，记录的信息较少。此选项只能在配置文件中设置。 | | log_slow_admin_statements默认:OFF范围:全球 | 启用时，像ALTER TABLE和OPTIMIZE TABLE这样的管理语句可以进行日志记录。 | | log_slow_extra默认:OFF范围:全球 | 启用时，有额外的信息，如查询的Handler_%状态变量的值。只有在记录到文件以及 MySQL 8.0.14 和更高版本中，才支持该功能。不启用log_slow_extra的主要原因是如果您有需要旧格式的脚本。 | | log_slow_slave_statements默认:OFF范围:全球 | 启用后，复制的语句也有资格进行日志记录。这只适用于语句格式的二进制日志事件。 | | log_throttle_queries_not_using_indexes默认:0范围:全球 | 当您启用了对执行完全扫描的所有查询的记录时，此选项可以限制每分钟可以记录查询的最大次数。 | | log_timestamps默认:UTC范围:全球 | 时间戳是使用 UTC 还是系统时区。此选项也适用于错误日志和一般查询日志。它仅在记录到文件时适用。 | | long_query_time默认:10范围:全局，会话 | 记录查询之前的最短查询延迟时间(秒)(除非它正在进行完全扫描，并且您已启用记录这些查询)。支持小数秒。设置为 0 以记录所有查询。**警告:**记录所有查询有很大的开销，最好在测试系统上或短时间内完成。 | | slow_query_log默认:OFF范围:全球 | 是否启用慢速查询日志。 | | slow_query_log_file默认:<hostname>-slow.log范围:全球 | 慢速查询日志文件的路径和文件名。默认位置在数据目录中，根据系统的主机名命名。 |

建议将log_output保留为默认值，并将事件记录到由slow_query_log_file设置的文件中。以表格的形式获取慢速查询日志似乎很有吸引力；但是，在这种情况下，数据保存为逗号分隔值(CSV ),对表的查询不能使用索引。还有一些功能如log_slow_extra是log_output = TABLE不支持的。

这些选项意味着您可以细粒度地控制记录哪些查询。除了log_short_format以外的所有选项都可以动态更改，所以您可以根据情况需要进行更改。如果你觉得很难确定选项是如何相互作用的，那么图 9-1 显示了一个决定过程的流程图，该过程决定一个查询是否应该被记录。(流程图只是说明性的，实际的代码路径是不同的。)

img/484666_1_En_9_Fig1_HTML.png

图 9-1

确定是否将查询记录到慢速日志的流程图

流程从查询类型开始。对于管理语句和复制语句，它们只有在启用了相应选项的情况下才会继续。常规查询首先检查它们是否符合不使用索引的条件，然后依靠检查查询执行时间(延迟)。如果满足任一条件，则检查是否检查了足够多的行。一些更好的细节，比如不使用索引的语句的节流，在图中被忽略了。

一旦有了所需的查询设置，就需要查看日志中的事件，以确定是否有任何查询需要注意。

记录事件

慢速查询日志由纯文本格式的事件组成。这意味着您可以使用任何您喜欢的文本查看器来检查文件。在 Linux 和 Unix 上，less命令是一个很好的选择，因为它对处理大文件有很好的支持。在 Microsoft Windows 上，Notepad++是一种常见的选择，但对大文件没有同样好的支持。Windows 上的另一个选项是安装 Windows Subsystem for Linux (WSL ),它允许您安装 Linux 发行版，并以这种方式访问像less这样的命令。

事件的格式取决于设置。清单 9-1 显示了一个默认格式的事件示例，用long_query_time = 0记录所有查询。请注意，由于页面宽度有限，一些行已经换行。

# Time: 2019-09-17T09:37:53.269881Z
# User@Host: root[root] @ localhost [::1]  Id:    22
# Query_time: 0.032531  Lock_time: 0.000221 Rows_sent: 10  Rows_examined: 4089
SET timestamp=1568713073;
SELECT CountryCode, COUNT(*) FROM world.city GROUP BY CountryCode ORDER BY COUNT(*) DESC LIMIT 10;

Listing 9-1A slow query log event in the default format

第一行显示了查询的执行时间。这是时间戳，您可以控制是使用 UTC 还是系统时间来使用log_timestamp选项。第二行显示了哪个帐户执行了查询和连接 id。第三行包括查询的一些基本统计数据:查询执行时间、等待锁的时间、返回给客户机的行数和检查的行数。

SET timestamp查询设置查询的时间戳，以 epoch(1970 年 1 月 1 日 00:00:00 UTC)以来的秒数度量，最后慢速查询在最后一行。

在统计数据中，查询时间和检查的行数与发送的行数之间的比率是特别重要的。与返回的行数相比，检查的行数越多，索引的效率通常越低。但是，您应该总是在查询的上下文中查看信息。在这种情况下，查询会找到城市最多的十个国家代码。如果不执行全表或索引扫描，就无法找到，所以在这种情况下，检查的行数与发送的行数之比很低是有原因的。

如果您在版本 8.0.14 和更高版本中启用了log_slow_extra，那么您将获得查询的附加信息，如清单 9-2 所示。

# Time: 2019-09-17T10:09:50.054970Z
# User@Host: root[root] @ localhost [::1]  Id:    22
# Query_time: 0.166589  Lock_time: 0.099952 Rows_sent: 10  Rows_examined: 4089 Thread_id: 22 Errno: 2336802955 Killed: 0 Bytes_received: 0 Bytes_sent: 0 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 4079 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 10 Sort_scan_count: 1 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2019-09-17T10:09:49.888381Z End: 2019-09-17T10:09:50.054970Z
SET timestamp=1568714989;
SELECT CountryCode, COUNT(*) FROM world.city GROUP BY CountryCode ORDER BY COUNT(*) DESC LIMIT 10;

Listing 9-2Using log_slow_extra with the slow query log

从性能角度来看，主要感兴趣的统计数据是以Bytes_received开始，以Created_tmp_tables结束的统计数据。其中一些统计数据相当于查询的Handler_%状态变量。在这种情况下，您可以看到Read_next计数器是大量被检查行的主要贡献者。Read_next在扫描索引以查找行时使用，因此可以断定查询执行了索引扫描。

如果您需要知道在给定时间执行了什么，查看原始事件会非常有用。如果您更想知道哪些查询通常对系统负载贡献最大，那么您需要聚合数据。

聚合

可以使用 MySQL 安装中包含的mysqldumpslow(在 Microsoft Windows 上为mysqldumpslow.pl)脚本来聚合慢速查询日志中的数据。mysqldumpslow是一个 Perl 脚本，默认情况下，它通过用N替换数值，用'S'替换字符串来规范化日志中的查询。这允许脚本以类似于性能模式中的events_statements_summary_by_digest表的方式来聚合查询。

Note

该脚本要求在您的系统上安装 Perl。在总是有 Perl 的 Linux 和 Unix 上，这不是问题，但是在 Microsoft Windows 上，您将需要自己安装 Perl。一种选择是从 http://strawberryperl.com/ 安装草莓 Perl。

有几个选项可以控制mysqldumpslow的行为。这些总结在表 9-2 中。此外，慢速查询日志文件可以作为不带选项名称的参数给出。

表 9-2

mysqldumpslow的命令行参数

[计]选项

缺省值

描述

| | --- | --- | --- | | -a | | 不要用N和'S'替换数字和字符串值。 | | --debug | | 在调试模式下执行。 | | -g | | 对查询执行模式匹配(使用与grep相同的语法),并且只包括匹配的查询。 | | -h | * | 默认情况下，mysqldumpslow搜索 MySQL 配置文件中设置的datadir中的文件。此选项指定文件应该匹配的主机名，假设使用默认的慢速查询日志文件名。可以使用通配符。 | | --help | | 显示帮助文本。 | | -i | | 在自动算法中使用的mysql.server启动脚本中的实例名，以查找慢速查询日志文件。 | | -l | | 不要提取查询的锁定时间。 | | -n | 0 | 在抽象为N之前，数字中必须包含的最小位数。 | | -r | | 颠倒查询的返回顺序。 | | -s | at | 如何对查询进行排序。默认情况下，根据平均查询时间进行排序。排序选项的完整列表将单独介绍。 | | -t | (全部) | 在结果中返回的最大查询数。 | | --verbose | | 在脚本执行期间打印附加信息。 |

-s、-t和-r选项是最常用的。虽然mysqldumpslow可以使用默认路径和主机名中的 MySQL 配置文件来搜索慢速查询日志，但更常见的是在命令行中将慢速查询日志文件的路径指定为参数。

-s选项用于指定如何对结果中包含的查询进行排序。对于某些排序选项，可以选择使用总计或平均值进行排序。分类选项在表 9-3 中列出，也可从mysqldumpslow --help输出中获得。总计列指定用于按总计排序的选项，而平均值列显示用于按平均值排序的选项。

表 9-3

mysqldumpslow的排序选项

总数

平均的

描述

| | --- | --- | --- | | c | | 按查询执行的次数(计数)排序。 | | l | al | 按锁定时间排序。 | | r | ar | 按发送的行数排序。 | | t | at | 按查询时间排序。 |

有时使用不同的排序选项生成几个报告会很有用，这样可以更好地了解在实例上执行的查询。

作为一个案例研究，考虑一个实例从一个空的慢速查询日志文件开始；然后执行清单 9-3 中的查询。执行这些查询时，会话的long_query_time设置为 0，以记录所有查询，这有助于避免花费很长时间执行查询。

SET GLOBAL slow_query_log = ON;
SET long_query_time = 0;
SELECT * FROM world.city WHERE ID = 130;
SELECT * FROM world.city WHERE ID = 131;
SELECT * FROM world.city WHERE ID = 201;
SELECT * FROM world.city WHERE ID = 2010;
SELECT * FROM world.city WHERE ID = 1;
SELECT * FROM world.city WHERE ID = 828;
SELECT * FROM world.city WHERE ID = 131;
SELECT * FROM world.city WHERE CountryCode = 'AUS';
SELECT * FROM world.city WHERE CountryCode = 'CHN';
SELECT * FROM world.city WHERE CountryCode = 'IND';
SELECT * FROM world.city WHERE CountryCode = 'GBR';
SELECT * FROM world.city WHERE CountryCode = 'USA';
SELECT * FROM world.city WHERE CountryCode = 'NZL';
SELECT * FROM world.city WHERE CountryCode = 'BRA';
SELECT * FROM world.city WHERE CountryCode = 'AUS';
SELECT * FROM world.city WHERE CountryCode = 'DNK';
SELECT * FROM world.city ORDER BY Population DESC LIMIT 10;
SELECT * FROM world.city ORDER BY Population DESC LIMIT 4;
SELECT * FROM world.city ORDER BY Population DESC LIMIT 9;

Listing 9-3The queries used to create slow query log events for a case study

对于WHERE子句或LIMIT子句，有三个具有不同值的基本查询。首先，通过主键找到城市，主键将搜索一行以返回一行。第二，城市是通过作为二级索引的CountryCode找到的，所以找到了几行，但返回的行数仍然相同。第三，检查所有城市以返回人口最多的城市。

假设慢速查询日志文件被命名为mysql-slow.log，并且您正在文件所在的同一个目录中执行mysqldumpslow，那么您可以对查询进行分组，并按照查询被执行的次数对它们进行排序，如清单 9-4 所示。-t选项用于将报告限制为包括三个(规范化的)查询。

shell$ mysqldumpslow -s c -t 3 mysql-slow.log

Reading mysql slow query log from mysql-slow.log
Count: 9  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=150.1 (1351), root[root]@localhost
  SELECT * FROM world.city WHERE CountryCode = 'S'

Count: 7  Time=0.02s (0s)  Lock=0.00s (0s)  Rows=1.0 (7), root[root]@localhost
  SELECT * FROM world.city WHERE ID = N

Count: 3  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=7.7 (23), root[root]@localhost
  SELECT * FROM world.city ORDER BY Population DESC LIMIT N

Listing 9-4Using mysqldumpslow to sort the queries by count

注意WHERE和LIMIT条款是如何被修改成使用N和'S'的。查询时间以Time=0.00s (0s)的形式列出，平均查询时间(0.00s)在前，总时间在括号中。锁和行统计信息也是如此。

因为mysqldumpslow脚本是用 Perl 编写的，所以如果您想包含对新排序选项的支持或者更改输出，修改脚本相对容易。例如，如果您想在平均执行时间中包含更多的小数，您可以在usage子例程之前修改printf语句(MySQL 8.0.18 包含的脚本中的第 168–169 行),如下所示

    printf "Count: %d  Time=%.6fs (%ds)  Lock=%.2fs (%ds)  Rows=%.1f (%d), $user\@$host\n%s\n\n",
          $c, $at,$t, $al,$l, $ar,$r, $_;

变化出现在第一行的Time=%.6fs部分。这将打印以微秒为单位的平均执行时间。

摘要

本章展示了如何使用慢速查询日志来收集在 MySQL 实例上执行的查询的信息。慢速查询日志侧重于根据执行时间和查询是否使用索引(实际上是执行全表扫描还是索引扫描)来捕获查询。与性能模式相比，慢速查询日志的主要优点是日志包括执行的确切语句，并且是持久化的。缺点是开销大，而且很难得到返回您感兴趣的查询的报告。

首先，讨论了用于配置慢速查询日志的配置选项。有一些选项可以控制最小执行时间、是否应该记录不使用索引的查询而不管执行时间、要记录的查询类型等等。在 MySQL 8.0.14 和更高版本中，您可以使用log_slow_extra来包含关于慢速查询的更多详细信息。

其次，讨论了两个慢速查询日志事件的例子。有一个使用默认信息的例子和一个启用了log_slow_extra的例子。如果您正在查找在给定时间点执行的查询的信息，原始事件会很有用。对于更一般的查询，用mysqldumpslow脚本聚集数据更有用。在上一节中讨论了mysqldumpslow的使用。

下一部分将介绍一些对性能调优有用的工具，首先以 MySQL Enterprise Monitor 为例讨论监控。

十、MySQL 企业监控器

无论是在系统级还是查询级，监控都是性能调优的关键之一。本章将介绍 MySQL 可用的监控解决方案之一，MySQL 企业监控器，也称为 MEM。

本章将首先概述 MySQL 企业监控器的架构和原理。如果您想尝试 MySQL Enterprise Monitor，那么有一个部分提供了安装说明，随后讨论了如何启动和停止服务管理器，以及如何将 MySQL 实例添加到受监控实例的列表中。最后，是用户界面之旅。

本书的其余部分使用 MySQL Enterprise Monitor 中的图表和报告来说明监控工具的使用，但是您也可以使用其他监控解决方案。如果你对 MySQL 企业监控器不感兴趣，可以跳过这一章。

概观

MySQL Enterprise Monitor 是 Oracle 专用于 MySQL 的监控解决方案。它作为 MySQL Server 的配套产品提供给客户，由 MySQL 开发团队开发。

Note

MySQL Enterprise Monitor 需要 MySQL Enterprise Edition 或 MySQL Cluster CGE(运营商级版本)订阅才能在 30 天试用版之后使用(另请参见下一节中的下载说明)。您可以在 www.mysql.com/products/enterprise/ 查看 MySQL 商业特性。

MySQL Enterprise Monitor 由多个组件组成，每个组件在整个监控解决方案中都扮演着自己的角色。在版本 8 中，有两个主要组件:

**服务管理器:**该组件存储收集的指标，并提供查看数据和管理配置的前端接口。服务管理器由两部分组成，一部分是 Tomcat 服务器，它是服务管理器的应用端，另一部分是存储库，它是一个存储数据的 MySQL 数据库。
代理: MySQL Enterprise Monitor 使用代理连接到被监控的 MySQL 实例。服务管理器包括一个内置代理，默认情况下它会监控存储库。代理可以监控本地操作系统以及本地和远程 MySQL 实例。

Note

本书遵循 MySQL 企业监控手册( https://dev.mysql.com/doc/mysql-monitor/en/ )的惯例，用标题案例来写服务经理和代理。

由于代理只能监控运行它的操作系统 CPU 和内存使用量、磁盘容量等指标——所以最好在监控 MySQL 实例的每台主机上安装一个代理。这将允许您将主机指标与 MySQL 活动相关联。如果您无法在本地安装代理，例如，如果您使用的云解决方案不允许您访问操作系统，您可以使用安装在另一台主机上的代理来监控 MySQL 指标。在这种情况下，一种选择是使用服务管理器中的内置代理。图 10-1 显示了一个有三台主机的设置示例，其中一台用于服务管理器，两台主机安装了被监控的 MySQL 实例。

img/484666_1_En_10_Fig1_HTML.jpg

图 10-1

MySQL 企业监控器组件概述

顶部的主机安装了 MySQL 企业监控服务管理器。它包括前端——这里用一个带有图形的网页来描述——以及内置的代理和存储库。内置代理监控存储库，也可以选择性地用于监控其他 MySQL 实例(图中未显示),如果您无法访问主机(例如某些云产品),或者如果您正在测试并希望监控安装了服务管理器的同一主机上的第二个 MySQL 实例，这将非常有用。

主机 1 和主机 2 是两台安装了 MySQL 服务器的主机。每台主机上都安装了一个 MySQL 企业监控代理。代理向 MySQL 实例查询指标，并将指标发送给服务管理器，服务管理器将它们存储在存储库中。服务管理器还可以向代理发送请求，例如，运行特别报告或更改代理收集指标的频率。

服务经理和代理的安装过程相似，都使用客户安装程序。下一节将介绍如何安装服务管理器。如果您想尝试安装代理，那么它将作为一个练习留给读者。

装置

MySQL Enterprise Monitor 的安装非常简单，尽管与其他 MySQL 产品不同。下载该软件与您使用 MySQL 社区版时可能习惯的方式不同，安装总是通过专用的安装程序来完成。本节将指导您完成 MySQL Enterprise Monitor 的下载、安装过程和设置。

[计] 下载

安装的第一步是下载 MySQL 企业监控器。有两个地方可以下载 MySQL 企业监控器。现有的 MySQL 客户可以从 My Oracle Support (MOS)中的 Patches & Updates 选项卡下载。这是推荐给客户的位置，因为修补程序和更新会更频繁地更新，并且包括自 2011 年以来的所有版本。另一个地点是位于 https://edelivery.oracle.com/ 的甲骨文软件交付云，它也允许注册用户下载 30 天的试用版。这些说明涵盖了 Oracle 软件交付云。

Note

新帐户和一段时间没有使用的帐户可能需要进行出口验证，这可能需要几天时间。

你从“主页”开始，如图 10-2 所示。

img/484666_1_En_10_Fig2_HTML.jpg

图 10-2

甲骨文软件交付云主页

如果您没有登录，您需要使用新用户创建一个新用户？在此注册图标。登录后，您将进入搜索页面。图 10-3 显示了搜索表单的一部分。

img/484666_1_En_10_Fig3_HTML.jpg

图 10-3

Oracle 软件交付云搜索表单

在文本字段左侧的下拉框中选择释放。如果您对其他产品也感兴趣，您可以保留默认值，即所有类别，其中包括软件包。在文本字段中，输入 MySQL Enterprise Monitor ，并在显示的搜索列表中单击 MySQL Enterprise Monitor ，或者单击文本字段右侧的搜索按钮(图中未显示列表和按钮)。然后点击 MySQL Enterprise Monitor 结果旁边的添加到购物车。

当产品被添加到购物车后，您可以点击页面右上角附近的结帐链接(图中也没有显示)。下一个屏幕如图 10-4 所示，允许您选择为哪些平台下载。

img/484666_1_En_10_Fig4_HTML.jpg

图 10-4

选择要下载的平台

选择你感兴趣的平台。如果您计划在一个平台上安装服务管理器，同时在另一个平台上安装代理来监控实例，那么您需要选择两个平台。当您决定要为哪些平台下载时，点击继续。

下一步是接受许可协议。接受前请仔细阅读。Oracle 试用许可协议位于文档末尾。接受条款和条件后，点击继续。

Note

作为其中一个步骤，您可能需要完成一项关于 Oracle 软件交付云可用性的调查。

最后一步是选择您想要下载 MySQL Enterprise Monitor 的哪些部分。如图 10-5 所示。

img/484666_1_En_10_Fig5_HTML.jpg

图 10-5

选择要下载 MySQL 企业监控器的哪些部分

每个平台有两个软件包，一个用于服务经理，一个用于代理。可选地(推荐)，您可以单击屏幕截图底部中间的查看摘要详细信息链接，以显示每个文件的 SHA-1 和 SHA-256 校验和。您可以使用这些来验证下载是否成功完成。

您可以通过两种方式下载文件。如果您单击文件名，您将一个接一个地下载文件。或者，检查您想要的文件并点击下载按钮，使用下载管理器开始下载。如果您没有安装下载管理器，您将在下载开始前被引导完成安装。

Tip

Oracle 软件交付云使用通用文件名，如V982880-01.zip。将文件重命名为包含您下载的产品、平台和版本信息的名称非常有用。

下载完成后，您可以开始安装过程。

安装过程

MySQL Enterprise Monitor 使用自己的安装程序，该程序在所有平台上都是一样的。支持通过图形用户界面或文本模式使用向导模式执行安装，或者您可以在命令行上提供所有参数并使用无人值守模式。

下载文件的名称取决于您下载的平台和 MySQL 企业监控器的版本。例如，用于微软 Windows 的服务管理器版本 8.0.17 被命名为V982881-01.zip。其他文件的名称类似。如果解压缩 ZIP 文件，您会发现几个文件:

PS> ls | select Length,Name

   Length Name
   ------ ----
  6367299 monitor.a4.pdf
  6375459 monitor.pdf
  5275639 mysql-monitor-html.tar.gz
  5300438 mysql-monitor-html.zip
281846252 mysqlmonitor-8.0.17.1195-windows64-installer.exe
281866739 mysqlmonitor-8.0.17.1195-windows64-update-installer.exe
      975 README_en.txt
      975 READ_ME_ja.txt

确切的文件名和大小取决于平台和 MySQL 企业监控器版本。注意，有两个可执行文件，在本例中是mysqlmonitor-8.0.17.1195-windows64-installer.exe和mysqlmonitor-8.0.17.1195-windows64-update-installer.exe。前者用于从头开始安装 MySQL Enterprise Monitor，而另一个(有时也称为更新安装程序)用于执行现有安装的升级。PDF 和 HTML 文件是手册，但您通常最好使用位于 https://dev.mysql.com/doc/mysql-monitor/en/ 的在线手册，因为它会定期更新。

Tip

如果您想使用基于文本的向导或无人值守模式，请使用--help参数调用安装程序，以获得受支持参数的列表。

本讨论将继续使用图形用户界面进行安装。您可以通过执行不带任何参数的安装程序来开始安装。第一步是选择语言(英语、日语和简体中文均可)。然后，系统会告诉您需要确保将安装过程中输入的用户名和密码保存在一个安全的位置。

通过欢迎屏幕后，通过指定安装位置来正确启动配置。在 Microsoft Windows 上，默认位置是C:\Program Files\MySQL\Enterprise\Monitor，在 Linux 上，作为root用户安装时是/opt/mysql/enterprise/monitor，作为非特权用户安装时是相对于主目录的mysql/enterprise/monitor。

图 10-6 所示的下一个屏幕要求您选择要监控多大的系统。

img/484666_1_En_10_Fig6_HTML.jpg

图 10-6

选择系统的大小

系统大小决定了默认设置，例如服务管理器的内存配置。安装完成后，您可以手动调整内存设置，但是选择正确的系统大小意味着您不必一开始就担心这些设置。除非您只想用几个实例来尝试 MySQL Enterprise Monitor，否则请选择中型或大型系统。

接下来，您需要指定要使用的端口号。MySQL Enterprise Monitor 使用 Tomcat 服务器作为前端，端口 18080 作为默认的未加密端口，18443 作为默认的 SSL 端口。您将始终使用 SSL 端口。(非 SSL 端口是出于传统原因而存在的，但不能用于前端。)

此时，如果您使用root帐户在 Linux 上安装，将会询问您希望在哪个用户帐户下运行 Tomcat 进程(MySQL 服务器存储库进程将使用mysql用户)。默认是mysqlmem。如果您使用非 root 帐户在 Linux 上安装，将会通知您安装程序无法设置自动启动。

服务管理器使用 MySQL 实例来存储数据，包括收集的指标。您可以选择(见图 10-7 )使用安装程序附带的 MySQL 实例或使用现有的 MySQL 实例。

img/484666_1_En_10_Fig7_HTML.jpg

图 10-7

选择要使用的 MySQL 实例

除非您有非常充分的理由选择其他方式，否则建议使用捆绑的 MySQL 数据库。这不仅允许安装程序使用已知与服务管理器配合良好的基本配置，还简化了升级。

Caution

不要试图使用您想要监控的 MySQL 实例作为服务管理器的存储库。MySQL Enterprise Monitor 确实会导致大量的数据库活动，如果您使用生产数据库，那么当它应该监控的数据库关闭时，您的监控将停止工作。

现在，您可以选择服务管理器用来连接 MySQL 实例的用户名和密码，以及端口号和模式名。如图 10-8 所示。

img/484666_1_En_10_Fig8_HTML.jpg

图 10-8

为捆绑的 MySQL 服务器选择设置

不要轻易选择密码。监控将包括许多关于您的系统的细节，包括主机名和查询。这意味着选择强密码很重要。

这就是配置的结束，安装程序准备开始实际的安装步骤。安装需要一点时间，因为它包括安装 MySQL 服务器实例和 Tomcat 服务器前端。安装完成后，显示一个确认屏幕，随后显示图 10-9 中的警告。

img/484666_1_En_10_Fig9_HTML.jpg

图 10-9

关于默认情况下使用的自签名证书的警告

安装程序为 SSL 连接创建自签名证书。这将很好地加密通信，但它不允许验证您是否连接到正确的服务器。您可以选择购买由可信提供商签名的证书，并让 MySQL Enterprise Monitor 使用该证书。如果您继续使用默认的自签名证书(这里假设的)，浏览器将在您第一次连接到服务管理器时抱怨您不能信任该连接(在这种情况下这是无害的)。

这就完成了安装。最后一个屏幕显示确认您已完成向导，您可以选择打开自述文件并启动浏览器。安装程序已经在后台启动了服务管理器，因此除了在浏览器中打开服务管理器的 URL 之外，您不需要做任何其他事情。如果您的浏览器与安装服务管理器的主机在同一台主机上，并且您选择了默认的 SSL 端口(18443)，则 URL 为https://localhost:18443/。

Note

Tomcat 可能需要一点时间来准备响应连接，这使得第一次连接尝试需要一段时间才能完成。

如上所述，如果您使用默认的自签名证书，浏览器将警告您存在潜在的安全风险。Firefox 的一个例子如图 10-10 所示。

img/484666_1_En_10_Fig10_HTML.jpg

图 10-10

Firefox 警告网站无法验证

你需要接受这个风险。如何做到这一点取决于您的浏览器和版本。在 Firefox 68 的情况下，你进入高级选项并选择接受风险并继续。

连接到服务管理器的第一步是进行更多的配置。如图 10-11 所示，大部分信息都集中在一个屏幕上。

img/484666_1_En_10_Fig11_HTML.jpg

图 10-11

服务管理器配置屏幕

顶部要求您配置两个用户。具有管理员角色的用户是您用来通过浏览器登录服务管理器的管理用户(如果需要，您可以在以后创建更多具有较少权限的用户)。具有代理角色的用户是在其他主机上安装代理来监控 MySQL 实例时使用的用户。确保为两个用户选择强密码。

左下角允许您配置 MySQL Enterprise Monitor 是否应该自动检查升级，如果是，是否需要代理设置。在右下角，您可以配置数据应该保留多长时间。您保存数据的时间越长，您就可以追溯到更远的时间来调查问题，并且您保存的细节也越多。代价是数据库的大小增加了。

完成设置后，您将被带到一个新特性页面，您可以为新创建的管理用户设置想要使用的时区和区域设置。

Tip

如果要再次卸载服务管理器，可以使用卸载程序。在 Microsoft Windows 上，您可以通过控制面板中的程序应用来完成此操作。在其他平台上，使用最顶层安装目录中的uninstall命令。

因为在测试期间您可能需要启动和停止服务管理器，下一节将展示如何做。

启动和停止服务管理器

服务管理器被设计为作为服务启动和停止。在 Microsoft Windows 上，当您在 Linux 上使用root帐户安装服务管理器时，安装程序将始终为您安装服务。如果您在 Linux 上以非 root 用户的身份安装它，您可以手动执行服务脚本来启动和停止服务管理器。

Tip

如果手动启动进程，首先启动 MySQL 存储库服务，然后启动 Tomcat。停止它的时候，是反过来的，先停止 Tomcat，再停止 MySQL repository 服务。

微软视窗软件

在 Microsoft Windows 上，安装程序始终需要管理员权限才能运行，这意味着它也可以将 Service Manager 进程作为服务安装。默认情况下，这些服务被设置为在您启动和关闭计算机时自动启动和停止。

您可以通过打开服务应用来编辑服务的设置。在 Windows 10 上，最简单的方法是使用键盘上的 Windows 键(或者通过单击左下角的 Windows 图标打开开始菜单)，然后输入 Services ，如图 10-12 所示。

img/484666_1_En_10_Fig12_HTML.jpg

图 10-12

打开服务应用

与截图相比，搜索结果可能在某种程度上有所不同。点击最佳匹配下的服务应用匹配。这将打开应用，您可以在其中控制服务。在服务应用中，您可以通过启动、停止、暂停或重新启动服务来控制服务。存储库服务命名为 MySQL Enterprise MySQL，，Tomcat 服务命名为 MySQL Enterprise Tomcat ，如图 10-13 所示。

img/484666_1_En_10_Fig13_HTML.jpg

图 10-13

控制服务

点按服务时，您会在服务列表左侧的面板中获得基本控制操作。您也可以右键单击服务来获取操作以及编辑服务属性的选项。这些属性包括是否自动启动和停止服务。

Linux 操作系统

如何在 Linux 上启动和停止 MySQL Enterprise Monitor 取决于您是否使用root操作系统用户执行了安装。如果您使用的是root用户，那么您可以通过mysql-monitor-server服务使用service命令(没有对systemd的本地支持)来启动和停止进程；否则，使用安装目录下的mysqlmonitorctl.sh脚本。无论哪种方式，您都可以添加tomcat或mysql参数来更改其中一个进程的状态。

清单 10-1 展示了如何使用service命令来启动、重启和停止 MySQL 企业监控器。

shell$ sudo service mysql-monitor-server start
Starting mysql service  [ OK ]
2019-08-24T06:45:43.062790Z mysqld_safe Logging to '/opt/mysql/enterprise/monitor/mysql/data/ol7.err'.
2019-08-24T06:45:43.168359Z mysqld_safe Starting mysqld daemon with databases from /opt/mysql/enterprise/monitor/mysql/data
Starting tomcat service  [ OK ]

shell$ sudo service mysql-monitor-server restart
Stopping tomcat service . [ OK ]
Stopping mysql service 2019-08-24T06:47:57.907854Z mysqld_safe mysqld from pid file /opt/mysql/enterprise/monitor/mysql/runtime/mysqld.pid ended
. [ OK ]
Starting mysql service  [ OK ]
2019-08-24T06:48:04.441201Z mysqld_safe Logging to '/opt/mysql/enterprise/monitor/mysql/data/ol7.err'.
2019-08-24T06:48:04.544643Z mysqld_safe Starting mysqld daemon with databases from /opt/mysql/enterprise/monitor/mysql/data
Starting tomcat service  [ OK ]

shell$ sudo service mysql-monitor-server stop tomcat
Stopping tomcat service . [ OK ]

shell$ sudo service mysql-monitor-server stop mysql
Stopping mysql service 2019-08-24T06:48:54.707288Z mysqld_safe mysqld from pid file /opt/mysql/enterprise/monitor/mysql/runtime/mysqld.pid ended
. [ OK ]

Listing 10-1Changing the status of the services with the service command

首先启动两个服务，然后重新启动，最后逐个停止服务。没有必要一个接一个地停止服务，但是这可能是有用的，例如，如果您需要对存储库进行维护。

清单 10-2 展示了使用mysqlmonitorctl.sh脚本的相同示例。

shell $ ./mysqlmonitorctl.sh start
Starting mysql service  [ OK ]
2019-08-24T06:52:34.245379Z mysqld_safe Logging to '/home/myuser/mysql/enterprise/monitor/mysql/data/ol7.err'.
2019-08-24T06:52:34.326811Z mysqld_safe Starting mysqld daemon with databases from /home/myuser/mysql/enterprise/monitor/mysql/data
Starting tomcat service  [ OK ]

shell$ ./mysqlmonitorctl.sh restart
Stopping tomcat service . [ OK ]
Stopping mysql service 2019-08-24T06:53:08.292547Z mysqld_safe mysqld from pid file /home/myuser/mysql/enterprise/monitor/mysql/runtime/mysqld.pid ended
. [ OK ]
Starting mysql service  [ OK ]
2019-08-24T06:53:15.310640Z mysqld_safe Logging to '/home/myuser/mysql/enterprise/monitor/mysql/data/ol7.err'.
2019-08-24T06:53:15.397898Z mysqld_safe Starting mysqld daemon with databases from /home/myuser/mysql/enterprise/monitor/mysql/data
Starting tomcat service  [ OK ]

shell$ ./mysqlmonitorctl.sh stop tomcat
Stopping tomcat service . [ OK ]

shell$ ./mysqlmonitorctl.sh stop mysql
Stopping mysql service 2019-08-24T06:54:39.592847Z mysqld_safe mysqld from pid file /home/myuser/mysql/enterprise/monitor/mysql/runtime/mysqld.pid ended
. [ OK ]

Listing 10-2Changing the status of the services with mysqlmonitorctl.sh

这些步骤与前面使用service命令的例子非常相似。事实上，service 命令调用的脚本与mysqlmonitorctl.sh脚本相同，只是其中的路径和用户名取决于用来安装服务管理器的操作用户和安装路径。

添加 MySQL 实例

如果您只是想玩玩 MySQL Enterprise Monitor，您不需要做更多的事情。服务管理器的内置代理将自动监控存储库实例，因此当您第一次登录到用户界面时，已经有了可用的监控数据。如果安装了代理，代理还会自动注册它正在监控的实例。最后一个选项是从用户界面添加一个实例，这将在本节中讨论。

如果您要添加监控的 MySQL 实例与服务管理器或现有代理安装在同一台主机上，它将被自动检测到，并且页面右上方带有海豚和问号的图标将被突出显示，如图 10-14 所示。

img/484666_1_En_10_Fig14_HTML.jpg

图 10-14

一个实例显示为不受监控

请注意，在海豚的右边写着 1，在一个(黄色)圆圈里有一个问号。这是已找到但未被监控的 MySQL 实例的数量。当您将鼠标悬停在图标上时，将显示一个工具提示，其中包含未受监控的实例的数量。如果您单击海豚或数字，它会将您带到 MySQL 实例配置屏幕，您也可以通过左侧窗格中的菜单访问该屏幕。

Note

通过用户界面添加的实例将由现有代理(如果您自己没有安装任何代理，则为内置代理)监控。只有安装了代理的系统的操作系统才会受到监控。

实例配置屏幕包括添加新实例的选项、MySQL Enterprise Monitor 找到的未受监控实例的列表以及受监控实例的列表。图 10-15 显示了与开始监控新的和未被监控的实例相关的页面部分。

img/484666_1_En_10_Fig15_HTML.jpg

图 10-15

“实例配置”页面

您可以通过使用页面顶部的添加 MySQL 实例或添加批量 MySQL 实例按钮来添加对任何 MySQL 实例的监控。如果您想要监控的实例列在未监控的 MySQL 实例列表中，您也可以在那里选择它，然后单击监控实例按钮，这将带您进入与添加 MySQL 实例相同的表单，不同之处在于已知的连接设置已经预先填充。该表单有多个选项卡，其中连接设置选项卡如图 10-16 所示。

img/484666_1_En_10_Fig16_HTML.jpg

图 10-16

“添加实例”表单的“连接设置”选项卡

关于连接设置需要注意的主要问题是，您可以选择让 MySQL Enterprise Monitor 自动创建比用于设置监控的管理用户权限更少的用户。建议允许创建这些用户，因为这允许代理使用权限尽可能少的用户来执行任务。

如果您有加密要求，可以在“加密设置”选项卡中编辑这些要求。很少需要高级设置选项卡。如果要设置对多个实例的监控，您可能需要在“组设置”选项卡中为实例指定一个组。添加实例后，也可以更改这些设置。

添加实例需要一些时间。当它准备好时，您可以开始探索用户界面的其余部分。

图形用户界面

服务管理器的 Tomcat 服务器提供的用户界面是您使用 MySQL Enterprise Monitor 花费最多时间的地方。正如您已经看到的，它可以用来添加新的实例。本节将进一步深入用户界面，并讨论一般导航、指导、时间序列图和查询分析器。

一般导航

MySQL Enterprise Monitor 用户界面将特性分成逻辑组，支持按组、主机、代理或实例进行过滤。本节将简要介绍该界面，目的是当本书后面提到图表或报告时，如果您想更深入地了解它，可以在界面中找到它们。

图 10-17 显示了用户界面中页面的左上部分。在这里，您可以选择要访问的功能以及要显示哪些目标的数据。

img/484666_1_En_10_Fig17_HTML.jpg

图 10-17

MySQL 企业监控器中页面的左上角

功能导航位于左侧窗格的中央，页面顶部的两个搜索字段中应用了过滤器。屏幕截图中带有标签 Global Summaries 的搜索字段允许您选择一组实例。可以手动创建组，也可以为相互复制的实例自动创建组。全局概要是一个包含所有实例的特殊组。右侧的搜索字段允许您限制组中包括的实例、代理或主机。

这些功能包括仪表板、图表、报告等。可用功能列表取决于您应用的过滤器。菜单项包括

**概述:**这是一个高级仪表板。
**拓扑:**仅当选择了复制组时，此选项才可用。它将带您进入一个图表，该图表显示了该组的拓扑以及每个实例的复制状态。
**事件:**返回实例的监控事件报告。当顾问(稍后)设置的某些条件满足时，就会引发事件。这些事件具有不同的严重性，从通知到紧急情况不等。
**指标:**这将带您进入显示代理收集的指标的报告。无论使用哪种过滤器，时间序列图表总是可用的(但是哪个图表取决于过滤器)。对于单个实例，还有关于表统计、用户统计、内存使用、数据库文件 I/O、InnoDB 缓冲池、进程和锁等待的报告。这些报告中的几个将在后面的章节中使用。
**查询:**这是 MySQL 查询分析器，允许您调查在实例上执行了哪些查询。timeseries 图形链接到查询分析器，因此您可以从检查图形到查看在被调查的时间段内执行了哪些查询。
**复制:**复制仪表板和其他与复制相关的报告。
**备份:**MySQL 企业备份(MEB)创建的备份信息。
**配置:**配置 MySQL Enterprise Monitor 的各个方面，包括实例和顾问。
**帮助:**文档包括您已经看到的新内容，以及下载可用于故障排除的诊断报告的权限。如果您有 MySQL 支持合同，并且需要在支持票据中提供诊断，则通常会使用诊断报告。

有必要进一步解释的一个术语是顾问。

顾问

Advisor 是 MySQL Enterprise Monitor 用于定义数据收集频率、触发事件的条件以及事件严重性的规则的名称。这是一个重要的概念，您应该花一些时间来理解和配置。

获得有用的监控解决方案的最重要步骤之一是确保在正确的时间获得正确的事件(警报),但避免不必要的事件。这包括确保将每个警报设置为适当的严重性。起初，你可能认为事件越多越好，这样你就能知道发生的一切。然而，这并不是使用监控系统的最佳方式。如果您在检查事件时有许多误报，或者您在凌晨 3:00 因为一个很容易等到早上的问题被不必要的叫醒，那么您开始忽略事件，这肯定会迟早错过一个重要的事件。简而言之，你与顾问的合作应该是持续的，以不断改进他们，在“正确的”时间触发“正确的”事件。

Tip

监控工作的一个重要部分是确保监控系统触发与问题紧急程度相匹配的事件。我们的目标应该是永远不要忽略一个事件，并且总是在一个适合紧急情况的时间和方式得到提醒。

可以在左侧窗格的配置项下配置顾问。顾问按组组织，如图 10-18 所示。

img/484666_1_En_10_Fig18_HTML.jpg

图 10-18

顾问被组织成小组

每个组都包含相似类型的指导，例如，有一个性能组有 22 个指导，如过多的锁定进程和未被有效使用的索引。

默认情况下，所有指导都启用了严重性级别的阈值，这些阈值设置为在许多情况下都能正常工作的值。然而，由于没有两个系统是相同的，您需要通过展开组并点击顾问名称左侧的菜单图标来微调设置，如图 10-19 所示。

img/484666_1_En_10_Fig19_HTML.jpg

图 10-19

用于编辑指导配置的菜单

还可以使用指导项目左侧的+图标展开指导，这允许您编辑特定实例组或单个实例的指导。那个？ Info 列中的图标提供了附加信息，如评估的表达式或指导的数据源。还有图中未示出的附加信息。

时间序列图表

时间序列图是显示一段时间内指标的图表。这是所有监控解决方案的标准功能。您可以过滤要显示的图形，并更改要绘制的时间范围以及绘制样式。

图 10-20 显示了 timeseries graphs 页面的一部分，重点是访问过滤和绘图风格的控件。

img/484666_1_En_10_Fig20_HTML.jpg

图 10-20

时间序列图

图表上方是选择显示哪些图表以及图表的时间范围的选项。屏幕截图左侧的搜索字段允许您在保存的时间序列组之间进行选择。默认情况下，有一个名为 All Timeseries Graphs 的组——顾名思义——包括所有适合实例过滤的时间序列图。

您可以使用屏幕截图右上角的漏斗图标来访问时间序列图表的选项。这将打开一个框架，允许您选择要显示的图表和要覆盖的时间范围。

每个图形下方的两个小按钮允许您在使用折线图和堆叠图模式之间切换。该屏幕截图显示了顶部图形中的堆叠模式和下部图形中的线条模式的示例。线路模式是默认模式。您还可以使用字段左侧的滑块(不包括在屏幕截图中)在保存的图表组之间进行选择，来更改图表的高度。

当您将鼠标悬停在图表上方时，图表上方的三个图标会变得可见，并允许您以 CSV 格式导出图表数据、以 PNG 图像打开图表或移动图表，从而允许您根据自己的需要对图表进行重新排序。在这种情况下，如果有两个组合在一起的图形，控件将应用于这两个图形。

更改图表时间范围的另一种方法是突出显示感兴趣的图表部分，并放大该部分。这还允许您转到查询分析器，检查在此期间执行了哪些查询。图 10-21 显示了一个在图表中突出显示时间帧的例子。

img/484666_1_En_10_Fig21_HTML.jpg

图 10-21

选择时间序列图的一部分

请注意，在突出显示区域的右上方，有三个图标来控制如何处理选择。框中的 X 放弃选择，数据库圆柱体在查询分析器中打开所选时间范围的图形，放大镜缩放时间序列图形以使用所选时间范围。

查询分析器

查询分析器是一个使 MySQL Enterprise Monitor 从其他监控解决方案中脱颖而出的特性。它允许您查看在给定时间段内对实例执行了哪些查询，这在调查性能问题时是非常宝贵的。

查询分析器页面分为三个区域。在顶部可以访问过滤选项，然后可以选择一个或多个图表，页面的其余部分是语句列表。图 10-22 显示了一个例子。

img/484666_1_En_10_Fig22_HTML.jpg

图 10-22

查询分析器

顶部的下拉框显示截图中的所有语句，允许您选择要SHOW语句的语句类型。默认情况下包括所有语句。右边是配置视图按钮，它带您到一个页面，您可以在这里配置查询分析器页面应该如何配置。这包括要覆盖的时间范围、要显示的图表、过滤选项以及每个语句要包含的信息。

默认情况下，查询分析器包括查询响应时间索引(QRTi)的图表。查询响应时间索引的定义以及如何使用将在第 19 章中介绍，在该章中，查询分析器用于寻找优化的候选对象。

MySQL 企业监控器之旅到此结束。我们鼓励您自己进一步探索用户界面。

摘要

本章简要介绍了 MySQL Enterprise Monitor，目的是让您安装它并监控一个 MySQL 实例。首先，讨论了体系结构和原理的概述。MySQL Enterprise Monitor 包含一个服务管理器，数据在其中聚合，您可以通过用户界面访问监控系统。对主机和实例的监控由代理完成。服务管理器中有一个内置的代理，您可以在 MySQL 实例的主机上安装其他代理。

概述之后是下载和安装说明。由于 MySQL Enterprise Monitor 是一款纯商业产品，您可以从 Oracle 软件交付云或 My Oracle Support 下载。使用安装程序完成安装。本章介绍了如何使用服务管理器安装程序的图形用户界面。

启动和停止服务管理器的基础是将其作为服务安装。在 Linux 和 Unix 上，您也可以将服务管理器安装为非 root 用户，在这种情况下，可以从安装目录中直接调用service命令使用的相同脚本。

添加要监控的实例有两种主要方式。如果安装代理来监控实例，代理将注册该实例。您还可以从服务管理器的用户界面添加实例。

最后，我们快速浏览了一下服务管理器的图形用户界面。重点是过滤您看到的实例数据和特性列表、时间序列图和查询分析器。这些特性中的几个将在本书的剩余部分用来演示监控。

下一章将介绍在后面章节中用到的另一个有用的工具:MySQL Workbench。

MySQL8-查询性能调优教程-二-