sqlite数据库文件组成：

主数据库文件：
├── fgedudb.db # 主数据库文件
├── fgedudb.db-wal # WAL日志文件（WAL模式）
└── fgedudb.db-shm # 共享内存文件（WAL模式）

文件结构：
┌────────────────────────────────────────┐
│ 数据库文件头（100字节） │
├────────────────────────────────────────┤
│ 页面1（数据库头页） │
├────────────────────────────────────────┤
│ 页面2（B-Tree根页） │
├────────────────────────────────────────┤
│ 页面3…N（数据页） │
├────────────────────────────────────────┤
│ 自由块列表 │
└────────────────────────────────────────┘

文件大小计算：
文件大小 = 页面大小 × 页面数量
默认：4096字节 × 页面数

sqlite数据库文件头（前100字节）：

偏移量 大小 说明
────────────────────────────────────────────────────
0 16 魔数：”SQLite format 3\000″
16 2 页面大小（大端序）
18 1 文件格式写版本号
19 1 文件格式读版本号
20 1 每页保留字节数
21 1 最大嵌入载荷比例
22 1 最小嵌入载荷比例
23 1 叶节点载荷比例
24 1 文件变化计数器
28 4 数据库大小（页数）
32 4 第一个自由块页号
36 4 自由块总数
40 4 Schema Cookie
44 4 Schema格式号
48 4 默认页面缓存大小
52 4 最大根页号（自动清理）
56 4 文本编码（1=UTF-8, 2=UTF-16le, 3=UTF-16be）
60 4 用户版本号
64 4 增量清理模式
68 4 应用程序ID
72 20 保留（零）
92 4 版本有效号
96 4 SQLite版本号

sqlite数据库页面类型：

1. B-Tree页面
├── 表B-Tree内部页（0x05）
├── 表B-Tree叶子页（0x0D）
├── 索引B-Tree内部页（0x02）
└── 索引B-Tree叶子页（0x0A）

2. 溢出页面（0x00）
└── 存储超过页面容量的数据

3. 自由块页面（0x00）
└── 存储被删除的页面

页面大小范围：
├── 最小：512字节
├── 默认：4096字节
├── 最大：65536字节
└── 必须是2的幂次

页面大小选择建议：
├── 小数据量：1024-2048字节
├── 一般应用：4096字节（默认）
├── 大数据量：8192-65536字节
└── SSD存储：可使用较大页面

sqlite数据库B-Tree页面结构：

叶子页结构：
┌────────────────────────────────────────┐
│ 页面头（8字节） │
├────────────────────────────────────────┤
│ 单元指针数组（从上往下增长） │
├────────────────────────────────────────┤
│ 未使用空间 │
├────────────────────────────────────────┤
│ 单元内容区（从下往上增长） │
└────────────────────────────────────────┘

内部页结构：
┌────────────────────────────────────────┐
│ 页面头（12字节） │
├────────────────────────────────────────┤
│ 单元指针数组 │
├────────────────────────────────────────┤
│ 未使用空间 │
├────────────────────────────────────────┤
│ 单元内容区 │
├────────────────────────────────────────┤
│ 最右子页指针（4字节） │
└────────────────────────────────────────┘

页面头字段：
├── 页面类型（1字节）
├── 第一个自由块偏移（2字节）
├── 单元数量（2字节）
├── 单元内容区起始偏移（2字节）
├── 碎片字节数（1字节）
└── 内部页额外：最右子页指针（4字节）

sqlite数据库B-Tree类型：

1. 表B-Tree
├── 存储表数据
├── 键为ROWID（64位整数）
├── 值为行数据
└── 每个表一个表B-Tree

2. 索引B-Tree
├── 存储索引数据
├── 键为索引列值+ROWID
├── 无值（仅键）
└── 每个索引一个索引B-Tree

B-Tree结构示意：
根页（内部页）
│
┌─────────────┼─────────────┐
↓ ↓ ↓
内部页 内部页 内部页
│ │ │
┌─────┼─────┐ ┌─────┼─────┐ ┌─────┼─────┐
↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓
叶子页 叶子页 叶子页 叶子页 叶子页 叶子页 叶子页 叶子页 叶子页

sqlite数据库ROWID机制：

ROWID特性：
├── 64位有符号整数
├── 自动递增
├── 唯一标识每行
└── B-Tree的主键

ROWID与INTEGER PRIMARY KEY：
sqlite> CREATE TABLE fgedu_test (
…> id INTEGER PRIMARY KEY, — 实际是ROWID的别名
…> name TEXT
…> );

sqlite> CREATE TABLE fgedu_test2 (
…> id INTEGER PRIMARY KEY AUTOINCREMENT, — 显式自增
…> name TEXT
…> );

ROWID查询：
sqlite> SELECT rowid, * FROM fgedu_test;
rowid id name
—— — —-
1 1 测试1
2 2 测试2

WITHOUT ROWID表：
sqlite> CREATE TABLE fgedu_config (
…> key TEXT PRIMARY KEY,
…> value TEXT
…> ) WITHOUT ROWID;
— 使用主键作为B-Tree键，无ROWID

sqlite数据库页面大小选择依据：

考虑因素：
├── 数据量大小
├── 记录大小
├── 查询模式
├── 存储介质
└── 内存限制

页面大小与性能关系：

小页面（512-1024字节）：
├── 优点：减少空间浪费
├── 缺点：B-Tree深度增加
├── 适用：小记录、随机访问
└── 场景：嵌入式设备

默认页面（4096字节）：
├── 优点：平衡性能和空间
├── 缺点：无明显缺点
├── 适用：大多数应用
└── 场景：通用应用

大页面（8192-65536字节）：
├── 优点：减少B-Tree深度
├── 缺点：增加空间浪费
├── 适用：大记录、顺序访问
└── 场景：数据仓库、分析

sqlite数据库页面大小配置建议：

应用场景 推荐页面大小 说明
──────────────────────────────────────────────
嵌入式设备 1024-2048 节省存储空间
移动应用 4096 平衡性能
Web应用 4096 默认推荐
数据分析 8192-16384 减少IO次数
大数据存储 32768-65536 大记录优化
SSD存储 8192-16384 发挥SSD优势

设置页面大小：
— 创建数据库时设置
sqlite> PRAGMA page_size = 8192;
sqlite> CREATE TABLE fgedu_data (…);

— 注意：必须在创建任何表之前设置
— 数据库创建后无法修改页面大小

sqlite数据库存储空间规划：

空间估算公式：
数据库大小 ≈ 数据量 × (1 + 索引开销比例) × 页面利用率

索引开销：
├── 主键索引：约10-20%
├── 普通索引：每个约10-15%
├── 唯一索引：每个约10-15%
└── 复合索引：约15-25%

空间规划示例：
数据量：100万条记录
每条记录：500字节
原始数据：500MB

索引规划：
├── 主键索引：50MB
├── 2个普通索引：100MB
└── 总索引：150MB

预估数据库大小：
500MB × 1.3 × 1.1 ≈ 715MB

建议预留空间：
├── 数据增长：30%
├── WAL文件：10%
├── 临时空间：10%
└── 总预留：50%

sqlite数据库索引与B-Tree关系：

每个索引对应一个B-Tree：
├── 主键：表B-Tree（自动）
├── UNIQUE约束：索引B-Tree
├── CREATE INDEX：索引B-Tree
└── FOREIGN KEY：建议创建索引

索引B-Tree结构：
键 = 索引列值 + ROWID
值 = 无（仅存储键）

索引存储开销：
├── 索引键大小
├── 页面头开销
├── 指针开销
└── 约10-20%原始数据大小

索引深度计算：
深度 ≈ logN(记录数)
N = 页面容量 ≈ 页面大小 / (键大小 + 指针大小)

示例：
页面大小：4096字节
键大小：20字节
指针大小：8字节
N ≈ 4096 / 28 ≈ 146

100万记录：深度 ≈ log146(1000000) ≈ 3层
1亿记录：深度 ≈ log146(100000000) ≈ 4层

查看数据库基本信息：
$ sqlite3 /sqlite/fgdata/fgedudb.db
SQLite version 3.45.2 2024-03-12 11:06:23
Enter “.help” for usage hints.

sqlite> .dbinfo
database page size: 4096
write format: 1
read format: 1
reserved bytes: 0
max page count: 1073741823
current page count: 128
freelist page count: 0
schema cookie: 1
schema format: 4
default cache size: 0
autovacuum top: 0
text encoding: 1 (utf-8)
user version: 0
application id: 0
software version: 3045002
number of tables: 5
number of indexes: 3
number of triggers: 0
database size in pages: 128
database size in bytes: 524288

查看页面大小：
sqlite> PRAGMA page_size;
page_size
———
4096

查看数据库大小：
sqlite> PRAGMA page_count;
page_count
———-
128

sqlite> SELECT (page_count * page_size) / 1024.0 / 1024.0 as size_mb
…> FROM pragma_page_count, pragma_page_size;
size_mb
——-
0.5

查看表和索引信息：
sqlite> SELECT name, type, tbl_name, rootpage
…> FROM sqlite_master
…> WHERE type IN (‘table’, ‘index’);
name type tbl_name rootpage
————- —– ————- ——–
fgedu_users table fgedu_users 2
fgedu_orders table fgedu_orders 3
fgedu_products table fgedu_products 4
idx_users_email index fgedu_users 5

查看表空间使用：
sqlite> SELECT
…> name,
…> SUM(pgsize) / 1024.0 as size_kb,
…> COUNT(*) as page_count
…> FROM dbstat
…> GROUP BY name
…> ORDER BY size_kb DESC;
name size_kb page_count
————- ——- ———-
fgedu_users 32.0 8
fgedu_orders 24.0 6
fgedu_products 16.0 4
idx_users_email 8.0 2

查看自由块信息：
sqlite> PRAGMA freelist_count;
freelist_count
————–
0

查看数据库完整性：
sqlite> PRAGMA integrity_check;
integrity_check
—————
ok

创建新数据库并设置页面大小：
$ sqlite3 /sqlite/fgdata/fgedudb_8k.db
SQLite version 3.45.2 2024-03-12 11:06:23
Enter “.help” for usage hints.

sqlite> PRAGMA page_size = 8192;

sqlite> CREATE TABLE fgedu_test (
…> id INTEGER PRIMARY KEY,
…> name TEXT,
…> value REAL
…> );

验证页面大小：
sqlite> PRAGMA page_size;
page_size
———
8192

插入测试数据：
sqlite> INSERT INTO fgedu_test (name, value)
…> WITH RECURSIVE cnt(x) AS (
…> SELECT 1 UNION ALL SELECT x+1 FROM cnt WHERE x < 10000 ...> )
…> SELECT ‘user’ || x, x * 1.0 FROM cnt;

查看数据库大小：
sqlite> PRAGMA page_count;
page_count
———-
16

sqlite> SELECT (page_count * page_size) / 1024.0 as size_kb
…> FROM pragma_page_count, pragma_page_size;
size_kb
——-
128.0

查看当前缓存配置：
sqlite> PRAGMA cache_size;
cache_size
———-
-2000

设置缓存大小：
— 设置为64MB（负数表示KB）
sqlite> PRAGMA cache_size = -64000;

sqlite> PRAGMA cache_size;
cache_size
———-
-64000

查看缓存使用情况：
sqlite> PRAGMA cache_status;
cache_size lookaside cache_used schema_used
———- ——— ———- ———–
-64000 0 262144 8192

临时存储配置：
sqlite> PRAGMA temp_store;
temp_store
———-
0

sqlite> PRAGMA temp_store = MEMORY;

sqlite> PRAGMA temp_store;
temp_store
———-
2

查看表的B-Tree信息：
sqlite> SELECT
…> m.name as table_name,
…> m.rootpage,
…> SUM(d.pgsize) / 1024.0 as size_kb,
…> COUNT(DISTINCT d.pgno) as page_count
…> FROM sqlite_master m
…> LEFT JOIN dbstat d ON m.name = d.name
…> WHERE m.type = ‘table’
…> GROUP BY m.name;
table_name rootpage size_kb page_count
————- ——– ——- ———-
fgedu_users 2 32.0 8
fgedu_orders 3 24.0 6
fgedu_products 4 16.0 4

分析索引B-Tree：
sqlite> EXPLAIN QUERY PLAN SELECT * FROM fgedu_users WHERE email = ‘test@fgedu.net.cn’;
QUERY PLAN
`–SEARCH fgedu_users USING INDEX idx_users_email (email=?)

查看索引层级：
sqlite> SELECT
…> name,
…> COUNT(*) as depth_estimate
…> FROM dbstat
…> WHERE name LIKE ‘idx_%’
…> GROUP BY name;
name depth_estimate
————— ————–
idx_users_email 2

重建表（优化B-Tree碎片）：
sqlite> VACUUM;

更新统计信息：
sqlite> ANALYZE;

查看优化效果：
sqlite> PRAGMA integrity_check;
integrity_check
—————
ok

sqlite> SELECT
…> name,
…> SUM(pgsize) / 1024.0 as size_kb
…> FROM dbstat
…> GROUP BY name
…> ORDER BY size_kb DESC;
name size_kb
————- ——-
fgedu_users 28.0
fgedu_orders 22.0
fgedu_products 14.0

优化索引：
sqlite> REINDEX;

sqlite> EXPLAIN QUERY PLAN SELECT * FROM fgedu_users WHERE email = ‘test@fgedu.net.cn’;
QUERY PLAN
`–SEARCH fgedu_users USING INDEX idx_users_email (email=?)

在线备份：
sqlite> .backup /sqlite/backup/fgedudb_backup.db

sqlite> .databases
main: /sqlite/fgdata/fgedudb.db r/w

验证备份：
$ sqlite3 /sqlite/backup/fgedudb_backup.db “PRAGMA integrity_check;”
ok

$ sqlite3 /sqlite/backup/fgedudb_backup.db “SELECT COUNT(*) FROM fgedu_users;”
COUNT(*)
——–
10004

文件级备份：
$ cp /sqlite/fgdata/fgedudb.db /sqlite/backup/fgedudb_$(date +%Y%m%d).db

$ ls -lh /sqlite/backup/
total 1.5M
-rw-r–r– 1 sqlite sqlite 512K Apr 8 10:30 fgedudb_20240408.db
-rw-r–r– 1 sqlite sqlite 512K Apr 8 10:30 fgedudb_backup.db

恢复数据库：
$ cp /sqlite/backup/fgedudb_20240408.db /sqlite/fgdata/fgedudb.db

$ sqlite3 /sqlite/fgdata/fgedudb.db “PRAGMA integrity_check;”
ok

检查碎片情况：
sqlite> SELECT
…> name,
…> SUM(pgsize) as total_size,
…> SUM(pgsize) – COUNT(*) * 4096 as fragmentation
…> FROM dbstat
…> GROUP BY name;
name total_size fragmentation
————- ———- ————-
fgedu_users 32768 0
fgedu_orders 24576 0

执行碎片整理：
sqlite> VACUUM;

查看整理后效果：
sqlite> PRAGMA page_count;
page_count
———-
120

sqlite> SELECT (page_count * page_size) / 1024.0 / 1024.0 as size_mb
…> FROM pragma_page_count, pragma_page_size;
size_mb
——-
0.46875

自动清理配置：
sqlite> PRAGMA auto_vacuum;
auto_vacuum
———–
0

sqlite> PRAGMA auto_vacuum = INCREMENTAL;

sqlite> PRAGMA auto_vacuum;
auto_vacuum
———–
2

执行增量清理：
sqlite> PRAGMA incremental_vacuum(100);

创建大表测试：
sqlite> CREATE TABLE fgedu_large_data (
…> id INTEGER PRIMARY KEY,
…> data TEXT,
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP
…> );

插入大量数据：
sqlite> INSERT INTO fgedu_large_data (data)
…> WITH RECURSIVE cnt(x) AS (
…> SELECT 1 UNION ALL SELECT x+1 FROM cnt WHERE x < 100000 ...> )
…> SELECT hex(randomblob(500)) FROM cnt;

查看存储情况：
sqlite> PRAGMA page_count;
page_count
———-
12500

sqlite> SELECT (page_count * page_size) / 1024.0 / 1024.0 as size_mb
…> FROM pragma_page_count, pragma_page_size;
size_mb
——-
48.828125

优化存储：
sqlite> VACUUM;

sqlite> PRAGMA page_count;
page_count
———-
12300

压缩数据：
sqlite> UPDATE fgedu_large_data SET data = NULL WHERE id % 2 = 0;

sqlite> VACUUM;

sqlite> PRAGMA page_count;
page_count
———-
6200

sqlite数据库文件管理规范：

1. 文件命名规范
├── 主数据库：应用名.db
├── 备份文件：应用名_日期.db
└── 临时文件：应用名_temp.db

2. 文件位置规范
├── 主数据库：/sqlite/fgdata/
├── 备份文件：/sqlite/backup/
└── 临时文件：/sqlite/temp/

3. 文件权限规范
├── 数据库文件：644
├── 目录权限：755
└── 所有者：sqlite用户

4. 备份策略规范
├── 全量备份：每日一次
├── 增量备份：每小时一次
└── 异地备份：每周一次

5. 监控规范
├── 文件大小监控
├── 增长速率监控
├── 完整性检查
└── 空间预警

sqlite数据库存储优化技巧：

1. 页面大小优化
├── 根据数据特点选择合适的页面大小
├── 大数据量使用较大页面
└── 小数据量使用默认页面

2. 索引优化
├── 创建必要的索引
├── 删除未使用的索引
├── 使用复合索引
└── 定期执行ANALYZE

3. 碎片整理
├── 定期执行VACUUM
├── 使用自动清理模式
└── 监控碎片率

4. 数据压缩
├── 清理历史数据
├── 压缩大字段
└── 使用BLOB存储二进制

5. WAL模式优化
├── 启用WAL模式
├── 定期执行checkpoint
└── 监控WAL文件大小

sqlite数据库底层原理总结：

文件结构：
├── 单文件存储
├── 固定大小页面
├── B-Tree组织数据
└── 文件头存储元数据

页面机制：
├── 页面是IO的基本单位
├── 页面大小影响性能
├── 页面类型决定用途
└── 页面缓存提升性能

B-Tree机制：
├── 表数据存储在表B-Tree
├── 索引数据存储在索引B-Tree
├── ROWID作为表B-Tree主键
└── B-Tree深度影响查询效率

存储优化：
├── 合理选择页面大小
├── 创建必要的索引
├── 定期维护数据库
└── 监控存储使用情况