MySQL Query performance optimization A

The company has a set of Web systems, the user feedback system some of the page access speed is slow, poor experience, and occasionally an HTTP 502 error.

This is a typical server-side IO blocking issue, through the access page of the program logic to trace, found that the problem should be on a SQL query.

In a step that the page program runs, there is a SQL

Select distinct (server)    from  where='GD0001'

The data in the User_record table is about 20 million, and the field type has a GD0001 record of about 5 million, and this SQL executes about 30 of the results. There is an index on the Type field, but the SQL statement takes more than one minute to execute.

The value of the server field after being de-weighed is the root cause of slow page access.

Depending on the requirements of the program, the value of the server field needs to be evaluated in real time, so the SQL is used to get the results when the program is designed. When the amount of data is low, there is no problem, however, data growth is faster than originally expected, resulting in performance problems.

To solve this problem is not difficult, because the scope of the server field value is relatively stable, you can find a way to extract the value out of a redundant table, and through a mechanism to let the value of the new table and the original table in the server field values are kept in sync, check the new table, So the problem of slow access will be solved.

Obviously, it takes a small amount of work to solve the problem using this solution. To minimize the cost of solving this problem, the best way is to optimize the query, if the original query run time is a minute, then the time to run this query down to a second, the problem is solved.

This goal seems to be difficult to achieve, in fact, it can be done.

Select distinct (server)   from where = ' GD0001 '

Because the filter criteria for this SQL statement type field is indexed, the logical query steps for the entire SQL statement are as follows

1. Filter out the identity of the primary key field that meets the required record through the type index

2. Locating source data recorded in a table by primary key identification

3. Get the value of the field to distinct to get the final result.

In the three steps above, the most consumed performance is the second step. Because the actual data of the index and the table is actually placed separately, probably looks like the following figure. The largest one in the figure is actually a data table, all the data in the table is on it, but it doesn't look like a "table".

The second step is to target the actual data by filtering out the primary key ID of the qualifying record by the index, as in the following diagram

Imagine that the SQL to optimize, and the type value of GD0001 records have 5 million, even if MySQL is not stupid to go to the 5 million times to get the results, but certainly not easy to complete. If you can optimize this step, the cost of the entire query will go down.

Select distinct  from where = ' GD0001 '

For this SQL, our goal is not to get the values of all the fields, just the value of the server field is sufficient.

If we put the value of the server field in the index of the Type field, we can get the result of the second step when we look at the index in the first step. The execution process such as

In a relational database, an index called an overlay index is designed to meet this optimization requirement.

The Overwrite index creation statement optimized for this SQL statement is as follows

Create Index Index_type_server    on User_record (type, server)

This index creation statement organizes the values of type and server two fields into an index, so when

Select distinct (server)    from User_record    where='GD0001'

All the query steps can be completed in the index, instead of going to the source data table to extract the data, that is, when the index is not established the second step of the query is eliminated, so the performance of the query has been greatly improved.

The query takes more than a minute before the overwrite index is established, and the time of the query drops to a level of hundreds of milliseconds after indexing. The problem that the Web page was slow to load and occasionally reported an HTTP 502 error has been resolved.

It is a science to get the query result quickly by using the index of SQL statement. The rational use of the index, can allow the optimization of program performance from the code level to the database level, so that the problem by the most suitable tools and means to solve, the use of the best, so not only to reduce the complexity of code, but also improve the efficiency of problem solving. This is a skill that a programmer has to have.

Extended reading: http://www.cnblogs.com/aspwebchh/p/6652855.html

Original link: https://www.chhblog.com/article_view?id=385

MySQL Query performance optimization A