To see the efficiency of SQL execution, it is not difficult to think of using Explain to analyze slow queries, but only if you understand the business context. Otherwise, it’s hard to pinpoint.

The system is gradually evolving, a system in operation must be according to the scene gradually improve the optimization performance. High concurrency is the test of resource saving, which is not only to replace excellent and advanced technology and optimize architecture, but also to save resources as much as possible from a small point.

And in the data access of a system, the bottleneck of the system is often from the database, so we should reduce the access to the database as far as possible!

The background,

Did the leader suddenly arrange something: the pressure measurement index of these interfaces is too low and needs to be optimized accordingly?

Ideally, you are familiar with the business scenario and can map the problem to analyze the business and accurately assess which SQL is experiencing performance bottlenecks.

Then start Baidu: how to improve SQL execution efficiency?

Analyze slow queries with diagnostic tools like Explain, Show Profile, and trace.

But in most cases the lines of business are too long to be completed by one person. Involves various policy modes and listening actions. If you want to directly locate the point, you still need to output the SQL and execution efficiency triggered after the request is initiated. Here efficiency simply refers to the time of SQL execution.

Once you have a clear goal, start working on it.

Add JDBC tracing

Continuing the topic of the previous article: How to leverage log link tracing for performance analysis?

2008 SQL Execute time formula

To deal with this kind of problem first analysis, SQL execution time calculation how to divide? The following figure shows the SQL statement execution process.

If you want to count the SQL execution time. So you can get a rough formula for the program

SQL execution time = time after data extraction - time when parsing beginsCopy the code

J. 2008 Adds adds JDBC tracking

Those of you who have read persistence frameworks such as Hibernate or MyBatis know that Statement comes under the java.sql base package

A Statement provides an object that executes a static SQL Statement and returns the result it produces. By default, only one ResultSet object can be opened for each Statement object.

Therefore, if reads from one ResultSet object are interleaved with reads from another, then each object must be generated by a different Statement object. If there is an open object, all execution methods in the Statement interface implicitly close the current ResultSet object for that Statement.

Continuing to look at the source code, you can see that the object PreparedStatement precompiles the SQL Statement after the Statement provides the method to execute. SQL statements are precompiled and stored in a PreparedStatement object. This statement can then be executed effectively multiple times using this object.

To test this idea, consider other custom database drivers.

The StatementWraper implementation Statement provides an object to execute a static SQL Statement and return the result it produces.

Recording Log Details

public class PreparedStatementWraper extends StatementWraper implements PreparedStatement { private PreparedStatement raw; private String sql; public PreparedStatementWraper(PreparedStatement raw, String sql) { super(raw); this.raw = raw; this.sql = sql; } // ========== record logs ========== @override public ResultSet executeQuery() throws SQLException {long startTime = System.currentTimeMillis(); try { return raw.executeQuery(); } finally { doLog("executeQuery", sql, startTime); }}... jCopy the code

Finally, log output Logs are collected using the Logback component

This kind of problem has been introduced before

  • How to implement log link tracing in microservices distributed architecture?

  • How to make good use of log link tracing for performance analysis?