4000857126
在项目中有一个单表数据量比较大,曾经100w.现在小一点,但也是很可观的。问一下,对于大数据单表(有索引),查询条件多个,并且需要按照时间排序。有没有比较好一点的方法啊?
Java代码
select id, devicename, devicetype, alarm_level, logdesc, start_time, end_time, event_status, src_ip, src_port, dest_ip, dest_port, flow_alarm_type, flow_alias, group_name, event_man, event_advice, event_time from (select id, devicename, devicetype, alarm_level, logdesc, start_time, end_time, event_status, src_ip, src_port, dest_ip, dest_port, flow_alarm_type, flow_alias, group_name, event_man, event_advice, event_time from alarm_manage where 1=1 and (end_time is null or event_time is null ) and (event_status = '未确认' or event_status = '已确认') order by id desc) where rownum<=200
附上表结构
索引字段为去掉id之后的其他索引 DEVICENAME, DEVICETYPE, START_TIME, ALARM_LEVEL, LOGDESC, EVENT_STATUS
1、end_time is null or event_time is null 如果end_time/event_time 允许null 将不走索引,建议不用使用null值
2、建议使用下执行计划看看啥情况