SQLite数据库查询优化：如何将查询耗时降至1秒以内？

我的SQLite数据库大小为3GB，其中ANC_TB表包含100万行数据。执行以下查询语句耗时约5秒：

db_mgr().ExecuteQuery(ToString(_T("SELECT CORE_MAX_PIN_BURNUP FROM ANC_TB WHERE UNIT_ID='%s' AND  CYCLE_NUM=%d AND FA_ID='%s' ORDER BY CORE_MAX_PIN_BURNUP DESC LIMIT 1"),
                               pi().ucms,
                               itr->cycle,
                               itr->fa_id
                              )
                              .GetBuffer(0)
                      );
  
if (db_mgr().Start())
{
    itr->a_Max_Pin_Burnup = db_mgr().Get_int(_T("CORE_MAX_PIN_BURNUP"));
}

我已尝试通过执行sqlite3_exec(mpDB, "PRAGMA cache_size=100000;", NULL, NULL, NULL);增大内存缓存，但仍未达到目标，请问如何将查询时间降至1秒以内？

优化方案 #

1. 创建针对性复合索引（最核心） #

你的查询逻辑是先按UNIT_ID、CYCLE_NUM、FA_ID过滤数据，再取CORE_MAX_PIN_BURNUP的最大值。直接创建覆盖这四个字段的复合索引，让SQLite无需回表查询，直接从索引中获取结果：

CREATE INDEX idx_anc_tb_unit_cycle_fa_burnup ON ANC_TB (UNIT_ID, CYCLE_NUM, FA_ID, CORE_MAX_PIN_BURNUP DESC);

这个索引会把符合过滤条件的数据按CORE_MAX_PIN_BURNUP降序排列，查询时直接取第一条，完全跳过全表扫描和排序步骤，性能提升最明显。

2. 调整SQLite系统参数 #

除了cache_size，还可以调整以下参数进一步榨取性能：

• 开启WAL日志模式，提升读写并发和IO效率：

  PRAGMA journal_mode=WAL;
  

• 若业务允许少量数据丢失风险（比如非核心业务数据），关闭同步锁：

  PRAGMA synchronous=OFF;
  

• 将临时表存储设为内存，避免磁盘临时文件操作：

  PRAGMA temp_store=MEMORY;
  

3. 改用参数化查询 #

当前用字符串拼接生成SQL，不仅有SQL注入风险，还会导致SQLite无法复用执行计划。改成参数化查询，让SQLite缓存执行计划，重复查询时更快：

const TCHAR* sql = _T("SELECT CORE_MAX_PIN_BURNUP FROM ANC_TB WHERE UNIT_ID=? AND CYCLE_NUM=? AND FA_ID=? ORDER BY CORE_MAX_PIN_BURNUP DESC LIMIT 1");
sqlite3_stmt* stmt;
sqlite3_prepare_v2(mpDB, CT2A(sql), -1, &stmt, NULL);
// 绑定参数
sqlite3_bind_text(stmt, 1, CT2A(pi().ucms), -1, SQLITE_STATIC);
sqlite3_bind_int(stmt, 2, itr->cycle);
sqlite3_bind_text(stmt, 3, CT2A(itr->fa_id), -1, SQLITE_STATIC);
// 执行查询
if (sqlite3_step(stmt) == SQLITE_ROW) {
    itr->a_Max_Pin_Burnup = sqlite3_column_int(stmt, 0);
}
// 释放资源
sqlite3_finalize(stmt);

4. 数据库文件与存储优化 #

• 执行VACUUM命令整理数据库碎片，优化存储结构：

  VACUUM;
  

• 确保数据库文件存储在SSD上，机械磁盘的随机IO性能远低于SSD，这对SQLite这类依赖磁盘IO的数据库影响极大。

内容的提问来源于stack exchange，提问作者정글은엘리스