在 MySQL 中分析和优化慢查询

简介

维护数据库系统的性能是优化数据库支持应用程序响应速度的重要组成部分。这涉及管理服务器的硬件资源和软件配置，但也与你要求它执行的查询有关。

在之前的指南中，我们检查了如何在 MySQL 中识别性能低下和慢查询。在本指南中，我们将通过讨论如何评估和优化运行缓慢的查询来迈出下一步。这将建立在之前文章的讨论基础上，为您提供一个完整的策略来识别和解决与性能不佳的查询相关的問題。

启用查询分析以了解慢查询阶段

尝试优化慢查询时，您可能想要做的第一件事是分析查询以找出查询到底是在哪个地方花费了最长时间。

首先，通过输入以下内容检查查询分析是否已启用

SELECT @@PROFILING;

 +-------------+
 | @@profiling |
 +-------------+
 |           0 |
 +-------------+
 1 row in set, 1 warning (0.00 sec)

0 表示分析目前已禁用。您可以通过输入以下内容启用分析

SET PROFILING = 1;

启用分析后，您可以通过输入以下内容开始列出可用的分析信息

SHOW PROFILES;

 +----------+------------+--------------------------+
 | Query_ID | Duration   | Query                    |
 +----------+------------+--------------------------+
 |        1 | 3.00368075 | select sleep(3)          |
 |        2 | 0.00740700 | select * from mysql.user |
 |        3 | 0.00075875 | select @@profiling       |
 +----------+------------+--------------------------+
 3 rows in set, 1 warning (0.00 sec)

输出将向您展示自启用分析以来，当前会话中执行的最新的语句。每个查询都显示了总执行时间，并有一个可以用于深入了解的查询编号。

要显示最新查询的分析信息，请输入

SHOW PROFILE;

 +----------------------+----------+
 | Status               | Duration |
 +----------------------+----------+
 | starting             | 0.000144 |
 | checking permissions | 0.000020 |
 | Opening tables       | 0.000024 |
 | init                 | 0.000013 |
 | optimizing           | 0.000047 |
 | executing            | 0.000032 |
 | end                  | 0.000010 |
 | query end            | 0.000016 |
 | closing tables       | 0.000012 |
 | freeing items        | 0.000367 |
 | cleaning up          | 0.000074 |
 +----------------------+----------+
 11 rows in set, 1 warning (0.00 sec)

默认情况下，输出将向您展示语句在查询处理的每个阶段花费的确切时间。这可以帮助您准确地识别查询中性能不佳的部分。

要显示与其他查询相关的结果，您可以添加 FOR QUERY <N> 子句以及 SHOW PROFILES 输出提供的查询 ID。

例如，要获取查询 1 的默认显示，您可以输入

SHOW PROFILE FOR QUERY 1;

 +----------------------+----------+
 | Status               | Duration |
 +----------------------+----------+
 | starting             | 0.001419 |
 | checking permissions | 0.000028 |
 | Opening tables       | 0.000030 |
 | init                 | 0.000020 |
 | optimizing           | 0.000020 |
 | executing            | 0.000025 |
 | User sleep           | 3.000165 |
 | end                  | 0.000043 |
 | query end            | 0.000019 |
 | closing tables       | 0.000012 |
 | freeing items        | 0.000540 |
 | logging slow query   | 0.001320 |
 | cleaning up          | 0.000041 |
 +----------------------+----------+
 13 rows in set, 1 warning (0.00 sec)

在这里，输出清楚地显示了在 User sleep 状态下花费了很长时间，该状态直接管理执行的功能。

您还可以更改输出以显示不同类型的信息。这在您查看了默认输出后通常很有帮助，以便检查更具体的信息。

例如，您可以通过输入以下内容查看有关第一个查询的块输入和输出的信息

SHOW PROFILE BLOCK IO FOR QUERY 1;

 +----------------------+----------+--------------+---------------+
 | Status               | Duration | Block_ops_in | Block_ops_out |
 +----------------------+----------+--------------+---------------+
 | starting             | 0.001419 |          184 |             0 |
 | checking permissions | 0.000028 |            0 |             0 |
 | Opening tables       | 0.000030 |            0 |             0 |
 | init                 | 0.000020 |            0 |             0 |
 | optimizing           | 0.000020 |            0 |             0 |
 | executing            | 0.000025 |            0 |             0 |
 | User sleep           | 3.000165 |            0 |             0 |
 | end                  | 0.000043 |            0 |             0 |
 | query end            | 0.000019 |            0 |             0 |
 | closing tables       | 0.000012 |            0 |             0 |
 | freeing items        | 0.000540 |            0 |             0 |
 | logging slow query   | 0.001320 |           64 |             8 |
 | cleaning up          | 0.000041 |            0 |             0 |
 +----------------------+----------+--------------+---------------+
 13 rows in set, 1 warning (0.00 sec)

除了以前的信息之外，这还显示了 MySQL 必须与文件系统上的块数据交互的时间。

您可以指定的可用输出变体包括

• ALL: 显示所有可用信息
• BLOCK IO: 显示查询生成的输入和输出块数
• CONTEXT SWITCHES: 显示执行查询期间发生的主动和被动上下文切换次数
• CPU: 显示用户空间和系统类别中的 CPU 时间
• IPC: 显示发送和接收的进程间消息数量
• PAGE FAULTS: 显示页面错误的数量，细分为重大错误和轻微错误
• SOURCE: 显示 MySQL 中执行的实际函数，以及它们所在的源文件和行号，以允许执行映射
• SWAPS: 显示查询所需的交换次数

要显示与查询相关的所有可能信息，请输入

SHOW PROFILE ALL FOR QUERY 1\G

我们使用 \G 语句终止序列触发备用输出格式，使结果更易于阅读，因为显示了许多额外的输出。

在使用分析信息对查询的哪个部分速度慢有一个很好的了解后，您可以关闭分析以节省资源

SET PROFILING = 0;

使用 EXPLAIN 了解查询性能

查询分析应该帮助您了解查询执行阶段中最耗时的阶段以及与查询相关的资源。然后，您可以使用 MySQL 的 EXPLAIN 语句来了解查询优化器对查询的评估。

EXPLAIN 语句接受一个 SELECT、DELETE、UPDATE、INSERT 或 REPLACE 语句，并显示查询优化器将如何评估和执行给定的查询。输出显示了 MySQL 将如何连接表、选择字段、排序和过滤结果等。它还显示了诸如将评估多少行以及将使用哪些索引来加速过程等内容。

要了解此语句的工作原理，请要求 MySQL 解释它将如何执行查询 SELECT * FROM INFORMATION_SCHEMA.VIEWS;

EXPLAIN SELECT * FROM information_schema.views\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cat
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: name
      key_len: 194
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: vw
   partitions: NULL
         type: ref
possible_keys: schema_id,type,view_client_collation_id,view_connection_collation_id,type_2
          key: type
      key_len: 1
          ref: const
         rows: 100
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: sch
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,catalog_id
          key: PRIMARY
      key_len: 8
          ref: mysql.vw.schema_id
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: conn_coll
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.vw.view_connection_collation_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: client_coll
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,character_set_id
          key: PRIMARY
      key_len: 8
          ref: mysql.vw.view_client_collation_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: cs
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.client_coll.character_set_id
         rows: 1
     filtered: 100.00
        Extra: NULL
6 rows in set, 1 warning (0.01 sec)

上面的输出显示了需要六个独立的查询才能将该查询的信息整合在一起。如果你查看表定义，你可以看到原因

SHOW CREATE TABLE information_schema.views\G

为格式化修改后，生成的表创建命令如下

CREATE algorithm=undefined definer=`mysql.infoschema`@`localhost` SQL security definer view `views`
AS
  SELECT `cat`.`name`                                                                                                             AS `table_catalog`,
         `sch`.`name`                                                                                                             AS `table_schema`,
         `vw`.`name`                                                                                                              AS `table_name`,
         IF((can_access_view(`sch`.`name`,`vw`.`name`,`vw`.`view_definer`,`vw`.`options`) = TRUE),`vw`.`view_definition_utf8`,'') AS `view_definition`,
         `vw`.`view_check_option`                                                                                                 AS `check_option`,
         `vw`.`view_is_updatable`                                                                                                 AS `is_updatable`,
         `vw`.`view_definer`                                                                                                      AS `definer`,
         IF((`vw`.`view_security_type` = 'DEFAULT'),'DEFINER',`vw`.`view_security_type`)                                          AS `security_type`,
         `cs`.`name`                                                                                                              AS `character_set_client`,
         `conn_coll`.`name`                                                                                                       AS `collation_connection`
  FROM   (((((`mysql`.`tables` `vw`
  JOIN   `mysql`.`schemata` `sch`
  ON    ((
                       `vw`.`schema_id` = `sch`.`id`)))
  JOIN   `mysql`.`catalogs` `cat`
  ON    ((
                       `cat`.`id` = `sch`.`catalog_id`)))
  JOIN   `mysql`.`collations` `conn_coll`
  ON    ((
                       `conn_coll`.`id` = `vw`.`view_connection_collation_id`)))
  JOIN   `mysql`.`collations` `client_coll`
  ON    ((
                       `client_coll`.`id` = `vw`.`view_client_collation_id`)))
  JOIN   `mysql`.`character_sets` `cs`
  ON    ((
                       `cs`.`id` = `client_coll`.`character_set_id`)))
  WHERE  ((
                       0 <> can_access_table(`sch`.`name`,`vw`.`name`))
         AND    (
                       `vw`.`type` = 'VIEW'))

有五个连接语句，这意味着将需要查询六个独立的表才能构建结果。这与我们在执行的 EXPLAIN 语句中返回的条目数量一致——每个表一个。

要解释 EXPLAIN 语句的结果，您需要了解每个单独的列代表什么。特别要注意 type、key、rows 和 filtered 列。

type: const

type: eq_ref

type: index

type: ref

. . .
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.client_coll.character_set_id
. . .

         rows: 1
     filtered: 100.00

重写缓慢的查询以更快地执行

现在我们已经讨论了 MySQL 允许您评估单个语句性能的一些方法，我们可以谈谈如何提高这种性能。

您需要考虑的确切步骤在很大程度上取决于您在查询分析和 EXPLAIN 调查中发现的内容。我们将介绍以下一些优化查询性能的最常见方法。

CREATE INDEX <index_name> ON <table> (<columns_to_index>)

CREATE INDEX last_and_first_names ON person (last_name, first_name);

SELECT * FROM <table> LIMIT 5

结论

在本指南中，我们介绍了一些评估单个查询和获取有关可能导致其变慢的更多信息的简单方法。我们讨论了查询分析，并使用 MySQL 的 EXPLAIN 语句来获取有关查询计划程序如何评估查询的详细信息。然后，我们研究了一些通过修改查询使其更好地与数据库检索数据的方式相一致来避免这些延迟的方法。

数据库是负责对大量可能以多种不同格式存在的数据进行操作的复杂软件。除了简单地检索必要的数据之外，还期望数据库能够快速返回结果。MySQL 的查询计划程序和优化程序机制中内置了无数优化，以帮助最大限度地减少回答查询所需的时间。

但是，数据库只能在有限的范围内自动优化。作为数据库的用户，您还需要了解被访问的数据、对查询的要求以及哪些查询最需要优化。为此，重要的是了解如何评估您的标准以及在查询性能未达到预期水平时如何进行调整。