SQLite查询max()函数的性能

Question

SQLite查询max()函数的性能

4

我有一张表格，大约有150万行和三个列。列'timestamp'的类型为REAL并被索引。我通过PHP PDO访问SQLite数据库。

以下三个查询在不到1毫秒的时间内运行：

select timestamp from trades
select timestamp + 1 from trades
select max(timestamp) from trades

下面的选择器需要近半秒钟的时间：

select max(timestamp) + 1 from trades

为什么会这样呢？

编辑： Lasse要求一个“解释查询计划”，由于我目前没有SQLite3命令行工具直接访问，所以我在PHP PDO查询中运行了它。我猜这没关系，这是结果：

explain query plan select max(timestamp) + 1 from trades:
    [selectid] => 0
    [order] => 0
    [from] => 0
    [detail] => SCAN TABLE trades (~1000000 rows)

explain query plan select max(timestamp) from trades:
    [selectid] => 0
    [order] => 0
    [from] => 0
    [detail] => SEARCH TABLE trades USING COVERING INDEX tradesTimestampIdx (~1 rows)

- Toxiro

2

尝试从SQLite3命令行工具执行以下语句：explain query plan select max(timestamp)+1 from trades; 并告诉我们它的含义，然后对于 explain query plan select max(timestamp) from trades; 做同样的事情。 - Lasse V. Karlsen

由于PHP使用了过时的SQLite版本，无法正确优化最后一个查询。 - CL.

@CL。你能提供一个参考资料吗？这个资料可能会支持我下面的解释。 - Tim Biegeleisen

在最新的SQLite版本中，最后两个查询非常快。在任何SQLite版本中，只有在您不读取所有结果时，前两个查询才会很快。 - CL.

我已经添加了explain query plan的结果。我猜Tim的答案是正确的？有没有在SQL中的变通方法？当然，在运行查询之后，我可以在PHP中添加一个变通方法，但是选择目前是一个子查询，我更喜欢保持这样。好的，我可能会用当前版本的SQLite测试这个。 - Toxiro

您的SQLite似乎没有在缓慢查询上使用索引。 - Tim Biegeleisen

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tim Biegeleisen · Accepted Answer

这个查询的原因是：

select max(timestamp) + 1 from trades

查询引擎需要计算每条记录的MAX值，然后将其加1，这就是查询时间过长的原因。计算MAX值需要进行全表扫描，由于每个记录都需要加1，因此必须重复扫描。

在查询中：

select timestamp + 1 from trades

你正在为每个记录进行计算，但引擎只需要扫描整个表一次。在这个查询中。

select max(timestamp) from trades

引擎确实需要扫描整个表，但只需扫描一次。

来自SQLite文档：

包含单个MIN()或MAX()聚合函数的查询，其参数为索引最左侧列的查询可能可以通过进行单个索引查找而不是扫描整个表来满足。

我在文档中强调了可能，因为如果列x不是索引最左侧的列，则SELECT MAX(x)+1 FROM table形式的查询可能需要扫描整个表。