基于R脚本的SQLite数据库性能优化方案咨询

一、SQLite核心配置调优 #

• 开启WAL模式：执行PRAGMA journal_mode=WAL;，该模式支持读写并发，避免默认DELETE模式下读阻塞写的问题，对每日有ETL写入、同时Shiny应用需查询的场景提升明显。
• 调大内存缓存：执行PRAGMA cache_size=-2048;（负数单位为MB，此处设置2GB缓存），让SQLite将更多热数据驻留内存，大幅减少磁盘IO开销。
• 调整同步策略：若ETL流程有可靠备份、可接受极端场景下的少量数据丢失，执行PRAGMA synchronous=NORMAL;，替代默认的FULL模式，降低磁盘写入等待时间。
• 启用查询计划缓存：执行PRAGMA query_plan_cache=1;，重复查询无需重新生成执行计划，节省CPU资源。

二、数据模型优化 #

• 日期字段类型重构：将Datetime、Day、Week、Month、Year等文本类型改为INTEGER（存储Unix时间戳）或DATETIME类型，SQLite对数值类型的索引与查询效率远高于文本。同时可删除冗余的Day/Week等字段，查询时通过strftime('%Y-%m-%d', Datetime)按需生成，保证数据一致性。
• 构建复合索引：针对你的聚合查询场景，替换单一Day索引为复合索引：
  • 按日聚合exporter-importer对：CREATE INDEX idx_maritime_day_from_to ON maritime_transport(Day, From, To);
  • 同理为周/月/年聚合场景创建对应复合索引（如idx_maritime_month_from_to），让查询直接通过索引覆盖返回结果，无需回表扫描。
• 合并同构大表（可选）：三张运输表结构完全一致，可合并为单表并新增TransportType字段（取值maritime/railway/air），减少索引维护成本，跨运输方式的聚合查询也更高效。

三、预聚合物理表替代动态视图 #

动态视图每次查询都需全表扫描计算，大数据量下性能极差，最优方案是将聚合逻辑转移至ETL阶段，生成预聚合物理表：

1. 设计分层聚合表，比如日粒度聚合表：

   CREATE TABLE daily_balance (
       TransportType TEXT NOT NULL,
       Day TEXT NOT NULL,
       From TEXT NOT NULL,
       To TEXT NOT NULL,
       Balance NUMERIC NOT NULL,
       PRIMARY KEY (TransportType, Day, From, To)
   );
   

2. 每日ETL完成后，仅针对新增日期的数据做聚合更新：

   -- 假设新增数据日期为'2024-05-20'
   INSERT INTO daily_balance (TransportType, Day, From, To, Balance)
   SELECT 'maritime', Day, From, To, SUM(Value) AS Balance
   FROM maritime_transport
   WHERE Day = '2024-05-20'
   GROUP BY Day, From, To
   ON CONFLICT(TransportType, Day, From, To) DO UPDATE SET Balance = Balance + excluded.Balance;
   

3. 周/月/年粒度的聚合可直接基于日表二次计算，无需再扫描原始大表，Shiny应用查询时直接读取聚合表，响应速度能提升数倍甚至数十倍。

四、查询语句优化 #

• 避免SELECT *：仅查询业务需要的字段，减少数据传输量与内存占用。
• 利用索引覆盖：确保查询的字段均包含在索引中，让SQLite直接通过索引返回计算结果，无需回表。
• 分批加载数据：若需展示大量数据，使用LIMIT/OFFSET或按日期范围拆分查询，避免单次查询占用过多资源。

五、应用层配合优化 #

• 缓存查询结果：在Shiny应用中使用memoise或shiny::reactiveCache缓存高频聚合查询结果（如当日余额数据），避免重复向数据库发起请求。
• 异步处理查询：用future/promises包实现异步查询，让耗时查询在后台运行，不阻塞UI交互。

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