清河洛 > 后端服务 > sql学习

Sqlite常用配置

qingheluo2025-06-26清河洛13

Sqlite文件锁和并发Sqlite对数据库文件通过各种锁来控制进程或线程的并行读写，以此实现原子事务UNLOCKED（无锁）：没有锁被持有，这是默认状态 SHARED（共享锁）：正在被读，同时允许任意多个进程持有SHARED锁(这使得Sqlite支持并发读) 当存在SHARED锁处于活动状态时，不允许其他线程或进程向数据库文件写入 RESERVED（保留锁）：表示一个进程准备在未来的某个时间点写入，当前只能从数据库文件读取多个SHARED锁会和一个RESERVED锁同时存在，但是一次只能激活一个RESERVED锁 PENDING（挂起锁）：想向数据库写入，但目前在等...

Sqlite文件锁和并发

Sqlite对数据库文件通过各种锁来控制进程或线程的并行读写，以此实现原子事务

UNLOCKED（无锁）：没有锁被持有，这是默认状态
SHARED（共享锁）：正在被读，同时允许任意多个进程持有SHARED锁(这使得Sqlite支持并发读)
    当存在SHARED锁处于活动状态时，不允许其他线程或进程向数据库文件写入
RESERVED（保留锁）：表示一个进程准备在未来的某个时间点写入，当前只能从数据库文件读取
    多个SHARED锁会和一个RESERVED锁同时存在，但是一次只能激活一个RESERVED锁
PENDING（挂起锁） ：想向数据库写入，但目前在等待所有的SHARED锁被清理
    如果PENDING锁处于活动状态，不允许使用新的SHARED锁，但允许继续使用现有的SHARED锁
EXCLUSIVE（互斥锁）：正在被写，只允许一个EXCLUSIVE锁，且不能合其他任何类型的锁同时存在

数据库Page(页)

在SQLite中，Page是数据库存储的基本单位，是SQLite与底层存储设备进行数据交互的最小粒度，类似与操作系统中的Block(块)

每个Page的大小固定，必须为512-65536之间的2的幂的数，所有数据均以Page为单位进行组织

访问数据时会将对应Page加载到内存缓存中，修改数据后会先在缓存中更新Page，再批量写入磁盘

每个Page包含头部（Header）和数据区（Data Area）

头部存储Page的元信息（如页类型、校验和、空闲列表等）
数据区采用B-tree节点存储，存储内部节点（表记录、索引条目和子Page指针）和叶子节点（存储实际数据行）

Page大小对数据库的影响

空间利用率：小Page适合小记录，避免Page内空间浪费；大Page适合大记录，减少Page拆分开销
内存占用：大Page会占用更多缓存空间，但可以提升缓存命中率
I/O 效率：大Page能减少磁盘I/O次数，适合顺序读取场景，小Page更适合随机访问
查询效率：大Page使得B-tree能容纳更多键值，减少树的高度，从而加速查询

Page大小建议与操作系统文件系统的Block(块)大小对齐，避免跨块读写带来的性能损耗

Sqlite会在内部利用局部性原理（相邻Page的数据可能被连续访问）自动预读相邻Page到缓存，从而提升性能

一旦数据库创建后，Page大小不可修改，但可以通过备份还原或VACUUM操作重建数据库

VACUUM操作

VACUUM操作用来重建数据库，其操作的流程为

1、创建临时数据库    创建一个新的临时数据库文件
2、复制有效数据      将原数据库中的所有有效数据复制到临时数据库中
3、删除原数据库      删除原数据库文件
4、重命名临时数据库  将临时数据库文件重命名为原数据库文件名

VACUUM操作的作用

1、回收磁盘空间
    当删除数据时，SQLite仅将数据标记为 “已删除”，不会释放对应的磁盘空间
    这些被标记的空间会被记录在数据库的空闲Page链表中，供后续插入操作重复使用
    VACUUM通过重建数据库文件，移除已删除数据占用的空闲Page，减小文件体积
2、整理碎片化数据
    频繁增删改会导致数据分散存储，形成碎片，增加查询时磁盘 I/O 次数
    VACUUM将数据Page连续存储，消除碎片，使存储更加紧凑，从而提高数据读取效率
3、优化数据库性能
    整理碎片化后，索引的指向更加准确，查找效率也会相应提高
3、重置自动增长计数器
    Sqlite会自动维护一个rowid字段并赋予AUTOINCREMENT约束
    自动维护记录已使用的最大值的计数器，即使删除了最大值的记录，该计数器不会自动减少
    导致后续插入的记录使用比实际需要更大的主键值，也导致了该记录的不连续
    VACUUM会重置并重新计算AUTOINCREMENT计数器，使其基于当前数据库中的最大值，避免主键值浪费
4、配置项生效
    在数据库使用过程中，使用PRAGMA命令修改了某些配置项的值，如
    修改了Page大小、日志模式等

ANALYZE操作

ANALYZE操作用于收集数据库表和索引的统计信息，以帮助查询优化器生成更高效的执行计划，其操作的逻辑为

1、数据采样与扫描
    扫描表中约20%的数据，可以通过系统变量 ANALYZE_SAMPLE_SIZE 调整采样比例
    也可以通过analysis_limit限制扫描行数
2、统计信息存储：
    统计结果存储在内部表sqlite_stat1，包含行数、索引列的唯一值分布等信息
    若启用SQLITE_ENABLE_STAT3/4编译选项，还会生成sqlite_stat3/4表（包含值分布的直方图数据）
3、执行范围
    ANALYZE     分析整个数据库。
    ANALYZE database_name   分析指定数据库
    ANALYZE table_name      仅分析特定表及其索引
    ANALYZE index_name      仅分析指定索引
4、并发与锁
    需要写访问权限，执行时会获取排他锁，阻塞其他写操作（但允许读操作）

ANALYZE操作的作用

1、优化查询性能
    通过收集数据分布、行数、列唯一值数量等统计信息，查询优化器可以估算不同执行路径的成本，选择最优的索引或连接顺序
2、维护统计信息准确性
    当数据分布发生显著变化（如大量增删改数据）时，更新统计信息以避免优化器基于过时数据生成低效计划

ANALYZE操作一般使用时机

1、数据分布变化后
    在执行大量插入、删除或更新操作后运行，确保统计信息反映最新数据分布
2、创建新索引后
    新建索引后运行，使优化器了解索引的选择性和分布特性
3、性能调优
    当查询性能下降且怀疑执行计划不合理时，更新统计信息可能改善性能
4、数据库维护任务
    作为定期维护的一部分，确保统计信息长期准确

ANALYZE操作注意事项

1、性能开销：对大型表或启用STAT3/4时，分析可能耗时较长，建议在低峰期执行
2、持久化存储：统计信息直接写入数据库文件，重启后依然有效
3、与VACUUM的区别：VACUUM重组数据库文件并释放未使用空间，但不会更新统计信息
4、手动编辑统计表：虽然可手动修改sqlite_stat*表，但通常不推荐，除非有特殊需求

PRAGMA语句

PRAGMA语句是特定于SQLite的扩展，用于修改SQLite库的操作方式或查询SQLite库的内部（非表）数据

PRAGMA function[(args)];
    // 执行某个操作函数
    // 当不传入参数时括号必须省略
PRAGMA pragma_name;
    // 查询当前状态下某个配置项的值
PRAGMA pragma_name = value;
    // 修改某个配置项的值

几乎所有的配置项均为连接配置项，仅影响当前连接，不会影响其他数据库连接，并且当前连接断开后重新连接会恢复默认值

永久配置项

user_version
schema_version
page_size，尽在创建数据库时可以设置，已创建的数据库无法修改
journal_mode的wal值，其他值为连接配置项

当执行的操作有返回结果且没有副作用时，可以使用select语句获取相同的结果，from的对象为操作名加上"pragma_"前缀，如database_list对应pragma_database_list

PRAGMA database_list;

select * from pragma_database_list

查看信息

database_list           列出当前所有的数据库连接
freelist_count          被标记为可回收数据的数量
encoding                字符串如何编码，一旦创建后该值无法被修改
page_count              当前数据库中的Page数量
compile_options         查看Sqlite在编译时使用的编译选项
function_list           查看所有支持的函数列表
pragma_list             获取所有支持的PRAGMA命令列表
                        某些命令会由于编译参数改变而不一定可用
index_info(index_name)  数据库索引的信息
index_list(table_name)  所有与表相关联的索引

user_version            int，存储在数据库头的用户自定义的版本值
        一个32位的有符号整数值，由开发人员设置，用于版本跟踪的目的

schema_version          存储在数据库头中的的数据库架构改变版本(次数)
        每当一个架构改变命令(如CREATE、DROP语句)执行后，这个值会递增
        该值由SQLite自动管理，不推荐手动设置值，除非特殊需求
        VACUUM操作被视为架构更改，因为VACUUM通常会更改sqlite_schema表中条目的rootpage值

data_version            返回一个int整数，表示数据库内容版本号
        用于检测数据库内容或配置是否被其他连接修改
        当其他连接修改了数据库的内容或配置时，该连接获取的版本号会增加
        该连接本身修改了数据库内容或配置不会导致版本号增加

table_info(table_name)    获取指定表的各字段信息
        返回信息：name，type，notnull，dflt_value(默认值)，pk(是否主键)

table_list[(table_name)]  获取指定表的信息，不指定参数返回所有表
        schema  表或视图的模式(如“main”或“temp”）
        name    表或视图的名称
        type    类型，table、view、shadow、virtual
        ncol    表中列的数量，包括生成列和隐藏列
        wr      是否为WITHOUT ROWID表
        strict  是否为STRICT表

        默认每个表的都有一个特殊的列 rowid，由Sqlite自动维护，类似于自增索引
        当创建表时没有定义主键时会以该列为主键进行搜索优化
        当创建表时定义了主键时会以定义的主键进行搜索优化，但仍然会维护rowid，此时rowid没有起到作用，反而会增加额外的性能损耗和空间浪费
        创建表时在最后标注WITHOUT ROWID将不会额外维护rowid列，但是要求创建表时必须设有主键，否则会报错
        CREATE TABLE IF NOT EXISTS wordcount(
          word TEXT PRIMARY KEY,
          cnt INTEGER
        ) WITHOUT ROWID;

        STRICT表：表示严格数据类型的表
        Sqlite默认对数据类型没有严格限制，字段数据类型可以随意编写也不会报错，且传入的数据类型不对时也不会报错
        如一个INTEGER列，当传入'123'时会自动转换为整数，不能转换为整数会以字符串形式存储
        开启严格模式后创建表的时候只能使用Sqlite支持的数据类型，INT、INTEGER、REAL、TEXT、BLOB、ANY，并且传入的值如果不符合数据类型会报错
        在严格模式下，增加了ANY数据类型，可以传入任意数据类型，并且不会进行任何的类型转换
        创建表时在最后标注STRICT表时该表为开启严格数据类型的表
        CREATE TABLE t1(a ANY) STRICT;

数据库设置

query_only     bool，是否以只读模式打开数据库
        开启时所有改变数据的操作会导致SQLITE_READONLY错误
        但并非真正的只读，仍然可以运行WAL数据同步或COMMIT

ignore_check_constraints    bool(false)  是否忽略CHECK约束
        CREATE TABLE users ( age INTEGER CHECK(age >= 0) )

recursive_triggers  bool(false)，是否启用递归触发器功能，默认一个触发动作不会触发另一个触发

secure_delete       bool(false)|fase，是否启用安全删除模式
    默认删除数据时，仅将数据标记为“已删除”，不会从磁盘文件中物理移除，这些数据存在潜在的数据泄露风险
    开启后删除数据时，会用零覆盖被删除的数据存储区域，确保数据无法通过某些手段恢复
    关闭可以减少CPU周期数和磁盘 I/O 数来提高性能
    特殊的值fast，是介于“开”和“关”的中间设置，在不增加I/O量但使用更多的 CPU 周期的情况下使用零覆盖已删除的内容，但会在已删Page上留下一些遗留痕迹

busy_timeout         int(毫秒)，读取或写入的超时时间(SQLITE_BUSY报错)
    数据库另一个进程正在写入数据时
    WAL模式下正在进行数据库同步时
    某些语句执行时，如VACUUM、ALTER TABLE 或 PRAGMA journal_mode
    等等
    此时如果进行数据库读取或写入会进行排队等待，超过指定时长后返回SQLITE_BUSY报错

reverse_unordered_selects  bool(false)   开启反转无ORDER BY查询的结果顺序
    在查询语句中，无ORDER BY默认可能按底层存储顺序（如rowid升序）返回数据
    但这是不可靠的，可能受底层存储结构、索引、查询计划等影响而变化
    如果代码的逻辑默认无ORDER BY查询的返回顺序是确定的，并在后续逻辑中使用了该顺序
    那么可能导致BUG，该配置项主要用于改变默认的无ORDER BY查询排序结果来提前暴漏可能的bug
    督促开发者显示添加ORDER BY保证稳定性

Page相关

page_size           int，获取或设置Page大小，单位字节
    只有在创建数据库时可以设置
    已经创建的数据库无法设置
max_page_count      int，允许的最大Page数，实测无法更改
cache_size          int，在内存中缓存的Page最大量，默认2000
        为负时会根据当前使用量动态调整大小

内存相关

mmap_size       int(字节)，设置内存映射 I/O 的最大字节数
    默认情况下内存映射 I/O 是关闭的，也就是说 mmap_size 的默认值是 0
    合适的值可以提高性能，特别是对于大型数据库和大型查询
cache_spill   bool(true) | int   缓存溢出行为
    在执行复杂查询或事务时需在内存中缓存中间结果
    当缓存中间结果的数据量超过cache_size会部分溢出(spill)到临时磁盘文件
    启用会增加磁盘I/O降低性能，但可以避免因缓存不足导致SQLITE_NOMEM错误
    当值为int时，表示缓存Page同时超过该值和cache_size时才会触发缓存溢出

soft_heap_limit   建议堆内存的大小，会尝试不超过该值，但可以临时超出
hard_heap_limit   限制顿内存的大小，不允许超出 ，否则返回SQLITE_NOMEM
    只有在编译时启用了SQLITE_ENABLE_MEMORY_MANAGEMENT选项时才有效。否则返回0

日志模式

为了事务的原子性，在执行一个事务时，会先将执行的信息保留至日志中，后面根据事务的执行是否成功来决定是否同步到数据库文件中

日志模式用于控制在进行了事务提交时的日志信息保留方式，用于突发事件(如系统崩溃、断电等)后的数据恢复

journal_mode       获取或设置回滚日志模式，用于在事务错误时的数据回滚
    DELETE    默认，日志文件在每个事务结束时被删除
    TRUNCATE  在事务结束时，日志文件被截断为零长度，由于截断比删除更快，所以性能比DELETE稍好一些
    PERSIST   在事务结束时日志文件不会被删除或截断，头部会被重写，表明日志不再有效
              重写头部比删除或截断文件更快，所以性能比TRUNCATE稍好一些，但可能会影响secure_delete的设置
    MEMORY    日志文件被存储在内存中，这种模式性能最好，但是如果系统崩溃，所有未提交的事务都会丢失
    WAL       Write-Ahead Logging模式，所有的更改首先被写入到一个单独的日志文件（WAL文件）
              在一定的时机一次性将WAL文件中的记录所有更改写入数据库中
              这种模式提供了最好的并发性能
    OFF       不保留任何日志记录
    仅WAL为永久配置项，其余为连接配置项

journal_size_limit   int，在PERSIST或WAL模式下日志文件大小的最大字节数
    当日志体积超过该值会截断为该大小
    默认为-1表示无限制
    设为0表示尽可能减少日志文件体积

wal_autocheckpoint   int，WAL文件写入数据库的Page数量阈值
    当WAL文件大小超过指定的Page数量后会将数据同步到数据库文件中
    如果存在未完成的事务，那么即使WAL大小达到阈值也不会执行数据同步

Sqlite本身不支持并行读写的，但是在开启了WAL日志模式后可以支持并行读写

1、开始写的时候，会在WAL最后的位置标注一个检查点，并在这个检查点之后开始写入
2、开始读的时候，会首先查看WAL文件，查找是否有写操作标注的检查点
    如果有，从该检查点向前搜索
    如果没有，从WAL末尾向前搜索
3、如果找到则返回，未找到会在数据库主文件中查找

这样保证了并行的读写操作，在写入没有提交事务之前无法获取到该数据

开启了WAL模式后，每次操作记录无论是否成功都会在WAL文件中记录，这样本身就避免了系统崩溃或电源故障时可能会导致数据库损坏或数据丢失，所以一般开启了WAL模式需要设置synchronous的值为MORMAL，避免为FULL时每次事务后都需要额外的数据同步带来的性能消耗

磁盘同步

synchronous     磁盘的同步模式，控制如何将数据写入物理存储
    0 | OFF     关闭同步。不会等待将数据写入磁盘。性能最高，但在系统崩溃或电源故障时，可能导致数据库损坏或数据丢失
    1 | NORMAL  普通同步。会在某些关键操作时等待将数据写入磁盘。性能较好，但在某些情况下，仍然可能导致数据库损坏
    2 | FULL    完全同步（默认）。会在每个关键操作时等待将数据写入磁盘。数据的持久性得到了保证，但性能可能较低
    3 | EXTRA   额外同步。类似于FULL，会在某些额外操作时进行同步，提供更高的数据持久性保证。性能可能会进一步降低。

数据库错误检查

cell_size_check   bool(false)  开启自动检查B树每个节点大小是否符合Page大小的限制
    当从磁盘读取数据库文件时和插入或更新数据时都会自动检查
    开启后会降低数据库性能，但能避免数据损坏
    不开启该项时可以手动运行integrity_check来手动检查包括该项的数据库错误

查询优化

analysis_limit      int
    限制ANALYZE命令在分析索引时扫描的最大Page数
    从而在性能和统计信息准确性之间取得平衡
    如果未设置或为 -1，会扫描尽可能多的页以获得更准确的统计信息
    设置为正整数 N 时，在分析每个索引时最多只扫描 N 个Page
    该设置仅影响当前数据库连接的ANALYZE操作

函数相关

optimize([MARK])     执行自动优化查询性能操作
    通过智能分析数据库自上次分析以来发生显著数据变化的表
    为这些表执行ANALYZE命令确保查询优化器基于最新数据生成执行计划
    手动ANALYZE需指定表名或全库扫描，效率较低
    optimize更智能，仅分析变化显著的表
    并且可在事务内执行，但若事务回滚，统计信息更新也会撤销

    可选的参数为位掩码：
    0x00001 调试模式，不实际执行任何优化，而是为每个优化返回一行文本
    0x00002 默认启用，对可能受益的表运行ANALYZE
    0x00010 默认启用，运行ANALYZE时临时设置analysis_limit以防止过多的运行时间
    0x10000 检查所有表的大小，在数据量大小允许的情况下建议使用此掩码位

    要查看所有未实际执行的优化，传入-1：optimize(-1)

磁盘空间优化

auto_vacuum ：自动回收删除的数据所占用的磁盘空间，默认关闭，可选值：
    0 | none        关闭自动回收
    1 | full        启用全量回收，每次删除数据后都会尝试回收
    2 | incremental 启用增量回收，每次删除数据后会将删除数据所在空间标记为可回收，但不会执行回收
        需要手动执行"PRAGMA incremental_vacuum;"来回收
    自动回收仅会截断空闲列表页面，不会进行碎片整理，实际上会使碎片更严重