Latency correlation for "MySQL" performance optimizations

Source: Internet
Author: User

Background
A business database load alarm Abnormal, CPU usr reached 30-40, high. Use the tool to view the SQL that the database is executing, most of the preceding:

    1. 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 >= ' 2014-05-29 ' ORDER by ID ASC LIMIT 149420, 20 ;

The amount of data in the table is approximately 36w, which is a very typical sort + paging query:ORDER by col limit N,offset M , when MySQL executes this type of SQL, it needs to scan to N rows before fetching M rows. For this sort of large data volume, it is very fast to take a few rows of data in front of you, but the more you rely on it, the worse the performance of SQL will be, because the larger the N, the more time it takes for MySQL to scan the unwanted data and throw it away.

Analysis
There are many ways to optimize for limit,
1 Front-end cache to reduce query operations falling to the library
2 Optimizing SQL
3 Bookmark the latest/large ID value of the last query, and trace the M-line record backwards.
4 Use Sphinx Search optimization.
For the second way we recommend using the " Deferred Association " method to optimize the sort operation, which is "deferred association": Returns the required primary key by using the Overwrite index query, and then obtains the required data based on the primary key associated with the original table.

Solution
Based on the idea of deferred correlation, modify the SQL as follows:
Before optimization

  1. [Email protected] 12:33:48>explain SELECT ID, cu_id, name, info, Biz_type, gmt_create, Gmt_modified,start_time, End_ti Me, Market_type, back_leaf_category,item_status,picuture_url from relation where Biz_type =\ ' 0\ ' and End_time >=\ ' 2014-05-29\ ' ORDER by ID ASC LIMIT 149420, 20;
  2. +----+-------------+-------------+-------+---------------+-------------+---------+------+--------+------------- ----------------+
  3. | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
  4. +----+-------------+-------------+-------+---------------+-------------+---------+------+--------+------------- ----------------+
  5. | 1 | Simple | Relation | Range | Ind_endtime | Ind_endtime | 9 | NULL | 349622 | Using where; Using Filesort |
  6. +----+-------------+-------------+-------+---------------+-------------+---------+------+--------+------------- ----------------+
  7. 1 row in Set (0.00 sec)

Its execution time:

After optimization:

    1. Select A.* from Relation A, (select ID from relation where biz_type = ' 0 ' and end_time >= ' 2014-05-29 ' ORDER by ID ASC LI MIT 149420, b where a.id=b.id
  1. [Email protected] 12:33:43>explain Select a.* from Relation A, (SELECT ID from relation where biz_type = ' 0 ' and End_tim E >= ' 2014-05-29 ' ORDER by ID ASC LIMIT 149420, b where a.id=b.id;
  2. +----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
  3. | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
  4. +----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
  5. | 1 | PRIMARY | <derived2> | All | NULL | NULL | NULL | NULL |       20 | |
  6. | 1 | PRIMARY | A | Eq_ref | PRIMARY | PRIMARY | 8 | b.ID |       1 | |
  7. | 2 | DERIVED | Relation | Index | Ind_endtime | PRIMARY | 8 | NULL |       733552 | |
  8. +----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
  9. 3 Rows in Set (0.36 sec)

Execution Time:



The optimized execution time is 1/3 of the original.

Latency correlation for "MySQL" performance optimizations

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.