sqlite3 select min, max 一起选择比单独选择要慢得多 [英] sqlite3 select min, max together is much slower than select them separately

问题描述

sqlite> explain query plan select max(utc_time) from RequestLog;
0|0|0|SEARCH TABLE RequestLog USING COVERING INDEX key (~1 rows) # very fast

sqlite> explain query plan select min(utc_time) from RequestLog;
0|0|0|SEARCH TABLE RequestLog USING COVERING INDEX key (~1 rows) # very fast

sqlite> explain query plan select min(utc_time), max(utc_time) from RequestLog;
0|0|0|SCAN TABLE RequestLog (~8768261 rows) # will be very very slow

虽然我分别使用 min 和 max，但它工作得很好.但是，当我出于某种原因同时选择 min 和 max 时，sqlite 会忘记"索引.有没有我可以做的配置(我已经使用了 Analyze，它不起作用)?或者对这种行为有什么解释吗?

While I use min and max separately, it works perfectly. However, sqlite will 'forget' the index while I select the min and max together for some reason. Is there any configuration I can do (I used Analyze already, it won't work)? or is there any explanation for this behavior?

sqlite> .schema 

CREATE TABLE FixLog(
                    app_id text,  __key__id INTEGER,
                    secret text, trace_code text, url text,
                    action text,facebook_id text,ip text,
                    tw_time datetime,time datetime,
                    tag text,to_url text,
                    from_url text,referer text,weight integer,
                    Unique(app_id, __key__id)
                    );
CREATE INDEX key4 on FixLog(action);
CREATE INDEX time on FixLog(time desc);
CREATE INDEX tw_time on FixLog(tw_time desc);



sqlite> explain query select min(time) from FixLog;
0|0|0|SEARCH TABLE FixLog USING COVERING INDEX time (~1 rows)
sqlite> explain query select max(time) from FixLog;
0|0|0|SEARCH TABLE FixLog USING COVERING INDEX time (~1 rows)
sqlite> explain query plan select max(time), min(time) from FixLog;
0|0|0|SCAN TABLE FixLog (~1000000 rows)

推荐答案

这是 sqlite 查询优化器的一个已知怪癖，如下所述:http://www.sqlite.org/optoverview.html#minmax:

This is a known quirk of the sqlite query optimizer, as explained here: http://www.sqlite.org/optoverview.html#minmax:

假设存在适当的索引，将优化以下形式的查询以在对数时间内运行:

Queries of the following forms will be optimized to run in logarithmic time assuming appropriate indices exist:

 SELECT MIN(x) FROM table;
 SELECT MAX(x) FROM table;

为了进行这些优化，它们必须完全按照上面显示的形式出现 - 只更改表和列的名称.不允许添加 WHERE 子句或对结果进行任何算术运算.结果集必须包含单列.MIN 或 MAX 函数中的列必须是索引列.

In order for these optimizations to occur, they must appear in exactly the form shown above - changing only the name of the table and column. It is not permissible to add a WHERE clause or do any arithmetic on the result. The result set must contain a single column. The column in the MIN or MAX function must be an indexed column.

UPDATE (2017/06/23):最近，这已更新为包含单个 MAX 或 MIN 的查询可能会被索引查找满足(允许诸如算术之类的事情)；但是，它们仍然排除在单个查询中具有多个这样的聚合运算符(因此 MIN,MAX 仍然会很慢):

UPDATE (2017/06/23): Recently, this has been updated to say that a query containing a single MAX or MIN might be satisfied by an index lookup (allowing for things like arithmetic); however, they still preclude having more than one such aggregation operator in a single query (so MIN,MAX will still be slow):

包含单个 MIN() 或 MAX() 聚合函数(其参数是索引的最左侧列)的查询可能会通过执行单个索引查找而不是扫描整个表来满足.示例:

Queries that contain a single MIN() or MAX() aggregate function whose argument is the left-most column of an index might be satisfied by doing a single index lookup rather than by scanning the entire table. Examples:

SELECT MIN(x) FROM table;
SELECT MAX(x)+1 FROM table;

这篇关于sqlite3 select min, max 一起选择比单独选择要慢得多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！