SQLite数据库除法计算最优方案及Ruby Sequel性能优化咨询

速度是否正常？ #

绝对不正常。单个ObjectIndex仅9000行数据，查询耗时10秒远超SQLite的正常表现，核心原因是缺少合适的索引，导致每次查询都要扫描全表（580多万行），效率极低。

优化方案 #

1. 新增复合索引（最关键）

针对你的查询模式（按ObjectIndex过滤，按TimeIndex排序并取Value），创建复合索引可以让数据库直接定位到目标数据，无需全表扫描：

CREATE INDEX idx_my_table_obj_time_value ON my_table (ObjectIndex, TimeIndex, Value);

这个索引包含了查询所需的所有字段，数据库可以直接从索引中获取数据（覆盖索引），查询速度会大幅提升。

2. 让数据库直接完成计算，减少Ruby端开销

你当前的方案是把两组数据拉到Ruby后再做除法，既浪费IO带宽，又占用Ruby内存。可以通过SQL JOIN让数据库直接完成关联和计算，只返回最终结果：

division = db[:my_table].as(:t1)
  .join(:my_table.as(:t2), TimeIndex: :TimeIndex)
  .where(t1__ObjectIndex: 1, t2__ObjectIndex: 2)
  .order(:t1__TimeIndex)
  .select_append(
    # 处理除数为0的情况，计算后保留两位小数
    Sequel.case(
      [[Sequel.expr(:t2__Value) == 0, 0]],
      (Sequel.expr(:t1__Value).cast(Float) / Sequel.expr(:t2__Value).cast(Float)).round(2)
    ).as(:ratio)
  )
  .select_map(:ratio)

这样数据库会一次性完成TimeIndex的匹配、除法计算和结果返回，避免了两次查询和Ruby端的数组处理，性能提升明显。

3. 调整SQLite运行参数

根据服务器内存情况，调整SQLite的缓存和模式参数，进一步提升性能：

# 增大缓存（设置为800MB，根据实际内存调整，单位是页，1页=4096字节）
db.execute("PRAGMA cache_size = -200000")
# 启用WAL模式，提升读写并发性能
db.execute("PRAGMA journal_mode = WAL")
# 如果可以接受断电数据丢失的风险，关闭同步写入（大幅提升速度）
db.execute("PRAGMA synchronous = OFF")

原方案慢的原因 #

• 无索引导致两次全表扫描，每次都要遍历580多万行数据找9000条目标记录；
• 把大量数据从数据库传输到Ruby端，再做数组拼接和计算，额外增加IO和CPU开销。

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