mysql、mssqlおよびoracleページングクエリー方法の詳細

この例では、mysql、mssql、oracleのページングクエリー方法について説明します.皆さんの参考にしてください.具体的な分析は以下の通りである.
ページングクエリーはweb開発で最も一般的な技術で、最近資料を調べることで、少し自分の心得があります.
一、mysqlのページングクエリー
注意:

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

PageNumはクエリーするページ番号で、pageSizeはクエリーごとのデータ量です.
方法1:

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

この文の意味は、m+nレコードをクエリーし、前のmレコードを削除し、後のnレコードを返すことです.このクエリはページング機能を実現できるのは間違いありませんが、mの値が大きいほどクエリのパフォーマンスが低下します(後のページ数ほどクエリのパフォーマンスが低下します).MySQLもm+nレコードをスキャンする必要があります.
方法2:

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

このクエリは毎回n個のレコードを返すが、方式1のようにm個のレコードをスキャンする必要はなく、ビッグデータ量のページングの場合、性能は方式1より明らかに優れているが、このページングクエリは、クエリのたびに前回のクエリ(前のページ)の最大id(または最小id)を取得しなければならない.このクエリの問題は、前回のクエリ(前のページ)の最大id(または最小id)を取得できない場合があります.たとえば、現在3ページ目で5ページ目のデータをクエリする必要がある場合、このクエリメソッドは役に立たないことです.
方法3:
方式2では実現できないクエリを回避するためにもlimit m,n句を用いる必要があり,性能のためにはmの値をできるだけ小さくする必要がある.例えば,現在3ページ目,5ページ目,1ページ10データ目,現在3ページ目の最大idはmax_id#:

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

このクエリ方式は、方式2の問題を部分的に解決したものですが、現在2ページ目にある場合は、100ページまたは1000ページ目をクエリする必要があり、パフォーマンスは依然として低下します.
方法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; 
 

このクエリは、同じ方法でmの値が大きい場合がありますが、内部のサブクエリは、テーブル全体ではなくフィールドidのみをスキャンするため、方式1クエリよりもパフォーマンスが優れ、方式2および方式3では解決できない問題を解決できます.
方法5:

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

このクエリ方式は同方式4であり、同様にサブクエリによってフィールドidをスキャンし、効果は同方式4である.性能については、方式5の性能は方式4よりやや優れている.方式5は表の関連付けを行う必要がなく、簡単な比較であるからだ.
二、Sql Serverページングクエリー
方法1:
SQL Server 2000/2005に適用

SELECT TOP     *
 FROM table1
 WHERE id NOT IN
      (
      SELECT TOP    *(  -1) id FROM table1 ORDER BY id
      )
 ORDER BY id

方法2:
SQL Server 2000/2005に適用
--順番の書き方:

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

--降順の書き方:

 SELECT TOP     *
 FROM table1
 WHERE id <=
 (
 SELECT ISNULL(MIN(id),0) 
 FROM 
 (
 SELECT TOP    *(  -1)+1 id FROM table1 ORDER BY id Desc
 ) A
 )
 ORDER BY id Desc

方法3:
SQL Server 2005に適用

 SELECT TOP     * 
 FROM 
     (
     SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
     ) A
 WHERE RowNumber >    *(  -1)

説明、ページサイズ:各ページの行数;ページ数:何ページ目.使用する場合は、「ページサイズ」と「ページサイズ*(ページ数-1)」を数値に置き換えてください.
その他のシナリオ:プライマリ・キーがない場合は、テンポラリ・テーブル、シナリオ3で作成できますが、効率が低下します.最適化を推奨する場合は、プライマリ・キーとインデックスを追加すると、クエリーの効率が向上します.
SQLクエリー・アナライザを使用して、比較を表示します:私の結論は:
ページングスキーム2:(IDがどれだけ大きいかとSELECT TOPでページングする)効率が最も高く、SQL文のページングスキーム1:(Not InとSELECT TOPでページングする)効率に次いで、SQL文のページングスキーム3:(SQLのカーソルストレージプロセスでページングする)効率が最も悪いが、最も汎用的である
三、oracleページングクエリー
方法1:

SELECT * FROM 
( SELECT A.*, ROWNUM RN FROM 
  (SELECT * FROM tab) A 
   WHERE ROWNUM <= 40 ) 
     WHERE RN >= 21;

このページングは、次の実行時間よりも少なく、効率的です.データ量が大きい場合oracleは自動的に最適化されます!
方法2:

select * from 
(select c.*,rownum rn from tab c) where rn between 21 and 40

この2つの書き方を比較すると、ほとんどの場合、最初のクエリの効率は2番目よりずっと高いです.
これは、CBO最適化モードでは、Oracleが外部のクエリー条件を内部階層クエリーにプッシュして、内部階層クエリーの実行効率を向上させることができるためです.
1番目のクエリ文の場合、2番目の階層のクエリ条件WHERE ROWNUM<=40は、Oracleによって内部階層クエリにプッシュされます.これにより、Oracleクエリの結果がROWNUM制約条件を超えると、クエリが終了して結果が返されます.
一方、2番目のクエリ文は、クエリ条件BETWEEN 21 AND 40がクエリの3番目のレベルに存在するため、Oracleでは3番目のレベルのクエリ条件を最上位レベルにプッシュできません.
(RNが何を表しているのか分からないので、最内層にプッシュしても意味がありません).したがって、2番目のクエリ文では、Oracleの最上位レベルが中間レベルに返されるのは、条件を満たすすべてのデータであり、中間レベルが最上位レベルに返されるのもすべてのデータです.データのフィルタリングは最外層で完了し、この効率は最初のクエリよりもはるかに低いことが明らかになりました.
上で分析したクエリーは、単一テーブルの単純なクエリーだけでなく、最内層クエリーが複雑なマルチテーブル連合クエリーまたは最内層クエリーにソートが含まれている場合と同様に有効です.
本明細書で説明したデータベース・プログラムの設計に役立つことを願っています.