MySQL latency Association performance optimization method and mysql latency performance optimization

Source: Internet
Author: User

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 the Code 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 the Code 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 the Code 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 the Code 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.