满足 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。这是包含完整结果的表格。  A  A1 乙 B1  C  C1   MySQL  3.44秒 70.76毫秒 15.66毫秒 PostgreSQL  3.57秒 6.27秒 1.56秒 68.90毫秒 1.58秒 16.02毫秒 微软SQL服务器 2.72秒 68.07秒 2.52秒 745.07毫秒 2.55秒 68.76毫秒 结论 结果非常明显，松散扫描是一个很好的优化，有助于显着减少使用索引时为 GROUP BY 或 DISTINCT 查询评估的行数。 尽管 PostgreSQL 允许您编写复杂的递归查询来处理低基数列，其有效性与 MySQL 相同，但它对基数较高的列 A 具有显着的性能损失。  MS SQL Server 在 A 列上的性能优于其他数据库，但在任何其他情况下显然性能较差，但是，当然，某些解决方法仍然比原始查询更好。 我希望 PostgreSQL 和 MS SQL Server 在下一个版本中实现跳过扫描优化。

This story contains new, firsthand information uncovered by the writer.

Read My Stories

Hire Me

My Blog

該音頻是用故事的原始語言製作的！

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

About Author

註釋

標籤

这篇文章刊登在

Related Stories

加密货币增长：创建有效的用户角色

释放人工智能的力量。前沿技术的系统评价：摘要与介绍

数字游民请注意：您需要了解泰国新推出的 DTV 签证

点击赚钱：Telegram 可能会在 Solana 之前吸引下一个 100 亿加密用户

加密货币增长：创建有效的用户角色

释放人工智能的力量。前沿技术的系统评价：摘要与介绍

数字游民请注意：您需要了解泰国新推出的 DTV 签证

点击赚钱：Telegram 可能会在 Solana 之前吸引下一个 100 亿加密用户

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps