提高分层 SQL 结构的性能 sql

?## 上下文
我和我的团队最近在一个拥有数百万页面的足球迷网站上工作。该网站的想法是成为足球支持者的权威资源，尤其是在投注方面。数据库和[应用程序架构]不是特别复杂。这是因为调度程序负责定期重新计算复杂数据并将其存储在表中，这样查询就不必涉及[SQL 聚合]。因此，真正的挑战在于[非功能性需求]，例如性能和页面加载时间。
应用领域体育行业有多个数据提供者，每个提供者都为其客户提供不同的数据集。具体来说，足球行业有四种类型的数据：

传记数据：身高、宽度、年龄、他们效力的球队、获得的奖杯、获得的个人奖项以及足球运动员和教练。
历史数据：过去比赛的结果和那些比赛中的事件，如进球、助攻、黄牌、红牌、传球等。
当前和未来数据：当前赛季的比赛结果和这些比赛中发生的事件，以及未来比赛的表格。
实时数据：正在进行的游戏的实时结果和实时事件。

我们的网站涉及所有这些类型的数据，特别关注出于 SEO 原因的历史数据和支持投注的实时数据。
分层表结构由于我签署了NDA ，我无法与您分享整个数据结构。同时，了解足球赛季的结构就足以了解这种现实情况。
详细地说，足球提供商通常按如下方式组织赛季中的比赛数据：

季节：有开始和结束日期，通常持续一个日历年
比赛：比赛所属的联赛。竞争的一个实例存在于一个赛季中。[在此处]解有关足球比赛如何运作的更多信息。
阶段：与比赛相关的阶段（例如，资格赛阶段、淘汰赛阶段、决赛阶段）。每场比赛都有自己的规则，很多比赛只有一个阶段。
组：与阶段相关的组（例如，A 组、B 组、C 组……）。一些比赛，例如世界杯，涉及不同的团体，每个团体都有自己的球队。大多数比赛只有一个通用组适用于所有团队。
转：从逻辑的角度来看，对应于一天的比赛。它通常持续一周，涵盖了属于一个小组的所有球队的比赛（例如，MLS 有 17 场主场比赛和 17 场客场比赛；因此，它有 34 个回合）。
比赛：两支足球队之间的比赛。

如下图[ER 模式]所示，这 5 个表代表了一个分层数据结构：
【提高分层 SQL 结构的性能】
技术、规格和性能要求我们使用Express 4.17.2和 Sequelize 6.10作为 ORM（对象关系映射）在 Node.js 和 TypeScript 中开发后端。前端是使用 TypeScript 开发的 Next.js 12应用程序。至于数据库，我们决定选择由 AWS 托管的 Postgres 服务器。
该网站在AWS Elastic Beanstalk上运行，前端有 12 个实例，后端有 8 个实例，目前每天有 1k 到 5k 的查看者。我们客户的目标是在一年内达到每天 60k 的浏览量。因此，该网站必须准备好在不降低性能的情况下托管数百万月度用户。
该网站应在Google Lighthouse测试中的性能、SEO 和可访问性方面得分 80+ 。此外，加载时间应始终小于 2 秒，理想情况下为数百毫秒。真正的挑战在这里，因为该网站包含超过 200 万个页面，并且预渲染它们都需要数周时间。此外，大多数页面上显示的内容都不是静态的。因此，我们选择了增量静态再生方法。当访问者点击一个没有人访问过的页面时，Next.js 会使用从后端公开的 API 检索到的数据生成它。然后，Next.js 将页面缓存 30 或 60 秒，具体取决于页面的重要性。
因此，后端必须快速为服务器端生成过程提供所需的数据。
为什么查询分层表很慢现在让我们看看为什么分层表结构会带来性能挑战。
JOIN 查询很慢
分层数据结构中的一个常见场景是，您希望根据与层次结构中较高对象关联的参数过滤叶子。例如，您可能想要检索在特定赛季中进行的所有比赛。由于叶表Game不直接连接到Season，因此您必须执行一个涉及与层次结构中的元素一样多的 JOIN 的查询。
因此，您最终可能会编写以下查询：

SELECT GA.* FROM `Game` GA LEFT JOIN `Turn` T on GA.`turnId` = T.`id` LEFT JOIN `Group` G on T.`groupId` = G.`id` LEFT JOIN `Phase` P on G.`phaseId` = P.`id` LEFT JOIN `Competition` C on P.`competitionId` = C.`id` LEFT JOIN `Season` S on C.`seasonId` = S.`id` WHERE S.id = 5

这样的查询很慢。每个 JOIN 都会执行一次笛卡尔积运算，这需要时间并且可能会产生数千条记录。因此，分层数据结构越长，性能就越差。

此外，如果您想检索所有数据而不仅仅是表中的Game列，由于笛卡尔积的性质，您将不得不处理数千行和数百列。这可能会变得混乱，但这就是 ORM 发挥作用的地方。
ORM数据解耦和转换需要时间
通过 ORM 查询数据库时，您通常对检索应用程序级表示中的数据感兴趣。原始数据库级别表示在应用程序级别可能没有用。因此，当大多数高级 ORM 执行查询时，它们会从数据库中检索所需数据并将其转换为应用程序级表示。这个过程包括两个步骤：数据解耦和数据转换。
在幕后，来自 JOIN 查询的原始数据首先被解耦，然后在应用程序级别转换为相应的表示。因此，在处理所有数据时，具有数百列的数千条记录成为一小组数据，每个数据都具有数据模型类中定义的属性。因此，包含从数据库中提取的原始数据的数组将成为一组Game对象。每个Game对象都有一个包含其各自Turn实例的转弯场。然后，该Turn对象将有一个组字段存储其各自的Group对象等。
生成这种转换后的数据是您愿意接受的开销。处理凌乱的原始数据具有挑战性，并且会导致代码异味。另一方面，这个幕后发生的过程需要时间，你不能忽视它。当原始记录有数千行时尤其如此，因为处理存储数千个元素的数组总是很棘手。
换句话说，分层表结构的常见 JOIN 查询在数据库和应用程序层都很慢。
列传播作为一种解决方案解决方案是以分层结构将列从父级传播到其子级，以避免此性能问题。让我们来了解一下原因。
为什么应该在分层数据库上传播列
在分析上面的 JOIN 查询时，很明显问题在于在叶子表上应用了过滤器Game。您必须遍历整个层次结构。但是既然 Game 是层次结构中最重要的元素，为什么不直接在其中添加seasonId、competitionId、phaseId和groupId列呢？这就是列传播的意义所在！
通过将外部键列直接传播给子项，您可以避免所有的 JOIN。您现在可以将上面的查询替换为以下查询：

SELECT * FROM `Game` GA WHERE GA.seasonId = 5

可以想象，这个查询比原来的查询快得多。此外，它会直接返回您感兴趣的内容。因此，您现在可以忽略 ORM 数据解耦和转换过程。

请注意，列传播涉及数据重复，您应该谨慎而明智地使用它。但在深入研究如何优雅地实现它之前，让我们看看应该传播哪些列。
如何选择要传播的列如果您向下传播层次结构中较高的实体的每一列，这将有所帮助；这在过滤方面可能很有用。例如，这涉及外部密钥。此外，您可能希望传播用于过滤数据的[枚举列]或生成包含来自父级的聚合数据的列，以避免 JOIN。
列传播的前 3 种方法当我的团队选择列传播方法时，我们考虑了三种不同的实现方法。让我们一一分析。
1. 创建物化视图
我们必须在层次表结构中实现列传播的第一个想法是创建具有所需列的物化视图。物化视图存储查询的结果，它通常表示复杂查询的行和/或列的子集，例如上面介绍的 JOIN 查询。
当涉及到具体化查询时，您可以定义何时生成视图。然后，您的数据库负责将其存储在磁盘上并使其像普通表一样可用。即使生成查询可能很慢，您也只能少量启动它。因此，物化视图代表了一种快速的解决方案。
另一方面，物化视图并不是处理实时数据的最佳方法。这是因为物化视图可能不是最新的。它存储的数据取决于您决定何时生成视图或刷新它。此外，涉及大数据的物化视图会占用大量磁盘空间，这可能会带来问题并花费您的存储成本。
2. 定义虚拟视图
另一种可能的解决方案是使用虚拟视图。同样，虚拟视图是存储查询结果的表。与物化视图的不同之处在于，这一次您的数据库不会将查询结果存储在磁盘上，而是将其保存在内存中。因此，虚拟视图始终是最新的，从而解决了实时数据的问题。
另一方面，每次访问视图时，数据库都必须执行生成查询。所以，如果生成查询需要时间，那么涉及到视图的整个过程不能不慢。虚拟视图是一个强大的工具，但考虑到我们的性能目标，我们不得不寻找另一种解决方案。
3. 使用触发器
[SQL 触发器]允许您在数据库中发生特定事件时自动启动查询。换句话说，触发器使您能够跨数据库同步数据。因此，通过在层次结构表中定义所需的列并让自定义触发器更新它们，您可以轻松实现列传播。
可以想象，触发器会增加性能开销。这是因为每次他们等待的事件发生时，您的数据库都会执行它们。但是执行查询需要时间和内存。因此，触发器是有代价的。另一方面，这种成本通常可以忽略不计，尤其是与虚拟或物化视图带来的缺点相比时。
触发器的问题是定义它们可能需要一些时间。同时，您只能处理此任务一次，并在需要时对其进行更新。因此，触发器允许您轻松优雅地实现列传播。此外，由于我们采用了列传播并使用触发器实现了它，因此我们已经设法大大满足了客户定义的性能要求。
视图带来的缺点相比时。
触发器的问题是定义它们可能需要一些时间。同时，您只能处理此任务一次，并在需要时对其进行更新。因此，触发器允许您轻松优雅地实现列传播。此外，由于我们采用了列传播并使用触发器实现了它，因此我们已经设法大大满足了客户定义的性能要求。
层次结构在数据库中很常见，如果处理不当，可能会导致应用程序出现性能问题和效率低下。这是因为它们需要长时间的 JOIN 查询和 ORM 数据处理，这些都是缓慢且耗时的。幸运的是，您可以通过将列从父级传播到层次结构中的子级来避免所有这些。我希望这个真实的案例研究可以帮助您构建更好更快的应用程序！
关于提高分层 SQL 结构的性能，你学废了么？