万王林审稿人|SunShujuanLiangCe本文将展示列传播，一种在处理分层数据结构时直接提高查询性能的方法。本文将使用一个基于数据驱动项目的真实场景来进行讲解，其中该项目是一家体育行业初创公司开发的实时数据网站。本文将向您介绍您需要了解的有关列传播的知识，以解决分层SQL表结构中固有的性能问题。背景本文中的项目涉及一个拥有数百万页面的足球迷网站。该网站努力成为粉丝中的权威，尤其是在博彩方面。由于调度器负责定期重新计算复杂数据并将其存储在表中，因此查询不必涉及SQL聚合，数据库和应用程序架构也不是特别复杂。所以真正的挑战在于性能和页面加载时间等非功能性需求。应用领域体育行业的数据来源很多，每个来源都向其客户提供不同的数据集。具体来说，足球行业有四类数据：简介数据：身高、体重、年龄、效力过的球队、获得的奖杯、个人奖项、球员和教练。历史数据：过往比赛成绩和技术统计，如进球、助攻、黄牌、红牌、传球等。当前和未来统计：本赛季已完成和即将进行的比赛的结果和技术统计。实时数据：比赛的实时成绩和技术统计。该网站涉及所有这些类型的数据，特别关注有利于SEO的历史数据和支持投注的实时数据。由于分层表结构的保密要求，部分数据结构不能完全公开。但也可以通过足球赛季的结构看出来。具体来说，足球提供商通常按如下方式组织一个赛季的比赛数据：赛季：有开始和结束日期，通常持续一个日历年。竞赛：竞赛所属的竞赛。阶段：赛事的阶段（例如，资格赛、淘汰赛、决赛）。每个活动都有自己的规则，许多活动只有一个阶段。组：与阶段关联的组（例如，组A、组B、组C...）。世界杯等赛事涉及不同的小组，每个小组都有球队。大多数锦标赛只有一个适用于所有团队的通用组。回合（Turn）：逻辑上是相对于一天的活动而言的。它通常持续一周，涵盖属于一个小组的所有球队的比赛（例如，MLS有17个主场比赛和17个客场比赛，所以它有34轮）。比赛：两支足球队之间的比赛。如下图ERschema所示，这5张表代表了一个层次化的数据结构：技术、参数和性能要求我们在Node.js和TypeScript端开发后使用Express4.17.2和Sequelize6.10作为ORM（对象关系映射）。前端是使用TypeScript开发的Next.js12应用程序。该数据库是由AWS托管的Postgres服务器。该网站在AWSElasticBeanstalk上运行，前端有12个实例，后端有8个实例，目前每天有1000到5000名访问者。客户的目标是在一年内达到每天60,000次页面浏览，因此网站必须准备好在不影响性能的情况下每月接待数百万用户。该站点在GoogleLighthouse性能、搜索引擎优化和可访问性测试中的得分应超过80分。此外，加载时间应始终少于2秒，最好是几百毫秒。真正的挑战是该站点包含超过200万个页面，所有这些页面都需要数周时间才能预呈现。此外，大多数页面上显示的内容都不是静态的。因此，我们选择了增量静态再生方法。当访问者点击其他人未访问过的页面时，Next.js使用从后端公开的API检索的数据生成该页面。Next.js然后将页面缓存30或60秒，具体取决于页面的重要性。因此，后端必须快速向服务器端生成过程提供所需的数据。为什么查询层次表很慢现在让我们看看为什么层次表结构会带来性能挑战。JOIN查询很慢根据与层次结构中较高对象关联的参数过滤叶子是层次结构数据结构中的常见情况。例如，检索特定赛季的所有比赛。由于叶表Game未直接连接到Season，因此您必须执行一个查询，以连接与层次结构中的元素一样多的元素。因此，您可以编写以下查询：SELECTGA.*FROM`Game`GALEFTJOIN`Turn`TonGA.`turnId`=T.`id`LEFTJOIN`Group`GonT.`groupId`=G.`id`LEFTJOIN`Phase`PonG.`phaseId`=P.`id`LEFTJOIN`Competition`ConP.`competitionId`=C.`id`LEFTJOIN`Season`SonC.`seasonId`=S.`id`WHERES.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`GAWHEREGA.seasonId=5可以想象，此查询将比原始查询快得多。另外，它直接返回你感兴趣的内容，所以ORM数据解耦和转换过程也可以暂时忽略。请注意，列传播涉及数据复制，应谨慎使用。在深入探讨如何优雅地执行此操作之前，让我们看看应该传播哪些列。如何选择要传播的列如果层次结构中较高实体的每一列都向下传播，这在过滤（例如外键）方面很有用。或者，您可以通过传播用于过滤数据的枚举列或生成包含来自父项的聚合数据的列来避免JOIN。排名前三的列传播方法在选择列传播方法时，我们的团队考虑了三种不同的实现方式。1.创建物化视图要在分层表结构中实现列传播，我们首先要创建一个具有所需列的物化视图。物化视图存储查询的结果，这些结果通常表示复杂查询的行和/或列的子集，例如上述的JOIN查询。当涉及具体化查询时，您可以定义何时生成视图。然后数据库会将其存储在磁盘上并使其像普通表一样可用。尽管生成查询可能很慢，但您只能稍微启动它。因此，物化视图代表了一种快速的解决方案。另一方面，物化视图可能不是处理实时数据的最佳方式，因为物化视图可能不是最新的。它存储的数据取决于您决定何时生成视图或刷新它。此外，涉及大数据的物化视图会消耗大量磁盘空间，这可能会产生问题并增加存储成本。2.定义虚拟视图另一种可能的解决方案是使用虚拟视图。同样，虚拟视图是存储查询结果的表。与物化视图不同的是，这次数据库不再将查询结果存储在磁盘上，而是保存在内存中。因此，虚拟视图始终是最新的，解决了实时数据的问题。此外，每次访问视图时，数据库都必须执行构建查询。因此，如果生成查询需要时间，那么涉及视图的整个过程一定很慢。虚拟视图是一个强大的工具，但鉴于我们的性能目标，我们寻求其他解决方案。3.使用触发器SQL触发器允许您在数据库中发生特定事件时自动启动查询。换句话说，触发器使您能够跨数据库同步数据。所以，在hierarchy表中定义需要的列，让自定义触发器更新它们，这样列传播就很容易实现了。因为每次触发器等待的事件发生时数据库都会执行触发器，所以触发器会增加可想而知的性能开销。执行查询需要时间和内存，因此会产生成本，但与虚拟视图或物化视图带来的劣势相比，这种成本通常可以忽略不计。触发器的问题在于定义它们可能需要一些时间。此外，您只能处理此任务一次并在需要时更新它。您可以通过触发器轻松实现列传播。此外，我们还通过这种方式在很大程度上满足了客户定义的性能要求。层次结构在数据库中很常见。由于JOIN查询和ORM数据处理时间长，过程缓慢且耗时。如果处理不当，可能会导致应用程序性能下降和效率低下。但是，您可以通过在层次结构中将列从父级传播到子级来避免这些问题。译者介绍万望林，51CTO社区编辑，高级DBA工程师，拥有十余年DBA和系统运维经验。具有丰富的系统和Oracle数据库维护经验，IT基础架构背景，获得阿里云ACE、CKA、RHCE和OracleOCP认证。擅长领域包括Oracle、Ansible、Linux、系统架构、云原生等。原标题：ImprovingPerformanceinaHierarchicalSQLStructure，作者：AntonelloZanini
