"Tamping MySQL Basics" Once the optimization process of MySQL statement

　　1. "Cause of the incident"

Today in the project, found to provide the client interface time is very slow, reached more than 2 seconds, I first time, grabbed the interface, looked at the operation of SQL, found to be 2 SQL slow, accounted for more than 1 seconds.

A SQL is a paging SQL that links 5 tables using 2 order BY and a limit.

One SQL is the Count (*) of the previous SQL, that is, 5 tables are linked, and of course there is no limit (take the total).

　　2. "Start Optimization"

1) "Optimization ideas"

The first one is to do a client call to the service layer's data cache

The second one is to optimize the SQL itself.

The emphasis here is on optimizing SQL itself

2) "Use Expain"

Use the Explain statement to view the statement,

There's nothing wrong with that. Using the index, the number of rows scanned, a 85-line, a 338-line, and the others are 1 rows.　　　　　　　　3) "Use sub-query optimization" Using sub-query optimization, the effect is similar, can only think of another way 4) "Get rid of order by" and colleagues in the discussion, think the original 5 tables that add the index is added, why slow it, I said the inside also did a sort of processing. The speaker is careless and the listener is interested.　　　　　　　　He said you get rid of the sorting test, sure enough, after the removal of the sort, the time dropped to 0.002 seconds, a lot faster. 　　　　But why is the order by sort slow, because that field of order by is also indexed. 5) "Create Federated Index" later queried the following article (analysis of two ways to improve query efficiency of ORDER BY statement in MySQLThe order by field must be established with the field in the preceding where statement if the query comes in a large amount of data. Federated Index, a colleague establishes an index order that is first the field in the where statement, and finally the one in order by. 6) The "final plan" makes sense, but in view of the trouble with the DBA, creating an index to build a specific index for a particular project is not a good deal. This part of the data changes infrequently, can be made into the form of a cache, but the idea of analyzing the problem and the process of optimizing the SQL order is still a harvest3. "References" 　　

1)Two ways to improve query efficiency of ORDER BY statement in MySQL

2) Two ways to improve query efficiency of ORDER BY statement in MySQL "tamping MySQL Foundation"

"Tamping MySQL Basics" Once the optimization process of MySQL statement