有一张表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,差距很明显