python - 参数替换后让sqlite3进行查询优化

Question

我只是将性能问题追溯到 sqlite3 中的查询优化（我正在使用 Python 接口，但我不确定它是否相关，问题可能与 sqlite API 有关）。

我在下面描述了这样一个例子。

我的表看起来像这样，有数百万条记录：

CREATE TABLE articles (
  id integer primary key,
  title text,
)
CREATE TABLE "tags"  (
  tag text,
  article_id integer not null,
  foreign key(article_id) references articles(id),
)

这是我要运行的请求：

SELECT tag                                                                            
FROM "tags" AS t1                                                             
INNER JOIN                                                                                
    (                                                                                      
    SELECT id                                                                              
    FROM "articles"                                                                    
    LIMIT (?)                                                                            
    ) t2                                                                                   
ON t1.article_id = t2.id                                                              

我观察到，如果限制参数是硬编码的，那么查询会更快（0.01s vs 15s）。

使用set_trace_callback，我看到查询完全一样。

但是，explain没有给出相同的结果。

我得出的结论是 SQLite 查询优化器在参数替换之前运行。

确实，如果我将查询修改为

SELECT t1.tag FROM
    (
    SELECT id
    FROM "articles"
    LIMIT (?)
    ) as t2 CROSS JOIN
    "tags" as t1
WHERE 
    t1.article_id = t2.id

它的工作速度与硬编码限制相同，因为 CROSS JOIN 允许控制 for 循环（链接）的顺序。

因此，我得出结论，查询优化器不会以相同的顺序运行嵌套循环。

在这个玩具示例中，有一个明显更好的顺序。但是，在其他情况下，我可能希望查询优化器在参数替换后运行。

有没有办法在仍然使用防止 SQL 注入的安全方法的同时做到这一点？

简单的解决方案是验证限制是整数并使用简单的字符串替换。

标签： pythonsqlite