印刻数据库

外观

SQLite 数据库文件结构 ​

了解 SQLite 数据库文件的内部结构对于优化数据库性能、排查问题和进行数据恢复至关重要，尤其是在生产运维场景中。本文将详细介绍 SQLite 数据库文件的整体结构、页结构、B 树组织和其他相关文件，并提供实用的生产运维建议。

数据库文件概述 ​

文件组成 ​

  • 主数据库文件：扩展名为 .db 或无扩展名，包含数据库的所有表、索引和数据
  • 事务日志文件：
    • 回滚日志模式：-journal 文件
    • WAL 模式：-wal 文件和 -shm 共享内存文件
  • 临时文件：用于排序、临时表等操作

文件特点 ​

  • 单一文件：所有数据存储在单个文件中，便于管理和迁移
  • 跨平台兼容：同一数据库文件可在不同操作系统上使用
  • 可变大小：数据库文件大小根据数据量自动增长
  • 自包含：包含所有元数据和数据，无需外部文件

文件格式版本 ​

SQLite 使用文件格式版本号标识数据库文件的格式：

  • 文件格式版本：当前为 1（SQLite 3.0 及以上版本）
  • 写入版本：0 表示使用传统回滚日志，1 表示使用 WAL 模式
  • 读取版本：0 表示只能读取传统格式，1 表示可以读取 WAL 格式

数据库文件整体结构 ​

文件头 ​

  • 大小：前 100 字节
  • 主要内容：
    • 魔数（Magic Number）："SQLite format 3\000"，标识文件类型
    • 文件格式版本号
    • 页大小：数据库页的大小（默认 4KB，可在 512B-64KB 之间调整）
    • 写入版本和读取版本
    • 保留空间大小：每页末尾保留的空间大小
    • 最大嵌入载荷大小
    • 根页面位置：
      • 页 1：主数据库文件的根页面
      • 页 2：SQLITE_MASTER 表的根页面
    • 文本编码：UTF-8、UTF-16le 或 UTF-16be
    • 用户版本：用户自定义的版本号
    • 应用 ID：应用程序标识符
    • 版本验证器：用于检测数据库文件格式变更

数据库页 ​

  • 基本单位：数据库文件由固定大小的页组成
  • 页大小：从文件头中读取，默认为 4KB
  • 页类型：
    • B-Tree 索引页：存储索引数据
    • B-Tree 表页：存储表数据
    • 溢出页：存储大型 BLOB 或 TEXT 数据
    • 空闲列表页：管理空闲页
    • 空闲列表映射页：管理空闲列表页

页组织 ​

  • 连续存储：页在文件中连续存储
  • 页号：从 1 开始编号
  • 页头：每个页包含一个页头，描述页的类型和状态
  • 页数据：页头之后是实际的数据

页结构详解 ​

页头结构 ​

  • 大小：固定为 8 字节（B-Tree 页）或 12 字节（溢出页）
  • 主要内容（B-Tree 页）：
    • 页类型：
      • 0x02：内部索引页
      • 0x05：内部表页
      • 0x0a：叶子索引页
      • 0x0d：叶子表页
    • 空闲空间偏移：页中空闲空间的起始位置
    • 单元格数量：页中存储的单元格数量
    • 单元格内容偏移：单元格内容区域的起始位置
    • 右边页指针：内部页指向下一个兄弟页的指针（仅内部页有）

单元格结构 ​

  • 存储位置：页头之后是单元格指针数组，指向单元格内容
  • 单元格指针：每个指针占 2 字节，指向单元格内容的起始位置
  • 单元格内容：存储实际的数据记录或索引项

叶子表页单元格 ​

  • 结构：
    • 行号：占 1-9 字节，变长整数
    • 有效负载大小：占 1-9 字节，变长整数
    • 行数据：包含所有列的数据
    • 溢出指针：如果有效负载大于页大小的一定比例，指向溢出页

内部表页单元格 ​

  • 结构：
    • 行号：占 1-9 字节，变长整数
    • 子页指针：占 4 字节，指向下级 B-Tree 页
    • 行数据：包含行的前缀数据，用于搜索

索引页单元格 ​

  • 结构：
    • 有效负载大小：占 1-9 字节，变长整数
    • 索引数据：包含索引键值
    • 行号：占 1-9 字节，指向表数据的行号
    • 溢出指针：如果有效负载过大，指向溢出页

溢出页 ​

  • 用途：存储大型 BLOB 或 TEXT 数据
  • 结构：
    • 页头：12 字节，包含下一个溢出页的指针
    • 数据：存储实际的溢出数据
  • 链接结构：溢出页通过指针链接成链表
  • 生产注意事项：溢出页会降低查询性能，建议避免存储过大的 BLOB 或 TEXT 数据

B 树组织 ​

表 B 树 ​

  • 结构：每个表对应一个 B 树
  • 根页面：表的根页面位置存储在 SQLITE_MASTER 表中
  • 内部节点：包含行号和子页指针
  • 叶子节点：包含完整的行数据
  • 主键索引：表 B 树本身就是以主键为键的索引

索引 B 树 ​

  • 结构：每个索引对应一个 B 树
  • 根页面：索引的根页面位置存储在 SQLITE_MASTER 表中
  • 内部节点：包含索引键和子页指针
  • 叶子节点：包含索引键和指向表数据的行号
  • 唯一性：唯一索引确保索引键唯一

SQLITE_MASTER 表 ​

  • 用途：存储数据库的元数据
  • 结构：特殊的 B 树，根页面位于页 2
  • 列：
    • type：对象类型（table、index、view、trigger）
    • name：对象名称
    • tbl_name：表名（对于索引、触发器等）
    • rootpage：对象的根页面位置
    • sql：创建对象的 SQL 语句
  • 生产使用：可以通过查询 SQLITE_MASTER 表获取数据库结构信息，用于数据库迁移和备份

事务相关文件 ​

回滚日志文件 ​

  • 文件名：数据库名-journal
  • 用途：在回滚日志模式下，存储修改前的数据页
  • 结构：
    • 文件头：包含魔数和数据库页大小
    • 日志记录：每个记录包含页号和修改前的数据
    • 提交记录：表示事务已提交
  • 生命周期：事务提交后，回滚日志文件被删除或截断
  • 生产注意事项：回滚日志模式下，写入操作会锁定整个数据库，不适合高并发场景

WAL 文件 ​

  • 文件名：数据库名-wal
  • 用途：在 WAL 模式下，存储修改内容
  • 结构：
    • 文件头：包含魔数、版本号和检查点信息
    • 帧：每个帧包含页号、序列号和修改后的数据
    • 检查点：标记已合并到数据库文件的帧
  • 生命周期：通过检查点机制管理大小，不会自动删除
  • 生产注意事项：WAL 模式支持读写并发，适合高并发场景，但需要定期执行检查点

WAL 共享内存文件 ​

  • 文件名：数据库名-shm
  • 用途：在 WAL 模式下，用于多个连接之间共享 WAL 文件的状态信息
  • 结构：包含检查点信息、锁状态和其他共享数据
  • 生命周期：数据库连接关闭后自动删除

临时文件 ​

  • 用途：用于排序、临时表、子查询等操作
  • 位置：默认存储在系统临时目录
  • 命名：使用随机文件名，如 sqlite_temp_*.db
  • 生命周期：连接关闭后自动删除
  • 生产注意事项：确保临时目录有足够的磁盘空间，避免因临时文件过大导致系统故障

版本差异 ​

版本演进 ​

SQLite 版本文件格式变化生产环境影响
3.0 (2004)引入现代文件格式，版本号 1基础版本，稳定可靠
3.7.0 (2010)引入 WAL 模式支持支持读写并发，提高高并发场景性能
3.8.0 (2013)改进页格式，支持更大的数据库支持更大规模的数据库，适合数据量增长较快的场景
3.11.0 (2016)改进溢出页处理提高大型 BLOB 数据的存储效率

向后兼容性 ​

  • 向后兼容：新版本的 SQLite 可以读取旧版本的数据库文件
  • 向前兼容：旧版本的 SQLite 无法读取新版本的数据库文件（如果使用了新特性）
  • 自动升级：打开旧版本数据库文件时，会自动升级文件格式（如果需要）
  • 生产建议：在升级 SQLite 版本前，备份数据库文件，并测试兼容性

文件访问模式 ​

读取模式 ​

  • 共享锁：允许多个连接同时读取数据库文件
  • 读取流程：
    1. 获取共享锁
    2. 读取数据库文件内容
    3. 释放共享锁（或保持，取决于连接设置）

写入模式 ​

  • 排他锁：在回滚日志模式下，需要获取排他锁
  • WAL 模式：只需要预留锁，允许其他连接读取
  • 写入流程：
    1. 获取预留锁
    2. 写入 WAL 文件
    3. 提交事务
    4. 释放预留锁

生产运维最佳实践 ​

页大小优化 ​

  • 默认页大小：4KB
  • 调整原则：
    • 对于大型数据库，使用较大的页大小（8KB-16KB）可以减少 B 树高度，提高查询性能
    • 对于小型数据库，使用较小的页大小（2KB）可以减少空间浪费
    • 页大小应与磁盘扇区大小对齐，提高 I/O 效率
  • 生产操作：
    sql
    -- 设置页大小（必须在创建数据库时设置）
PRAGMA page_size = 8192;
VACUUM;  -- 重建数据库以应用新的页大小

预留空间优化 ​

  • 用途：为后续的插入和更新操作预留空间
  • 调整原则：
    • 对于频繁更新的表，增加预留空间可以减少页分裂
    • 对于静态表，减少预留空间可以节省磁盘空间
  • 生产操作：
    sql
    -- 设置预留空间为 10%
PRAGMA reserve_space = 10;

WAL 模式优化 ​

  • 优点：
    • 读写并发，提高性能
    • 更快的恢复速度
    • 减少磁盘 I/O
  • 生产优化建议：
    • 定期执行检查点，控制 WAL 文件大小
    • 根据写入负载调整 wal_autocheckpoint 参数
    • 生产操作：
      sql
      -- 设置自动检查点阈值为 1000 页
PRAGMA wal_autocheckpoint = 1000;

-- 手动执行检查点
PRAGMA wal_checkpoint;

数据库文件维护 ​

  • 定期 VACUUM：重建数据库文件，减少碎片，提高性能
  • 定期完整性检查：检测数据库文件是否损坏
  • 生产操作：
    sql
    -- 执行 VACUUM
VACUUM;

-- 检查数据库完整性
PRAGMA integrity_check;

常见问题（FAQ） ​

Q: SQLite 数据库文件最大可以多大？ ​

A: SQLite 理论上支持最大 281TB 的数据库文件，但实际建议不超过 1TB，以确保良好的性能和可维护性。

Q: 如何查看 SQLite 数据库文件的页大小？ ​

A: 可以使用以下方法：

  • 使用 PRAGMA page_size; 命令
  • 直接查看数据库文件的前 100 字节
  • 使用 SQLite 管理工具，如 DB Browser for SQLite

Q: 什么是 SQLite 的溢出页？ ​

A: 当一条记录的大小超过页大小的一定比例（默认 3/4）时，超出部分会存储在溢出页中。溢出页通过指针链接成链表，虽然可以存储大型数据，但会降低查询性能。

Q: 如何优化 SQLite 数据库文件大小？ ​

A: 可以采取以下措施：

  • 使用 VACUUM 命令重建数据库文件，减少碎片
  • 优化表结构，避免过度使用 BLOB 类型
  • 调整页大小和预留空间
  • 定期清理不再使用的数据

Q: SQLite 数据库文件损坏的原因有哪些？ ​

A: 数据库文件损坏的常见原因：

  • 意外断电或系统崩溃
  • 磁盘故障或文件系统错误
  • 恶意修改数据库文件
  • 错误的文件操作（如直接编辑数据库文件）
  • 版本不兼容

Q: 如何检测和修复 SQLite 数据库文件损坏？ ​

A: 可以使用以下方法：

  • 使用 PRAGMA integrity_check; 命令检测损坏
  • 使用 sqlite3 database.db ".recover" | sqlite3 new_database.db 命令恢复数据
  • 使用专业的 SQLite 修复工具

Q: 生产环境中应该使用回滚日志模式还是 WAL 模式？ ​

A: 建议在生产环境中使用 WAL 模式，因为它支持读写并发，提高性能，适合高并发场景。但需要注意定期执行检查点，控制 WAL 文件大小。

Q: 如何安全地备份 SQLite 数据库文件？ ​

A: 安全备份 SQLite 数据库文件的方法：

  • 在回滚日志模式下，确保没有写入操作，直接复制数据库文件
  • 在 WAL 模式下，执行 PRAGMA wal_checkpoint(FULL); 后再复制数据库文件
  • 使用 .backup 命令进行在线备份

总结 ​

SQLite 数据库文件采用简洁高效的结构设计，使用单一文件存储所有数据和元数据，便于管理和迁移。了解数据库文件的内部结构对于优化性能、排查问题和进行数据恢复至关重要。

主要要点：

  • 数据库文件由页组成，页是基本的存储单位
  • 采用 B 树结构存储表数据和索引
  • 支持两种事务日志模式：回滚日志和 WAL 模式
  • 包含元数据存储在 SQLITE_MASTER 表中
  • 可以通过调整页大小、预留空间等参数优化性能
  • 不同 SQLite 版本支持不同的文件格式特性，需要根据生产环境选择合适的版本

通过深入理解 SQLite 数据库文件结构，并遵循本文的生产运维最佳实践，DBA 可以更好地管理和优化 SQLite 数据库，确保其性能、可靠性和安全性。