SQLite中带WHERE子句的COUNT类排行榜查询性能优化

• 业务场景：当前正在开发排行榜功能，用于计算榜单的核心查询语句如下：

  SELECT id, COUNT(type) FROM Data 
  WHERE UPPER(type) = $1 AND id != '123123123123' 
  GROUP BY id ORDER BY COUNT(type) DESC LIMIT 10;
  

• 数据表现状：
  • Data表总存量约400万行，每日新增数据10万行以上
  • 表上已建1个基于code、version列的组合唯一索引，用于实现两列组合值的唯一性约束
• 当前查询表现：上述SQL执行耗时约8-10秒，无法满足业务响应标准
• 已尝试的优化路径：
  • 考虑为每个用户的行数据维护独立计数器，但由于type的取值多达100余种，方案落地复杂度极高，不具备可行性
  • 已尝试在id、type列上添加联合索引，但查询耗时没有明显改善，部分场景下耗时甚至超过8-10秒
• 核心目标：寻找可落地的优化方案，提升该排行榜查询的执行速度，同时保障数据库整体运行性能

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