给定一个微秒精度的半开UNIX时间戳间隔表 [start_time_us; stop_time_us):
CREATE TABLE interval(
start_time_us INTEGER NOT NULL,
stop_time_us INTEGER NOT NULL
);
我想选择与给定查询间隔相交的间隔 [:start_time_us,:stop_time_us):
SELECT * FROM interval
WHERE start_time_us < :stop_time_us
AND :start_time_us < stop_time_us
ORDER BY start_time_us;
为了让它更快,我创建了这些覆盖指数:
CREATE INDEX interval_start_idx
ON interval(start_time_us, stop_time_us);
CREATE INDEX interval_stop_idx
ON interval(stop_time_us, start_time_us);
但是,如果我EXPLAIN QUERY PLAN
上面的查询,我得到:
QUERY PLAN
`--SEARCH interval USING INDEX interval_start_idx (start_time_us<?)
据我所知,这意味着SQLite将从interval_start_idx
开始迭代start_time_us < :stop_time_us
索引B树的行,只返回具有:start_time_us < stop_time_us
的行。平均来说,它会迭代一半的B树,这是 O(N),这太慢了。
反思一下,这是有意义的,因为要使用第二个索引并只迭代B树的所需部分,SQLite需要知道每行的start_time_us < stop_time_us
。即使它知道这一点(例如)。CHECK
约束),它还不够聪明,无法计算出这样的优化。
我可以在子查询中从一个索引转换为另一个索引:
SELECT * FROM interval
WHERE
(
SELECT start_time_us
FROM interval
WHERE :start_time_us < stop_time_us
ORDER BY stop_time_us
LIMIT 1
) <= start_time_us
AND start_time_us < :stop_time_us
ORDER BY start_time_us;
生产:
QUERY PLAN
|--SEARCH interval USING INDEX idx_interval_start (start_time_us>? AND start_time_us<?)
`--SCALAR SUBQUERY 1
`--SEARCH interval USING INDEX idx_interval_stop (stop_time_us>?)
但它只适用于非重叠区间。
我怎么能在对数时间内做到这一点呢?
1条答案
按热度按时间dsekswqp1#
我看到的工作是使用有界运算符
BETWEEN
,而不是开放式的<
或>
,这在逻辑上是等价的,但可能会欺骗优化器更好地使用索引:- 1
是为了满足stop_time_us
的排他性和BETWEEN
的包容性。