当数据库出现问题时，捕获运行系统上的查询是一项棘手的任务。现有解决方案，例如tcpdump、SHOW PROCESSLIST;或第三方监控代理，可能会导致额外的系统负载或仅能够收集一小部分查询。

从今天开始，所有PlanetScale数据库分支可以无需产生任何性能开销，自动追踪针对其执行的每条SQL查询统计信息。通过使用支持PlanetScale数据库的Vitess技术，我们不仅可以追踪每条查询的执行次数、返回的行数，还能够记录每条查询的执行时间。通过这些数据库洞察信息，您可以调试查询并对应用进行优化，或快速采用无阻塞的模式变更来部署新索引。

作为初始策略，我们将运行时间超过100毫秒的查询标记为优化候选查询。

详细了解如何使用查询统计信息，请参阅我们的文档。

调试慢查询

在开发这一功能期间，我们在PlanetScale的生产数据库中发现了一些查询需要额外的索引以提高完成速度。我们成功将数据库备份功能中的一个查询平均运行时间减少了98%。首先让我们看看数据库备份功能的工作方式，然后深入分析慢查询问题。

每个数据库分支每天至少会进行一次备份到外部存储。每日备份在成功完成后会替换前一天的备份，从而确保我们始终保留每个分支数据的最新副本。

删除每日备份需要两步：

  1. 标记备份记录为删除（软删除），通过设置其deleted_at字段时间戳。
  2. 删除备份在外部存储中的数据文件。

我们会定期运行一个后台任务，以找到需要删除的过期每日备份。该任务分批运行以下查询：

SQL1select * from backup where deleted_at is null and expires_at <= :vtg1 order by id asc limit :vtg2

这里:vtg1是一个由Vitess提供的替换参数。实际查询如下所示：

SQL1select * from backup where deleted_at is null and expires_at <= current_timestamp order by id asc limit 1000
优化SQL查询——通过查询统计提升性能插图

使用查询统计报告，我们发现执行此查询平均耗时719毫秒，用于查找需要删除的过期备份。虽然这个慢查询运行在后台任务中而不是Web请求周期内，因此不会直接影响用户体验，但它确实耗费了过多时间，占用了工作节点和数据库资源。接下来我们来分析该查询为何会如此缓慢。

使用查询统计优化慢查询

我们认为这个查询本应更快，因为我们对(expires_at, data_deleted_at, deleted_at)字段创建了复合索引。然而，MySQL的执行计划显示，查询优化器选择了对表的全表扫描，而不是使用此索引。

执行计划如下所示：

SQL1> explain select * from backup where deleted_at is null and expires_at <= current_timestamp order by id asc limit 1000\G;
2*************************** 1. row ***************************
3        id: 1
4  select_type: SIMPLE
5        table: backup
6   partitions: NULL
7         type: ALL
8possible_keys: index_backup_on_expires_at_and_data_deleted_at_and_deleted_at
9          key: NULL
10      key_len: NULL
11          ref: NULL
12         rows: <100s of thousands—every row in the table>
13     filtered: 5.00
14        Extra: Using where

对数据的几个观察揭示了为何当前索引并不理想，并指向了一个更具选择性的索引，可显著缩小结果集：

  1. 97%的备份记录已过期，因此通过expires_at < current_timestamp查询的选择性很低。
  2. 97.5%的记录是软删除状态，因此查询deleted_at is not null的选择性很高。

回头来看，这在处理每日备份替换前一天备份的场景下是合理的。但对大多数表来说，这种工作负载表现是不正常的，情况恰好相反：绝大部分记录是“活动的”而不是软删除的。我们通常将deleted_at作为复合索引的尾键，但对备份表来说这并不合适。

如果我们调整索引键的顺序为(deleted_at, expires_at, data_deleted_at)，那么查询将具有很高的选择性。它可以先从记录集中排除已删除的行，然后在剩余可能过期的少量行中搜索。

新的索引对应的查询执行计划显示，查询优化器确实选择了该索引，并估算它只需访问单行即可提供结果。

SQL1> explain select * from backup where deleted_at is null and expires_at <= current_timestamp order by id asc limit 1000\G;
2*************************** 1. row ***************************
3           id: 1
4  select_type: SIMPLE
5        table: backup
6   partitions: NULL
7         type: range
8possible_keys: index_backup_on_deleted_at_and_expires_at_and_data_deleted_at
9          key: index_backup_on_deleted_at_and_expires_at_and_data_deleted_at
10      key_len: 17
11          ref: NULL
12         rows: 1
13     filtered: 100.00
14        Extra: Using index condition; Using filesort

结论

通过结合使用PlanetScale查询统计与MySQL执行计划优化索引，我们成功将后台任务中耗时719毫秒的查询降低至不足20毫秒。随着表大小的增长，全表扫描性能会进一步下降，这种查询最终可能会耗费数据库内的过多资源，从而影响为Web请求提供服务的其他查询。通过优化该查询，我们确保了这一过程永远不会影响用户体验。


优化SQL查询——通过查询统计提升性能插图1

关注公众号：程序新视界，一个让你软实力、硬技术同步提升的平台

除非注明，否则均为程序新视界原创文章，转载必须以链接形式标明本文链接

本文链接：http://www.choupangxia.com/2025/05/20/sql/