印刻数据库

外观

SQLite 数据库文件优化 ​

本文档详细介绍 SQLite 数据库文件的优化方法和最佳实践，帮助您减小数据库文件大小，提高数据库性能。

数据库文件结构 ​

SQLite 数据库文件由以下部分组成：

  • 文件头：包含数据库版本、页大小、编码等元数据
  • B-Tree 页：存储表数据和索引
  • 空闲列表：管理空闲页
  • 预写日志（WAL）：用于实现事务的持久化（WAL 模式）
  • 回滚日志：用于事务回滚

文件优化方法 ​

1. 使用 VACUUM 命令 ​

VACUUM 命令用于重建数据库文件，回收空闲空间，减小文件大小。

sql
-- 执行 VACUUM
VACUUM;

-- 为特定表执行 VACUUM（SQLite 3.25.0+）
VACUUM table_name;

-- 执行 VACUUM 并分析表
VACUUM;
ANALYZE;

2. 调整页大小 ​

SQLite 默认页大小为 4KB，可以根据存储设备的特性调整页大小，提高 I/O 性能。

sql
-- 查看当前页大小
PRAGMA page_size;

-- 设置页大小（需要在创建数据库时设置，或使用 VACUUM 重建）
PRAGMA page_size = 8192;
VACUUM;

3. 启用自动 VACUUM ​

自动 VACUUM 可以在后台自动回收空闲空间，不需要手动执行 VACUUM 命令。

sql
-- 启用自动 VACUUM
PRAGMA auto_vacuum = INCREMENTAL;

-- 查看自动 VACUUM 设置
PRAGMA auto_vacuum;

-- 执行增量 VACUUM（当 auto_vacuum = INCREMENTAL 时）
PRAGMA incremental_vacuum;
PRAGMA incremental_vacuum(10); -- 回收 10 个空闲页

4. 优化 WAL 模式 ​

调整 WAL 自动检查点频率 ​

sql
-- 查看当前 WAL 自动检查点设置
PRAGMA wal_autocheckpoint;

-- 设置 WAL 自动检查点频率（页数量）
PRAGMA wal_autocheckpoint = 1000;

-- 手动执行检查点
PRAGMA wal_checkpoint;
PRAGMA wal_checkpoint(TRUNCATE); -- 截断 WAL 文件

控制 WAL 文件大小 ​

sql
-- 限制 WAL 文件大小
PRAGMA journal_size_limit = 1000000; -- 1MB

5. 优化索引 ​

  • 删除不再使用的索引
  • 避免创建冗余索引
  • 定期重建索引
sql
-- 删除不再使用的索引
DROP INDEX IF EXISTS unused_index;

-- 重建索引
DROP INDEX IF EXISTS idx_users_email;
CREATE INDEX idx_users_email ON users (email);

6. 优化表结构 ​

  • 删除不再使用的表
  • 优化列数据类型
  • 拆分大表
sql
-- 删除不再使用的表
DROP TABLE IF EXISTS unused_table;

-- 优化列数据类型（需要重建表）
CREATE TABLE new_table (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER DEFAULT 0
);
INSERT INTO new_table SELECT id, name, age FROM old_table;
DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;

文件优化最佳实践 ​

1. 定期执行 VACUUM ​

  • 根据数据库使用情况，定期执行 VACUUM 命令
  • 对于频繁更新的数据库，建议每周执行一次 VACUUM
  • 对于不太活跃的数据库，建议每月执行一次 VACUUM

2. 监控数据库文件大小 ​

  • 定期监控数据库文件大小变化
  • 设置文件大小告警阈值
  • 分析文件大小增长原因

3. 选择合适的页大小 ​

  • 根据存储设备的扇区大小选择合适的页大小
  • 对于 SSD 设备，建议使用较大的页大小（如 8KB 或 16KB）
  • 对于 HDD 设备，建议使用默认页大小（4KB）

4. 合理使用 WAL 模式 ​

  • 对于读写频繁的数据库，建议使用 WAL 模式
  • 定期执行检查点，控制 WAL 文件大小
  • 考虑使用 journal_size_limit 限制 WAL 文件大小

5. 优化数据存储 ​

  • 避免存储大量冗余数据
  • 使用合适的数据类型存储数据
  • 考虑使用压缩存储大字段

常见问题（FAQ） ​

Q: VACUUM 命令会锁定数据库吗？ ​

A: 是的，VACUUM 命令会锁定整个数据库，建议在业务低峰期执行。

Q: 自动 VACUUM 和手动 VACUUM 有什么区别？ ​

A: 自动 VACUUM 只能回收空闲页，不能重建整个数据库文件，而手动 VACUUM 可以重建整个数据库文件，更彻底地优化文件结构。

Q: 如何确定是否需要执行 VACUUM？ ​

A: 可以通过查看数据库文件大小和空闲列表大小来判断，如果空闲列表较大，说明需要执行 VACUUM。

sql
-- 查看空闲列表大小
PRAGMA freelist_count;

Q: 调整页大小会影响数据库兼容性吗？ ​

A: 不会，SQLite 支持多种页大小，新版本 SQLite 可以读取旧版本创建的不同页大小的数据库文件。