Small knowledge, big challenge! This paper is participating in theEssentials for programmers”Creative activities.

preface

Db query timeout is often encountered during project operation. At this time, people often think of optimizing SQL, but today I want to say that the query timeout may not be a PROBLEM of SQL statement or orM framework

The environment

  • orm: mybatis – plus
  • db: mysql
  • The amount of data of 132 w

test

In this case, we use the SQL query select * from STu, which is a full table scan

The code is as follows:


LambdaQueryWrapper<Student> lambdaQuery = getLambdaQuery();
lambdaQuery.select(Student::getClassNo);

baseMapper.selectList(lambdaQuery);

Copy the code

Execute the print as follows

SQL: [SELECT class_no FROM student]

At this point I modify the code

LambdaQueryWrapper<Student> lambdaQuery = getLambdaQuery();
lambdaQuery.select(Student::getClassNo);

baseMapper.selectObjs(lambdaQuery);

Copy the code

Execute the print as follows

SQL: [SELECT class_no FROM student]

What was found? The SQL statements are the same but the queries differ by a full 5s

thinking

In this case, the difference is that the first query is straight out of the encapsulated object, and the second query is just obJ

We all know that one of the functions of ORM is to map the database fields to our class attributes and assemble the row data into objects. So the second method skips this part and directly puts it into a list. The first method is to call the assignment process by reflection

You can verify that

validation

Arthas trace functional area validation

First, after continuous breakpoint debugging, the key code is as follows

public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {
  PreparedStatement ps = (PreparedStatement) statement;
  / / db queries
  ps.execute();
  // Data assignment
  return resultSetHandler.handleResultSets(ps);
}
Copy the code

At that time with arthas trace command print trace respectively org. Apache. Ibatis. Executor. Statement. PreparedStatementHandler query

This is the selectList result

This is the selectObjs result

You can see the time difference in red

It turns out that the conjecture is right, which means that encapsulating the data takes the most time

conclusion

Although I use mybatis- Plus framework here, not on behalf of all ORM framework, but the principle is the same, as long as the query result is an object, it needs to encapsulate data, will go to reflection call assignment

So we need to be aware of this in our development, if you are just querying a field, there is no need to receive data with an object