最近解决了一个生产 sql 慢查询的问题，排查问题之后发现一些比较隐匿且容易忽略的问题。
最近业务上需要上线一个预警功能，需要查出一段时间内交易，求出当前交易成功率。当成功率低于设定阈值时，短信预警。业务逻辑很简单，测试环境测试也没问题之后，部署上线。实际生产运行时却发现每次 sql 查询需要花费 60 多秒。
spring boot + mybatis + oracle。
需要查询的表数量级为亿级。
交易表结构(已经简化)大致如下。
我要评论create table tb_test
(
bank_code varchar2(20),
create_time date,
oid_bill number(16) not null
)
/
create index tb_test_create_time_index
on tb_test (create_time)
/
create unique index tb_test_oid_bill_uindex
on tb_test (oid_bill)
/
alter table tb_test
add constraint tb_test_pk
primary key (oid_bill)
/
该项目的增删改查语句使用 mybatisgenerate 自动生成，查询语句使用 create_time 做为条件查询，自动生成 sql 如下。
select *
from tb_test
where create_time >= #{start_time}
and create_time < #{end_time};
我们通过设置 druid 的配置，将具体查询 sql 日志输出到控制台。具体设置如下。
<bean id="datasource" class="com.alibaba.druid.pool.druiddatasource" init-method="init" destroy-method="close">
... ...
<property name="filters" value="stat,slf4j" />
</bean>
<!-- logback -->
<logger name="druid.sql.statement" level="debug" additivity="false">
<appender-ref ref="stdout"/>
</logger>
具体 sql 日志如下：
从日志中我们可以清楚看到实际运行的 sql，以及查询参数与类型。
从查询语句看来，我们查询条件正确，且由于 create_time 存在独立索引，所以查询会走索引，查询速度应该很快，不至于每次查询需要花费 60 多秒。
所以当时猜测这次查询由于某些原因发生了全表扫描，未走索引才导致慢查询。在 google 搜索相关资料，看见一篇文章 。
根据文章描述的是 oracle 中存在隐式转换的情况，当类型不匹配的时，oracle 会主动将类型转换成目标类型。查看我们表结构，create_time 为 date 类型，而根据日志我们查询参数传递的 create_time 却为 timestamp 类型。
所以实际在数据库查询 sql 如下：
select *
from tb_test
where (create_time >= to_timestamp('2018-03-03 18:45:32', 'yyyy-mm-dd hh24:mi:ss') and
create_time < to_timestamp('2019-01-03 18:45:32', 'yyyy-mm-dd hh24:mi:ss'));
可能这里发生一次隐式转换。
如何证明这个猜想那？我们可以使用 explain plan ，分析 sql 执行计划.上面 sql 执行计划如下。
从上图我们可以从 tb access full 看出，这次查询慢确实由于是全表扫描导致。
然后我们查看执行计划中的 predicate information 信息，oracle 使用 internal_funcatipon 转换 create_time 类型 。从这点那可以看出查询过程索引字段发生一次内联函数转换。
sql 性能优化往往会有一点，避免在索引字段使用函数。
既然知道原因，那么解决办法也没有这么难了。我们将查询 sql 改为如下就能解决。
select *
from tb_test
where create_time >= to_date(#{start_time}, 'yyyy-mm-dd hh24:mi:ss')
and create_time < to_date(#{end_time}, 'yyyy-mm-dd hh24:mi:ss');
-- 或者使用 cast 函数
select *
from tb_test
where create_time >= cast(#{start_time} as date)
and create_time < cast(#{end_time} as date);
解决完问题，我们分析下 java 类型中的 date 类型为什么最终会转换成 oracle 中的 timestamp 类型。
这次案例中我们使用 mybatis 框架，框架内部会将 java 数据类型转换成对应的 jdbc 数据类型。查看 这一节我们可以发现 java date 类型将会转换成 java.sql.timestamp。
然后我们查看 oracle jdbc 数据类型转换规则。在 https://docs.oracle.com/cd/b19306_01/java.102/b14355/datacc.htm#bhcjbjcc 我们可以看到，timestamp 将转换成 oracle 中 timestamp。
假设我们将 create_time 类型修改成 timestamp，然后查询的时候将 create_time 转换成 date 类型，是否也会发生内联函数转换，然后导致全表扫描那？查询 sql 如下。
-- create_time 类型为 timestamp
select *
from tb_test
where create_time >= to_date('2018-02-27 19:36:21', 'yyyy-mm-dd hh24:mi:ss')
and create_time < to_date('2018-12-27 19:36:21', 'yyyy-mm-dd hh24:mi:ss')
。。。。
。。。。
。。。。
我们用 explain plan 分析这个 sql。
我们可以看到，确实发生了一次内联转化，但是却在另外一边。这次查询走的是索引。
从这个例子我们可以看出，在索引字段上使用函数会导致全表扫描。但是在传入查询参数上使用函数并不会导致索引失效。
1 sql 查询时需要注意两边数据类型的一致性，虽然数据库隐式转换会帮我们解决数据不一致的问题，但是这种隐式转化带来一些隐蔽问题，让我们第一时间并不能很快发现。所以使用显示转换代替隐式转换。这样我们的 sql 清晰易懂，而且更加可控。
2 学会使用 explain plan 分析慢 sql。
3 索引字段上使用相关函数会导致慢查询，查询时切勿在索引字段上使用函数。
1、 https://docs.oracle.com/cd/b19306_01/server.102/b14200/sql_elements002.htm#g195937
2、
如果觉得好的话，请帮作者点个赞呗~ 谢谢
喜欢本文的读者们，欢迎长按关注订阅号程序通事~让我与你分享程序那些事。
如对本文有疑问， 点击进行留言回复！！
Mybatis PersistenceException异常：Error building SqlSession The error may exist in SQL Mapper Configur
MyBatis——动态SQL语句——if标签和where标签复合使用
网友评论