这条sql使用了where条件,查询时间很慢,但是通过查看表定义发现已经建立时间索引,后经同事指点发现使用to_char类型是无法击中索引的,而occur_time列的类型为timestamp()需要查询条件也是时间类型才可以走索引查询,后将sql改为:
优化前:
--执行成功,执行耗时34秒446毫秒 select t.CATEGORY_ID as categoryId, sum(ifnull(t.insert_success_number, 0)+ifnull(t.update_success_number, 0)) as dataCap , t.CATEGORY_ENG as categoryEng from TEST.TEST_ALL_2022 t where to_char(t.OCCUR_TIME, 'yyyy-MM-dd') = '2022-03-30' group by t.CATEGORY_ID, t.CATEGORY_ENG
优化后
--执行成功,执行耗时3秒102毫秒 select t.CATEGORY_ID as categoryId, sum(ifnull(t.insert_success_number, 0)+ifnull(t.update_success_number, 0)) as dataCap , t.CATEGORY_ENG as categoryEng from TEST.TEST_ALL_2022 t where OCCUR_TIME>='2022-03-30 00:00:00' and OCCUR_TIME<='2022-03-31 00:00:00' group by t.CATEGORY_ID, t.CATEGORY_ENG
评论已关闭