性能文章>一次慢查询暴露的隐蔽的问题>

一次慢查询暴露的隐蔽的问题原创

709616

最近解决了一个生产 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 日志如下:

image.png

从日志中我们可以清楚看到实际运行的 SQL,以及查询参数与类型。

从查询语句看来,我们查询条件正确,且由于 CREATE_TIME 存在独立索引,所以查询会走索引,查询速度应该很快,不至于每次查询需要花费 60 多秒。

所以当时猜测这次查询由于某些原因发生了全表扫描,未走索引才导致慢查询。在 Google 搜索相关资料,看见一篇文章 https://www.cnblogs.com/chen--biao/p/9770554.html。

根据文章描述的是 Oracle 中存在隐式转换的情况,当类型不匹配的时,Oracle 会主动将类型转换成目标类型。查看我们表结构,CREATETIME 为 Date 类型,而根据日志我们查询参数传递的 CREATETIME 却为 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 执行计划如下。

image.png

从上图我们可以从 TB ACCESS FULL 看出,这次查询慢确实由于是全表扫描导致。

然后我们查看执行计划中的 Predicate Information 信息,Oracle 使用 INTERNALFUNCATIPON 转换 CREATETIME 类型 。从这点那可以看出查询过程索引字段发生一次内联函数转换。

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 数据类型。查看Mybatis 类型转换 这一节我们可以发现 Java Date 类型将会转换成 java.sql.TIMESTAMP。

image.png

然后我们查看 Oracle JDBC 数据类型转换规则。我们可以看到,TIMESTAMP 将转换成 Oracle 中 TIMESTAMP。

image.png

问题扩展

假设我们将 CREATETIME 类型修改成 TIMESTAMP,然后查询的时候将 CREATETIME 转换成 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。

image.png

我们可以看到,确实发生了一次内联转化,但是却在另外一边。这次查询走的是索引。

从这个例子我们可以看出,在索引字段上使用函数会导致全表扫描。但是在传入查询参数上使用函数并不会导致索引失效。

总结

1 SQL 查询时需要注意两边数据类型的一致性,虽然数据库隐式转换会帮我们解决数据不一致的问题,但是这种隐式转化带来一些隐蔽问题,让我们第一时间并不能很快发现。所以使用显示转换代替隐式转换。这样我们的 SQL 清晰易懂,而且更加可控。

2 学会使用 EXPLAIN PLAN 分析慢 SQL。

3 索引字段上使用相关函数会导致慢查询,查询时切勿在索引字段上使用函数。

请先登录,再评论

很有收获

1年前

为你推荐

字符串字面量长度是有限制的
前言 偶然在一次单元测试中写了一个非常长的字符串字面量。 正文 在一次单元测试中,我写了一个很长的字符串字面量,大概10万个字符左右,编译时,编译器给出了异常告警 `java: constant
多次字符串相加一定要用StringBuilder而不用-吗?
今天在写一个读取Java class File并进行分析的Demo时,偶然发现了下面这个场景(基于oracle jdk 1.8.0_144): ``` package test; public c
如何通过反射获得方法的真实参数名(以及扩展研究)
前段时间,在做一个小的工程时,遇到了需要通过反射获得方法真实参数名的场景,在这里我遇到了一些小小的问题,后来在部门老大的指导下,我解决了这个问题。通过解决这个问题,附带着我了解到了很多新的知识,我觉得
高吞吐、低延迟 Java 应用的 GC 优化实践
本篇原文作者是 LinkedIn 的 Swapnil Ghike,这篇文章讲述了 LinkedIn 的 Feed 产品的 GC 优化过程,虽然文章写作于 April 8, 2014,但其中的很多内容和
「每日五分钟,玩转 JVM」:久识你名,初居我心
聊聊 JVMJVM,一个熟悉又陌生的名词,从认识Java的第一天起,我们就会听到这个名字,在参加工作的前一两年,面试的时候还会经常被问到JDK,JRE,JVM这三者的区别。JVM可以说和我们是老朋友了
据说99.99%的人都会答错的类加载的问题
概述首先还是把问题抛给大家,这个问题也是我厂同学在做一个性能分析产品的时候碰到的一个问题。 同一个类加载器对象是否可以加载同一个类文件多次并且得到多个Class对象而都可以被java层使用吗请仔细注意
Java多线程——并发测试
编写并发程序时候,可以采取和串行程序相同的编程方式。唯一的难点在于,并发程序存在不确定性,这种不确定性会令程序出错的地方远比串行程序多,出现的方式也没有固定规则。那么如何在测试中,尽可能的暴露出这些问
Java多线程知识小抄集(一)
本文主要整理笔者遇到的Java多线程的相关知识点,适合速记,故命名为“小抄集”。本文没有特别重点,每一项针对一个多线程知识做一个概要性总结,也有一些会带一点例子,习题方便理解和记忆。 1.interr