SQLite是否在WHERE子句中使用多个AND条件优化查询?
在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距离计算
-
(根据评论和后续测试更新了答案。)
您问题的实际答案
如何确保如果我们有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太复杂了,无法接受这种策略。)