MySQL latency Association performance optimization method and mysql latency performance optimization

MySQL latency Association performance optimization method and mysql latency performance optimization

Background]

An error occurred while alerting the load of a business database. The cpu usr reached 30-40, which remained high. Use tools to view the SQL statements being executed by the database. The most advanced ones are:
Copy codeThe Code is as follows:
SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified, start_time, end_time, market_type, back_leaf_category, item_status, picuture_url FROM relation where biz_type = '0' AND end_time> = '2017-05-29 'order by id asc LIMIT 2014, 20;

The table's data volume is about. This SQL statement is a typical sorting + paging query: order by col limit N, OFFSET M, mySQL needs to scan N rows before taking M rows when executing such SQL statements. For such sorting operations on large data volumes, It is very fast to retrieve the first few rows of data, but the closer the back, the worse the performance of SQL, because the larger the N, mySQL needs to scan unnecessary data and then discard it, which takes a lot of time.

[Analysis]

There are many methods for limit optimization,
1. Add cache at the front end to reduce the query operations that fall into the database
2. Optimize SQL statements
3. Use bookmarks to record the latest/large id values of the last query and trace the M-Row Records backwards.
4. Use Sphinx for search optimization.
For the second method, we recommend that you use the "delayed Association" method to optimize the sorting operation. What is "delayed Association": the primary key is returned by overwriting the index query, then, associate the original table with the primary key to obtain the required data.

[Solution]

Based on the concept of latency Association, modify the SQL statement as follows:

Before Optimization
Copy codeThe Code is as follows:
Root @ xxx 12:33:48> explain SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified, start_time, end_time, market_type, back_leaf_category, item_status, picuture_url FROM relation where biz_type = \ '0 \ 'AND end_time >=\ '2017-05-29 \' order by id asc LIMIT 2014, 20;
+ ---- + ------------- + ------- + --------------- + ------------- + --------- + ------ + -------- + --------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + ------------- + --------- + ------ + -------- + --------------------------- +
| 1 | SIMPLE | relation | range | ind_endtime | 9 | NULL | 349622 | Using where; Using filesort |
+ ---- + ------------- + ------- + --------------- + ------------- + --------- + ------ + -------- + --------------------------- +
1 row in set (0.00 sec)

Execution time:

After optimization:
Copy codeThe Code is as follows:
SELECT. * FROM relation a, (select id from relation where biz_type = '0' AND end_time> = '2017-05-29 'order by id asc LIMIT 2014, 20) B where. id = B. id

Copy codeThe Code is as follows:
Root @ xxx 12:33:43> explain SELECT. * FROM relation a, (select id from relation where biz_type = '0' AND end_time> = '2017-05-29 'order by id asc LIMIT 2014, 20) B where. id = B. id;
+ ---- + ------------- + -------- + --------------- + --------- + ------ + -------- + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + -------- + --------------- + --------- + ------ + -------- + ------- +
| 1 | PRIMARY | <derived2> | ALL | NULL | 20 |
| 1 | PRIMARY | a | eq_ref | PRIMARY | 8 | B. id | 1 |
| 2 | DERIVED | relation | index | ind_endtime | PRIMARY | 8 | NULL | 733552 |
+ ---- + ------------- + -------- + --------------- + --------- + ------ + -------- + ------- +
3 rows in set (0.36 sec)

Execution time:

The execution time after optimization is 1/3 of the original.