主持人 > 博客 > 管理 > 使用面向构建者的 B2B 服务示例优化数据库查询

使用面向构建者的 B2B 服务示例优化数据库查询

如何在不迁移到生产力更高的服务器的情况下将数据库查询数量增加 10 倍并保持系统功能？我将告诉你我们如何应对数据库性能下降的情况，如何优化 SQL 查询以服务尽可能多的用户而不增加计算资源的成本。

我提供管理建筑公司业务流程的服务。大约有 3 家公司与我们合作。每天有超过 10 人使用我们的系统工作 4-10 小时。它解决了规划、通知、警告、验证的各种问题......我们使用PostgreSQL 9.6。我们的数据库中有大约 300 个表，每天收到多达 200 亿条查询（10 万个不同的查询）。平均每秒有 3-4 个请求，在最活跃的时刻每秒有超过 10 个请求。大多数查询都是 OLAP。增删改查的次数少很多，OLTP负载相对较轻。我提供了所有这些数字，以便您可以评估我们项目的规模并了解我们的经验对您有多大用处。

图一。抒情的

当我们开始开发时，我们并没有真正考虑数据库会承受什么样的负载，以及如果服务器停止拉取我们会做什么。在设计数据库时，我们遵循一般建议，尽量不要搬起石头砸自己的脚，但超出了一般建议，例如“不要使用模式” 实体属性值 我们没有进去。我们基于规范化的原则进行设计，避免数据冗余并且不关心加速某些查询。第一批用户到达后，我们就遇到了性能问题。和往常一样，我们对此完全没有准备。事实证明，第一个问题很简单。通常，所有问题都可以通过添加新索引来解决。但曾经有一段时间，简单的补丁不再起作用。意识到我们缺乏经验，并且越来越难以理解导致问题的原因，我们聘请了专家来帮助我们正确设置服务器、连接监控，并向我们展示从哪里获取 统计.

图二。统计

因此，我们每天在数据库上执行大约 10 个不同的查询。在这10万个中，有执行2-3百万次、平均执行时间为0.1-0.3毫秒的怪物，也有平均执行时间为30秒、每天被调用100次的查询。

优化所有 10 个查询是不可能的，因此我们决定弄清楚我们的努力方向，以便正确提高数据库的性能。经过几次迭代，我们开始对请求进行类型划分。

最高请求数

这些是占用最多时间（总时间）的最重查询。这些查询要么经常调用，要么需要很长时间才能执行（长而频繁的查询在速度斗争的第一次迭代中进行了优化）。因此，服务器在执行上花费的时间最多。此外，重要的是按总执行时间和 IO 时间分开顶级请求。优化此类查询的方法略有不同。

所有公司的惯常做法都是与 TOP 请求合作。它们很少；即使优化一个查询也可以释放 5-10% 的资源。然而，随着项目的成熟，优化 TOP 查询变得越来越重要。所有简单的方法都已经想出来了，最“重”的请求“只”占用3-5%的资源。如果 TOP 查询总共花费的时间少于 30-40%，那么您很可能已经努力让它们快速运行，是时候继续优化下一组查询了。
仍有待回答该组中应包含多少个热门查询的问题。我通常至少取10个，但不超过20个。我尽量保证TOP组中第一个和最后一个的时间相差不超过10倍。也就是说，如果查询执行时间从第 1 位急剧下降到第 10 位，那么我就取 TOP-10，如果下降更缓慢，那么我将组大小增加到 15 或 20。
使用面向构建者的 B2B 服务示例优化数据库查询

中农

这些都是紧随 TOP 之后发出的请求，最后 5-10% 除外。通常，优化这些查询有机会大大提高服务器性能。这些请求的权重可能高达 80%。但即使他们的份额已经超过了 50%，那么也是时候更仔细地审视他们了。

尾巴

如前所述，这些查询出现在最后，占用 5-10% 的时间。仅当您不使用自动查询分析工具时，您才可以忘记它们，然后优化它们也可以很便宜。

如何评价各组？

我使用一个 SQL 查询来帮助对 PostgreSQL 进行这样的评估（我确信可以为许多其他 DBMS 编写类似的查询）

用于估计 TOP-MEDIUM-TAIL 组大小的 SQL 查询

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

查询的结果是三列，每列包含处理该组查询所花费的时间百分比。在请求中，有两个数字（在我的例子中是 20 和 800）将一组请求与另一组请求分开。

这是优化工作开始时和现在的请求份额的粗略比较。

使用面向构建者的 B2B 服务示例优化数据库查询

从图中可以看出，TOP请求的占比急剧下降，但“中农”却有所增加。
起初，TOP 请求包含明显的错误。随着时间的推移，儿童疾病消失了，TOP 请求的比例下降了，必须付出越来越多的努力来加快困难请求的速度。

为了获取请求的文本，我们使用以下请求

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

以下是帮助我们加快 TOP 查询速度的最常用技术的列表：

  • 重新设计系统，例如，使用消息代理重新设计通知逻辑，而不是定期查询数据库
  • 添加或更改索引
  • 将 ORM 查询重写为纯 SQL
  • 重写惰性数据加载逻辑
  • 通过数据反规范化进行缓存。例如，我们有一个表连接Delivery -> Invoice -> Request -> Application。也就是说，每次交付都通过其他表与应用程序关联。为了不链接每个请求中的所有表，我们在交付表中复制了指向该请求的链接。
  • 在程序内存中缓存带有参考书的静态表和很少更改的表。

有时，这些更改相当于令人印象深刻的重新设计，但它们提供了 5-10% 的系统负载，并且是合理的。随着时间的推移，排气管变得越来越小，需要越来越认真的重新设计。

然后我们把目光转向第二类诉求——中农群体。里面的查询还有很多，看起来要花很多时间来分析整个组。然而，大多数查询的优化都非常简单，并且许多问题以不同的变体重复了数十次。以下是我们应用于数十个类似查询的一些典型优化的示例，每组优化查询将数据库卸载 3-5%。

  • 开始使用 EXISTS，而不是使用 COUNT 和全表扫描来检查记录是否存在
  • 摆脱了 DISTINCT（没有通用的方法，但有时你可以通过将请求加速 10-100 倍来轻松摆脱它）。

    例如，代替查询从大型交货表 (DELIVERY) 中选择所有驱动程序 

    SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID

    对相对较小的表 PERSON 进行查询

    SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
FROM PERSON
WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)

    看起来我们使用了相关子查询，但它的加速速度超过了 10 倍。

  • 在许多情况下，COUNT 被完全放弃并且
    改为近似值计算
  • 而不是 
    UPPER(s) LIKE JOHN%’

    使用 

    s ILIKE “John%”

每个特定请求有时会加速 3-1000 倍。尽管性能令人印象深刻，但起初我们认为优化需要 10 毫秒才能完成的查询是没有意义的，它是第三百个最繁重的查询之一，并且占用整个数据库加载时间的百分之一。但通过将相同的方法应用于一组相同类型的查询，我们赢回了几个百分点。为了不浪费时间手动检查所有数百个查询，我们编写了几个简单的脚本，使用正则表达式来查找相同类型的查询。因此，自动搜索查询组使我们能够通过适度的努力进一步提高性能。

因此，三年来我们一直在开发相同的硬件。日均负载约为30%，高峰时可达70%。请求数量以及用户数量增加了大约 10 倍。所有这一切都归功于对这些相同组的 TOP-MEDIUM 请求的持续监控。一旦 TOP 组中出现新的请求，我们立即对其进行分析并尝试加快速度。我们使用查询分析脚本每周检查一次 MEDIUM 组。如果我们遇到已经知道如何优化的新查询，我们会快速更改它们。有时我们会发现可以同时应用于多个查询的新优化方法。

根据我们的预测，当前的服务器将承受用户数量再增长3-5倍。确实，我们还有一张王牌 - 我们仍然没有按照建议将 SELECT 查询传输到镜像。但我们并不是有意识地这样做，因为我们想先彻底耗尽“智能”优化的可能性，然后再打开“重炮”。
对已完成的工作进行批判性审视可能会建议使用垂直缩放。购买更强大的服务器，而不是浪费专家的时间。服务器可能不会花费那么多，特别是因为我们还没有耗尽垂直扩展的限制。然而，仅请求数量增加了10倍。几年来，系统的功能不断增加，现在请求的类型也更多了。由于缓存，现有的功能可以通过更少的请求和更高效的请求来执行。这意味着您可以安全地再乘以 5 以获得真实的加速系数。所以，根据最保守的估计，我们可以说加速度是50倍甚至更多。垂直摆动服务器的成本将增加 50 倍。特别是考虑到一旦进行优化，它就一直有效，并且每个月都会收到租用服务器的账单。

来源： habr.com

添加评论