技术图谱

Appearance

一、核心概念与架构 ​

1. 核心概念 ​

数据库模型： 轻量级关系型数据库管理系统（RDBMS），以单一文件形式存储。

表（Table）： 数据库的基本存储单位，由行（Row）和列（Column）组成。

字段（Field/Column）： 表中的一列，定义了数据的类型和属性。

记录（Record/Row）： 表中的一行，包含一个完整的数据实体。

主键（Primary Key）： 用于唯一标识表中每条记录的一个或多个字段。

索引（Index）： 提高查询性能的数据结构，类似于书籍的目录。

事务（Transaction）： 一组SQL操作，要么全部成功执行，要么全部不执行（ACID特性）。

视图（View）： 基于一个或多个表的查询结果的虚拟表。

触发器（Trigger）： 在特定表上执行特定操作时自动触发的一段SQL代码。

约束（Constraint）： 对表中数据的限制条件（如NOT NULL、UNIQUE、FOREIGN KEY等）。

2. 架构与组成 ​

SQLite 整体架构： 嵌入式数据库，无服务器、无配置，单一文件存储。

核心组件：

  • SQL解析器： 将SQL语句解析成语法树
  • 查询优化器： 生成最优的执行计划
  • 虚拟机： 执行SQLite虚拟机指令
  • 存储引擎： 处理数据的存储和检索
  • B树模块： 实现索引和表存储
  • Pager模块： 管理内存分页和磁盘I/O
  • 操作系统接口： 提供跨平台的文件操作

文件结构：

  • 单一数据库文件，包含表、索引、视图、触发器等所有数据库对象
  • 支持事务回滚日志文件（.wal）和临时文件

3. 数据类型 ​

动态类型系统： SQLite使用动态类型，列的类型是建议性的，不是强制性的。

存储类（Storage Classes）：

  • NULL：空值
  • INTEGER：有符号整数（1, 2, 3, 4, 6, 8字节）
  • REAL：浮点型（8字节IEEE浮点数）
  • TEXT：文本字符串（UTF-8、UTF-16BE或UTF-16LE编码）
  • BLOB：二进制大对象（按原样存储）

亲和类型（Type Affinity）：

  • TEXT：倾向于存储文本数据
  • NUMERIC：倾向于存储数值数据
  • INTEGER：倾向于存储整数值
  • REAL：倾向于存储浮点值
  • BLOB：不做任何转换，按原样存储

4. 索引与优化 ​

索引类型：

  • 主键索引： 基于表的主键创建的索引
  • 唯一索引： 确保字段值唯一的索引
  • 普通索引： 提高查询速度的常规索引
  • 覆盖索引： 索引包含了查询所需的全部列
  • 表达式索引： 基于表达式计算结果创建的索引

索引实现： SQLite使用B+树作为索引数据结构。

索引优化：

  • 为WHERE、JOIN、ORDER BY子句中频繁使用的列创建索引
  • 避免在索引列上使用函数或表达式
  • 合理设计组合索引
  • 定期分析表（ANALYZE）以更新统计信息

查询优化技巧：

  • 使用EXPLAIN查询执行计划
  • 避免使用SELECT *，只查询需要的列
  • 合理使用索引，避免全表扫描
  • 使用参数绑定，避免SQL注入和提高性能

5. 事务与并发控制 ​

ACID 特性： SQLite支持完整的ACID事务。

事务隔离级别： SQLite默认为序列化隔离级别（SERIALIZABLE）。

锁定机制：

  • 数据库级锁： 读锁（SHARED）和写锁（EXCLUSIVE）
  • 表级锁： 在执行某些操作时锁定整张表
  • 行级锁： 通过MVCC（多版本并发控制）实现

事务模式：

  • DEFERRED： 开始时不获取锁，需要时才获取
  • IMMEDIATE： 开始时获取读锁，准备稍后获取写锁
  • EXCLUSIVE： 开始时就获取写锁

WAL模式： 预写式日志模式，提高并发性能

  • 写操作不阻塞读操作
  • 减少数据库文件碎片
  • 提高写入性能

6. 特殊功能 ​

内存数据库： 支持将数据库完全存储在内存中

数据库附件： 可以在一个连接中访问多个数据库文件

自定义函数： 支持通过宿主语言扩展SQL函数

虚拟表： 支持自定义表实现，用于访问外部数据源

全文搜索： 内置FTS3/FTS4/FTS5全文搜索扩展

JSON支持： 内置JSON函数，支持JSON数据操作

地理空间支持： 内置R*树索引，支持地理位置数据

7. 管理与维护 ​

备份与恢复：

  • 文件复制： 简单复制数据库文件（需在无写操作时）
  • VACUUM命令： 回收未使用的空间，优化数据库文件
  • .backup命令： SQLite命令行工具的备份功能
  • 导出/导入： 使用.dump命令导出，使用sqlite3命令导入

数据库优化：

  • ANALYZE： 更新统计信息，帮助查询优化器
  • VACUUM： 重建数据库文件，减少碎片
  • REINDEX： 重建索引
  • CHECKPOINT： 在WAL模式下将日志内容写入主数据库文件

常用工具：

  • sqlite3： 命令行客户端工具
  • DB Browser for SQLite： 图形界面管理工具
  • SQLiteStudio： 跨平台图形界面管理工具
  • 各种编程语言的SQLite驱动

二、SQLite 常见问题及答案 ​

1. 基础概念类 ​

Q1: SQLite 是什么类型的数据库？它的主要特点是什么？ ​

A1:

  • SQLite是一种嵌入式关系型数据库管理系统（RDBMS）。
  • 主要特点：
    1. 无服务器架构：不需要单独的服务器进程
    2. 零配置：安装后即可使用，无需配置
    3. 单一文件存储：整个数据库保存在一个文件中
    4. 轻量级：库文件小，资源占用少
    5. 跨平台：支持几乎所有操作系统
    6. 支持ACID事务：保证数据的一致性和可靠性
    7. 开源免费：使用不受限制

Q2: SQLite 的动态类型系统是什么意思？与其他数据库有什么不同？ ​

A2:

  • 动态类型系统是指SQLite允许在同一个列中存储不同类型的数据，列的类型是建议性的，不是强制性的。
  • 与其他数据库的主要区别：
    1. 大多数关系型数据库（如MySQL、PostgreSQL）使用静态类型系统，列的数据类型在创建表时确定且严格执行
    2. SQLite的列有类型亲和性（Type Affinity），会优先将值转换为列的亲和类型
    3. 动态类型系统提供了更大的灵活性，但可能导致数据一致性问题
    4. 其他数据库的静态类型系统提供了更强的数据一致性保障，但灵活性较低

Q3: WAL 模式是什么？它有什么优缺点？ ​

A3:

  • WAL（Write-Ahead Logging）模式是SQLite的一种事务日志模式，写操作先写入日志文件，再异步写入主数据库文件。
  • 优点：
    1. 读操作不阻塞写操作，写操作也不阻塞读操作，提高并发性能
    2. 减少数据库文件碎片
    3. 写入性能更好，特别是对于大量小事务
    4. 故障恢复更快
  • 缺点：
    1. 需要额外的磁盘空间存储WAL文件
    2. 数据库文件和WAL文件必须同时备份，否则可能导致数据不一致
    3. 某些特殊操作可能受限制

2. 性能优化类 ​

Q4: 如何优化SQLite查询性能？ ​

A4:

  • 合理创建索引：为WHERE、JOIN、ORDER BY子句中频繁使用的列创建索引
  • 使用EXPLAIN分析查询执行计划，找出性能瓶颈
  • 避免使用SELECT *，只查询需要的列
  • 使用参数绑定，而不是字符串拼接，提高性能并防止SQL注入
  • 对于复杂查询，考虑使用视图或临时表
  • 适当使用事务：批量操作时使用事务可以显著提高性能
  • 调整页面大小和缓存大小：根据应用需求优化SQLITE_PAGE_SIZE和缓存参数
  • 定期执行VACUUM命令：回收未使用的空间，优化数据库文件
  • 使用WAL模式：提高并发读写性能

Q5: 索引在什么情况下会失效？如何避免？ ​

A5:

  • 索引失效的常见情况：
    1. 在索引列上使用函数或表达式（如WHERE LOWER(name) = 'john'）
    2. 使用!=、<>等操作符（虽然可能仍会使用索引，但效率较低）
    3. LIKE查询以通配符开头（如LIKE '%john'）
    4. 在复合索引中不使用最左前缀列
    5. 数据类型不匹配（如字符串列与数字比较）
    6. 对索引列进行运算（如WHERE price * 1.1 > 100）
  • 避免方法：
    1. 尽量避免在索引列上使用函数或表达式
    2. 对于需要函数处理的情况，考虑使用表达式索引
    3. 设计复合索引时遵循最左前缀原则
    4. 保持数据类型一致性
    5. 对于LIKE查询，尽量使用前缀匹配（如LIKE 'john%'）

Q6: 如何提高SQLite的写入性能？ ​

A6:

  • 使用事务：将多个写操作包装在一个事务中
  • 使用WAL模式：设置PRAGMA journal_mode=WAL
  • 调整同步模式：适当降低同步级别（但会增加数据丢失风险）
  • 批量插入：使用INSERT INTO ... VALUES (...), (...), (...)语法
  • 禁用自动提交：设置PRAGMA synchronous=OFF（仅在数据不重要时使用）
  • 增加页面缓存：设置更大的缓存大小（PRAGMA cache_size）
  • 避免频繁的索引更新：可以先删除索引，插入数据后再重建
  • 优化表结构：减少不必要的触发器和约束

3. 部署与维护类 ​

Q7: 如何备份SQLite数据库？有哪些注意事项？ ​

A7:

  • 备份方法：
    1. 文件复制：在无写操作时直接复制数据库文件
    2. 使用VACUUM INTO命令：VACUUM INTO 'backup.db'
    3. 使用.dump命令导出：sqlite3 database.db ".dump" > backup.sql
    4. 使用.backup命令：sqlite3 database.db ".backup backup.db"
  • 注意事项：
    1. 备份时应确保没有写操作，否则可能导致备份文件损坏
    2. 在WAL模式下，需要同时备份数据库文件和WAL文件
    3. 定期备份，制定合理的备份策略
    4. 测试备份文件的可恢复性
    5. 对于重要数据，考虑增量备份或异地备份

Q8: 如何处理SQLite数据库锁定问题？ ​

A8:

  • 锁定原因：SQLite使用文件锁实现并发控制，当多个进程同时访问数据库时可能出现锁定
  • 解决方法：
    1. 减少事务持续时间：尽量缩短事务的执行时间
    2. 优化查询：减少长时间运行的查询
    3. 使用WAL模式：提高并发性能，减少锁定争用
    4. 实现重试机制：在应用程序中处理锁定错误并重试操作
    5. 避免长时间持有数据库连接
    6. 对于高并发场景，考虑使用连接池或分布式数据库
  • 锁定类型及处理：
    • 共享锁（读锁）：多个读操作可以同时进行
    • 排它锁（写锁）：写操作需要独占访问
    • 数据库锁定超时：可以设置超时时间，避免无限等待

Q9: SQLite数据库文件过大怎么办？如何进行压缩？ ​

A9:

  • 原因分析：数据库文件过大可能是由于大量删除操作导致的空间未回收、数据量增长或碎片过多
  • 解决方法：
    1. 执行VACUUM命令：回收未使用的空间，优化数据库文件
    2. 重建索引：使用REINDEX命令重建所有索引
    3. 清理不需要的数据：定期删除或归档历史数据
    4. 分割数据库：对于非常大的数据库，可以考虑分割成多个数据库文件
    5. 调整页面大小：通过PRAGMA page_size设置合适的页面大小
    6. 使用WAL模式：减少数据库文件碎片
  • 预防措施：
    1. 定期执行VACUUM命令
    2. 合理设计数据保留策略
    3. 避免频繁的插入和删除操作

基于 MIT 许可发布

版权所有 © 2025 yizhuo