使用 Python 的 SQLite 模块连接比手动连接慢

Question

我正在使用 python 内置的 sqlite3 模块来访问数据库。我的查询在一个包含 150000 个条目的表和一个包含 40000 个条目的表之间执行联接，结果再次包含大约 150000 个条目。如果我在 SQLite Manager 中执行查询，则需要几秒钟，但是如果我从 Python 执行相同的查询，一分钟后它还没有完成。这是我使用的代码：

cursor = self._connection.cursor()
annotationList = cursor.execute("SELECT PrimaryId, GOId " + 
                                "FROM Proteins, Annotations " +
                                "WHERE Proteins.Id = Annotations.ProteinId")
annotations = defaultdict(list)
for protein, goterm in annotationList:
    annotations[protein].append(goterm)

我执行 fetchall 只是为了测量执行时间。有人对性能上的巨大差异有解释吗？我在 Mac OS X 10.6.4 上使用 Python 2.6.1。

我手动实现了连接，而且速度更快。代码如下所示：

cursor = self._connection.cursor()
proteinList = cursor.execute("SELECT Id, PrimaryId FROM Proteins ").fetchall()
annotationList = cursor.execute("SELECT ProteinId, GOId FROM Annotations").fetchall()
proteins = dict(proteinList)
annotations = defaultdict(list)
for protein, goterm in annotationList:
    annotations[proteins[protein]].append(goterm)

因此，当我自己获取表然后在 Python 中进行连接时，大约需要 2 秒。上面的代码需要永远。我在这里错过了什么吗？

我尝试了与 apsw 相同的操作，并且效果很好（代码不需要完全改变），性能很棒。我仍然想知道为什么 sqlite3 模块的速度这么慢。

Answer 1

这里有一个关于它的讨论： http://www.mail -archive.com/[email protected]/msg253067.html

看来sqlite3模块存在性能瓶颈。有一个建议如何加快查询速度：

• 确保您在连接列上有索引，
• 使用 pysqlite

Answer 2

您尚未发布相关表的架构，但我认为索引可能存在问题，特别是 Proteins.Id 或 Annotations.ProteinId （或两者）上没有索引。

像这样创建 SQLite 索引

CREATE INDEX IF NOT EXISTS index_Proteins_Id ON Proteins (Id)
CREATE INDEX IF NOT EXISTS index_Annotations_ProteinId ON Annotations (ProteinId)

Answer 3

我想更新这个，因为我注意到同样的问题，我们现在是 2022 年......

在我自己的应用程序中，我使用 python3 和 sqlite3 在大型数据库上进行一些数据整理（> 100000 行 * > 200 列）。特别是，我注意到我的 3 个表内部联接在 python 中的运行时间约为 12 分钟，而从 CLI 在 sqlite3 中运行相同的联接查询则需要约 100 秒。所有连接谓词都已正确索引，并且 EXPLAIN QUERY PLAN 指示添加的时间很可能是因为我正在使用 SELECT *，这在我的特定上下文中是必要的邪恶。

性能差异让我整晚都抓狂，直到我意识到这里有一个快速解决方案：从命令行运行 Sqlite3 脚本。这绝对是最好的解决方法，但我有研究要做，所以这是我的解决办法。

1. 将查询写入 .sql 文件（我使用 f-strings 来传递变量，因此我在这里使用了带有 {foo} 的示例）

fi = open("filename.sql", "w")
fi.write(f"CREATE TABLE {Foo} AS SELECT * FROM Table1 INNER JOIN Table2 ON Table2.KeyColumn = Table1.KeyColumn INNER JOIN Table3 ON Table3.KeyColumn = Table1.KeyColumn;")
fi.close()

2. 从 python 内部运行 os.system 并将 .sql 文件发送到 sqlite3

os.system(f"sqlite3 {database} < filename.sql")

确保关闭在运行此命令之前，请先检查任何打开的连接，这样您就不会最终被锁定，并且如果您要返回到 python 中的 sqlite 中工作，则必须在之后重新实例化任何连接对象。

希望这会有所帮助，如果有人找到了它的来源，请链接到它！