我有一个大约有150万行和三列的表。列'timestamp'的类型是REAL,并已索引。我正在通过PHP PDO访问SQLite数据库。
以下三个选择在不到一毫秒的时间内运行:
select timestamp from trades
select timestamp + 1 from trades
select max(timestamp) from trades
下面的select几乎需要半秒:
select max(timestamp) + 1 from trades
为什么?
编辑:Lasse要求"解释查询计划",我已经在PHP PDO查询中运行了这个,因为我目前没有直接的SQLite3命令行工具访问。我想没关系,结果如下:
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)
查询原因
select max(timestamp) + 1 from trades
花费的时间太长了,以至于查询引擎必须为每条记录计算MAX
值,然后给它加1。计算MAX
值需要执行一次全表扫描,必须对每条记录重复此操作,因为要在值上添加1。
查询
select timestamp + 1 from trades
您对每条记录进行计算,但是引擎只需要扫描整个表一次。在这个查询
中select max(timestamp) from trades
引擎必须扫描整个表,但是它也只扫描一次。
来自SQLite文档:
包含单个MIN()或MAX()聚合函数的查询,其参数为索引的最左列,可以通过执行单个索引查找而不是扫描整个表来满足。
我强调了文档中的可能,因为看起来对于表单SELECT MAX(x)+1 FROM table
的查询可能需要全表扫描如果列x
不是索引最左边的列