如何优化SQLite的性能?

发表时间: 2024-02-29 18:46

有一张表attack_logs,用于存储攻数据,有如下字段

字段说明

service

src_ip

dst_ip

behavior

behavior_count

timestamp

end_timestamp

服务

源IP

目标IP

内容行为

攻击次数

开始时间戳

结束时间戳

有如下索引

执行语句 SELECT count(*) FROM attack_logs

可以看到,总数为超过100W条,耗时159ms

执行plan看下,

EXPLAIN QUERY PLAN SELECT count(*) FROM attack_logs

可知,用到了timestamp的索引

执行语句 SELECT count(*) FROM attack_logs GROUP BY src_ip,dst_ip

src_ip和dst_ip分组,耗时 531ms

执行语句 EXPLAIN QUERY PLAN SELECT count(*) FROM attack_logs GROUP BY src_ip,dst_ip

上图可知道,用到srcdst索引,而且是覆盖索引,不需要回表查询

执行语句 SELECT count(*) FROM attack_logs WHERE (timestamp between 1708963200 and 1709222399) GROUP BY src_ip,dst_ip

可以看到,多加了一条where条件,耗时增加到27s,

我们看看这条sql,做了哪些事件 EXPLAIN QUERY PLAN SELECT count(*) FROM attack_logs WHERE (timestamp between 1708963200 and 1709222399) GROUP BY src_ip,dst_ip

我们看到,用到了srcdst的普通索引,需要进行回表查询比较每条记录的时间

那么我们是不是可以建联合索引,把src_ip,dst_ip,timestamp一起做索引

我们在执行sql EXPLAIN QUERY PLAN SELECT count(*) FROM attack_logs WHERE (timestamp between 1708963200 and 1709222399) GROUP BY src_ip,dst_ip

通过上图,我们看到,用到了覆盖索引,我们再执行sql看看耗时

SELECT count(*) FROM attack_logs WHERE (timestamp between 1708963200 and 1709222399) GROUP BY src_ip,dst_ip

可以看到,耗时明显减少

注意,联合索引的优先级很重要,如上面的联合索引顺序为src_ip>dst_ip>timestamp,如果把优先级调整一下,为

我们再执行 EXPLAIN QUERY PLAN SELECT count(*) FROM attack_logs WHERE (timestamp between 1708963200 and 1709222399) GROUP BY src_ip,dst_ip

这时用到了临时的B+树来给src_ip和dst_ip做分组,我们看看耗时

可以看到,耗时11s,差距很明显