数据库索引失效的一种场景：分析问题的思路和策略

这是公司研发团队发现的一个关于数据库索引失效方面的问题，我们的工程师对该问题进行了分析和解决并写了这份小结。归根揭底还是对开发框架和技术应用的把握上存在纰漏，但个人觉得在分析问题->找出原因->确认解决方案这一思路和策略上本文能起到一定借鉴作用，所以稍微梳理了一下拿出来和大家分享。

问题的现状是测试人员反馈某一个功能操作耗时很长（需要20秒以上），而开发人员核对代码发现无论从业务逻辑上还是代码实现上都没有问题，涉及到的数据查询等功能在数据库中也创建了合适的索引以确保查询效率，于是我们的工程师就去研究了，过程如下：

1.      首先应用内嵌入了一个系统监控平台JavaMelody（此为google出品的一个开源神器 https://code.google.com/p/javamelody/）

2.      通过该神器观测到该操作的方法执行帧信息，发现其中耗时最多的一步：

3.      下钻至此方法内部，看到其调用的sql为：

4.      对该sql进行分析，将其摘入PLSQL Developer工具内，并对变量赋值进行验证执行，反复验证多次发现每次速度都还不到1秒：

5.      就此产生疑惑：为何在服务器上执行需要20多秒，而在客户端工具内却飞快呢？猜测跟JDBC有关。为了证实这个想法，做了单元测试，果不其然，在单元测试中同样需要20多秒，为毛通过JDBC查询就这么慢呢？

6.      前思后想，在JDBC中和在客户端中查询最大的区别就在于前者使用了绑定变量，而后者是一个静态的sql。为了进一步验证，把单元测试中的sql变成了静态的（非变量绑定形式），发现快多了，和在第4步PLSQL Developer客户端工具中执行一样快，委屈了JDBC：

原来问题在于变量绑定。

问题找到了，那就要分析导致这种结果的原因。简单说来，我们的故事是这样的：

MapSqlParameterSource parameters = new MapSqlParameterSource();parameters.addValue("queryTimeFrom", queryTimeFrom );parameters.addValue("queryTimeTo", queryTimeTo);

其中queryTimeFrom和queryTimeTo是方法传进来的参数，类型为java.util.Date。

执行查询调用的是：

List<DrugOrderAdmin> result = getJdbcTemplate().query(sql.toString(), parameters, new DrugOrderAdminMapper());

本以为传进sql里的参数是Date类型，但其实经过了spring框架内部代码那么一折腾，最后变成了Timestamp类型：

其中 isDateValue方法的定义为：

再到oracle中，原本sql中的 AND PLAN_TIME >=?AND PLAN_TIME <= ?  这个条件，由于传入的参数是Timestamp类型，导致oracle解释器把sql解释为：

AND TO_TIMESTAMP(PLAN_TIME) >= ?AND TO_TIMESTAMP(PLAN_TIME) <= ?

这样一来，PLAN_TIME字段上的索引就被忽略，走了全表扫描（除非另外建一个函数索引TO_TIMESTAMP(PLAN_TIME)），导致最终查询速度产生了如此巨大的差别。

原因找到了，解决方案也就很明确了，即在绑定变量时强制指定JDBC类型为DATE：

parameters.addValue("queryTimeFrom", queryTimeFrom);改成:parameters.addValue("queryTimeFrom", queryTimeFrom , Types.DATE);parameters.addValue("queryTimeTo", queryTimeTo); 改成:parameters.addValue("queryTimeTo", queryTimeTo, Types.DATE);

再回到PLSQL Developer中，模拟一下传入参数为Timestamp类型的情况，这次采用绑定变量的方式，即在所有需要传入参数的地方预留一个变量（以&开头命名），在执行时客户端会自动弹出变量输入对话框：

这次可以看到，以绑定变量方式执行查询，并且绑定变量为Timestamp类型时，PLSQL Developer同样需要很久，之前的疑惑不释而解：

总结：在使用绑定变量进行sql查询时，一定要注意传入参数的类型和column类型一致，能够明确指定参数类型的情况下尽量明确指定，否则类型不当会导致列的索引失效。