SQLite中如何优化多个无关联同结构分表的查询性能？

合并同结构年份分表为单表的方案完全可行，且能带来确定性的性能提升，核心原因如下：

• 消除多表查询的重复固定开销：逐表查询时，每次查询都需要完成表打开、SQL解析、执行计划生成、B树结构初始化等固定操作，50次查询的固定开销累加后远高于单表查询的单次固定开销。当单趟查询扫描的数据量不大时，这部分重复开销甚至能占总耗时的80%以上。
• 支持统一的高效索引：分表场景下索引只能在单表维度生效，跨表查询时无法利用全局有序的索引结构完成排序、范围裁剪，要么需要额外做全局排序，要么只能逐表全表扫描，性能损耗极大。

20GB体量的SQLite库合并操作耗时远低于预期：普通消费级SSD上写入速度可达50-100MB/s，全量合并仅需数分钟，你可以保留原分表不动，后台逐表插入数据到新总表，校验完成后再切换查询逻辑，无业务中断风险。

以下方案无需重构表结构即可生效，按优先级从高到低排列：

1. 为现有分表建立匹配查询逻辑的联合索引 #

当前查询慢的核心原因是缺少有效索引，只能全表扫描。从你给出的SQL来看，过滤维度优先级从高到低为time时间范围、longitude经度范围，直接给每个分表创建如下联合索引即可：

CREATE INDEX idx_celestial_time_lng ON {table_name}(time, longitude);

该索引会先通过时间条件快速裁剪掉查询范围外的绝大多数数据，再在剩余的小范围数据集上过滤经度条件，同时可以直接利用索引的有序性满足窗口函数ORDER BY time的排序要求，省掉内存/磁盘排序的开销，性能通常可提升10倍以上。注意不要颠倒字段顺序，否则时间范围裁剪效果会大幅下降。

2. 精简查询逻辑，减少逐行计算冗余 #

当前生成的SQL存在多处可优化的冗余计算：

• 提前在Rust侧计算常量：把birth_degree ± wanted_degree的模360结果提前算好作为常量传入SQL，不要让数据库引擎在逐行判断时重复计算固定值，同时简化经度范围判断逻辑，去掉重复的realModulo、abs函数调用。
• 删除无用列：内层查询中计算的day_scoped_date在后续逻辑中完全没有使用，直接移除即可减少不必要的计算开销。

3. 用UNION ALL替代循环逐表查询，减少交互次数 #

暂不合并表的情况下，不要在Rust侧循环向每个分表单独发起查询，仅拼接查询时间范围覆盖到的年份分表，用UNION ALL组成单个子查询一次性提交给SQLite执行，参考结构如下：

SELECT *,(SECOND_LAG>60 OR SECOND_LAG IS NULL) AS TRANSIT_START, (SECOND_LEAD > 60 OR SECOND_LEAD IS NULL) AS TRANSIT_END, time FROM (
    SELECT 
      *, 
      UNIX_TIME - LAG(UNIX_TIME,1) OVER (ORDER BY time) as SECOND_LAG,
      LEAD(UNIX_TIME,1) OVER (ORDER BY time) - UNIX_TIME as SECOND_LEAD FROM (
        SELECT 
          {select_param}, 
          CAST(strftime('%s', time) AS INT) AS UNIX_TIME,
          longitude
        FROM (
            SELECT * FROM Mars_2002 WHERE time BETWEEN '{parsed_lower_date_limit}' AND '{parsed_upper_date_limit}'
            UNION ALL
            SELECT * FROM Mars_2003 WHERE time BETWEEN '{parsed_lower_date_limit}' AND '{parsed_upper_date_limit}'
            -- 仅拼接时间范围内的分表，不要加入无关年份表
        )
        WHERE -- 填入简化后的经度过滤条件
    )
) WHERE (TRANSIT_START AND NOT TRANSIT_END) OR (TRANSIT_END AND NOT TRANSIT_START);

这种写法可以让SQLite一次性生成全局执行计划，消除逐次查询的交互开销，配合各分表的联合索引，性能已经和单表查询非常接近。

4. 调整SQLite基础配置 #

执行查询前设置如下PRAGMA参数，无需改表即可获得性能提升：

PRAGMA journal_mode = WAL; -- 关闭读写互斥，查询不会被写入操作阻塞
PRAGMA cache_size = -2000000; -- 分配2GB内存作为数据库页缓存，可根据机器内存调整，单位为KB
PRAGMA temp_store = MEMORY; -- 排序、临时表操作全在内存执行，避免磁盘临时文件开销

如果后续完成总表合并，建议将time字段从TEXT类型改为INTEGER类型直接存储Unix时间戳，省去查询时strftime类型转换、字符串比较的开销，可再带来30%左右的性能提升。

内容的提问来源于stack exchange，提问作者Mateusz Pydych