SQLite3数据库访问性能优化7个建议实用指南

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“SQLite3数据库访问性能优化7个建议”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

理解这一步时，SQLite 是一个轻量级的关系型数据库管理系统，广泛应用来嵌入式设备、移动应用和小型桌面程序中。它的一个强大特性是借助 PRAGMA 命令，用户能够查询和设置数据库的设置参数，以优化查询性能和数据库管理。PRAGMA 命令提供了多种控制数据库行为的功能，包括缓存大小、索引设置、查询优化等。在这篇文章中，我们将探讨如何采用 SQLite 的 PRAGMA 命令来优化查询性能，提升数据库的响应速度。

在 SQLite 里，PRAGMA 是一个特殊的命令，用来控制数据库的行为、查询优化、以及性能调优。PRAGMA 命令能够用来设置数据库的各种设置，如缓存大小、数据库模式、事务行为等。

一.空间释放

1.如何查询：

PRAGMA auto_vacuum;

含义：查询数据库的auto-vacuum标记。

2.标记含义：

auto-vacuum标记的含义：
实际处理时，正常情况下，当提交一个从数据库中删除数据的事务时，数据库文件不改变大小。未采用的文件页被标记同时在以后的添加操作中再次采用。这种情况下采用VACUUM命令释放删除得到的空间。但是Vacuum的效率很低！

3.如何设置：

PRAGMA auto_vacuum = 0 | 1;

在这个场景下，当开启auto-vacuum，也就是执行pragma auto_vacuum=1;

理解这一步时，提交一个从数据库中删除数据的事务时，数据库文件自动收缩， (VACUUM命令在auto-vacuum开启的数据库中不起作用)。数据库会在内部存储一些信息以便兼容这一功能，这使得 数据库文件比不开启该选项时稍微大一些。

4.注意事项：

从实现思路看，只有在数据库中未建任何表时才能改变auto-vacuum标记。试图在已有表的情况下修改不会导致报错。

实际处理时，不要打开。也就是“PRAGMA auto_vacuum = 0；”执行命令。

二.缓存大小

1.如何查询：

PRAGMA cache_size;

查询SQLite一次存储在内存中的数据库文件页数。

2.标记含义：

从实现思路看，每页采用约1.5K内存，缺省的缓存大小是2000. 若需采用改变大量多行的UPDATE或DELETE命令，同时且不介意SQLite采用更多的内存的话，能够增大缓存以提高性能。

3.如何设置

PRAGMA cache_size = Number-of-pages;

修改SQLite一次存储在内存中的数据库文件页数。

4.注意事项：

实际处理时，采用cache_size pragma改变缓存大小时，改变仅对当前对话有效，当数据库关闭重新打开时缓存大小恢复到缺省大小。要想永久改变缓存大小，采用default_cache_size pragma.

落到代码里，修改为8000，也就是执行命令“PRAGMA cache_size =8000；”即可；

三.LIKE运算符PRAGMA case_sensitive_like;

PRAGMA case_sensitive_like = 0 | 1;

标记含义：落到代码里，LIKE运算符的缺省行为是忽略latin1字符的大小写。所以在缺省情况下'a' LIKE 'A'的值为真。能够借助打开 case_sensitive_like pragma来改变这一缺省行为。当启用case_sensitive_like，'a' LIKE 'A'为假而'a' LIKE 'a'依然为真。

1.注意事项：

SQLite3.6.22版本不兼容。

2.建议：

在这个场景下，打开。也就是执行命令“PRAGMA case_sensitive_like = 1;”命令。不然搜索中文字串会出错。

四.LIKE运算符PRAGMA count_changes;

PRAGMA count_changes = 0 | 1;
查询或更改count-changes标记。

正常情况下INSERT, UPDATE和DELETE语句不得到数据。

在这个场景下，当开启count-changes，以上语句得到一行含一个整数值的数据——该语句插入，修改或删除的行数。

1.注意事项：

得到的行数不包括由触发器产生的插入，修改或删除等改变的行数。

2.建议：

结合项目来看，打开，便于调试。也就是执行“PRAGMA count_changes = 1；”该命令。

五.页面大小PRAGMA page_size;

PRAGMA page_size = bytes;
查询或设置page-size值。

注意事项：

理解这一步时，只有在未新建数据库时才能设置page-size。页面大小必须是2的整数倍且大于等于512小于等于8192。

实际处理时，上限能够借助在编译时修改宏定义SQLITE_MAX_PAGE_SIZE的值来改变。上限的上限是32768。

六.磁盘同步

1.如何查询：

PRAGMA synchronous;

指令含义：查询"synchronous"标记的设定，得到整数值；

2.如何设置：

PRAGMA synchronous = FULL; (2)

PRAGMA synchronous = NORMAL; (1)
PRAGMA synchronous = OFF; (0)

3.参数含义：

落到代码里，当synchronous设置为FULL (2), SQLite数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘。这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。

理解这一步时，当synchronous设置为NORMAL, SQLite数据库引擎在大部分紧急时刻会暂停，但不像FULL模式下那么频繁。 NORMAL模式下有很小的几率(但不是不存在)发生电源故障导致数据库损坏的情况。但实际上，在这种情况 下很可能你的硬盘已经不能采用，或者发生了其他的不可恢复的硬件错误。

实际处理时，设置为synchronous OFF (0)时，SQLite在传递数据给系统以后直接继续而不暂停。若运行SQLite的应用程序崩溃， 数据不会损伤，但在系统崩溃或写入数据时意外断电的情况下数据库可能会损坏。另一方面，在synchronous OFF时 一些操作可能会快50倍甚至更多。
在SQLite 2里，缺省值为NORMAL.而在3中修改为FULL。

4.建议：

若有定期备份的机制，而且少量数据丢失可接受，用OFF。

七.内存模式

1.如何查询：

PRAGMA temp_store;

指令含义：查询"temp_store"参数的设置；

2.如何设置：

PRAGMA temp_store = DEFAULT; (0)
PRAGMA temp_store = FILE; (1)
PRAGMA temp_store = MEMORY; (2)

3.参数含义：

从实现思路看，当temp_store设置为DEFAULT (0),采用编译时的C预处理宏 TEMP_STORE来定义储存临时表和临时索引的位置。

落到代码里，当设置为FILE (1)，则存放于文件中。temp_store_directorypragma 可用来指定存放该文件的目录。

当设置为MEMORY (2)，临时表和索引则存放于内存中。

4.注意事项：

实际处理时，当改变temp_store设置，所有已存在的临时表，索引，触发器及视图将被立即删除。

5.建议：

采用2，即内存模式。

附指令表集：

八.总结

SQLite 的PRAGMA命令为开发者提供了灵活的设置选项，能够显著提升数据库的查询和写入性能。借助调整缓存大小、同步模式、WAL 模式、索引优化等设置，你能够针对特定的应用场景对 SQLite 进行定制化优化。合理采用 PRAGMA 命令不仅能提高性能，还能确保数据库在高负载环境下的稳定性和可靠性。

到此这篇关于SQLite3数据库访问性能优化7个建议的文章就介绍到这了,更多相关SQLite3性能优化内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多兼容脚本之家！