Detailed explanation of MySQL limit usage and performance analysis of paging query statements

This article mainly introduces the performance analysis of limit usage and paging query statements in detail, and has certain reference Value, interested friends can refer to it.

limit usage

When we use query statements, we often need to return the first few or middle rows of data. What should we do at this time? Don't worry, mysql already provides us with such a function.

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 parameter must be an

integer

constant. If two parameters are given, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of returned record rows. The offset of the initial record row is 0 (instead of 1): For compatibility with PostgreSQL, MySQL also supports the syntax: LIMIT # OFFSET #. <pre class="brush:sql;">mysql&gt; SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15</pre>In order to retrieve all record rows from a certain offset to the end of the recordset, you can specify the second parameter as -1:

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

If only one parameter is given, it Indicates the maximum number of record rows returned:

mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行

In other words,

LIMIT n

is equivalent to LIMIT 0,n.

Performance analysis of Mysql's paging query statement

MySql paging sql statement, if compared with MSSQL's

TOP

syntax, then MySQL's LIMIT syntax is much more elegant. Using it for pagination is natural.

The most basic paging method:

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

In the case of small and medium data volumes, such SQL is sufficient. The only issue that needs attention is to ensure that it is used

Index

: For example, if the actual SQL is similar to the following statement, then it is better to establish a composite index on the category_id and id columns: The code is as follows:

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10

Paging method of subquery:

As the amount of data increases, the number of pages will increase. Viewing the SQL of the next few pages may be similar:

The code is as follows:

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10

In a nutshell, the farther back the page is, the greater the offset of the

LIMIT statement will be and the speed will be significantly slower

.
At this time, we can improve the paging efficiency through subqueries, roughly as follows:

SELECT * FROM articles WHERE id >= 
(SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10

JOIN paging method

SELECT * FROM `content` AS t1 
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2 
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

After my test, join The efficiency of paging and subquery paging are basically at the same level, and the time consumed is basically the same. explain SQL statement:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

Why is this happening? Because the subquery is completed on the index, and the ordinary query is completed on the data file, generally speaking, the index file is much smaller than the data file, so the operation will be more efficient.

In fact, you can use a method similar to

Strategy mode

to handle paging. For example, if it is judged to be within one hundred pages, use the most basic paging method. If it is greater than one hundred pages, then Use the paging method of subquery .

The above is the detailed content of Detailed explanation of MySQL limit usage and performance analysis of paging query statements. For more information, please follow other related articles on the PHP Chinese website!