1. 框架
mybatis,oracle
2. 问题
昨天DBA发来邮件说有一个SQL引起CPU高负载。查看了一下SQL,根据时间条件查询,时间是有索引的,但是CPU会突然飙高。
SELECT * FROM (
SELECT t1.*,rownum as rn
FROM (
SELECT count(:"SYS_B_0") as COUNT ,T.USER_ID, T.USER_NAME
FROM TEST T
WHERE :"SYS_B_1"=:"SYS_B_2" AND CREATED_TIME >= :4 AND CREATED_TIME <= :5
group by T.USER_ID,T.USER_NAME ORDER BY COUNT desc ) t1
WHERE rownum <= :6 ) t2
WHERE rn >= :7
然后看一下SQL的执行计划,发现该SQL进行了全表扫描。