我有一个查询涉及时差小于 2 小时的几行(~0.08333 天):
SELECT mt1.*, mt2.* FROM mytable mt1, mytable mt2
WHERE ABS(JULIANDAY(mt1.date) - JULIANDAY(mt2.date)) < 0.08333
这个查询相当慢,即 ~ 1 秒(表有 ~ 10k 行)。
一个想法是使用一个INDEX
。明显地CREATE INDEX id1 ON mytable(date)
没有什么改善,很正常。
然后我注意到神奇的查询CREATE INDEX id2 ON mytable(JULIANDAY(date))
-
使用时没有帮助:
... WHERE ABS(JULIANDAY(mt1.date) - JULIANDAY(mt2.date)) < 0.08333
-
使用时没有帮助:
... WHERE JULIANDAY(mt2.date) - 0.08333 < JULIANDAY(mt1.date) < JULIANDAY(mt2.date) + 0.08333
-
...但在使用时极大地提高了性能(查询时间愉快地除以 50!):
... WHERE JULIANDAY(mt1.date) < JULIANDAY(mt2.date) + 0.08333
AND JULIANDAY(mt1.date) > JULIANDAY(mt2.date) - 0.08333
当然 1.、2. 和 3. 是等价的,因为在数学上,
|x-y| < 0.08333 <=> y - 0.08333 < x < y + 0.08333
<=> x < y + 0.08333 AND x > y - 0.08333
问题:为什么解决方案 1. 和 2. 没有使用 INDEX,而解决方案 3. 正在使用它?
Note:
我正在使用 Python + Sqlitesqlite3
module
-
解决方案 1. 和 2. 没有使用索引这一事实在执行时得到了确认EXPLAIN QUERY PLAN SELECT ...
:
(0, 0, 0, u'SCAN TABLE mytable AS mt1')
(0, 1, 1, u'SCAN TABLE mytable AS mt2')
事实解决方案 3. 正在使用索引,在执行时显示EXPLAIN QUERY PLAN SELECT ...
:
(0, 0, 1, u'SCAN TABLE mytable AS mt2')
(0, 1, 0, u'SEARCH TABLE mytable AS mt1 USING INDEX id2 (<expr>>? AND <expr><?)')
我相信,包括AND
推理如下:
查询中的 WHERE 子句被分解为“术语”,其中每个术语
通过 AND 运算符与其他运算符分隔。如果 WHERE 子句
由 OR 运算符分隔的约束组成,然后整个
子句被认为是 OR 子句所对应的单个“术语”
应用优化。
SQLite 查询优化器概述
可能值得跑步ANALYZE看看情况是否有所改善。
根据评论:
我认为前面添加的段落可以阐明为什么 ABS(x-y) 表达式 ...
添加了以下内容。
为了可供索引使用,术语必须采用以下形式之一:
列=表达式
列 IS 表达式
列 > 表达式
列 >= 表达式
列 列 表达式=列
表达式 > 列
表达式 >= 列
表达式 表达式 列 IN(表达式列表)
列 IN(子查询)
列为空
我不确定它是否适用于BETWEEN (e.g. WHERE column BETWEEN expr1 AND expr2
).
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)