The introduction

As a back-end development programmer, Mysql database must be more familiar to everyone, everyone is writing different SQL statements to add, delete, change and check the database every day, so how to beautiful SQL statement? This requires us to know why and why when writing SQL. At the same time, Mysql is basically a knowledge point that must be asked in an interview. Therefore, this series will analyze the principle of Mysql, still a real interview experience to open the door of Mysql.

The season of gold and silver is upon us again, and Kenney continues his audition for a job in a department of a book under the giant of the universe.

Interviewer: I see you did a page search on your resume. Can you tell me how you did it? Bitches: Limit is mainly used for paging. Interviewer: Can you tell me how limit works? The small base: emm…

In our daily development, we often need to return the first few rows of the table or some middle rows of the table, so we can use the limit to implement these requirements. So how does limit work?

Limit usage method

There are two common ways to use limit:

1. SELECT * FROM TABLE LIMIT XXX; 

2. SELECT * FROM TABLE LIMIT XXX, XXX;
Copy the code

As shown above, limit can accept one parameter, or two parameters. What is the difference between them?

The syntax for using limit is as follows:

SELECT … FROM … LIMIT OFFSET N; The first parameter, OFFSET, represents the OFFSET from which the recordset is returned. Note that the initial OFFSET is 0 instead of 1. The second argument N: indicates the maximum number of rows to be returned, when N = -1 indicates that the last row of the last recordset is returned

For example:

// returns row 3-5 of the recordset.SELECT * FROM TABLE LIMIT 2.3; // returns rows 1-3 of the recordset.SELECT * FROM TABLE LIMIT 3; // This is equivalent to the followingSELECT * FROM TABLE LIMIT 0.3; Return rows 1-3 of the recordsetCopy the code

The principle of limit

So how does limit work? Mysql 5.7 storage engine innoDB mysql 5.7 storage engine InnoDB mysql 5.7 storage engine InnoDB

Test_limit = test_limit = test_limit = test_limit

CREATE TABLE `test_limit` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `a` INT NULL.`b` INT NULL,
  PRIMARY KEY (`id`)) ENGINE=InnoDB ;
Copy the code

The table structure is simple, with one increment primary key and two integer fields. The diagram below:

Let’s insert 100,000 pieces of data into it:

delimiter ;;
create procedure gen_data()
    begin
    declare i int;
    set i=1;
    while(i<=100000)do
        insert into test_limit values(i, i, i);
        set i=i+1;
    end while;
end;;
delimiter ;
call gen_data();
Copy the code

The results are shown below:

Then we analyze the principle of limit, take OFFSET value 10,100,1000 respectively, the maximum return data N is 5, and use explain to check the execution plan of SQL, as shown in the following figure:

1. explain select * from test_limit order by id limit 10.5;

2. explain select * from test_limit order by id limit 100.5;

3. explain select * from test_limit order by id limit 1000.5;
Copy the code

When N=100, 105 rows are scanned and 101-105 rows are returned. When N=1000, 1005 rows are scanned and 1001-1005 rows are returned.

The limit statement scans the OFFSET+N rows, then discards the first OFFSET row and returns the last N rows.

Possible performance problems when limit is used

From the above analysis, when the offset is large, the number of scanned rows will also increase, so the performance consumption is also very large, which is common in paging.

In this regard, we have carried out a simple experiment, and the experimental results are as follows:

It can be seen from the figure that the larger OFFSET is, the longer the query takes.

Through sub-query optimization, the results are as follows:

It can be seen from the results that the query time is reduced compared with that without sub-query, and the effect is not so obvious because of the amount of data. Then what is the reason for the decrease? Similarly, a query of the SQL execution plan through Explain results in the following:

As can be seen from the execution plan, before sub-query optimization, SQL execution plan type is ALL, that is, full table scan; After using subquery optimization, you can see that the execution plan types of SQL are range and index, that is, indexes are used to improve the performance of SQL queries.

So what’s an index? Why do indexes improve query performance? Next, we will start the Mysql series to fully analyze the important points of Mysql.