一、从一次慢查询说起

最近接到线上告警，某个移动端的订单查询接口响应时间突破5秒大关。使用EXPLAIN QUERY PLAN分析SQL执行计划时，发现查询没有使用本该存在的索引，反而进行了全表扫描。这个案例让我重新审视SQLite的查询优化机制：究竟在什么场景下需要手动干预索引使用？什么样的查询结构调整能让执行效率产生质变？

二、SQLite索引工作原理

SQLite采用B-Tree索引结构，其索引决策基于成本估算。创建基础索引的示例：

-- 创建含50万记录的订单表
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    amount REAL CHECK(amount > 0),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 建立用户ID和时间字段的复合索引
CREATE INDEX idx_user_time ON orders(user_id, create_time);

-- 错误的索引选择案例
EXPLAIN QUERY PLAN 
SELECT * FROM orders WHERE create_time > '2023-01-01';
/* 输出显示
SCAN TABLE orders
使用了全表扫描而非索引
*/

三、强制索引实战指南

3.1 INDEXED BY强制指令

-- 强制使用复合索引的后半部分
SELECT order_id, amount 
FROM orders INDEXED BY idx_user_time
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

-- 强制索引与查询条件不匹配的典型案例
EXPLAIN QUERY PLAN
SELECT user_id FROM orders INDEXED BY idx_user_time 
WHERE amount > 1000;
/* 
输出显示：
SEARCH TABLE orders USING INDEX idx_user_time
但实际查询效率可能比全表扫描更差
*/

3.2 参数化影响验证

-- 参数化查询对索引选择的影响
EXPLAIN QUERY PLAN 
SELECT * FROM orders 
WHERE user_id = @user AND create_time > date('now','-30 day');

-- 使用绑定参数的优化版本
WITH params(user_filter, time_filter) AS (
  VALUES(12345, datetime('now','-30 day'))
)
SELECT order_id FROM orders 
WHERE user_id = (SELECT user_filter FROM params)
  AND create_time > (SELECT time_filter FROM params);

四、查询结构调整三大策略

4.1 结果集预筛选

-- 原始低效查询
SELECT o.*, u.name 
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.vip_level > 3 
  AND o.create_time > '2023-06-01';

-- 优化后的分批查询
WITH vip_users AS (
  SELECT id FROM users WHERE vip_level > 3
)
SELECT o.*, u.name
FROM orders o
JOIN vip_users u ON o.user_id = u.id
WHERE o.create_time > '2023-06-01';

4.2 分页查询深度优化

-- 传统分页的性能瓶颈
SELECT * FROM orders 
ORDER BY create_time DESC
LIMIT 10 OFFSET 100000;

-- 优化的游标分页法
SELECT * FROM orders 
WHERE create_time < (SELECT create_time FROM orders WHERE order_id = ?)
ORDER BY create_time DESC 
LIMIT 10;

4.3 表达式重写技巧

-- 原始LIKE查询
SELECT * FROM product 
WHERE product_name LIKE '%智能手表%';

-- 优化后的全文检索
CREATE VIRTUAL TABLE product_fts USING fts5(name, content='product');
SELECT * FROM product_fts 
WHERE name MATCH '智能手表';

五、关键注意事项

  1. 统计信息维护：建议每写入5000条记录执行一次ANALYZE
  2. 索引选择悖论：复合索引中的字段顺序调整对查询效率的影响测试
  3. 虚拟表优化：FTS5与RTREE的性能对比基准测试
  4. WAL模式权衡：写并发场景下的索引维护代价

六、优化效果验证体系

建立优化前后的性能对比指标体系：

-- 查询性能基线测试
.timer on
PRAGMA cache_size = -2000;  -- 设置1MB缓存

-- 执行计划对比分析
EXPLAIN QUERY PLAN SELECT ...;

-- 实际执行时间统计
SELECT * FROM orders WHERE ...;

七、总结与最佳实践

经过二十多次迭代优化，我们的订单查询响应时间从最初的5.3秒降至87毫秒。关键经验包括：

  1. 在数据分布倾斜严重时强制索引更有效
  2. CTE表达式对复杂查询的可读性提升
  3. 分页优化的游标法在移动端的适配优势
  4. 定期维护统计信息对优化器决策的影响