A scenario

Click query by time in a certain function, the query speed reaches 2 minutes, and it is a single table query with 600W table data

Ii. Verification Problem

SQL is executed in the database visualization tool, and the execution speed is normal, and the index goes. However, in the application query, it takes 2 minutes.

Three analysis

The query should not be indexed in the application because the field is a time field, so it is likely to be a function conversion in the database.

4 to solve

Operation 1 restores the table in the test environment. In the test environment, the amount of data in the table is 50w, and the difference between adding data and not adding indexes is not shown

Operation 2 contact the DBA and the DBA says that the time type exists and adds a function conversion to the database

Operation 3 Add a table to the test environment database and set it to 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_code '; ALTER TABLE test_table ADD (CUSTOMS_NC_CODE VARCHAR2(128 CHAR) ); COMMENT ON COLUMN test_table.CUSTOMS_NC_CODE IS 'vendor NC code '; ALTER TABLE test_table ADD (COMMITMENT_NC_CODE VARCHAR2(128 CHAR) ); COMMENT ON COLUMN test_table.COMMITMENT_NC_CODE IS 'NC_code '; ALTER TABLE test_table ADD (CUSTOMS_NC_CODE VARCHAR2(128 CHAR) ); COMMENT ON COLUMN test_table.CUSTOMS_NC_CODE IS 'vendor NC code '; ALTER TABLE test_table ADD (FREIGHT_TYPE VARCHAR2(128 CHAR) ); COMMENT ON COLUMN test_table.FREIGHT_TYPE IS 'test_table ';Copy the code

Add data to the table

UPDATE test_table SET PRINTED_DATE = sysdate +10 WHERE ID > 1000000 and ID<3000000 SET random number UPDATE for different rows in the same column Test_table r set r.freight_type = (select floor(dbms_random.value(100,99999999)) from dual) where id > 2000000 and Id <2100000 add INDEX to printed_Date CREATE INDEX IDX_test_table2 ON test_table (printed_Date);Copy the code

Add entity classes in 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; -- -- -- -- -- -- --}Copy the code

Execute the query

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

The query time in the database visualization tool is less than 2s. The query time in the test environment page is close to 6s

Note No index is displayed on the page

Jpa is the orM framework at the bottom, so there is a problem with the mapping of the time attribute, so add @temporal (temporalType.date) annotation to the time attribute printed_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; -- -- -- -- --}Copy the code

In the page to execute the query, the query time is less than 2s, and the database visualization tool query time is consistent

To further test, drop the index

drop index  IDX_test_table2;
Copy the code

The page query time is close to 6s, which completely proves that the page query went to the index.

conclusion

Even if the attribute in Java is of Date type, it should be marked with @Temporal(temporalType.date) on the attribute, otherwise it cannot be the most mapped to the field in the database, and the database will have invisible function conversion during query, resulting in index failure