最近在折腾一个几十 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 只有索引——猜猜哪个更快?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
#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