SQLite 索引设计

索引设计概述

索引是提高数据库查询性能的核心机制，通过创建索引可以加速数据的检索和排序。良好的索引设计可以显著提高查询效率，减少磁盘 I/O，而不合理的索引设计则可能导致性能下降，增加写操作开销。索引设计是 SQLite 数据库性能优化的关键环节，需要根据实际业务需求和查询模式进行精心设计。

索引类型

SQLite 支持多种索引类型，每种类型适用于不同的场景。了解各种索引类型的特点和适用场景是设计高效索引的基础。

B树索引

B树索引是 SQLite 默认的索引类型，适合大多数查询场景：

结构：使用平衡 B 树结构，确保查询、插入、删除操作的时间复杂度为 O(log n)
适用场景：等值查询、范围查询、排序操作
特点：支持多种数据类型，查询效率高，是最常用的索引类型
版本支持：所有 SQLite 版本

R树索引

R树索引用于地理空间数据查询，适合处理多维数据：

结构：使用 R 树结构，将空间对象映射到矩形区域
适用场景：地理空间数据查询、范围查询
特点：加速空间数据的检索，适合 GIS 应用
版本支持：SQLite 3.3.0+

FTS 索引

FTS（Full-Text Search）索引用于全文搜索，适合处理文本数据：

结构：使用倒排索引技术，将文本内容映射到文档 ID
适用场景：文本搜索、文档检索、模糊查询
特点：支持全文搜索、模糊匹配、短语搜索
版本支持：
- FTS3/FTS4：SQLite 3.5.0+
- FTS5：SQLite 3.9.0+

表达式索引

表达式索引基于表达式计算结果创建，而非单个列：

结构：与 B 树索引类似，但基于表达式计算结果
适用场景：频繁使用表达式查询的场景
特点：加速基于表达式的查询，如 lower(name)、date(created_at)
版本支持：SQLite 3.9.0+

部分索引

部分索引基于表中满足特定条件的行创建：

结构：只包含满足条件的行，索引大小更小
适用场景：只需要索引表中部分数据的场景
特点：减少索引大小，提高查询效率，降低维护成本
版本支持：SQLite 3.8.0+

索引设计原则

选择合适的索引列

为频繁查询的列创建索引：如 WHERE 子句中经常出现的列
为 JOIN 操作的列创建索引：加速表连接操作
为排序和分组的列创建索引：加速 ORDER BY 和 GROUP BY 操作
为唯一性约束的列创建索引：确保数据唯一性，同时加速查询

复合索引设计

复合索引是基于多个列创建的索引，设计时需要注意以下几点：

列顺序：将选择性高的列放在前面，提高索引效率
前缀匹配：复合索引支持前缀匹配，如 (a,b,c) 索引可以用于查询 a、a+b、a+b+c
避免冗余：如果已有 (a,b) 索引，不需要再创建 (a) 索引
覆盖查询：考虑将查询中常用的列包含在索引中，实现覆盖查询

避免过度索引

索引会增加写操作的开销，因此需要避免过度索引：

只创建必要的索引：避免为所有列创建索引
定期清理无用索引：删除不再使用的索引
考虑索引维护成本：索引会增加 INSERT、UPDATE、DELETE 操作的时间

考虑数据分布

选择性高的列：适合创建索引，如用户 ID、邮箱
选择性低的列：不适合创建索引，如性别、状态（只有少数几个值）
频繁更新的列：索引维护成本高，需谨慎考虑

索引创建与管理

创建索引

sql

-- 创建普通索引
CREATE INDEX idx_users_email ON users(email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 创建复合索引
CREATE INDEX idx_orders_user_id_date ON orders(user_id, order_date DESC);

-- 创建表达式索引
CREATE INDEX idx_users_name_lower ON users(lower(name));

-- 创建部分索引
CREATE INDEX idx_orders_active ON orders(order_id) WHERE status = 'active';

-- 创建 FTS5 索引
CREATE VIRTUAL TABLE articles USING fts5(title, content);

查看索引

sql

-- 查看表的所有索引
PRAGMA index_list(users);

-- 查看索引的详细信息
PRAGMA index_info(idx_users_email);

-- 查看索引的创建语句
SELECT sql FROM sqlite_master WHERE type='index' AND name='idx_users_email';

删除索引

sql

-- 删除索引
DROP INDEX IF EXISTS idx_users_email;

-- 删除虚拟表（FTS 索引）
DROP TABLE IF EXISTS articles;

重建索引

对于频繁更新的表，定期重建索引可以提高性能，减少索引碎片：

sql

-- 重建所有索引
REINDEX;

-- 重建特定表的所有索引
REINDEX users;

-- 重建特定索引
REINDEX idx_users_email;

索引使用场景

等值查询

sql

-- 为 email 列创建索引
CREATE INDEX idx_users_email ON users(email);

-- 查询效率高，使用索引加速
SELECT * FROM users WHERE email = 'user@example.com';

范围查询

sql

-- 为 created_at 列创建索引
CREATE INDEX idx_users_created_at ON users(created_at);

-- 查询效率高，使用索引加速
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

排序操作

sql

-- 为 last_name 和 first_name 列创建复合索引
CREATE INDEX idx_users_name ON users(last_name, first_name);

-- 查询效率高，使用索引加速排序
SELECT * FROM users ORDER BY last_name, first_name;

JOIN 操作

sql

-- 为 order_items 表的 product_id 列创建索引
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- 查询效率高，使用索引加速 JOIN 操作
SELECT * FROM order_items oi JOIN products p ON oi.product_id = p.id;

分组操作

sql

-- 为 category_id 列创建索引
CREATE INDEX idx_products_category ON products(category_id);

-- 查询效率高，使用索引加速分组
SELECT category_id, COUNT(*) FROM products GROUP BY category_id;

索引性能优化

索引选择性优化

索引选择性是衡量索引质量的重要指标：

计算选择性：选择性 = 不同值数量 / 总行数
高选择性：选择性 > 0.2，适合创建索引
低选择性：选择性 < 0.1，不适合创建索引

选择性计算示例：

sql

-- 计算 email 列的选择性
SELECT COUNT(DISTINCT email) / COUNT(*) AS selectivity FROM users;

复合索引顺序优化

复合索引的列顺序对查询性能影响很大：

将选择性高的列放在前面：提高索引效率
将频繁查询的列放在前面：支持更多的查询场景
考虑查询模式：根据实际查询模式调整列顺序
示例：对于查询 WHERE user_id = 1 AND status = 'active'，应创建 (user_id, status) 索引，而非 (status, user_id)

避免索引失效

以下情况可能导致索引失效，需要避免：

在索引列上使用函数：如 WHERE lower(name) = 'john'，除非使用表达式索引
使用 OR 连接多个条件：如 WHERE a = 1 OR b = 2，除非为所有列创建索引
使用 != 或 <> 操作符：如 WHERE status != 'active'
使用 LIKE 以通配符开头：如 WHERE name LIKE '%john%'
类型不匹配：如将字符串与数字比较
使用 NOT IN 操作符：如 WHERE id NOT IN (1, 2, 3)

覆盖索引优化

覆盖索引包含查询所需的所有列，避免回表查询，提高查询效率：

sql

-- 查询需要 id、name、email 列
SELECT id, name, email FROM users WHERE email = 'user@example.com';

-- 创建覆盖索引，包含所有需要的列
CREATE INDEX idx_users_email_covering ON users(email, id, name);

版本差异

SQLite 在不同版本中引入了多种索引相关的功能，了解这些差异对于设计兼容不同版本的索引至关重要：

SQLite 3.3.0+

引入 R 树索引支持

SQLite 3.5.0+

引入 FTS3/FTS4 全文搜索索引

SQLite 3.8.0+

引入部分索引支持
优化了 B 树索引的性能

SQLite 3.9.0+

引入 FTS5 全文搜索索引
引入表达式索引支持

SQLite 3.28.0+

优化了索引的查询计划生成
增强了 EXPLAIN QUERY PLAN 的输出

SQLite 3.35.0+

优化了索引的内存使用
增强了部分索引的功能

生产运维最佳实践

索引设计流程

分析查询模式：收集并分析应用程序的查询日志，找出频繁执行的查询
识别热点查询：找出执行时间长、资源消耗大的热点查询
设计候选索引：根据查询模式设计候选索引
测试索引性能：使用 EXPLAIN QUERY PLAN 分析索引使用情况
评估维护成本：考虑索引对写操作的影响
部署和监控：部署索引后监控查询性能和资源消耗

生产环境索引管理

索引审查

定期审查索引使用情况，删除无用索引：

sql

-- 查找未使用的索引（需要开启索引使用统计）
PRAGMA stats_index; -- 查看索引使用统计

-- 查找重复或冗余索引
SELECT 
    tbl_name,
    name AS index_name,
    sql
FROM sqlite_master 
WHERE type='index' 
ORDER BY tbl_name, name;

索引监控

监控索引的使用情况和性能影响：

使用 EXPLAIN QUERY PLAN：分析查询是否使用了预期的索引
监控查询执行时间：比较添加索引前后的查询性能
监控索引大小：过大的索引会增加存储和 I/O 开销
监控写操作性能：索引会影响插入、更新和删除操作的性能

索引优化策略

读密集型应用：可以适当增加索引数量，提高查询性能
写密集型应用：减少索引数量，降低写操作开销
大型表：优先为频繁查询的列创建索引，避免创建过多索引
历史数据表：可以创建更多索引，因为历史数据更新频率低

常见问题（FAQ）

索引越多越好吗？

不是。索引会增加写操作的开销，因此需要平衡查询性能和写操作性能。只创建必要的索引，定期清理无用索引。一般来说，索引数量不应超过表列数量的 20%。

如何确定索引是否被使用？

使用 EXPLAIN QUERY PLAN 语句分析查询计划，查看是否使用了预期的索引。如果查询计划中显示 "SEARCH TABLE ... USING INDEX"，则表示使用了索引；如果显示 "SCAN TABLE"，则表示执行了全表扫描。

sql

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'user@example.com';

如何优化 LIKE 查询？

避免使用以通配符开头的 LIKE 查询，如 LIKE '%john%'
对于以通配符结尾的查询，如 LIKE 'john%'，可以使用 B 树索引
对于需要全文搜索的场景，使用 FTS 索引
考虑使用表达式索引，如 lower(name) 索引

如何处理低选择性的列？

对于低选择性的列，如性别、状态，不建议创建单独的索引。可以考虑：

将其作为复合索引的后缀列
使用部分索引，只索引满足特定条件的数据
对于频繁查询的低选择性列，可以考虑创建索引

如何优化 JOIN 查询的索引？

为 JOIN 条件中的列创建索引
考虑 JOIN 顺序，将小表放在前面
使用覆盖索引，避免回表查询
对于多表 JOIN，考虑创建复合索引

如何重建索引？

使用 REINDEX 语句重建索引：

sql

-- 重建特定索引
REINDEX idx_users_email;

-- 重建表的所有索引
REINDEX users;

-- 重建所有索引
REINDEX;

实际案例分析

案例 1：电商订单查询优化

问题：某电商系统的订单查询 SELECT * FROM orders WHERE user_id = 123 AND status = 'shipped' ORDER BY order_date DESC 执行缓慢。

分析：

查询条件包含 user_id 和 status 列
需要按 order_date 降序排序
缺少合适的索引

解决方案：创建复合索引 (user_id, status, order_date DESC)

sql

-- 创建优化索引
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, order_date DESC);

优化效果：

查询时间从 500ms 降低到 50ms
查询计划从全表扫描变为索引搜索
I/O 操作减少 90%

案例 2：用户搜索优化

问题：用户搜索功能 SELECT * FROM users WHERE lower(name) LIKE 'john%' 执行缓慢。

分析：

查询使用了 lower(name) 表达式
缺少对应的表达式索引
导致全表扫描

解决方案：创建表达式索引 lower(name)

sql

-- 创建表达式索引
CREATE INDEX idx_users_name_lower ON users(lower(name));

优化效果：

查询时间从 300ms 降低到 30ms
使用索引加速查询
支持高效的前缀匹配

案例 3：部分索引优化

问题：某系统中有大量历史订单，查询 SELECT * FROM orders WHERE status = 'active' AND total_amount > 1000 执行缓慢。

分析：

只有少量订单处于 active 状态
全表索引会包含大量历史数据
索引大小过大，查询效率低

解决方案：创建部分索引

sql

-- 创建部分索引
CREATE INDEX idx_orders_active_high_amount ON orders(total_amount) WHERE status = 'active';

优化效果：

索引大小减少 95%
查询时间从 400ms 降低到 40ms
写操作开销降低 80%

总结

索引设计是 SQLite 数据库性能优化的核心环节，需要根据实际业务需求和查询模式进行精心设计。良好的索引设计可以显著提高查询效率，减少磁盘 I/O，而不合理的索引设计则可能导致性能下降。

设计高效索引的关键在于：

选择合适的索引类型
合理设计索引列和复合索引
避免过度索引
考虑数据分布和查询模式
定期审查和优化索引
考虑不同 SQLite 版本的兼容性

通过遵循本文档中的最佳实践和优化建议，您可以设计出高效、可靠的索引，确保 SQLite 数据库在生产环境中表现出色。随着应用程序的发展和数据量的增长，索引设计也需要不断调整和优化，以适应新的查询模式和业务需求。

SQLite 索引设计 ​

索引设计概述 ​

索引类型 ​

B树索引 ​

R树索引 ​

FTS 索引 ​

表达式索引 ​

部分索引 ​

索引设计原则 ​

选择合适的索引列 ​

复合索引设计 ​

避免过度索引 ​

考虑数据分布 ​

索引创建与管理 ​

创建索引 ​

查看索引 ​

删除索引 ​

重建索引 ​

索引使用场景 ​

等值查询 ​

范围查询 ​

排序操作 ​

JOIN 操作 ​

分组操作 ​

索引性能优化 ​

索引选择性优化 ​

复合索引顺序优化 ​

避免索引失效 ​

覆盖索引优化 ​

版本差异 ​

SQLite 3.3.0+ ​

SQLite 3.5.0+ ​

SQLite 3.8.0+ ​

SQLite 3.9.0+ ​

SQLite 3.28.0+ ​

SQLite 3.35.0+ ​

生产运维最佳实践 ​

索引设计流程 ​

生产环境索引管理 ​

索引审查 ​

索引监控 ​

索引优化策略 ​

常见问题（FAQ） ​

索引越多越好吗？ ​

如何确定索引是否被使用？ ​

如何优化 LIKE 查询？ ​

如何处理低选择性的列？ ​

如何优化 JOIN 查询的索引？ ​

如何重建索引？ ​

实际案例分析 ​

案例 1：电商订单查询优化 ​

案例 2：用户搜索优化 ​

案例 3：部分索引优化 ​

总结 ​

SQLite 索引设计

索引设计概述

索引类型

B树索引

R树索引

FTS 索引

表达式索引

部分索引

索引设计原则

选择合适的索引列

复合索引设计

避免过度索引

考虑数据分布

索引创建与管理

创建索引

查看索引

删除索引

重建索引

索引使用场景

等值查询

范围查询

排序操作

JOIN 操作

分组操作

索引性能优化

索引选择性优化

复合索引顺序优化

避免索引失效

覆盖索引优化

版本差异

SQLite 3.3.0+

SQLite 3.5.0+

SQLite 3.8.0+

SQLite 3.9.0+

SQLite 3.28.0+

SQLite 3.35.0+

生产运维最佳实践

索引设计流程

生产环境索引管理

索引审查

索引监控

索引优化策略

常见问题（FAQ）

索引越多越好吗？

如何确定索引是否被使用？

如何优化 LIKE 查询？

如何处理低选择性的列？

如何优化 JOIN 查询的索引？

如何重建索引？

实际案例分析

案例 1：电商订单查询优化

案例 2：用户搜索优化

案例 3：部分索引优化

总结