Back to home

Sqlite 数据删除方案

问题

一个基于 Sqlite 的系统空间不够,数据库部分就吃掉了近 70+% 。由于没有数据迁移方案,只能先删一部分。

DELETE FROM

原计划是直接删掉一部分半年前的数据,发现两点问题。

  • 删除不会释放空间 只是将空间标记为可重用
  • 慢 几十G的数据,删除指令执行后通常无法正常返回,需要手工杀死

对于第一点可以执行 VACUUM 进行事后压缩,但是压缩指令无法执行其他事务。

A VACUUM will fail if there is an open transaction, or if there are one or more active SQL statements when it is run.

至于删除指令过慢,没有找到具体的原因。可能是数据量过大。有人推荐用事务,但是我将多个 ID 使用 WHERE id IN (id1, id2, id3..) 子句合并成为一条提交,应该不存在事务的问题。

INSERT INTO

有人建议检索出来插入新的数据库,然后将老库直接删掉。
检索出来很简单,除了基本的 [.dump][http://www.sqlite.org/sqlite.html] 指令,还可以做条件导出:

sqlite3 {db_name} <<!
.headers on
.mode insert {table_name}
.out {db_name}.new.sql
SELECT * FROM {table_name} WHERE {condition};
!

用真实数据库名称,表名,条件子句替换后,执行的结果就是一条插入SQL,我们只需要在目标数据库执行就行。
如何拷贝数据库表结构 ?

sqlite3 {db_name} .schema | sqlite3 {db_name}.new.db

最后在新库执行导出的SQL就行。

由于数据量大,导入非常的慢,8000条记录花了60s。

INSERT INTO .. SELECT ..

上面导出的SQL是一条条的 INSERT INTO,每一条记录插一次,而不是 INSERT INTO table VALUES (record1, recod2…) 这样。有人推荐改成 INSERT INTO table SELECT d1 as c1, d2 as c2 UNION SELECT d3, d4 的形式 via。因为插入多行(INSERT INTO .. VALUES)非标准SQL,Sqlite不支持。评论中已经有人回复了,Sqlite 从 2012-03-20 (3.7.11) 已经开始支持这种语法。

我就将单条插入改为多条插入。执行语句时又发现问题。

SQLite error: too many terms in compound SELECT

原因是 SELECT 连接语句过多,这个限制是个硬编码 SQLITE_MAX_COMPOUND_SELECT,默认是 500。这个好解决,取模 500 然后重新生成插入语句即可。

查询完(还是很慢),导入到新库,旧库改名。最后算是跌跌撞撞完成了这次迁移。

总结

原想吐槽下Sqlite,但是把它用到几十G的地方分明就是杀牛用宰鸡刀,应用架构有问题。不过再看了 StackOverflow上还有一篇优化问答后,发现能够将 Insert-per-second 弄到9000+,觉得还有调优的机会,以后再找机会吧。

存储本来就是一个核心问题,做设计的时候一定要考虑到后期的拓展,一味贪小图快,这就是下场。