菜单
探索 Prisma

MySQL / 读取和查询数据

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

type 列告诉您 MySQL 如何在查询中实现任何连接操作。MySQL 文档对 可用的不同连接类型以及它们如何影响性能有很好的描述。

type: const

例如，类型为 const 将非常快，因为它意味着 MySQL 正在将唯一列与常量值进行比较，这最多应返回单行。

type: eq_ref

eq_ref 类型 类似地是用于组合两个表的最快连接操作，因为它表明查询使用完整的唯一、非空索引值进行比较。

type: index

index 类型 在需要全表扫描时使用，但查询可以扫描索引树而不是扫描实际表。这使得它在查询范围内性能相对较低，但仍然在某种程度上进行了优化，因为它可以在较小的索引而不是实际表上进行扫描。

type: ref

ref 连接类型 表示 MySQL 能够使用索引的前导列进行比较（当整个索引包含多个未用于比较的列时）。这意味着 MySQL 即使不匹配整个比较，也能够有效地使用部分索引。

解释 key 列

key 列及其相关列（possible_keys、key_length 和 ref）帮助您了解哪些索引可用，使用了哪些索引以及索引使用效率如何。

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

possible_keys 列 告诉我们哪些索引可用于正在连接的表。它很有用，因为它向我们展示了 MySQL 在尝试查找适合满足查询的索引时从什么开始的。如果您在非主键上连接表，这可能是需要查看的地方，以确保您拥有可用于加速该过程的索引。

key 列 本身告诉您 MySQL 决定使用哪个索引来运行查询。在某些情况下，这甚至可能包括未在 possible_keys 列中列出的结果。例如，当索引包含查询要求的所有列时，可以比关联的表更有效地扫描它，即使该索引不适合（并且不用于）实际连接。

key_length 列 指示实际使用了多少索引。对于多列索引，这可以帮助您了解在此特定比较中使用了多少存储索引。ref 列 也在此处发挥作用，它告诉您具体与特定索引进行比较的内容。这可以帮助您确定创建更优化的索引是否是个好主意。

解释评估的行数

rows 和 filtered 列很有用，因为它们告诉我们正在检查的表的范围。

         rows: 1
     filtered: 100.00

rows 列 给出了 MySQL 认为它必须评估才能执行查询每个部分的行数。此处的数字受我们讨论过的所有先前列的影响。它向我们指示了给定提供的语句和可用索引，MySQL 可以多么有效地精简整个表。

filtered 列 估计在应用查询中的任何行过滤条件后，将返回的评估行的百分比。如果值为 100，则表示不会发生行过滤。低于 100 的值表示由于连接条件或其他过滤，某些评估的行将不会返回。

重写慢查询以加快执行速度

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

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

创建额外的索引

提高查询执行速度的最直接方法之一是确保您的表具有适当的索引。您的索引应反映您查询数据的最常见方式，并且可能会随着您的使用演变或实现其他查询而随时间变化。

要创建新索引，您可以使用 CREATE INDEX 语句

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

您可以创建许多不同类型的索引，具体取决于您要索引的列的数据类型、值的最大长度以及您要使用的索引结构。如果您的查询已在使用索引但索引性能不佳，则这些选项可能值得研究。但是，首先，您可以通过主要使用默认值来保持相对简单。

您需要做的第一个考虑是您想要索引哪些列。如果您经常在特定列上进行连接，那么确保您的索引中考虑了这些列非常重要。索引主要用于行查找，因此连接操作（其中匹配不同表中的各种行）在没有索引的情况下非常昂贵。

如果单列完全封装了您通常查询表的方式，则可以使用单列创建索引。您还可以对多列进行索引，以使索引更有用。如果对多列进行索引，则排序非常重要。第一列应该是查找匹配项的主要方式，而任何其他列都应帮助您优化这些匹配项。

例如，如果您为表 person 创建 first_name 和 last_name 的索引，您几乎肯定希望使用如下内容定义索引：

CREATE INDEX last_and_first_names ON person (last_name, first_name);

在这里，last_name 列是索引中的第一列或最左边的列，这意味着它可以快速查找 Alan Smith 或所有 Smith 条目。但是，它对于查找所有 Alan 实例无效，除非我们预计经常仅按名字查询，否则这可能没问题。

虽然创建索引可以帮助您提高数据集的读取性能，但重要的是不要过度索引表。索引会影响写入速度，因为每次在表中记录新值时，还必须更新与其关联的所有索引。如果表的主键与将对其使用的查询模式非常匹配，那么表的效果最佳，因为它允许您从强大的索引性能中受益，而无需大量补充索引来适应不同的访问模式。

限制候选行

您可以加快查询速度的另一种方法是在可能的情况下限制语句中候选行和返回行的数量。这是一个非常特定于上下文的建议，因此在许多情况下它并不适用，但是如果您可以减少 MySQL 必须评估的行数，则可以显着提高性能。

您可以使用 LIMIT 子句 来做到这一点。例如，要仅返回五个结果，您可以使用如下格式：

SELECT * FROM <table> LIMIT 5

在许多情况下（取决于查询中包含的其他子句），限制返回的行数可以帮助 MySQL 短路查询过程，比正常情况下更早停止。这有助于尽早节省时间，而不是在整个数据集上执行，然后在稍后截断结果。

这在连接多个表时尤其有用。在任何可能的情况下，最好在连接发生之前限制查询的结果。这允许 MySQL 评估少量数据以进行其余连接，而不是评估第一个表的全部数据。

仅选择相关字段

提高查询性能的另一种方法是注意您实际返回的列。这可以帮助从许多不同的方面加快结果速度。

限制您选择的列影响性能的主要方式是通过最小化网络负载。通过返回您不需要的列，较大的查询会显着增加查询生成的网络流量。仅选择满足查询逻辑约束所需的列有助于通过减少在网络上传输的内容来防止此问题。

选择性地选择返回的列可以影响性能的另一种方式与索引有关。如果 MySQL 能够将您返回的所有列与索引关联起来，则它可能会对那个更小、更有组织的数据集而不是整个表进行操作。当您的索引与您的查询模式良好匹配时，限制您查询的列允许这种情况发生。

删除与前导通配符的比较

可能对查询性能产生负面影响的一件事是过度依赖通配符。在 SQL 中，% 字符在比较中用作通配符，表示可以替换任何值。这是一种在记录中查找数据的非常强大的方法，但它可能会对查询性能产生非常大的影响。

对于前导或前缀通配符尤其如此。前导通配符是检查列值是否与以通配符开头的模式匹配的比较。例如，检查列是否与 %Main St. 匹配。这执行得特别糟糕，因为 MySQL 无法使用索引来发现匹配的结果。相反，即使该列具有索引，它也必须在整个表中查找匹配项。

在这个特定的例子中，如果您需要定期按街道名称查询以查找该街道上的所有地址，那么将街道名称和街道号码拆分为两个单独的列可能更有意义。这将允许您使用索引快速查询“Main St.”上的地址，然后在需要查找特定地址时根据需要过滤这些结果。首先使用街道名称，其次使用街道号码列创建复合索引将使此操作更快。

结论

在本指南中，我们介绍了评估单个查询以及获取有关可能导致查询速度变慢的更多信息的一些基本方法。我们讨论了分析查询，并使用 MySQL 的 EXPLAIN 语句来获取有关查询计划器如何评估查询的详细信息。然后，我们看了一些方法，您可以通过修改查询以更好地与数据库检索数据的方式对齐，从而避免这些速度减慢的情况。

数据库是复杂的软件，负责处理大量可以采用多种不同格式的数据。除了简单地检索必要的数据之外，人们还期望数据库能够快速返回结果。MySQL 的查询计划器和优化器机制中内置了无数的优化措施，以帮助最大限度地减少回答查询所需的时间。

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

在 PRISMA.IO 上的相关内容

如果您正在将 Prisma 与您的 MySQL 数据库一起使用，您可以阅读文档的查询优化部分，了解如何优化您的查询。这将帮助您了解在使用 Prisma 时，各种查询结构如何影响您的数据库性能。

Prisma 是一个用于 Typescript 和 Node.js 的开源数据库工具包，旨在使应用程序开发人员在处理数据库时更高效和更有信心。
关于作者
Justin Ellingwood

Justin Ellingwood

自 2013 年以来，Justin 一直在撰写关于数据库、Linux、基础设施和开发者工具的文章。他目前与妻子和两只兔子住在柏林。他通常不必以第三人称写作，这对所有相关方来说都是一种解脱。
在 GitHub 上编辑此页

Prisma 数据指南

一个不断增长的文章库，专注于使数据库更易于理解。

用 ❤️ 由 Prisma 制作