How to improve MySQL Limit query performance

In MySQL database operations, we always hope to avoid the database engine from performing a full table scan when performing some queries, because a full table scan takes a long time and most of the scans are meaningless to the client. In fact, we can use the Limit keyword to avoid full table scans and improve efficiency.

There is a table on MySQL 5.0.x with tens of millions of records, and now we need to read out hundreds of millions of records. Commonly used methods, cycle in sequence:

select * from mytable where index_col = xxx limit offset, limit;

Experience: If there is no blob/text field and the single-line record is relatively small, you can set the limit to a larger value to speed up the process.

Problem: The first tens of thousands of records are read very quickly, but the speed decreases linearly. At the same time, the MySQL server CPU is 99%, which is unacceptable.

Call explain select * from mytable where index_col = xxx limit offset , limit; Display type = ALL

In the MySQL optimization document, the explanation of "All" is written

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values ​​or column values ​​from earlier tables.

It seems that for all , MySQL uses a more clumsy method, so why not use range method instead? Because id is incremented, it is also easy to modify the sql.

select * from mytable where id > offset and id < offset + limit and index_col = xxx

explain shows type = range, and the result speed is very ideal, returning results dozens of times faster.

Limit syntax:

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

The LIMIT clause can be used to force a SELECT statement to return a specified number of records. LIMIT accepts one or two numeric arguments. The argument must be an integer constant.

If two arguments are given, the first argument specifies the offset of the first returned row, and the second argument specifies the maximum number of returned rows. The initial record row has an offset of 0 (not 1).

For compatibility with PostgreSQL, MySQL also supports the syntax: LIMIT # OFFSET #.

mysql> SELECT * FROM table LIMIT 5,10; // Retrieve rows 6-15
//To retrieve all the rows from a certain offset to the end of the record set, you can specify the second parameter as -1
mysql> SELECT * FROM table LIMIT 95,-1; // Retrieve rows 96-last
//If only one parameter is given, it indicates the maximum number of rows to return. In other words, LIMIT n is equivalent to LIMIT 0,n
mysql> SELECT * FROM table LIMIT 5; // Retrieve the first 5 rows

MySQL limit brings great convenience to paging, but when the amount of data is large, the performance of limit drops sharply. Taking the same 10 pieces of data, the following two sentences are not on the same order of magnitude.

select * from table limit 10000,10
select * from table limit 0,10

In this article, limit is not used directly. Instead, the offset id is first obtained and then the limit size is directly used to obtain the data. According to his data, it is obviously better than using limit directly.

Here I use data to test in two situations.

1. When the offset is relatively small:

select * from table limit 10,10 
// Run multiple times, keep the time between 0.0004-0.0005 Select * From table Where vid ＞=(Select vid From table Order By vid limit 10,1) limit 10 
// Run multiple times, the time is kept between 0.0005-0.0006, mainly 0.0006

Conclusion: When the offset is small, it is better to use limit directly. This is obviously the reason for the subquery.

2. When the offset is large:

select * from table limit 10000,10 
// Run multiple times, the time remains around 0.0187 Select * From table Where vid >=(Select vid From table Order By vid limit 10000,1) limit 10
//After multiple runs, the time remains at around 0.0061, which is only 1/3 of the previous one. It can be expected that the larger the offset, the better the latter.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links