背景
sqlite有个不足的地方，就是当你对数据库的表进行删除时，数据库文件大小不会变小。根本原因是它只是将删除部分的页打了标记，意味着这部分数据是脏数据，可以直接覆盖。因此对于用户空间它的容量还是不变。
这样导致应用如果要对每个业务数据库容量进行限制变得很麻烦。
auto-vacuum作用
auto-vacuum原理是把空闲页移到数据库末尾，从而减小数据库大小。但是存在个问题，会出现数据库碎片化。而vaccum刚好与之相反，该命令会对数据库进行反碎片化。
vaccum原理是通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件。这消除了空闲页，把表中的数据排列为连续的，另外会清理数据库文件结构。
但是vacuum的缺陷有两个：
- 需要2倍当前数据库大小的容量，增大内存占用。（这跟它整理碎片的原理有关，需要先创建临时表存放数据，再进行整理处理）
- 执行命令特别耗时。
因此目前的方案是2者结合使用。开启auto-vaccum并且每天固定时间进行一次vacuum。
开启/查看auto-vaccum
大前提是在还没有建表的过程中开启。成功建表后无法开启auto-vaccum
Pragma statements supported by SQLite
该文档中详细描述到auto-vaccum
PRAGMA schema.auto_vacuum;
PRAGMA schema.auto_vacuum = 0 | NONE | 1 | FULL | 2 | INCREMENTAL;
Query or set the auto-vacuum status in the database.
The default setting for auto-vacuum is 0 or "none", unless the SQLITE_DEFAULT_AUTOVACUUM compile-time option is used. The "none" setting means that auto-vacuum is disabled. When auto-vacuum is disabled and data is deleted data from a database, the database file remains the same size. Unused database file pages are added to a "freelist" and reused for subsequent inserts. So no database file space is lost. However, the database file does not shrink. In this mode the VACUUM command can be used to rebuild the entire database file and thus reclaim unused disk space.
When the auto-vacuum mode is 1 or "full", the freelist pages are moved to the end of the database file and the database file is truncated to remove the freelist pages at every transaction commit. Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse.
Auto-vacuuming is only possible if the database stores some additional information that allows each database page to be traced backwards to its referrer. Therefore, auto-vacuuming must be turned on before any tables are created. It is not possible to enable or disable auto-vacuum after a table has been created.
When the value of auto-vacuum is 2 or "incremental" then the additional information needed to do auto-vacuuming is stored in the database file but auto-vacuuming does not occur automatically at each commit as it does with auto_vacuum=full. In incremental mode, the separate incremental_vacuum pragma must be invoked to cause the auto-vacuum to occur.
The database connection can be changed between full and incremental autovacuum mode at any time. However, changing from "none" to "full" or "incremental" can only occur when the database is new (no tables have yet been created) or by running the VACUUM command. To change auto-vacuum modes, first use the auto_vacuum pragma to set the new desired mode, then invoke the VACUUM command to reorganize the entire database file. To change from "full" or "incremental" back to "none" always requires running VACUUM even on an empty database.
When the auto_vacuum pragma is invoked with no arguments, it returns the current auto_vacuum mode.
FULL模式，每次提交都会自动发生，每次都会将空闲页移动到数据库文件的末尾
INCREMENTAL模式，顾名思义只有在增量的情况下才会触发auto-vacuum。我的理解是只有在数据库是增大的情况下才会触发，因为有可能其他表有delete数据，还有空闲页可以使用。这时候就没必要auto-vaccum
auto-vaccum两者模式，菜鸟教程中full与INCREMENTAL模式搞反了。
NONE0FULL1INCREMENTAL2
查看是否开启auto-vaccum
在终端命令行输入sqlite3 database_name “PRAGMA auto_vacuum;”
也可以进入数据库后输入PRAGMA auto_vacuum;
开启auto-vaccum
sqlite> PRAGMA auto_vacuum;
0
sqlite> PRAGMA auto_vacuum = FULL;
sqlite> PRAGMA auto_vacuum;
1
sqlite>
测试过程
./sqlite_test securityLog_day_20210928 10000
root root 788K Sep 3 00:05 logSecurity.db
进入数据库删除表
DELETE FROM securityLog_day_20210928;
容量依然不变
root root 788K Sep 3 00:05 logSecurity.db
开启auto-vaccum
PRAGMA auto_vacuum = FULL;
./sqlite_test securityLog_day_20210929 10000
root root 400K Sep 3 00:35 logSecurity.db
root@firewall:/mnt/flash/workspace/topn_test# sqlite3 logSecurity.db
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> .tables
securityLog_day_20210929
sqlite> DELETE FROM securityLog_day_20210929;
sqlite> .exit
root@firewall:/mnt/flash/workspace/topn_test# ll -h
total 100K
drwxr-xr-x 2 root root 4.0K Sep 3 00:36 ./
drwxr-xr-x 10 root root 4.0K Sep 3 00:00 ../
-rw-r--r-- 1 root root 12K Sep 3 00:36 logSecurity.db
-rwxrwxrwx 1 root root 80K Sep 3 00:02 sqlite_test*
测试有效。
Original: https://blog.csdn.net/chenwr2018/article/details/122024054
Author: chenwr2018
Title: sqlite auto-vacuum的使用
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/815915/
转载文章受原作者版权保护。转载请注明原作者出处！