如何优化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，差距很明显