-- 创建测试数据
CREATE TABLE logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    level TEXT NOT NULL,
    message TEXT NOT NULL,
    source TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- 插入 10 万条测试数据
INSERT INTO logs (level, message, source, created_at)
SELECT
    CASE ABS(RANDOM()) % 5
        WHEN 0 THEN 'DEBUG' WHEN 1 THEN 'INFO' WHEN 2 THEN 'WARN'
        WHEN 3 THEN 'ERROR' ELSE 'FATAL'
    END,
    'Log message ' || id,
    CASE ABS(RANDOM()) % 3
        WHEN 0 THEN 'app' WHEN 1 THEN 'auth' ELSE 'api'
    END,
    datetime('now', '-' || ABS(RANDOM()) % 365 || ' days')
FROM (
    WITH RECURSIVE cnt(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cnt WHERE x < 100000)
    SELECT x AS id FROM cnt
);

-- 无索引查询
.timer on
SELECT * FROM logs WHERE level = 'ERROR' AND source = 'auth';
-- 可能需要几十毫秒（全表扫描）

-- 创建索引后
CREATE INDEX idx_logs_level_source ON logs(level, source);

SELECT * FROM logs WHERE level = 'ERROR' AND source = 'auth';
-- 几毫秒（使用索引）

-- 基本索引
CREATE INDEX idx_logs_level ON logs(level);

-- IF NOT EXISTS（幂等操作）
CREATE INDEX IF NOT EXISTS idx_logs_level ON logs(level);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 多列索引（复合索引）
CREATE INDEX idx_logs_level_source ON logs(level, source);

-- 删除索引
DROP INDEX IF EXISTS idx_logs_level;

-- 重命名索引（SQLite 3.25.0+）
ALTER INDEX idx_logs_level RENAME TO idx_logs_level_v2;

-- 查看索引大小
ANALYZE;  -- 收集统计信息
SELECT * FROM sqlite_stat1 WHERE tbl = 'logs';

-- 查看数据库大小
PRAGMA page_count;
PRAGMA page_size;
SELECT page_count * page_size / 1024.0 / 1024.0 AS size_mb FROM pragma_page_count(), pragma_page_size();

CREATE INDEX idx_logs_level_source_date ON logs(level, source, created_at);

-- ✅ 可以使用索引
SELECT * FROM logs WHERE level = 'ERROR';
SELECT * FROM logs WHERE level = 'ERROR' AND source = 'auth';
SELECT * FROM logs WHERE level = 'ERROR' AND source = 'auth' AND created_at > '2026-01-01';

-- ❌ 不能使用索引（跳过了 level 和 source）
SELECT * FROM logs WHERE created_at > '2026-01-01';

-- ⚠️ 只能使用索引的第一列
SELECT * FROM logs WHERE source = 'auth';

-- 验证：查看查询计划
EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR' AND source = 'auth';
-- SCAN TABLE logs USING INDEX idx_logs_level_source (level=? AND source=?)

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE source = 'auth';
-- SCAN TABLE logs（全表扫描）

-- 策略 1：等值查询列在前，范围查询列在后
-- 查询：WHERE status = 'active' AND created_at > '2026-01-01'
CREATE INDEX idx_good ON orders(status, created_at);

-- ❌ 不好：范围查询列在前
CREATE INDEX idx_bad ON orders(created_at, status);

-- 策略 2：选择性高的列在前
-- level 只有 5 个值，source 只有 3 个值
-- 但查询通常先过滤 level，所以 level 在前
CREATE INDEX idx_logs_level_source ON logs(level, source);

-- 只为活跃用户创建索引
CREATE INDEX idx_active_users_email ON users(email) WHERE is_active = 1;

-- 只为错误日志创建索引（假设错误日志占比 5%）
CREATE INDEX idx_error_logs ON logs(source, created_at) WHERE level = 'ERROR';

-- 使用部分索引
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'test@example.com' AND is_active = 1;
-- SCAN TABLE users USING INDEX idx_active_users_email

-- ❌ 不能使用部分索引（条件不匹配）
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'test@example.com' AND is_active = 0;
-- SCAN TABLE users（全表扫描）

-- 不区分大小写的邮箱查找
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- 使用表达式索引
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE lower(email) = 'test@example.com';
-- SCAN TABLE users USING INDEX idx_users_email_lower

-- 日期部分索引
CREATE INDEX idx_logs_month ON logs(strftime('%Y-%m', created_at));

-- JSON 字段索引
CREATE INDEX idx_users_city ON users(json_extract(profile, '$.city'));

-- 普通索引（需要回表）
CREATE INDEX idx_orders_user ON orders(user_id);

-- 查询：需要回表获取 total_amount
SELECT user_id, total_amount FROM orders WHERE user_id = 1;
-- SEARCH TABLE orders USING INDEX idx_orders_user (user_id=?)
-- 然后需要访问主表获取 total_amount

-- 覆盖索引（无需回表）
CREATE INDEX idx_orders_user_cover ON orders(user_id, total_amount);

-- 查询：所有数据都在索引中
EXPLAIN QUERY PLAN
SELECT user_id, total_amount FROM orders WHERE user_id = 1;
-- SEARCH TABLE orders USING COVERING INDEX idx_orders_user_cover (user_id=?)

-- 查询：SELECT a, b FROM t WHERE c = ?
-- 覆盖索引：(c, a, b)
CREATE INDEX idx_cover ON t(c, a, b);

-- 查询：SELECT COUNT(*) FROM t WHERE status = 'active'
-- 覆盖索引：(status)
CREATE INDEX idx_status ON t(status);

-- 查询：SELECT id, name FROM t ORDER BY name
-- 覆盖索引：(name, id)
CREATE INDEX idx_name_cover ON t(name, id);

-- 普通表（有隐藏的 rowid）
CREATE TABLE tags (
    article_id INTEGER NOT NULL,
    tag TEXT NOT NULL,
    PRIMARY KEY (article_id, tag)
);

-- WITHOUT ROWID 表（数据直接按主键存储）
CREATE TABLE tags_norowid (
    article_id INTEGER NOT NULL,
    tag TEXT NOT NULL,
    PRIMARY KEY (article_id, tag)
) WITHOUT ROWID;

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

-- 只分析特定表
ANALYZE logs;

-- 查看统计信息
SELECT * FROM sqlite_stat1;
-- tbl  | idx                              | stat
-- logs | idx_logs_level_source            | 100000 20000 33333
-- 含义：10 万行，level 有 20000 个不同值对，平均每个 level 值有 33333 行

-- 清除统计信息
DELETE FROM sqlite_stat1;

-- 列出表的所有索引
.indices logs

-- 查看索引定义
SELECT * FROM sqlite_master WHERE type = 'index' AND tbl_name = 'logs';

-- EXPLAIN QUERY PLAN 查看是否使用了索引
EXPLAIN QUERY PLAN SELECT * FROM logs WHERE level = 'ERROR';

-- 邮箱查找（大小写不敏感）
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- 登录查询
SELECT * FROM users
WHERE lower(email) = lower('Test@Example.com')
AND password_hash = 'hashed_pw';

-- 只为错误日志建立详细索引（错误日志占比 < 5%）
CREATE INDEX idx_error_detail ON logs(source, created_at, message)
WHERE level IN ('ERROR', 'FATAL');

-- 覆盖查询
SELECT source, created_at, message
FROM logs
WHERE level IN ('ERROR', 'FATAL')
AND source = 'auth'
AND created_at > '2026-04-01';

-- 用户订单列表（覆盖索引）
CREATE INDEX idx_orders_user_cover ON orders(user_id, status, created_at, total_amount);

-- 查询用户近期订单
SELECT created_at, total_amount, status
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;