JPA解决时间类型不一致导致索引不生效

一 场景

在某一功能点击根据时间查询，查询速度达到2分钟，而且为单表查询，表数据量600w

二 验证问题

将sql在数据库可视化工具中执行，发现执行速度正常，而且走索引。但是，在应用中查询，却达到2分钟。

三 分析

在应用中查询应该没有走索引，因为字段为时间字段，那么很可能是在数据库做了函数转换。

四 解决

操作 1 在测试环境还原，测试环境该表数据量50w，体现不出加所以和不加索引的区别

操作 2 联系DBA DBA说存在，时间类型在数据库中增加了函数转换

操作 3 在测试环境数据库增加表 并设定为600w条数据

create table test_table as select rownum as id from xmltable('1 to 6000000');

alter table test_table add  (printed_Date DATE);

ALTER TABLE test_table ADD (IDENTITY_CARD VARCHAR2(20 CHAR));

ALTER TABLE test_table ADD (COMMITMENT_NC_CODE VARCHAR2(128 CHAR) );
COMMENT ON COLUMN test_table.COMMITMENT_NC_CODE IS '应收结算单位NC代码';

ALTER TABLE test_table ADD (CUSTOMS_NC_CODE VARCHAR2(128 CHAR) );
COMMENT ON COLUMN test_table.CUSTOMS_NC_CODE IS '供应商NC代码';

ALTER TABLE test_table ADD (COMMITMENT_NC_CODE VARCHAR2(128 CHAR) );
COMMENT ON COLUMN test_table.COMMITMENT_NC_CODE IS '应收结算单位NC代码';

ALTER TABLE test_table ADD (CUSTOMS_NC_CODE VARCHAR2(128 CHAR) );
COMMENT ON COLUMN test_table.CUSTOMS_NC_CODE IS '供应商NC代码';

ALTER TABLE test_table ADD (FREIGHT_TYPE VARCHAR2(128 CHAR) );
COMMENT ON COLUMN test_table.FREIGHT_TYPE IS '费用类别';

为表添加数据

这里可以设置不同时间
UPDATE test_table SET PRINTED_DATE = sysdate +10 WHERE ID > 1000000 and ID<3000000

为同一列不同行设置随机数
update test_table r 
set r.FREIGHT_TYPE = 
(select floor(dbms_random.value(100,99999999)) from dual)
where id > 2000000 and id<2100000

对printed_Date 添加索引  
CREATE INDEX IDX_test_table2 ON test_table (printed_date);

在java中增加实体类

/**
 * @author 子羽
 * @Description 测试实体类
 * @Date 2021/8/30
 */
@Entity
@Table(name = "TEST_TABLE")
public class TestTable {
    @Id
    @GeneratedValue(strategy =  GenerationType.AUTO)
    private String id;

    private Date printedDate;

    private String commitmentNcCode;


    private String freightType;

    -------
}

执行查询

SELECT *
FROM (
        SELECT *
        FROM test_table testtable0_
        WHERE 1 = 1
                AND testtable0_.printed_date >= TO_DATE('2017-08-31', 'yyyy-mm-dd')
                AND testtable0_.printed_date < TO_DATE('2021-09-30', 'yyyy-mm-dd')
        ORDER BY testtable0_.printed_date DESC
)
WHERE rownum <= 11;

在数据库可视化工具中查询 查询时间不到2s 在测试环境页面执行查询 查询时间接近6s

说明页面查询没有走索引

分析：jpa底层是orm框架，是不是时间属性的映射有问题，于是在时间属性printed_date上增加 @Temporal(TemporalType.DATE)注解

@Entity
@Table(name = "TEST_TABLE")
public class TestTable {
    @Id
    @GeneratedValue(strategy =  GenerationType.AUTO)
    private String id;
    
    @Temporal(TemporalType.DATE)
    private Date printedDate;

    private String commitmentNcCode;


    private String freightType;

    -----
}

在页面执行查询，查询时间不到2s，和数据库可视化工具查询时间一一致

进一步测试，删除索引

drop index  IDX_test_table2;

页面查询时间接近6s，完全证明页面查询走了索引。

结论

就算java中的属性是Date类型，也要在属性上标记 @Temporal(TemporalType.DATE)，否则无法和数据库中的字段最映射，查询时会出现数据库隐形的函数转换，导致索引失效