DevNull 知识库

Appearance

34｜SQL 索引与事务 ​

数据量大了之后，查询速度会变慢。索引是数据库的"目录"——通过预排序的数据结构，让数据库不需要扫描整张表就能定位到目标记录。事务则保证一组操作要么全部成功、要么全部回滚，防止数据处于不一致的中间状态。

一、索引 ​

创建索引 ​

sql
CREATE INDEX idx_servers_status ON servers(status);
CREATE INDEX idx_servers_project ON servers(project_id);

索引的名字通常以 idx_表名_列名 命名。创建后，按 status 或 project_id 查询时会利用索引加速。

唯一索引 ​

sql
CREATE UNIQUE INDEX idx_servers_hostname ON servers(hostname);

唯一索引同时约束列值不能重复，尝试插入重复值会报错。

主键索引 ​

声明 PRIMARY KEY 时，数据库会自动创建索引：

sql
CREATE TABLE servers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,   -- 自动创建主键索引
    hostname TEXT
);

复合索引 ​

多列联合查询时，创建包含多列的索引：

sql
CREATE INDEX idx_servers_status_env ON servers(status, env);

复合索引的列顺序很重要。上面的索引对 WHERE status = ? 和 WHERE status = ? AND env = ? 有效，但对单独的 WHERE env = ? 无效。

查看查询是否使用索引 ​

SQLite 用 EXPLAIN QUERY PLAN：

sql
EXPLAIN QUERY PLAN SELECT * FROM servers WHERE status = 'running';

输出中出现 USING INDEX idx_servers_status 表示使用了索引；出现 SCAN TABLE servers 表示全表扫描。

索引的代价 ​

索引不是免费的：

收益代价
查询加速占用额外磁盘空间
唯一约束插入、更新、删除时需要维护索引，变慢

不要给每一列都建索引。适合建索引的列：

  • WHERE 条件中频繁使用的列
  • JOIN 的关联列
  • 需要排序（ORDER BY）的列
  • 主键和外键

不适合建索引的列：

  • 取值很少的列（如只有 true/false）
  • 很少查询的列
  • 频繁更新的大文本列

二、事务 ​

事务是一组操作的集合，满足 ACID 特性：

特性含义
原子性（Atomicity）要么全部成功，要么全部回滚
一致性（Consistency）事务前后数据处于合法状态
隔离性（Isolation）并发事务互不干扰
持久性（Durability）提交后数据永久保存

基本用法 ​

python
conn = sqlite3.connect("ops.db")

try:
    cursor = conn.cursor()

    # 扣减库存
    cursor.execute(
        "UPDATE inventory SET count = count - ? WHERE item_id = ?",
        (1, 100),
    )

    # 记录日志
    cursor.execute(
        "INSERT INTO logs (action, item_id) VALUES (?, ?)",
        ("deduct", 100),
    )

    conn.commit()   # 提交，两组操作同时生效
except Exception:
    conn.rollback()   # 出错时回滚，两组操作都不生效
    raise
finally:
    conn.close()

事务的边界 ​

SQLite 默认在非自动提交模式下运行：

python
conn = sqlite3.connect("ops.db")
# conn.isolation_level = "DEFERRED"  # 默认

# 第一条 DML 语句（INSERT/UPDATE/DELETE）自动开启事务
cursor.execute("INSERT INTO logs ...")   # 事务开始

# commit 前，其他连接看不到这条记录
cursor.execute("INSERT INTO logs ...")
conn.commit()   # 事务结束，数据对所有连接可见

上下文管理器 ​

Python 3.12+ 的 sqlite3 支持连接作为上下文管理器：

python
with sqlite3.connect("ops.db") as conn:
    cursor = conn.cursor()
    cursor.execute("UPDATE inventory SET count = count - 1 WHERE item_id = 100")
    cursor.execute("INSERT INTO logs ...")
    # 退出 with 块时自动 commit，异常时自动 rollback

并发与隔离级别 ​

SQLite 的并发模型：

模式特点
单线程一个连接同时只能被一个线程使用
多线程连接不能跨线程共享
序列化完全串行执行（默认，最安全）

FastAPI 的并发请求如果共用一个 SQLite 连接，会触发 OperationalError: database is locked。解决方案：

python
# 每个请求单独创建连接（最简单，但性能差）
def get_db():
    conn = sqlite3.connect("ops.db")
    try:
        yield conn
    finally:
        conn.close()

生产环境推荐换 PostgreSQL 或 MySQL，它们有成熟的连接池和并发控制。

三、常见错误 ​

忘记 commit 导致数据丢失 ​

python
conn = sqlite3.connect("ops.db")
cursor = conn.cursor()
cursor.execute("INSERT INTO logs ...")
# 忘记 conn.commit()
conn.close()   # 数据未保存

异常时没有 rollback ​

python
try:
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
except Exception:
    # 错误：没有 rollback，第一条 UPDATE 可能已经生效
    print("出错")

长事务持有锁 ​

python
# 错误：事务开启后长时间不提交，阻塞其他写入
conn.execute("BEGIN")
cursor.execute("UPDATE ...")
# 执行大量计算...
time.sleep(60)
conn.commit()

# 正确：事务尽量简短，只包含必要的 DML 操作