利用 Insights 进行查询性能分析

分析数据库性能是一项复杂的任务。即使你的数据库运行良好，且大部分查询速度很快，一些缓慢或索引不当的查询模式也可能会让用户感到沮丧，甚至在数据集和流量不断增长时引发更大的问题。
为了帮助开发人员识别和排查有问题的查询，PlanetScale Insights 现在能够按查询模式显示时间序列指标。

查询模式

Insights 的分析基本单位是“查询模式”，因此值得探讨 Insights 如何定义这一概念以及这么做的原因。众所周知，关系型数据库接收 SQL 语句形式的查询，例如：

SELECT * FROM users WHERE id = 123

由于 PlanetScale 数据库往往每秒接收成千上万甚至数百万条查询，为每条查询单独报告性能统计数值不太现实。我们希望能够对相似的查询进行归类，并查看一段时间的汇总数据。例如，了解“过去一小时内基于 id 的用户查询用了多长时间”，比了解“查询用户 123 用了多长时间”更加有意义。
为确定哪些查询应该归为一组，我们使用 Vitess 的 SQL 解析器为每条查询找到一种标准化模式。Vitess 查询服务层将 SQL 转换为抽象语法树（AST），并替换其中的字面量为通用占位符。对上面的查询应用这个过程，我们得到以下标准化表示：

SELECT * FROM users WHERE id = ?

除了字面量的替换，Vitess 的 AST 标准化还能消除表面上的语法差异，例如大小写或多余括号的存在。有了标准化查询，我们可以计算其指纹（已标准化 SQL 的哈希值），并用它对查询进行归类，聚合生成的遥测数据包括查询次数、总执行时间、读取、返回和写入数据的总行数等。我们还生成查询执行时间的概要，从而可以显示误差范围内的百分位（例如，中位数和第 95 百分位）。

时间序列查询数据的实际应用

为了了解这些数据的实际用法，让我们看一个 PlanetScale 工程师在主要生产数据库中排查查询问题的案例。
作为常规维护的一部分，我们会定期查看生产数据库中最耗时和执行次数最多的查询。在这个过程中，我们注意到以下查询，该查询会从一个相当大的表中删除过期行的块。在 PlanetScale Insights 的主页面中，这条查询平均耗时约 8 秒。

统计表展示查询指标

点击该查询后，我们可以看到过去 24 小时内性能（中位数和第 95 百分位延迟）的图表。查看查询延迟的中位数和第 95 百分位数据，我们发现一种有趣的模式：

查询图表显示高延迟

删除过期数据的后台作业在每小时的第 10 分钟启动。在启动后的前 10 分钟（10–20 分钟区间），平均和第 95 百分位延迟都相对较低——仅几百毫秒。然而，在接下来的 10 分钟区间（20–30 分钟），执行时间激增至接近 15 分钟，这显然还有改进的空间！
问题的查询是带有 WHERE 子句和限制（LIMIT）的 DELETE 操作，限制条件指定只删除前 n 行。这一限制的目的是避免长时间运行删除操作，以免对其他查询产生影响。在我们的案例中，限制设置为 500。当每小时运行开始时，WHERE 子句匹配的行很多，因此找到前 500 个匹配项相对较快。但随着作业进行，越来越多符合条件的行已经被删除，从而导致找到可删除行的成本提高。在每小时运行接近尾声时，这条查询接近每次操作都需要进行完全表扫描。由于表中有超过 1 亿行，这个操作变得异常昂贵。
那么我们如何加速这一过程？添加索引！
在 minute 列上添加索引可以让数据库快速识别并删除符合 WHERE 子句条件的行。由于我们使用的是 PlanetScale，可以轻松添加索引，而在构建索引期间不会影响性能或导致停机。即使表非常繁忙且有数亿行数据，这也是可行的。
在打开部署请求（添加索引）、获得团队审批并点击“部署更改”后，数据库开始生成索引。索引构建完成后，我们在查询延迟图表上看到一个部署标记，标记为 #505——这是我们对该数据库的第 505 次部署请求。从图表上可以确认查询延迟显著减少，甚至图表显示数据已经降至零：