AWK与MySQL在数据聚合方面的比较

Question

在尝试确定 AWK 还是 MySQL 更适合处理日志文件并返回聚合统计信息时，我注意到以下行为，这让我感到困惑：

为了测试，我使用了一个具有 4 列和大约 900 万条记录的文件。我使用了同一台服务器，该服务器是带有 SSD 和 1GB RAM 的 VPS。

column1 是一个具有约 10 个唯一值的列，并且所有列的组合的总唯一值数约为 4k。

在 MySQL 中，我使用一个定义为 table (column1、column2、column3、column4) 的表，没有索引。

数据格式：

column1,column2,column3,column4 column1,column2,column3,column4

AWK 脚本：

BEGIN {
    FS = ",";
    time = systime();
}  {
    array[$1]++;  #first test
    #array[$1 "," $2 "," $3 "," $4]++; #second test
}
} END {
    for (value in array) {
            print "array[" value "]=" array[value];
    }
}

MySQL查询：

Query 1: SELECT column1, count(*) FROM log_test GROUP BY column1;

Query 2: SELECT column1, column2, column3, column4, count(*) 
FROM log_test GROUP BY column1, column2, column3, column4;

如预期，AWK比MySQL慢。然而，当我运行第一个返回10行聚合数据的测试时，MySQL需要约7秒才能完成，而AWK需要约22秒。

我了解awk逐行读取并处理数据，因此我希望当我运行第二个输出4k行的测试时，AWK应该像第一个测试一样花费相同的时间，因为它仍然有相同数量的行要读取，并且没有进行更多的处理。然而，AWK需要约90秒，但只使用0.1%的内存，而MySQL需要约45秒，并使用3%的内存。

1. 当它基本上读取相同的文件时，为什么AWK在测试2中比测试1花费更多的时间？
2. 为什么AWK不使用更多的内存？awk是否将其值存储在硬盘上而不是内存中？
3. 当它基本上也必须逐行读取表格时，为什么MySQL要快得多？
4. 是否有更有效的方法来聚合这些数据？

- Ballard

---

你能发布MySQL的show create table语句吗？这样我就可以看到你的列是如何定义的。 - Raymond Nijland

创建表 log_test ( column1 varchar(20) DEFAULT NULL, column2 varchar(20) DEFAULT NULL, column3 varchar(20) DEFAULT NULL, column4 varchar(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 - Ballard

Answer 1

在第二种情况下，Awk必须存储所有元组（并且要处理一个更大的关联映射表）。为了验证这一点，请尝试2个和3个字段计数的中间步骤。

至于内存使用情况，您能否查看进程使用的确切字节数？强制awk在结束时休眠，并测量两种情况下的内存使用情况（以字节为单位），您将看到差异。

MySQL以比仅打印文本更有效的方式存储数字数据。更重要的是，它可能会以预解析的形式存储数据，而awk必须对每行执行昂贵的记录和字段拆分（您没有显示MySQL设置，但如果您使用char（10）或其他固定宽度字段，则MySQL不必重新处理数据）。

最有效的方法是预先排序或应用维护更新的索引，尽管这会以每次插入的时间为代价。或者，如果列很小且具有已知的宽度，则可以编写自己的C实用程序，利用这些假设（文件只是一组结构体）。

- SheetJS

---

我知道它有一个更大的关联映射，但如果它需要大约22秒逐行处理文件，那么它做了什么是如此昂贵的呢？ - Ballard

@Ballard AWK 逐行读取（这本身需要一些处理——查找记录分隔符），并且拆分成字段（这不是一个简单的过程）。MySQL 不必这样做，因为在执行插入时已经隐式地完成了。但是，正如您的数据所显示的那样，在两种情况下，第二种方式都明显较慢（反映了关联映射/多字段组的复杂性）。 - SheetJS

关联映射在第二种情况下并不更复杂，因为AWK没有多维数组，只是一个更大的集合。我想知道为什么它似乎在处理更大的关联映射时会遇到很多问题。 - Ballard

Answer 2

如果您考虑到大型文本文件可以通过8:1进行压缩，并且SQL不会将数据存储为ASCII文本（它使用压缩方法），而解压缩比从磁盘读取要快得多（您可以在低处理器活动中看到这一点）。

如果SQL能够直接搜索压缩数据，那么涉及的工作就会少得多。 SQL通过索引和其他预处理来使搜索在SQL中更快。

- GT Electronics

Answer 3

在这两种情况下，都需要进行字段拆分；你说得对，在处理方面的差异是可以忽略不计的。

然而，你需要考虑到Awk如何实现关联数组的方法。为了增加给定的数组条目，它需要构造用作索引的字符串，然后在可能的索引列表中找到该条目。

我从问题描述中推测，对于以下情况：

array[$1]++

输入数据的$1有10个不同的值，每个值都小于20个字符（根据MYSQL表规范）。构造索引需要从输入记录中复制20个字符。对于这900万个输入记录中的每个记录，只需要将最多10个长度小于20个字符的字符串与第一个字段进行比较，以确定要增加"array"的哪个条目。

但对于以下情况：

array[$1 "," $2 "," $3 "," $4]++

我们需要从输入记录中复制高达80个字符到临时内存中，组装索引。而在第一种情况下，我们只需要复制20个字符。

你说过输出将有4000多行，这意味着在900万条记录的最后阶段，每个潜在的增量必须搜索和比较多达4000个80个字符的字符串。

我不清楚Awk使用什么样的方法来索引/哈希关联数组索引的细节（我希望它会比直接搜索/比较迭代更有效），但你可以看到搜索10个列表与搜索4000个列表的影响是不同的。

你还会注意到输入字段的长度也会影响AWK处理。如果一个字段是5个字符而不是20个字符，复制该字段所需要的时间将延长4倍。

最后，请注意，在将AWK与MYSQL进行比较时，您还必须考虑将数据加载到MYSQL数据库中所需的时间。如果无论使用AWK还是MYSQL聚合输出，都将加载数据，则使用MYSQL进行聚合可能更好。

但如果你只需要将它加载到MYSQL数据库中以便聚合，那么这个时间必须添加到查询时间中，我认为最终结果会更接近。

- DavidG