Latency correlation for "MySQL" performance optimizations

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

2. [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;
4. +----+-------------+-------------+-------+---------------+-------------+---------+------+--------+------------- ----------------+
6. | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
8. +----+-------------+-------------+-------+---------------+-------------+---------+------+--------+------------- ----------------+
10. | 1 | Simple | Relation | Range | Ind_endtime | Ind_endtime | 9 | NULL | 349622 | Using where; Using Filesort |
12. +----+-------------+-------------+-------+---------------+-------------+---------+------+--------+------------- ----------------+
14. 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

2. [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;
4. +----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
6. | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
8. +----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
10. | 1 | PRIMARY | <derived2> | All | NULL | NULL | NULL | NULL |       20 | |
12. | 1 | PRIMARY | A | Eq_ref | PRIMARY | PRIMARY | 8 | b.ID |       1 | |
14. | 2 | DERIVED | Relation | Index | Ind_endtime | PRIMARY | 8 | NULL |       733552 | |
16. +----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
18. 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