数据库原理与选型 / 系统课时

索引结构与查询加速原理

索引结构与查询加速原理

索引的价值不是“让所有查询都更快”,而是用额外存储和写入维护成本,换取更少的数据页访问。本节从 SQLite 的 B-tree 组织方式出发,再用查询计划验证一次从全表扫描到索引查找的变化。

学习目标

  • 理解 SQLite 普通索引如何组织键值与行定位信息
  • 使用 EXPLAIN QUERY PLAN 判断查询是否利用索引
  • 评估索引对读取速度、写入成本和数据库体积的影响

索引到底保存了什么

SQLite 以页为单位组织 B-tree。对普通 rowid 表,表本身按 rowid 组织;二级索引则按“索引列值 + rowid”排序。查询先在索引 B-tree 中定位匹配项,再根据 rowid 回到表中取未包含在索引里的列。

SQLite 二级索引从 B-tree 到表记录的查找路径

这种有序结构让等值和范围条件不必从第一行检查到最后一行。索引并不复制整张表,而是保存用于排序和定位的字段;如果查询还需要其他列,就会发生“先查索引、再查表”的第二步。若所需列都已经在索引中,SQLite 可能直接使用 covering index 返回结果。

用查询计划观察加速方式

下面先创建 10000 行测试数据并查看无索引计划,再为 email 创建索引后重复同一查询。EXPLAIN QUERY PLAN 不执行查询本身,而是告诉你优化器准备怎样访问数据。

sql
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    display_name TEXT NOT NULL
);

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM seq
    WHERE n < 10000
)
INSERT INTO users(id, email, display_name)
SELECT
    n,
    printf('user%05d@example.com', n),
    printf('User %05d', n)
FROM seq;

EXPLAIN QUERY PLAN
SELECT id, display_name
FROM users
WHERE email = 'user09000@example.com';

CREATE INDEX idx_users_email ON users(email);

EXPLAIN QUERY PLAN
SELECT id, display_name
FROM users
WHERE email = 'user09000@example.com';

SELECT id, display_name
FROM users
WHERE email = 'user09000@example.com';

预期结果:

text
<ID>|<ID>|0|SCAN users
<ID>|<ID>|0|SEARCH users USING INDEX idx_users_email (email=?)
9000|User 09000

<ID> 是查询计划节点编号,SQLite 版本不同可能变化。关键不是编号,而是访问方式从 SCAN users 变成 SEARCH users USING INDEX idx_users_email:前者需要检查整表,后者先利用有序索引缩小候选范围。

为什么索引也会拖慢系统

每增加一个索引,SQLite 都要额外保存一棵索引 B-tree。INSERT 必须把新键插入对应索引,DELETE 要移除键,UPDATE 如果修改了索引列,还要同步删除旧键并写入新键;这些操作可能引起更多页面写入和页分裂。

因此索引的代价主要有三类:数据库文件更大、写事务工作更多、维护和分析成本增加。对写多读少的表,盲目给每列建索引可能让整体性能变差;对读多且过滤条件稳定的表,针对高频谓词建立少量索引通常更划算。

哪些查询更可能受益

索引优先服务于经常出现在 WHERE、JOIN、ORDER BY 或某些分组路径中的列,尤其是能显著缩小结果集的条件。联合索引还受列顺序影响,通常应围绕真实查询模式设计,而不是按字段重要性排序。

低选择性的条件不一定值得单独索引。例如一个只有“启用/停用”两种值的列,即使建了索引,查询大量匹配行时优化器仍可能认为扫描整表更便宜。最终判断应以 EXPLAIN QUERY PLAN 和真实数据上的耗时、读页数量为准。

还要区分显式索引与约束产生的自动索引:UNIQUE 以及某些主键约束会让 SQLite 创建内部索引,而 INTEGER PRIMARY KEY 在普通 rowid 表中直接作为 rowid 的别名,通常不需要再为同一列建立重复索引。索引真正有效的前提,是它对应了实际查询路径,而不是仅仅“存在”。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

索引结构与查询加速原理

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通