mysql-sql调优利器 Performance Schema 与Explain (v8.0.x)

序言

在进行编写sql 查询的过程中，时常会遇到查询慢，但是又不知道是什么原因，往往上网找很久，别人的问题貌似跟我们遇到的很像，但是相同的解决方案确怎么也解决不了我们的问题，所以mysql 给我们提供了两种非常好的对于sql性能的判断工具，能够让我们快速的定位到查询慢的原因，帮助我们更好的解决问题，话不多说，正片开始!

Performance Schema （性能模式）

show-profile官网地址：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

我们进入mysql 官方文档中查询 show-profile，会看到上述的一段话：

不推荐使用 SHOW PROFILE 和 SHOW PROFILES 语句；期望它们在未来的 MySQL 版本中被删除。改用性能模式；请参阅第 27.19.1 节，“使用性能模式进行查询分析”。

所以如果你打算继续使用show-profile 来进行性能分析，请前去官网学习。

---

Preformance Schema官网地址：https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html

---

使用性能模式进行查询分析

performance_schema.setup_actors表

setup_actors 表可用于限制按主机、用户或帐户收集历史事件，以减少运行时开销和历史表中收集的数据量。该示例的第一步显示了如何将历史事件的收集限制为特定用户。

mysql> SELECT * FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+

• HOST:主机名。这应该是一个字面名称，或“%”表示“任何主机”。
• USER:用户名。这应该是一个字面名称，或“%”表示“任何用户”。
• ROLE:没用过。
• ENABLED:是否为该行匹配的前台线程启用检测。值为 YES 或 NO。
• HISTORY:是否记录该行匹配的前台线程的历史事件。值为 YES 或 NO。

---

1. 更新 setup_actors 表中的默认行以禁用所有前台线程的历史事件收集和监视，并插入一个新行以启用运行查询的用户的监视和历史事件收集：

mysql> UPDATE performance_schema.setup_actors
       SET ENABLED = 'NO', HISTORY = 'NO'
       WHERE HOST = '%' AND USER = '%';

mysql> INSERT INTO performance_schema.setup_actors
       (HOST,USER,ROLE,ENABLED,HISTORY)
       VALUES('主机名','用户名','%','YES','YES');

主机和用户名就是这里写的，现在这里先不阐述这样的做的目的是为什么，不理解接着看。
这里以我本地为主，我的主机名是localhost,用户名是root

mysql> SELECT * FROM performance_schema.setup_actors;
+-----------+-----------+------+---------+---------+
| HOST      | USER      | ROLE | ENABLED | HISTORY |
+-----------+-----------+------+---------+---------+
| %         | %         | %    | NO      | NO      |
| localhost | root      | %    | YES     | YES     |
+-----------+-----------+------+---------+---------+

2. 通过更新 setup_instruments 表确保启用语句和阶段检测。

mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%statement/%';

mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%stage/%';

3. 通过更新 setup_consumers表,确保启用 events_statements_* 和 events_stages_* 消费者。

mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_statements_%';

mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_stages_%';

4. 在您正在监视的用户帐户下，运行您要分析的语句。例如：

select * from test;

5. 通过查询 events_statements_history_long 表来识别语句的 EVENT_ID。此步骤类似于运行 SHOW PROFILES 来识别 Query_ID。以下查询产生类似于 SHOW PROFILES 的输出：

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
       FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like 'select * from test';
			

这里的SQL_TEXT 写我们刚才写的sql语句就可以;
这里为什么要除以1000000000000,是因为

Performance Schema 以皮秒（万亿分之一秒）为单位显示事件计时器信息，以将计时数据标准化为标准单位。在以下示例中，TIMER_WAIT 值除以 1000000000000 以显示以秒为单位的数据。值也被截断到小数点后 6 位，以便以与 SHOW PROFILES 和 SHOW PROFILE 语句相同的格式显示数据。

结果：

发现时间有点长；
6, 查询 events_stages_history_long 表以检索语句的阶段事件。阶段使用事件嵌套链接到语句。每个阶段事件记录都有一个 NESTING_EVENT_ID 列，其中包含父语句的 EVENT_ID。

SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
       FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=2802;

可以看到主要花费时间的是执行过程中。

SELECT * FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=2802;

我们来看看完整的字段：

• THREAD_ID、EVENT_ID：事件启动时与事件关联的线程和线程当前事件编号。 THREAD_ID 和 EVENT_ID 值一起唯一标识行。没有两行具有相同的一对值。
• END_EVENT_ID：此列在事件开始时设置为 NULL，并在事件结束时更新为线程当前事件编号。
• EVENT_NAME：产生事件的工具的名称。这是 setup_instruments 表中的 NAME 值。仪器名称可能有多个部分并形成层次结构，如第 27.6 节“性能模式仪器命名约定”中所述。
• SOURCE：包含产生事件的检测代码的源文件的名称以及检测发生的文件中的行号。这使您能够检查源代码以确定所涉及的确切代码。
• TIMER_START、TIMER_END、TIMER_WAIT：事件的时间信息。这些值的单位是皮秒（万亿分之一秒）。 TIMER_START 和 TIMER_END 值指示事件计时开始和结束的时间。 TIMER_WAIT 是事件经过的时间（持续时间）。如果事件尚未完成，则 TIMER_END 是当前计时器值，TIMER_WAIT 是到目前为止经过的时间（TIMER_END - TIMER_START）。如果事件由 TIMED = NO 的仪器产生，则不收集计时信息，并且 TIMER_START、TIMER_END 和 TIMER_WAIT 都为 NULL。有关以皮秒为单位的事件时间和影响时间值的因素的讨论，请参阅第 27.4.1 节，“性能模式事件计时”。
• WORK_COMPLETED、WORK_ESTIMATED：这些列提供阶段进度信息，用于已实施以生成此类信息的工具。 WORK_COMPLETED 表示该阶段已经完成了多少工作单元，WORK_ESTIMATED 表示该阶段预期有多少工作单元。有关更多信息，请参阅舞台活动进度信息。
• NESTING_EVENT_ID：嵌套此事件的事件的 EVENT_ID 值。阶段事件的嵌套事件通常是语句事件。
• NESTING_EVENT_TYPE：嵌套事件类型。值为 TRANSACTION、STATEMENT、STAGE 或 WAIT。

Explain

Explain 官网地址：https://dev.mysql.com/doc/refman/8.0/en/explain.html

---

DESCRIBE 和 EXPLAIN 语句是同义词。在实践中，DESCRIBE 关键字更常用于获取有关表结构的信息，而 EXPLAIN 用于获取查询执行计划（即 MySQL 如何执行查询的说明）。
以下讨论根据这些用途使用 DESCRIBE 和 EXPLAIN 关键字，但 MySQL 解析器将它们视为完全同义词。

---

获取表结构信息

DESCRIBE demo;

---

DESCRIBE 是 SHOW COLUMNS 的快捷方式。这些语句还显示视图信息。 SHOW COLUMNS 的描述提供了有关输出列的更多信息。请参阅第 13.7.7.5 节，“SHOW COLUMNS 语句”。

默认情况下，DESCRIBE 显示有关表中所有列的信息。 col_name，如果给定，是表中列的名称。在这种情况下，该语句仅显示命名列的信息。如果给定，wild 是一个模式字符串。它可以包含 SQL % 和 _ 通配符。在这种情况下，该语句仅显示名称与字符串匹配的列的输出。除非字符串包含空格或其他特殊字符，否则无需将字符串括在引号内。

提供 DESCRIBE 语句是为了与 Oracle 兼容。

SHOW CREATE TABLE、SHOW TABLE STATUS 和 SHOW INDEX 语句还提供有关表的信息。请参阅第 13.7.7 节，“SHOW 语句”。

---

获取执行计划信息

EXPLAIN 语句提供有关 MySQL 如何执行语句的信息：

• EXPLAIN 适用于 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句。在 MySQL 8.0.19 及更高版本中，它也适用于 TABLE 语句。
• 当 EXPLAIN 与可解释语句一起使用时，MySQL 显示来自优化器的有关语句执行计划的信息。也就是说，MySQL 解释了它将如何处理该语句，包括有关表如何连接以及以何种顺序连接的信息。有关使用 EXPLAIN 获取执行计划信息的信息，请参阅第 8.8.2 节，“EXPLAIN 输出格式”。
• 当 EXPLAIN 与 FOR CONNECTION connection_id 一起使用而不是可解释的语句时，它将显示在命名连接中执行的语句的执行计划。请参阅第 8.8.4 节“获取命名连接的执行计划信息”。
• 对于可解释的语句，EXPLAIN 生成可以使用 SHOW WARNINGS 显示的附加执行计划信息。请参见第 8.8.3 节，“扩展 EXPLAIN 输出格式”。
• EXPLAIN 对于检查涉及分区表的查询很有用。请参阅第 24.3.5 节，“获取有关分区的信息”。
• FORMAT 选项可用于选择输出格式。 TRADITIONAL 以表格格式显示输出。如果没有 FORMAT 选项，这是默认设置。 JSON 格式以 JSON 格式显示信息。在 MySQL 8.0.16 及更高版本中，TREE 提供了比传统格式更精确的查询处理描述的树状输出；它是唯一显示散列连接使用情况的格式（参见第 8.2.1.4 节，“散列连接优化”）并且始终用于 EXPLAIN ANALYZE。

EXPLAIN 需要执行解释语句所需的相同权限。此外，EXPLAIN 还需要任何解释视图的 SHOW VIEW 权限。 EXPLAIN … FOR CONNECTION 如果指定的连接属于不同的用户，则还需要 PROCESS 权限。

在 EXPLAIN 的帮助下，您可以看到应该在哪里为表添加索引，以便通过使用索引查找行来更快地执行语句。您还可以使用 EXPLAIN 检查优化器是否以最佳顺序连接表。要提示优化器使用与 SELECT 语句中表命名顺序相对应的连接顺序，请以 SELECT STRAIGHT_JOIN 而不仅仅是 SELECT 开始该语句。 （参见第 13.2.10 节，“SELECT 语句”。）

优化器跟踪有时可能会提供与 EXPLAIN 的信息互补的信息。但是，优化器跟踪格式和内容可能会因版本而异。有关详细信息，请参阅 MySQL 内部：跟踪优化器。

如果在您认为应该使用索引时遇到索引未使用的问题，请运行 ANALYZE TABLE 来更新表统计信息，例如键的基数，这可能会影响优化器所做的选择。请参阅第 13.7.3.1 节，“ANALYZE TABLE 语句”。

MySQL Workbench 具有 Visual Explain 功能，可提供 EXPLAIN 输出的可视化表示。请参阅教程：使用 Explain 提高查询性能。

通过 EXPLAIN ANALYZE 获取信息

MySQL 8.0.18 引入了 EXPLAIN ANALYZE，它运行一条语句并产生 EXPLAIN 输出以及时间和其他基于迭代器的关于优化器的期望如何匹配实际执行的信息。对于每个迭代器，提供以下信息：

• 预计执行成本cost
• 估计返回的行数rows
• 返回第一行的时间 actual time … 前面;
• 执行此迭代器（包括子迭代器，但不包括父迭代器）所花费的时间，以毫秒为单位。（当有多个循环时，此图显示每个循环的平均时间）actual time … 后面;
• 迭代器返回的行数rows
• 循环数loops

1. 查询执行信息使用 TREE 输出格式显示，其中节点表示迭代器。 EXPLAIN ANALYZE 始终使用 TREE 输出格式。在 MySQL 8.0.21 及更高版本中，可以选择使用 FORMAT=TREE; 显式指定。 TREE 以外的格式仍然不受支持。
2. EXPLAIN ANALYZE 可以与 SELECT 语句以及多表 UPDATE 和 DELETE 语句一起使用。从 MySQL 8.0.19 开始，它也可以与 TABLE 语句一起使用。
3. 从 MySQL 8.0.20 开始，您可以使用 KILL QUERY 或 CTRL-C 终止此语句。
4. EXPLAIN ANALYZE 不能与 FOR CONNECTION 一起使用。

---

(此语句输出中显示的实际时间actual time值以毫秒为单位。)

---

通过Explain 获取

参数说明：

• id：select查询编号，这个不重要
• select_type:查询类型
  simple：简单查询、没有子查询
  primary：最外面的select,在有子查询的语句中，最外面的select查询就是primary
  subquery：子查询
  union：union语句的第二个或者说是后面那一个
  union result：union的结果
  select_type 其实不是很重要。
• table： 表名，使用别名时显示别名
• type：查询性能等级类型（很重要）
  system：表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计
  const：表最多有一个匹配行，一般是根据主键查，或者唯一索引，达到常量级别，性能很好
  eq_ref：通过索引直接定位到某一行，常见于连接查询中
  ref： 通过索引，可以直接引用到某些数据行
  range：根据索引做范围扫描
  index：扫描所有的索引节点
  all：全表扫描
• possible key：可能用到的索引
  注意：系统估计可能用到几个索引，最终只能用一个。
• key：最终用到的索引
• key_len：使用的索引最大长度
• Filtered:表示返回结果的行数占需读取行数的百分比
• Extra：详细说明

Performance Schema 与Explain 的不同之处

Performance Schema 与Explain 都是用于sql语句的分析，但是explain 是从整体上进行分析，分析其执行这个查询是否使用了索引，查询过程中扫描了多少行，是否使用了临时表、是否使用文件排序等，而Performance Schema 是分析一条sql 从开始查询到结束查询 的过程中，各个环节分别使用了多少时间。

---