SQLite 优化笔记
2010/04/30 17:16最近在折腾一个几十 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 只有索引——猜猜哪个更快？http://blog.ieph.net/archives/316
关于索引：
在进行多个表联合查询的时候，使用索引可以显著的提高速度，刚才用SQLite做了一下测试。建立三个表：
(id integer primary key,
num integer not null,
word1 text not null,
word2 text not null);
create table t2
(id integer primary key,
num integer not null,
word1 text not null,
word2 text not null);
create table t3
(id integer primary key,
num integer not null,
word1 text not null,
word2 text not null);
建立若干索引：
t1表：在num，word1，word2上有复合索引
t2表：在num，word1，word2上各有一个索引
t3表：在word1上有一个索引
create index idxT2Num on t2(num);
create index idxT2Word1 on t2(word1);
create index idxT2Word2 on t2(word2);
create index idxT3Word1 on t2(word1);
向三个表中各插入10000行数据，其中num项为随机数字，word1和word2中是英文单词，三个表中对应的num，word1和word2列中都包含有部分相同值，但是它们在表中出现的顺序不同。
速度测试结果：
很慢（t3.word2上没有索引）
2) select count(*) from t3,t1 where t1.word2=t3.word2;
很慢（t1.word2上没有独立索引）
3) select count(*) from t1,t2 where t1.word2=t2.word2;
很快（t2.word2上有索引）
4) select count(*) from t2,t1 where t1.word2=t2.word2;
很慢（t1.word2上没有独立索引）
5) select count(*) from t1,t2 where t1.num=t2.num;
很快（t2.num上有索引）
6) select count(*) from t2,t1 where t1.num=t2.num;
很快（t1的复合索引中，第一个列是num）
7) select count(*) from t1,t3 where t1.num=t3.num;
很慢（t3.num上没有索引）
8) select count(*) from t3,t1 where t1.num=t3.num;
很快（t1的复合索引中，第一个列是num）
结论：在from子句后面的两个表中，如果第2个表中要查询的列里面带有索引，这个查询的速度就快，反之就慢。比如第三个查询，from后面的第2个表是 t2，t2在word2上有索引，所以这个查询就快，当输入SQL命令并回车后，查询结果就立即显示出来了，但是如果使用第4个查询命令（即把t1和t2 的位置互换），查询起来却用了1分零6秒。
可见索引的建立对于提高数据库查询的速度是非常重要的。
更多关于SQLite查询优化的知识可以参考《Chris Newman》写的《SQLite》一书的第四章：《Query Optimization》