EF Core生成的SQLite数据库已建索引仍查询缓慢优化咨询

一、索引优化（优先级最高） #

你当前建立的是SymbolId和TimeStamp的单列索引，而你所有的查询都需要同时按交易对过滤、按时间排序/聚合，单列索引无法协同生效，是慢查询的核心原因，按场景调整索引即可获得十倍以上的性能提升：

• 针对指定交易对最新10条记录查询：建立Trades(SymbolId, TimeStamp DESC)联合索引，如果查询需要返回价格、交易量等字段，可以直接将这些字段加入索引做成覆盖索引，例如Trades(SymbolId, TimeStamp DESC, Price, Amount)，查询无需回表，即可毫秒级返回结果。
• 针对按月统计价格高低点、按交易对统计交易量等聚合查询：高频查询建议直接做预计算汇总表，按交易对+月份维度提前预存最高/最低价、总交易量，新数据入库时同步更新汇总表，查询直接返回预计算结果。低频查询可以建立Trades(SymbolId, TimeStamp, Price, Amount)联合索引，让聚合操作直接走索引无需回表。
• EF Core使用注意：避免在TimeStamp等索引字段上调用DATE()、YEAR()等函数，会导致索引失效，尽量改成区间过滤写法，例如查询2024年1月数据写为TimeStamp >= new DateTime(2024,1,1) && TimeStamp < new DateTime(2024,2,1)，保证可以命中联合索引。

二、SQLite专属参数调优 #

SQLite默认参数适配小体量数据库，50GB以上量级必须调整核心参数：

• 调整缓存大小：执行PRAGMA cache_size = -20971520（负号代表单位为KB，示例为20GB缓存），建议设置为当前服务器可用物理内存的1/3到1/2，尽量将热点索引存在内存中，减少磁盘IO。
• 调整页大小：执行PRAGMA page_size = 32768，将默认4KB页大小调整为32KB，大库场景下可以大幅降低随机IO次数，该参数需要在建库时设置，已建好的库需要执行VACUUM重建生效。
• 调整读写模式：执行PRAGMA journal_mode = WAL开启WAL模式，解决读写阻塞问题，大幅提升并发读写性能。执行PRAGMA synchronous = NORMAL，如果数据可靠性要求不高可以设为OFF，减少磁盘同步开销。
• 关闭冗余校验：如果不需要外键约束，执行PRAGMA foreign_keys = OFF关闭外键校验，降低额外开销。

三、是否需要更换PostgreSQL？ #

5亿条记录的量级SQLite本身完全可以支撑，仅当存在以下场景时建议更换：

• 需要支持多进程并发写入，SQLite写锁为库级锁，高并发写入场景下锁等待会非常严重。
• 需要运行大量复杂分析查询，PostgreSQL的查询优化器、并行查询、列式存储扩展能力远强于SQLite，分析类查询性能提升会非常明显。
• 后续数据量会持续增长到100GB以上，SQLite的VACUUM、全量备份等运维操作耗时会大幅上升，PostgreSQL的成熟运维工具可以降低运维成本。

如果调整索引和参数后，常用查询耗时可以降到1秒以内，完全可以继续使用SQLite，无需更换数据库。

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