13、索引的创建与使用：CREATE INDEX、索引类型（B-tree、Hash）、查询性能优化、EXPLAIN QUERY PLAN
索引这东西，说白了就是数据库的「目录」。你想想看，一本几百页的书，如果没有目录，你要找一个关键词就得从头翻到尾——这就是全表扫描。有了索引，直接翻到对应页码，效率天差地别。
我在嵌入式项目里吃过不少亏。有一次，一个设备上的SQLite数据库只有几万条记录，但查询慢得像蜗牛。后来一查，发现每次查询都在做全表扫描。加了个索引之后，速度提升了上百倍。嗯，从那以后，我建表之前都会先想想：哪些字段会被频繁查询？
13.1 索引的本质与分类
索引是一种独立于表存储的数据结构。它保存了表中某些列的值，以及这些值对应的行在表中的物理位置。查询时，数据库先查索引，再通过索引找到实际数据行。
SQLite支持两种索引类型：B-tree索引和Hash索引。不过说实话，SQLite默认只实现了B-tree索引。Hash索引在SQLite中是通过特殊的虚拟表模块实现的，日常开发中几乎用不到。
| 索引类型 | 底层结构 | 适用场景 | 特点 |
|---|---|---|---|
| B-tree | 平衡多路搜索树 | 范围查询、排序、模糊匹配 | 支持 >、<、BETWEEN、LIKE |
| Hash | 哈希表 | 等值查询（=） | 不支持范围查询，内存占用高 |
我个人习惯，99%的场景用B-tree就够了。Hash索引虽然等值查询更快，但嵌入式设备内存有限，Hash表占用的内存往往比B-tree大得多。
13.2 创建索引：CREATE INDEX
创建索引的语法很简单，但用起来有讲究。先看基本用法：
-- 单列索引
CREATE INDEX idx_user_name ON users(name);
-- 唯一索引（不允许重复值）
CREATE UNIQUE INDEX idx_user_email ON users(email);
-- 复合索引（多列）
CREATE INDEX idx_user_age_name ON users(age, name);
-- 部分索引（只索引满足条件的行）
CREATE INDEX idx_active_users ON users(status) WHERE status = 1;这里我要特别强调一下复合索引的列顺序。我在项目中遇到过这样一个坑：有个查询条件是 WHERE age = 25 AND name LIKE '张%'，我建了 INDEX(age, name)，查询很快。但另一个查询是 WHERE name = '张三' AND age = 25，这个索引也能用，因为SQLite的查询优化器会调整顺序。
但如果你建的是 INDEX(name, age)，而查询条件是 WHERE age = 25（没有name条件），这个索引就废了。为什么？因为B-tree索引是按最左前缀原则工作的。第一个列没有约束，索引就不知道怎么走。
我曾经在一个日志表上建了5个索引，结果插入一条记录要花200毫秒。因为每次插入都要更新所有索引。索引不是越多越好，写频繁的表要克制。
13.3 索引类型详解：B-tree vs Hash
B-tree索引，说白了就是一棵平衡树。每个节点存储多个键值，叶子节点存储指向数据行的指针。因为树是平衡的，查找任何数据的时间复杂度都是O(log n)。
Hash索引呢？它把键值通过哈希函数映射到一个固定大小的数组上。等值查询时，直接计算哈希值就能定位，时间复杂度O(1)。但范围查询就抓瞎了——哈希值没有顺序关系。
我画了一张图，帮你理解这两种结构的区别：
嵌入式设备上，优先用B-tree。Hash索引虽然快，但内存开销大，而且不支持范围查询。你想想看，一个物联网设备上报的数据，查询时经常要查「最近一小时的数据」，这就是范围查询，Hash搞不定。
13.4 查询性能优化：EXPLAIN QUERY PLAN
怎么知道你的索引有没有生效？SQLite提供了一个利器：EXPLAIN QUERY PLAN。它能告诉你查询计划是怎么走的。
-- 先建表
CREATE TABLE sensor_data (
id INTEGER PRIMARY KEY,
device_id INTEGER,
temperature REAL,
timestamp INTEGER
);
-- 插入一些测试数据
INSERT INTO sensor_data VALUES (1, 101, 25.3, 1700000000);
INSERT INTO sensor_data VALUES (2, 102, 26.1, 1700000100);
-- ... 更多数据
-- 查看没有索引时的查询计划
EXPLAIN QUERY PLAN
SELECT * FROM sensor_data WHERE device_id = 101;
-- 输出: SCAN TABLE sensor_data （全表扫描！）
-- 创建索引
CREATE INDEX idx_device ON sensor_data(device_id);
-- 再次查看查询计划
EXPLAIN QUERY PLAN
SELECT * FROM sensor_data WHERE device_id = 101;
-- 输出: SEARCH TABLE sensor_data USING INDEX idx_device (device_id=?)看到区别了吗？SCAN是全表扫描，SEARCH ... USING INDEX就是用了索引。我一般写查询语句之前，都会先跑一遍EXPLAIN，确认索引被用上了。
13.5 实战：索引优化案例
有一次，我在一个智能家居项目里遇到了性能问题。设备状态表有50万条记录，查询某个设备最近100条状态要花3秒钟。用户按一下开关，等3秒才看到状态更新，这谁受得了？
原始查询是这样的：
SELECT * FROM device_status
WHERE device_id = 205
ORDER BY timestamp DESC
LIMIT 100;我建了索引：
CREATE INDEX idx_device_time ON device_status(device_id, timestamp DESC);注意这里用了DESC，因为查询是按时间倒序。加上索引后，查询时间从3秒降到了5毫秒。嗯，这就是索引的威力。
- 选择性高的列优先：比如设备ID（有1000种值）比状态（只有3种值）更适合建索引
- 复合索引的列顺序：等值条件放前面，范围条件放后面
- 覆盖索引：如果查询的列都在索引里，数据库就不需要回表查数据了
- 避免过多索引：每个索引都会拖慢写入速度
13.6 索引的维护与删除
索引不是建好就完事了。随着数据不断插入、更新、删除，B-tree会变得不平衡，索引效率会下降。这时候需要重建索引：
-- 重建所有索引
REINDEX;
-- 重建指定索引
REINDEX idx_device_time;
-- 删除索引
DROP INDEX IF EXISTS idx_device_time;我一般会在数据批量导入之后，执行一次REINDEX。因为批量插入时，索引是逐条更新的，会产生很多碎片。重建一下，查询性能能恢复不少。
在嵌入式设备上，REINDEX会占用大量CPU和内存。如果设备正在处理实时数据，最好安排在系统空闲时执行。我曾经在设备上报高峰期跑REINDEX，结果导致数据积压，差点把缓冲区撑爆了。
好了，索引这块的内容就这些。记住一句话：索引是查询加速器，但不是万能药。用对了，性能飞升；用错了，反而拖累系统。多跑EXPLAIN，多观察实际效果，慢慢你就能找到感觉。