最近在折腾一个几十 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
完整结果如下，未注明的单位都是秒，越暗的格子越快：
| page size (bytes) |
1024 |
2048 |
4096 |
8192 |
16384 |
32768 |
| file size (bytes) |
6,518,057,984 |
8,241,479,680 |
8,241,385,472 |
8,231,018,496 |
6,586,433,536 |
6,584,467,456 |
| synchronous |
FULL |
OFF |
FULL |
OFF |
FULL |
OFF |
FULL |
OFF |
FULL |
OFF |
FULL |
OFF |
| HDD |
INSERT
INTO |
a |
6.12 |
5.65 |
6.12 |
5.58 |
6.00 |
5.52 |
5.87 |
5.44 |
5.75 |
5.40 |
5.75 |
5.46 |
| b |
48.25 |
41.17 |
57.02 |
44.04 |
62.04 |
42.46 |
55.00 |
41.23 |
45.53 |
30.62 |
45.74 |
31.13 |
| c |
48.15 |
39.72 |
56.36 |
44.20 |
56.54 |
46.77 |
55.66 |
45.71 |
44.79 |
36.63 |
46.76 |
36.72 |
SELECT
COUNT
(*) |
before
VACUUM |
a1 |
0.23 |
0.21 |
0.22 |
0.19 |
0.55 |
0.48 |
0.26 |
0.21 |
0.21 |
0.17 |
0.20 |
0.19 |
| b1 |
71.14 |
96.80 |
126.71 |
150.36 |
139.94 |
161.63 |
51.64 |
71.43 |
39.61 |
59.25 |
40.71 |
61.68 |
| c1 |
13.30 |
15.01 |
36.66 |
35.39 |
13.73 |
15.34 |
7.94 |
7.59 |
3.43 |
3.30 |
1.24 |
0.97 |
| a2 |
0.04 |
0.03 |
0.23 |
0.16 |
0.55 |
0.55 |
0.34 |
0.35 |
0.20 |
0.17 |
0.22 |
0.17 |
| b2 |
0.44 |
0.44 |
127.56 |
129.53 |
140.05 |
139.97 |
81.47 |
83.51 |
45.77 |
45.33 |
40.60 |
40.31 |
| c2 |
0.06 |
0.05 |
36.48 |
36.49 |
13.81 |
15.69 |
6.16 |
6.04 |
2.99 |
3.13 |
0.89 |
0.67 |
after
VACUUM |
a1 |
0.23 |
0.21 |
0.21 |
0.19 |
0.55 |
0.50 |
0.33 |
0.32 |
0.21 |
0.22 |
0.21 |
0.20 |
| b1 |
71.36 |
80.18 |
127.12 |
130.90 |
139.91 |
140.16 |
82.01 |
85.37 |
45.35 |
48.86 |
40.66 |
41.78 |
| c1 |
0.03 |
0.03 |
0.17 |
0.20 |
0.48 |
0.45 |
0.33 |
0.21 |
0.18 |
0.02 |
0.18 |
0.01 |
| a2 |
0.03 |
0.03 |
0.21 |
0.18 |
0.54 |
0.55 |
0.28 |
0.28 |
0.20 |
0.22 |
0.21 |
0.19 |
| b2 |
0.44 |
0.44 |
128.20 |
130.51 |
140.32 |
139.92 |
81.33 |
82.61 |
45.28 |
45.02 |
40.88 |
39.88 |
| c2 |
0.03 |
0.03 |
0.20 |
0.19 |
0.48 |
0.45 |
0.31 |
0.31 |
0.18 |
0.20 |
0.17 |
0.18 |
| VACUUM |
1413.67 |
1079.27 |
1549.42 |
1280.28 |
1354.26 |
1193.45 |
948.60 |
876.00 |
792.43 |
654.82 |
682.16 |
648.19 |
| SSD |
INSERT
INTO |
a |
5.80 |
5.64 |
5.72 |
5.55 |
5.67 |
5.50 |
5.61 |
5.44 |
5.48 |
5.34 |
5.50 |
5.40 |
| b |
46.05 |
33.73 |
56.61 |
43.68 |
57.33 |
45.03 |
55.73 |
42.34 |
45.33 |
34.08 |
46.67 |
33.79 |
| c |
46.19 |
35.76 |
55.46 |
46.39 |
52.51 |
44.86 |
51.04 |
43.74 |
41.89 |
32.56 |
41.31 |
32.99 |
SELECT
COUNT
(*) |
before
VACUUM |
a1 |
0.07 |
0.07 |
0.07 |
0.07 |
0.22 |
0.21 |
0.06 |
0.06 |
0.07 |
0.06 |
0.06 |
0.06 |
| b1 |
17.82 |
28.53 |
50.74 |
62.63 |
60.28 |
72.55 |
17.93 |
29.57 |
14.41 |
25.82 |
14.01 |
24.77 |
| c1 |
0.84 |
0.87 |
0.99 |
0.96 |
0.50 |
0.50 |
0.37 |
0.50 |
0.11 |
0.30 |
0.05 |
0.05 |
| a2 |
0.04 |
0.03 |
0.07 |
0.07 |
0.22 |
0.21 |
0.13 |
0.13 |
0.10 |
0.10 |
0.08 |
0.08 |
| b2 |
0.49 |
0.49 |
50.75 |
50.70 |
60.18 |
60.75 |
38.38 |
39.44 |
22.63 |
23.53 |
18.55 |
20.09 |
| c2 |
0.06 |
0.06 |
0.99 |
0.98 |
0.19 |
0.50 |
0.28 |
0.27 |
0.10 |
0.13 |
0.04 |
0.03 |
after
VACUUM |
a1 |
0.07 |
0.07 |
0.07 |
0.07 |
0.22 |
0.21 |
0.14 |
0.13 |
0.10 |
0.10 |
0.09 |
0.08 |
| b1 |
17.48 |
18.96 |
50.61 |
51.12 |
60.18 |
62.64 |
39.35 |
42.17 |
23.88 |
26.85 |
18.49 |
22.16 |
| c1 |
0.03 |
0.03 |
0.06 |
0.03 |
0.19 |
0.02 |
0.12 |
0.01 |
0.09 |
0.01 |
0.07 |
0.00 |
| a2 |
0.03 |
0.03 |
0.07 |
0.07 |
0.21 |
0.21 |
0.13 |
0.13 |
0.10 |
0.10 |
0.08 |
0.08 |
| b2 |
0.49 |
0.49 |
50.64 |
51.12 |
60.28 |
60.53 |
38.43 |
38.50 |
22.64 |
22.82 |
20.21 |
18.87 |
| c2 |
0.04 |
0.03 |
0.06 |
0.06 |
0.20 |
0.19 |
0.12 |
0.12 |
0.09 |
0.00 |
0.08 |
0.01 |
| VACUUM |
594.84 |
610.94 |
714.03 |
716.23 |
530.24 |
610.41 |
436.40 |
439.43 |
330.01 |
344.00 |
337.77 |
338.67 |
跑完了之后发现这个程序并不好，比如：插入的数据过于整齐，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
|
发表于 2024-1-24 11:25