识别和分析问题 MySQL 查询

发表于： 2025年9月14日 2025年9月14日
分类： mysql, PlanetScale
标签： mysql

虽然我们尽力避免，但在实际工作中效率低下的查询可能会悄悄混入工作负载之中，进而对数据库系统性能造成负面影响。这种情况在大型数据库环境中尤为常见，比如数据库包含数十 GB 或 TB 的数据、数百个表以及每天执行数千种查询模式。
幸运的是，MySQL 能够收集数据来识别问题查询，并对其进行分析以深入探讨性能下降的原因。本文将介绍在原生 MySQL 中如何使用几种内置技术做到这一点。如果你使用 PlanetScale，这些信息可以通过 PlanetScale Insights 仪表板更轻松直观地获取。本文稍后会简要讨论这一功能。

示例数据架构

以下是本文使用的示例架构：
示例架构图
本文使用一段模拟工作负载作为测试数据，接下来你会看到一些针对这些表执行的查询。

开启 `performance_schema`

如果你的应用程序执行了大量查询，可能很难知道从哪里开始找问题。如何确定哪些查询表现不佳？一种方法是手动在数据库 shell 中运行查询并检查其执行时间，也可以使用浏览器测试你的 Web 应用以找出加载缓慢的页面或请求。然而，还有更系统化的方法去识别这些问题查询。
你可以利用 performance_schema 数据库中的表信息来识别这些问题。首先切换到 performance_schema 数据库：

USE performance_schema;

接着确保 performance_schema 功能处于启用状态。默认情况下它是开启的，但如果主机 MySQL 的内存不足，可能会被手动关闭，因为它所有追踪的信息都存储于内存中的 PERFORMANCE_SCHEMA 存储引擎。如果启用，运行以下命令将看到类似结果：

SHOW VARIABLES LIKE 'performance_schema';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

确定慢查询

接下来是找出需要解决的慢查询。你可以使用 performance_schema 和 sys 数据库中的表利用以下几种技术识别问题查询。值得注意的是，performance_schema 数据库中包含许多表：

SELECT COUNT(*)
  FROM information_schema.tables
  WHERE table_schema = 'performance_schema';

+----------+
| COUNT(*) |
+----------+
| 113      |
+----------+

（你的版本中表的数量可能有所不同）。
一个好的起点是查看 events_statements_summary_by_digest 表提供的信息。以下是通过 avg_timer_wait 列获取平均执行时长最高的一行示例：

SELECT * 
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'game'
ORDER BY avg_timer_wait DESC LIMIT 1 \G;

***************************[ 1. row ]***************************
SCHEMA_NAME                 | game
DIGEST                      | 8ee55a42fea07da3d5e786413a6c1bd...
DIGEST_TEXT                 | SELECT `p1` . `username` , `m` ...
COUNT_STAR                  | 2486
SUM_TIMER_WAIT              | 425862156000000
MIN_TIMER_WAIT              | 149411000000
AVG_TIMER_WAIT              | 171304165000
MAX_TIMER_WAIT              | 380467000000
SUM_LOCK_TIME               | 1306000000
...

其中，SUM_TIMER_WAIT 和 AVG_TIMER_WAIT 等列是评估执行代价最高查询的好参考。上述结果中的时间单位是**皮秒**，因此需要除以 **1万亿**（转换为秒）。例如，这里的平均值 AVG_TIMER_WAIT 为 0.17 秒，此查询一共执行了 2486 次（基于 COUNT_STAR 值）。
除了 performance_schema，sys 数据库也包含许多帮助识别慢查询的有用统计信息。例如你可以查看 statements_with_sorting、statements_with_runtimes_in_95th_percentile 和 statements_with_full_table_scans 等表。以下为按 rows_examined_avg 列筛选执行时长最高的查询示例：

SELECT substring(query,1,50), avg_latency, rows_examined_avg
FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY rows_examined_avg DESC LIMIT 10;

+----------------------------------------------------+-------------+-------------------+
| substring(query,1,50)                              | avg_latency | rows_examined_avg |
+----------------------------------------------------+-------------+-------------------+
| SELECT `alias` FROM `chat` . `message` LIMIT ?     | 2.13 s      | 10000000          |
| SELECT `alias` FROM `message` LIMIT ?              | 881.20 ms   | 2500004           |
| EXPLAIN ANALYZE SELECT `p1` .  ... ` WHERE `m` . ` | 223.97 ms   | 1081990           |
| SELECT `p1` . `username` , `m` ... ` WHERE `m` . ` | 57.75 ms    | 421446            |
...

如果你更关注表层面的统计数据而不是直接分析查询，可以从行读取角度查看表的表现。以下是一个示例，按索引和非索引读取统计：

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'game' AND object_name = 'message';

+---------------+-------------+------------+------------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR |
+---------------+-------------+------------+------------+
| game          | message     | PRIMARY    | 0          |
| game          | message     | to_id      | 0          |
| game          | message     | from_id    | 164500     |
| game          | message     | <null>     | 2574002473 |
+---------------+-------------+------------+------------+

从结果中可以看出，超过 20 亿行读取未通过索引匹配（INDEX_NAME 为 <null> 的行）。解决方案包括：为此表添加索引，或优化查询，或者两者同时进行。
此外，sys 数据库中还有其他一些有趣的统计表，例如 statements_with_full_table_scans，能帮助统计每个查询执行的全表扫描次数：

USE sys;
SELECT query, db, exec_count, total_latency
FROM sys.statements_with_full_table_scans
ORDER BY exec_count DESC LIMIT 5;

+-------------------------------------------------------------------+------+------------+---------------+
| query                                                             | db   | exec_count | total_latency |
+-------------------------------------------------------------------+------+------------+---------------+
| SELECT `class` , `size` FROM `spaceship` WHERE `size` > ?         | game | 8422       | 26.65 s       |
| SELECT `p1` . `username` , `m` ... ` WHERE `m` . `created_at` > ? | game | 6742       | 6.45 min      |
...

这些查询触发了大量全表扫描，在示例数据库中，除了默认主键索引外没有其他索引，因此触发了所有查询的全表扫描。

使用 EXPLAIN 检查查询

通过识别问题查询后，接下来可以使用 EXPLAIN 或 EXPLAIN ANALYZE 深入检查查询的执行情况。例如以下示例：

EXPLAIN ANALYZE SELECT p1.username, m.to_id, p2.username, m.from_id
FROM message m
LEFT JOIN player p1 ON m.to_id = p1.id
LEFT JOIN player p2 ON m.from_id = p2.id
WHERE m.created_at > '2020-10-10 00:00:00';

生成详细的执行成本与查询计划信息：

+--------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop left join  (cost=333272 rows=332036) (actual time=0.515..320 rows=345454 loops=1)                                           |
|     -> Nested loop left join  (cost=217059 rows=332036) (actual time=0.475..240 rows=345454 loops=1)                                       |
|         -> Filter: (m.created_at > TIMESTAMP'2020-10-10 00:00:00')  (cost=100846 rows=332036) (actual time=0.153..188 rows=345454 loops=1) |
|             -> Table scan on m  (cost=100846 rows=996208) (actual time=0.148..159 rows=1e+6 loops=1)                                       |
|         -> Single-row index lookup on p1 using PRIMARY (id=m.to_id)  (cost=0.25 rows=1) (actual time=54.5e-6..70.9e-6 rows=1 loops=345454) |
|     -> Single-row index lookup on p2 using PRIMARY (id=m.from_id)  (cost=0.25 rows=1) (actual time=137e-6..153e-6 rows=1 loops=345454)     |
+--------------------------------------------------------------------------------------------------------------------------------------------+

任何表扫描或具有高成本的节点需要进一步优化，比如通过添加索引或优化查询语句。

使用 `performance_schema` 对查询进行探测

除了 EXPLAIN 的基本功能外，还可以对查询执行的每个阶段进行时间分析，具体步骤如下：

确保 performance_schema 的探测与消费者启用：
配置历史记录，比如仅针对某个用户开启：
执行查询并获取历史记录：

最终可以看到时间拆分数据：

+------------------------------------------------+----------------------------------+--------------+
| event_name                                     | source                           | milliseconds |
+------------------------------------------------+----------------------------------+--------------+
| stage/sql/starting                             | init_net_server_extension.cc:110 | 0.2400       |
| stage/sql/executing                            | sql_union.cc:1676                | 735.3020     |
...

利用 PlanetScale Insights

虽然 MySQL 提供了很多功能来深挖问题查询，但获取这些信息可能会比较繁琐。PlanetScale Insights 能更轻松地可视化这些数据，例如让你快速找到慢查询并评估是否需要添加索引，从而更直观地监控你的数据库性能。
PlanetScale Insights 提供了一系列有用的统计信息与自动异常检测，使你能专注于优化查询以及开发软件，更高效地工作。

识别和分析问题 MySQL 查询插图