SQLite分享学习
SQLite分享学习
什么是数据库
数据库是结构化数据的集合,用于存储和管理信息。
关系型数据库和非关系型数据库
| 特性 | 关系型数据库 | 非关系型数据库 |
|---|---|---|
| 数据模型 | 表结构（行/列），预定义模式 | 灵活模型（键值、文档、图等），动态模式 |
| 事务一致性 | 强一致性（ACID：原子性、隔离性等） |
最终一致性（BASE 模型） |
| 扩展性 | 垂直扩展为主，水平扩展复杂（需分片） |
原生支持水平扩展（分布式集群） |
| 查询语言 | SQL（标准化，支持复杂 JOIN） | 无统一语言（各数据库自有语法） |
| 典型场景 | 金融交易、ERP 系统、复杂分析报表 | 实时推荐、社交网络、IoT 数据流 |
关系型数据代表及场景
-
MySQL
- 特点：开源、轻量级、兼容性好，支持事务和复杂查询。
- 场景：Web 应用（如博客、电商后台）、中小企业业务系统。
-
PostgreSQL
- 特点：支持 JSON 等半结构化数据，扩展性强，符合 SQL 标准。
- 场景：地理信息系统（GIS）、科研数据分析。
-
Oracle
- 特点：企业级功能（高可用、安全审计），性能优化出色。
- 场景：银行核心系统、大型 ERP。
-
SQL Server
- 特点：与 Windows 生态集成度高，商业智能工具丰富。
- 场景：企业级 Windows 应用、数据分析平台。
非关系型数据代表及场景
-
Redis
- 特点：内存存储，支持持久化，提供多种数据结构
- 特点：用户在线状态管理、未读消息计数器（如 Discord 的实时在线显示）
-
Memcached
- 特点：纯内存缓存，简单高效，不支持持久化。
- 场景：静态资源缓存、数据库查询加速。
-
MongoDB
- 特点：类 JSON 文档存储，动态模式，支持索引与聚合。
- 场景：聊天媒体文件元数据存储（如 Slack 的文件共享记录）
-
HBase
- 特点：基于 HDFS，强一致性，适合离线处理。
- 场景：大数据仓库（如用户画像）。
数据库事务
数据库事务和 ACID
事务（Transaction）是数据库操作的逻辑单元，由一组不可分割的 SQL 命令组成，要么全部成功执行（提交），要么全部失效（回滚）。其核心目标是确保数据的一致性，典型应用如银行转账（扣款与存款必须同时成功或同时撤销）。
ACID 是事务的四大基本属性，保障数据操作的可靠性：
- 原子性（Atomicity）
- 定义：事务中的所有操作作为一个整体执行，全部成功或全部回滚到初始状态。
- 实现机制：通过日志（如 Undo Log）记录操作前的数据状态，失败时触发回滚
- 示例 1：转账中扣款成功但存款失败时，系统自动撤销扣款操作。
- 示例 2：在 IM 中的应用: 收到新消息
-
graph LR A[开始事务] --> B[写入扩散表] B --> C[写入消息详情] C --> D{成功?} D -->|是| E[提交] D -->|否| F[回滚]
- 一致性（Consistency）
-
定义：事务执行前后，数据库必须满足所有预设约束（如主键、外键、数据类型规则）。
-
关键点：依赖应用层逻辑与数据库约束共同维护，非数据库独立实现。
-
示例：检查消息接收者是否存在
-
CREATE TABLE inbox ( id INTEGER PRIMARY KEY, receiver_id INTEGER NOT NULL REFERENCES users(id), sender_id INTEGER NOT NULL REFERENCES users(id), content TEXT NOT NULL, msg_type TEXT CHECK(msg_type IN ('text', 'image', 'video')), created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
- 隔离性（Isolation）
-
定义：并发事务互不干扰，每个事务的操作在提交前对其他事务不可见。
-
隔离级别：
-
读未提交（Read Uncommitted）：可能读到未提交的数据（脏读）。 -
读已提交（Read Committed）：避免脏读，但可能出现不可重复读。 -
可重复读（Repeatable Read）：避免不可重复读，但可能有幻读。 -
串行化（Serializable）：完全隔离，无并发问题但性能最低。 -
隔离级别 IM 场景问题 读未提交 看到未确认的已读状态 读已提交 消息状态更新后立即可见 可重复读 保证聊天会话历史一致性
-
-
实现机制：锁机制（行级锁）或多版本并发控制（MVCC）。
- 持久性（Durability）
- 定义：事务提交后，修改永久生效，即使系统崩溃也不丢失。
- 实现机制：预写日志（WAL）或重做日志（Redo Log），提交前先写日志再更新数据
- WAL 机制在 IM 中的应用:
-
graph LR S[发送消息] --> W[写入WAL文件] R[读取消息] --> DB[从主数据库+WAL读取] W --> C[Checkpoint] --> DB C -->|定期| M[合并到主数据库]
-- IM消息投递事务示例
BEGIN TRANSACTION;
INSERT INTO outbox(sender_id, receiver_id, content) VALUES (101, 202, 'Hello!');
INSERT INTO inbox(receiver_id, sender_id, content) VALUES (202, 101, 'Hello!');
COMMIT;
WAL
WAL（Write-Ahead Logging，预写式日志） 是 SQLite 在 3.7.0 版本引入的日志机制，彻底改变了传统的回滚日志（Rollback Journal） 工作模式。
- 核心原理:
graph LR
W[写操作] --> WAL[写入WAL文件]
R[读操作] --> DB[直接读取主数据库]
WAL --> C[Checkpoint] --> DB
- 传统模式（回滚日志）与 WAL 模式对比：
| 特性 | Rollback Journal 模式 | WAL 模式 |
|---|---|---|
| 写操作流程 | 修改前复制数据页到日志 → 修改数据库文件 | 直接追加日志（WAL 文件） |
| 读操作路径 | 读取数据库文件 | 读取数据库文件 + WAL 文件（最新数据） |
| 锁机制 | 写时排他锁阻塞读写 | 读写并发（读不阻塞写，写不阻塞读） |
| 崩溃恢复 | 通过日志回滚未完成事务 | 通过 WAL 重放已提交事务 |
- 开启 wal 后数据库文件结构
app.db（主数据库）
app.db-wal（预写日志）
app.db-shm（共享内存索引）
SQlite 使用事务
- 事务的关键词
--启动事务
BEGIN TRANSACTION; --或 BEGIN;
--提交事务
COMMIT;
--回滚事务
ROLLBACK;
- SQLite 提供灵活约束违反处理方式（适用于
INSERT/UPDATE）：
| 策略 | 行为 |
|---|---|
REPLACE |
违反唯一约束时删除冲突行，插入新数据 |
IGNORE |
跳过冲突操作，继续执行后续命令 |
ABORT |
终止命令并回滚当前操作（默认策略） |
--"upsert"效果
INSERT OR REPLACE INTO users (id, name) VALUES (1, 'Alice');
--批量入库,忽略所有约束错误
INSERT OR IGNORE INTO users (id, name)
VALUES
(1, 'Alice'),
(2, 'Blob'), -- 若 id=2 已存在，跳过本行
(3, 'Candy');
--批量入库,忽略已存在
INSERT INTO users (id, name)
VALUES
(1, 'Alice'),
(2, 'Blob')
ON CONFLICT(id) DO NOTHING; -- 仅忽略id冲突
- 异常处理
try:
cursor.execute("BEGIN")
# 执行操作
cursor.execute("COMMIT")
except Exception as e:
cursor.execute("ROLLBACK")
- 优化策略
SQLite 默认会为每条独立的 SQL 语句隐式创建一个事务（即 Autocommit 模式）。这种设计确保了每条语句的原子性和一致性
性能代价：每条语句都需要写日志文件（rollback journal 或 WAL）、获取锁、同步磁盘，导致高频写入时效率低
-
事务批量处理
使用
BEGIN TRANSACTION 和COMMIT 将多条操作包裹在单个事务中, 从而减少事务提交次数，将多次磁盘 I/O 合并为一次BEGIN TRANSACTION; INSERT INTO table VALUES (...); INSERT INTO table VALUES (...); -- 批量插入 COMMIT; -
预编译语句
预编译语句（Prepared Statements） 是一种将 SQL 命令预先编译为可重复执行的二进制模板的技术（核心原理是“一次编译，多次运行”）。
它通过占位符（如 ? 或命名参数 :param）替代实际值，后续只需绑定不同参数即可高效执行
sqlite3_prepare_v2(db, "INSERT INTO table VALUES (?,?)", -1, &stmt, NULL); for (i=0; i<count; i++) { sqlite3_bind_int(stmt, 1, value1); sqlite3_bind_text(stmt, 2, value2); sqlite3_step(stmt); sqlite3_reset(stmt); // 重用语句 } sqlite3_clear_bindings(stmt); // 可选：清除绑定 sqlite3_finalize(stmt); // 销毁语句对象
- IM 场景事务控制
-
# Python消息投递示例（含错误回滚） try: cursor.execute("BEGIN IMMEDIATE") cursor.execute("INSERT INTO messages (chat_id, sender_id, content) VALUES (?, ?, ?)", (1234, 101, "会议改到明天下午")) cursor.execute("UPDATE conversations SET last_msg_time = CURRENT_TIMESTAMP WHERE id = ?", (1234,)) cursor.execute("COMMIT") except sqlite3.Error as e: cursor.execute("ROLLBACK") logger.error(f"消息发送失败: {e}") notify_user("消息发送失败，请重试")
-
批量消息入库优化
-- 单条提交（性能差） INSERT INTO messages(chat_id, sender_id, content) VALUES (5678, 102, 'Hi'); -- 事务批量提交（推荐） BEGIN TRANSACTION; INSERT INTO messages(chat_id, sender_id, content) VALUES (1234, 101, '项目文档已更新'); INSERT INTO messages(chat_id, sender_id, content) VALUES (1234, 103, '收到，马上查看'); INSERT INTO messages(chat_id, sender_id, content) VALUES (5678, 102, '今晚聚餐吗？'); COMMIT; -- 群聊消息批量插入 WITH new_msgs(chat_id, sender_id, content) AS ( VALUES (1234, 101, '会议改期'), (1234, 102, '收到'), (5678, 103, '新功能设计') ) INSERT INTO messages(chat_id, sender_id, content) SELECT chat_id, sender_id, content FROM new_msgs; -
通过
upsert方式更新消息状态-- 消息已读状态更新 INSERT OR REPLACE INTO message_status(message_id, user_id, is_read, read_time) VALUES (1234, 101, 1, CURRENT_TIMESTAMP); -- 批量更新消息状态 INSERT INTO message_status(message_id, user_id, is_read) VALUES (5566, 101, 1), (7788, 101, 0) ON CONFLICT(message_id, user_id) DO UPDATE SET is_read = excluded.is_read, read_time = CASE WHEN excluded.is_read = 1 THEN CURRENT_TIMESTAMP ELSE read_time END;
SQLite 锁机制
锁竞争: 指在多进程或多线程并发访问数据库时，多个操作因争用同一锁资源而导致的阻塞或等待现象
SQLite 采用数据库文件级锁而非行级或表级锁, 意味着任何写操作（INSERT/UPDATE/DELETE）需获取排他锁（EXCLUSIVE Lock） ，此时整个数据库文件被独占
- 锁升级机制（Lock Escalation）
graph LR
UNLOCKED --> SHARED[读操作 SHARED] --> RESERVED[写操作 RESERVED] --> PENDING[提交前 PENDING] --> EXCLUSIVE[提交 EXCLUSIVE]
关键瓶颈：从 RESERVED 升级到 EXCLUSIVE 时（即提交阶段），需等待所有 SHARED 锁释放，此时新读操作被阻塞（PENDING 锁阻止新读）
- 锁冲突场景(传统策略)
# 用户A发送图片（获取EXCLUSIVE锁）
cursor.execute("BEGIN IMMEDIATE")
cursor.execute("INSERT INTO messages ...") # 耗时操作
# 在此期间...
# 用户B同时查询消息（被PENDING锁阻塞）
cursor.execute("SELECT * FROM messages WHERE chat_id=1234") # 返回SQLITE_BUSY错误
-- 设置3秒锁等待超时
PRAGMA busy_timeout = 3000; -- 设置3秒锁等待超时
-- 写操作优化
BEGIN IMMEDIATE; -- 立即获取RESERVED锁，减少冲突
- SQLite 锁类型与行为对照表
| 锁类型 | 获取条件 | 行为特征 | 并发影响 |
|---|---|---|---|
| SHARED | 读操作（SELECT） | 允许多个读并发 | 阻塞写锁升级 |
| RESERVED | 写操作开始（DML 语句） | 仅允许一个，与读共存 | 阻塞其他写操作 |
| PENDING | Commit 前准备阶段 | 阻止新读，等待已有读释放 | 新读操作被拒（SQLITE_BUSY） |
| EXCLUSIVE | Commit 执行阶段 | 独占数据库，排斥一切其他锁 | 所有操作阻塞 |
参考 SQLite 锁机制简介: https://huili.github.io/lockandimplement/machining.html
虚拟列和虚拟表
虚拟列（Generated Columns）
虚拟列是通过表达式基于同一行其他列动态计算生成的列，无需显式存储（默认 VIRTUAL），也可选择持久化存储（STORED）。也称为“计算列”
官方文档地址: https://sqlite.org/gencol.html
| 类型 | 计算时机 | 存储空间 | 是否支持 ALTER TABLE |
|---|---|---|---|
| VIRTUAL | 查询时实时计算 | 不占用 | ✅ 支持 |
| STORED | 写入时计算并存储 | 占用 | ❌ 不支持 |
- 表达式仅限同一行内的列、常量及确定性标量函数（如
abs()、substr()），不支持子查询或聚合函数 - 不能作为主键，且每表至少需有一个非生成列
-- 创建表并添加虚拟列
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
income REAL,
tax_rate REAL,
tax REAL AS (income * tax_rate) -- 实时计算税额
);
-- 查询时直接使用虚拟列
SELECT id, tax FROM employees WHERE tax > 1000;
-- 为虚拟列创建索引可加速检索
CREATE INDEX idx_tax ON employees(tax);
- 使用虚拟列标记敏感消息
-- 消息敏感词检测列
CREATE TABLE messages (
id INTEGER PRIMARY KEY,
content TEXT,
has_sensitive BOOLEAN GENERATED ALWAYS AS (content LIKE '%密码%') VIRTUAL
);
-- 查询含敏感词消息
SELECT * FROM messages WHERE has_sensitive = true;
虚拟表（Virtual Tables）
虚拟表是通过自定义模块（Module）实现的抽象表结构，数据可来源于外部（如 CSV、API）或内部特殊索引（如全文检索）。
此处不进行详情介绍.
字段约束
字段约束（Field Constraints）是数据库中对表中列（字段）设定的强制性规则，用于限制可插入的数据类型，确保数据的准确性、完整性和一致性。
-
NOT NULL 约束作用：禁止字段值为
NULL（空值），强制必须有有效数据输入CREATE TABLE Users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, -- 禁止 name 为空 age INTEGER ); -- 插入时若 `name` 未赋值，操作将失败 -
DEFAULT 约束作用：字段未指定值时，自动填充预设默认值。
CREATE TABLE Orders ( id INTEGER PRIMARY KEY, amount REAL DEFAULT 0.0, -- 未赋值时默认为 0.0 created_at TEXT DEFAULT CURRENT_TIMESTAMP ); -
UNIQUE 约束作用：字段未指定值时，自动填充预设默认值。
CREATE TABLE Products ( id INTEGER PRIMARY KEY, sku TEXT UNIQUE, -- sku 值必须唯一 price REAL ); --常用于标识码、邮箱等唯一性字段 -
PRIMARY KEY 约束作用：字段未指定值时，自动填充预设默认值。
CREATE TABLE OrderItems ( order_id INTEGER PRIMARY KEY,--主键 product_id INTEGER, quantity INTEGER ); --单字段主键直接在列后声明；复合主键需单独定义 -
CHECK 约束作用：字段未指定值时，自动填充预设默认值。
CREATE TABLE Employees ( id INTEGER PRIMARY KEY, age INTEGER CHECK (age >= 18), -- 年龄必须 ≥18 salary REAL CHECK (salary >= 0) -- 工资必须是正数 ); --不满足验证的数据,入库失败 -
FOREIGN KEY 约束作用：强制字段值匹配另一表的主键，建立表间关联。
CREATE TABLE Orders ( id INTEGER PRIMARY KEY, customer_id INTEGER, FOREIGN KEY (customer_id) REFERENCES Customers(id) -- 关联 Customers 表 ); --插入时 `customer_id` 必须在 `Customers.id` 中存在
创建方式对比
| 约束类型 | 列级声明示例 | 表级声明示例 |
|---|---|---|
PRIMARY KEY |
id INT PRIMARY KEY |
PRIMARY KEY (id, name) |
FOREIGN KEY |
❌ 不支持 | FOREIGN KEY (cid) REFERENCES Classes(id) |
CHECK |
age INT CHECK (age > 0) |
CHECK (age > 0 AND salary < 10000) |
UNIQUE / NOT NULL |
sku TEXT UNIQUE / name TEXT NOT NULL |
仅支持列级声明 |
- IM 场景下的字段约束
-- 用户表
CREATE TABLE users (
id INTEGER PRIMARY KEY,
phone TEXT NOT NULL UNIQUE CHECK(LENGTH(phone) BETWEEN 5 AND 15),
nickname TEXT NOT NULL DEFAULT '新用户',
avatar_url TEXT,
status TEXT CHECK(status IN ('online', 'offline', 'busy')) DEFAULT 'offline',
last_active DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 群聊表
CREATE TABLE group_chats (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
owner_id INTEGER NOT NULL REFERENCES users(id),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
max_members INTEGER CHECK(max_members BETWEEN 1 AND 5000) DEFAULT 500
);
-- 聊天关系表
CREATE TABLE chat_participants (
chat_id INTEGER NOT NULL REFERENCES group_chats(id),
user_id INTEGER NOT NULL REFERENCES users(id),
joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
role TEXT CHECK(role IN ('member', 'admin', 'owner')) DEFAULT 'member',
PRIMARY KEY (chat_id, user_id)
) WITHOUT ROWID;
SQLite 约束的特殊性
-
主键可为
NULL- SQLite 允许主键值为
NULL（违反 SQL 标准），但会导致行为不一致，实践中应显式声明 NOT NULL
- SQLite 允许主键值为
-
约束删除限制
- SQLite 不支持直接删除约束（如
ALTER TABLE DROP CONSTRAINT）。 - 替代方案：重建表或新建表迁移数据
- SQLite 不支持直接删除约束（如
-
空值与
NULL 的区别- 空值 ('')：有效字符串，不占用存储空间
-
NULL：表示“未知”，占用空间，索引效率低 - 查询时空值用
= '' 判断，NULL 需用IS NULL
SQLite 索引
什么是索引?
索引是一种高效查找数据的专用数据结构，它通过创建特定字段的排序副本，使数据库引擎能快速定位数据位置
本质：数据库的"目录系统"
graph LR
A[查询请求] --> B{是否有索引?}
B -->|有索引| C[通过索引树快速定位]
B -->|无索引| D[全表扫描]
C --> E[返回结果]
D --> E
索引保存在哪里?
SQLite 将所有索引与表数据统一存储在同一个数据库文件中，采用模块化页管理：
数据库文件结构：
├── 数据库头 (100字节)
├── 表B-tree页 (存储实际行数据)
├── 索引B-tree页 (索引数据结构)
└── 空闲页列表
-
B-tree/B+tree 结构
- 平衡树结构保证查找效率 O(log n)
- 叶子节点存储：索引键值 + 对应 ROWID
-
索引页组成
# 索引页内存结构示例 class IndexPage: page_type: int # 0x02表示索引页 cell_count: int # 当前页存储的索引条目数 cells: list # 索引条目数组 right_pointer: int # 右子树指针 -
与表数据的关联
- 每个索引条目包含 ROWID（行唯一标识）
- 通过 ROWID 回表获取完整行数据
graph LR
A[数据表 orders] -->|行数据| B[ROWID=1, amount=100, created_at='2025-01-01']
C[索引 idx_created] -->|索引项| D['2025-01-01' → ROWID=1]
-
ROWID
-
ROWID 是一个内置的隐藏列，用于唯一标识表中的每一行记录。 -
通过
CREATE TABLE ... WITHOUT ROWID; 创建的表不包含rowid- 需使用复合主键或非整数主键，节省空间并提升特定查询效率
- 须显式定义
PRIMARY KEY - 不支持
AUTOINCREMENT 或sqlite3_last_insert_rowid() 等依赖rowid 的功能
-
PRIMARY KEY 与rowid 的关系-
主键为
INTEGER 类型 → 主键是 rowid 的别名 -
主键非
INTEGER 类型 → rowid 与主键独立存在 -
WITHOUT ROWID 表 → 无 rowid ，主键为唯一标识
-
特性 rowid（或INTEGER PRIMARY KEY）非整数主键（普通表） WITHOUT ROWID 表的主键是否物理唯一标识 ✅ 是 ❌ 否（ rowid 仍是底层标识）✅ 是 查询效率 ⭐⭐⭐⭐⭐ (O(log n)) ⭐⭐ (O(2log n)，需回表) ⭐⭐⭐⭐ (O(log n)) 主键类型限制 仅 INTEGER任意类型 任意类型，但强制非空 存储开销 较小（ rowid 与主键合并）较大（主键需额外索引） 较小（无 rowid 冗余）是否支持 AUTOINCREMENT✅ 是 ❌ 否 ❌ 否 -
索引与数据表的核心区别：
| 特性 | 数据表 | 索引 |
|---|---|---|
| 数据结构 | B-tree 或堆表 | B-tree（有序结构） |
| 存储内容 | 所有列数据 | 索引列值 + ROWID |
| 排序方式 | 插入顺序或主键排序 | 索引字段显式排序 |
| 物理位置 | 数据库文件主存储区 | 数据库文件独立存储区 |
| 更新同步机制 | 基础数据 | 自动与基础表同步（增删改时维护） |
索引的使用
索引创建
CREATE INDEX idx_products_price ON products(price);
执行步骤：
- 解析列定义
- 创建 B-tree 结构
- 扫描全表提取(price, ROWID)对
- 按键值排序插入 B-tree
- 更新 sqlite_master 系统表
使用到索引
EXPLAIN QUERY PLAN
SELECT * FROM products WHERE price BETWEEN 100 AND 200;
索引类型
| 索引类型 | 创建方式 | 最佳场景 |
|---|---|---|
| 单列索引 | CREATE INDEX idx_name ON tbl(col) |
高频过滤列 |
| 复合索引 | CREATE INDEX idx_name ON tbl(col1, col2) |
多条件查询 |
| 唯一索引 | CREATE UNIQUE INDEX idx_name ON tbl(col) |
主键/唯一约束 |
| 覆盖索引 | CREATE INDEX idx_name ON tbl(col1, col2) |
避免回表查询 |
| 部分索引 | CREATE INDEX idx_name ON tbl(col) WHERE condition |
过滤特定数据子集 |
-- 复合索引（覆盖常见查询）
CREATE INDEX idx_msg_search ON messages(chat_id, sender_id, created_at DESC);
-- 部分索引（仅索引未读消息）
CREATE INDEX idx_unread ON messages(chat_id, created_at) WHERE is_read = 0;
-- 全文搜索索引（消息内容搜索）
CREATE VIRTUAL TABLE msg_fts USING fts5(content, tokenize="unicode61");
联合索引
联合索引（也叫组合索引、复合索引、多列索引）是指对表上的多个列进行索引。联合索引的创建方法跟单个索引的创建方法一样，不同之处仅在于有多个索引列。
-- 建表语句
CREATE TABLE test1 (
key1 TEXT NOT NULL,
key2 TEXT NOT NULL,
key3 TEXT NOT NULL,
key4 TEXT NOT NULL,
key5 TEXT NOT NULL,
PRIMARY KEY (key1)
);
-- 创建联合索引
CREATE INDEX idx_k234 ON test1 (key2, key3, key4);
-- 相当于创建了索引 key2, key2&key3, key2&key3&key4
-- 联合索引的使用
EXPLAIN QUERY PLAN SELECT * FROM test1 WHERE key2='1' AND key3='1' AND key4='3'; --可使用到索引
EXPLAIN QUERY PLAN SELECT * FROM test1 WHERE key4='1' AND key3='1' AND key2='3'; --可使用到索引
EXPLAIN QUERY PLAN SELECT * FROM test1 WHERE key3='1' AND key2='1' AND key4='3'; --可使用到索引
EXPLAIN QUERY PLAN SELECT * FROM test1 WHERE key3='1' AND key4='3'; --不使用到索引
- 最左原则: 组成联合索引的多个列，越靠左边优先级越高，同时也只有
SQL查询条件中，包含了最左的字段，才能使用联合索引 - SQL中使用范围查询时会停止匹配，比如
>、<、between、like这类范围条件，并不会继续使用联合索引 - 是否使用联合索引查询, 跟
WHERE后的条件顺序无关，因为优化器会对SQL查询进行重排序, 查询条件和索引顺序不一致不影响索引的使用
部分索引
部分索引是建立在一个表的子集上的索引； 该子集是由一个条件表达式定义的(叫做部分索引的谓词)。该索引只包含表中那些满足这个谓词的行。部分索引是一个特殊的特性，但是在某些场合很有用。
在普通索引中，表中每一行的索引中只有一个条目。在部分索引中，表中只有一部分行具有相应的索引条目。例如，部分索引可能会忽略被索引的列为NULL的条目。如果明智地使用，部分索引可能导致较小的数据库文件，并改善查询和写入性能。
CREATE INDEX idx_k345 ON test1 (key3, key4, key5) WHERE key5='ok';
EXPLAIN QUERY PLAN SELECT * FROM test1 WHERE key3='3';
-- SCAN test1
-- 查询条件 key3='3' 不满足索引的过滤条件（缺少 key5='ok'）
-- 没有其他可用索引，只能全表扫描
EXPLAIN QUERY PLAN SELECT * FROM test1 WHERE key5='ok';
-- SCAN test1 USING INDEX idx_k345
-- 满足索引过滤条件（key5='ok'）, 查询是 SELECT *, 需要回表查完整数据
-- 未使用索引最左列（缺少 key3 条件）,执行索引扫描（非查找），遍历索引中所有行
EXPLAIN QUERY PLAN SELECT key3, key4, key5 FROM test1 WHERE key5='ok';
-- SCAN test1 USING COVERING INDEX idx_k345
-- 满足索引过滤条件（key5='ok'）,查询列 (key3, key4, key5) 全部在索引中,执行索引覆盖扫描（不需要回表）
EXPLAIN QUERY PLAN SELECT * FROM test1 WHERE key3='3' AND key5='ok';
-- SEARCH test1 USING INDEX idx_k345 (key3=?)
-- 满足索引过滤条件（key5='ok'）, 触发索引查找（高效检索）
-- 查询是 SELECT *, 需要回表查完整数据
EXPLAIN QUERY PLAN SELECT * FROM test1 WHERE key4='4' AND key5='ok';
-- SCAN test1 USING INDEX idx_k345
-- 满足索引过滤条件（key5='ok'）, key4 不是索引的首列,执行索引扫描（非查找），遍历索引中所有行
-- 查询是 SELECT *, 需要回表查完整数据
- 总结: 部分索引通过预过滤机制突破了组合索引的最左原则要求，但索引内部的列顺序（最左前缀原则）仍影响检索效率。
索引冗余
CREATE TABLE users1 (
id INTEGER PRIMARY KEY,
phone TEXT NOT NULL UNIQUE CHECK(LENGTH(phone) BETWEEN 5 AND 15),
nickname TEXT NOT NULL DEFAULT '新用户',
avatar_url TEXT,
status TEXT CHECK(status IN ('online', 'offline', 'busy')) DEFAULT 'offline',
last_active DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX "idx_phone" ON "users1" ("phone");
EXPLAIN QUERY PLAN SELECT * FROM users1 WHERE phone='1434325'
-- SEARCH users1 USING INDEX sqlite_autoindex_users1_1 (phone=?)
- SQLite会自动为UNIQUE约束创建索引, 此处
sqlite_autoindex_users1_1是phone字段的UNIQUE索引 - 优化器优先选择唯一索引(
sqlite_autoindex_users1_1)而非普通索引(idx_phone) - 综上: 创建的
idx_phone是多余的
索引最佳实践
索引如何设计?
检索有大量重复数据的字段，不适合建立索引，反而会导致检索速度变慢，因为扫描索引节点的速度比全表扫描要慢。
例如表中有 status 这个经常重复的数据, 建立索引再对其检索后，反而比不建立索引查询要慢一倍多。
--好：高选择性列 + 覆盖查询
create_index("users", ["country", "city", "last_login"])
--坏：低选择性列 + 未覆盖查询
create_index("products", ["is_active"]) --布尔值选择性低
--坏：过度索引
CREATE INDEX idx_temp1 ON orders(status); --status只有3种值
CREATE INDEX idx_temp2 ON orders(created_at); --created_at值分布过高
--坏：索引列参与计算
SELECT * FROM products WHERE price*0.9 > 100; --无法使用price索引
SELECT * FROM products WHERE price > 100/0.9; --可以使用price索引
可以避免索引的情况:
- 小表全扫描更快（<1000 行）
- 低频查询字段
- 超高频写表（索引维护代价>收益）
- text/blob 大字段（用全文索引替代）
多索引使用
和全表扫描/只使用一个索引的速度比起来，去分析两个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是用一个索引.
SQLite 优化器会在以下情况考虑多索引：
- WHERE 子句包含多个独立条件（使用 AND 连接）
- 每个条件都有适用的单列索引
- 没有更优的复合索引可用
- 优化器判断多索引扫描比全表扫描更快
-- 假设存在两个索引：
-- idx_created(created_at)
-- idx_amount(amount)
SELECT * FROM orders WHERE created_at > '2025-08-01' AND amount > 150;
执行过程：
graph LR
A[查询] --> B[idx_created索引<br>获取满足created_at条件的ROWID]
A --> C[idx_amount索引<br>获取满足amount条件的ROWID]
B --> D[ROWID集合交集]
C --> D
D --> E[回表查询完整数据]
索引覆盖和回表
索引覆盖: 当索引包含查询所需的所有列时，引擎无需回表可直接从索引获取数据。
graph LR
A[索引扫描] --> B[直接获取查询列数据]
B --> C[返回结果集]
回表查询: 当 SQL 引擎无法直接从索引获取所有查询数据时，需要回到原始数据表获取完整行的过程。
graph LR
A[索引扫描] --> B[获取ROWID]
B --> C[根据ROWID回基表]
C --> D[读取完整数据行]
场景示例表结构:
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount REAL,
created_at DATETIME,
status TEXT
);
-- 创建单列索引
CREATE INDEX idx_created ON orders(created_at);
回表查询示例:
SELECT id, customer_id, amount, status FROM orders WHERE created_at > '2025-08-01'; --需回表查询
执行步骤：
- 索引扫描：使用
idx_created 定位符合日期条件的记录 - 获取 ROWID：从索引中读取对应行的
ROWID (即主键 id) - 回表访问：根据 ROWID 回
orders表读取整行数据 - 提取数据：从行数据中获取
customer_id, amount, status - 返回结果：组合数据返回结果集
sequenceDiagram
participant C as Client
participant E as SQL Engine
participant I as idx_created索引
participant T as orders表
C->>E: 发送查询请求
E->>I: 查找 created_at > '2025-08-01'
I-->>E: 返回ROWID列表
loop 每行处理
E->>T: 根据ROWID请求完整数据
T-->>E: 返回行数据
E->>E: 提取所需列
end
E-->>C: 返回结果集
索引覆盖示例:
-- 创建覆盖索引
CREATE INDEX idx_covering ON orders(created_at, customer_id, amount);
SELECT customer_id, amount FROM orders WHERE created_at > '2025-08-01';
执行步骤：
- 索引扫描：使用
idx_covering 定位符合条件的索引条目 - 直接获取数据：从索引直接读取
customer_id, amount 值 - 返回结果：组合数据返回结果集
sequenceDiagram
participant C as Client
participant E as SQL Engine
participant I as idx_covering索引
C->>E: 发送查询请求
E->>I: 查找 created_at > '2025-08-01'
loop 每行处理
I-->>E: 直接返回customer_id, amount
end
E-->>C: 返回结果集
- 索引覆盖示例
-
-- 创建覆盖索引 CREATE INDEX idx_msg_cover ON messages(chat_id, created_at DESC, content, sender_id); -- 使用覆盖索引的查询 EXPLAIN QUERY PLAN SELECT id, content, send_time FROM messages WHERE chat_id=1234 ORDER BY created_at DESC LIMIT 100; -- 输出：USING COVERING INDEX idx_msg_cover
-
ROWID 优化
-- 消息表（避免ROWID回表） CREATE TABLE messages ( chat_id INTEGER, msg_id INTEGER, content TEXT, PRIMARY KEY(chat_id, msg_id) ) WITHOUT ROWID; -- 直接使用主键作为物理存储位置
设计原则
| 优先级 | 列类型 | 排列顺序 | 示例场景 |
|---|---|---|---|
| 1 | WHERE 条件列 | 高选择性优先 | user_id(过滤) |
| 2 | ORDER BY/GROUP BY | 排序顺序一致 | created_at DESC(排序) |
| 3 | SELECT 返回列 | 低 |
status(返回但不过滤) |
- 错误使用示例
-- 无法使用索引的操作（函数运算）
SELECT * FROM messages WHERE LENGTH(content) > 100;
-- 优化方案
SELECT * FROM messages WHERE content GLOB '*?????????*';
-- 低效索引（布尔字段）
CREATE INDEX idx_read_status ON messages(is_read);
-- 优化方案：使用部分索引
CREATE INDEX idx_unread_only ON messages(chat_id) WHERE is_read = 0;
创建索引的规则
-
经常频繁用作查询条件的字段应酌情考虑为其创建索引。
-
建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。
-
建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合
-
经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间
-
尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。
-
建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择分词索引(FTS)
创建索引需要注意的情况:
- 字段存在大量的重复值时，不适合建立索引
- 索引不能参与计算，因此经常带函数查询的字段没必要索引, 或者修改查询不要带函数查询
- 建立联合索引时必须要考虑优先级，查询频率最高的字段应当放首位
- 当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大
- 值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构
验证索引覆盖
EXPLAIN QUERY PLAN
SELECT customer_id, amount
FROM orders
WHERE created_at > '2025-08-01';
-- 输出结果：
SEARCH TABLE orders USING COVERING INDEX idx_covering (created_at>?)
监控索引效率
-- 监控查询性能
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM messages WHERE chat_id=5678 AND is_read=0;
-- 分析索引效率
ANALYZE;
SELECT * FROM sqlite_stat1 WHERE tbl_name = 'messages';
查询优化示例
SELECT id, content FROM messages WHERE chat_id=1234 ORDER BY id DESC LIMIT 100;
-- 覆盖索引 (chat_id + id + content)
CREATE INDEX idx_chat_msg_cover ON messages(chat_id, id DESC, content);
-- 复合索引 (chat_id + created_at)
CREATE INDEX idx_chat_time ON messages(chat_id, created_at DESC);
-- 部分索引（未读消息）
CREATE INDEX idx_unread_msgs ON messages(chat_id) WHERE is_read = 0;
-
idx_chat_msg_cover索引覆盖了chat_id、id和content字段 - 查询条件
WHERE chat_id=1234可以直接使用索引过滤 -
ORDER BY id DESC可以利用索引的降序排列 -
LIMIT 100只需扫描索引前 100 条记录 - 无需回表操作，直接从索引获取所需数据
graph TB
Q[查询] --> I[使用idx_chat_msg_cover索引]
I --> F[按chat_id=1234过滤]
F --> S[按id DESC排序]
S --> L[取前100条]
L --> R[返回id和content]
总结
-
设计优化
- 数据结构规范, 避免数据冗余和不一致(字段定义不一致, 入库值不一致)
- 根据数据特性选择合适的数据类型, 使用 INTEGER 代替 TEXT 提高查询性能
- 合理的索引提高查询速度, 需要平衡储存占用和数据入库的性能影响
-
CREATE TABLE messages_2025 ( id INTEGER PRIMARY KEY, chat_id INTEGER REFERENCES chats(id), content TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
-
查询优化
- 避免查询全部字段(SELECT *), 减少返回的大小. 可以使用到索引覆盖
- 合理使用 WHERE 过滤数据
- 避免列上使用函数, 会导致全表扫描
- 使用 JOIN 替换子查询
- LIMIT 限制返回条数
-
-- 优化前（全表扫描） SELECT * FROM messages ORDER BY id DESC; -- 优化后（索引覆盖） SELECT id, content FROM messages WHERE chat_id=1234 ORDER BY id DESC LIMIT 100;
-
合理缓存
- 合理利用索引覆盖
- 应用层缓存
- 非必要不查询: 接口返回数据-> 入库-> 查库
-
graph TB A[收到新消息,显示用户昵称和头像] --> B{内存缓存} B -->|存在| C[直接更新UI] B -->|不存在| D[查询数据库] D --> E[更新缓存] E --> C
-
事务使用
-
多个操作合并事务, 减少操作
-
减少事务数据的大小, 大量数据锁库时间更久
-
场景 策略 单条消息发送 自动提交事务 批量导入历史消息 每 1000 条手动提交事务
-
-
编程实践
- 使用连接池
- 避免循环操作数据库
- 预编译 SQL,提高重复执行性能
- 捕获错误, 合理处理
-
# 预编译语句示例（Python） stmt = db.prepare("INSERT INTO messages(chat_id, content) VALUES (?, ?)") for msg in new_messages: stmt.execute((msg.chat_id, msg.content))
浙公网安备 33010602011771号