sqlite 最佳化筆記

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做了一下測試。

建立三個表：
create table t1 
(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 idxT1 on t1(num,word1,word2);
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列中都包含有部分相同值，但是它們在表中出現的順序不同。

速度測試結果：
1) select count(*) from t1,t3 where t1.word2=t3.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》