SQLite性能优化 #

SQLite 性能优化方向：
├── 查询优化
├── 索引优化
├── PRAGMA配置
├── 数据库设计
├── 事务优化
└── 应用层优化

-- EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'test@example.com';

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

-- 查看执行时间
.timer on
SELECT * FROM users WHERE email = 'test@example.com';

-- 不推荐：SELECT *
SELECT * FROM users;

-- 推荐：只选择需要的列
SELECT name, email FROM users;

-- 使用索引列作为条件
SELECT * FROM users WHERE email = 'test@example.com';

-- 避免在条件中使用函数
-- 不推荐
SELECT * FROM users WHERE lower(email) = 'test@example.com';

-- 推荐
SELECT * FROM users WHERE email = 'test@example.com' COLLATE NOCASE;

-- 限制返回行数
SELECT * FROM users LIMIT 100;

-- 分页查询
SELECT * FROM users LIMIT 20 OFFSET 0;

-- 不推荐：相关子查询
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id)
FROM users;

-- 推荐：JOIN
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
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 为WHERE条件列创建索引
CREATE INDEX idx_users_email ON users(email);

-- 为JOIN列创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

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

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

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

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

-- 删除不必要的索引
DROP INDEX idx_unused;

-- 更新统计信息
ANALYZE;

-- 分析特定表
ANALYZE users;

-- WAL模式（推荐）
PRAGMA journal_mode = WAL;

-- 优点：
-- 1. 读写不冲突
-- 2. 更好的并发性能
-- 3. 更快的事务处理

-- 完全同步（最安全，最慢）
PRAGMA synchronous = FULL;

-- 正常同步（推荐）
PRAGMA synchronous = NORMAL;

-- 不同步（最快，不安全）
PRAGMA synchronous = OFF;

-- 设置缓存大小（页数）
PRAGMA cache_size = 10000;

-- 负数表示KB
PRAGMA cache_size = -64000;  -- 64MB

-- 设置页面大小（创建数据库时）
PRAGMA page_size = 4096;  -- 4KB（默认）
PRAGMA page_size = 8192;  -- 8KB
PRAGMA page_size = 16384; -- 16KB
PRAGMA page_size = 32768; -- 32KB

-- 临时文件存储位置
PRAGMA temp_store = MEMORY;  -- 内存
PRAGMA temp_store = FILE;    -- 文件

-- 正常锁定模式
PRAGMA locking_mode = NORMAL;

-- 排他锁定模式（单连接时更快）
PRAGMA locking_mode = EXCLUSIVE;

-- 启用外键约束
PRAGMA foreign_keys = ON;

-- 禁用外键约束（批量导入时更快）
PRAGMA foreign_keys = OFF;

-- 不推荐：每条语句一个事务
INSERT INTO users (name) VALUES ('User1');
INSERT INTO users (name) VALUES ('User2');
INSERT INTO users (name) VALUES ('User3');

-- 推荐：使用事务
BEGIN;
INSERT INTO users (name) VALUES ('User1');
INSERT INTO users (name) VALUES ('User2');
INSERT INTO users (name) VALUES ('User3');
COMMIT;

-- 批量导入时临时禁用
PRAGMA foreign_keys = OFF;
PRAGMA synchronous = OFF;

BEGIN;
-- 批量插入
INSERT INTO users ...;
COMMIT;

PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;

-- 使用INTEGER而不是TEXT存储数字
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    age INTEGER,  -- 不是 TEXT
    salary REAL   -- 不是 TEXT
);

-- 使用合适大小的类型
-- INTEGER 自动选择最小存储大小

-- 避免数据冗余
-- 不推荐
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_name TEXT,
    user_email TEXT,
    ...
);

-- 推荐
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 对于非整数主键的表，考虑WITHOUT ROWID
CREATE TABLE users (
    uuid TEXT PRIMARY KEY,
    name TEXT
) WITHOUT ROWID;

-- Python示例
-- 使用连接池复用连接

import sqlite3
from contextlib import contextmanager

@contextmanager
def get_connection(db_path):
    conn = sqlite3.connect(db_path)
    try:
        yield conn
    finally:
        conn.close()

-- 使用预处理语句
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

-- 批量操作
cursor.executemany("INSERT INTO users (name) VALUES (?)", users_list)

-- 使用生成器延迟加载大量数据
def iter_users(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    while True:
        row = cursor.fetchone()
        if row is None:
            break
        yield row

-- 启用计时
.timer on

SELECT * FROM users WHERE email = 'test@example.com';
-- Run Time: real 0.001 user 0.000000 sys 0.000000

-- 数据库统计
PRAGMA database_list;
PRAGMA page_count;
PRAGMA page_size;

-- 计算数据库大小
SELECT page_size * page_count / 1024 / 1024 AS size_mb 
FROM pragma_page_count;

-- 表信息
SELECT 
    name,
    (SELECT COUNT(*) FROM pragma_table_info(m.name)) AS columns
FROM sqlite_master m
WHERE type = 'table';

性能优化检查清单：
□ 使用WAL模式
□ 创建合适的索引
□ 使用事务批量操作
□ 只查询需要的列
□ 使用EXPLAIN分析查询
□ 设置合理的PRAGMA
□ 定期执行ANALYZE
□ 定期执行VACUUM