1. SQLite优化器的工作流程
当我们向SQLite发送一条SELECT语句时，数据库会经历从"文本解析"到"物理执行"的全过程。就像导航软件规划路线时需要评估多条路径耗时类似，优化器的核心任务就是找到成本最低的执行路径。
在查询优化的第一阶段，SQLite会使用sqlite3_prepare_v2()将SQL转换为语法树。此时会触发优化器的查询重写逻辑，例如将LEFT JOIN转换为更高效的INNER JOIN（当查询条件包含非空约束时）。
以下是典型的WHERE条件重写示例：
-- 原始查询
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE status = 'VIP');
-- 优化后的等价查询
SELECT orders.*
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
WHERE customers.status = 'VIP';
2. 执行计划选择的关键步骤
2.1 索引匹配的判定逻辑
执行以下建表语句：
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
department_id INTEGER,
salary REAL,
hire_date DATE
);
CREATE INDEX idx_dept ON employees(department_id);
CREATE INDEX idx_salary_dept ON employees(salary, department_id);
当执行WHERE department_id = 5 AND salary > 10000时，优化器需要决策使用哪个索引。此时会计算每个索引的选择率：
- 单列索引
idx_dept可能匹配department_id=5（约5%的数据） - 组合索引
idx_salary_dept匹配salary>10000（约20%数据）
虽然组合索引匹配更少行，但需要额外判断department_id=5的条件。优化器会计算这两种方案的CPU比较成本。
2.2 多表连接的顺序优化
假设我们有以下三个关联表：
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL);
CREATE TABLE payments (order_id INTEGER, status TEXT);
执行三表连接查询：
SELECT *
FROM users
JOIN orders ON users.id = orders.user_id
JOIN payments ON orders.id = payments.order_id
WHERE users.id = 100;
优化器会枚举所有可能的连接顺序：
- 先连接users和orders（通过主键快速定位）
- 再连接结果集与payments表
由于users表存在直接条件id=100，该记录在orders表中可能对应N条记录，在payments表中对应M条记录。优化器会选择user-driven的执行顺序以减少中间结果集。
3. 成本估算的数学模型
3.1 IO成本的计算公式
SQLite使用以下公式计算扫描成本：
总成本 = 页面访问次数 × 页面IO成本系数
对于索引扫描：
- 若使用B树索引且查询条件为等值查找，IO成本为：
log(N) + 1（N为总页数） - 范围扫描则按
覆盖的记录数 / 每页记录数估算页数
3.2 多条件查询的联合选择率
当存在多个WHERE条件时，优化器会使用以下公式估算联合选择率：
联合选择率 = 条件1选择率 × 条件2选择率 × ... × 条件N选择率 × 相关性修正因子
例如某表有100万条记录：
WHERE age > 30 (选择率20%)
AND city = '北京' (选择率5%)
若两个条件完全独立，联合选择率应为1%（1000000 × 0.2 × 0.5 = 10000），但实际可能有相关性修正（如北京中高年龄段人口较多）。
4. 优化器决策实例分析
4.1 索引覆盖与回表决策
使用复合索引的场景：
CREATE INDEX idx_covering ON employees(department_id, salary);
-- 查询语句
SELECT department_id, salary
FROM employees
WHERE department_id BETWEEN 10 AND 20;
优化器会发现索引idx_covering能完全覆盖查询需求，执行计划将是：
SEARCH TABLE employees USING INDEX idx_covering (department_id>? AND department_id<?)
4.2 排序优化机制分析
当遇到带有排序的查询时：
SELECT *
FROM employees
WHERE department_id = 5
ORDER BY salary DESC
LIMIT 10;
优化器有两种选择：
- 先进行全量排序再过滤
- 使用
idx_salary_dept索引逆向扫描
后者能够直接按salary降序读取部门5的记录，省略排序步骤。通过EXPLAIN QUERY PLAN可以看到：
USE TEMP B-TREE FOR ORDER BY --> 无此提示表示使用索引排序
5. 实用优化技巧
5.1 统计信息的收集
通过ANALYZE命令生成统计信息表：
ANALYZE;
SELECT * FROM sqlite_stat1;
该表包含每个索引的stat字段，记录该索引不同值的数量和分布。例如：
idx_dept|12345|{500,490,...,510}
5.2 强制索引的使用
当自动选择不理想时，可使用INDEXED BY提示：
SELECT *
FROM employees INDEXED BY idx_salary_dept
WHERE department_id = 5 AND salary > 10000;
6. 应用场景分析
6.1 物联网设备场景
在车载设备应用中，典型的查询模式是时间范围查询：
SELECT *
FROM sensor_data
WHERE device_id = 'XYZ'
AND timestamp BETWEEN '2023-01-01' AND '2023-01-31';
通过建立(device_id, timestamp)的复合索引，可以使查询完全走索引扫描。
6.2 移动应用场景
在用户会话管理中，高频查询是：
SELECT session_id
FROM user_sessions
WHERE user_id = ?
AND expire_time > CURRENT_TIMESTAMP
ORDER BY created_time DESC
LIMIT 1;
需要建立(user_id, expire_time, created_time)的三列索引才能优化排序和过滤操作。
7. 技术优缺点总结
优势特性：
- 轻量级的决策算法，适合嵌入式场景
- 基于规则的索引选择策略直观易懂
- 支持手动统计信息收集
现存不足：
- 缺少直方图统计信息
- 不支持多列相关性的自动检测
- 无法自动创建虚拟索引进行测试
8. 注意事项
- 避免在WHERE条件中对索引列使用函数
-- 无法使用索引的反例
SELECT * FROM employees WHERE CAST(department_id AS TEXT) = '005';
- LIKE查询的前导通配符问题
-- 能使用索引的情况
WHERE name LIKE '张%'
-- 无法使用索引的情况
WHERE name LIKE '%小明'
- OR条件的拆分处理
-- 更优的改写方式
SELECT * FROM table
WHERE col1 = 1
UNION ALL
SELECT * FROM table
WHERE col2 = 2;