SQLite优化
目录
插入效率优化
显式开启事务
所谓”事务“就是指一组SQL命令,这些命令要么一起执行,要么都不被执行。在SQLite中,每调用一次sqlite3_exec()函数,就会隐式地开启了一个事务,如果插入一条数据,就调用该函数一次,事务就会被反复地开启、关闭,会增大IO量。如果在插入数据前显式开启事务,插入后再一起提交,则会大大提高IO效率,进而加快数据插入速度。示例代码如下:
sqlite3_exec ( db, "BEGIN" , 0 , 0 , 0 ) ;
for ( int i= 0 ; i< nCount; ++ i)
{
std:: stringstream ssm;
ssm<< "insert into t1 values(" << i<< "," << i* 2 << "," << i/ 2 << "," << i* i<< ")" ;
sqlite3_exec ( db, ssm. str ( ) . c_str ( ) , 0 , 0 , 0 ) ;
}
sqlite3_exec ( db, "COMMIT" , 0 , 0 , 0 ) ;
写同步(synchronous )
在SQLite中,数据库配置的参数都由编译指示(pragma)来实现的,而其中synchronous选项有三种可选状态,分别是full、normal、off。这篇博客以及官方文档里面有详细讲到这三种参数的设置。简要说来,full写入速度最慢,但保证数据是安全的,不受断电、系统崩溃等影响,而off可以加速数据库的一些操作,但如果系统崩溃或断电,则数据库可能会损毁。SQLite3中,该选项的默认值就是full,如果我们再插入数据前将其改为off,则会提高效率。如果仅仅将SQLite当做一种临时数据库的话 或者有定期备份的机制且少量数据丢失可接受,可将同步方式设置为OFF。在代码中,设置方法就是在打开数据库之后,直接插入以下语句: sqlite3_exec(db,"PRAGMA synchronous = OFF; ",0,0,0);
执行准备
SQLite执行SQL语句的时候,有两种方式:一种是使用前文提到的函数sqlite3_exec(),该函数直接调用包含SQL语句的字符串;另一种方法就是“执行准备”(类似于存储过程)操作,即先将SQL语句编译好,然后再一步一步(或一行一行)地执行。如果采用前者的话,就算开起了事务,SQLite仍然要对循环中每一句SQL语句进行“词法分析”和“语法分析”,这对于同时插入大量数据的操作来说,简直就是浪费时间。因此,要进一步提高插入效率的话,就应该使用后者, sqlite3_exec()只适合执行一次性语句。示例代码如下:
sqlite3_exec ( db, "begin;" , 0 , 0 , 0 ) ;
sqlite3_stmt * stmt;
const char * sql = "insert into t1 values(?,?,?,?)" ;
sqlite3_prepare_v2 ( db, sql, strlen ( sql) , & stmt, 0 ) ;
for ( int i= 0 ; i< nCount; ++ i)
{
sqlite3_reset ( stmt) ;
sqlite3_bind_int ( stmt, 1 , i) ;
sqlite3_bind_int ( stmt, 1 , i* 2 ) ;
sqlite3_bind_int ( stmt, 1 , i/ 2 ) ;
sqlite3_bind_double ( stmt, 1 , i* i) ;
}
sqlite3_finalize ( stmt) ;
sqlite3_exec ( db, "commit;" , 0 , 0 , 0 ) ;
内存数据库:
如果不需要将数据存储到文件,可采用内存数据库。sqlite3_open(":memory:", &db);
在内存数据库中attach文件数据库,这样可以保证文件数据库中的数据在内存数据库中可见 对于insert操作,在内存数据库中完成,其它操作,delete和update需要同时操作内存和文件数据库。select视情况而定。 定时将文件数据库中的内容更新到文件数据库中,需要flush 磁盘的cache防止数据不同步引起的数据库损坏, 代码中调用的是sync()
和sata_flush_cache(g_SqliteLinkInfo[i].devName);
sync函数只是将所有修改过的块缓冲区排入写队列,然后就返回,它并不等待实际写磁盘操作结束。通常称为update的系统守护进程会周期性地(一般每隔30秒)调用sync函数。这就保证了定期冲洗内核的块缓冲区。命令sync(1)也调用sync函数。fsync函数只对由文件描述符filedes指定的单一文件起作用,并且等待写磁盘操作结束,然后返回。fsync可用于数据库这样的应用程序,这种应用程序需要确保将修改过的块立即写到磁盘上, 来自https://blog.csdn.net/zhouxinlin2009/article/details/89633464
write()函数,它与read不同,read是直接系统调用将磁盘中的内容读到内存中。而write仅仅只是将内存中的东西写到磁盘文件的缓冲区中,至于何时将这些内容放入磁盘,得看操作系统的心情——CPU的调度,队列的优先级都是关键。当然也会有如下情况,我们意图立即将内容刷新至磁盘,因为可能现在在一个多用户条件下,磁盘内容极有可能马上要被别人获取。
以下情况会立即刷新数据到磁盘:
1.exit函数,而非_exit函数,exit函数的主要步骤有下面三项:
a.调用退出处理函数。
b.刷新输出缓存。
c.调用_exit函数。
2.return时。
这个不用解释,当进程结束,PCB回收,当然会释放文件描述符表中的文件。
3.缓存区满。
4.fsync()函数。函数原型:int fsync(int fd);//成功返回0,出错返回-1.《UNIX高级环境编程》有描述。
使用WAL模式
对于需要创建索引的表,先插入数据再创建索引,比先创建索引再创建数据库,更快。即索引会降低插入的效率。
查询速度优化
创建索引,可以显著提高select的速度,同时也可以提高update的速度, 除了创建索引,其它优化方法与插入优化方法类似,但是使用执行准备方法时,调用的函数不一样,见示例:sqlite3_exec ( db, "BEGIN" , 0 , 0 , NULL ) ;
char * sql = "select * from testinfo where id = ?" ;
sqlite3_prepare_v2 ( db, sql, strlen ( sql) , & stmt, 0 ) ;
for ( i = 0 ; i < 1000000 ; i++ )
{
sqlite3_reset ( stmt) ;
sqlite3_bind_int ( stmt, 1 , i) ;
rc = sqlite3_step ( stmt) ;
while ( rc == SQLITE_ROW)
{
n1 = sqlite3_column_int ( stmt, 0 ) ;
n2 = sqlite3_column_int ( stmt, 1 ) ;
ch1 = sqlite3_column_text ( stmt, 2 ) ;
ch2 = sqlite3_column_text ( stmt, 3 ) ;
rc = rc = sqlite3_step ( stmt) ;
}
}
sqlite3_finalize ( stmt) ;
SQLite 深度优化
SQLite加密
Sqlite程序中预留了sqlite3_key、sqlite3_rekey加密接口,但是需要自己实现加密解密逻辑,比如移植AES算法实现基于AES加密算法的数据库加密。
page size和cache ize优化
SQLite数据库把其所存储的数据以page为最小单位进行存储(与操作系统文件系统的页类似)。cache_size的含义为当进行查询操作时,用多少个page来缓存查询结果,加快后续查询相同索引时方便从缓存中寻找结果的速度。测试发现不同的page size和?cache size,数据检索耗时不同。优化方案 为使用我们拷机时产生的数据库数据进行比对测试,最优的page size是16KB,cache size需要根据我们分配的内存计算. 在小内存的设备上有可能cache高导致内存不足,如果出现这种情况可以调用sqlite3_soft_heap_limit64
限制sqilte的heap内存,这样做能够控制sqlite的cache内存;另外,需要限制sqlite的default_cache_size值,default_cache_size
的值的计算方法为: 总内存/2/page size
SQLite内存分配优化
Sqlite默认采用系统的内存分配器,同时也支持内存池分配,每次写入或者检索操作时,均会分配内存空间。数据库操作频繁,频繁调用系统内存分配会导致内存碎片和分配耗时。优化方法为开启数据库memsys5零内存分配器:设备启动时,申请200M内存,并调用sqlite3_config(SQLITE_CONFIG_HEAP,pBuf,iSetMemSize,iMnReq);
将这段内存提供给数据库使用. 优化后数据库所有操作分配的内存均在我们申请的固定内存空间中,不会产生内存碎片,并且节约了内存分配时间。
禁用文件锁
当没有多进程的需求时,我们可以直接注释掉os_unix.c
中(公司的不是这个文件)所有文件锁相关的操作(文件锁加解锁接口unixLock、unixUnlock中在线程锁加锁成功后直接返回成功,即unixEnterMutex后直接返回成功)。也许你会很奇怪,虽然没有文件锁的需求,但这个操作耗时也很短,是否有必要特意优化呢?其实并不全然。耗时多少是比出来。SQLite中有cache机制。被加载进内存的page,使用完毕后不会立刻释放。而是在一定范围内通过LRU的算法更新page cache。这就意味着,如果cache设置得当,大部分读操作都不会读取新的page。然而因为文件锁的存在,本来这个只需在内存层面进行的读操作,不得不进行至少一次I/O操作。而我们知道,I/O操作是远远慢于内存操作的
多线程并发优化
SQLite提供了Busy Retry的方案,即发生阻塞时,会触发Busy Handler,此时可以让线程休眠一段时间后,重新尝试操作。重试一定次数依然失败后,则返回SQLITE_BUSY错误码。 SQLite通过两个锁来控制并发。第一个锁对应DB文件,通过5种状态进行管理;第二个锁对应WAL文件,通过修改一个16-bit的unsigned short int的每一个bit进行管理。尽管锁的逻辑有一些复杂,但此处并不需关心。这两种锁最终都落在OS层的sqlite3OsLock、sqlite3OsUnlock和sqlite3OsShmLock上具体实现。它们在锁的实现比较类似。以lock操作在iOS上的实现为例:1.通过pthread_mutex_lock进行线程锁,防止其他线程介入。然后比较状态量,若当前状态不可跳转,则返回SQLITE_BUSY; 2.通过fcntl进行文件锁,防止其他进程介入。若锁失败,则返回SQLITE_BUSY。而SQLite选择Busy Retry的方案的原因也正是在此---文件锁没有线程锁类似pthread_cond_signal的通知机制。当一个进程的数据库操作结束时,无法通过锁来第一时间通知到其他进程进行重试。因此只能退而求其次,通过多次休眠来进行尝试。通过上面的各种分析、准备,终于可以动手开始修改了。我们知道,iOS app是单进程的,并没有多进程并发的需求,这和SQLite的设计初衷是不相同的。这就给我们的优化提供了理论上的基础。在iOS这一特定场景下,我们可以舍弃兼容性,提高并发性。新的方案修改为:
当OS层进行lock操作时:
通过pthread_mutex_lock进行线程锁,防止其他线程介入。然后比较状态量(其实就是回滚日志和wal的日志下的锁能不能获得,比如当前要获得一个wal读锁,获取不到,只有等释放了才能获取),若当前状态不可跳转,则将当前期望跳转的状态,插入到一个FIFO的Queue尾部。最后,线程通过pthread_cond_wait进入 休眠状态,等待其他线程的唤醒。 忽略文件锁 当OS层的unlock操作结束后
取出Queue头部的状态量,并比较状态是否能够跳转。若能够跳转,则通过pthread_cond_signal_thread_np唤醒对应的线程重试。 pthread_cond_signal_thread_np是Apple在pthread库中新增的接口,与pthread_cond_signal类似,它能唤醒一个等待条件锁的线程。不同的是,pthread_cond_signal_thread_np可以指定一个特定的线程进行唤醒。 新的方案可以在DB空闲时的第一时间,通知到其他正在等待的线程,最大程度地降低了空等待的时间,且准确无误。此外,由于Queue的存在,当主线程被其他线程阻塞时,可以将主线程的操作“插队”到Queue的头部。当其他线程发起唤醒通知时,主线程可以有更高的优先级,从而降低用户可感知的卡顿。该方案上线后,卡顿检测系统检测到等待线程锁的造成的卡顿下降超过90%,SQLITE_BUSY的发生次数下降超过95%。
其它优化(知道下就好)
Shared-Cache Mode
Shared-Cache Mode官方说明文档 这个特性,知道下就好。If you are using multiple threads, you can try using the shared page cache, which will allow loaded pages to be shared between threads, which can avoid expensive I/O calls.Enable Or Disable Shared Pager Cache int sqlite3_enable_shared_cache(int);
内存映射IO