为什么SQL聚合函数比Python和Java慢这么多（或贫民OLAP）

Question

我需要一个真正的数据库管理员的看法。在我的Macbook Pro上，Postgres 8.3执行这个查询需要200毫秒，而Java和Python在处理同样的计算时只需不到20毫秒（350,000行数据）：

SELECT count(id), avg(a), avg(b), avg(c), avg(d) FROM tuples;

使用SQL数据库时，这种情况正常吗？

这个表的结构（它存储的是调查的回答）：

CREATE TABLE tuples (id integer primary key, a integer, b integer, c integer, d integer);

\copy tuples from '350,000 responses.csv' delimiter as ','

为了提供一些背景，我在Java和Python中做了一些测试，它们的表现比SQL好得多（除了纯Python的情况）：

java   1.5 threads ~ 7 ms    
java   1.5         ~ 10 ms    
python 2.5 numpy   ~ 18 ms  
python 2.5         ~ 370 ms

即使是sqlite3在与Postgres比较时也表现得不错，尽管它假设所有列都是字符串（对比一下：在Postgres中，仅仅将列类型从整数改为数字就会导致速度下降10倍）

我尝试过的一些调优方法没有成功，包括（盲目跟随一些网络建议）：

increased the shared memory available to Postgres to 256MB    
increased the working memory to 2MB
disabled connection and statement logging
used a stored procedure via CREATE FUNCTION ... LANGUAGE SQL

所以我的问题是，我的经历正常吗？这就是我在使用SQL数据库时可以期待的情况吗？我明白ACID特性是有代价的，但在我看来，这种情况有点疯狂。我并不是在追求实时游戏的速度，但既然Java可以在不到20毫秒的时间内处理数百万个双精度数，我有点羡慕。

有没有更好的方法可以以低成本（无论是金钱还是服务器复杂性）进行简单的在线分析处理（OLAP）？我看过Mondrian和Pig + Hadoop，但对维护另一个服务器应用程序并不太兴奋，也不确定它们是否真的有帮助。

---

不，Python代码和Java代码都是在内部完成所有工作的。 我只是生成4个数组，每个数组有350,000个随机值，然后计算平均值。我没有把生成过程算入时间，只计算平均值的步骤。Java的线程计时使用了4个线程（每个数组一个平均值），虽然有点过头，但确实是最快的。

sqlite3的计时是由Python程序驱动的，并且是从磁盘运行的（不是：内存：）

我知道Postgres在背后做了很多事情，但大部分工作对我来说并不重要，因为这些数据是只读的。

Postgres的查询在后续运行时不会改变时间。

我重新运行了Python测试，包括从磁盘读取的时间。计时显著变慢，接近4秒。但我猜Python的文件处理代码大部分是用C写的（虽然csv库可能不是？），所以这让我觉得Postgres也没有从磁盘流式读取数据（或者你是对的，我应该向编写他们存储层的人致敬！）

Answer 1

我重新测试了一下，使用MySQL指定ENGINE = MEMORY，结果没有任何变化（还是200毫秒）。使用内存数据库的Sqlite3也差不多，时间是250毫秒。

这里的数学计算看起来是对的（至少大小是对的，因为这就是sqlite数据库的大小 :-)

我不太相信“磁盘导致慢”的说法，因为所有迹象都表明这些表是在内存中的（Postgres的开发者们都警告说，不要太努力地把表固定在内存中，因为他们发誓操作系统会比程序员做得更好）

为了澄清一下时间的比较，Java代码并没有从磁盘读取数据，如果Postgres是从磁盘读取并计算复杂查询，那这就不公平了，但这其实不是重点，我认为数据库应该足够聪明，能够把小表放到内存中，并预编译一个存储过程。

更新（回应下面的第一个评论）：

我不确定如何在不使用聚合函数的情况下测试查询，这样才算公平，因为如果我选择所有行，它会花很多时间来序列化和格式化所有内容。我并不是说慢是因为聚合函数，也可能只是因为并发、完整性等带来的开销。我只是不知道如何将聚合函数单独作为独立变量来测试。

Answer 2

我觉得你的测试方案其实没什么用。为了完成数据库查询，数据库服务器需要经过几个步骤：

1. 解析SQL语句
2. 制定查询计划，也就是决定用哪些索引（如果有的话），进行优化等等
3. 如果使用了索引，就去查找指向实际数据的指针，然后去数据的相应位置
4. 如果没有使用索引，就要扫描整个表来确定需要哪些行
5. 从磁盘加载数据到一个临时位置（希望是内存，但不一定）
6. 进行count()和avg()的计算

所以，在Python中创建一个数组并计算平均值，基本上跳过了除了最后一步以外的所有步骤。因为从磁盘读取数据是程序中最耗时的操作之一，这在测试中是一个重大缺陷（你可以看看我之前问的这个问题的回答）。即使在你的其他测试中从磁盘读取数据，过程也是完全不同的，很难判断结果的相关性。

为了获取更多关于Postgres花费时间的信息，我建议进行以下测试：

• 将你的查询执行时间与不使用聚合函数的SELECT进行比较（也就是省略第5步）
• 如果发现聚合导致显著的速度下降，试试Python是否能更快，通过普通的SELECT获取原始数据进行比较。

要加快你的查询速度，首先要减少磁盘访问。我非常怀疑是聚合导致了耗时。

有几种方法可以做到这一点：

• 缓存数据（在内存中！）以便后续访问，可以通过数据库引擎自身的功能或使用像memcached这样的工具
• 减少存储数据的大小
• 优化索引的使用。有时候这可能意味着完全不使用索引（毕竟，这也是磁盘访问）。对于MySQL，我记得如果你认为查询会获取超过10%的表中所有数据，建议跳过索引的使用。
• 如果你的查询很好地利用了索引，我知道对于MySQL数据库，将索引和数据放在不同的物理磁盘上会有帮助。不过，我不确定这是否适用于Postgres。
• 还有可能存在更复杂的问题，比如如果由于某种原因结果集无法完全在内存中处理，就会将行交换到磁盘。但我建议等到遇到严重的性能问题且找不到其他解决办法时再研究这些，因为这需要对你过程中的许多底层细节有了解。

更新：

我刚意识到你似乎在上述查询中不需要索引，而且很可能也没有使用任何索引，所以我关于索引的建议可能没什么帮助。抱歉。不过，我还是认为问题不在于聚合，而在于磁盘访问。无论如何，我还是把索引的内容保留在这里，可能还有用。

Answer 3

Postgres做的事情比看起来要复杂得多（首先要保持数据的一致性！）

如果你的数据不需要完全准确，或者表格更新得不频繁，但你又经常需要进行这个计算，那么你可以考虑使用物化视图来加快速度。

（注意，我在Postgres中没有使用过物化视图，它们看起来有点像黑科技，但可能适合你的情况。）

物化视图

还要考虑连接到服务器的开销，以及发送请求到服务器再返回的时间。

我觉得200毫秒对于这种情况来说已经算不错了。我在我的Oracle服务器上做了个快速测试，使用相同的表结构，大约有50万行且没有索引，耗时大约1到1.5秒，这几乎都是Oracle从磁盘读取数据的时间。

真正的问题是，200毫秒够快吗？

-------------- 更多内容 --------------------

我对使用物化视图来解决这个问题很感兴趣，因为我从来没有玩过它们。这是在Oracle上进行的。

首先，我创建了一个每分钟刷新的物化视图。

create materialized view mv_so_x 
build immediate 
refresh complete 
START WITH SYSDATE NEXT SYSDATE + 1/24/60
 as select count(*),avg(a),avg(b),avg(c),avg(d) from so_x;

在刷新期间，没有返回任何行。

SQL> select * from mv_so_x;

no rows selected

Elapsed: 00:00:00.00

一旦刷新完成，速度比直接查询快得多。

SQL> select count(*),avg(a),avg(b),avg(c),avg(d) from so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:05.74
SQL> select * from mv_so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:00.00
SQL> 

如果我们向基础表中插入数据，物化视图不会立即显示结果。

SQL> insert into so_x values (1,2,3,4,5);

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select * from mv_so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:00.00
SQL> 

但等一会儿，物化视图会在后台更新，结果会快速返回。

SQL> /

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899460 7495.35823 22.2905352 5.00276078 2.17647059

Elapsed: 00:00:00.00
SQL> 

这并不是理想的情况。首先，它不是实时的，插入或更新的数据不会立即可见。此外，不管你是否需要，都会有一个查询在运行以更新物化视图（这个可以设置成任何时间间隔，或者按需更新）。不过，这确实展示了如果你能接受数据不是完全实时的，物化视图可以让最终用户感受到更快的响应速度。