提高分层 SQL 结构的性能

​译者 | 万望琳

审校 | 孙淑娟 梁策

本文将展示在处理分层数据结构时，列传播这一直接提高查询性能的方法。本文将使用基于数据驱动项目的真实场景来讲解，其中项目为某体育行业初创公司开发的实时数据网站。本文将带你了解有关列传播的相关知识，以解决分层 SQL 表结构中固有的性能问题。

背景

本文所做项目涉及一个拥有数百万页面的足球球迷网站。该网站致力于成为球迷心中的权威，尤其是在投注方面。因为调度程序负责定期重新计算复杂数据并将其存储在表中，这样查询就不必涉及SQL 聚合，数据库和应用程序架构也不是特别复杂。因此，真正的挑战在于非功能性需求，例如性能和页面加载时间。

应用领域

体育行业的数据来源有很多，每个来源都为其客户提供不同的数据集。具体来说，足球行业有四种类型的数据：

1. 个人档案数据：身高、体重、年龄、效力球队、所获奖杯、个人奖项、球员和教练。
2. 历史数据：过往赛果和技术统计，如进球、助攻、黄牌、红牌、传球等。
3. 当前和未来数据：当前赛季已完结和将进行的比赛结果与技术统计。
4. 实时数据：比赛实时结果与技术统计。

该网站涉及所有这些类型的数据，同时特别关注有利于搜索引擎优化的历史数据和支持投注的实时数据。

分层表结构

出于保密要求，部分数据结构无法完全公开。但通过足球赛季的结构也可以了解相关情况。

具体来说，足球提供商通常按如下方式组织赛季中的比赛数据：

• 赛季(Season)：有开始和结束日期，通常持续一个日历年。
• 赛事(Competition)：比赛所属的赛事。
• 阶段(Phase)：赛事所处的阶段（例如，资格赛、淘汰赛、决赛阶段）。每个赛事都有自己的规则，很多赛事只有一个阶段。
• 组别(Group)：与阶段相关的组（例如，A 组、B 组、C 组……）。像世界杯等赛事会涉及不同的组别，每个组内涵盖相应球队。大多数赛事只有一个通用组适用于所有球队。
• 回合(Turn)：是从逻辑上相对于进行一天的赛事而言的。通常持续一周，涵盖属于一个小组的所有球队的比赛（例如，MLS 有 17 场主场比赛和 17 场客场比赛，因此它有 34 个回合）。
• 比赛(Game)：两支足球队之间的比赛。

如下图ER 模式所示，这 5 张表代表了一个分层数据结构：

技术、参数和性能要求

我们使用Express 4.17.2和 Sequelize 6.10作为 ORM（对象关系映射）在 Node.js 和 Typescript 中开发后端。前端是使用 Typescript 开发的 Next.js 12应用程序。数据库则选用由 AWS 托管的 Postgres 服务器。

该网站在AWS Elastic Beanstalk上运行，前端有 12 个实例，后端有 8 个实例，目前每天有 1000到 5000的访问者。客户的目标是在一年内达到每天6万的浏览量，因此该网站必须准备好在无损性能的情况下托管数百万月度用户。

在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字段。然后，该Turn对象将有一个Group字段存储其各自的Group对象等。

生成这种转换后的数据是无法摆脱的负担。处理凌乱的原始数据具有挑战，并且会导致代码异味。另一方面，这个后台发生的过程需要时间。因为处理存储数千个元素的数组总是非常棘手，当原始记录有数千行时尤其如此。

换句话说，分层表结构的常见 JOIN 查询在数据库和应用程序层都很慢。

列传播作为一种解决方案

针对这一性能问题，在分层结构将列从父级传播到其子级可以作为一种解决方案。

为什么应该在分层数据库上传播列

在分析上面的 JOIN 查询时，很明显问题在于在叶子表Game应用了过滤器。你必须遍历整个层次结构。但是既然 Game 是层次结构中最重要的元素，为什么不直接在其中添加seasonId、competitionId、phaseId和groupId列呢？这就是列传播的意义所在。

将外部键列直接传播给子项可以避免所有的 JOIN。现在你可以将上面的查询替换为以下查询：

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

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

请注意，列传播涉及数据重复，需要少用、慎用。在深入研究如何优雅实现之前，让我们看看应该传播哪些列。

如何选择要传播的列

如果向下传播层次结构中较高的实体的每一列，这在过滤方面可能很有用（例如外部密钥）。此外，你也可用传播用于过滤数据的枚举列，或生成包含来自父级的聚合数据的列来避免 JOIN。

Top 3- 列传播方法

在选择列传播方法时，我们的团队考虑了三种不同的实现方法。

1. 创建物化视图

要在层次表结构中实现列传播，我们首先是想创建具有所需列的物化视图。物化视图存储查询的结果，它通常表示复杂查询的行和/或列的子集，例如上面介绍的 JOIN 查询。

当涉及到具体化查询时，你可以定义何时生成视图。然后数据库会将其存储在磁盘上并使其像普通表一样可用。即使生成查询可能很慢，你也只能一点点地启动它。因此，物化视图代表了一种快速的解决方案。

另一方面，物化视图对处理实时数据可能并非最佳方法，因为物化视图可能不是最新的。它存储的数据取决于你决定生成视图或刷新它的时间。此外，涉及大数据的物化视图会占用大量磁盘空间，这可能会带来问题并增加存储成本。

2. 定义虚拟视图

另一种可能的解决方案是使用虚拟视图。同样，虚拟视图是存储查询结果的表。与物化视图的不同之处在于，这一次数据库不会将查询结果存储在磁盘上，而是将其保存在内存中。因此，虚拟视图始终是最新的，从而解决了实时数据的问题。

此外，每次访问视图时，数据库都必须执行生成查询。所以，如果生成查询需要时间，那么涉及到视图的整个过程必然很慢。虚拟视图是一个强大的工具，但考虑到我们的性能目标，还需寻找其他解决方案。

3. 使用触发器

SQL 触发器可以让你在数据库中发生特定事件时自动启动查询。换句话说，触发器使你能够跨数据库同步数据。因此，在层次结构表中定义所需的列，并让自定义触发器更新它们，这样就可轻松实现列传播。

因为每次触发器等待的事件发生时，数据库都会执行它们，所以可以想见，触发器会增加性能开销。执行查询需要时间和内存，所以会有成本，但与虚拟或物化视图带来的缺点相比，这种成本通常可以忽略不计。

触发器的问题是，定义它们可能需要一些时间。同时，你只能处理此任务一次，并在需要时要对其更新。通过触发器可以让你轻松实现列传播。此外，通过这种方式，我们也极大满足了客户定义的性能要求。

层次结构在数据库中很常见。因为需要长时间的 JOIN 查询和 ORM 数据处理，过程缓慢且耗时。如果处理不当，可能会导致应用程序出现性能和效率低下的问题。不过，你可以在层次结构中将列从父级传播到的子级来避免这些问题。

译者介绍

万望琳，51CTO社区编辑，资深DBA工程师，具有十余年DBA以及系统运维经验，曾就职于南网/合生创展等，目前就职于某大型跨国银行。拥有丰富的系统、Oracle数据库等维护经验，IT基础架构背景，获得阿里云ACE，CKA，RHCE以及Oracle OCP等认证。擅长领域有Oracle，Ansible，Linux，系统架构，云原生等。

原文标题：​​Improving Performance in a Hierarchical SQL Structure​​​，作者：Antonello Zanini​