VACUUM command in SQLite

最新推荐文章于 2025-12-01 18:34:47 发布

原创最新推荐文章于 2025-12-01 18:34:47 发布 · 189 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

SQLite 专栏收录该内容

4 篇文章

订阅专栏

本文详细解释了SQLITE数据库中的VACUUM命令，包括其用途、工作原理以及注意事项。通过运行VACUUM命令，可以回收空闲空间、减少数据库文件大小、避免数据碎片化，并在删除数据后自动清理页面。此外，文章还讨论了如何在不同模式下使用VACUUM命令，以及其与自动清理模式的区别。

http://www.sqlite.org/lang_vacuum.html

The VACUUM command rebuilds the entire database. There are several reasons an application might do this:

Unless SQLite is running in "auto_vacuum=FULL" mode, when a large amount of data is deleted from the database file it leaves behind empty space, or "free" database pages. This means the database file might be larger than strictly necessary. Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file.
Frequent inserts, updates, and deletes can cause the database file to become fragmented - where data for a single table or index is scattered around the database file. Running VACUUM ensures that each table and index is largely stored contiguously within the database file. In some cases, VACUUM may also reduce the number of partially filled pages in the database, reducing the size of the database file further.
Normally, the database page_size and whether or not the database supports auto_vacuum must be configured before the database file is actually created. However, when not in write-ahead log mode, the page_size and/or auto_vacuum properties of an existing database may be changed by using the page_size and/or pragma auto_vacuum pragmas and then immediately VACUUMing the database. When in write-ahead log mode, only the auto_vacuum support property can be changed using VACUUM.

VACUUM only works on the main database. It is not possible to VACUUM an attached database file.

The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file. When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction. This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space.

The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY .

A VACUUM will fail if there is an open transaction, or if there are one or more active SQL statements when it is run.

As of SQLite version 3.1, an alternative to using the VACUUM command to reclaim space after data has been deleted is auto-vacuum mode, enabled using the auto_vacuum pragma. When auto_vacuum is enabled for a database free pages may be reclaimed after deleting data, causing the file to shrink, without rebuilding the entire database using VACUUM. However, using auto_vacuum can lead to extra database file fragmentation. And auto_vacuum does not compact partially filled pages of the database as VACUUM does.