Querying and analyzing MySQL, SQL Server, and Oracle databases by PAGE (Operation Manual)

Querying and analyzing MySQL, SQL Server, and Oracle databases by PAGE (Operation Manual)

Querying and analyzing MySQL, SQL Server, and Oracle databases by PAGE (Operation Manual)

1. MySQL paging Query

Method 1:

Select * from table order by id limit m, n;

This statement means to query m + n records, remove the first m records, and return the last n records. Undoubtedly, this query can implement the paging function. However, if the value of m is larger, the query performance will be lower (the more pages that follow the query, the lower the query performance ), because MySQL also needs to scan m + n records.

Method 2:

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

This query returns n records each time, but does not need to scan m records like method 1. In the case of large data volumes, the performance can be significantly better than method 1, however, you must obtain the maximum id (or minimum id) of the last query (Previous Page) at each query ). The problem with this query is that sometimes we cannot get the maximum id (or minimum id) of the last query (Previous Page). For example, if the current query is on page 3rd, We need to query the data on page 5th, this query method is useless.

Method 3:

To avoid queries that cannot be implemented in method 2, we also need to use the limit m and n clauses. For performance, we need to minimize the value of m, for example, if the current page is 3rd, You Need To Query 10 pieces of data per page. The maximum id of the current page 5th is # max_id #:

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

In fact, this query method partially solves the problem of method 2. However, if you need to query 2nd or 100th pages on 1000 pages, the performance will still be poor.

Method 4:

Select * from table as a 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 method 1, and the m value may be very large. However, because the internal subquery only scans the field id rather than the entire table, the performance is better than the method 1 query, in addition, this query can solve problems that cannot be solved by methods 2 and 3.

Method 5:

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

This query method is the same as Method 4. It also uses the subquery to scan the field id. The result is the same as Method 4. As for the performance, the performance of Method 5 is slightly better than that of Method 4, because Method 5 does not need to be associated with tables, but is a simple comparison.

--------------------------------------------------------------------------------

2. SQL Server paging Query

Method 1:

Assume that the number of pages is 10 and the content on the 5th page is displayed. The query statement is as follows:
-- 10 indicates the page size.

Select top 10 *
From test
Where id not in
(
-- 40 is calculated as follows: 10 * (5-1)
Select top 40 id from test order by id
)
Order by id

Principle: Take out 5th pages of the database, that is, 40-50 records. First, extract the id value of the first 40 records in the database, and then extract the first 10 elements of the remaining parts.

Method 2:

Take the preceding result as an example.
-- The meaning of data is the same as that mentioned above

Select top 10 *
From test
Where id>
(
Select isnull (max (id), 0)
From
(
Select top 40 id from test order by id
)
)
Order by id

Principle: first query the first 40 records and obtain their most id value. If the id value is null, 0 is returned, query the records whose id value is greater than the maximum id value of the first 40 records. This query has a condition that the id must be of the int type.

Method 3:

Select top 10 *
From
(
Select row_number () over (order by id) as rownumber, * from test
)
Where rownumber> 40

Principle: first sort all the data in the table by a rowNumber, and then query the first 10 records whose rownuber is greater than 40.
This method is similar to a paging method in Oracle, but it only supports

Method 4:

Stored Procedure Query
Create a stored procedure

Alter procedure pageDemo
@ PageSize int,
@ Page int
AS
Declare @ temp int
Set @ temp = @ pageSize * (@ page-1)
Begin
Select top (select @ pageSize) * from test where id not in (select top (select @ temp) id from test) order by id
End
Execute the Stored Procedure
Exec 10, 5

--------------------------------------------------------------------------------

3. Oracle paging Query

Method 1:

Minute by ROWID

Select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from (select rowid rid, cid from
T_xiaoxi order by cid desc) where rownum <10000) where rn> 9980) order by cid desc;

Execution time: 0.03 seconds

Method 2:

By analysis function

Select * from (select t. *, row_number () over (order by cid desc) rk from t_xiaoxi t) where rk <10000 and rk> 9980;

Execution time: 1.01 seconds

Method 3:

Minute by ROWNUM

Select * from (select t. *, rownum rn from (select * from t_xiaoxi order by cid desc) t where rownum <10000) where

Rn> 9980; execution time: 0.1 seconds

Here, t_xiaoxi is the table name, cid is the table's key field, and the records 9981-9999 are sorted by CID in descending order. The t_xiaoxi table has more than 70000 records.
Personal perception 1 is the best, followed by 3, and 2 is the worst.

This article permanently updates the link address: