MySQL 松散扫描优化：针对 PostgreSQL 和 Microsoft 的性能比较评估

满足 GROUP BY 子句的最通用方法是扫描整个表或索引并从中仅提取不同的值。此操作可能有 2 种策略。

哈希聚合

当查询需要根据某些非索引列对数据进行分组时，通常会采用此技术。在哈希聚合中，构造了一个哈希表，其中键代表按列分组的值的唯一组合。

当数据库引擎扫描行时，它会计算每行的按列分组值的哈希值，并将与每个哈希值对应的聚合数据存储在哈希表中。

虽然此方法对于大型数据集可能会占用大量内存，但当服务器有足够的内存来存储哈希表时，它通常是最快的方法。

流聚合

当要分组的数据已经在分组依据列上排序或接近排序时，使用流聚合。当数据流进入时，数据库引擎将当前行与前一行进行比较。

如果当前行属于同一组，则数据库引擎继续聚合。当新的分组开始时，返回前一组的聚合结果，并开始新的聚合。

与哈希聚合相比，流聚合使用的内存更少，但它需要对数据进行排序，如果数据尚未排序，则可能会涉及额外的排序操作。

但这两种策略仍然需要全列扫描，并且在处理低基数列时有更好的策略，PostgreSQL和MS SQL Server没有，但MySQL有。

什么是松散扫描？

松散扫描是一种高级 MySQL 优化技术，适用于某些 GROUP BY 操作的上下文，特别是在处理的行数相对于表中的总行数相对较少的情况下。

该技术通过减少需要从数据库读取的数据量来显着提高查询性能。

松散扫描的基本原理

本质上，松散扫描技术的运行原理很简单：它不是扫描整个索引以查找符合条件的行（也称为“紧密”扫描），而是“松散”扫描每个组的第一个匹配行。找到匹配项后，它立即进入下一组。

此方法可确保减少需要计算的行数，从而减少执行查询所需的总时间。

除了MySQL之外，类似的技术也在其他数据库引擎中实现。它被称为“跳过扫描”甲骨文, SQLite ，和蟑螂数据库、“跳跃扫描” 数据库2 ,和“混合扫描” 尤加字节数据库。

设置环境

我想理论已经足够了；让我们进入实际部分，对 MySQL 与 PostgreSQL 和 Microsoft SQL Server 中的松散扫描进行比较分析。我将在笔记本电脑上使用最新的 docker 容器以及 MySQL 8.0.33、PostgreSQL 15.3 和 MS SQL 2022-CU4。

我将创建一张包含 100 万行和三列具有不同基数的整数数据类型的表。第一列有 10 万个唯一值，第二列有 1000 个，第三列只有 10 个唯一值。

我将创建三个单独的非聚集索引并在每列上运行 GROUP BY 查询。每个查询将执行 5 次（不计算经过时间），只是为了预热数据库，然后再执行 20 次，之后我们将比较平均执行时间。

因此，我尝试将所有数据库引擎置于相当平等的境地。

我用一个脚本来初始化所有数据库中的示例表：

 -- MySQL create table numbers ( id int not null ); insert into numbers(id) with tmp as ( select a.id + b.id * 10 + c.id * 100 + d.id * 1000 as id from (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c cross join (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d ) select id from tmp; create table group_by_table ( id int not null, a int not null, b int not null, c int not null, primary key (id) ); insert into group_by_table(id, a, b, c) with tmp as ( select a.id + b.id * 10000 as id from numbers as a cross join numbers as b ) select id, floor(rand() * 100000) as a, floor(rand() * 1000) as b, floor(rand() * 10) as c from tmp where id < 1000000; create index idx_group_by_table_a on group_by_table(a); create index idx_group_by_table_b on group_by_table(b); create index idx_group_by_table_c on group_by_table(c); -- PostgreSQL create table group_by_table ( id int not null, a int not null, b int not null, c int not null, primary key (id) ); insert into group_by_table(id, a, b, c) select id, floor(random() * 100000) as a, floor(random() * 1000) as b, floor(random() * 10) as c from generate_series(1, 1000000, 1) as numbers(id); create index idx_group_by_table_a on group_by_table(a); create index idx_group_by_table_b on group_by_table(b); create index idx_group_by_table_c on group_by_table(c); -- MS SQL Server create table group_by_table ( id int not null, a int not null, b int not null, c int not null, primary key clustered (id) ); with tmp as ( select row_number() over (order by (select 1)) - 1 as id from sys.all_columns as a cross join sys.all_columns as b ) insert into group_by_table(id, a, b, c) select id, floor(rand(checksum(newid())) * 100000) as a, floor(rand(checksum(newid())) * 1000) as b, floor(rand(checksum(newid())) * 10) as c from tmp where id < 1000000; create nonclustered index idx_group_by_table_a on group_by_table(a); create nonclustered index idx_group_by_table_b on group_by_table(b); create nonclustered index idx_group_by_table_c on group_by_table(c);

结果

所有数据库在 A 列上的执行情况几乎相同，该列的数据基数很高（100 万行中有 10 万个唯一值）。 PostgreSQL 总执行时间为 3.57 秒，MS SQL Server - 2.72 秒，MySQL - 3.44 秒。

但在 B 列中，基数只有 1000 个唯一值，MySQL 的总执行时间降至 70.76 毫秒，而 PostgreSQL 用时 1.56 秒，MS SQL Server 用时 2.52 秒。

因此，MySQL 完成第二个查询的速度比 PostgreSQL 快 22 倍，比 MS SQL Server 快 35 倍。

C 列的情况甚至更好，其中只有 10 个唯一值：MySQL - 16.66ms、PostgreSQL - 1.58s 和 MS SQL Server - 2.55s。

在最后一个示例中，MySQL 速度极快，其性能比 PostgreSQL 快了近 95 倍，比 MS SQL Server 快了 150 倍以上。

下面是使用对数刻度的可视化。它显示 20 次执行后的总执行时间。

MS SQL 和 PostgreSQL 可以做些什么来提高性能？

虽然 PostgreSQL 和 MS SQL Server 目前缺乏此类优化，但您可以使用一个技巧来提高这些引擎上的 GROUP BY 查询的性能。这个想法是在索引中进行多次查找，而不是依赖默认的完整索引扫描。

例如，在 PostgreSQL 中，您可以执行递归查询来查找所有唯一值。第一次迭代从列中选择最小值，而其他每次迭代都选择比前一个值大的下一个值。

 with recursive t as ( select min(a) as x from group_by_table union all select (select min(a) from group_by_table where a > tx) from t where tx is not null ) select count(*) from ( select x from t where x is not null union all select null where exists (select 1 from group_by_table where a is null) ) as tmp;

我们也可以在 MS SQL Server 中执行相同的操作。但不幸的是，MS SQL Server 递归查询不支持 TOP 或聚合运算符，因此我将使用临时表来存储结果并使用 LOOP 进行迭代。

当然，这会产生更多的开销，但似乎没有其他通用方法可以在 SQL Server 中完成此类优化。

 create table #result (x int); declare @current int; select top (1) @current = a from group_by_table order by a; while @@rowcount > 0 begin insert into #result values (@current); select top (1) @current = a from group_by_table where a > @current order by a; end; select count(*) from #result;

现在让我们比较一下这些修改后的查询与原始查询和 MySQL 的运行情况。我将修改后的查询引用为 A1、B1 和 C1。这是包含完整结果的表格。

结论

结果非常明显，松散扫描是一个很好的优化，有助于显着减少使用索引时为 GROUP BY 或 DISTINCT 查询评估的行数。

尽管 PostgreSQL 允许您编写复杂的递归查询来处理低基数列，其有效性与 MySQL 相同，但它对基数较高的列 A 具有显着的性能损失。

MS SQL Server 在 A 列上的性能优于其他数据库，但在任何其他情况下显然性能较差，但是，当然，某些解决方法仍然比原始查询更好。

我希望 PostgreSQL 和 MS SQL Server 在下一个版本中实现跳过扫描优化。