-- 完整字节码（调试用）
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 查询计划（优化用，推荐）
EXPLAIN QUERY PLAN SELECT * FROM users WHERE id = 1;

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    amount REAL NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status, created_at);

-- 示例 1：使用索引
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42;
-- SEARCH TABLE orders USING INDEX idx_orders_user (user_id=?)

-- 示例 2：全表扫描
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE amount > 100;
-- SCAN TABLE orders（没有 amount 索引）

-- 示例 3：复合索引
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'completed' AND created_at > '2026-01-01';
-- SEARCH TABLE orders USING INDEX idx_orders_status (status=? AND created_at>?)

-- 示例 4：覆盖索引
CREATE INDEX idx_orders_cover ON orders(user_id, status, amount);
EXPLAIN QUERY PLAN
SELECT user_id, status, amount FROM orders WHERE user_id = 42;
-- SEARCH TABLE orders USING COVERING INDEX idx_orders_cover (user_id=?)

-- ❌ 不好：返回所有列
SELECT * FROM orders WHERE user_id = 42;

-- ✅ 好：只返回需要的列（可能使用覆盖索引）
SELECT id, status, amount FROM orders WHERE user_id = 42;

-- ❌ 不好：索引列上使用函数（索引失效）
SELECT * FROM users WHERE lower(email) = '[email protected]';
-- 但如果创建了表达式索引，就可以使用
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- ❌ 不好：对索引列进行计算
SELECT * FROM orders WHERE amount * 1.1 > 1000;

-- ✅ 好：将计算移到右边
SELECT * FROM orders WHERE amount > 1000 / 1.1;

-- ❌ 不好
SELECT * FROM orders WHERE strftime('%Y', created_at) = '2026';

-- ✅ 好：使用范围查询
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- ⚠️ OR 可能导致索引失效
SELECT * FROM users WHERE email = '[email protected]' OR name = '张三';

-- ✅ 改用 UNION ALL
SELECT * FROM users WHERE email = '[email protected]'
UNION ALL
SELECT * FROM users WHERE name = '张三' AND email != '[email protected]';

-- ⚠️ 可能导致相关子查询
SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders WHERE status = 'completed'
);

-- ✅ 改用 EXISTS（通常更快）
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'completed'
);

-- ✅ 或用 JOIN
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

-- ✅ 没有 ORDER BY 时 LIMIT 非常快
SELECT * FROM users LIMIT 10;

-- ⚠️ 有 ORDER BY 时需要排序
SELECT * FROM users ORDER BY name LIMIT 10;
-- 如果有 ORDER BY 列的索引就很快

-- ❌ 大 OFFSET 值性能差
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 100000;
-- SQLite 需要扫描前 100010 行然后丢弃前 100000 行

-- ✅ 使用游标分页（更高效）
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;

-- ❌ 前缀通配符无法使用索引
SELECT * FROM users WHERE name LIKE '%张%';

-- ✅ 后缀通配符可以使用索引
SELECT * FROM users WHERE name LIKE '张%';

-- ✅ 使用 FTS5 全文搜索替代前缀通配符
-- 参见第 11 章

-- SQLite 优化器会自动选择 JOIN 顺序
-- 但确保连接列有索引

-- ✅ 确保连接列有索引
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_items_order ON order_items(order_id);

-- ✅ 小表驱动大表（SQLite 通常自动优化）
SELECT u.name, o.id, p.name
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
JOIN users u ON o.user_id = u.id
WHERE u.id = 1;

EXPLAIN QUERY PLAN
SELECT u.name, o.id
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;

-- 预期输出：
-- SEARCH TABLE users AS u USING INTEGER PRIMARY KEY (rowid=?)
-- SEARCH TABLE orders AS o USING INDEX idx_orders_user (user_id=?)

-- 相关子查询（对外部表的每一行执行一次子查询）
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- ✅ 使用 LEFT JOIN + GROUP BY 通常更快
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 当子查询结果集较大时，EXISTS 通常更快
-- 当子查询结果集较小时，IN 通常更快

-- EXISTS：找到第一个匹配就停止
SELECT * FROM users WHERE EXISTS (
    SELECT 1 FROM orders WHERE user_id = users.id AND status = 'completed'
);

-- IN：需要计算完整的子查询结果集
SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders WHERE status = 'completed'
);

-- ⚠️ 可能需要临时 B-Tree
SELECT * FROM users ORDER BY name, age;

-- ✅ 创建复合索引避免排序
CREATE INDEX idx_users_name_age ON users(name, age);

EXPLAIN QUERY PLAN SELECT * FROM users ORDER BY name, age;
-- 应该显示使用索引而不是临时 B-Tree

-- ⚠️ GROUP BY 可能需要临时表
SELECT department, COUNT(*) FROM employees GROUP BY department;

-- ✅ 创建索引优化 GROUP BY
CREATE INDEX idx_emp_dept ON employees(department);

-- 方式 1：重新排列 WHERE 条件顺序
-- 方式 2：使用部分索引限制选择
-- 方式 3：使用 CROSS JOIN 强制表的连接顺序
-- 方式 4：调整复合索引的列顺序

-- 查询优化器提示（SQLite 不直接支持，但可以通过重构 SQL 实现）
-- 如果优化器选择了错误的索引，考虑：
-- 1. 运行 ANALYZE 更新统计信息
-- 2. 重新设计索引
-- 3. 重构查询

# 分析数据库文件结构
sqlite3_analyzer mydb.db

# 输出包含：
# - 每张表的页面数
# - 每个索引的页面数
# - 空间利用率
# - B-Tree 深度

# 比较两个数据库的差异
sqldiff db1.db db2.db

# 输出 SQL 语句将 db1 转换为 db2

-- 开启计时
.timer on

-- 执行查询
SELECT COUNT(*) FROM logs WHERE level = 'ERROR';
-- Run Time: real 0.012 user 0.011000 sys 0.001000

-- 比较有无索引的性能差异
.timer on
SELECT * FROM logs WHERE level = 'ERROR' LIMIT 100;
-- 无索引：~50ms
-- 有索引：~0.5ms

-- 问题查询
SELECT * FROM orders
WHERE user_id = 42 AND status = 'completed'
ORDER BY created_at DESC
LIMIT 10;

-- 步骤 1：查看查询计划
EXPLAIN QUERY PLAN SELECT * FROM orders
WHERE user_id = 42 AND status = 'completed'
ORDER BY created_at DESC LIMIT 10;
-- SCAN TABLE orders（全表扫描）

-- 步骤 2：创建合适的索引
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);

-- 步骤 3：验证优化效果
EXPLAIN QUERY PLAN SELECT * FROM orders
WHERE user_id = 42 AND status = 'completed'
ORDER BY created_at DESC LIMIT 10;
-- SEARCH TABLE orders USING INDEX idx_orders_user_status_date (user_id=? AND status=?)

-- 慢：每次 COUNT 都全表扫描
SELECT COUNT(*) FROM orders WHERE status = 'pending';

-- 快：使用索引
CREATE INDEX idx_orders_status ON orders(status);
SELECT COUNT(*) FROM orders WHERE status = 'pending';

-- 更快：维护计数表
CREATE TABLE order_counts (
    status TEXT PRIMARY KEY,
    cnt INTEGER DEFAULT 0
);

-- 使用触发器维护计数
CREATE TRIGGER order_insert AFTER INSERT ON orders
BEGIN
    INSERT INTO order_counts (status, cnt) VALUES (NEW.status, 1)
    ON CONFLICT(status) DO UPDATE SET cnt = cnt + 1;
END;

-- ❌ 慢：大偏移量
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 100000;

-- ✅ 快：游标分页
SELECT * FROM products WHERE id > 100000 ORDER BY id LIMIT 20;

-- ✅ 延迟关联（如果需要按非主键排序）
SELECT p.* FROM products p
INNER JOIN (
    SELECT id FROM products ORDER BY name LIMIT 20 OFFSET 100000
) t ON p.id = t.id;

-- 创建覆盖索引
CREATE INDEX idx_products_cover ON products(category, price, id, name);

-- 高效分页
SELECT id, name, price FROM products
WHERE category = 'electronics' AND price > 100
ORDER BY price ASC
LIMIT 20;

-- 月度订单统计
CREATE INDEX idx_orders_month ON orders(
    strftime('%Y-%m', created_at), status
);

SELECT strftime('%Y-%m', created_at) AS month, status, COUNT(*), SUM(amount)
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY month, status;