在 SQLite 中，索引是一种用于加速查询操作的数据结构。它通过创建数据表中特定列的指针来减少全表扫描的需要，从而大幅提高查询性能。本文将介绍 SQLite 中索引的使用策略、创建方法、优化技巧及常见问题。

📌 本文由 www.52kanjuqing.com 整理，仅此一次引用。

📚 目录

  1. 什么是索引？
  2. SQLite 中的索引类型
  3. 如何创建索引
  4. 自动创建索引与手动创建索引
  5. 索引的优势与限制
  6. 使用索引优化查询性能
  7. 联合索引与覆盖索引
  8. 索引的维护与删除
  9. 出站参考链接与站内推荐
  10. 参考资料

1. 什么是索引？

索引是一种数据库对象，用于提高查询性能。索引本质上是一个排序的数据结构，允许数据库快速定位特定记录，而无需遍历整个数据表。

常见类型：

  • 单列索引：针对单个列创建索引
  • 多列（联合）索引：同时索引多个列
  • 唯一索引：确保索引列的值唯一
  • 全文索引：适用于全文搜索

2. SQLite 中的索引类型

SQLite 支持以下几种索引类型：

类型说明示例
普通索引基础的索引类型，用于提高查找效率CREATE INDEX idx_name ON table_name(column);
唯一索引强制索引列的值唯一，类似主键CREATE UNIQUE INDEX idx_name ON table_name(column);
聚集索引默认情况下，主键列具有聚集索引PRIMARY KEY 自带聚集索引
全文索引用于高效的全文搜索CREATE VIRTUAL TABLE ... USING fts4(...);

3. 如何创建索引

使用 CREATE INDEX 语法来创建索引：

CREATE INDEX idx_column_name ON table_name(column_name);

示例：

CREATE INDEX idx_user_age ON users(age);

通过 UNIQUE 关键字创建唯一索引：

CREATE UNIQUE INDEX idx_email ON users(email);

4. 自动创建索引与手动创建索引

  • 自动创建索引：当你创建主键或唯一约束时，SQLite 会自动为相关列创建索引。 CREATE TABLE users ( id INTEGER PRIMARY KEY, -- 自动创建索引 email TEXT UNIQUE -- 自动创建唯一索引 );
  • 手动创建索引：针对查询频繁的非主键列，可以手动创建索引以加速查询。

5. 索引的优势与限制

优势：

  • 查询加速：索引通过减少扫描的行数，显著提高查询效率。
  • 唯一性约束：保证数据的唯一性，如主键和唯一索引。

限制：

  • 插入、更新和删除性能下降：每次数据变化时，索引也需要更新。
  • 占用存储空间：索引会消耗额外的磁盘空间，尤其是对于大表。

6. 使用索引优化查询性能

  • WHERE 子句优化：在 WHERE 子句中涉及到索引列时，SQLite 会自动选择使用索引。 SELECT * FROM users WHERE age = 25; -- 会使用索引 idx_user_age
  • 避免全表扫描：使用索引可以避免对整个数据表的全表扫描，尤其在数据量大时非常显著。
  • 索引覆盖查询：当查询的所有列都在索引中时，SQLite 可以直接从索引获取数据，而无需访问数据表。

7. 联合索引与覆盖索引

联合索引：

联合索引是对多个列进行索引，这对于优化涉及多个列的查询非常有用。

CREATE INDEX idx_user_name_age ON users(name, age);

覆盖索引：

当查询只涉及索引中的列时，SQLite 可以通过索引直接返回结果，而无需访问原始表。此时，查询被称为覆盖查询。

SELECT name FROM users WHERE age = 25;
-- 如果 age 和 name 都在 idx_user_name_age 中，SQLite 可以仅使用该索引。

8. 索引的维护与删除

更新索引：

索引不会自动更新。当插入、更新或删除数据时，SQLite 会自动更新索引。

删除索引：

DROP INDEX IF EXISTS idx_column_name;

示例：

DROP INDEX IF EXISTS idx_user_age;

9. 🔗 出站参考链接与站内推荐

官方文档与权威资料：

站内推荐阅读（www.52kanjuqing.com）：

10. 📚 参考资料

  1. SQLite 官方文档：CREATE INDEX
    https://www.sqlite.org/lang_createindex.html
  2. SQL Performance Explained – Markus Winand
  3. SQL Server Indexes – Microsoft Documentation
  4. Stack Overflow – sqlite-index tag

✅ 下一篇是 《SQLite Indexed By：优化特定查询的索引策略》，它将深入探讨如何使用 INDEXED BY 指定查询使用特定的索引，以进一步优化查询性能。如果你有任何特定的需求或优化场景，可以告诉我，我将定制化内容。继续吗？