SQLite索引 #

-- 索引是一种数据结构，用于加速查询
-- 类似于书籍的目录，可以快速定位数据

-- 索引的特点：
-- 1. 提高查询速度
-- 2. 降低插入/更新/删除速度
-- 3. 占用存储空间
-- 4. 自动维护

SQLite 索引类型：
├── 单列索引
├── 复合索引
├── 唯一索引
├── 部分索引
├── 表达式索引
└── 全文索引（FTS）

-- CREATE INDEX 基本语法
CREATE INDEX index_name ON table_name (column1, column2, ...);

-- 示例表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    age INTEGER,
    status INTEGER
);

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

-- 创建复合索引
CREATE INDEX idx_users_status_age ON users(status, age);

-- 唯一索引：确保列值唯一
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 尝试插入重复值会失败
INSERT INTO users (name, email) VALUES ('Test', 'existing@example.com');
-- Error: UNIQUE constraint failed

-- 避免重复创建
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- 部分索引：只索引满足条件的行
CREATE INDEX idx_users_active ON users(email) WHERE status = 1;

-- 只有活跃用户会被索引
-- 查询时自动使用
SELECT * FROM users WHERE status = 1 AND email = 'test@example.com';

-- 表达式索引：对表达式结果建立索引
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- 查询时使用相同表达式
SELECT * FROM users WHERE lower(email) = 'test@example.com';

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

-- 输出：
-- seq | name              | unique | origin | partial
-- ----|-------------------|--------|--------|--------
-- 0   | idx_users_email   | 0      | c      | 0
-- 1   | idx_users_status  | 0      | c      | 0

-- 查看索引详情
PRAGMA index_info(idx_users_email);

-- 输出：
-- seqno | cid | name
-- ------|-----|-------
-- 0     | 2   | email

-- 查看数据库中所有索引
SELECT name, tbl_name, sql 
FROM sqlite_master 
WHERE type = 'index';

-- 只查看用户创建的索引
SELECT name, tbl_name, sql 
FROM sqlite_master 
WHERE type = 'index' AND sql IS NOT NULL;

-- 删除索引
DROP INDEX idx_users_email;

-- 使用 IF EXISTS
DROP INDEX IF EXISTS idx_users_email;

-- SQLite 查询优化器会自动选择是否使用索引

-- 使用 EXPLAIN QUERY PLAN 查看执行计划
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'test@example.com';

-- 输出：
-- QUERY PLAN
-- `--SEARCH TABLE users USING INDEX idx_users_email (email=?)

-- WHERE 条件
SELECT * FROM users WHERE email = 'test@example.com';

-- JOIN 条件
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- 需要在 orders.user_id 上创建索引

-- ORDER BY
SELECT * FROM users ORDER BY email;
-- 可以使用 idx_users_email 索引

-- GROUP BY
SELECT status, COUNT(*) FROM users GROUP BY status;
-- 可以使用 idx_users_status 索引

-- 复合索引：idx_users_status_age (status, age)

-- 使用索引
SELECT * FROM users WHERE status = 1;              -- ✓ 使用索引
SELECT * FROM users WHERE status = 1 AND age > 20; -- ✓ 使用索引

-- 不使用索引
SELECT * FROM users WHERE age > 20;                -- ✗ 不使用索引

-- 部分使用
SELECT * FROM users WHERE status = 1 ORDER BY age; -- ✓ 使用索引

-- 最左前缀原则：
-- 索引 (a, b, c)
-- WHERE a = 1         ✓
-- WHERE a = 1 AND b = 2 ✓
-- WHERE a = 1 AND b = 2 AND c = 3 ✓
-- WHERE b = 2         ✗
-- WHERE c = 3         ✗
-- WHERE a = 1 AND c = 3 ✓ (只用到a)

-- 选择索引列的原则：
-- 1. WHERE 条件中常用的列
-- 2. JOIN 条件中的列
-- 3. ORDER BY / GROUP BY 中的列
-- 4. 选择性高的列（值差异大）

-- 选择性计算
SELECT 
    COUNT(DISTINCT status) AS distinct_values,
    COUNT(*) AS total_rows,
    CAST(COUNT(DISTINCT status) AS REAL) / COUNT(*) AS selectivity
FROM users;

-- 选择性接近1的列适合建索引

-- 索引的代价：
-- 1. 占用存储空间
-- 2. 降低写入性能
-- 3. 增加维护成本

-- 建议：
-- 1. 只为必要的查询创建索引
-- 2. 定期审查和删除无用索引
-- 3. 使用复合索引代替多个单列索引

-- ANALYZE：收集统计信息，帮助优化器选择索引
ANALYZE;

-- 分析特定表
ANALYZE users;

-- 分析特定索引
ANALYZE idx_users_email;

-- 在大量数据变更后执行
INSERT INTO users ... -- 大量插入
ANALYZE;

-- EXPLAIN QUERY PLAN 查看执行计划
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'test@example.com';

-- 输出说明：
-- SCAN TABLE：全表扫描
-- SEARCH TABLE USING INDEX：使用索引
-- USE TEMP B-TREE：使用临时B树排序

-- 全表扫描示例
EXPLAIN QUERY PLAN SELECT * FROM users WHERE age > 20;
-- SCAN TABLE users

-- 使用索引示例
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'test@example.com';
-- SEARCH TABLE users USING INDEX idx_users_email

-- SQLite 不支持索引提示
-- 但可以通过 INDEXED BY 强制使用或不使用索引

-- 强制使用索引
SELECT * FROM users INDEXED BY idx_users_email WHERE email = 'test@example.com';

-- 禁止使用索引
SELECT * FROM users NOT INDEXED WHERE email = 'test@example.com';

-- 覆盖索引：查询的所有列都在索引中
CREATE INDEX idx_users_covering ON users(status, age, name);

-- 这个查询只需要访问索引
SELECT status, age, name FROM users WHERE status = 1;

-- 检查执行计划
EXPLAIN QUERY PLAN SELECT status, age, name FROM users WHERE status = 1;
-- SEARCH TABLE users USING COVERING INDEX idx_users_covering

-- 主键自动创建索引
CREATE TABLE users (
    id INTEGER PRIMARY KEY,  -- 自动创建索引
    name TEXT
);

-- INTEGER PRIMARY KEY 是 ROWID 的别名
-- 数据按 ROWID 存储，本身就是索引

-- UNIQUE 约束自动创建唯一索引
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE  -- 自动创建唯一索引
);

-- 等价于
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT
);
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 外键不会自动创建索引
-- 建议手动创建
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 创建外键索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- SQLite 没有直接的 REINDEX 命令
-- 可以删除并重新创建

DROP INDEX idx_users_email;
CREATE INDEX idx_users_email ON users(email);

-- 或使用 REINDEX（SQLite 3.1+）
REINDEX idx_users_email;

-- 重建所有索引
REINDEX;

-- 查看索引统计
SELECT 
    name AS index_name,
    tbl_name AS table_name
FROM sqlite_master
WHERE type = 'index' AND sql IS NOT NULL;

-- 检查是否有未使用的索引
-- 需要通过应用程序日志分析