Before explaining limit, let’s talk about paging.

Paging has logical paging and physical paging, just as deletion has logical deletion and physical deletion. Logical deletion is to change the state of the database, physical deletion is to directly delete the records of the database, and logical deletion only changes the state of the database.

Why does logical paging take up a lot of memory space, for example, I have a table, the information of the table is:

————— – Table structure for vote_record_memory – ————————

DROP TABLE IF EXISTS `vote_record_memory`;

CREATE TABLE `vote_record_memory` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`user_id` varchar(20) NOT NULL,

`vote_id` int(11) NOT NULL,

`group_id` int(11) NOT NULL,

`create_time` datetime NOT NULL,

PRIMARY KEY (`id`), KEY `index_id` (`user_id`) USING HASH )

ENGINE=MEMORY AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8;

After inserting 3 million pieces of data into the table

What a huge amount of data and how terrible the memory it occupies, why do we choose a database again. This is also the size of the mysql storage space when we use the cloud server.

We generally do not recommend the use of logical paging, and the use of physical paging. When using physical paging, it is necessary to consider the usage of limit.

Explain limit

limit X, Y, skip the first X data, read Y data

X represents the offset of the first returned record row, Y represents the maximum number of returned record rows If X is 0, that is, limit 0, Y, which is equivalent to limit Y,

Through business analysis limit

I have a salary table, only the latest _first two records_ are displayed, and the employee name and salary commission remarks are inquired at the same time

The efficiency of limit

I have a requirement, which is to find out the data of 3600000 to 3.800000 from the vote_record_memory table. At this time, add an index to the id. The index type is Normal, and the index method is BTREE. Use two methods to query.

— method 1 SELECT * FROM vote_record_memory vrm LIMIT 3600000,20000;

– Method 2 SELECT * FROM vote_record_memory vrm WHERE vrm.id >= 3600000 LIMIT 20000

You will find that the execution efficiency of Method 2 is much higher than that of Method 1, which is almost one-ninth the time of Method 1.

Why is the efficiency of method 1 low and the efficiency of method 2 high?

Analysis One

Because in method 1, we use pure limit. As the line offset increases, the limit will decrease in efficiency when it reaches a certain level. And method 2 uses the index plus where and limit, the performance is basically stable, and it is not affected by the offset and the number of rows.

It can be seen that the execution efficiency of the limit statement may not be very high, because a full table scan will be performed, which is why the number of rows scanned by method 1 is 4 million rows. The number of scan lines in method 2 is 47945, which is why method 2 is highly efficient. We try to avoid full table scan queries, especially when the data is very large. This table has only 4 million pieces of data. There is such a big gap between method 1 and method. It is conceivable that there are tens of millions of pieces of data.

Use the index as much as possible if you can use the index, the type is at least the range level_, this is not what I said, it is required in 5.2.8 of the Alibaba Development Manual_

The result and return time of my query without indexing is similar to the time of method 1:

SELECT * FROM vote_record_memory vrm WHERE vrm.id >= 3600000 LIMIT

20000 Affected rows: 0 Time: 0.196s

This is why we try to use indexes. MySQL index methods generally include BTREE index and HASH index. The efficiency of hash index is higher than that of BTREE index, but we often use BTREE index instead of hash index. Because the most important point is: Hash index can only satisfy “=”, “IN” and “<=>” queries, and cannot use range queries.

If it is a range query, why do we use BTREE index. The BTREE index is a binary tree index. Those who have learned the data structure should be clear, so I won’t go into details here.

limit physical paging

We all know that limit generally has two parameters, X and Y, X means skip X data, read Y data, we will query the data here

If it is a SQL statement for paging, what we can see is:

— front page

SELECT * from vote_record_memory LIMIT 0,20;

— The second page

SELECT * from vote_record_memory LIMIT 20,20;

— page three

SELECT * from vote_record_memory LIMIT 40,20;

– Fourth page

SELECT * from vote_record_memory LIMIT 60,20;

– n pages

SELECT * from vote_record_memory LIMIT (n-1)*20,20;