设为首页 加入收藏

TOP

SQLite 优化笔记(一)
2012-02-01 13:19:45 】 浏览:4901
Tags:SQLite 优化 笔记

最近在折腾一个几十 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

4cache_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

首页 上一页 1 2 下一页 尾页 1/2/2
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇SQlite索引 下一篇sqlite插入数据sql语句

最新文章

热门文章

Hot 文章

Python

C 语言

C++基础

大数据基础

linux编程基础

C/C++面试题目