遇到过一个Date类型作为条件查询走不上索引的问题,分析下原因:
以下为简化后的场景:通过时间范围作一个邮件发送数量的统计
java:
import java.util.Date;
public List<Object> listRecentTaskInfoByStatus( Date start, Date end,String sendType) {
Map<String,String> paraMap = new HashMap<String,String>();
paraMap.put(TASK_STAT_START_TIME, start);
paraMap.put(TASK_STAT_END_TIME, end);
return defaultDao.getObjList(nameSpace.get(sendType), paraMap, "CHANNEL_RECENT_STAT");
}
ibatis:
<select id="SELECT_CHANNEL_RECENT_STAT" resultMap="MtnPlanSendlog_RM_CHANNEL_TASK" parameterClass="java.util.HashMap">
<![CDATA[
SELECT /*+index (a SENDLOG_GSEND_IND)*/
overview_id,
count(*) as tmp_count
FROM
MTN_PLAN_SENDLOG a
WHERE
is_deleted = 'n'
and overview_id is not null
and GMT_SEND >= #startTime#
and GMT_SEND < #endTime#
GROUP BY overview_id
]]>
</select>
现象:
sql查询缓慢,DBA观察发现没有走上GMT_SEND的索引
原因:
由于需要小时分秒的信息,我们使用的是java.util.Date,观察发现在数据库端有类似如下函数转换
TO_TIMESTAMP(date_column) = parameter_timestamp
导致纵然加了hit也走不上索引.
为什么会触发oracle做隐式转换呢?因为在ibatis的处理中,java.util.Date会转换为java.sql.Timestamp(具体原因可以参考java.sql.PreparedStatement和相关文档,在此不详述)
ps.setTimestamp(i, new java.sql.Timestamp(((Date) parameter).getTime()));
JAVA传下去的是Timestamp,而数据库的类型是Date,根据oracle的策略,会对date类型做强制转换TO_TIMESTAMP(GMT_SEND),因此就走不上索引了 。
解决方案:
经过网上查询以及和郑隐、肖建冰、胡伟军同学的讨论,认为可选且比较靠谱的解决方案主要有3种:
1. 将数据库的列改为timestamp(风险较太大)
2. 使用to_date('2011-03-08 15:45:43.123','yyyy-mm-dd HH24:MI:SS....')
3. 将传入参数由java.util.Date转换为String,然后在ibatis端使用to_date函数解决
and GMT_SEND >= to_date(#startTime#,'YYYY-MM-DD HH24:MI:SS')