Background
A business database load alarm Abnormal, CPU usr reached 30-40, high. Use the tools to view the SQL that the database is executing, most of which are:
Copy Code 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 >= ' 2014-05-29 ' ORDER by ID ASC LIMIT 149420, 20 ;
The amount of data in a table is approximately 36w, which is a very typical sort + paging query: Order by col limit N,offset m, MySQL executes such SQL when it needs to scan to N rows before fetching M rows. For such a large amount of data sorting operations, take a few lines before the first few rows of data will be very fast, but the higher the performance of SQL will be worse, because the greater the N, MySQL needs to scan the unwanted data and then throw away, which takes a lot of time.
Analysis
There are a number of ways to optimize for limit,
1 front-end cache, reduce the query operation down to the library
2 Optimizing SQL
3 Use bookmarks, record the last query latest/large ID value, back to the M line records.
4 Use Sphinx Search optimization.
For the second approach, we recommend using deferred correlation to optimize the sort operation, what is "deferred association": return the required primary key by using the Overwrite index query, and then obtain the required data based on the primary key associated with the original table.
Solution
According to the idea of delaying association, modify SQL as follows:
Before optimization
Copy Code 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, Marke T_type, Back_leaf_category,item_status,picuture_url from relation where Biz_type =\ ' 0\ ' and End_time ' >=\ ' ' ORDER BY ID ASC LIMIT 149420, 20;
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+------------- ----------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+------------- ----------------+
| 1 | Simple | Relation | Range | Ind_endtime | Ind_endtime | 9 | NULL | 349622 | The Using where; Using Filesort |
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+------------- ----------------+
1 row in Set (0.00 sec)
Its execution time:
After optimization:
Copy Code code as follows:
Select A.* from Relation A, (select IDs 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
Copy Code code as follows:
Root@xxx 12:33:43>explain Select a.* from Relation A, (select IDs from relation where Biz_type = ' 0 ' and End_time >= ' 2 014-05-29 ' ORDER by ID ASC LIMIT 149420 b where a.id=b.id;
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
| 1 | PRIMARY | <derived2> | All | NULL | NULL | NULL | NULL | 20 | |
| 1 | PRIMARY | A | Eq_ref | PRIMARY | PRIMARY | 8 | b.ID | 1 | |
| 2 | DERIVED | Relation | Index | Ind_endtime | PRIMARY | 8 | NULL | 733552 | |
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
3 Rows in Set (0.36 sec)
Execution Time:
After the optimization, the execution time is original 1/3.
