使用 SQL 聚合函数提高性能

在本文中，您将了解 SQL 聚合函数如何提供一种显着提高应用程序性能的简单方法。特别是，您将看到它们如何在现实场景中改变游戏规则，该场景基于为体育行业初创公司开发的数据驱动应用程序。

现在让我们更深入地研究这个场景，并了解为什么 SQL 聚合函数在数据科学中如此重要。

场景介绍

我最近开发的应用程序旨在通过网络在体育界提供高级数据探索功能。具体来说，它需要允许探索原始数据和聚合数据。由于数据库涉及TB级的异构和非结构化数据，挑战主要集中在后端和数据库端。现在，让我们深入研究这个场景。

技术、服务器规格和架构

我们使用Spring Boot 2.5.3框架和Hibernate 5.4.32.Final ORM（对象关系映射）在 Kotlin 中开发后端。我们通过Dokku管理的Docker容器将其部署在 8GB 4 CPU VPS上。初始堆大小设置为 2GB，并限制为 7GB，而剩余 GB 的 RAM 分配给基于Redis的缓存系统。我们开发 Web 应用程序时考虑到了性能。具体来说，它基于这里描述的多层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。平均而言，每个表至少包含 1500 万行。

性能问题

前端应用程序的关键功能之一是让用户分析来自一个或多个赛季的所有选定比赛的数百个不同运动参数（例如，传球、投掷、拦网）的聚合值。为了检索这些数据，我们开发了一个后端 API，对上述表执行查询。这样的查询只不过是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 聚合函数看起来很有前途，但在实际使用之前，我不知道它们是否能发挥作用。具体来说，应用程序级操作生成一个数据结构，其中包含在所选游戏中选择的每个参数的列上的平均值以及每个列（从 1 到 144）value的总和。您可以轻松地翻译成以下查询：areaXX

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 聚合函数进行聚合。gameIdparameterIdparameterId

定义正确的索引

由于该查询涉及 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 个实际上是数学运算。