SQLite是否在WHERE子句中使用多个AND条件优化查询？

小编典典

SQLite是否在WHERE子句中使用多个AND条件优化查询？

python

在SQL数据库（我使用Python + Sqlite）中，如何确保（如果我们有100万行）查询

SELECT * FROM mytable WHERE myfunction(description) < 500 AND column2 < 1000
                           [-----------------------------]   [--------------]
                               high-CPU cost condition         easy-to-test 
                              requiring 100 µs per test         condition

是否已优化，以便仅在易于测试的第二个条件已经为True 时才测试
第一个条件（CPU昂贵）？（因为这是合乎逻辑的AND，所以这是懒惰的AND吗？）

例：

如果始终测试第一个条件，则将需要100万x 100 µs = 100秒！
如果先测试第二个条件，则仅对5000个项目进行预过滤（在我的用例中），然后应用第一个条件将非常快。

注意：

column2不一定是ID，也可能是其他ID
在我的用例中，myfunction涉及Levenshtein距离计算

阅读 222

2021-01-20

共1个答案

小编典典

（根据评论和后续测试更新了答案。）

您问题的实际答案

如何确保如果我们有100万行，则对查询…进行优化，以便仅在易于测试的第二个条件已经为True时才测试第一个条件（CPU昂贵）？

取决于

WHERE子句中的实际条件，以及
SQLite查询优化器在估算这些条件的成本方面有多聪明。

一个简单的测试应该告诉您查询是否可以充分“优化”以满足您的需求。好消息是，至少在某些情况下，SQLite 将首先 执行轻松（廉价）条件。

对于测试表“ mytable”

CREATE TABLE mytable (
    description TEXT(50) NOT NULL,
    column2 INTEGER NOT NULL,
    CONSTRAINT mytable_PK PRIMARY KEY (column2)
);

包含一百万行

description  column2
-----------  -------
row000000          0
row000001          1
row000002          2
...
row999999     999999

Python测试代码

import sqlite3
import time

log_file_spec = r'C:\Users\Gord\Desktop\log_file.txt'

def myfunc(thing):
    with open(log_file_spec, 'a') as log:
        log.write('HODOR\n')
    return(int(thing[-6:]))


with open(log_file_spec, 'w'):
    pass  # just empty the file
cnxn = sqlite3.connect(r'C:\__tmp\SQLite\test.sqlite')
cnxn.create_function("myfunction", 1, myfunc)
crsr = cnxn.cursor()
t0 = time.time()
sql = """\
SELECT COUNT(*) AS n FROM mytable
WHERE myfunction(description) < 500 AND column2 < 1000
"""
crsr.execute(sql)
num_rows = crsr.fetchone()[0]
print(f"{num_rows} rows found in {(time.time() - t0):.1f} seconds")

cnxn.close()

退货

500 rows found in 1.2 seconds

并计数log_file.txt中的行，我们看到

C:\Users\Gord>find /C "HODOR" Desktop\log_file.txt

---------- DESKTOP\LOG_FILE.TXT: 1000

表明我们的函数仅被调用了1000次，而不是一百万次。SQLite显然已经应用了column2 < 1000第一个myfunction(description) < 500条件，然后将该条件应用于了第一个条件的行子集。

（原始的“袖手旁观”答案。）

您问题的实际答案取决于查询优化器的智能程度。一个简单的测试应该告诉您查询是否可以充分“优化”以满足您的需求。

但是，如果您的测试发现您的原始方法太慢，则有两种选择：

选项1：尝试简单比较“第一个”

更改顺序可能会影响查询计划，例如

... WHERE <easy_condition> AND <expensive_condition>

可能会比

... WHERE <expensive_condition> AND <easy_condition>

选项2：尝试使用子查询强制执行顺序

再次，取决于查询优化器的智能性

SELECT easy.* 
FROM 
    (SELECT * FROM mytable WHERE column2 < 1000) easy
WHERE myfunction(easy.description) < 500

可以先应用便宜的条件，然后再将昂贵的条件应用于结果的行子集。（但是，有注释表明，SQLite太复杂了，无法接受这种策略。）

2021-01-20