Python SQLite模块的连接速度比手动慢

4 投票

3 回答

2154 浏览

提问于 2025-04-16 00:32

我正在使用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模块会这么慢。

数据处理数据库连接 sqlite 数据库模块数据库优化 mac os x 查询性能 apsw

3 个回答

我想更新一下这个内容，因为我也遇到了同样的问题，现在已经是2022年了...

在我自己的应用程序中，我使用的是python3和sqlite3来处理一些大数据库（超过10万行，超过200列）。特别是，我发现我在python中执行的三个表的内连接大约需要12分钟，而在命令行中用sqlite3执行同样的连接查询只需要大约100秒。所有的连接条件都有正确的索引，而EXPLAIN QUERY PLAN显示，增加的时间很可能是因为我使用了SELECT *，在我的特定情况下这是个不得已的选择。

这种性能差异让我整晚都在抓头发，直到我意识到这里有个快速解决办法：从命令行运行Sqlite3脚本。这最多算是个权宜之计，但我有研究要做，所以这是我的解决方法。

把查询写到一个.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()

在python中运行os.system，把.sql文件发送给sqlite3

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

确保在运行之前关闭任何打开的连接，这样你就不会被锁住，如果你之后还想在python中继续使用sqlite，就需要重新创建连接对象。

希望这能帮到你，如果有人找到了这个问题的根源，请分享链接！

回答于 2025-04-16 由 Python大师

分享举报

你没有提供相关表格的结构，不过我觉得可能是索引出了问题，特别是没有在 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)

回答于 2025-04-16 由 Python大师

分享举报

这里有个讨论：http://www.mail-archive.com/python-list@python.org/msg253067.html

看起来在sqlite3这个模块中，有性能瓶颈的问题。这里有一些建议可以让你的查询更快：

确保在连接的列上有索引
使用pysqlite

回答于 2025-04-16 由 Python大师

分享举报

Python SQLite模块的连接速度比手动慢

3 个回答

撰写回答