在 SQLite 中，索引（Index） 是提升 查询性能 的关键数据库对象，尤其在 WHERE、JOIN、ORDER BY 等操作频繁的列上。
一、什么是索引？
索引是一个 独立的数据结构（通常是 B 树），存储某列（或多列）的值及其对应的行位置（ROWID），加速数据检索。
类比：书的目录 → 快速找到章节页码
二、创建索引
基本语法
CREATE [UNIQUE] INDEX [IF NOT EXISTS] 索引名
ON 表名 (列名1 [ASC|DESC], 列名2, ...);| 选项 | 说明 |
|---|---|
UNIQUE | 确保索引列值唯一（违反时报错） |
IF NOT EXISTS | 避免重复创建 |
ASC / DESC | 排序方向（默认 ASC） |
示例
-- 单列索引
CREATE INDEX idx_users_email ON users(email);
-- 多列索引（复合索引）
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);
-- 唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);三、索引类型
| 类型 | 说明 |
|---|---|
| 普通索引 | CREATE INDEX |
| 唯一索引 | CREATE UNIQUE INDEX |
| 复合索引 | 多列联合索引 |
| 部分索引 | 只对满足条件的行建索引 |
| 表达式索引 | 对表达式结果建索引 |
四、高级索引
1. 部分索引（Partial Index）
只为符合 WHERE 条件的行创建索引，节省空间。
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';仅对
status='active'的用户建立
2. 表达式索引（Expression Index）
对 计算结果 建索引。
CREATE INDEX idx_users_name_lower ON users(LOWER(name));
-- 查询时必须匹配表达式
SELECT * FROM users WHERE LOWER(name) = 'alice';
-- 才会命中索引3. 覆盖索引（Covering Index）
索引包含查询所需 所有列，无需回表。
CREATE INDEX idx_covering ON orders(user_id, order_date, total);
SELECT user_id, order_date, total
FROM orders
WHERE user_id = 123;
-- 直接从索引读取数据，超快！五、查看索引
-- 查看所有索引
PRAGMA index_list(表名);
-- 查看索引结构
PRAGMA index_info(索引名);
-- 查看所有索引 SQL
SELECT name, sql FROM sqlite_master WHERE type = 'index';六、删除索引
DROP INDEX IF EXISTS 索引名;七、自动索引？主键 & UNIQUE
| 情况 | 自动创建索引 |
|---|---|
PRIMARY KEY | 自动在主键列建唯一索引 |
UNIQUE 约束 | 自动建唯一索引 |
INTEGER PRIMARY KEY | 就是 ROWID 别名，无额外索引 |
CREATE TABLE users (
id INTEGER PRIMARY KEY, -- 自动索引
email TEXT UNIQUE -- 自动唯一索引
);八、索引使用分析（必备技能）
用 EXPLAIN QUERY PLAN 检查是否命中索引
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'alice@example.com';输出解读：
| 输出 | 含义 |
|---|---|
SEARCH TABLE users USING INDEX idx_users_email | 命中索引 |
SCAN TABLE users | 全表扫描，慢！ |
九、最佳实践（Best Practices）
| 建议 | 说明 |
|---|---|
在 WHERE、JOIN、ORDER BY 频繁使用的列建索引 | 高频查询列优先 |
避免在低选择性列建索引（如 gender） | 几乎无效 |
| 复合索引遵循 最左前缀原则 | (a,b) 支持 a、a,b 查询，不支持单独 b |
定期用 ANALYZE 更新统计信息 | ANALYZE; 帮助优化器选择索引 |
| 写多于读？慎用索引 | 插入/更新会变慢 |
十、复合索引顺序原则（重要！）
CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at);可加速的查询：
WHERE user_id = ?WHERE user_id = ? AND status = ?WHERE user_id = ? AND status = ? ORDER BY created_at
不可加速：
WHERE status = ?（跳过user_id）WHERE created_at = ?
原则：把 过滤性最强、等值查询 的列放最左
十一、维护命令
-- 重新构建所有索引（修复碎片）
REINDEX;
-- 更新统计信息（优化查询计划）
ANALYZE;
-- 检查索引是否损坏
PRAGMA integrity_check;总结：索引决策表
| 场景 | 是否建索引 |
|---|---|
WHERE 条件列 | ✅ |
JOIN 的连接键 | ✅ |
ORDER BY 列 | ✅（尤其配合 LIMIT） |
| 小表（<1000 行） | ❌ 不必要 |
| 频繁插入/更新列 | ⚠️ 权衡性能 |
| 布尔值/枚举低基数列 | ❌ 无效 |
示例：电商优化
-- 高频查询：用户订单列表
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
-- 高频查询：按状态筛选
CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'deleted';
-- 覆盖索引：避免回表
CREATE INDEX idx_orders_cover ON orders(user_id, created_at, total, status);需要我帮你 分析某个 SQL 是否会命中索引？
或 为你的表设计最优索引方案？把表结构发我！
