sqlite - SQLite改进大小写和按性能分组

Question

我正在使用SQLite3优化查询。
有一些“ CASE WHEN”，“ GROUP BY”，“ COUNT”功能。

但是查询非常慢（大约14秒）

这是我的数据库文件信息。



大小：约2GB
行：约300万
列：55列



我该如何优化查询的性能？

是否有更好的查询结果？

请帮助我TT谢谢。

select
  case
    when score = 100 then 'A'
    when score < 100 and score >= 40 then 'B'
    else 'C'
  end as range,
  count(*) as count
from grade_info
where type < 9 and 
   (date >= '2019-07-09 00:00:00' and date <= '2019-07-09 23:59:59') and 
   is_new = 1
group by
  case
    when score = 100 then 'A'
    when score < 100 and score >= 40 then 'B'
    else 'C'
  end;

表 grade_info具有多列索引： (type, date, is_new, score)

在此查询中始终使用列的条件（类型，日期，is_new）。这是解释查询计划的结果。

 selectid | order | from | detail
 --------------------------------
 0          0       0      SEARCH TABLE grade_info USING INDEX idx_03 (type<?) (~2777 rows) 
 0          0       0      USE TEMP B-TREE FOR GROUP BY 

我想要这样的结果。

A | 5124
B | 124
C | 12354

Answer 1

如Shawn建议的那样，尝试更改索引以将date列作为第一列：

CREATE INDEX [idx_cover] ON [grade_info] ([date], [is_new], [type], [score]);

sqlite允许在WHERE和GROUP BY子句中引用别名表达式，因此您可以简单地说 GROUP BY range而不是重复CASE语句。这可能不会改变效率，但是会使查询更短且更具可读性。
如果按照MikeT的建议运行 ANALYZE，则执行计划应更改为“ COVERING INDEX ...”。如果我理解正确，则表明可以通过遍历单个多列索引来执行整个查询，而无需返回表数据。
尝试日期 BETWEEN '2019-07-09 00:00:00' AND '2019-07-09 23:59:59'。
最后，CASE ... WHEN发生短路，因此请确保首先放置更可能的情况，以避免不必要的计算。同时消除多余的条件检查。如果您已经在上一个条件中检查了某个范围，则无需在下一个条件中重新评估该范围。 （如果您已经排除了分数= 100，那么就不必检查分数<100，因为它当然会小于100 ...假设所有分数都确保在0到100之间）。例如，如果分数是均匀分布的，则后续操作可能会更快，可能会消除+17000个条件检查。

SELECT
  CASE
    WHEN score < 40 then 'C'
    WHEN score < 100 then 'B'  -- already tested to be >= 40
    ELSE 'A'                   -- already tested to be >= 100
  END AS range,
  count(*) AS count
FROM grade_info
WHERE type < 9 AND
      (date BETWEEN '2019-07-09 00:00:00' AND '2019-07-09 23:59:59') AND
      is_new = 1       
GROUP BY
  range;