SQLite使用中的几个问题
一、Sqlite删除记录后数据库文件大小不变
原因是:
sqlite采用的是变长纪录存储,当你从Sqlite删除数据后,未使用的磁盘空间被添加到一个内在的”空闲列表”中用于存储你下次插入的数据,用于提高效率,磁盘空间并没有丢失,但也不向操作系统返回磁盘空间,这就导致删除数据乃至清空整个数据库后,数据文件大小还是没有任何变化,还是很大。
解决方法:两种
1、手动:
在数据删除后,手动执行VACUUM命令,执行方式很简单
sqlite> vacuum;
在Navicat中可以直接执行,eg:
DELETE FROM Company WHERE Age < 190;VACUUM;
但是此语句在C#代码中执行不生效,可以查看下文的方法。
2、自动:
在数据库文件建成中,将auto_vacuum设置成“1”。
参考:
压缩Sqlite数据文件大小,解决数据删除后占用空间不变的问题
扩展:C# 中如何解决?
方法也是两种:
1、手动释放空间
先写一个执行sql语句的函数:
private void ExecuteSql(string sDbPath, string sqlStr) { using (SQLiteConnection conn = new SQLiteConnection("data source = " + sDbPath)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); cmd.CommandText = sqlStr; cmd.ExecuteNonQuery(); conn.Close(); } } }
在删除数据表/大量数据后,调用上述函数(dbPath为数据库的地址)。
ExecuteSql(dbPath, "VACUUM");
参考代码:SQLiteRepository.cs
以上代码视sqlite-net.dll 的版本而改变,
SQLiteCommand cmd = new SQLiteCommand(SQLiteConnection); //sqlite-net-pcl\1.7.335 SQLiteCommand SQLiteCmd = SQLiteConnection.CreateCommand(sqlStr); //sqlite-net-pcl\1.4.118
2、设置数据库为自动释放空间
当数据库中无数据表时,设置其属性:
ExecuteSql(dbPath, "PRAGMA auto_vacuum = 1;");
测试了一下发现效果也挺好的。
比较两种方法,自动更方便。但是需要注意的是,在进行频繁的插入、更新、删除操作时,数据库会产生大量的内存碎片。自动释放空间的方法只能释放空闲数据页,但是并不会对内存碎片进行整理,在这个过程中反而会产生额外的内存碎片;
而手动方式可以同时释放空闲空间和整理内存碎片。
推荐使用第一种方式。
二、Sqlite执行效率优化
实践:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/// <summary> /// 执行时间测试 /// </summary> public class ExecuteTimeHelper { public static void Test() { SQLiteConnection connection = Run(() => new SQLiteConnection("Test.db"), "连接对象初始化并打开连接"); Run(() => connection.CreateTable<Info>(), "创建数据表Info"); SQLiteCommand command = Run(() => new SQLiteCommand(connection), "命令对象初始化"); Run(() => { command.CommandText = $"DELETE FROM Info;VACUUM;UPDATE sqlite_sequence SET seq ='0' where name ='Info';"; command.ExecuteNonQuery(); }, "执行DELETE命令及收缩数据库"); int count = 200; Run(() => { for (int i = 0; i < count; i++) { command.CommandText = $"INSERT INTO Info(Name, Age) VALUES ('A{i:000}','{i}')"; command.ExecuteNonQuery(); //将返回操作所影响的记录条数 } command.ExecuteScalar<object>(); }, $"[---不使用事务---]事务执行INSERT命令,插入{count}条数据"); Run(() => { command.CommandText = $"DELETE FROM Info;VACUUM;UPDATE sqlite_sequence SET seq ='0' where name ='Info';"; command.ExecuteNonQuery(); }, "执行DELETE命令及收缩数据库"); Run(() => connection.BeginTransaction(), "开始事务"); int count2 = 3000; Run(() => { for (int i = 0; i < count2; i++) { command.CommandText = $"INSERT INTO Info(Name, Age) VALUES ('A{i:000}','{i}')"; command.ExecuteNonQuery(); } var result = command.ExecuteScalar<object>(); }, $"[---使用事务---]执行INSERT命令,插入{count2}条数据"); Run(() => connection.Commit(), "提交事务"); //或者用事务方法 执行批量sql语句 connection.RunInTransaction(() => { int count2 = 3000; Run(() => { for (int i = 0; i < count2; i++) { command.CommandText = $"INSERT INTO Info(Name, Age) VALUES ('A{i:000}','{i}')"; command.ExecuteNonQuery(); } var result = command.ExecuteScalar<object>(); }, $"[---使用事务---]执行INSERT命令,插入{count2}条数据"); }); Run(() => connection.Close(), "关闭连接"); Console.ReadKey(); } public static void Run(Action action, string description) { Stopwatch sw = Stopwatch.StartNew(); action(); Console.WriteLine($"--> {description}: {sw.ElapsedMilliseconds}ms"); } public static T Run<T>(Func<T> func, string description) { Stopwatch sw = Stopwatch.StartNew(); T result = func(); Console.WriteLine($"--> {description}: {sw.ElapsedMilliseconds}ms"); return result; } } class Info { public long ID { set; get; } public string Name { set; get; } public long Age { set; get; } }
- 无论是执行插入或查询操作,使用事务比不使用事务快,尤其是在批量插入操作时,减少得时间非常明显;比如在不使用事务的情况下插入3000条记录,执行所花费的时间为17.252s,而使用事务,执行时间只用了0.057s,效果非常明显,而SQL Server不存在这样的问题。
- 不能每次执行一条SQL语句前开始事务并在SQL语句执行之后提交事务,这样的执行效率同样是很慢,最好的情况下,是在开始事务后批量执行SQL语句,再提交事务,这样的效率是最高的。
即使只插入一条数据也是有很大差异的:
三、SQLite.SQLiteException异常: database is locked、Busy
这就涉及到sqlite的并发读写问题:
1.sqlite3支持多线程同时读操作,但不支持多线程同时写操作。
2.同一时刻只能有一个线程去进行写操作,并且在一个线程进行写操作的时候,其他线程是不能进行读操作的。
当一个线程正在写操作时,其他线程的读写都会返回操作失败的错误,显示数据库文件被锁住。
1、sqlite3的锁及事务类型
- UNLOCKED ,无锁状态。数据库文件没有被加锁。
- SHARED 共享状态。数据库文件被加了共享锁。可以多线程执行读操作,但不能进行写操作。
- RESERVED 保留状态。数据库文件被加保留锁。表示数据库将要进行写操作。
- PENDING 未决状态。表示即将写入数据库,正在等待其他读线程释放 SHARED 锁。一旦某个线程持有 PENDING 锁,其他线程就不能获取 SHARED 锁。这样一来,只要等所有读线程完成,释放 SHARED 锁后,它就可以进入 EXCLUSIVE 状态了。
- EXCLUSIVE 独占锁。表示它可以写入数据库了。进入这个状态后,其他任何线程都不能访问数据库文件。因此为了并发性,它的持有时间越短越好。
说明:
- 当执行select即读操作时,需要获取到SHARED锁(共享锁),
- 当执行insert/update/delete操作(即内存写操作时),需要进一步获取到RESERVERD锁(保留锁),
- 当进行commit操作(即磁盘写操作时),需要进一步获取到EXCLUSIVE锁(排它锁)。
- 对于RESERVERD锁,sqlite3保证同一时间只有一个连接可以获取到保留锁,也就是同一时间只有一个连接可以写数据库(内存),但是其它连接仍然可以获取SHARED锁,也就是其它连接仍然可以进行读操作(这里可以认为写操作只是对磁盘数据的一份内存拷贝进行修改,并不影响读操作)。
- 对于EXCLUSIVE锁,是比保留锁更为严格的一种锁,在需要把修改写入磁盘即commit时需要在保留锁/未决锁的基础上进一步获取到排他锁,顾名思义,排他锁排斥任何其它类型的锁,即使是SHARED锁也不行,所以,在一个连接进行commit时,其它连接是不能做任何操作的(包括读)。
- PENDING锁(即未决锁),则是比较特殊的一种锁,它可以允许已获取到SHARED锁的事务继续进行,但不允许其它连接再获取SHARED锁,当已存在的SHARED锁都被释放后(事务执行完成),持有未决锁的事务就可以获得commit的机会了。sqlite3使用这种锁来防止writer starvation(写饿死)。
sqlite3只支持库级锁,库级锁意味着什么?
意味着同时只能允许一个写操作,也就是说,即事务T1在A表插入一条数据,事务T2在B表中插入一条数据,这两个操作不能同时进行,即使你的机器有100个CPU,也无法同时进行,而只能顺序进行。表级都不能并行,更别说元组级了——这就是库级锁。
但是,SQLite尽量延迟申请X锁,直到数据块真正写盘时才申请X锁,这是非常巧妙而有效的。
2、死锁的情况
3、对SQLITE_BUSY的处理
更多:Sqlite WAL原理
How to open SQLite connection in WAL mode