最近在折腾一个几十 G 的 SQLite 数据库，里边有十来个表，大都有数千万条数据，结果是一个 SELECT COUNT(*) 都一个小时没动静……于是翻了些资料优化了一下，以下是流水账：

1、将数据库从 HDD 转移到 SSD 。由于 SSD 的 IOPS 是 HDD 的数十倍，某些查询可以有十倍以上的提升。不过 SSD 空间实在有限，如果能把索引独立存放就好了……

从 HDD 到 SSD ，SELECT COUNT(*) 所花的时间：

~ 5000 s   ⇒   546 s

2、VACUUM 。这个命令用于删除那些留给插入更新的多余空间，据说还能清理磁盘碎片，可以提升两倍左右的检索速度，不过相当花时间占空间。SQLite 要先在临时文件夹建立和数据库相当大小的文件，再在数据库文件夹建立和数据库相当大小的 journal 文件。也就是说数据库所在驱动器和临时文件夹都要保证足够的空间。可以设置系统 TMP 或者用 PRAGMA temp_store_directory 改变临时文件夹得位置，也可以用 PRAGMA journal_size_limit 设置 journal 文件的上限。官网上说 VACUUM 的速度是 2M/S ，我试了下就算在 HDD 上至少也有 4M/S ，SSD 上可以达到 10M/S 以上，这个时间也和数据库的整齐程度有关，不过对于大数据库而言还是很慢就是了。

VACUUM 前后，SELECT COUNT(*) 所花的时间：

546 s   ⇒   205 s

3、设置 page_size 。这个情况似乎比较复杂，对于小表而言不同的 page_size 几乎没有什么区别，但是大表可以有五倍的差距。默认的编译参数下 SQLite 的 page_size 取值可以是 512 、1024 、2048 、4096 、8192 、16384 和 32768 ，默认是 1024 ，这和 Linux 的 cluster size 一致，而 NTFS 是 4K ，有人说设置为 4K 可以提高性能，不过我试了下 4K 差不多是最差（大部分情况下 2K 更差），倒是 8K 开始有大提升，16K 和 32K 差的不是很远。一般而言 page_size 越大速度越快，系统负担越重，不过也有很多其它因素的影响，比如不同的 page_size 下数据库文件的大小有差别，大 page_size 不利于内存缓存某些数据以备重复查询等。

page_size 从 1024 改为 32768 ，SELECT COUNT(*) 所花的时间：

205 s   ⇒   45 s

4、cache_size 按理说也是有影响的，不过我尝试了不同的 cache_size 几乎没有区别，是内存不够的原因？

5、对于一般的数据库，主键比索引要快，但是 SQLite 似乎是个例外，因为主键似乎是和数据存在一起的，读取时会浪费很多时间在无用的数据上，尤其当表中存在巨大的 TEXT 数据时非常明显；而索引是单独存放的，反而比主键要快，如果表中一行数据的容量很大，那么甚至可能有一百倍的差距。

主键 vs 索引，SELECT COUNT(*) 所花的时间：

45 s   ⇒   1 s

下面这个程序比较了不同因素的影响。其中建立三个表，并存入数量相同的数据。表 a 和表 b 的结构是完全相同的，只不过表 a 插入的是短字符串，而表 b 插入的是大段文字；表 c 和表 b 插入的数据是完全相同的，只不过表 b 有主键，而 c 只有索引——猜猜哪个更快？

#import sqlite3
from pysqlite2 import dbapi2 as sqlite3
from time import time
import sys
 
def db_init(conn):
 
    conn.execute('CREATE TABLE a (id INTEGER PRIMARY KEY, value TEXT)')
    conn.execute('CREATE TABLE b (id INTEGER PRIMARY KEY, value TEXT)')
    conn.execute('CREATE TABLE c (id INTEGER, value TEXT)')
    conn.execute('CREATE INDEX c_id ON c (id)')
    conn.commit()
 
def db_insert(conn):
 
    print 'Benchmark of "INSERT INTO":',
 
    small_text = 'a text'
    large_text = 'a very very very very long text ' * 100
 
    prev_time = time()
    for i in xrange(1000000): conn.execute('INSERT INTO a VALUES (?, ?)', (i, small_text))
    conn.commit()
    print 'a = %.2fs;' % (time() - prev_time),
 
    prev_time = time()
    for i in xrange(1000000): conn.execute('INSERT INTO b VALUES (?, ?)', (i, large_text))
    conn.commit()
    print 'b = %.2fs;' % (time() - prev_time),
 
    prev_time = time()
    for i in xrange(1000000): conn.execute('INSERT INTO c VALUES (?, ?)', (i, large_text))
    conn.commit()
    print 'c = %.2fs.' % (time() - prev_time)
 
def db_count(conn):
 
    print 'Benchmark of "SELECT COUNT(*)":',
 
    prev_time = time()
    conn.execute('SELECT COUNT(*) FROM a').fetchall()
    print 'a = %.2fs;' % (time() - prev_time),
 
    prev_time = time()
    conn.execute('SELECT COUNT(*) FROM b').fetchall()
    print 'b = %.2fs;' % (time() - prev_time),
 
    prev_time = time()
    conn.execute('SELECT COUNT(*) FROM c').fetchall()
    print 'c = %.2fs.' % (time() - prev_time)
 
def db_vacuum(conn):
 
    print 'Benchmark of "VACUUM":',
 
    prev_time = time()
    conn.execute('VACUUM')
    print '%.2fs.' % (time() - prev_time)
 
if __name__ == '__main__':
 
    conn = sqlite3.connect('test.db')
    conn.execute('PRAGMA page_size = %s' % sys.argv[1]) # 1024, 2048, 4096, 8192, 16384, 32768
    conn.execute('PRAGMA synchronous = %s' % sys.argv[2]) # FULL, OFF
 
    db_init(conn)
    db_insert(conn)
    db_count(conn)
    db_count(conn)
    db_count(conn)
    db_vacuum(conn)
    db_count(conn)
    db_count(conn)
    db_count(conn)
 
    conn.close()

这里还顺便测试了 INSERT INTO 和 VACUUM 的时间，不过没有 SELECT COUNT(*) 那么波澜起伏就是了。

db_count 算三次是考察缓存对查询的影响，一般第一次查询非常慢，后面就很快了，第二次和第三次的时间通常差不多，下面只显示前两次的。

运行环境：
CPU ：Intel i7 860
RAM ：8G
HDD ：WD15EARS （西数绿盘我自重……）
SSD ：Intel X25M 80G
OS ：Windows 7 x64
Python ： 2.6.5 final amd64
Pysqlite ：2.6.0
SQLite ：3.6.23.1

完整结果如下，未注明的单位都是秒，越暗的格子越快：

跑完了之后发现这个程序并不好，比如：插入的数据过于整齐，VACUUM 对性能几乎没有影响，而文件大小倒是大起大落；程序运行中间由于没有停顿，上一步操作的写入没完成就开始了下一步，由于 SQLite 的写入是异步的，这可能会影响计时。不过由于我不想再跑一遍了，姑且就以这个结果来说明吧。

首先 c 表很有意思，插入的效率和 b 表相当，但检索的效率有时甚至比 a 表还要快，这个与其说是数据库的改进方向还不如说是 SQLite 的 bug 。另外注意程序中没有用自带的 sqlite3 模块而是用了重新编译的 pysqlite2 ，这是因为 Python 2.6.5 final win32 amd64 的 sqlite3 是 2.5.9 版本，这个版本上 c 表在各种情况下都比 b 表慢。而 3.6.4 版本中引入了独特的 INDEXED BY 语句，我本想试试指定一个独立存放的索引能不能提升性能，结果 SQLite 已经优化了索引的选择。

即便是在这个 6G 的数据库中，page_size = 1024 性能也相当理想，也是唯一能缓存数据以备第二次检索的。将 page_size 设为 32768 对检索的改进都微乎其微（插入和 VACUUM 倒是不小）。至于 2048 和 4096 会导致文件大小增加了五分之一，速度也就狠狠地慢下来了。看来一般情况下 page_size 使用默认设置对于数据库性能和系统占用都是比较理想的，如果是数 G 以上的数据库倒是可以提高 page_size 。

在检索和 VACUUM 时 SSD 的速度大约是 HDD 的三倍，而 INSERT 几乎没有区别。由于 SSD 有写入寿命的限制，对于这种不怎么更新的数据库，似乎插入完成后再存到 SSD 比较好。

关掉 synchronous 对于插入也有三倍左右的提升，网上有人说 SSD 上 nosync 插入有 150 倍的速度提升，可是我没碰到。另外检索和 VACUUM 反而下降了是怎么回事？想不通……话说回来官网的测试也有不少 nosync 反而更快的结果。

总之 SQLite 的参数要根据实际情况来选择，而实际情况很复杂，可能还是要多测试——得到这样一个说了等于没说的结论。

原文地址：http://blog.ieph.net/archives/316