[MySQL] delay correlation for performance optimization
[background]
A business database load alarm abnormal, cpu usr reached 30-40, high. Use the tool to view the sql being executed in the database, and most of the top ones are:
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 data volume of the table is about 36w, and the sql is a very typical sort + paging query: order by col limit NLFSET M, MySQL needs to scan N rows before fetching M rows when performing this kind of sql. For this kind of sorting operation with a large amount of data, taking the first few rows of data will be very fast, but the lower the back, the worse the performance of sql, because the larger the N, the MySQL needs to scan the unwanted data and then throw it away, which takes a lot of time.
[analysis]
There are many ways to optimize for limit
1 the front end is cached to reduce the query operations that fall into the library
2 optimize SQL
(3) use bookmarks to record the latest / large id value of the last query, and trace back the M-line record.
4 use Sphinx search optimization.
For the second way, we recommend to use the method of "deferred association" to optimize the sort operation, what is meant by "deferred association": by using the overlay index query to return the required primary key, and then associate the original table with the primary key to get the desired data.
[solution]
According to the idea of delay correlation, modify the SQL as follows:
Before optimization
Click (here) to collapse or open
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 > =\' 2014-05-29\ '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 | Using where; Using filesort |
+-+-
1 row in set (0.00 sec) its execution time:
After optimization:
Click (here) to collapse or open
SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time > = '2014-05-29' ORDER BY id asc LIMIT 149420, 20) b where a.id=b.id
Root@xxx 12:33:43 > explain SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time > = '2014-05-29' ORDER BY id asc LIMIT 149420, 20) b where a.id=b.id
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | PRIMARY | | 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.36sec) execution time:
After optimization, the execution time is 1 prime 3.
If you think you will benefit from this article, you can sponsor a bottle of beverage in the south ^ _ ^
