在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经过优化,只有在易于测试的第二个条件已经为真的情况下,才会测试第一个条件(CPU昂贵)？ (因为它是一个逻辑AND,它是一个懒惰的AND？)
例：
>如果始终测试第一个条件,则需要100万x100μs= 100秒！
>如果首先测试第二个条件,那么只有5000个项目会被预过滤(在我的用例中),然后应用第一个条件会非常快.
注意：
> column2不是必需的ID,也可能是其他内容
>在我的用例中,myfunction涉及Levenshtein距离计算
最佳答案 (根据评论和后续测试更新了答案.)
你问题的实际答案
how to make sure that, if we have 1 million rows, the query … is optimized so that the 1st condition (CPU-expensive) is only tested if the easy-to-test second condition is already True?依赖于取决于
> 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表明我们的功能只被称为一千次,而不是一百万次. SQLite已明确应用了column2 (原创“袖口”答案.)
问题的实际答案取决于查询优化器的聪明程度.一个简单的测试应该告诉您,您的查询是否会根据您的需求进行充分“优化”.
但是,如果您的测试发现原始方法太慢,您确实有几个选项：
选项1：尝试进行简单比较“第一次”
更改订单可能会影响查询计划,例如
... WHERE AND
可能会变得更快
... WHERE AND
选项2：尝试使用子查询强制执行订单
同样,取决于查询优化器的聪明
SELECT easy.*
FROM
(SELECT * FROM mytable WHERE column2 < 1000) easy
WHERE myfunction(easy.description) < 500可能首先应用廉价条件,然后在得到的行子集上应用昂贵的条件. (但是,一条评论表明SQLite太复杂了,不适合这种策略.)
