Skip to main content

SQLite VACUUM如何优化数据库

为什么需要SQLite真空命令

首先,当删除表、视图、索引和触发器等数据库对象或从表中删除数据时,数据库文件大小保持不变。因为SQLite只是将删除的对象标记为自由对象,并将其保留供将来使用。因此,数据库文件的大小总是在增长。

其次,当从表中插入或删除数据时,索引和表会变得支离破碎,尤其是对于插入、更新和删除次数较多的数据库。

第三,insert、update和delete操作会在单个数据库页面中创建未使用的数据块。它减少了单个页面中可以存储的行数。因此,它增加了保存表格的页数。因此,它会增加表的存储开销,需要更多的读/写时间,并降低缓存性能。

SQLite提供了真空命令来解决上述三个问题。

SQLite首先将数据库文件中的数据复制到临时数据库。此操作会对数据库对象进行碎片整理,忽略可用空间,并重新打包各个页面。然后,SQLite将临时数据库文件的内容复制回原始数据库文件。原始数据库文件被覆盖。

因为VACUUM命令会重建数据库,所以可以使用它更改某些特定于数据库的配置参数,例如页面大小、页面格式和默认编码。为此,可以使用pragma设置新值,然后清空数据库。

SQLite真空命令

除rowid值外,VACUUM命令不会更改数据库的内容。如果使用INTEGER主键列,则真空不会更改该列的值。但是,如果使用未关联的rowid,真空命令将重置rowid值。除了更改rowid值,VACUUM命令还从头开始构建索引。

定期执行VACUUM命令是一种很好的做法,尤其是从数据库中删除大型表或索引时。

需要注意的是,VACCUM命令需要存储来保存原始文件和副本。此外,VACUUM命令要求以独占方式访问数据库文件。换句话说,如果数据库有挂起的SQL语句或打开的事务,真空命令将无法成功运行。

目前,从3.9.2版开始,您可以在主数据库上运行真空命令,而不是附加的数据库文件。

尽管SQLite启用自动真空模式,自动触发真空过程,但存在一些限制。手动运行真空命令是一种很好的做法。

如何运行SQLite真空命令

下面显示了如何运行真空命令:

VACUUM;

确保在运行命令时没有打开的事务。

以下语句启用全自动真空模式:

PRAGMA auto_vacuum = FULL;

要启用增量真空,请使用以下语句:

PRAGMA auto_vacuum = INCREMENTAL;

以下语句禁用自动真空模式:

PRAGMA auto_vacuum = NONE;

插入从句

下面是真空with INTO子句的语法:

VACUUM schema-name INTO filename;

带有INTO子句的真空语句保持原始数据库文件不变,并使用指定的文件名创建新数据库。新数据库将包含与原始数据库相同的逻辑内容,但已完全清空。

INTO子句中的文件名可以是任何计算结果为字符串的SQL表达式。它必须是指向不存在的文件或空文件的路径,否则真空导入命令将导致错误。

真空命令对于生成实时数据库的备份副本非常有用。它是事务安全的,生成的数据库是原始数据库的一致快照。但是,如果计划外关机或断电中断了命令,则生成的数据库可能已损坏。

下面的语句使用VACUUM INTO命令生成一个文件名为chinook_backup的新数据库。从chinook数据库的主模式中复制数据的数据库:

VACUUM main INTO 'c:\sqlite\db\chinook_backup.db';

在本教程中,您已经了解了为什么需要使用SQLite VACUUM命令,以及如何运行它来优化数据库。