共计 3986 个字符，预计需要花费 10 分钟才能阅读完成。
## 上下文
我和我的团队最近在一个领有数百万页面的足球迷网站上工作。该网站的想法是成为足球支持者的权威资源，尤其是在投注方面。数据库和 [应用程序架构] 不是特地简单。这是因为调度程序负责定期从新计算简单数据并将其存储在表中，这样查问就不用波及[SQL 聚合]。因而，真正的挑战在于[非功能性需要]，例如性能和页面加载工夫。
应用领域
体育行业有多个数据提供者，每个提供者都为其客户提供不同的数据集。具体来说，足球行业有四种类型的数据：
- 传记数据：身高、宽度、年龄、他们效劳的球队、取得的奖杯、取得的集体奖项以及足球运动员和教练。
- 历史数据：过来较量的后果和那些较量中的事件，如进球、助攻、黄牌、红牌、传球等。
- 以后和将来数据：以后赛季的比赛结果和这些较量中产生的事件，以及将来较量的表格。
- 实时数据：正在进行的游戏的实时后果和实时事件。
咱们的网站波及所有这些类型的数据，特地关注出于 SEO 起因的历史数据和反对投注的实时数据。
分层表构造
因为我签订了 NDA，我无奈与您分享整个数据结构。同时，理解足球赛季的构造就足以理解这种现实情况。
具体地说，足球提供商通常按如下形式组织赛季中的较量数据：
- 节令：有开始和完结日期，通常继续一个日历年
- 较量 ：较量所属的联赛。竞争的一个实例存在于一个赛季中。[在此处] 解无关足球比赛如何运作的更多信息。
- 阶段：与较量相干的阶段（例如，资格赛阶段、淘汰赛阶段、决赛阶段）。每场较量都有本人的规定，很多较量只有一个阶段。
- 组：与阶段相干的组（例如，A 组、B 组、C 组……）。一些较量，例如世界杯，波及不同的个人，每个个人都有本人的球队。大多数较量只有一个通用组实用于所有团队。
- 转：从逻辑的角度来看，对应于一天的较量。它通常继续一周，涵盖了属于一个小组的所有球队的较量（例如，MLS 有 17 场主场较量和 17 场客场较量；因而，它有 34 个回合）。
- 较量：两支足球队之间的较量。
如下图 [ER 模式] 所示，这 5 个表代表了一个分层数据结构：
技术、规格和性能要求
咱们应用 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 构造的性能，你学废了么？
