关于ios本地大容量存储sqlite优化

趁着app新闻量越来越大,每一回从互连网获取数据已经不是很可取的方案了,本地数据库的应用已经越发常见了。
而说道移动端,不得不提的就是SQlite了,随着当地数据库的多量选择,FMDB也遭逢了成千成万程序员的关注。
FMDB是一个很棒的库,对SQLite的展开了一层更契合mvc的卷入,使得应用变得要命便利和造福。
回过头来说,移动DB的数据量也是尤为大,动辄10万条的数据已经不再稀奇,对于几十万条大量的数量来说,读取,插入等的优化对于用户体验的优化是很关键的。

优化

对于尚未做任何优化的DB来说,几十万的数目读取,至少是几十秒甚至可能须要更长的时日,固然采纳loading页面等的缓冲,那要么远远不够的。

1)使用数据库事务

FMDB默认是对每一句sqlite语句执行工作操作的。事务是为了对数据库进行操作失利时,举办多少回滚的一种安全部制。可是没次执行都实施工作是一件很是耗时的操作,此时在进行多量数量操作时,可以在全体语句执行到位后,在做通盘的事情操作。例:

-(void)shiwucaozuo
{
     [_dataBase inTransaction:^(FMDatabase *db, BOOL *rollback) {
        for (int i = 0; i<100000; i++) {
          //执行sql语句
            BOOL a = [db executeUpdate:SQL];
            if (!a) {
                *rollback = YES;
                return;
            }
        }
    }];
}

那样展现的决定就可以使得的主宰时间了,插入等操作优化会拥有一个数码级的增高优化。
小技巧:在查询出多少后,在数据量不大的情状下,可以加载到内存中,防止每一回举办数据库查询操作。
比如,数据转换成model将来可以以key-value的格局储存在字典中

2)使用FMDB的Statement cache的机制

FMDB的cache机制就是在实践DB操作截至后会依照sql作为key查询cache。倘诺没有cache就会到场cache中,会有三遍查询操作。能够使用FMDB的cache缓存sql,那里推荐的做法是缓存不带参数的sql,然后在预备执行使用时向来通过缓存时回来的id从cache中直接得到sql语句,那样只须求一遍的询问操作,在推行大气sql语句时,大批量的查询依旧会对品质有所损耗的,只缓存不带参数的sql语句有助于增强查询成效
例:

-(void)cacheSQl:(FMDatabase*) withSql:(NSString*)sql{
    id = [db cache:sql]'
    //上面的db需要缓存,下次可以通过该id找回sql.
}
可以通过
[db executeUpdataWithStatementID]方法直接获取sql。

3)修改日志格局

日记方式

SQLite中国和日本记情势首要有DELETE和WAL三种,其余二种比如TRUNCATE,PERSIST,MEMORY基本原理都与DELETE情势相同,不作详细展开。DELETE方式应用影子分页技术(Shadow
paging),DELETE方式下,日志中记录的改变前数据页内容;WAL格局下,日志中著录的是改变后的数据页内容。事务提交时,DELETE方式将日志刷盘,将DB文件刷盘,成功后,再将日志文件清理;WAL情势则是将日志文件刷盘,即可成功提交进度。那么WAL格局下,数据文件曾几何时更新呢?那里引入了检查点概念,检查点的功力就是期限将日志中的新页覆盖DB文件中的老页,并通过参数wal_autocheckpoint来支配检查点时机,达到权衡读写的目标。
DELETE形式下,写作业直接更新db-page,并将old-page写入日志,读事务则直接读db-page,因为db-page中保存了付出的富有业务的翻新。事务提交后,间接将日志文件删除;若事务必要回滚,则将日志中old-page中的内容覆盖db-page,復苏原来内容。WAL情势下,写作业将更新写到日志文件中,不更新db-page,事务提交时,也不影响db-page,只是将日志持久化而已。若事务回滚,则不将日志写入文件即可。由于新型的数目在日记文件中,那么哪些读取到新型的数码吧?WAL格局通过end-mark(事务提交位点)达到这一目标。具体而已,事务初叶时,会首先扫描日志文件,获取方今一个end-mark,在读取数据时,首先会判定page是或不是则在wal日志文件中留存,因为同一个page,一定是wal文件中的比db文件中的要新。借使存在,则利用,否则,再从db文件中赢得指定的page。从流水线上来看,这么些历程比较慢,因为格外气象下,每便读都需求扫描wal文件和db文件。为了抓实性能,WAL情势中有一个wal-index文件,这几个文件记录了页号和该页在WAL文件中的偏移,并且wal-index文件选择共享缓存落成,从文件名也得以看出,后缀是.shm,因而断定page是不是在wal文件存在的操作实质是四遍内存读。wal-index接纳hash表存储,因而查询功能也卓绝高。与历史观的DBMS不一致,SQLite中记录的日志,实质是dirty-page,重做精神是对利用WAL中的日志页覆盖db-page,那种已毕方式比较不难,同时也正如浪费空间,因为一个page是1k,固然只更新1byte,也会导致日志记录1k。
咱俩可以在数据库建立即就改成日志格局
WAL日志情势亮点:
1) 读写可以出现,不会堵塞
2)唯有一个WAL文件,质量优势鲜明。

4)调整数据变索引建立刻间。

在创造表时,很多时候需求建立目录,索引可以荣升查找的效用,可是建立目录更加是插入多量数量再度创立目录时索要消耗多量的习性损耗。所以可以能够在全量拉取数据形成后手动添加index索引。

5)写同步(synchronous)(不推荐)

在SQLite中,数据库配置的参数都由编译提醒(pragma)来兑现的,而里边synchronous选项有两种可选状态,分别是full、normal、off。那篇博客以及合法文档中间有详尽讲到这三种参数的设置。简要说来,full写入速度最慢,但有限支撑数据是安全的,不受断电、系统崩溃等影响,而off可以加速数据库的片段操作,但只要系统崩溃或断电,则数据库可能会损毁。
SQLite3中,该选用的默许值就是full,如若大家再插入数据前将其改为off,则会提升功能。借使单单将SQLite当做一种暂时数据库的话,完全没须求设置为full。在代码中,设置格局就是在开拓数据库之后,直接插入以下语句:

sqlite3_exec(db,"PRAGMA synchronous = OFF; ",0,0,0); 

当synchronous设置为FULL (2),
SQLite数据库引擎在火急时刻会停顿以确定数据已经写入磁盘。那使系统崩溃或电源出难点时能确保数据库在重起后不会破坏。FULL
synchronous很安全但很慢。
当synchronous设置为NORMAL,
SQLite数据库引擎在大部热切时刻会搁浅,但不像FULL形式下那么频仍。
NORMAL格局下有很小的几率(但不是不存在)暴发电源故障造成数据库损坏的情状。但实际,在那种情景
下很可能你的硬盘已经不可以采用,或者发生了任何的不行苏醒的硬件错误。
安装为synchronous OFF
(0)时,SQLite在传递数据给系统之后直接接轨而不间断。若运行SQLite的应用程序崩溃,
数据不会挫伤,但在系统崩溃或写入数据时意外断电的情状下数据库可能会破坏。另一方面,在synchronous
OFF时 一些操作可能会快50倍甚至越多。可是那种操作照旧有高风险的。

6)关于查询的优化FTS

FTS虚拟表对于查询的优化是很鲜明的。
FTS3 和FTS4 是一个SQLite 虚拟表的模块,
允许用户执行全文检索一组文档从最普遍()方法
然而在使用进度中发觉有不少不雷同的地点 如:

    CREATE VIRTUAL TABLE table1 USING fts4(content TEXT) */ FTS4 表/*
    CREATE TABLE IF NOT EXISTS table1(content TEXT); /* 普通表*/

当然在FMDB中:

NSString *storePath = @"db路径";
FMDatabase *db = [FMDatabase databaseWithPath:storePath];

[db open];

FMSimpleTokenizer *simpleTok = [[FMSimpleTokenizer alloc] initWithLocale:NULL];

[db installTokenizerModule];
[FMDatabase registerTokenizer:simpleTok withKey:@"simple"];

[db executeUpdate:@"CREATE VIRTUAL TABLE works_test USING fts4(id, title, title_tr, content, content_tr, dynasty, dynasty_tr, author, author_tr, tokenize=fmdb simple)"];

[db close];

陈设等其余操作和原先一样。
可是查询的时候不是大家一般喜欢使用的#like#了 而是 #MATCH#
当然据说比like查询的进程快上1000倍(听说)

SELECT * FROM works_test WHERE works_test MATCH 'something';

7)最终关于PRAGMA命令用法

PRAGMA语句是SQLITE数据的SQL扩张,是它独有的特色,首要用以修改SQLITE库或者内数据查询的操作。它使用与SELECT、INSERT等话语一样的形式来发出请求,但也有多少个第一的不比:

  1. 一定的PRAGMA语句可能被移走,新的PRAGMA语句可能在新的版本中添加。因而,后向兼容无法担保。
  2. 不解的PRAGMA命令不会有不当音讯出现,它只是简短的不经意。
  3. 多少PRAGMA只在SQL的编译阶段起功用,而不是执行阶段。 那意味一旦选择C语言,sqlite3_prepare(),
    sqlite3_step(),
    sqlite3_finalize()这多少个API,pragma命令可能只在prepare()的调用里运行,而不是在后多少个API当中举办。或者,pragma可能在sqlite3_step()执行的时候运行。到底在哪些阶段执行,取决于pragma从自家,以及是哪些sqlite的release版本。
  4. pragma命令是sqlite特有的,基本上不容许与任何数据库保持包容。

上边我们看看sqlite到底有些有用的pragma命令:
auto_vacuum
automatic_index
cache_size
case_sensitive_like
checkpoint_fullfsync
collation_list
compile_options
count_changes¹
database_list
default_cache_size¹
empty_result_callbacks¹
encoding
foreign_key_list
foreign_keys
freelist_count
full_column_names¹
fullfsync
ignore_check_constraints
incremental_vacuum
index_info
index_list
integrity_check
journal_mode
journal_size_limit
legacy_file_format
locking_mode
max_page_count
page_count
page_size
parser_trace²
quick_check
read_uncommitted
recursive_triggers
reverse_unordered_selects
schema_version
secure_delete
short_column_names¹
synchronous
table_info
temp_store
temp_store_directory¹
user_version
vdbe_listing²
vdbe_trace²
wal_autocheckpoint
wal_checkpoint
writable_schema
此处边有多少个标了右上标为1的,似乎早就被obsoleted掉了。标为2的,只被用于debug,仅当sqlite在预编译宏SQLITE_DEBUG下build出来,才有用。

上面看看那个命令的现实性用法:

  1. PRAGMA auto_vacuum;
    PRAGMA auto_vacuum = 0 或 NONE | 1 或 FULL | 2 或 INCREMENTAL;
    那边,0和NONE表示的含义相同。
    缺省值为0, 表示禁用auto vacuum.
    除非SQLITE_DEFAULT_AUTOVACUUM宏在编译的时候定义了。数据删除的时候,数据库大小不会变动。没用的数据库文件页面会被添加到freelist里头,用于将来录取。那时,使用VACUUM命令,可以重建总体数据库,以回收无用的磁盘空间。
    值为1时,所有的freelist页会被移动到文件末尾,每一次事务提交的时候文件会被截短。注意,自动vacuum只是从文件是截断freelist页,并不曾展开零散重整等操作,也就是说,它并未VACUUM命令来得彻底。事实上,自动vacuum会让碎片愈来愈多。
    只有在数据库存储某些附加新闻的时候,它同意每个数据库页来跟踪它的引用页,自动vacuum才用得上。它必须在尚未创设任何表的意况下启用。在一个表已经创办了今后,是不能够启用和停用auto-vacuum的。
    值为2时,表示增量vacuum,意味着并不是在历次提交业务的时候自动vacuum,要求调用一个独门的incremental_vacuum语句来触发auto-vacuum。
    数据库可以在1和2二种vacuum格局下进展切换。可是不可能从none到full或incremental间切换。要想切换,要么数据库是全新的数据库(没有任何表), 或者独立运行vacuum命令将来。改变机关vacuum方式,首先执行auto_vacuum语句设置新的方式,然后调用VACUUM来重整数据库。
    不带参数的auto_vacuum语句重返当前的auto_vacuum模式值。

  2. PRAGMA automatic_index;
    PRAGMA automatic_index = boolean;
    询问,设置或者排除自动索引的效劳。缺省值为true (1).

  3. PRAGMA cache_size;
    PRAGMA cache_size = <number of pages>;
    询问或者涂改开拓的数据库内存里头能包容的最多的数据库页数。缺省值是2000. 这么设定只会转移如今对话中的cache
    size,当数据库重新打开,又会东山再起默认值。你可以选取default_cache_size来设定所有会话中的cache
    size

  4. PRAGMA case_sensitive_like=boolean;
    默许行为是忽视ascii字符的高低写。’a’ LIKE ‘A’会是true.
    当禁用case_sensitive_like时,会用默许的like行为。当启用它时,就会有别轻重缓急写。

  5. PRAGMA checkpoint_fullfsync
    PRAGMA checkpoint_fullfsync=boolean;
    询问或安装fullfsync的标志值。固然设置了该值,则F_FULLFSYNC同步方法会在checkpoint操作时调用,默许值是off。只有Mac
    OS-X操作系统帮忙F_FULLFSYNC。此外,如若设定了fullfsync值,那么F_FULLFSYNC同步方法会在颇具sync操作里使用,也checkpoint_fullfsync标志完全毫不相关。

  6. PRAGMA collation_list;
    归来当前数据库连接定义的持有排序依次。

  7. PRAGMA compile_options;
    其一要赞,重回编译SQLITE时接纳的装有预编译宏。当然,以”SQLITE_”打头的前缀会被忽视。实际上它是经过调用sqlite3_compileoption_get()方法再次来到的。

  8. PRAGMA count_changes;
    PRAGMA count_changes=boolean;
    该命令已经停用. 只是为着保全后向包容. 倘若不安装此值,INSERT,
    UPDATE, DELETE语句不会回到多少行改变的数额。
    事实上,sqlite3_changes()可以收获改变的行数。

  9. PRAGMA database_list;
    回到当前数据库连接关联的数目库列表.

  10. PRAGMA default_cache_size;
    PRAGMA default_cache_size = Number-of-pages;
    安装缺省的cache sie, 是以页为单位。不幸的是,该命令也将被丢掉。

  11. PRAGMA empty_result_callbacks;
    PRAGMA empty_result_callbacks = boolean;
    仅作后向兼容用。倘使将该标志值清除,sqlite3_exec()提供的回调函数(重返0或多行数据)将不被触发。

  12. PRAGMA encoding;
    PRAGMA encoding = “UTF-8”;
    PRAGMA encoding = “UTF-16”;
    PRAGMA encoding = “UTF-16le”;
    PRAGMA encoding = “UTF-16be”;
    缺省值是utf-8。即使利用attach命令,则会须求运用与main数据库相同的字符集编码,如若新的数据库编码与main不一致,则会失利。

  13. PRAGMA foreign_key_list(table-name);
    回来外键列表

  14. PRAGMA foreign_keys;
    PRAGMA foreign_keys = boolean;
    查询设置或者免除关于外键的限制,
    外键限制唯有在BEGIN或者SAVEPOINT不在PENDING状态时设置才使得。
      改变该装置会潜移默化所有曾经准备好的SQL语句的实践。
    从3.6.19始发,默认的FK强制限制是OFF。也就是说,不会强制外键重视。

  15. PRAGMA freelist_count;
    归来数据库文件中未选拔页的多寡

  16. PRAGMA full_column_names;
    PRAGMA full_column_names = boolean;
    deprecated.

    1. 设若有AS子句,列名就会用AS后的别名
    2. 假设结果只是一般的表明式,而不是源表的列名,则运用表明式的文本
    3. 假若运用了short_column_names开关为ON,则应用源表列名,并且不带表名前缀
    4. 一经八个开关都设为OFF,则运用第2个规则。
    5. 结果列是学有源表源列的整合:TABLE.COLUMN
  17. PRAGMA fullfsync;
    PRAGMA fullfsync = boolean;
    缺省值为OFF,也唯有MAC os协理F_FULLFSYNC

  18. PRAGMA ignore_check_constraints = boolean;
    是或不是强制check约束,缺省值为off

  19. PRAGMA incremental_vacuum(N);
    N页从freelist中移除。用于设定此参数。每一回截短相同的页数。该命令必须是在auto_vacuum=incremental形式下才有效。若是freelist中的页数少于N,或者N小于1,或者N被完全忽视,那么一切freelist会被清除。

  20. PRAGMA index_info(index-name);
    赢得具名的index音讯。

  21. PRAGMA index_list(table-name);
    收获与对象表关联的目录的的相关音信

  22. PRAGMA integrity_check;
    PRAGMA integrity_check(integer);
    举办总体库的完全性检查,会翻动错序的记录、丢失的页,毁坏的目录等。

PRAGMA journal_mode;
PRAGMA database.journal_mode;
PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL |
OFF
PRAGMA database.journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY |
WAL | OFF
用于安装数据库的journal_mode.
DELETE是缺省的一言一动。在此情势下,每便事务终止的时候,journal文件会被剔除,它会促成工作提交。
TRUNCATE情势,通过将回滚journal截短成0,而不是剔除它。一大半情情形下,它要比DELETE格局速度快(因为不用删除文件)
PERSIST形式,每趟事务截止时,并不删除rollback
journal,而只是在journal的尾部填充0,这样会阻拦别的数据库连接来rollback.
该方式在一些平台下,是一种优化,越发是剔除或者truncate一个文本比覆盖文件的第一块代价高的时候。
MEMORY形式,只将rollback日志存储到RAM中,节省了磁盘I/O,但牵动的代价是政通人和和完整性上的损失。如若中间crash掉了,数据库有可能破坏。
WAL格局,也就是write-ahead log取代rollback
journal。该格局是持久化的,跨多少个数据为总是,在再度打开数据库未来,依旧有效。该方式只在3.7.0自此才有效。
(经过试验,发现,它会变动七个文本:.shm和.wal)
OFF情势,那样就从未有过工作匡助了。 
其余要留意的是,对于memory数据库,唯有三种模式:
MEMORY或者OFF。并且,当前一旦有活泼的政工,则不容许改变工作格局。

  1. PRAGMA journal_size_limit
    PRAGMA journal_size_limit = N ;
    比方老是时,用了”exclusive mode(PRAGMA
    locking_mode=exclusive)或者(PRAGMA journal_mode=persist),
    提交业务未来,journal文件会仍旧在文件系系统当中。这或许会抓好了功效,然而也消耗了上空。一个大的事务(如VACUUM),会开销大量的磁盘空间。
    该装置会限制journal文件的轻重。默许值是-1。

  2. PRAGMA legacy_file_format;
    PRAGMA legacy_file_format = boolean;
    假诺该值为ON,则会选拔3.0.0文件格式,假若为off,
    则会动用新式的文件格式,可能引致旧版本的sqlite无法打开该文件。
    率先次新文件格式的sqlite3数据库打开时,该值为off.不过默认值会是on.

  3. PRAGMA locking_mode;
    PRAGMA locking_mode = NORMAL | EXCLUSIVE
    缺省值是NORMAL.
    数据库连接在每一个读或写作业终点的时候放掉文件锁。若是是EXCLUSIVE形式,连接永远不会自由文件锁。在此方式下,首回施行读操作时,会收获并装有共享锁,首回写,会拿走并装有排它锁。
    刑满释放排它锁,仅当关闭数据库连接,或者将锁形式改回为NORMAL时,再度走访数据库文件(读或写)才会放掉。简单的设置为NORMAL是不够的,唯有当下次再拜访时才会自由排它锁。
    有下述四个理由,去设置锁形式为EXCLUSIVE

    1. 应用程序必要阻止其它进度访问数据库文件
    2. 文件系统的序列调用数量减少了,导致有些属性下跌
    3. WAL日志情势可以在EXCLUSIVE格局下利用,而不必要利用共享内存
      当指定数据库名时,只好目的数据库生效。如:
      PRAGMA main.locking_mode=EXCLUSIVE;
      不指定数据库名时,则对持有打开的数据库生效。temp或者memory数据库总是使用exclusive锁方式。
        首次跻身WAL日志格局时,锁格局应用的是exclusive,那未来,锁方式也不可能更改,直到退出WAL日志形式,尽管锁形式开始时利用的是NORMAL,第三次跻身WAL,那时锁情势可以变动,并且不要求退出WAL情势。
  4. PRAGMA max_page_count;
    PRAGMA max_page_count = N;
    查询或者安装数据库文件的最大页数

  5. PRAGMA page_count;
    回去数据库文件的页数

  6. PRAGMA page_size;
    PRAGMA page_size = bytes;
    询问或者设置数据库文件的页大小,
    必须是2的乘方,并且介于512和65536中间。
    创立数据库时,会给定一个缺省的高低。page_size命令会立时改变页大小(如若数据库是空的话,就是说在未曾创立任何表的情状下)。若是指定了新大小,是在运转VACUUM命令之间,同时数据库不是在WAL日志格局下,那么VACUUM命令会将页大小调整到新的大小(那时应该没有是事创立表的限量)
    SQLITE_DEFAULT_PAGE_SIZE 缺省值是1024,最大的缺省页大小是8192.
    windows下,有时候可能缺省页大小大于1024,取决于GetDiskFreeSpace()来博取真实的装置扇区大小。

  7. PRAGMA parser_trace = boolean;
    用在DEBUG的时候。

  8. PRAGMA quick_check;
    PRAGMA quick_check(integer)
    与integrity_check相像,不过略去了对索引内容与表内容相当的校验。

  9. PRAGMA read_uncommitted;
    PRAGMA read_uncommitted = boolean;
    读未提交开关。缺省的工作隔离级是:可串行化。任何进度或线程都得以安装读未提交隔离级,然则,SERIALIZABLE仍被选择,除了共享某页和表情势的缓存的那多少个总是。

  10. PRAGMA recursive_triggers;
    PRAGMA recursive_triggers = boolean;
    会潜移默化所有的言语执行。3.6.18原先,这几个开关是不扶助的。缺省值是off.

  11. PRAGMA reverse_unordered_selects;
    PRAGMA reverse_unordered_selects = boolean;
    当打开此开关时,不带order by的select语句,会输出相反顺序的结果。

  12. PRAGMA schema_version;
    PRAGMA schema_version = integer ;
    PRAGMA user_version;
    PRAGMA user_version = integer ;
    schema和user
    version是在数据库文件头40,60字节处的32位整数(大端表示)。
    schema版本由sqlite内部维护,当schema改变时,就会增多该值。显式改变该值相当惊险。
    user版本可以被应用程序使用。

  13. PRAGMA secure_delete;
    PRAGMA database.secure_delete;
    PRAGMA secure_delete = boolean
    PRAGMA database.secure_delete = boolean
    设为ON时,删除的情节会用0来掩盖。缺省值由宏SQLITE_SECURE_DELETE
    决定。那就是OFF了。

  14. PRAGMA short_column_names;
    PRAGMA short_column_names = boolean;
    deprecated.

  15. PRAGMA synchronous;
    PRAGMA synchronous = 0 | OFF | 1 | NORMAL | 2 | FULL;
    查询设置sync标志值。缺省值是FULL.

  16. PRAGMA table_info(table-name);
    重临表的为主音讯

  17. PRAGMA temp_store;
    PRAGMA temp_store = 0 | DEFAULT | 1 | FILE | 2 | MEMORY;
    询问或设置temp_store参数值。
    SQLITE_TEMP_STORE PRAGMA temp_store Storage used forTEMP tables
    0 any file
    1 0 file
    1 1 file
    1 2 memory
    2 0 memory
    2 1 file
    2 2 memory
    3 any memory

  18. PRAGMA temp_store_directory;
    PRAGMA temp_store_directory = ‘directory-name’;
    设置或变更temp_store的目录位置. deprecated.

  19. PRAGMA vdbe_listing = boolean;
    用于DEBUG

  20. PRAGMA vdbe_trace = boolean;
    用于DEBUG

  21. PRAGMA wal_autocheckpoint;
    PRAGMA wal_autocheckpoint=N;
    安装WAL自动检查点的距离(以页为单位), 缺省值是1000。

  22. PRAGMA database.wal_checkpoint;
    PRAGMA database.wal_checkpoint(PASSIVE);
    PRAGMA database.wal_checkpoint(FULL);
    PRAGMA database.wal_checkpoint(RESTART);

  23. PRAGMA writable_schema = boolean;
    当设为ON时,SQLITE_MASTER表能够实施CUD操作。那样做很惊险!!

网站地图xml地图