SQLite部分特点与功能集
2015-09-01 11:46:25 
阿炯
事务与锁
运维核心命令PRAGMA
对线程安全和并发支持
事务与锁
SQLite是一款轻型的遵守ACID的嵌入式关系型数据库管理系统。Android和iOS的设备内置的都是SQLite数据库，支持事务和多数的SQL92标准。本文着重介绍3.7.0版本后的SQLite的新特性：事务与锁。
主要缺点
SQLite 只提供数据库级的锁定，所以不支持高并发。
不支持存储过程。
SQLite 没有用户帐户概念，而是根据文件系统确定所有数据库的权限。这会使强制执行存储配额发生困难，强制执行用户许可变得不可能。
事务与锁(< 3.7.0)
SQLite的事务和锁是很重要的概念。
锁
SQLite有5个不同的锁状态
UNLOCKED(未加锁)
SHARED(共享)
RESERVED(保留)
PENDING(未决)
EXCLUSIVE(排它)
SQLite有一个加锁表，记录数据库连接的锁状态。每个数据库连接在同一时刻只能处于其中一个锁状态，每种状态(UNLOCKED)都有一种锁与之对应。
读
数据库连接最初处于UNLOCKED状态，在此状态下，连接还没有存取数据库。当连接到了一个数据库，甚至已经用BEGIN开始了一个事务时，连接都还处于UNLOCKED状态。为了能够从数据库中读取数据，连接必须必须进入SHARED状态，也就是说首先要获得一个SHARED锁。多个连接可以同时获得并保持共享锁，也就是说多个连接可以同时从同一个数据库中读数据，SQLite是支持并发读取数据的。
写
一个连接想要写数据库，它必须首先获得一个RESERVED锁。一个数据库上同时只能有一个RESERVED锁，保留锁可以与共享锁共存，RESERVED锁即不阻止其它拥有SHARED锁的连接继续读数据库，也不阻止其它连接获得新的SHARED锁。
一旦一个连接获得了RESERVED锁，它就可以将数据写入缓冲区，而不是实际地写到磁盘。当连接想要提交修改(或事务)时，需要获得PENDING锁，之后连接就不能再获得新的SHARED锁了，但已经拥有SHARED锁的连接仍然可以继续正常读数据库。当所有其它SHARED锁都被释放时，拥有PENDING锁的连接就可以将其锁提升至EXCLUSIVE锁，此时就可以将以前对缓冲区所做的修改写到数据库文件。所以SQLite是不支持并发写的。
事务
SQLite有三种不同的事务
DEFERRED(推迟)
MMEDIATE(立即)
EXCLUSIVE(排它)
事务类型在BEGIN命令中指定：
DEFERRED
一个DEFERRED事务不获取任何锁(直到它需要锁的时候)，BEGIN语句本身也不会做什么事情——它开始于UNLOCK状态。默认情况下就是这样的，如果仅仅用BEGIN开始一个事务，那么事务就是DEFERRED的，同时它不会获取任何锁；当对数据库进行第一次读操作时，它会获取SHARED锁；同样，当进行第一次写操作时，它会获取RESERVED锁。
MMEDIATE
由BEGIN开始的IMMEDIATE事务会尝试获取RESERVED锁。如果成功，BEGIN IMMEDIATE保证没有别的连接可以写数据库。但是，别的连接可以对数据库进行读操作；但是，RESERVED锁会阻止其它连接的BEGIN IMMEDIATE或者BEGIN EXCLUSIVE命令，当其它连接执行上述命令时，会返回SQLITE_BUSY错误。这时你就可以对数据库进行修改操作了，但是你还不能提交，当你COMMIT时，会返回SQLITE_BUSY错误，这意味着还有其它的读事务没有完成，得等它们执行完后才能提交事务。
EXCLUSIVE
EXCLUSIVE事务会试着获取对数据库的EXCLUSIVE锁。这与IMMEDIATE类似，但是一旦成功，EXCLUSIVE事务保证没有其它的连接，所以就可对数据库进行读写操作了。
死锁
如果两个以BEGIN DEFERRED开始事务的连接都处于SHARED状态，并且都在等待对方结束SHARED从而进入RESERVED的话，就会进入死锁状态。所以BEGIN DEFERRED开始的事务是有可能产生死锁的。
Write-Ahead Logging ( >=3.7.0 )
SQLite 3.7.0之前是不支持写的时候读得。为了能够读得时候写，引入了Write-Ahead Logging(WAL)机制，这样可以支持一个写和多个读并发。
在引入WAL机制之前，SQLite使用rollback journal机制实现原子事务。
rollback journal机制的原理是：在修改数据库文件中的数据之前，先将修改所在分页中的数据备份在另外一个地方，然后才将修改写入到数据库文件中；如果事务失败，则将备份数据拷贝回来，撤销修改；如果事务成功，则删除备份数据，提交修改。
WAL机制的原理是：修改并不直接写入到数据库文件中，而是写入到另外一个称为WAL的文件中；如果事务失败，WAL中的记录会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。
同步WAL文件和数据库文件的行为被称为checkpoint(检查点)，它由SQLite自动执行，默认是在WAL文件积累到1000页修改的时候；当然在适当的时候，也可以手动执行checkpoint，SQLite提供了相关的接口。执行checkpoint之后，WAL文件会被清空。
在读的时候，SQLite将在WAL文件中搜索，找到最后一个写入点，记住它，并忽略在此之后的写入点(这保证了读写和读读可以并行执行)；随后，它确定所要读的数据所在页是否在WAL文件中，如果在，则读WAL文件中的数据，如果不在，则直接读数据库文件中的数据。
在写的时候，SQLite将之写入到WAL文件中即可，但是必须保证独占写入，因此写写之间不能并行执行。WAL在实现的过程中，使用了共享内存技术，因此，所有的读写进程必须在同一个机器上，否则，无法保证数据一致性。
优点
读和写可以完全地并发执行，不会互相阻塞(但是写之间仍然不能并发)。
WAL在大多数情况下，拥有更好的性能(因为无需每次写入时都要写两个文件)。
磁盘I/O行为更容易被预测
缺点
访问数据库的所有程序必须在同一主机上，且支持共享内存技术。
每个数据库现在对应3个文件：.db，-wal，-shm。
当写入数据达到GB级的时候，数据库性能将下降。
3.7.0之前的SQLite无法识别启用了WAL机制的数据库文件。
WAL引入的兼容性问题。在启用了WAL之后，数据库文件格式的版本号由1升级到了2，因此，3.7.0之前的SQLite无法识别启用了WAL机制的数据库文件。禁用WAL会使数据库文件格式的版本号恢复到1，从而可以被SQLite 3.7.0之前的版本识别。
WAL引入的性能问题。在一般情况下，WAL会提高SQLite的事务性能；但是在某些极端情况下，却会导致SQLite事务性能的下降。
在事务执行时间较长或者要修改的数据量达到GB级的时候，WAL文件会被占用，它会暂时阻止checkpoint的执行(checkpoint会清空WAL文件)，这将导致WAL文件变得很大，增加寻址时间，最终导致读写性能的下降。
当checkpoint执行的时候，会降低当时的读写性能，因此WAL可能会导致周期性的性能下降。
运维核心命令PRAGMA
SQLLite是一个小型的数据库产品，但是其中的运维命令并不像想象的简单，这里核心的运维命令是PRAGMA，该语句是特定于 SQLite 的 SQL 扩展，用于修改 SQLite 库的操作或查询 SQLite 库的内部（非表）数据。PRAGMA 语句使用与其他 SQLite 命令。这里有一个注意的地方，也就是PRAGMA命令本身，会进行更迭，同时在系统中使用错误的PRAGMA命令不会报错，这点是需要注意的。
PRAGMA 命令执行计划图
下面以数据库运维中的重要性和系统性能的重要性来进行关键命令的描述
synchronous 这里有一个表，关于sqlite3的性能和数据安全性之间的比率
这里在进入SQLite的时候需要考虑你的SQLite适用于哪种模型
# sqlite3 test.db
SQLite version 3.47.1 2024-11-25 12:07:48
Enter ".help" for usage hints.
sqlite>
sqlite>
sqlite> PRAGMA synchronous = FULL;
sqlite>
sqlite> PRAGMA synchronous;
2
sqlite>
关键业务推荐FULL模式。
WAL日志刷新是SQLite一个关键的数据库运维项目，这里我们可以管控的是在断电后，到底需要多长时间数据库可以恢复工作的问题，这里就牵扯我们之前学习到了SQLlite的工作模式，必须是wal 然后我们的数据刷新上面提到的同步模式要是full 那么现在我们要决定的是数据页面的刷新率，也就是我们产生多少数据页面后，进行数据页面刷新的问题。
# sqlite3 test.db
SQLite version 3.47.1 2024-11-25 12:07:48
Enter ".help"for usage hints.
sqlite>
sqlite> PRAGMA synchronous = FULL;
sqlite> PRAGMA synchronous;
2
sqlite>
sqlite> PRAGMA wal_autocheckpoint;
1000
sqlite> PRAGMA wal_autocheckpoint = 200;
200
sqlite> PRAGMA wal_autocheckpoint;
200
sqlite> PRAGMA wal_checkpoint;
0|10179839|10179839
sqlite> PRAGMA wal_checkpoint(TRUNCATE);
0|0|0
sqlite> PRAGMA wal_autocheckpoint;
1000
sqlite> PRAGMA wal_checkpoint;
0|0|0
要注意几个命令
PRAGMA autocheckpoint； 这个命令查看当前数据页面多少，才产生一次checkpoint 的工作。键入命令后会显示当前的配置是多少。
如果对数据页面触发值想进行改动，可以使用 PRAGMA wal_autocheckpint = 200; 这个意思是产生了200个页面就进行数据的刷新到磁盘的工作。
同时需要注意每次启动数据库都需要带有一个配置的文件的脚本，来加载配置否则SQLlite是不会加载正确的预设配置给你。
所以程序在启动SQLite的时候，应该将这些写入，或者写一个脚本如下
PRAGMA journal_mode = WAL;
PRAGMA synchronous = FULL;
PRAGMA wal_autocheckpoint = 200;
把这些存储在一个.sql文件中，然后启动的时候，但在操作中我发现了一个新的问题，关于一些配置可以固化，一些不可以，这里总结如下。
SQLite PRAGMA 持久化 vs 非持久化
一、持久化 PRAGMA（Persistent）
这些 PRAGMA 会写入数据库文件，对所有连接永久生效。
Sqlite有两种模式，rollback-journal 和 wal 模式，可通过命令PRAGMA journal_mode = XXX; 来控制。
这两个模式的区别：rollback-journal 模式会产生读和写的库锁，没有看错，是库锁；wal 模式会产生多读、并发，和多写的库锁。
二、会话级 PRAGMA（Non-persistent）
这些只影响当前 连接 或 事务，不会写入数据库文件。
一个库或者一个SQLite的数据库要进行哪些初始化设置
对新库的初始化包含了如下的设置：
1.数据库存储文本的编码
2.数据库页面的大小
3.设置文件身份标识
4.设置数据文件的版本
5.控制SQLite是否进行磁盘空间的回收
6.设置数据库运行的模式
下面是这六项，SQLite在LINUX和WINDOWS中的比对如下：
在 Linux 与 Windows 下的关键 PRAGMA 差异说明
1.数据库存储文本的编码（PRAGMA encoding）
结论： SQLite 行为完全一致，主要差异来自 Windows 终端默认不是 UTF-8。
2.数据库页面大小（PRAGMA page_size）
结论： 两平台行为一致，4K 页通常最佳。
3.文件身份标识（PRAGMA application_id）
示例：sql PRAGMA application_id = 0x1234ABCD;
下面是这些命令合集，可以将这些进行一个标准化的设置：
-- 1. 设置数据库编码（仅空库且未建表时有效）
PRAGMA encoding = 'UTF-8';
-- 2. 设置数据库页面大小（仅空库有效）
PRAGMA page_size = 4096;
-- 3. 设置 database file 的 application id（应用识别）
PRAGMA application_id = 0x1234ABCD;
-- 4. 设置数据库 schema 版本（供程序使用）
PRAGMA user_version = 20240101;
-- 5. 设置自动回收空间模式（写入数据库文件结构）
-- NONE / FULL / INCREMENTAL PRAGMA auto_vacuum = FULL;
-- 6. 设置日志模式（journal_mode）
-- DELETE / TRUNCATE / PERSIST / MEMORY / WAL PRAGMA journal_mode = WAL;
-- 7. 设置同步级别（磁盘安全等级）
-- OFF / NORMAL / FULL / EXTRA PRAGMA synchronous = NORMAL;
-- 8. WAL 模式下自动 checkpoint 间隔（不持久化，每次连接需设置）
PRAGMA wal_autocheckpoint = 200;
-- 9. 可选：严格表（类似强类型）
-- CREATE TABLE t(a INTEGER, b TEXT) STRICT;
-- 10. 可选：快速收缩空间
-- PRAGMA incremental_vacuum;
对线程安全和并发支持
SQLite支持多线程安全读取吗？
答案是肯定的，其支持多线程安全读取，但需要正确的配置和使用模式。这是一个非常重要且常见的问题。简单回答“是”或“否”都不够准确，关键在于理解其背后的机制。
1. 核心：编译时和运行时的线程模式
SQLite 的线程安全特性取决于两个因素：编译时的设置和运行时的连接模式。
A. 编译时设置 (SQLITE_THREADSAFE)
在编译 SQLite 库时，可以通过 -DSQLITE_THREADSAFE 宏定义其线程安全级别：
1).SQLITE_THREADSAFE=1 (默认值) ：线程安全模式。SQLite 库内部会使用互斥锁来保护核心数据结构，使其可以在多线程环境中安全使用。这是大多数预编译版本采用的模式。
2).SQLITE_THREADSAFE=2：串行化模式。比线程安全更严格，它要求所有对 SQLite 的调用都来自同一个线程，或者应用程序自行处理所有同步。不常用。
3).SQLITE_THREADSAFE=0：单线程模式。所有内部互斥锁都被禁用，库体积更小、运行更快，但绝对不能在多线程中使用。
如何检查你的 SQLite 版本是否线程安全？可以执行以下 SQL 查询：
SELECT sqlite_threadsafe();
1).返回 1 表示是线程安全的（模式1或2）。
2).返回 0 表示是单线程模式，不安全。
B. 运行时连接模式 (sqlite3_open_v2)
即使库本身是线程安全编译的，还需要在打开数据库连接时指定正确的标志。这是通过 sqlite3_open_v2() 函数的第三个参数来设置的。
1).SQLITE_OPEN_NOMUTEX：以此模式打开的连接不会持有互斥锁，因此速度更快。但要求每个连接都只被单个线程使用。如果你在多个线程中使用同一个连接，会导致崩溃或数据错误。
2).SQLITE_OPEN_FULLMUTEX：以此模式打开的连接是“可重入”的，意味着它可以被多个线程安全地共享但一次只能由一个线程使用）。线程需要自己通过互斥锁来协调对连接的访问。
关键点：一个数据库连接 (sqlite3*) 在同一时间只能由一个线程使用。不能让线程A和线程B同时对一个连接执行操作。线程安全指的是多个线程可以同时使用各自独立的数据库连接来读取数据。
2. 多线程读取的最佳实践
对于典型的“多读者，单写者”场景，推荐以下模式：
1).每个线程使用自己独立的数据库连接。
线程A使用连接A，线程B使用连接B。它们可以同时进行读取操作。
绝对不要在线程间共享同一个连接对象。
2).写操作需要序列化。
虽然多个读取可以同时进行，但任何写操作（INSERT, UPDATE, DELETE）或需要写锁的事务（BEGIN IMMEDIATE, BEGIN EXCLUSIVE）都会暂时阻塞其他连接的读写操作，直到写操作完成。
SQLite 使用锁机制（共享锁、保留锁、排他锁）来管理并发，确保数据一致性。
3).启用 WAL 模式（强烈推荐）
WAL (Write-Ahead Logging) 模式是提升 SQLite 并发性能的“神器”。
在 WAL 模式下，读取和写入可以同时发生。读者不会阻塞写者，写者也不会阻塞读者（在大多数情况下）。这极大地提升了多线程读取的性能。
启用方法：
PRAGMA journal_mode=WAL;
SQLite 完全支持安全高效的多线程读取，前提有三：
1).使用的 SQLite 库是线程安全编译的。
2).每个线程使用自己独立的数据库连接。
3).（可选但强烈推荐）启用 WAL 日志模式以最大化并发读取性能。
哪是否能够安全的使用数据库连接池？
可以，但需要非常小心，并且通常不推荐传统的连接池模式。因为连接不是线程安全的。
虽然 SQLite 支持多线程安全读取，但单个数据库连接 (sqlite3\*) 不是线程安全的。这意味着：
1).连接池中的连接可能被多个线程获取和使用；
2).如果两个线程同时使用同一个连接，会导致未定义行为、崩溃或数据损坏；
3).传统的连接池模式（如 MySQL、PostgreSQL）在这里不直接适用。
为什么不推荐复杂的连接池：
1).SQLite 连接本身很轻量 - 创建新连接的代价相对较小；
2).WAL 模式优化 - 启用 WAL 后，读写并发性能很好；
3).简化复杂性 - 避免连接泄漏、状态同步等问题；
4).事务边界 - 每个操作使用独立连接更符合事务的原子性。
对于大多数应用，为每个线程或每个操作创建新连接，配合 WAL 模式，是更简单、更安全的选择。拒绝复杂性。
运维核心命令PRAGMA
对线程安全和并发支持
事务与锁
SQLite是一款轻型的遵守ACID的嵌入式关系型数据库管理系统。Android和iOS的设备内置的都是SQLite数据库，支持事务和多数的SQL92标准。本文着重介绍3.7.0版本后的SQLite的新特性：事务与锁。
主要缺点
SQLite 只提供数据库级的锁定，所以不支持高并发。
不支持存储过程。
SQLite 没有用户帐户概念，而是根据文件系统确定所有数据库的权限。这会使强制执行存储配额发生困难，强制执行用户许可变得不可能。
事务与锁(< 3.7.0)
SQLite的事务和锁是很重要的概念。
锁
SQLite有5个不同的锁状态
UNLOCKED(未加锁)
SHARED(共享)
RESERVED(保留)
PENDING(未决)
EXCLUSIVE(排它)
SQLite有一个加锁表，记录数据库连接的锁状态。每个数据库连接在同一时刻只能处于其中一个锁状态，每种状态(UNLOCKED)都有一种锁与之对应。
读
数据库连接最初处于UNLOCKED状态，在此状态下，连接还没有存取数据库。当连接到了一个数据库，甚至已经用BEGIN开始了一个事务时，连接都还处于UNLOCKED状态。为了能够从数据库中读取数据，连接必须必须进入SHARED状态，也就是说首先要获得一个SHARED锁。多个连接可以同时获得并保持共享锁，也就是说多个连接可以同时从同一个数据库中读数据，SQLite是支持并发读取数据的。
写
一个连接想要写数据库，它必须首先获得一个RESERVED锁。一个数据库上同时只能有一个RESERVED锁，保留锁可以与共享锁共存，RESERVED锁即不阻止其它拥有SHARED锁的连接继续读数据库，也不阻止其它连接获得新的SHARED锁。
一旦一个连接获得了RESERVED锁，它就可以将数据写入缓冲区，而不是实际地写到磁盘。当连接想要提交修改(或事务)时，需要获得PENDING锁，之后连接就不能再获得新的SHARED锁了，但已经拥有SHARED锁的连接仍然可以继续正常读数据库。当所有其它SHARED锁都被释放时，拥有PENDING锁的连接就可以将其锁提升至EXCLUSIVE锁，此时就可以将以前对缓冲区所做的修改写到数据库文件。所以SQLite是不支持并发写的。
事务
SQLite有三种不同的事务
DEFERRED(推迟)
MMEDIATE(立即)
EXCLUSIVE(排它)
事务类型在BEGIN命令中指定：
DEFERRED
一个DEFERRED事务不获取任何锁(直到它需要锁的时候)，BEGIN语句本身也不会做什么事情——它开始于UNLOCK状态。默认情况下就是这样的，如果仅仅用BEGIN开始一个事务，那么事务就是DEFERRED的，同时它不会获取任何锁；当对数据库进行第一次读操作时，它会获取SHARED锁；同样，当进行第一次写操作时，它会获取RESERVED锁。
MMEDIATE
由BEGIN开始的IMMEDIATE事务会尝试获取RESERVED锁。如果成功，BEGIN IMMEDIATE保证没有别的连接可以写数据库。但是，别的连接可以对数据库进行读操作；但是，RESERVED锁会阻止其它连接的BEGIN IMMEDIATE或者BEGIN EXCLUSIVE命令，当其它连接执行上述命令时，会返回SQLITE_BUSY错误。这时你就可以对数据库进行修改操作了，但是你还不能提交，当你COMMIT时，会返回SQLITE_BUSY错误，这意味着还有其它的读事务没有完成，得等它们执行完后才能提交事务。
EXCLUSIVE
EXCLUSIVE事务会试着获取对数据库的EXCLUSIVE锁。这与IMMEDIATE类似，但是一旦成功，EXCLUSIVE事务保证没有其它的连接，所以就可对数据库进行读写操作了。
死锁
如果两个以BEGIN DEFERRED开始事务的连接都处于SHARED状态，并且都在等待对方结束SHARED从而进入RESERVED的话，就会进入死锁状态。所以BEGIN DEFERRED开始的事务是有可能产生死锁的。
Write-Ahead Logging ( >=3.7.0 )
SQLite 3.7.0之前是不支持写的时候读得。为了能够读得时候写，引入了Write-Ahead Logging(WAL)机制，这样可以支持一个写和多个读并发。
在引入WAL机制之前，SQLite使用rollback journal机制实现原子事务。
rollback journal机制的原理是：在修改数据库文件中的数据之前，先将修改所在分页中的数据备份在另外一个地方，然后才将修改写入到数据库文件中；如果事务失败，则将备份数据拷贝回来，撤销修改；如果事务成功，则删除备份数据，提交修改。
WAL机制的原理是：修改并不直接写入到数据库文件中，而是写入到另外一个称为WAL的文件中；如果事务失败，WAL中的记录会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。
同步WAL文件和数据库文件的行为被称为checkpoint(检查点)，它由SQLite自动执行，默认是在WAL文件积累到1000页修改的时候；当然在适当的时候，也可以手动执行checkpoint，SQLite提供了相关的接口。执行checkpoint之后，WAL文件会被清空。
在读的时候，SQLite将在WAL文件中搜索，找到最后一个写入点，记住它，并忽略在此之后的写入点(这保证了读写和读读可以并行执行)；随后，它确定所要读的数据所在页是否在WAL文件中，如果在，则读WAL文件中的数据，如果不在，则直接读数据库文件中的数据。
在写的时候，SQLite将之写入到WAL文件中即可，但是必须保证独占写入，因此写写之间不能并行执行。WAL在实现的过程中，使用了共享内存技术，因此，所有的读写进程必须在同一个机器上，否则，无法保证数据一致性。
优点
读和写可以完全地并发执行，不会互相阻塞(但是写之间仍然不能并发)。
WAL在大多数情况下，拥有更好的性能(因为无需每次写入时都要写两个文件)。
磁盘I/O行为更容易被预测
缺点
访问数据库的所有程序必须在同一主机上，且支持共享内存技术。
每个数据库现在对应3个文件：.db，-wal，-shm。
当写入数据达到GB级的时候，数据库性能将下降。
3.7.0之前的SQLite无法识别启用了WAL机制的数据库文件。
WAL引入的兼容性问题。在启用了WAL之后，数据库文件格式的版本号由1升级到了2，因此，3.7.0之前的SQLite无法识别启用了WAL机制的数据库文件。禁用WAL会使数据库文件格式的版本号恢复到1，从而可以被SQLite 3.7.0之前的版本识别。
WAL引入的性能问题。在一般情况下，WAL会提高SQLite的事务性能；但是在某些极端情况下，却会导致SQLite事务性能的下降。
在事务执行时间较长或者要修改的数据量达到GB级的时候，WAL文件会被占用，它会暂时阻止checkpoint的执行(checkpoint会清空WAL文件)，这将导致WAL文件变得很大，增加寻址时间，最终导致读写性能的下降。
当checkpoint执行的时候，会降低当时的读写性能，因此WAL可能会导致周期性的性能下降。
运维核心命令PRAGMA
SQLLite是一个小型的数据库产品，但是其中的运维命令并不像想象的简单，这里核心的运维命令是PRAGMA，该语句是特定于 SQLite 的 SQL 扩展，用于修改 SQLite 库的操作或查询 SQLite 库的内部（非表）数据。PRAGMA 语句使用与其他 SQLite 命令。这里有一个注意的地方，也就是PRAGMA命令本身，会进行更迭，同时在系统中使用错误的PRAGMA命令不会报错，这点是需要注意的。
PRAGMA 命令执行计划图
下面以数据库运维中的重要性和系统性能的重要性来进行关键命令的描述
synchronous 这里有一个表，关于sqlite3的性能和数据安全性之间的比率
| 模式 | 数值 | 性能 | 安全性 | 特点说明 |
| OFF | 0 | 5星（最快） | 1星（最低） | 不调用 fsync；掉电可能丢大量数据；适合批处理、缓存、不重要数据 |
| NORMAL | 1 | 4星 | 2星 | WAL 层 fsync 延迟到后台；掉电可能丢“最后一次提交”；推荐一般业务 |
| FULL | 2 | 3星 | 4星 | 每次事务提交都会 fsync；传统、稳健；TP 型业务常用 |
| EXTRA | 3 | 2星（最慢） | 5星（最安全） | FULL + metadata fsync；确保文件元数据一致；几乎坚不可摧 |
这里在进入SQLite的时候需要考虑你的SQLite适用于哪种模型
# sqlite3 test.db
SQLite version 3.47.1 2024-11-25 12:07:48
Enter ".help" for usage hints.
sqlite>
sqlite>
sqlite> PRAGMA synchronous = FULL;
sqlite>
sqlite> PRAGMA synchronous;
2
sqlite>
关键业务推荐FULL模式。
WAL日志刷新是SQLite一个关键的数据库运维项目，这里我们可以管控的是在断电后，到底需要多长时间数据库可以恢复工作的问题，这里就牵扯我们之前学习到了SQLlite的工作模式，必须是wal 然后我们的数据刷新上面提到的同步模式要是full 那么现在我们要决定的是数据页面的刷新率，也就是我们产生多少数据页面后，进行数据页面刷新的问题。
# sqlite3 test.db
SQLite version 3.47.1 2024-11-25 12:07:48
Enter ".help"for usage hints.
sqlite>
sqlite> PRAGMA synchronous = FULL;
sqlite> PRAGMA synchronous;
2
sqlite>
sqlite> PRAGMA wal_autocheckpoint;
1000
sqlite> PRAGMA wal_autocheckpoint = 200;
200
sqlite> PRAGMA wal_autocheckpoint;
200
sqlite> PRAGMA wal_checkpoint;
0|10179839|10179839
sqlite> PRAGMA wal_checkpoint(TRUNCATE);
0|0|0
sqlite> PRAGMA wal_autocheckpoint;
1000
sqlite> PRAGMA wal_checkpoint;
0|0|0
要注意几个命令
PRAGMA autocheckpoint； 这个命令查看当前数据页面多少，才产生一次checkpoint 的工作。键入命令后会显示当前的配置是多少。
如果对数据页面触发值想进行改动，可以使用 PRAGMA wal_autocheckpint = 200; 这个意思是产生了200个页面就进行数据的刷新到磁盘的工作。
同时需要注意每次启动数据库都需要带有一个配置的文件的脚本，来加载配置否则SQLlite是不会加载正确的预设配置给你。
所以程序在启动SQLite的时候，应该将这些写入，或者写一个脚本如下
PRAGMA journal_mode = WAL;
PRAGMA synchronous = FULL;
PRAGMA wal_autocheckpoint = 200;
把这些存储在一个.sql文件中，然后启动的时候，但在操作中我发现了一个新的问题，关于一些配置可以固化，一些不可以，这里总结如下。
SQLite PRAGMA 持久化 vs 非持久化
一、持久化 PRAGMA（Persistent）
这些 PRAGMA 会写入数据库文件，对所有连接永久生效。
| PRAGMA | 持久化 | 说明 |
| journal_mode | OK | 会改变数据库日志格式（如 WAL 会生成 -wal/-shm 文件） |
| auto_vacuum | OK | 改变数据库文件结构，需要重写文件 |
| encoding | OK（仅新库） | 写入文件头，建库后不能修改 |
| application_id | OK | 写入文件头 32-bit 标识 |
| page_size | OK | 修改数据库页大小 |
| user_version | OK | 写入文件头，用于 schema 版本控制 |
Sqlite有两种模式，rollback-journal 和 wal 模式，可通过命令PRAGMA journal_mode = XXX; 来控制。
这两个模式的区别：rollback-journal 模式会产生读和写的库锁，没有看错，是库锁；wal 模式会产生多读、并发，和多写的库锁。
二、会话级 PRAGMA（Non-persistent）
这些只影响当前 连接 或 事务，不会写入数据库文件。
| PRAGMA | 持久化 | 说明 |
| synchronous | NOT | 仅本连接的 IO 策略 |
| wal_autocheckpoint | NOT | WAL checkpoint 设置不写入文件 |
| checkpoint_fullfsync | NOT | 仅本连接有效 |
| analysis_limit | NOT | 影响 ANALYZE，本连接有效 |
| cache_size | NOT | 缓存页数，内存设置 |
| cache_spill | NOT | 脏页溢出策略 |
| busy_timeout | NOT | 锁等待时间 |
| automatic_index | NOT | 是否启用自动索引 |
| foreign_keys | NOT | 每次连接都需要重新 ON |
| foreign_key_check | NOT | 检查型，不保存状态 |
| integrity_check | NOT | 检查型，不保存状态 |
| defer_foreign_keys | NOT | 仅当前事务有效 |
| incremental_vacuum | NOT | 动作执行，不改变配置 |
| data_version | NOT | 查询 DB 是否被修改 |
| database_list | NOT | 查询类 |
| index_list / index_info / index_xinfo | NOT | 查询类 |
| 所有 deprecated PRAGMA | NOT | 均不持久 |
一个库或者一个SQLite的数据库要进行哪些初始化设置
对新库的初始化包含了如下的设置：
1.数据库存储文本的编码
2.数据库页面的大小
3.设置文件身份标识
4.设置数据文件的版本
5.控制SQLite是否进行磁盘空间的回收
6.设置数据库运行的模式
下面是这六项，SQLite在LINUX和WINDOWS中的比对如下：
在 Linux 与 Windows 下的关键 PRAGMA 差异说明
1.数据库存储文本的编码（PRAGMA encoding）
| 项目 | Linux | Windows | 说明 |
| 默认编码 | UTF-8 | UTF-8 | SQLite 默认 UTF-8，与 OS 无关 |
| 可设置 | OK 仅空库可设 | OK 仅空库可设 | 只能在创建 DB 后、建表前设 |
| 支持字符集 | UTF-8 / UTF-16le / UTF-16be | 同 Linux | SQLite 不验证字符集合法性 |
| 受系统终端影响 | OK | OK | Windows CMD 默认 GBK，需要 chcp 65001 |
结论： SQLite 行为完全一致，主要差异来自 Windows 终端默认不是 UTF-8。
2.数据库页面大小（PRAGMA page_size）
| 项目 | Linux | Windows | 说明 |
| 默认页大小 | 4096 | 4096 | SQLite 默认值一致 |
| 可设置范围 | 512–65536 | 同 Linux | 必须在建表前设置 |
| 持久化 | OK | OK | 修改文件结构 |
| 性能相关 | EXT4 通常 4K | NTFS 4K | 两平台默认都合适 |
结论： 两平台行为一致，4K 页通常最佳。
3.文件身份标识（PRAGMA application_id）
| 项目 | Linux | Windows | 说明 |
| 支持 | OK | OK | 完全跨平台 |
| 持久化 | OK | OK | 写入数据库头部 4 字节 |
| 用途 | 应用识别数据库类型 | 同 Linux | 类似 meta 信息 |
示例：sql PRAGMA application_id = 0x1234ABCD;
| 项目 | Linux | Windows | 说明 |
| 支持 | OK | OK | ORM / 迁移用 |
| 持久化 | OK | OK | 写入头部 32-bit 整数 |
| 典型用途 | schema version 管理 | 同 Linux | 仅给应用程序使用 |
| 项目 | Linux | Windows | 说明 |
| ----------- | ------- | ------- | ------------------------------ |
| 默认值 | NONE | NONE | 不自动回收 |
| FULL | OK | OK | 删除数据立即收缩文件 |
| INCREMENTAL | OK | OK | 手动 PRAGMA incremental_vacuum |
| 持久化 | OK | OK | 写入文件结构 |
| 文件系统影响 | EXT4 更快 | NTFS 较慢 | 仅 OS 层差异 |
| 模式 | Linux | Windows | 差异 |
| -------- | ----- | ------- | -------------- |
| DELETE | OK | OK | 一致 |
| TRUNCATE | OK | OK | 一致 |
| PERSIST | OK | OK | 一致 |
| MEMORY | OK | OK | 一致 |
| WAL | 强 | 弱一点 | 最大差异：锁实现不同 |
| 关键项 | Linux | Windows |
| 文件锁 | POSIX fcntl | LockFileEx |
| 并发性能 | 较强 | 较弱（锁开销大） |
| SHM 文件 | .db-shm | .db-shm |
| 值 | Linux 行为 | Windows 行为 | 说明 |
| OFF | 无 fsync | 无 FlushFileBuffers | 最快最不安全 |
| NORMAL | 推荐 WAL 默认 | 同 Linux | 性能 + 数据安全折中 |
| FULL | fsync() | FlushFileBuffers() | Windows 写盘更慢 |
| EXTRA | FULL + metadata fsync | 类似 FULL | 最安全 |
下面是这些命令合集，可以将这些进行一个标准化的设置：
-- 1. 设置数据库编码（仅空库且未建表时有效）
PRAGMA encoding = 'UTF-8';
-- 2. 设置数据库页面大小（仅空库有效）
PRAGMA page_size = 4096;
-- 3. 设置 database file 的 application id（应用识别）
PRAGMA application_id = 0x1234ABCD;
-- 4. 设置数据库 schema 版本（供程序使用）
PRAGMA user_version = 20240101;
-- 5. 设置自动回收空间模式（写入数据库文件结构）
-- NONE / FULL / INCREMENTAL PRAGMA auto_vacuum = FULL;
-- 6. 设置日志模式（journal_mode）
-- DELETE / TRUNCATE / PERSIST / MEMORY / WAL PRAGMA journal_mode = WAL;
-- 7. 设置同步级别（磁盘安全等级）
-- OFF / NORMAL / FULL / EXTRA PRAGMA synchronous = NORMAL;
-- 8. WAL 模式下自动 checkpoint 间隔（不持久化，每次连接需设置）
PRAGMA wal_autocheckpoint = 200;
-- 9. 可选：严格表（类似强类型）
-- CREATE TABLE t(a INTEGER, b TEXT) STRICT;
-- 10. 可选：快速收缩空间
-- PRAGMA incremental_vacuum;
对线程安全和并发支持
SQLite支持多线程安全读取吗？
答案是肯定的，其支持多线程安全读取，但需要正确的配置和使用模式。这是一个非常重要且常见的问题。简单回答“是”或“否”都不够准确，关键在于理解其背后的机制。
1. 核心：编译时和运行时的线程模式
SQLite 的线程安全特性取决于两个因素：编译时的设置和运行时的连接模式。
A. 编译时设置 (SQLITE_THREADSAFE)
在编译 SQLite 库时，可以通过 -DSQLITE_THREADSAFE 宏定义其线程安全级别：
1).SQLITE_THREADSAFE=1 (默认值) ：线程安全模式。SQLite 库内部会使用互斥锁来保护核心数据结构，使其可以在多线程环境中安全使用。这是大多数预编译版本采用的模式。
2).SQLITE_THREADSAFE=2：串行化模式。比线程安全更严格，它要求所有对 SQLite 的调用都来自同一个线程，或者应用程序自行处理所有同步。不常用。
3).SQLITE_THREADSAFE=0：单线程模式。所有内部互斥锁都被禁用，库体积更小、运行更快，但绝对不能在多线程中使用。
如何检查你的 SQLite 版本是否线程安全？可以执行以下 SQL 查询：
SELECT sqlite_threadsafe();
1).返回 1 表示是线程安全的（模式1或2）。
2).返回 0 表示是单线程模式，不安全。
B. 运行时连接模式 (sqlite3_open_v2)
即使库本身是线程安全编译的，还需要在打开数据库连接时指定正确的标志。这是通过 sqlite3_open_v2() 函数的第三个参数来设置的。
1).SQLITE_OPEN_NOMUTEX：以此模式打开的连接不会持有互斥锁，因此速度更快。但要求每个连接都只被单个线程使用。如果你在多个线程中使用同一个连接，会导致崩溃或数据错误。
2).SQLITE_OPEN_FULLMUTEX：以此模式打开的连接是“可重入”的，意味着它可以被多个线程安全地共享但一次只能由一个线程使用）。线程需要自己通过互斥锁来协调对连接的访问。
关键点：一个数据库连接 (sqlite3*) 在同一时间只能由一个线程使用。不能让线程A和线程B同时对一个连接执行操作。线程安全指的是多个线程可以同时使用各自独立的数据库连接来读取数据。
2. 多线程读取的最佳实践
对于典型的“多读者，单写者”场景，推荐以下模式：
1).每个线程使用自己独立的数据库连接。
线程A使用连接A，线程B使用连接B。它们可以同时进行读取操作。
绝对不要在线程间共享同一个连接对象。
2).写操作需要序列化。
虽然多个读取可以同时进行，但任何写操作（INSERT, UPDATE, DELETE）或需要写锁的事务（BEGIN IMMEDIATE, BEGIN EXCLUSIVE）都会暂时阻塞其他连接的读写操作，直到写操作完成。
SQLite 使用锁机制（共享锁、保留锁、排他锁）来管理并发，确保数据一致性。
3).启用 WAL 模式（强烈推荐）
WAL (Write-Ahead Logging) 模式是提升 SQLite 并发性能的“神器”。
在 WAL 模式下，读取和写入可以同时发生。读者不会阻塞写者，写者也不会阻塞读者（在大多数情况下）。这极大地提升了多线程读取的性能。
启用方法：
PRAGMA journal_mode=WAL;
| 场景 | 安全与否 | 说明 |
| 多个线程，每个线程使用自己的连接，库编译为线程安全模式 | 安全 | 这是标准且推荐的做法。结合 WAL 模式，性能最佳。 |
| 多个线程共享同一个连接 | 不安全 | 即使库是线程安全的，连接对象本身也不是为并发访问设计的。必须由应用层用互斥锁保护，确保同一时间只有一个线程使用该连接。 |
| 库编译为单线程模式 (SQLITE_THREADSAFE=0) | 不安全 | 无论如何使用，都不能在多线程环境中使用该库。 |
SQLite 完全支持安全高效的多线程读取，前提有三：
1).使用的 SQLite 库是线程安全编译的。
2).每个线程使用自己独立的数据库连接。
3).（可选但强烈推荐）启用 WAL 日志模式以最大化并发读取性能。
哪是否能够安全的使用数据库连接池？
可以，但需要非常小心，并且通常不推荐传统的连接池模式。因为连接不是线程安全的。
虽然 SQLite 支持多线程安全读取，但单个数据库连接 (sqlite3\*) 不是线程安全的。这意味着：
1).连接池中的连接可能被多个线程获取和使用；
2).如果两个线程同时使用同一个连接，会导致未定义行为、崩溃或数据损坏；
3).传统的连接池模式（如 MySQL、PostgreSQL）在这里不直接适用。
为什么不推荐复杂的连接池：
1).SQLite 连接本身很轻量 - 创建新连接的代价相对较小；
2).WAL 模式优化 - 启用 WAL 后，读写并发性能很好；
3).简化复杂性 - 避免连接泄漏、状态同步等问题；
4).事务边界 - 每个操作使用独立连接更符合事务的原子性。
对于大多数应用，为每个线程或每个操作创建新连接，配合 WAL 模式，是更简单、更安全的选择。拒绝复杂性。