1. limit usage of Mysql
When we use query statements, we often need to return the first few or some middle rows of data. What should we do at this time? never mind, mysql We have 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 the specified number of records. LIMIT accepts one or two numeric parameters. Argument must be an integer constant. Given two parameters, the first specifies the offset of the first return record row and the second specifies the maximum number of return record rows. The offset of the initial record line is 0 (not 1): in order to be compatible with PostgreSQL, MySQL also supports the syntax: LIMIT offset.
mysql> SELECT * FROM table LIMIT 5,10; // Retrieving record lines 6-15 //In order to retrieve all record lines from an offset to the end of the Recordset, you can specify the second parameter as - 1: mysql> SELECT * FROM table LIMIT 95,-1; // Retrieve record line 96 last //If only one parameter is given, it indicates that the maximum number of record rows is returned: mysql> SELECT * FROM table LIMIT 5; //Retrieve the first 5 record lines //In other words, LIMIT n is equivalent to LIMIT 0,n.
2. Performance analysis of Mysql paging query statement
Compared with the TOP syntax of MSSQL, MySQL's LIMIT syntax is much more elegant. It's natural to use it for paging.
2.1 basic paging mode:
SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...
In the case of small and medium amount of data, such SQL is enough. The only problem to be noted is to ensure that the index is used:
For example, if the actual SQL is similar to the following statement, then in category_ It is better to establish a composite index on the ID and ID columns:
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10
2.2 paging method of sub query:
As the amount of data increases, the number of pages will increase. The SQL of the next few pages may be similar:
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10
In a word, the more pages are paged back, the more offset the LIMIT statement will have, and the speed will obviously slow down.
At this time, we can improve paging efficiency by sub query, which is roughly as follows:
SELECT * FROM articles WHERE id >= (SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10
2.3 JOIN pagination 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, the efficiency of join paging and sub query paging is 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 that? Because the sub query is completed on the index, and the common 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, we can use similar policy mode to process paging. For example, if it is less than 100 pages, the most basic paging mode will be used. If it is more than 100 pages, the subquery paging mode will be used.
[quoting the original, Space of energy1010: MySql paging sql statement]
3. Oracle paging query statement
Retrieve N records from the M record in the database table
SELECT * FROM (SELECT ROWNUM r,t1.* From Table name t1 where rownum < M + N) t2 where t2.r >= M
For example, from table Sys_option (the primary key is sys_id) to retrieve 20 records from the 10th record, as follows:
SELECT * FROM (SELECT ROWNUM R,t1.* From Sys_option where rownum < 30 ) t2 Where t2.R >= 10
3. MSSQLSERVER paging query statement
SQL Server mainly uses SELECT TOP statement for paging. For specific scheme, please refer to
Paging scheme I: (paging with Not In and SELECT TOP)
SELECT TOP 10 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 20 id FROM TestTable ORDER BY id)) ORDER BY ID
SELECT TOP Page size * FROM TestTable WHERE (ID NOT IN (SELECT TOP Page size*the number of pages id FROM surface ORDER BY id)) ORDER BY ID SELECT TOP Page size *
SELECT TOP Page size * FROM TestTable WHERE (ID > (SELECT MAX(id) FROM (SELECT TOP Page size*the number of pages id FROM surface ORDER BY id) AS T)) ORDER BY ID
Paging scheme 2: (paging with ID greater than or SELECT TOP)
SELECT TOP 10 * FROM TestTable WHERE (ID > (SELECT MAX(id) FROM (SELECT TOP 20 id FROM TestTable ORDER BY id) AS T)) ORDER BY ID
Paging scheme 3: (paging with cursor stored procedure of SQL)
create procedure XiaoZhengGe @sqlstr nvarchar(4000), --Query string @currentpage int, --The first N page @pagesize int --Lines per page as set nocount on declare @P1 int, --P1 It's cursorial id @rowcount int exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output select ceiling(1.0*@rowcount/@pagesize) as PageCount --,@rowcount as Total number of lines,@currentpage as Current page set @currentpage=(@currentpage-1)*@pagesize+1 exec sp_cursorfetch @P1,16,@currentpage,@pagesize exec sp_cursorclose @P1 set nocount off
Other schemes: if there is no primary key, you can use temporary tables or scheme 3, but the efficiency will be low.
When it is recommended to optimize, the query efficiency will be improved by adding the primary key and index.
Through SQL query analyzer, display comparison: my conclusion is:
Paging scheme 2: (using ID greater than how many and SELECT TOP paging) is the most efficient, and SQL statements need to be spliced
Paging scheme 1: (using Not In and SELECT TOP paging) is the second most efficient, and SQL statements need to be spliced
Paging scheme 3: (paging with SQL cursor stored procedures) is the most inefficient, but the most common
In the actual situation, it is necessary to make a concrete analysis.
[reference: Paging query by SQL statement in SQL Server]