翻译:Betty

在这篇文章中,我想与你分享在Python中有效使用SQLite的一些技巧。SQLite是一个非常好用的数据库,实现高效存储中小规模的数据。它支持SQL的大多数公共特性。最可贵的是,Python用户不需要安装任何程序就可以开始使用SQLite,标准库中都带有SQLite3模块。

标准库SQLite(pysqlite)的文档比较详尽,所以我重复了。相反,这里要论述SQLite中提升性能的最佳方法。我也会分享一些你可能不了解的技巧。希望这些信息对你有所帮助。

执行、并发和自动提交

Pysqlite因怪异的事务性语义而臭名昭著。默认情况下,当你以书面形式进行第一次问题查询的时候,pysqlite将打开一个执行文件(这是通过检查你的每个查询来实现的)。当你调用connection.commit()或进行任何一个除了SELECT, INSERT, UPDATE或DELETE之外的查询(例如CREATE TABLE 或 PRAGMA)的时候,该操作将自动执行。

这样一来,写入就变得很容易,这就要求SQLite全局写锁,并且当你的查询命令为SELECT等的时候,也会遇到不必要的写锁,而这种情况是无需写锁的。因为SQLite的每一个数据库只允许写入一个程序,尽可能缩短所写入的内容才合乎你的最大利益。pysqlite的语义可能使人误以为SQLite完全不适合任何需要并行数据库存取的应用程序,尽管所写入的内容可以迅速执行。

有几种方法来解决全局写锁和pysqlite不良行为所造成的有问题的相互作用。最普遍的方法是使用预写日志(WAL)journal_mode选项。WAL模式允许多个读取与单个写入共存。通常,当一个连接对写入的内容进行锁定时,没有任何其他连接可以写入或读取,直到解除锁定为止。当另一个连接向数据库写入时,WAL模式允许进行读取操作,从而放松了这些限制。

在自动提交模式中使用pysqlite,以及在明确管理事务状态的应用程序中使用pysqlite,还可以优化性能。通过这种方式,你可以确保只有在绝对必要时才用到写锁。除非你明确地发出一个BEGIN语句,打开一项事务处理,否则所有的语句都将在它们各自的事务中独立执行。这意味着写锁被限定在所需的最短时间内。正如我前面提到的:所写入的内容可以迅速执行。这就允许在许多连接中以高性能的方式向数据库写入,即使一次只能写入一项。

另一种更为激烈的方法可能适用于某些应用程序,即保持专用的写线程,并将所有的写入发送到单个连接(这里here有更详细的描述)。与其他建议一样,在高强度的写入压力下,可能出现延迟的问题。队列可能会变得太大,当一个应用程序线程需要对写入进行验证之后再继续的时候,就会导致滞后的现象出现。因此,这种方法只适用于某些场景。

# Open database in autocommit mode by setting isolation_level to None.
conn = sqlite3.connect('app.db', isolation_level=None)

# Set journal mode to WAL.
conn.execute('pragma journal_mode=wal')

查看区别。

### Default behavior.

import sqlite3

writer = sqlite3.connect('/tmp/scratch.db', isolation_level=None)
reader = sqlite3.connect('/tmp/scratch.db', isolation_level=None)

writer.execute('create table foo (data)')
reader.execute('select * from foo;')  # No problem.

writer.execute('begin exclusive;')
reader.execute('select * from foo;')  # OperationalError: database is locked

### WAL-mode.

writer = sqlite3.connect('/tmp/wal.db', isolation_level=None)
writer.execute('pragma journal_mode=wal;')

reader = sqlite3.connect('/tmp/wal.db', isolation_level=None)
reader.execute('pragma journal_mode=wal;')

writer.execute('create table foo (data)')
reader.execute('select * from foo')  # No problem.

writer.execute('begin exclusive')  # Acquire write lock.
reader.execute('select * from foo')  # Still no problem!

您可以用一个简单的上下文管理器来处理事务:

from contextlib import contextmanager

@contextmanager
def transaction(conn):
    # We must issue a "BEGIN" explicitly when running in auto-commit mode.
    conn.execute('BEGIN')
    try:
        # Yield control back to the caller.
        yield
    except:
        conn.rollback()  # Roll back all changes if an exception occurs.
        raise
    else:
        conn.commit()

我的经验是:通过确保迅速写入,可以使SQLite非常适合基于网络的、多线程应用程序。WAL日志模式和自动提交模式也有助于确保我对于锁定和事务状态的假设是正确的。

用户自定义函数

SQLite嵌入内存中,和你的应用程序一起运行。这就使你可以轻松地运用自己的Python代码对SQLite加以扩展。SQLite提供相当多的钩子,其中一个合理的子集是由标准库的数据库驱动程序实现。库文档非常详细,并提供代码示例:

功能最强大的钩子不可以通过标准库来获得,而是我们可以用编程方式获得定义完整表格的能力(这需要利用普通的SQL)。使用虚拟表格API可以创建完全动态的表格。或者,换一种方式来说,创建返回表格数据的用户自定义的函数。

apsw SQLite的驱动程序提供了在Python中实现虚拟表格的钩子,但API与C 语言的等价程序密切相关,对于简单的使用案例有可能变得棘手。一个更简单的方法是通过vtfunc库来实现(这个方法将包括在peewee3.x中)。

现在我们来看一个使用vtfunc的简单的例子。在这里,我们会创建一个返回表格结果的用户自定义的函数(可连接、过滤、排序,等等,就像任何正常的查询那样)。

生成整数序列

作为一个简单的例子,让我们重新实现SQLite 生成序列的延伸。它类似于Python的范围内建指令。我们先定义一个类来实现两个方法(初始化和迭代)并定义两个必需的属性(参数、列):

import sqlite3
from vtfunc import TableFunction


class Series(TableFunction):
    params = ['start', 'stop', 'step']  # These are the arguments to our function.
    columns = ['output']  # These are the columns our output rows will have.
    name = 'series'  # Optional -- if unspecified, uses lower-case class name.

    def initialize(self, start=0, stop=None, step=1):
        pass

    def iterate(self, idx):
        pass

当我们的函数第一次被调用时,调用者所指定的参数被传递给初始化函数。参数可能有默认值。然后,当数据库需要用到表中的某一行(以便填入一个结果集),iterate()方法就会被调用,返回一行数据;或者如果没有更多的数据可用,调用StopIteration:

class Series(TableFunction):
    params = ['start', 'stop', 'step']  # These are the arguments to our function.
    columns = ['idx', 'output']  # These are the columns our output rows will have.
    name = 'series'  # Optional -- if unspecified, uses lower-case class name.

    def initialize(self, start=0, stop=None, step=1):
        self.start = self.curr = start
        self.stop = stop if stop is not None else float('inf')
        self.step = step

    def iterate(self, idx):
        if (self.step > 0 and self.curr >= self.stop) or \
           (self.step < 0 and self.curr <= self.stop):
            raise StopIteration
        ret = self.curr
        self.curr += self.step
        return (idx, ret)

为了开始使用我们的新系列函数,我们将用一个连接来记录它,然后查询它:

conn = sqlite3.connect(':memory:')
Series.register(conn)

for row in conn.execute('SELECT * FROM series(0, 10, 2)'):
    print row

# (0, 0)
# (1, 2)
# (2, 4)
# (3, 6)
# (4, 8)

print conn.execute('SELECT * FROM series(0, NULL, 20) LIMIT 4').fetchall()
# [(0, 0), (1, 20), (2, 40), (3, 60)]

使用虚拟表格,甚至有可能在一个完全独立的、像Redis或CSV数据那样的数据源中暴露一个SQL接口。

这里有更多的例子:

有用的语法

SQLite需要零配置来启动和运行,但为了快速运行,你可能要调整一些设置。SQLite的配置是通过执行特殊查询的形式PRAGMA <setting> = <value>。以下是我经常作出的一些修改:

journal_mode = wal  提前写好的日志意味着多个读取可以与单个的写入并存。通常,当获取写锁时,没有其他连接可以写入或读取!因此,这对于阅读量大的多线程应用程序有很大帮助。在大多数情况下,WAL模式的速度也明显加快。

  • cache_size = -size in KiB  默认的缓存大小是~2MB。通常,您希望缓存足够大,以便将工作数据保存在内存中,因此可以相应地调整大小。警告:正值被当做页数来处理,负值被视为KiB。
  • mmap_size = size in bytes!可能是更高性能的I/O密集型应用程序,也可能使用更少的内存,因为这些页数可以与操作系统的缓存共享。查看文档了解更多细节。
  • synchronous = 0  谨慎使用!禁用同步会导致操作系统崩溃或突然断电时数据损坏。然而,应用程序崩溃不会导致这种模式下的数据丢失。

编译标志

许多配置适合于老版本的SQLite,它不包括一些很酷的扩展模块。这是我通常用来编译SQLite的一些标志:

  • SQLITE_ALLOW_COVERING_INDEX_SCAN=1 --使覆盖指标优化
  • SQLITE_DEFAULT_CACHE_SIZE=-8000 --更理智的默认
  • SQLITE_DEFAULT_SYNCHRONOUS=0  --更快,只可能由于断电或系统崩溃而出现故障。
  • SQLITE_DEFAULT_WAL_SYNCHRONOUS=0
  • SQLITE_DISABLE_DIRSYNC --当文件被删除时,小的优化可以减少同步
  • SQLITE_ENABLE_FTS3 --使所有的全文搜索得以扩展!
  • SQLITE_ENABLE_FTS3_PARENTHESIS
  • SQLITE_ENABLE_FTS4
  • SQLITE_ENABLE_FTS5
  • SQLITE_ENABLE_JSON1 --使原来的JSON支持
  • SQLITE_ENABLE_STAT4 --使统计延伸
  • SQLITE_ENABLE_UPDATE_DELETE_LIMIT --允许使用关于UPDATE和DELETE查询的LIMIT子句。
  • SQLITE_STMTJRNL_SPILL=-1 --不把语句日志泄漏到磁盘
  • SQLITE_TEMP_STORE=3 --不把磁盘用于临时存储
  • SQLITE_USE_URI允许URI --连接字符串

对于调试/剖析,以下附加标志可能是有用的:

  • SQLITE_ENABLE_COLUMN_METADATA --使额外的元数据可用。
  • SQLITE_ENABLE_DBSTAT_VTAB --更多的统计数据!查看文档。
  • SQLITE_ENABLE_EXPLAIN_COMMENTS --增加了额外的信息来解释输出。
  • SQLITE_ENABLE_IOTRACE --增加iotrace命令到shell,用于低级的I/O日志记录。
  • SQLITE_ENABLE_STMT_SCANSTATUS --看文档。

编译SQLite的新版本,你可以运行下面这样的代码:

fossil clone http://www.sqlite.org/cgi/src sqlite.fossil
mkdir sqlite-src
cd sqlite-src/
fossil open ../sqlite.fossil

export CFLAGS="-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_JSON1..."  # etc...
export CFLAGS="$CFLAGS -fPIC -O2"
export PREFIX="$(pwd)"
LIBS="-lm" ./configure --enable-static --enable-shared --prefix="$PREFIX"
make && make install

你可以通过对你的自定义生成SQLite进行静态链接,创建独立的pysqlite库:

git clone https://github.com/ghaering/pysqlite
cd pysqlite/
cp $PREFIX/sqlite3.c .  # Copy sqlite3.c into checkout.
echo -e "library_dirs=$PREFIX/lib" >> setup.cfg
echo -e "include_dirs=$PREFIX/include" >> setup.cfg
LIBS="-lm" python setup.py build_static

可以使用静态链接pysqlite,就像你通常使用标准库的sqlite3模块那样:

from pysqlite2 import dbapi2 as sqlite3

conn = sqlite3.connect(':memory:')
print conn.execute('PRAGMA compile_options').fetchall()
# [(u'ALLOW_COVERING_INDEX_SCAN',),
#  (u'DEFAULT_CACHE_SIZE=-8000',),
#  (u'ENABLE_FTS3',),
#  (u'ENABLE_FTS3_PARENTHESIS',),
#  etc...

更多链接

要了解更多相关的信息,下面是一些有用的链接: