Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "introduction of paging query methods of mysql and mssql as well as oracle". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

This paper describes the paging query methods of mysql, mssql and oracle. Share it with you for your reference. The specific analysis is as follows:

1. Paging query in mysql

Note:

M = (pageNum-1) * pageSize;n= pageSize

PageNum is the page number to be queried, and pageSize is the amount of data per query.

Method 1:

Select * from table order by id limit m, n

The meaning of this statement is to query m records, remove the first m records, and return the last n records. There is no doubt that this query can be paged, but the higher the value of m, the lower the performance of the query (the later the number of pages, the lower the performance of the query), because MySQL also needs to scan m records.

Method 2:

Select * from table where id > # max_id# order by id limit n

The query returns n records each time, but does not need to scan m records as in mode 1. In the case of paging with a large amount of data, the performance can be significantly better than that of mode 1, but the paging query must get a maximum id (or minimum id) of the previous query (previous page) for each query. The problem with this query is that sometimes we can't get the maximum id (or minimum id) of the previous query (previous page). For example, if you are currently on page 3 and need to query the data on page 5, this query method cannot help.

Method 3:

In order to avoid queries that cannot be implemented in mode 2, we also need to use limit m, n clauses. For performance, we need to try our best to reduce the value of m. For example, currently on page 3, we need to query page 5 with 10 pieces of data per page. The current maximum id of page 3 is # max_id#:.

Select * from table where id > # max_id# order by id limit 20Jol 10

In fact, this query method partially solves the problem of mode 2, but if you are currently on page 2 and need to query page 100 or page 1000, the performance will still be poor.

Method 4:

The copy code is as follows:

Select * from table as an inner join (select id from table order by id limit m, n) as b on a.id = b.id order by a.id

This query is the same as mode one, the value of m may be very large, but because the internal subquery scans only the field id, not the whole table, the performance is better than the mode one query, and the query can solve the problems that can not be solved by mode two and mode three.

Method 5:

The copy code is as follows:

Select * from table where id > (select id from table order by id limit m, 1) limit n

The query mode is the same as the fourth mode, and the field id is also scanned through the subquery, and the effect is the same as the fourth way. As for performance, mode 5 will perform slightly better than mode 4, because mode 5 does not require an association in the progress table, but a simple comparison.

II. Sql Server paging query

Method 1:

Suitable for SQL Server 2000 Universe 2005

SELECT TOP page size * FROM table1 WHERE id NOT IN (SELECT TOP page size * (number of pages-1) id FROM table1 ORDER BY id) ORDER BY id

Method 2:

Suitable for SQL Server 2000 Universe 2005

-- written in sequence:

SELECT TOP page size * FROM table1 WHERE id > = (SELECT ISNULL (MAX (id), 0) FROM (SELECT TOP page size * (number of pages-1) + 1 id FROM table1 ORDER BY id) A) ORDER BY id

-- written in descending order

SELECT TOP Page size * FROM table1 WHERE id Page size * (number of pages-1)

Description, page size: the number of rows per page; number of pages: which page. When using, replace "Page size" and "Page size * (number of pages-1)" with numbers.

Other options: if there is no primary key, you can use a temporary table, or you can use option 3, but it will be inefficient.

It is recommended that when optimizing, add the primary key and index, the query efficiency will be improved.

Through the SQL query Analyzer, display the comparison: my conclusion is:

Paging scheme 2: (using ID greater than how much and SELECT TOP paging) is the most efficient and needs to concatenate SQL statements

Paging scheme 1: (using Not In and SELECT TOP paging) the efficiency is the second, and the SQL statements need to be spliced.

Paging scheme 3: (paging using SQL's cursor stored procedures) is the least efficient, but the most general

3. Oracle paging query

Method 1:

SELECT * FROM (SELECT A.S., ROWNUM RN FROM (SELECT * FROM tab) A WHERE ROWNUM = 21

This page takes less time and is more efficient than the one below. Oracle will be automatically optimized when the amount of data is large.

Method 2:

Select * from (select c. Mag Rownum rn from tab c) where rn between 21 and 40

Comparing the two writing methods, in most cases, the first query is much more efficient than the second.

This is because in CBO optimization mode, Oracle can push the outer query conditions to the inner query to improve the execution efficiency of the inner query.

For the first query statement, the query condition WHERE ROWNUM of the second layer

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.