[Consolidate Mysql Basics] record the mysql statement optimization process and consolidate mysql

[Consolidate Mysql Basics] record the mysql statement optimization process and consolidate mysql

 

1. [event cause]

 

Today, when I was working on a project, I found that the interface provided to the client was slow for more than 2 seconds. I caught the interface and read the running SQL, two SQL statements are found to be slow, accounting for more than 1 second respectively.

One SQL statement is a paging SQL statement that links five tables and uses two order by statements and one limit statement at the same time.

An SQL statement is the count (*) of the previous SQL statement, that is, it links five tables. Of course, there is no limit (take the total number ).

 

2. [optimization]

 

1) [optimization ideas]

The first is to make the client call the data cache at the service layer.

The second step is to optimize the SQL itself.

Here we will focus on optimizing the SQL itself.

 

2) [Use expain]

Use the explain statement to view the statement,

There is nothing wrong with it. indexes are used, and there are too many rows scanned. One row is 85, and one row is more than 338 rows. The other rows are also 1 row. 3) [Use subquery optimization] Use subquery for optimization. The effect is similar. You can only find another method. 4) [remove order by sorting] when discussing with colleagues, I think the indexes that should be added to the five tables are all added. Why is the speed slow? I said the sorting process is also done. The speaker is unintentional and the listener is interested. He said that you should try removing the sorting. After removing the sorting, the time dropped to 0.002 seconds, which is much faster. But why is order by sorting very slow, because the order by field is also indexed. 5) [create a joint index] Later I searched the following article (two ideas for improving the query efficiency of the Order by statement in mysql). If the queried data volume is large, the order by field, which must be created with the field in the where statement. Joint IndexThe index order created by the colleague must be the field in the where statement and the Field in order. 6) The [Final Solution] understands the truth, but it is not cost-effective to create an index for a specific project for DBAs. This part of data does not change frequently and can be made into a cache format. However, the analysis of the problem and the optimization of the SQL order process are still rewarding. 3. [References]

1) Two ideas for improving the query efficiency of Order by statements in mysql

2) [consolidate the foundation of Mysql] Two ideas for improving the query efficiency of Order by statements in mysql