SQL-结构的性能-提高分层 (SQL结构转换)

​译者 |万望琳

审校 |孙淑娟 梁策

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

背景

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

应用领域

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

分层表结构

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

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

如下图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 的查询。

因此你可能会编写以下查询：

 GA. `Game` GALEFT  `Turn` T  GA.`turnId`  T.`id`LEFT  `Group` G  T.`groupId`  G.`id`LEFT  `Phase` P  G.`phaseId`  P.`id`LEFT  `Competition` C  P.`competitionId`  C.`id`LEFT  `Season` S  C.`seasonId`  S.`id` S

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

此外，如果你想检索所有数据而不仅仅是表中的Game列，由于笛卡尔积的性质，你必须处理数千行和数百列。这个过程可能会变得混乱，但这正是 ORM 发挥作用的地方。

ORM数据解耦和转换需要时间

通过 ORM 查询数据库时，你可能会对检索基于应用程序级别的表中的数据感兴趣。原始数据库级别表示在应用程序级别可能没有用。因此，当大多数高级 ORM 执行查询时，它们会从数据库中检索所需数据并将其转换为应用程序级表示。这个过程包括两个步骤：数据解耦和数据转换。

在后台，来自 JOIN 查询的原始数据首先被解耦，然后在应用程序级别转换为相应的表示。因此，在处理所有数据时，具有数百列的数千条记录成为一个小组数据，每个数据都具有数据模型类中定义的属性。因此，包含从数据库中提取的原始数据的数组将成为一组Game对象。每个Game对象都有一个包含其各自Turn实例的turn字段。然后，该Turn对象将有一个Group字段存储其各自的Group对象等。

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

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

列传播作为一种解决方案

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

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

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

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

 `Game` GA GA

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

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

如何选择要传播的列

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

Top 3- 列传播方法

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

1. 创建物化视图

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

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

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

2. 定义虚拟视图

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

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

3. 使用触发器

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

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

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

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

译者介绍

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

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

---

sql数据库有什么优势?

美国Microsoft公司推出的一种关系型数据库系统。 SQLServer是一个可扩展的、高性能的、为分布式客户机/服务器计算所设计的数据库管理系统，实现了与WindowsNT的有机结合，提供了基于事务的企业级信息管理系统方案。 其主要特点如下：（1）高性能设计，可充分利用WindowsNT的优势。 （2）系统管理先进，支持Windows图形化管理工具，支持本地和远程的系统管理和配置。 （3）强壮的事务处理功能，采用各种方法保证数据的完整性。 （4）支持对称多处理器结构、存储过程、ODBC，并具有自主的SQL语言。 SQLServer以其内置的数据复制功能、强大的管理工具、与Internet的紧密集成和开放的系统结构为广大的用户、开发人员和系统集成商提供了一个出众的数据库平台

prepareStatement和Statement的区别

prepareStatement可以替换变量 在SQL语句中可以包含?，可以用ps=(select * from Cust where ID=?); int sid=1001; (1, sid); rs = (); 可以把?替换成变量。 而Statement只能用 int sid=1001; Statement stmt = (); ResultSet rs = (select * from Cust where ID=+sid); 来实现

SQL语言是谁发明的？

SQL是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统，可以使用相同的SQL语言作为数据输入与管理的 接口。它以记录集合作为操作对象，所有SQL语句接受集合作为输入，返回集合作为输出，这种集合特性允许一条SQL语句的输出作为另一条SQL语句的输入，所以SQL语句可以嵌套，这使他具有极大的灵活性和强大的功能，在多数情况下，在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的，这也意味着用SQL语言可以写出非常复杂的语句。　结构化查询语言(Structured Query Language)最早是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEM R开发的一种查询语言，它的前身是SQUARE语言。SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年推出以来，SQL语言得到了广泛的应用。如今无论是像Oracle、Sybase、DB2、Informix、SQL Server这些大型的数据库管理系统，还是像Visual Foxpro、PowerBuilder这些PC上常用的数据库开发系统，都支持SQL语言作为查询语言。　美国国家标准局(ANSI)与国际标准化组织（ISO）已经制定了SQL标准。ANSI是一个美国工业和商业集团组织，负责开发美国的商务和通讯标准。ANSI同时也是ISO和International Electrotechnical Commission(IEC)的成员之一。ANSI 发布与国际标准组织相应的美国标准。1992年，ISO和IEC发布了SQL国际标准，称为SQL-92。ANSI随之发布的相应标准是ANSI SQL-92。ANSI SQL-92有时被称为ANSI SQL。尽管不同的关系数据库使用的SQL版本有一些差异，但大多数都遵循 ANSI SQL 标准。SQL Server使用ANSI SQL-92的扩展集，称为T-SQL，其遵循ANSI制定的 SQL-92标准。　SQL语言包含4个部分：　※ 数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句。　※ 数据操作语言(DML)，例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。　※ 数据查询语言(DQL)，例如：SELECT语句。　※ 数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。　SQL语言包括三种主要程序设计语言类别的语句：数据定义语言(DDL)，数据操作语言(DML)及数据控制语言(DCL)。　SQL 是用于访问和处理数据库的标准的计算机语言。　SQL 指结构化查询语言 SQL 使我们有能力访问数据库 SQL 是一种 ANSI 的标准计算机语言 编者注：ANSI，美国国家标准化组织