在SQLite数据库里，索引并不是表结构的一部分，而是一种独立的物理存储对象。它以B树或B+树的形式存储被索引列的值和对应的rowid，查询优化器在处理WHERE、JOIN、ORDER BY和GROUP BY等操作时，可以借助索引快速跳过无关数据页。理解CREATE INDEX的语法细节，能够帮助我们在合适的位置构建真正有效的索引。
CREATE INDEX基础语法与索引类型
SQLite创建索引的基本语句格式如下：
CREATE INDEX index_name ON table_name(column_list);
这里的index_name必须在数据库内唯一，column_list可以包含一个或多个列名，多个列之间用逗号分隔。默认情况下，SQLite创建的是普通B树索引，允许重复键值。如果业务上要求某一列或某些列组合的值不能重复，可以在CREATE INDEX后面加上UNIQUE关键字，创建唯一索引。例如：
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
唯一索引除了加速查询之外，还承担了数据完整性约束的角色。当用户尝试插入或更新重复的email值时，SQLite会直接抛出约束错误。需要注意的是，如果表中已经存在重复数据，唯一索引的创建会失败，必须先清理数据。另一个常用的变体是表达式索引，它允许对列经过函数计算后的结果建立索引。比如用户经常按邮箱的小写形式进行查询，可以这样创建：
CREATE INDEX idx_users_email_lower ON users(lower(email));
这类索引只在查询条件中使用了完全相同的表达式时才会被启用。如果查询中写的是lower(email)，优化器可以命中这个索引；但如果写成了email LIKE 'xxx%'，则无法利用表达式索引。部分索引则适用于只关心表中一部分记录的查询场景，通过WHERE子句限定索引覆盖的数据范围。例如订单表中大部分订单已经完成，但经常需要查询未支付订单的创建时间，可以创建：
CREATE INDEX idx_orders_unpaid_created ON orders(created_at) WHERE status = 'unpaid';
部分索引的体积更小，写入时维护成本也更低，适合数据分布有明显冷热区分的表。
复合索引的列顺序与前缀匹配
复合索引是指在多个列上创建的索引，它的关键点在于列的顺序。SQLite的B树索引按照索引定义时的列顺序依次排序，因此查询条件只有符合最左前缀原则时，索引才能被有效利用。举例来说，假设我们在用户表上创建了复合索引：
CREATE INDEX idx_users_city_age ON users(city, age);
这个索引按照city排序，相同city再按age排序。如果查询是WHERE city = 'Beijing' AND age大于25，优化器可以使用索引定位到Beijing段，然后在该段内继续扫描age大于25的记录。如果查询只有WHERE age大于25而没有city条件，索引就失去了排序的起点，SQLite通常不会选择这个索引，而是回退到全表扫描。同理，如果查询条件是WHERE city = 'Beijing' ORDER BY age，这个索引也能发挥作用，因为数据在city相同的情况下已经按age排好序，可以避免额外的排序操作。
对于三列复合索引(a, b, c)，能够利用索引的查询模式只有三种：包含a、包含a和b、包含a和b和c。只查询b或c，或者查询b和c的组合，都无法触发索引。因此创建复合索引之前，需要先分析最常用的查询条件组合，把等值过滤的列放在前面，范围过滤或排序的列放在后面。例如经常出现WHERE status = 'active' AND created_at > '2025-01-01' ORDER BY created_at的查询，就可以创建索引(status, created_at)，这样既能过滤status，又能利用created_at的有序性完成范围扫描和排序。
此外，复合索引的列数量不宜过多。虽然SQLite允许最多2000列参与索引，但索引列越多，每次写入时需要更新的B树节点越多，空间占用也越大。通常建议将复合索引控制在3列以内，除非有非常明确的查询模式。
从查询计划判断索引使用场景
仅仅创建索引并不意味着SQLite一定会使用它。查询优化器会根据统计信息、索引选择性和查询代价估算来决定是否走索引。SQLite提供了EXPLAIN QUERY PLAN命令，让我们可以直接查看某条SQL语句的执行计划。例如：
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'test@ipipp.com';
执行结果中如果出现SCAN TABLE users，说明没有使用索引，而是全表扫描；如果出现SEARCH TABLE users USING INDEX idx_users_email，则说明索引被命中。这个工具非常实用，尤其在排查慢查询时，可以快速判断是索引缺失还是索引失效。
值得建立索引的典型场景包括：高基数列上的等值查询、范围查询中的有序扫描、频繁的排序和分组操作、以及能够形成覆盖索引的查询。所谓覆盖索引，指的是查询需要的所有列都包含在索引中，SQLite无需回表读取数据行。比如查询SELECT email FROM users WHERE email = 'x'，如果只在email上建了索引，这个查询可以直接从索引树中拿到email值，不需要访问原始表。覆盖索引可以大幅减少磁盘I/O，特别是在表行数很大时效果显著。
但并不是所有列都适合建索引。当一列的值重复度很高，例如性别、状态等低基数列，索引的选择性很差，扫描索引再回表的成本可能高于直接全表扫描。对于小数据量表，SQLite也可能选择全表扫描，因为此时索引的B树查找开销并不比遍历整张表低。此外，对于频繁写入、更新或删除的表，每个索引都会增加维护开销，需要权衡查询收益和写入代价。
索引维护成本与常见误区
SQLite的索引并非一次性创建完成就一劳永逸。每当对表执行INSERT、UPDATE或DELETE操作时，所有涉及的索引都必须同步更新。在插入一行数据时，如果表中存在5个索引，数据库就需要额外维护5棵B树，写入性能会明显下降。因此，在OLTP类型的高并发写入场景下，每多一个索引都可能导致写入延迟上升、事务竞争加剧。对于历史归档表或只读分析表，这种负担较小，可以适当多建索引。
一个常见的误区是给每一列都单独创建索引。这种方式看似全面，实际上会导致写入性能急剧恶化，同时占用大量磁盘空间。SQLite的查询优化器在一次查询中通常只会为一张表选择一个最合适的索引，其他索引不仅不会被用到，还会增加存储和维护成本。更好的做法是以实际的查询模式为依据，合并多个查询条件创建复合索引，并利用覆盖索引减少回表。
另一个容易忽略的问题是索引碎片和统计信息更新。SQLite内部会维护索引的统计信息，随着数据量变化，优化器可能会重新评估索引选择。对于大量删除或更新操作后，索引页面可能出现空间浪费，此时可以执行REINDEX命令重建索引，也可以使用VACUUM整理数据库文件。需要注意的是，REINDEX会锁定表，因此应在业务低峰期执行。对于绝大多数中小型应用来说，SQLite自动维护的索引统计信息已经足够可靠，不必频繁重建。
在真实项目中，建议先通过慢查询日志或EXPLAIN QUERY PLAN定位到消耗较高的SQL，再根据WHERE、ORDER BY和GROUP BY中的列组合创建索引，然后用EXPLAIN再次验证索引是否被采用。迭代式的索引优化比一次性创建大量索引更能平衡读写性能。
SQLite索引CREATE INDEX数据库性能优化修改时间：2026-09-20 08:37:41