SQLite学习笔记
参数查询
PRAGMA
PRAGMA 是 SQLite 中一种非常特殊的指令，你可以把它理解为SQLite 的“控制面板”或“调节旋钮”。
与标准的 SQL 语句（如 SELECT, INSERT）不同，PRAGMA 是 SQLite 独有的，主要用于：
- 修改数据库引擎的行为（如：开启外键、设置缓存大小）。
- 查询数据库的元数据（如：查看表结构、索引列表）。
- 执行维护操作（如：完整性检查、清理空间）。
以下是 SQLite 中常用 PRAGMA 命令的详细说明，按功能分类整理：
查询元数据（数据字典）
| 命令 | 说明 | 示例 |
|---|---|---|
| table_info | 获取表的列信息（列名、类型、是否主键等）。 | PRAGMA table_info('user_table'); |
| index_list | 列出表上的所有索引。 | PRAGMA index_list('user_table'); |
| index_info | 查看某个具体索引包含哪些列。 | PRAGMA index_info('idx_user_name'); |
| database_list | 列出当前挂载的所有数据库（主库及附加库）。 | PRAGMA database_list; |
| foreign_key_list | 查看表定义的外键信息。 | PRAGMA foreign_key_list('order_table'); |
性能与缓存优化
这些设置通常用于提升数据库的读写速度，建议在数据库连接建立时进行配置。
| 命令 | 说明 | 推荐设置/示例 |
|---|---|---|
| cache_size | 设置内存中缓存的数据库页数。值越大，读取越快，但占用内存越多。 | PRAGMA cache_size = -2000; (负数表示KB，即2MB) |
| temp_store | 控制临时表存储位置。 | PRAGMA temp_store = MEMORY; (存入内存，速度更快) |
| synchronous | 控制数据写入磁盘的同步级别。 FULL: 最安全（默认），最慢。 NORMAL: 平衡。 OFF: 最快，但在断电时可能丢失数据。 |
PRAGMA synchronous = NORMAL; |
| journal_mode | 设置日志模式。 DELETE: 默认模式。 WAL: 推荐。允许读写并发，性能更好。 |
PRAGMA journal_mode = WAL; |
数据完整性与安全
这些命令关乎数据的正确性和约束。
| 命令 | 说明 | 示例 |
|---|---|---|
| foreign_keys | 重要。SQLite 默认不强制执行外键约束，必须手动开启。 | PRAGMA foreign_keys = ON; |
| integrity_check | 检查整个数据库文件的完整性，看是否有损坏。 | PRAGMA integrity_check; |
| encoding | 查看或设置数据库的字符编码（UTF-8, UTF-16 等）。 | PRAGMA encoding; |
维护与清理
用于数据库文件的瘦身和重构。
| 命令 | 说明 | 示例 |
|---|---|---|
| auto_vacuum | 设置自动回收删除数据后的空闲空间。 NONE: 默认，不自动回收。 FULL: 自动回收。 INCREMENTAL: 增量回收。 |
PRAGMA auto_vacuum = FULL; |
| page_size | 设置数据库页大小（通常为 1024, 2048, 4096 等）。 | PRAGMA page_size = 4096; |
| wal_checkpoint | 在 WAL 模式下，强制将日志文件的数据写回主数据库文件。 | PRAGMA wal_checkpoint; |
语法与注意事项
PRAGMA 命令有两种主要形式：
- 读取值：
PRAGMA 命令名; - 设置值：
PRAGMA 命令名 = 值;或PRAGMA 命令名(值);
关键注意事项：
- 作用域：大多数
PRAGMA设置（如cache_size,foreign_keys）仅对当前数据库连接有效。如果你关闭了连接再重新打开，设置会恢复默认值。因此，通常建议在每次建立连接后立即执行必要的PRAGMA设置。 - 静默失败：如果你拼写错了一个
PRAGMA命令，SQLite 不会报错，而是会直接忽略它。 - 不可逆性：某些设置（如
page_size或encoding）只能在数据库创建之初设置，一旦有数据写入，通常无法更改（除非导出数据重建库）。
生产环境推荐配置清单
-- 1. 核心并发与性能配置
PRAGMA journal_mode = WAL; -- 开启预写日志，支持高并发
PRAGMA synchronous = NORMAL; -- 平衡速度与安全性
PRAGMA busy_timeout = 5000;
PRAGMA wal_autocheckpoint = 1000;
-- 2. 内存与缓存优化
PRAGMA cache_size = -20000; -- 分配 20MB 缓存
PRAGMA mmap_size = 268435456;
PRAGMA temp_store = MEMORY; -- 临时表存内存
-- 3. 数据完整性
PRAGMA foreign_keys = ON; -- 强制开启外键约束
详细配置原因解析
1. 开启预写日志模式
- 命令:
PRAGMA journal_mode = WAL; - 原因: 这是 SQLite 生产环境最重要的配置。
- 默认行为 (
DELETE): 每次写入时，SQLite 会锁定整个数据库文件，导致读写互斥（写入时不能读取，读取时不能写入）。 - WAL 模式: 写入操作会先写入一个单独的
-wal文件。这意味着读取者不会阻塞写入者，写入者也不会阻塞读取者。对于 Web 服务等高并发场景，这能带来数量级的性能提升。
- 默认行为 (
2. 平衡同步策略
- 命令:
PRAGMA synchronous = NORMAL; - 原因: 在“速度”与“极端安全性”之间取得平衡。
FULL(默认): 每次事务提交都强制刷盘（fsync），极其安全但速度慢，尤其是在机械硬盘上。OFF: 仅由操作系统决定何时刷盘，速度最快，但断电可能导致数据库损坏。NORMAL: 推荐。仅在 WAL 模式下切换检查点（Checkpoint）时进行 fsync。如果系统崩溃，数据通常能保持完整；只有在操作系统崩溃（不仅仅是断电）时才会有风险。这是现代生产环境的主流选择。
3. 解决“数据库被锁定”问题
- 命令:
PRAGMA busy_timeout = 5000; - 原因: 防止应用程序因短暂的锁冲突而直接报错崩溃。
- 即使开启了 WAL 模式，在极高并发写入或进行 Schema 变更时，仍可能出现锁竞争。
- 设置
5000(毫秒) 意味着：如果数据库正忙，SQLite 会等待 5 秒，直到锁释放。如果 5 秒后仍无法获取锁，才抛出SQLITE_BUSY错误。这能显著提高系统的容错率。
4. 优化 WAL 检查点频率
- 命令:
PRAGMA wal_autocheckpoint = 1000; - 原因: 控制 -wal文件的大小和回写频率。
- 该参数定义了 WAL 文件中积累多少页（Page）后触发一次检查点（将数据从 WAL 文件合并回主数据库文件）。
- 默认值通常较大（1000页）。设置为
1000左右可以防止 WAL 文件无限增长占用过多磁盘空间，同时保证数据能及时落盘。
5. 增大页缓存
- 命令:
PRAGMA cache_size = -20000; - 原因: 减少磁盘 I/O，提升查询速度。
- SQLite 会在内存中缓存一部分数据库页。
- 默认值通常较小（约 2MB）。
- 负数单位是 KB：
-20000表示分配约 20MB 的内存用于缓存。对于内存充足的服务器，适当增大此值（如 50MB-100MB）可以显著提高热点数据的读取性能。
6. 启用内存映射 (MMAP)
- 命令:
PRAGMA mmap_size = 268435456; - 原因: 利用操作系统特性加速读取。
- 该值设置为 256MB (268435456 字节)。
- MMAP 允许 SQLite 直接将数据库文件映射到进程的虚拟内存地址空间。读取数据时，无需调用
read()系统调用，而是直接访问内存指针，由操作系统负责按需加载物理页。这对于只读查询密集型的应用非常有效。
7. 临时表存内存
- 命令:
PRAGMA temp_store = MEMORY; - 原因: 加速排序和复杂查询。
- 当执行
ORDER BY、GROUP BY或创建临时表时，如果数据量大，SQLite 会使用磁盘文件。 - 强制设为
MEMORY后，这些临时操作直接在 RAM 中进行，避免了频繁的磁盘读写。
- 当执行
8. 强制开启外键
- 命令:
PRAGMA foreign_keys = ON; - 原因: 保证数据逻辑一致性。
- SQLite 为了兼容旧版本，默认不执行外键约束。
- 在生产环境中，必须显式开启它，以防止出现“孤儿数据”（例如：订单表里引用了一个不存在的用户 ID）。
配置前后对比
| 特性 | 默认配置 | 生产环境推荐配置 | 影响 |
|---|---|---|---|
| 并发能力 | 低 (读写互斥) | 高 (读写并发) | 支持多用户同时访问 |
| 写入性能 | 较慢 (频繁 fsync) | 快 (NORMAL + WAL) | 响应时间显著降低 |
| 稳定性 | 容易报 BUSY 错误 |
稳定 (自动重试) | 减少程序崩溃 |
| 内存利用 | 保守 (缓存小) | 积极 (大缓存+MMAP) | 充分利用服务器内存 |
补充建议
- 持久化设置：除了
foreign_keys和busy_timeout必须在每次连接时设置外，其他设置（如journal_mode,synchronous）一旦设置，通常会保存在数据库文件中，下次重启依然有效。但为了保险起见，在代码初始化时统一设置一遍是最好的习惯。 - 定期维护：在 WAL 模式下，建议定期（如每天低峰期）运行
PRAGMA wal_checkpoint(TRUNCATE);来回收磁盘空间，或者让应用程序在启动时检查 WAL 文件大小。
数据字典
sqlite_master
| 字段名 | 数据类型 | 含义 | 说明 |
|---|---|---|---|
| type | TEXT | 对象类型 | 记录该条目属于哪种数据库对象。常见的取值有： 'table'：普通表 'index'：索引 'view'：视图 'trigger'：触发器 |
| name | TEXT | 对象名称 | 该对象的实际名字。例如表名、索引名或触发器名。 |
| tbl_name | TEXT | 所属表名 | 对于表本身，这个字段通常与 name 相同。对于索引或触发器，这个字段记录它们是属于哪张表的。 |
| rootpage | INTEGER | 根页号 | 该对象在数据库文件中存储位置的起始页编号。SQLite 底层使用 B-Tree 结构，这个值指向 B-Tree 的根节点页。对于视图和触发器，该值通常为 0 或 NULL。 |
| sql | TEXT | 创建语句 | 这是最有用的字段。它存储了创建该对象时使用的原始 SQL 语句（例如 CREATE TABLE ...）。如果是系统自动创建的索引（如主键索引），该字段可能为 NULL。 |
sqlite_master 表是只读的。你不能直接对它执行 INSERT、UPDATE 或 DELETE 操作。当你执行 CREATE TABLE 或 DROP TABLE 等命令时，SQLite 会自动维护这张表的内容。
sqlite_temp_master
结构与sqlite_master 表类似
如果你创建了临时表（CREATE TEMP TABLE），它们不会出现在 sqlite_master 中，而是存储在 sqlite_temp_master表中。
对象信息查询
查看所有用户创建的表
-- 系统内部也会有一些以 sqlite_ 开头的表，通常我们需要过滤掉它们
SELECT name, sql
FROM sqlite_master
WHERE type='table' AND name NOT LIKE 'sqlite_%';
获取某张表的建表语句
-- 建表DDL
SELECT sql
FROM sqlite_master
WHERE type='table' AND name='你的表名';
-- 表字段列表
PRAGMA table_info('contacts');
查看某张表下的所有索引
SELECT name, sql
FROM sqlite_master
WHERE type='index' AND tbl_name='你的表名';
-- 列出表的所有索引
PRAGMA index_list('tablename');
-- 查看某个具体索引包含哪些列
PRAGMA index_info('索引名称');
浙公网安备 33010602011771号