python - Python 中的 Sqlite3 模块比 Shell 中的 SELECT 慢得多

Question

我在 Python 中使用 sqlite3 模块，但我发现它对于某个 SELECT 查询来说非常慢，相对于在命令 shell 中运行 sqlite3 中的查询。首先我会说这两个版本都是相同的 3.7.17。

我的查询是

SELECT r.ID, r.Date FROM my_table r
WHERE
r.Date IN (SELECT Date FROM my_table WHERE ID = r.ID GROUP BY Date LIMIT 2);

Python代码是

con = lite.connect(path_to_database)
cur = con.cursor()

with con:
    cur.execute(sql_query)

其中 sql_query 是包含初始查询的字符串变量。

我假设问题出在优化 IN 子查询上。

性能详情:my_table 包含 167000 条记录，在 shell 中的查询需要大约 10 秒，在 Python 中的查询需要 > 5 分钟(到这里我停止了它)。

目前，由于它是表创建，我只是将代码复制并粘贴到 shell 中作为解决方法，我该如何解决这个问题以便我可以从 Python 运行查询？

添加

当我运行 EXPLAIN QUERY PLAN 时，我得到以下信息

shell :

0           0           0           SCAN TABLE PIT_10_Days AS r (~500000 rows)
0           0           0           EXECUTE CORRELATED LIST SUBQUERY 1
1           0           0           SEARCH TABLE PIT_10_Days USING AUTOMATIC C
1           0           0           USE TEMP B-TREE FOR GROUP BY

python :

0           0           TABLE PIT_10_Days AS r 
0           0           TABLE PIT_10_Days

我不确定差异是在 Python 中获取 EXPLAIN QUERY PLAN 的问题，还是问题本身。

Answer 1

很抱歉这么晚，但是我现在才发现这个问题。
不幸的是，我不知道为什么 sqlite3 模块的行为与 shell 不同，但是您可以尝试从一开始就避免相关查询。我不确定它是否总能按照您的意愿行事，因为您没有在子查询中对结果进行排序。

我想您想要每个 ID 的两个最新日期？试试这个:

SELECT r.ID AS ID, max( r.Date ) AS Date
 FROM my_table AS r
 GROUP BY r.ID

UNION

SELECT r.ID, max( r.Date )
  FROM 
      my_table AS r
      JOIN ( 
        SELECT ID,
               max( Date ) AS Date
          FROM my_table
         GROUP BY ID) AS maxDat
      ON
      r.ID = maxDat.ID AND
      r.Date != maxDat.Date
 GROUP BY r.ID;

它选择 ID 及其最新日期。然后它将此结果与从表中取出实际最新日期的类似选择统一起来，以便您获得第二个最新日期。如果您需要的不仅仅是最新的两个日期，这将变得非常麻烦，但对于两个日期来说应该没问题，而且可能会快得多。