Analysis of low performance caused by large offset of LIMIT in MySQL query
- 2021-11-13 02:58:51
As we all know, mysql query uses select command, and limit and offset parameters can read records in a specified range, but offset is too large to affect the query performance and its optimization method
Paging is inevitable in our business system. When you think of paging, you will definitely think of using LIMIT in SQL to achieve it. However, if the incorrect use of LIMIT will lead to performance problems (SQL executes very slowly and may bring down the server), it will also be approved by the leader; So, let's look at how to use LIMIT correctly.
The following words are not much to say, let's take a look at the detailed introduction
LIMIT OFFSET, ROW_COUNT Paging
Ways of Performance Problems
SELECT * FROM myTable ORDER BY `id` LIMIT 1000000, 30
The person who wrote this SQL statement must have thought this: The MySQL database will directly locate the 10000000 bits that meet the requirements, and then take 30 pieces of data.
However, in fact, MySQL does not work like this.
LIMIT 1000000, 30 means: Scan 1000030 rows that meet the criteria, throw away the previous 1000000 rows, and return to the last 30 rows.
A better way
SELECT t.* FROM ( SELECT id FROM myTable ORDER BY id LIMIT 1000000, 30 ) q JOIN myTable t ON t.id = q.id
The general principle is:Subquery only used index column, did not take the actual data, so does not involve the disk IO, so even a relatively large offset, the query speed will not be too bad.
For those who are interested in specific principle analysis, please see this article: MySQL ORDER BY/LIMIT performance: late row lookups
To be continued.
ReferencesWhy does MYSQL higher LIMIT offset slow the query down? MySQL ORDER BY / LIMIT performance: late row lookups