使用 SQL 聚合函数提高性能

时间：2022-01-06 作者：电脑狂魔

场景介绍

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

技术、服务器规格和架构

我们使用Spring Boot 2.5.3框架和Hibernate 域名.Final ORM（对象关系映射）在 Kotlin 中开发了后端。我们通过Dokku管理的 Docker容器将其部署在 8GB 4 CPU VPS 上。初始堆大小设置为 2GB，限制为 7GB，而我们将剩余的 GB 内存分配给基于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 来对前面提到的表执行查询以检索数据。这样的查询只不过是从 10k 到 20k 行的简单 SELECT 返回。然后，这些数据通过多线程进程聚合，存储在Redis缓存中，最后以JSON序列化返回给前端应用。从 API 收到命中的第一刻（因此，在结果在 Redis 缓存中可用之前）到完成，用户必须等待 2 到 4 秒。

这种延迟是不可接受的。

深入研究性能问题

现在让我们看看刚才介绍的方法的缺点。

ORM 数据转换瓶颈

大多数高级 ORM 抽象了它们在数据库级别表示数据的方式。换句话说，ORM 执行查询，从数据库中检索所需的数据，并负责将其转换为其应用程序级表示。这个数据转换过程发生在幕后，但它无疑是一种开销。尽管该过程在性能方面通常可以忽略不计，但它很快就会成为数千行的瓶颈。

当使用 OO（面向对象）语言时，这种放缓尤其可能发生。此外，创建新的类实例需要时间和资源。限制对象大小和堆使用的一种方法可能是仅选择严格必要的列集。这种方法将使每个对象更轻，即使对象创建过程代表主要开销。因此，执行此转换过程所花费的时间不会发生显着变化。

循环需要时间

对包含数千个元素的对象数组执行诸如 sum 或 average 之类的简单操作并不是没有性能的。虽然这比不上 ORM 转换数据所花费的时间，但它确实代表了额外的开销。幸运的是，Java 支持许多线程安全的集合来并发执行操作。另一方面，打开和管理线程是复杂且耗时的任务。

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

什么是 SQL 聚合函数？

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

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

当与 GROUP BY 语句相关联时，它们代表了一种有效且有用的工具。多亏了它，您可以首先对所需的数据进行分组，然后通过利用它们来聚合它。

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

虽然 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 聚合函数在数据库级别返回聚合数据。它做这一切的同时使用的报表过滤了所需要的数据gameId，并parameterId基于相同的分组它parameterId。换句话说，首先根据所选的赛季比赛和需要分析的参数过滤数据。然后，结果信息按参数分组并由 SQL 聚合函数聚合。

定义正确的索引

由于该查询涉及 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 聚合函数的所需数据的查询总是比涉及简单 WHERE 子句的 SELECT 慢。尽管如此，执行时间应该保持在十分之一秒的数量级，无论如何，远少于在应用程序级别执行相同操作的时间。
可用的 SQL 聚合操作通常仅限于十几种，其中只有 5 或 6 种是数学运算。