Photo by Iga Palacz on Unsplash

Recently, I solved a problem of slow SQL query production. After troubleshooting, I found some hidden and easy to ignore problems.

Service Background

Recently, the business needs to launch an early warning function, which needs to find out the transaction within a period of time and calculate the success rate of the current transaction. When the success rate is lower than the threshold, the system sends an SMS warning. Once the business logic is simple and the test environment is well tested, the deployment goes live. The actual production run found that each SQL query took more than 60 seconds.

System Architecture

Spring Boot + Mybatis + Oracle.

The tables to be queried are of hundreds of millions.

Troubleshoot problems

The trade table structure (simplified) looks like this.

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)
/

Copy the code

MybatisGenerate was used as the query statement, CREATE_TIME was used as the conditional query statement, and the SQL was automatically generated as follows:


select *
from TB_TEST
where CREATE_TIME >= #{start_time}
  and CREATE_TIME < #{end_time};

Copy the code

We set up Druid’s configuration to output specific query SQL logs to the console. The specific Settings are as follows.

  <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>
Copy the code

SQL logs are as follows:

From the logs, we can clearly see the SQL actually running, as well as the query parameters and types.

Select * from CREATE_TIME; select * from CREATE_TIME; select * from CREATE_TIME;

So at that time guess this query due to some reasons occurred in the full table scan, did not walk index led to slow query. Search for relevant information on Google and see an article www.cnblogs.com/chen–biao/… .

When a type does not match, Oracle will actively convert the type to the target type. CREATE_TIME = Date; TIMESTAMP = TIMESTAMP;

So the actual SQL query in the database is as follows:


SELECT *
FROM TB_TEST
WHERE (CREATE_TIME >= to_timestamp('the 2018-03-03 18:45:32'.'yyyy-mm-dd hh24:mi:ss') and
       CREATE_TIME < to_timestamp('the 2019-01-03 18:45:32'.'yyyy-mm-dd hh24:mi:ss'));

Copy the code

Maybe there’s an implicit conversion going on here.

How do you prove this conjecture? We can analyze SQL execution plans using EXPLAIN Plans. The above SQL execution plan is as follows.

SQL > select * from TB ACCESS FULL;

Then we look at the Predicate Information in the execution plan, and Oracle converts the CREATE_TIME type using INTERNAL_FUNCATIPON. From this you can see that the query process index field has an inline function conversion.

SQL performance tuning tends to be a bit of an avoidance of using functions in index fields.

Now that we know the cause, the solution is not so difficult. We can solve this problem by changing the query SQL to the following.


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');

Or use the cast function
select *
from TB_TEST
where CREATE_TIME >= cast(#{start_time} as date)
  and CREATE_TIME < cast(#{end_time} as date);
Copy the code

The analysis reason

Having solved the problem, let’s examine why the Date type in Java was converted to the TIMESTAMP type in Oracle.

In this case, we use the Mybatis framework, which internally converts Java data types to the corresponding JDBC data types. Looking at the Mybatis conversion section, we can see that the Java Date type will be converted to java.sql.TIMESTAMP.

Then we looked at the Oracle JDBC data type conversion rules. In docs.oracle.com/cd/B19306_0… We can see that TIMESTAMP will be converted to TIMESTAMP in Oracle.

Problem extension

If we change CREATE_TIME to TIMESTAMP and then query CREATE_TIME to Date, would an inline function conversion also occur and result in a full table scan? The query SQL is as follows.

-- the CREATE_TIME type is TIMESTAMP
select *
from TB_TEST
where CREATE_TIME >= TO_DATE('the 2018-02-27 19:36:21'.'yyyy-mm-dd hh24:mi:ss')
  and CREATE_TIME < TO_DATE('the 2018-12-27 19:36:21'.'yyyy-mm-dd hh24:mi:ss')

Copy the code

.

.

.

We analyze this SQL with EXPLAIN PLAN.

We can see that an inline transformation does take place, but on the other side. This query follows the index.

We can see from this example that using functions on index fields causes a full table scan. But using a function on an incoming query parameter does not invalidate the index.

conclusion

1 SQL queries need to pay attention to the consistency of data types on both sides, although the database implicit conversion will help us solve the problem of inconsistent data, but this kind of implicit conversion brings some hidden problems, so that we can not be found quickly at the first time. Instead of implicit conversions, display conversions are used. This makes our SQL clearer and more manageable.

Learn to use EXPLAIN PLAN to analyze slow SQL.

3 Do not use related functions on index fields, which may slow query.

Reference documentation

2. Oracle time type

If you feel good, please give the author a thumbs up ~ thank you

To those who like this article, please click on our subscription number and let me share with you about our app.

3. Use Threadlocal in dubbo. Learn the template method of design patterns from source code