ページ分割されたデータ-古くから知られているデータの代替ビュー

データベースからの情報のページ付けの問題は、データベース自体と同じくらい古いものであり、したがって、1,000回以上議論されてきました。 おそらく、この問題が何らかの方法で対処および解決されていない単一のクライアント/サーバーシステムはありません。 今日は、一般的な公開Webアプリケーションでページネーションを整理する際の、クライアントレイヤーとMS SQLバックエンド間のやり取りの少し標準的ではない方法についてお話します。



まず、典型的なビジネス要件の概要を説明し、そこから入力条件を導き出します。リスト（ユーザー、商品、トランザクション）からデータを選択します。 サンプリングは、1つ以上のフィルターといくつかのソート基準を使用して実行されます。 すべてのデータをロードする必要があるJavaScriptのインタラクティブグリッドなどの純粋なクライアントソリューションの問題はここでは考慮されませんが、サーバーでページネーションが行われるより穏やかなオプションに焦点を当てます。



これは伝統的にどのように行われますか？ アプリケーション層は、フィルター値、必要な最初のレコードのインデックス、およびページあたりの必要なレコード数をバックエンドに渡します。 応答として、データベースはサンプル全体にフィルターを適用し、フィルター処理されたサブセット全体を配置し、MからM + N-1のレコードを返します。 これまたはその開発者および/またはこのまたはそのバージョンのRDBMSがこれを行う方法は今では重要ではありません-重要なことは、使用されているメソッド（MS SQL 2000の一時テーブル、2005年のROW_NUMBER OVER（） 2008、または2011年のTOP / OFFSET）、フィルターされた順序付きセットから番号付きサブセットを発行する必要は、必然的にフィルターとソート後に中間結果全体のねじれを解くことを意味します。 このアンワインドがどこで実行されるかは重要ではありません-データスペース上またはインデックスフィールド上で（たとえば、完全なインデックスカバレッジまたはINCLUDED列を使用する場合）-この違いは原則を変更しません。



フィルタリング係数が低く、フィルターが労働集約型（フルテキスト検索など）である場合、特に選択の最後のページに近づくと、この方法の効率が非常に低くなることは明らかです。 また、フィルターがパラメトリックで（たとえば、ユーザーテーブルのユーザーのタイプに応じて）、特別に作成されたインデックスに従って動作する場合でも、最後のページのすべてのリクエストに対してサーバーの努力のほぼ100％を捨てることにより、実際にはRDBMS自体の鉄片に誠実な同情が生じます。紡績。



実際のクライアント/サーバーアプリケーションでは、状況を複雑にするいくつかの点がまだあります。



a）ページネーションを使用してページを正しくレンダリングするために（用語について謝罪します）、アプリケーションレイヤーはフィルターを通過するレコードの総数を知る必要があります-Nで除算し、（明らかな注意を払って）切り上げるために必要なページ数を取得しますナビゲーションメニュー。 これを実現するには、バックエンドは実際に各ページの選択中に検索クエリを2回実行する必要があります-切り捨てられたバージョンで初めて-単に選択... COUNT（1）... WHERE、部分選択と並べ替えなし、2番目-すでに完全に、目的のセットを選択してレコード。 または、フィルター処理された出力に初めてアクセスしたときにCOUNTを実行し、この値を覚えておいてください。



b）ユーザーがページをナビゲートしているときに、新しいレコードがデータベースに表示され（または既存のレコードが削除され）、フィルター条件に該当する場合、ナビゲーションは移動し、ユーザーは1つ以上のレコードをスキップまたは再表示できます。



c）一般的に言えば、データベースは、ACIDの原則に基づいて構築されたパブリックシステムで最小のスケーラブルリンクです。 したがって、一度信頼できるすべてのデータを同時に持つようにシステムを設計した場合、最初にデータベースリソースを節約し、簡単にスケーラブルなアプリケーションサーバーに配布できるすべてのものを保存することは直接意味があります。



典型的なページネーション操作アルゴリズムは、Cのようなコードでは次のようになります（DBプレフィックスを持つ関数はデータベースで実行されます）。

while (1) { create_filter(FormData, &F, &S); //   F     S //      FormData DB_get_count(F, &C); //   -      while (1) // ,  F  S  (  ) { DB_get_records(F, N, M, S); //  N ,   M   F   S if (SORT_OR_FILTER_CHANGED == do_navigation(FormData, C, &N, &M, &S)) //    { break; //      -   } } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

それでは、最初の表示とその後の各ナビゲーションでDB_get_recordsのレコードの再フィルタリングを取り除いて、改善を試みましょう。 これを行うには、フィルターに一致するレコード数の選択を照会する代わりに、正しい順序でソートされた主キーの配列全体を選択します。 ここでは、レコードの主キーがコンパクト（たとえば、intまたはbigint）であると想定されており、最小限のフィルタリングでサンプリングしても妥当な数のレコードが得られます。 そうでない場合は、最初のケースではサロゲートキーを使用できます（ほとんどの場合これが行われます）。2番目のケースでは、選択を妥当な量（たとえば100,000）に制限するか、部分的にキーを選択して緩和的な決定を行うことができます。



DB_get_count疑似コード関数との類推により、DB_get_keysと呼びます



2番目のステップは、指定されたフィルターに従ってデータ選択関数をNからN + M-1に書き換え、渡されたキー配列に対応するキーを持つNレコードを厳密に配列内の順序で選択する関数にソートします。 彼女をサインにしましょう

 DB_get_records_by_keys(*K, N)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

、ここでKは目的のポイントからのキーの配列のアドレスです（つまり、Mで、Nはこれらのキーから選択するレコードの数です。擬似アルゴリズムは次のようになります。

 while (1) { create_filter(FormData, &F, &S); //   F     S //      FormData DB_get_keys(F, S, K); //    K   F   S while (1) // ,  F  S  (  ) { DB_get_records_by_keys(&(K[M]), N); //  N ,   M if (SORT_OR_FILTER_CHANGED == do_navigation(FormData, C, &N, &M, &S)) //    { break; //      -   } } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ここで、古典的なものと比較した実行速度の定性的評価を試みましょう。 バックエンドから選択されたキーの配列の転送時間は、必要なデータを検索する時間と比較して無視できると仮定します（これは通常、少量のデータとデータベースサーバーとアプリケーションサーバー間の適切なネットワークインターフェイスを転送する場合です）。 操作の一般的なアルゴリズムは変更されないため、DB_get_countデータベース関数の違いを比較するだけで済みます。 DB_get_keysおよびDB_get_records ?? DB_get_records_by_keys。



ほとんどの場合、DB_get_countは、主にフィルターによって選択された行をカウントする（つまり、プライマリ行キーをカウントする）ために内部ソートを必要とせず、SQLエンジンからこれらのキーを発行する必要がないため、少し速く動作します。 比較のために、2つの実行計画：



DB_get_recordsとDB_get_records_by_keysの比較は、いずれにしても、明らかに最初の方法を支持しません。 主キーによるレコードの取得は、検索操作のほんの一部です。



結果として、新しい方法はゲインを提供し、フィルターをより面倒にし、フィルターまたはソート基準を変更する操作ごとのユーザーによるページめくりの平均数が増えると予測します。 また、このメソッドを使用して同じフィールドのソート基準を反対に変更する（統計的に非常に頻繁な操作）ことは、逆にキーでレコードを選択するDB_get_records_by_keysにナビゲートしてパラメーターを追加するときに、最初の要素へのポインターを反転するだけで、通常はデータベースにアクセスせずに実行できることに注意してください転送された注文。



データベースの概念全体を実装するという観点から見ると、残っていることが1つだけあります。結果として出力が配列内のキーの順序を保持するように、キー配列をパラメーターとして統計または手順に効率的に渡す方法は？



このタスクを2つに分割します。パラメーターとして配列をデータベースコードに転送し、実際には順序を維持します。 最初の問題に対するいくつかの解決策があります-XMLまたはCSV表現に基づく、またはテーブル変数の作成と入力。 入力配列を一連の文字列に変換するSQLコード自体は、動的SQLクエリ、プロシージャ、またはテーブル関数として実行できます。



最も柔軟なバージョンは、CTEを使用したテーブル関数（TF）としてのダイナミクス（いわゆる共通テーブル式）であり、MS SQL 2008が埋め込みデータを処理するための再帰クエリを作成できます。

CTEのこの機能と、TFを複合クエリのテーブルソースとして使用できる機能を使用します。



並べ替え順序を保持するタスクは、返されるTFテーブルの構造にIDフィールドを追加し、目的のシード値と増分値（外部で並べ替えられるように）を追加し、返されるテーブル（内部）にレコードを挿入する明示的な順序を示すことで解決されます。 その結果、機能は次のようになりました。

 CREATE FUNCTION [dbo].[TF_IDListToTableWithOrder] ( @ListString varchar(MAX), @Delim char(1) ) RETURNS @ID TABLE ( RowIdx INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED, --     ID INT --     ) AS BEGIN SET @ListString = REPLACE(@ListString, ' ', '') IF LTRIM(RTRIM(ISNULL(@ListString, ''))) = '' RETURN --  ,     SET @ListString = @ListString + @Delim --   ,  CHARINDEX   > 0,    ; -- CTE    WITH IDRows (ID, Pos) AS ( --   ( ) SELECT CONVERT(INT, SUBSTRING(@ListString, 1, CHARINDEX(@Delim, @ListString, 1) - 1)), CHARINDEX(@Delim, @ListString, 1) + 1 UNION ALL --   (   ,   ) SELECT CONVERT(INT, SUBSTRING(@ListString, Pos, CHARINDEX(@Delim, @ListString, Pos) - Pos)), CHARINDEX(@Delim, @ListString, Pos) + 1 FROM IDRows WHERE Pos < LEN(@ListString) --    ) --   CTE    INSERT INTO @ID (ID) SELECT ID FROM IDRows ORDER BY Pos --     - ..       OPTION (MAXRECURSION 32767) --     100 (  ,    ) RETURN END
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

例を使用して説明します。



テストデータでテーブルを作成し、それらが非常に多様であることを確認します;）：

 DECLARE @testdata TABLE (ID INT IDENTITY PRIMARY KEY CLUSTERED, Name VARCHAR(128)) INSERT INTO @testdata SELECT TOP 1000 A.name + B.name FROM sysobjects A CROSS JOIN sysobjects B ORDER BY NEWID() SELECT * FROM @testdata
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

DB_get_keys関数を使用してキーの選択をシミュレートし、目的のフィールドで逆ソートし、すぐにCSVに変換します。

 DECLARE @STR VARCHAR(MAX) = '' SELECT TOP 20 @STR = @STR + ',' + CONVERT(VARCHAR, ID) FROM @testdata WHERE Name LIKE 'C%' ORDER BY Name DESC IF LEN(@STR) > 0 SET @STR = RIGHT(@STR, LEN(@STR)-1) SELECT @STR
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

最後に、DB_get_records_by_keysを模倣します。

 SELECT TD.* FROM @testdata TD INNER JOIN dbo.TF_IDListToTableWithOrder(@STR, ',') LTT ON TD.ID = LTT.ID ORDER BY LTT.RowIdx
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このすべてをアプリケーションサーバーと連携させるには、ナビゲーション中にユーザーコンテキスト（Webセッション用）にキー値の配列を保存する必要がありますが、これには大量のメモリが必要と思われます。 ただし、キーが整数であり、オブジェクトの配列ではなく単純なスカラー配列に格納されている場合（違いは基本です！）、100,000のキーがアプリケーションサーバーで400 kBしか占有しないとしましょう。これは、現代の標準ではかなり小さいです。



次に、ナビゲーション中に追加/削除されたエントリに対するメソッドの感度について説明します。 フィルター基準に該当する新しく追加されたレコードがユーザーに表示されないことは明らかです- それらのキー値は、リスト全体が選択された瞬間より後に表示されます。 削除に関しては、当然ながら、欠落しているレコードは返されず、キーのセットによって実際に受信されたレコードの数は要求された数よりも少ない場合があります。 この状況は、受信したレコードのIDを要求されたレコードと比較し、不足しているレコードの代わりに「監視中のレコードが削除されました」タイプのスタブを表示することにより、アプリケーション層で処理できます。 または、ビジネステーブルを使用したテーブル関数の結果のLEFT JOINを作成できます（この場合、リモートレコードのすべてのフィールドはNULLになります）。この事実はクライアントで処理されます。 一般的に、オプションが利用可能です。



そして最後。 この方法は、オンラインオークションシステムをアップグレードして、選択したロットをフィルター（ページまたは1つずつ-前後）で表示し、入札および継続的なナビゲーションの可能性がある場合に使用されました。 このようなアプリケーションでは、1つのフィルターでのナビゲーションの平均数が非常に多いため、従来のページネーションをこれに置き換えることは、ピーク負荷時にSQLサーバーの運命を大幅に軽減することを可能にする効果的な手段の1つでした