January 30, 2023
By: Kevin'

SQLite数据库瘦身

  1. SQLite 的默认行为: 空间标记为空闲, 但不立即释放
  2. 何时需要考虑数据库瘦身?
  3. VACUUM 命令: 数据库瘦身的利器, 但需谨慎使用
  4. 碎片整理的重要性: 提升查询性能
  5. 如何进行碎片整理?
  6. VACUUM 的第三种应用场景: 应用配置更改
  7. Auto-vacuum: 自动化的空间回收, 但并非万能
  8. 真空策略: 监控与决策
  9. 结语

SQLite数据库会随着数据量的增长而自动增大, 这似乎理所当然. 但是, 当删除数据时, SQLite 数据库文件的大小真的会缩小吗? 答案可能有些意外.

用一个例子来说明 SQLite 在删除数据时的实际行为.

假设有一个 SQLite 数据库文件, 大小接近 600MB. 使用命令行工具打开这个数据库, 并执行以下 SQL 命令, 删除 contacts 表中 state 列值为 'Texas' 的所有记录.

DELETE FROM contacts WHERE state = 'Texas';

代码补充: 以上 SQL 命令可以直接在 SQLite 命令行工具 (sqlite3) 中执行. 例如:

sqlite3 your_database.db
sqlite> DELETE FROM contacts WHERE state = 'Texas';
sqlite> .exit

这个数据库只有一个表 contacts 表, 而刚刚删除的 'Texas' 州的联系人约占总联系人的 5%. 也就是说, 我们删除了大约 5% 的数据行.

问题: 这次删除操作会释放多少磁盘空间呢?

我们再次查看数据库文件的大小, 却发现 文件大小没有任何变化! 这是怎么回事?

SQLite 的默认行为: 空间标记为空闲, 但不立即释放

SQLite 默认情况下并不会在删除记录后立即释放磁盘空间. 当在 SQLite 中删除数据时, 数据库会从底层数据页中删除数据, 并将这些未使用的页面标记为"空闲" . 但它并不会真正缩小数据库文件的大小, 释放磁盘空间.

会是问题吗? 这取决于使用场景和生产环境. 如果应用频繁进行大量的数据删除操作, 数据库文件可能会比实际需要的大得多.

那么, 这是否意味着删除的数据所占用的空间就浪费了呢? 当然不是. 没有任何空间被浪费. 当一行数据被删除后, 用于存储这些数据的底层数据库页面会被标记为空闲或可用, 并且 这些空闲页面会在未来需要更多存储空间时被重新利用.

何时需要考虑数据库瘦身?

然而, 如果删除了大量数据, 或者正面临磁盘空间不足的困境, 可能会想: 有没有办法回收这些被标记为空闲的存储空间, 真正缩小 SQLite 数据库文件的大小呢?

答案是肯定的. SQLite 提供了 " VACUUM" (真空) 命令来实现数据库瘦身.

VACUUM 命令: 数据库瘦身的利器, 但需谨慎使用

"VACUUM"命令的作用是重建整个数据库文件. 它会将数据库内容写入一个新的临时文件, 在这个过程中, SQLite 会清理碎片, 并仅复制实际使用的数据页到新文件中, 从而达到回收空间, 缩小文件大小的目的.

VACUUM;

但是, 使用 VACUUM 命令需要注意以下几点:

  • 代价昂贵: VACUUM 操作是一个非常耗时的过程. 它需要获取数据库的独占锁, 这意味着在 VACUUM 执行期间, 数据库将无法进行任何读写操作.
  • 耗时较长: 根据数据库的大小, VACUUM 可能需要几分钟甚至几十分钟才能完成.
  • 停机时间: 由于需要独占锁, VACUUM 操作通常需要一定的停机时间. 对于一些对可用性要求极高的应用, 停机时间可能是不可接受的.

如果可以接受停机时间, VACUUM 命令可以有效地:

  1. 释放磁盘空间: 回收被标记为空闲的页面, 缩小数据库文件大小.
  2. 整理碎片: VACUUM 还会对数据库进行碎片整理, 使相关数据在数据库文件中尽可能连续存储, 从而提高查询性能.

碎片整理的重要性: 提升查询性能

随着数据库的频繁插入, 更新和删除操作, 数据可能会分散存储在数据库文件的不同页面上, 导致数据碎片化.

什么是碎片化? 理想情况下, SQLite 会将相关数据连续存储在数据库中, 以优化 I/O 性能. 但随着时间的推移, 数据可能变得分散在数据库文件的各个页面中, 这会对查询性能产生明显的影响.

碎片化对性能的影响有多大? 在最坏的情况下, 查询速度可能会降低高达四倍. 虽然不是数量级的下降, 但这种程度的性能损失也不容忽视.

如何判断数据库是否碎片化严重? 目前没有直接的工具可以量化数据库的碎片化程度. 最好的方法是 设置日志记录, 跟踪查询性能随时间的变化. 如果日志记录的查询时间达到性能下降的阈值, 那么就可以考虑执行 VACUUM 操作来进行碎片整理.

如何进行碎片整理?

使用 VACUUM 命令可以对数据库进行碎片整理.

VACUUM 命令的工作原理(简要概括):

  1. 发起 VACUUM: 执行 VACUUM; 命令.
  2. 获取独占锁: SQLite 获取数据库的独占锁.
  3. 创建临时数据库: 创建一个新的临时空数据库文件.
  4. 复制数据和整理碎片: 读取原始数据库的 schema, 在新数据库中创建表, 索引等结构, 并将原始数据库的内容复制到新数据库中. 在这个过程中, SQLite 会有效地进行碎片整理, 并释放任何未使用的空间.
  5. 替换原始数据库: SQLite 删除原始数据库, 并将新的临时数据库重命名为原始数据库的文件名.

VACUUM 的第三种应用场景: 应用配置更改

除了释放空间和整理碎片, VACUUM 还有第三种应用场景: 在修改某些数据库配置后, 应用配置更改.

例如, 如果用以下 PRAGMA 命令更改了页面大小 (page_size):

PRAGMA page_size = 4096;

代码补充: 设置页面大小的 PRAGMA 命令:

sqlite3 your_database.db
sqlite> PRAGMA page_size = 4096;
sqlite> PRAGMA page_size; -- 确认页面大小是否已更改
sqlite> .exit

这个更改只有在数据库为空时才会生效. 如果数据库不为空, 并且想修改的页面大小, 必须使用 VACUUM 命令 来重建数据库, 从而应用新的配置. 这实际上相当于从头开始创建一个具有新页面大小的新数据库.

Auto-vacuum: 自动化的空间回收, 但并非万能

除了手动执行 VACUUM, SQLite 还提供了 Auto-vacuum (自动真空) 功能. 但需要注意的是, Auto-vacuum 并不等同于完整的 VACUUM 操作.

Auto-vacuum 的作用主要集中在释放磁盘空间, 它并不能帮助进行碎片整理. 实际上, Auto-vacuum 甚至可能导致更严重的碎片化.

Auto-vacuum PRAGMA 的设置:

可以使用以下 PRAGMA 命令设置 Auto-vacuum 模式:

PRAGMA auto_vacuum = NONE; -- 默认模式, 不自动真空
PRAGMA auto_vacuum = FULL; -- 完全自动真空模式
PRAGMA auto_vacuum = INCREMENTAL; -- 增量自动真空模式

代码补充: 设置 Auto-vacuum 模式的 PRAGMA 命令:

sqlite3 your_database.db
sqlite> PRAGMA auto_vacuum = FULL;
sqlite> PRAGMA auto_vacuum; -- 确认 Auto-vacuum 模式
sqlite> .exit

Auto-vacuum 的三种模式:

  • NONE (默认): 不启用自动真空. 数据库的行为与我们最开始描述的一样, 删除操作不会立即释放磁盘空间.

  • FULL (完全自动真空): 在每次事务结束后, 任何空闲或未使用的页面都会被移动到数据库文件的末尾, 然后数据库文件会被截断, 从而释放磁盘空间.

    • 优点: 数据库文件始终保持尽可能小的状态.
    • 潜在问题: 频繁的页面添加和删除可能会导致额外的磁盘抖动, 并可能加剧磁盘碎片化. 此外, 每个事务的耗时可能会略微增加, 因为数据库连接需要在每个事务结束时执行一些与 Auto-vacuum 相关的清理工作.

  • INCREMENTAL (增量自动真空): 执行 Auto-vacuum 所需的信息会存储在数据库文件中. 数据库会跟踪空闲页面, 但不会自动执行真空操作.

    • 手动触发增量真空: 要实际执行真空操作, 需要使用以下 PRAGMA 命令手动触发增量真空:
    PRAGMA incremental_vacuum; -- 增量真空, 移除所有空闲页
PRAGMA incremental_vacuum(pages); -- 增量真空, 移除最多指定页数的空闲页

    代码补充: 触发增量真空的 PRAGMA 命令:

    sqlite3 your_database.db
sqlite> PRAGMA incremental_vacuum; -- 或 PRAGMA incremental_vacuum(100);
sqlite> .exit

真空策略: 监控与决策

选择哪种真空策略取决于具体需求和环境. 以下是一些建议:

1. 定期监控关键指标:

  • 空闲页列表计数 (freelist_count): 使用以下 PRAGMA 命令获取数据库中的空闲页数量:

    PRAGMA freelist_count;

    代码补充: 获取空闲页列表计数的 PRAGMA 命令:

    sqlite3 your_database.db
sqlite> PRAGMA freelist_count;
sqlite> .exit

  • 总页数 (page_count): 使用以下 PRAGMA 命令获取数据库的总页数:

    PRAGMA page_count;

    代码补充: 获取总页数的 PRAGMA 命令:

    sqlite3 your_database.db
sqlite> PRAGMA page_count;
sqlite> .exit

2. 基于空闲页比例判断是否需要真空:

只有当 空闲页计数占总页数的比例显著偏高 时, 才考虑执行 VACUUM 操作来缩小数据库文件大小. 如果空闲页比例很小, 则可能没有太多空间可以回收, 无需过度关注数据库瘦身.

3. 监控查询性能, 决定是否进行碎片整理:

由于没有直接的碎片化衡量工具, 需要 通过日志记录监控查询性能, 检测查询执行时间是否随时间推移而变慢. 如果查询速度明显下降, 并且通过日志可以量化性能下降的程度, 那么可以考虑执行 VACUUM 操作来进行碎片整理, 提升查询性能.

4. 选择合适的真空执行时机:

根据实际环境, 选择合适的 VACUUM 执行时机非常重要. 一些可能的选项包括:

  • 启动或关闭时: 在应用启动或关闭时执行 VACUUM.
  • 空闲时段: 在业务低峰期的空闲时段执行 VACUUM.
  • 用户手动触发: 提供一个菜单选项, 允许用户手动触发 VACUUM.
  • 应用更新后: 在应用更新或数据迁移后执行 VACUUM.
  • 维护时段: 在计划的维护时段(例如, 在夜间)执行 VACUUM.

结语

希望这篇文章能够帮助你更深入地理解 SQLite 数据库的空间管理和瘦身机制. 合理地使用 VACUUM 和 Auto-vacuum 可以帮助你更好地管理 SQLite 数据库, 优化性能, 并有效地利用磁盘空间.

Tags: sqlite sql