如何在不增加效率的服务器并使系统正常工作的情况下，在数据库查询数量下增长10倍？ 我将告诉您如何应对数据库性能下降，如何优化SQL查询以服务尽可能多的用户而不增加计算资源的成本。
我提供用于管理建筑公司业务流程的服务。 大约有3000家公司与我们合作。 每天有超过1万人使用我们的系统工作4-10个小时。 它解决了计划，警报，警告，验证的各种任务。我们使用PostgreSQL 9.6。 我们数据库中大约有300个表，每天最多有2亿个请求（10,000个不同的请求）被发送到该表。 平均而言，我们每秒有3-4千个请求，在最活跃的时刻，每秒有超过1万个请求。 大多数请求是OLAP。 增加，修改和删除的次数要少得多，即OLTP负载相对较小。 我提供了所有这些数字，以便您可以评估我们的项目范围并了解我们的经验如何对您有用。
第一张图片。 抒情的
当我们开始开发时，我们并没有真正考虑过什么样的负载将落在数据库上，以及如果服务器停止拉动该怎么办。 在设计数据库时，我们遵循一般建议，并尝试不让自己措手不及，但是除了诸如“不使用
实体属性值”模式之类的一般提示外，我们没有去做。 基于规范化原理设计，可避免数据冗余，并且不关心加速某些查询。 第一批用户到达后，我们就遇到了性能问题。 和往常一样，我们对此完全没有准备。 第一个问题很简单。 通常，一切都是通过添加新索引来决定的。 但是有一段时间，简单的补丁停止工作了。 意识到没有足够的经验并且越来越难以理解问题的根源之后，我们聘请了专家来帮助我们正确设置服务器，连接监控，显示要查找的位置以获取
统计信息 。
第二张图片。 统计的
因此，我们每天在数据库中执行大约1万个不同的查询。 在这1万个怪物中，有2到3百万次的平均运行时间为0.1-0.3毫秒，而有30秒钟的平均运行时间的查询每天被称为100次。
无法优化所有一万个查询，因此我们决定找出从何处着手以正确地提高数据库性能。 经过几次迭代，我们开始将请求划分为多个类型。
TOP查询
这些是耗时最多（总时间）的最困难的查询。 这些查询要么经常被调用，要么是花费很长时间的查询（即使在为提高速度而进行的第一次迭代中，长时间和频繁查询也被优化了）。 结果，服务器总共花费了最多的时间执行它们。 此外，重要的是按总执行时间和IO时间分开最重要的请求。 优化此类查询的方式略有不同。
所有公司的通常做法是处理TOP请求。 它们很少，即使对一个请求进行优化也可以释放5-10％的资源。 但是，随着项目的变老，优化TOP查询已成为一项日益艰巨的任务。 所有简单的方法都已经解决了，即使是最“困难”的请求也只能占用“ 3-5％”的资源。 如果TOP查询总共花费不到30-40％的时间，那么很可能您已经在努力使它们快速运行，现在该着手优化下一组查询了。
仍然需要回答这个组中包含多少个顶级查询的问题。 我通常不少于10次，但不超过20次。我尝试确保TOP组中第一次和最后一次的时间相差不超过10次。 也就是说，如果查询执行时间从1个地方急剧下降到10个地方，那么我采用TOP-10，如果下降更平稳，那么我将组大小增加到15或20。
中农（中）
这些都是在TOP之后立即执行的所有请求，最后5-10％除外。 通常，在优化这些特定请求时，可以大大提高服务器性能。 这些查询可以“权衡”多达80％。 但是，即使它们的份额已超过50％，也该是更仔细地研究它们的时候了。
尾巴
就像说的那样，这些请求最终结束了，并且花费了5-10％的时间。 仅当您不使用自动查询分析工具时，您才可以忘记它们，因此它们的优化也很便宜。
如何评估每个小组？
我使用一个SQL查询来帮助对PostgreSQL进行这样的评估（我敢肯定，对于许多其他DBMS，您可以编写类似的查询）
SQL查询以估计TOP-MEDIUM-TAIL组的大小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）将一组请求与另一组分开。
这就是优化开始时的请求比例现在如何大致相关。
该图显示，TOP请求的份额急剧下降，但是“中农”却在增长。
最初，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查询速度的最常用技巧：
- 重新设计系统，例如，在消息代理上处理通知逻辑，而不是定期数据库查询
- 添加或修改索引
- 用纯SQL重写ORM查询
- 重写惰性数据加载逻辑
- 通过数据非规范化进行缓存。 例如，我们在表格交付->发票->请求->请求之间有一个链接。 即，每个交付都通过其他表与应用程序关联。 为了不链接每个请求中的所有表，我们在“交付”表中复制了到应用程序的链接。
- 用目录缓存静态表，很少在程序存储器中更改表。
有时，这些更改拖延了令人印象深刻的重新设计，但这些更改占系统卸载的5-10％，因此是合理的。 随着时间的流逝，废气变得越来越少，重新设计的需求也越来越严重。
然后，我们提请注意第二组请求-中农组。 它有更多的请求，并且似乎要花费很多时间来分析整个团队。 但是，大多数查询对于优化来说非常简单，并且许多问题以不同的形式重复了数十次。 以下是一些我们对数十个类似查询应用的典型优化示例，每组优化查询将数据库卸载了3-5％。
- 代替使用COUNT和全表扫描检查记录是否存在，开始使用EXISTS。
- 我们摆脱了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毫秒，包含在最繁重的查询的第300个查询中，而在数据库的总加载时间中却花费了百分之百。 但是将相同的配方应用于一组相似的请求，我们赢了百分之几。 为了不浪费时间手动查看所有数百个查询，我们编写了几个简单的脚本，这些脚本使用正则表达式查找相似的查询。 结果，对查询组的自动搜索使我们能够通过不费吹灰之力进一步提高性能。
结果，我们已经在同一硬件上工作了三年。 日平均负载约为30％，高峰时可达70％。 请求的数量以及用户的数量增长了大约10倍。 所有这一切都要归功于对这批TOP-MEDIUM查询的持续监视。 一旦新请求出现在TOP组中，我们将立即对其进行分析并尝试加快处理速度。 我们每周使用查询分析脚本审查MEDIUM组。 如果您遇到了我们已经知道如何进行优化的新请求，那么我们会迅速对其进行更改。 有时，我们发现可以同时应用于多个查询的新优化方法。
根据我们的预测，当前服务器将承受的用户数量再增加3-5倍。 没错，我们的保护套中还有另一个王牌；我们仍然没有按照建议将SELECT查询转换为镜像。 但是，我们并不是有意识地这样做的，因为我们首先要在启用“重型火炮”之前完全耗尽“智能”优化的可能性。
对完成的工作进行严格的审查可能会建议使用垂直缩放。 购买功能更强大的服务器，而不是浪费专家的时间。 服务器的成本可能不会很高，特别是因为垂直扩展的限制尚未耗尽。 但是，仅请求数增加了10倍。 几年来，系统的功能不断增强，现在有各种各样的请求。 由于缓存的功能由更少的请求执行，而且请求效率更高。 因此，您可以安全地再乘以5以获得真实的加速度系数。 因此，根据最保守的估计，我们可以说加速度是50倍或更多倍。 垂直摇动服务器50次会花费更多。 特别要考虑的是，一直进行优化后，每月都会有租用服务器的账单。