每次查询后重新打开sqlite数据库的效率如何

Question

我现在正在用tornado搭建一个网页服务器，但遇到了一些问题，就是不同的代码片段同时想要访问数据库。

为了简化这个问题，我创建了一个查询函数，基本上是这样做的（其实稍微复杂一点）：

def query(command, arguments = []):
    db = sqlite3.open("models/data.db")
    cursor = db.cursor()
    cursor.execute(command, arguments)
    result = cursor.findall()
    db.close()
    return result

我只是想知道，每次查询后重新打开数据库效率怎么样（我猜这应该是一个非常耗时的操作，或者它会缓存一些东西之类的？），还有没有更好的方法来处理这个问题。

Answer 1

为什么不每隔N秒就重新连接一次呢？在我的ajax预读取和数据库服务中，代码大概有30到40行，我每小时重新连接一次来获取更新。如果你需要处理实时数据，有更合适的数据库可以选择：

t0 = time.time()
con = None
connect_interval_in_sec = 3600

def myconnect(dbfile=<path to dbfile>):
    try:
        mycon = sqlite3.connect(dbfile)
        cur = mycon.cursor()
        cur.execute('SELECT SQLITE_VERSION()')
        data = cur.fetchone()
    except sqlite3.Error as e:
        print("Error:{}".format(e.args[0]))
        sys.exit(1)
    return mycon

在主循环中：

if con is None or time.time()-t0 > connect_interval_in_sec:
    con = myconnect()
    t0 = time.time()
<do your query stuff on con>

Answer 2

如果你想知道某个东西有多低效，最好的办法就是自己写个测试，亲自看看。

我先修复了你提供的例子中的错误，让它能正常工作，然后写了代码来创建一个测试案例，来测试它的表现。用timeit来计时其实很简单，就像往常一样。

你可以查看这个链接：http://pastebin.com/rd39vkVa

那么，运行这个测试后会发生什么呢？

$ python2.7 sqlite_test.py 10000
reopen: 2.02089715004
reuse:  0.278793811798
$ python3.3 sqlite_test.py 10000
reopen: 1.8329595914110541
reuse:  0.2124928394332528
$ pypy sqlite_test.py 10000
reopen: 3.87628388405
reuse:  0.760829925537

打开数据库的时间大约是执行一个非常简单的查询（这个查询几乎不返回任何结果）所需时间的4到8倍。这就是最糟糕的情况。

Answer 3

我想补充一个自己的回答，因为我不同意目前被接受的答案。那个答案说这个操作不是线程安全的，但这完全是错误的 - SQLite使用文件锁，根据当前平台来确保所有访问都符合ACID标准。

在Unix系统上，这将使用fcntl()或flock()锁，这是一种针对每个文件句柄的锁。因此，发布的代码每次都建立一个新连接，这样就会始终分配一个新的文件句柄，因此SQLite自己的锁机制会防止数据库损坏。由此可见，在NFS共享或类似环境中使用SQLite通常是个坏主意，因为这些环境往往不提供特别可靠的锁（不过这也取决于你的NFS实现）。

正如@abernert在评论中提到的，SQLite在多线程方面有问题，但这与线程之间共享单个连接有关。正如他提到的，如果你使用一个全局的连接池，当第二个线程从池中取出一个回收的连接时，就会出现运行时错误。这种问题在测试时可能不容易发现（比如负载很轻，可能只有一个线程在使用），但在后期可能会造成麻烦。Martijn Pieters后来的建议是使用线程本地的连接池，这样应该没问题。

根据SQLite常见问题解答，从版本3.3.1开始，只要连接不持有任何锁，实际上在线程之间传递连接是安全的 - 这是SQLite作者在批评线程使用时做出的让步。任何合理的连接池实现都会确保在替换池中的连接之前，所有事务都已经提交或回滚，因此如果不是因为Python对共享的检查，实际上全局的连接池是安全的。我相信即使使用更新版本的SQLite，这个检查仍然存在。我的Python 2.7.3系统中有一个sqlite3模块，sqlite_version_info报告的是3.7.9，但如果你从多个线程访问它，仍然会抛出RuntimeError。

无论如何，只要这个检查存在，连接就不能有效地共享，即使底层的SQLite库支持它。

至于你最初的问题，确实每次创建一个新连接的效率比保持一个连接池要低，但正如之前提到的，这需要一个线程本地的连接池，这实现起来有点麻烦。创建新连接的开销主要是打开文件并读取头部，以确保它是一个有效的SQLite文件。实际执行语句的开销更高，因为它需要进行锁定并执行相当多的文件输入输出操作，因此大部分工作实际上是在执行语句和/或提交时才进行。

有趣的是，至少在我查看过的Linux系统上，执行语句的代码会重复读取文件头的步骤 - 因此，打开一个新连接并不会太糟，因为打开连接时的初始读取会将头部加载到系统的文件系统缓存中。所以这归结为打开一个文件句柄的开销。

我还应该补充一点，如果你希望你的代码能够处理高并发，那么SQLite可能不是一个好的选择。正如他们自己的网站指出的，它并不适合高并发，因为所有访问都必须通过一个全局锁，这在并发线程数量增加时会造成性能下降。如果你使用线程只是为了方便，那没问题，但如果你真的期待高并发，那我建议避免使用SQLite。

总之，我认为你每次打开连接的方法并不是那么糟糕。使用线程本地的连接池能提高性能吗？可能是的。这个性能提升会明显吗？在我看来，除非你看到非常高的连接率，否则不会太明显，而在那种情况下，你会有很多线程，所以你可能还是想远离SQLite，因为它对并发处理得并不好。如果你决定使用连接池，确保在将连接返回池之前清理它 - SQLAlchemy有一些连接池功能，即使你不想要所有的ORM层，也可能会对你有帮助。

编辑

正如合理指出的，我应该附上实际的时间测试。这些来自一个相对低配置的VPS：

>>> timeit.timeit("cur = conn.cursor(); cur.execute('UPDATE foo SET name=\"x\"
    WHERE id=3'); conn.commit()", setup="import sqlite3;
    conn = sqlite3.connect('./testdb')", number=100000)
5.733098030090332
>>> timeit.timeit("conn = sqlite3.connect('./testdb'); cur = conn.cursor();
    cur.execute('UPDATE foo SET name=\"x\" WHERE id=3'); conn.commit()",
    setup="import sqlite3", number=100000)
16.518677949905396

你可以看到大约3倍的差异，这并不算小。然而，绝对时间仍然在毫秒以下，所以除非你每个请求都做很多查询，否则可能还有其他地方需要优化。如果你做很多查询，一个合理的折中方案可能是每个请求建立一个新连接（但不需要连接池的复杂性，每次重新连接即可）。

对于读取（即SELECT），每次连接的相对开销会更高，但绝对的墙钟时间开销应该是一致的。

正如在这个问题的其他地方已经讨论过的，你应该用真实的查询进行测试，我只是想记录我所做的事情，以得出我的结论。