Summary of 5 mysql optimization limit query statements

This article mainly introduces five methods for optimizing mysql limit query statements, which are subquery optimization method, inverted table optimization method, reverse search optimization method, limit limit optimization method and Only check the index method. Friends who need it can refer to

mysql's pagination is relatively simple. You only need limit offset, length to get the data, but when offset When the length is relatively large, the performance of mysql obviously decreases

1. Subquery optimization method

First find the first piece of data, and then find the data that is greater than or equal to this piece of data The id is the data to be obtained
Disadvantages: The data must be continuous. It can be said that there cannot be a where condition. The where condition will filter the data and cause the data to lose continuity. For specific methods, please see the query example below:

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from Member;
+----------+
| count(*) |
+----------+
|   169566 | 
+----------+
1 row in set (0.00 sec)
mysql> pager grep !~-
PAGER set to 'grep !~-'
mysql> select * from Member limit 10, 100;
100 rows in set (0.00 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100;
100 rows in set (0.00 sec)
mysql> select * from Member limit 1000, 100;
100 rows in set (0.01 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100;
100 rows in set (0.00 sec)
mysql> select * from Member limit 100000, 100;
100 rows in set (0.10 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100;
100 rows in set (0.02 sec)
mysql> nopager
PAGER set to stdout

mysql> show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00003300
   Query: select count(*) from Member
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.00167000
   Query: select * from Member limit 10, 100
*************************** 3. row ***************************
Query_ID: 3
Duration: 0.00112400
   Query: select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100
*************************** 4. row ***************************
Query_ID: 4
Duration: 0.00263200
   Query: select * from Member limit 1000, 100
*************************** 5. row ***************************
Query_ID: 5
Duration: 0.00134000
   Query: select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100
*************************** 6. row ***************************
Query_ID: 6
Duration: 0.09956700
   Query: select * from Member limit 100000, 100
*************************** 7. row ***************************
Query_ID: 7
Duration: 0.02447700
   Query: select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100

It can be known from the results that when the offset is more than 1000 using the subquery method can effectively improve performance.

2. Inverted table optimization method

The inverted table method is similar to creating an index, using a table to maintain the number of pages, and then through efficient connections Obtaining data

Disadvantages: It is only suitable for the case where the number of data is fixed, the data cannot be deleted, and it is difficult to maintain the page table

Introduction to the inverted table: (And the inverted index is specifically It is the algorithm cornerstone of the search engine)

The inverted table refers to an inverted index stored in memory that can append inverted records. An inverted list is a mini inverted index.

Temporary inverted file refers to an inverted index that is stored on the disk and stored in the form of a file and cannot append inverted records. The temporary inverted file is a medium-sized inverted index.

The final inverted file refers to the inverted index obtained by merging the temporary inverted files stored in the form of files on the disk. The final inverted file is a larger inverted index.

Inverted index is an abstract concept, and inverted list, temporary inverted file, and final inverted file are three different manifestations of inverted index.

3. Reverse search optimization method

When the offset exceeds half of the number of records, sorting is used first, so that the offset is reversed

Disadvantages: order by optimization is troublesome, you need to add indexes, which affect the efficiency of data modification, and you need to know the total number of records, and the offset is greater than half of the data

limit offset algorithm:
Forward Search: (Current page - 1) * Page length
Reverse search: Total records - Current page * Page length

Do an experiment to see how the performance is

Total number of records: 1,628,775
Number of records per page: 40
Total number of pages: 1,628,775 / 40 = 40720
Number of intermediate pages: 40720 / 2 = 20360

Page 21000
Forward search SQL :

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40

Time: 1.8696 seconds

Reverse lookup sql:

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40

Time: 1.8336 seconds

Page 30000
Forward search SQL:

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40

Time: 2.6493 seconds

Reverse search SQL:

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40

Time: 1.0035 seconds

Note that the result of the reverse search is in descending order desc, and InputDate is the insertion time of the record. You can also use a primary key joint index, but it is inconvenient.

4.limit optimization method

Limit the limit offset below a certain number. . Exceeding this number means there is no data. I remember Alibaba’s DBA said that they did this

5. Only look up the index method

The working principle of MySQL limit is to first Read n records, then discard the first n records, and read m desired records, so the larger n is, the worse the performance will be.
SQL before optimization:

SELECT * FROM member ORDER BY last_active LIMIT 50,5

SQL after optimization:

SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)

The difference is that the SQL before optimization requires more I/O waste because the index is read first , read the data again, and discard unnecessary rows. The optimized SQL (subquery) read-only index (Cover index) is enough, and then the required columns are read through member_id.

The above is the detailed content of Summary of 5 mysql optimization limit query statements. For more information, please follow other related articles on the PHP Chinese website!