python的sqlite3条件查询_python – 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

经过优化,只有在易于测试的第二个条件已经为真的情况下,才会测试第一个条件(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太复杂了,不适合这种策略.)

标签：python,sql,sqlite,lazy-evaluation,sqlite3

来源： https://codeday.me/bug/20190727/1549947.html