当前位置： 首页 > news >正文

SQLite分享学习

news 2025/10/1 14:23:53

SQLite分享学习

什么是数据库

数据库是结构化数据的集合,用于存储和管理信息。

‍

关系型数据库和非关系型数据库

特性 关系型数据库 非关系型数据库
数据模型 表结构（行/列），预定义模式 灵活模型（键值、文档、图等），动态模式
事务一致性 强一致性（ACID：原子性、隔离性等）
最终一致性（BASE 模型）
扩展性 垂直扩展为主，水平扩展复杂（需分片）
原生支持水平扩展（分布式集群）
查询语言 SQL（标准化，支持复杂 JOIN） 无统一语言（各数据库自有语法）
典型场景 金融交易、ERP 系统、复杂分析报表 实时推荐、社交网络、IoT 数据流

‍

关系型数据代表及场景

  1. MySQL

    • 特点：开源、轻量级、兼容性好，支持事务和复杂查询。
    • 场景：Web 应用（如博客、电商后台）、中小企业业务系统。

  2. PostgreSQL

    • 特点：支持 JSON 等半结构化数据，扩展性强，符合 SQL 标准。
    • 场景：地理信息系统（GIS）、科研数据分析。

  3. Oracle

    • 特点：企业级功能（高可用、安全审计），性能优化出色。
    • 场景：银行核心系统、大型 ERP。

  4. SQL Server

    • 特点：与 Windows 生态集成度高，商业智能工具丰富。
    • 场景：企业级 Windows 应用、数据分析平台。

‍

非关系型数据代表及场景

  1. Redis

    • 特点：内存存储，支持持久化，提供多种数据结构
    • 特点：用户在线状态管理、未读消息计数器（如 Discord 的实时在线显示）

  2. Memcached

    • 特点：纯内存缓存，简单高效，不支持持久化。
    • 场景：静态资源缓存、数据库查询加速。

  3. MongoDB

    • 特点：类 JSON 文档存储，动态模式，支持索引与聚合。
    • 场景：聊天媒体文件元数据存储（如 Slack 的文件共享记录）

  4. HBase

    • 特点：基于 HDFS，强一致性，适合离线处理。
    • 场景：大数据仓库（如用户画像）。

‍

‍

数据库事务

数据库事务和 ACID

事务（Transaction）是数据库操作的逻辑单元，由一组不可分割的 SQL 命令组成，要么全部成功执行（提交），要么全部失效（回滚）。其核心目标是确保数据的一致性，典型应用如银行转账（扣款与存款必须同时成功或同时撤销）。

CID 是事务的四大基本属性，保障数据操作的可靠性：

  1. 原子性（Atomicity）
  • 定义：事务中的所有操作作为一个整体执行，全部成功或全部回滚到初始状态。
  • 实现机制：通过日志（如 Undo Log）记录操作前的数据状态，失败时触发回滚
  • 示例 1：转账中扣款成功但存款失败时，系统自动撤销扣款操作。
  • 示例 2：在 IM 中的应用: 收到新消息
  • graph LRA[开始事务] --> B[写入扩散表]B --> C[写入消息详情]C --> D{成功?}D -->|是| E[提交]D -->|否| F[回滚]
  1. 一致性（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
);
  1. 隔离性（Isolation）

  • 定义：并发事务互不干扰，每个事务的操作在提交前对其他事务不可见。

  • 隔离级别：

    • ​读未提交​（Read Uncommitted）：可能读到未提交的数据（脏读）。
    • ​读已提交​（Read Committed）：避免脏读，但可能出现不可重复读。
    • ​可重复读​（Repeatable Read）：避免不可重复读，但可能有幻读。​
    • ​串行化​（Serializable）：完全隔离，无并发问题但性能最低。
    • 隔离级别 IM 场景问题
      读未提交 看到未确认的已读状态
      读已提交 消息状态更新后立即可见
      可重复读 保证聊天会话历史一致性

  • 实现机制：锁机制（行级锁）或多版本并发控制（MVCC）。

    ‍

  1. 持久性（Durability）
  • 定义：事务提交后，修改永久生效，即使系统崩溃也不丢失。
  • 实现机制：预写日志（WAL）或重做日志（Redo Log），提交前先写日志再更新数据
  • WAL 机制在 IM 中的应用:
  • graph LRS[发送消息] --> W[写入WAL文件]R[读取消息] --> DB[从主数据库+WAL读取]W --> C[Checkpoint] --> DBC -->|定期| 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 LRW[写操作] --> 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）、获取锁、同步磁盘，导致高频写入时效率低

‍

  1. 事务批量处理

    使用 BEGIN TRANSACTION​ 和 COMMIT​ 将多条操作包裹在单个事务中, 从而减少事务提交次数，将多次磁盘 I/O 合并为一次

    BEGIN TRANSACTION;
INSERT INTO table VALUES (...);
INSERT INTO table VALUES (...); -- 批量插入
COMMIT;

  2. 预编译语句

    预编译语句（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 SETis_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 LRUNLOCKED --> 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

​image​

‍

‍

虚拟列和虚拟表

虚拟列（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）是数据库中对表中列（字段）设定的强制性规则，用于限制可插入的数据类型，确保数据的准确性、完整性和一致性。

‍

  1. ​​NOT NULL​​ 约束

    作用：禁止字段值为 NULL​（空值），强制必须有有效数据输入

    CREATE TABLE Users (id INTEGER PRIMARY KEY,name TEXT NOT NULL,  -- 禁止 name 为空age INTEGER
);-- 插入时若 `name` 未赋值，操作将失败

  2. ​​DEFAULT​​ 约束

    作用：字段未指定值时，自动填充预设默认值。

    CREATE TABLE Orders (id INTEGER PRIMARY KEY,amount REAL DEFAULT 0.0,  -- 未赋值时默认为 0.0created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

  3. ​​UNIQUE​​ 约束

    作用：字段未指定值时，自动填充预设默认值。

    CREATE TABLE Products (id INTEGER PRIMARY KEY,sku TEXT UNIQUE,  -- sku 值必须唯一price REAL
);
--常用于标识码、邮箱等唯一性字段

  4. ​​PRIMARY KEY​​ 约束

    作用：字段未指定值时，自动填充预设默认值。

    CREATE TABLE OrderItems (order_id INTEGER PRIMARY KEY,--主键product_id INTEGER,quantity INTEGER
);
--单字段主键直接在列后声明；复合主键需单独定义

  5. ​​CHECK​​ 约束

    作用：字段未指定值时，自动填充预设默认值。

    CREATE TABLE Employees (id INTEGER PRIMARY KEY,age INTEGER CHECK (age >= 18),     -- 年龄必须 ≥18salary REAL CHECK (salary > 0)     -- 工资必须是正数
);
--不满足验证的数据,入库失败

  6. ​​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 约束的特殊性

  1. 主键可为 ​NULL​​

    • SQLite 允许主键值为 NULL​（违反 SQL 标准），但会导致行为不一致，实践中应显式声明 ​NOT NULL​​

  2. 约束删除限制

    • SQLite 不支持直接删除约束（如 ALTER TABLE DROP CONSTRAINT​）。
    • 替代方案：重建表或新建表迁移数据

  3. 空值与 ​NULL​​ 的区别

    • 空值 ('')：有效字符串，不占用存储空间
    • ​​NULL​​：表示“未知”，占用空间，索引效率低
    • 查询时空值用 = ''​ 判断，NULL​ 需用 IS NULL​

‍

SQLite 索引

什么是索引?

索引是一种高效查找数据的专用数据结构，它通过创建特定字段的排序副本，使数据库引擎能快速定位数据位置
本质：数据库的"目录系统"

graph LR A[查询请求] --> B{是否有索引?} B -->|有索引| C[通过索引树快速定位] B -->|无索引| D[全表扫描] C --> E[返回结果] D --> E

‍

索引保存在哪里?

SQLite 将所有索引与表数据统一存储在同一个数据库文件中，采用模块化页管理：

数据库文件结构：
├── 数据库头 (100字节)
├── 表B-tree页 (存储实际行数据)
├── 索引B-tree页 (索引数据结构)
└── 空闲页列表

  1. B-tree/B+tree 结构

    • 平衡树结构保证查找效率 O(log n)
    • 叶子节点存储：索引键值 + 对应 ROWID

  2. 索引页组成

       # 索引页内存结构示例class IndexPage:page_type: int    # 0x02表示索引页cell_count: int   # 当前页存储的索引条目数cells: list       # 索引条目数组right_pointer: int # 右子树指针

  3. 与表数据的关联

    • 每个索引条目包含 ROWID（行唯一标识）
    • 通过 ROWID 回表获取完整行数据

‍

graph LRA[数据表 orders] -->|行数据| B[ROWID=1, amount=100, created_at='2025-01-01']C[索引 idx_created] -->|索引项| D['2025-01-01' → ROWID=1]

  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);

执行步骤：

  1. 解析列定义
  2. 创建 B-tree 结构
  3. 扫描全表提取(price, ROWID)对
  4. 按键值排序插入 B-tree
  5. 更新 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");

‍

索引最佳实践

索引如何设计?

检索有大量重复数据的字段，不适合建立索引，反而会导致检索速度变慢，因为扫描索引节点的速度比全表扫描要慢。
例如表中有 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*1.1 > 100; --无法使用price索引
SELECT * FROM products WHERE price > 100/1.1; --可以使用price索引
可以避免索引的情况:
  1. 小表全扫描更快（<1000 行）
  2. 低频查询字段
  3. 超高频写表（索引维护代价>收益）
  4. text/blob 大字段（用全文索引替代）

‍

多索引使用

和全表扫描/只使用一个索引的速度比起来，去分析两个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是用一个索引.

SQLite 优化器会在以下情况考虑多索引：

  1. WHERE 子句包含多个独立条件（使用 AND 连接）
  2. 每个条件都有适用的单列索引
  3. 没有更优的复合索引可用
  4. 优化器判断多索引扫描比全表扫描更快
-- 假设存在两个索引：
--   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 LRA[索引扫描] --> B[直接获取查询列数据]B --> C[返回结果集]

回表查询: 当 SQL 引擎无法直接从索引获取所有查询数据时，需要回到原始数据表获取完整行的过程。

graph LRA[索引扫描] --> 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'; --需回表查询

执行步骤：

  1. 索引扫描：使用 idx_created​ 定位符合日期条件的记录
  2. 获取 ROWID：从索引中读取对应行的 ROWID​ (即主键 id)
  3. 回表访问：根据 ROWID 回 orders ​表读取整行数据
  4. 提取数据：从行数据中获取 customer_id, amount, status​
  5. 返回结果：组合数据返回结果集

‍

sequenceDiagramparticipant C as Clientparticipant E as SQL Engineparticipant 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: 提取所需列endE-->>C: 返回结果集

‍

‍

索引覆盖示例:

-- 创建覆盖索引
CREATE INDEX idx_covering ON orders(created_at, customer_id, amount);SELECT customer_id, amount FROM orders WHERE created_at > '2025-08-01';

执行步骤：

  1. 索引扫描：使用 idx_covering​ 定位符合条件的索引条目
  2. 直接获取数据：从索引直接读取 customer_id, amount​ 值
  3. 返回结果：组合数据返回结果集

‍

sequenceDiagramparticipant C as Clientparticipant E as SQL Engineparticipant I as idx_covering索引C->>E: 发送查询请求E->>I: 查找 created_at > '2025-08-01'loop 每行处理I-->>E: 直接返回customer_id, amountendE-->>C: 返回结果集

‍

‍

  • 索引覆盖示例
  • -- 创建覆盖索引
CREATE INDEX idx_msg_cover ON messages(chat_id, created_at DESC, content, sender_id);-- 使用覆盖索引的查询
EXPLAIN QUERY PLAN
SELECT id, content 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;

‍

验证索引覆盖

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;
  1. ​idx_chat_msg_cover ​索引覆盖了 chat_id​、id ​和 content ​字段
  2. 查询条件 WHERE chat_id=1234 ​可以直接使用索引过滤
  3. ​ORDER BY id DESC ​可以利用索引的降序排列
  4. ​LIMIT 100 ​只需扫描索引前 100 条记录
  5. 无需回表操作，直接从索引获取所需数据

‍

graph TBQ[查询] --> I[使用idx_chat_msg_cover索引]I --> F[按chat_id=1234过滤]F --> S[按id DESC排序]S --> L[取前100条]L --> R[返回id和content]

‍

‍

总结

  1. 设计优化

    • 数据结构规范, 避免数据冗余和不一致(字段定义不一致, 入库值不一致)
    • 根据数据特性选择合适的数据类型, 使用 INTEGER 代替 TEXT 提高查询性能
    • 合理的索引提高查询速度, 需要平衡储存占用和数据入库的性能影响
    •    CREATE TABLE messages_2025 (id INTEGER PRIMARY KEY,chat_id INTEGER REFERENCES chats(id),content TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);

  2. 查询优化

    • 避免查询全部字段(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;

  3. 合理缓存

    • 合理利用索引覆盖
    • 应用层缓存
    • 非必要不查询: 接口返回数据-> 入库-> 查库
    • graph TBA[收到新消息,显示用户昵称和头像] --> B{内存缓存}B -->|存在| C[直接更新UI]B -->|不存在| D[查询数据库]D --> E[更新缓存]E --> C

  4. 事务使用

    • 多个操作合并事务, 减少操作

    • 减少事务数据的大小, 大量数据锁库时间更久

    • 场景 策略
      单条消息发送 自动提交事务
      批量导入历史消息 每 1000 条手动提交事务

  5. 编程实践

    • 使用连接池
    • 避免循环操作数据库
    • 预编译 SQL,提高重复执行性能
    • 捕获错误, 合理处理
    •    # 预编译语句示例（Python）stmt = db.prepare("INSERT INTO messages(chat_id, content) VALUES (?, ?)")for msg in new_messages:stmt.execute((msg.chat_id, msg.content))

‍

http://www.sczhlp.com/news/7321/

相关文章：

  • 【Terminal】清空PowerShell历史命令
  • 8.7
  • 用小工具轻松跨越语言障碍的好办法VideoTrans
  • idea激活教程(保姆级教程)
  • Java面向对象——8.静态字段和静态方法
  • SM341700
  • 安卓系统禁用IPV6
  • SecureCRT激活教程（保姆级教程）
  • OpenAI Realtime API 详解：构建低延迟多模态交互体验（Beta版）
  • 抗体筛选服务｜高通量抗体检测｜单克隆抗体开发
  • apisix~redirect重定向插件
  • 深入解析：基于rk3588编译opencv支持GStreamer硬件加速
  • 防止NLP模型更新在特定任务上性能倒退
  • 就这样
  • js算法-拓扑排序
  • 规格驱动开发MCP初探
  • 抽象概念太难讲？用可视化让技术原理一目了然
  • 推荐FlyEnv一体化全栈环境管理工具, 帮助开发者快速设置部署本地开发环境
  • 图数据-拓扑排序
  • 【日记】已经八月了呢（830 字）
  • 多线程开发
  • Linux部署RocketMQ Dashboard，以及开启ACL权限控制
  • 基于DWT、矢量量化和自动分片的遥感图像半脆弱水印技术
  • keil MDK中debug时程序无法自行运行的问题
  • 前端-html+CSS基础到高级(六)html列表标签和表格标签 - 努力-
  • 小白编码知识乱码应对方案
  • html+CSS基础到高级(七)input表单标签 - 努力-
  • 国产化Excel处理组件Spire.XLS教程：使用 C# 将 DataTable 导出为 Excel 文件
  • 压缩感知中的OMP算法的实现
  • Helm 常用命令