使用 SQL 聚合函数提高性能

在本文中，您将了解 SQL 聚合函数是如何轻松的大幅提高应用程序性能的。特别是通过一个demo，为体育行业初创公司开发的数据驱动应用程序的真实场景中，聚合函数是如何发挥巨大作用的。

现在，让我们深入探讨这一场景，了解为什么 SQL 聚合函数在数据科学方面如此重要。    

场景介绍

我们最近做的是一些体育相关的高级数据搜索聚合功能。具体来说，它需要允许探索原始数据和聚合数据。由于数据库涉及 TB 级的异构和非结构化数据，因此面临的挑战主要集中在后端和数据库方面。现在，让我们深入了解一下这种情况。

技术、服务器规格和架构

我们使用 Kotlin、Spring Boot 2.5.3 框架和 Hibernate 5.4.32.Final ORM（对象关系映射）开发了后端。我们通过 Dokku 管理的 Docker 容器将其部署在一个 8GB 4 CPU 的 VPS 上。初始堆大小设置为 2GB，并限制为 7GB，剩余的 GB 内存分配给基于 Redis 的缓存系统。我们在开发网络应用程序时充分考虑了性能。具体来说，它基于本文所述的多层 Spring Boot 架构，并涉及多线程处理。

数据库结构

数据库是运行在 8GB 2 CPU VPS 上的 MySql 服务器。后端应用程序和数据库也在同一个服务器群中，但它们不共用同一个 VPS。由于来自体育运动的数据简单但高度异构，因此数据库的结构应避免重复并鼓励标准化。这就是选择关系数据库的原因。目前，该数据库涉及数百个表格，由于保密协议的原因，无法在此全部展示。

幸运的是，问题最多的表格或多或少都有相同的结构。因此，只分析一张表就足够了。其中，PositionalData 表的结构如下：

CREATE TABLE `PositionalData` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `area1` double DEFAULT NULL,
  `area2` double DEFAULT NULL,
  `area3` double DEFAULT NULL,
  `area4` double DEFAULT NULL,
  `area5` double DEFAULT NULL,
  .
  .
  .
  `area140` double DEFAULT NULL,
  `area141` double DEFAULT NULL,
  `area142` double DEFAULT NULL,
  `area143` double DEFAULT NULL,
  `area144` double DEFAULT NULL,
  `value` double DEFAULT NULL,
  `parameterId` int(11) NOT NULL,
  `gameId` int(11) NOT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `createdBy` int(11) DEFAULT NULL,
  `updatedAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) DEFAULT NULL,
  `deletedAt` datetime DEFAULT NULL,
  `deletedBy` int(11) DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如您所见，它涉及 100 多列，有 4 个以上的外部 ID。平均每个表至少包含 1,500 万行。

性能问题

前端应用程序的主要功能之一是让用户分析来自一个或多个赛季所有选定比赛的数百个不同体育参数（如传球、投球、盖帽）的汇总值。为了检索这些数据，我们开发了一个后台应用程序接口，用于对上述表格进行查询。这种查询只不过是一个简单的 SELECT，返回 10k 到 20k 行数据。然后，这些数据通过多线程进程进行聚合，存储在 Redis 缓存中，最后以 JSON 格式序列化并返回给前端应用程序。用户首次调用该 API（因此在 Redis 缓存中的结果可用之前）需要 2 到 4 秒的时间。这是不可接受的。

深入研究性能问题

现在让我们看看上述方法的缺点是什么。

ORM数据转换过程瓶颈

大多数高级 ORM 都抽象了数据库级的数据表示方式。换句话说，ORM 执行查询，从数据库中检索所需的数据，并负责将其转换为应用程序级的表示形式。这种数据转换过程是在后台进行的，但它无疑是一种开销。虽然就性能而言，它通常可以忽略不计，但当数据量达到数千行时，它就很容易成为瓶颈。在使用 OO（面向对象）语言时尤其如此。事实上，创建一个新的类实例需要时间和资源。为了限制对象的大小和堆的使用，一种解决方案可能是只选择严格必要的列集。尽管对象创建过程是主要的开销，但这将使每个对象变得更轻。因此，执行这一转换过程所花费的时间不会有很大变化。

循环需要时间

在包含数千个元素的对象数组上执行简单的操作（如求和或求平均值）并非没有性能问题。虽然这不能与 ORM 转换数据所花费的时间相比，但肯定会带来额外的开销。幸运的是，Java 支持许多线程安全集合来并发执行操作。另一方面，打开和管理线程是一项复杂而耗时的操作。

采用 SQL 聚合函数

让我们看看几个 SQL 聚合函数如何帮助我解决性能问题。

什么是 SQL 聚合函数？

SQL 聚合函数允许您执行涉及多行的计算并获得一个值作为结果。尽管每种 SQL 语言都有自己的聚合函数，但最常见的函数是：

• COUNT()：它返回所选行数的计数
• MIN()：提取最小值
• MAX()：提取最大值
• SUM()：执行求和运算
• AVG()：执行平均运算

当与 GROUP BY 语句结合使用时，它们是一个特别强大和有用的工具。有了它们，你可以先将所需数据分组，然后利用它们进行聚合。如果你想深入了解 MySQL 聚合函数，可以在这里找到所有支持的函数。

用查询代替应用程序级操作

虽然 SQL 聚合函数看起来很有前途，但在实际操作之前，我不知道它们是否能发挥作用。具体来说，应用级操作是生成一个数据结构，其中包含值列的平均值和所选游戏中每个参数的每个 areaX（X 从 1 到 144）列的总和。您可以很容易地将其转换为以下查询：

SELECT SUM(`area1`) as `area1`,  SUM(`area2`) as `area2`, SUM(`area3`) as `area3`, 
...
SUM(`area142`) as `area142`, SUM(`area143`) as `area143`, SUM(`area144`) as `area144`, 
AVG(`total`) as `total`, `parameterId`
FROM `PositionalData`
WHERE `parameterId` IN (:parameterIds) AND `gameId` IN (:gameIds)
GROUP BY `parameterId`

正如您所看到的，此查询利用 SQL 聚合函数返回数据库级别的聚合数据。所有这一切都是在使用andIN语句过滤所需数据并基于相同的数据进行分组的同时进行的。换句话说，首先根据所选的赛季比赛和所需的分析参数来过滤数据。然后，将结果数据按参数分组，最后通过 SQL 聚合函数进行聚合。gameId``parameterId``parameterId

正确的使用索引

由于该查询涉及 GROUP BY、IN 和 SQL 聚合语句，因此可能会很慢。这就是为什么定义正确的索引如此重要。具体来说，所应用的最重要且最有效的指标是以下一项：

ALTER TABLE `PositionalData` ADD INDEX `PositionalData_parameterId_gameId` (`parameterId`, `gameId`) USING BTREE;

优点

• 数据库级聚合比在大型数组上循环时在应用程序级执行相同的聚合逻辑要快得多。
• 使用涉及 SQL 聚合函数和 GROUP BY 语句的查询可以大大减少返回的行数。详细来说，这使我能够将行数从大约 10k 行增加到等于分析参数数量的行数。因此，这使得 ORM 执行的数据转换过程与时间无关，从而防止它成为瓶颈。
• 在数据库级别聚合允许您在执行相同请求时利用数据库缓存带来的性能优势。这可以降低应用程序级缓存的重要性，从而实现更轻的架构。

缺点

• SQL的时候一般都会用到聚合函数SELECT。当处理强类型编程语言时，ORM 需要结果的类型。并不是所有的 ORM 都允许您轻松定义，有时甚至将 SQL 聚合函数限制为仅限本机查询。这意味着失去 ORM 引入的抽象优势，并阻碍其使用。
• 涉及 SQL 聚合函数提取所需数据的查询总是比SELECT涉及简单WHERE子句的查询慢。同时，执行时间应保持在十分之一秒的量级，并且在任何情况下都比在应用程序级别执行相同操作要短得多。
• 可用的 SQL 聚合运算通常有十几个，其中只有 5 或 6 个实际上是数学运算。

性能比较

我们来比较一下在没有缓存和相同参数的情况下调用相同的涉及数据聚合的 API 时的响应时间结果。

• 应用层聚合响应时间：~2/4s
• 在数据库级别执行聚合时的响应时间：~800ms

关于 SQL 聚合函数的最终想法

在处理数据科学时，SQL 聚合函数无疑是一个将性能提升到新水平的好工具。尽管并非所有 ORM 都能完全或本机支持它们，但使用它们既简单又有效。无论哪种方式，了解如何利用它们可能对于提高性能至关重要，而本文的目的就是通过现实案例研究来解释这一点。