分析和优化 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'))

有五个 JOIN 语句，这意味着必须查询六个单独的表来构建结果。这与我们执行的 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 的查询规划器和优化器机制中内置了无数优化，以帮助最大限度地减少回答查询所需的时间。

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