回転：大容量データベースのクエリ最適化と改ページアルゴリズム案3

10231 ワード
大容量データ
私はこの文章を見た時、本当に元気が出て、考えがとてもよかったです。その後、オフィスオートメーションシステム（ASP.NET+C〓〓＋SQL SERVER）を作っている時に、ふとこの文章を思い出しました。この文を改造したら、とてもいいページの保存過程かもしれないと思います。そこで私はこの文章をネットで探しましたが、文章はまだ見つかっていませんでした。この文によって書かれたページ別の保存過程を見つけました。この記憶過程も現在流行しているページ別の保存過程です。先を争ってこの文字を保存過程に改造したことを後悔しています。

       
         
          
        CREATE PROCEDURE pagination2

            (

            @SQL nVARCHAR(4000),    --       SQL  

            @Page int,              --  

            @RecsPerPage int,       --        

            @ID VARCHAR(255),       --         ID 

            @Sort VARCHAR(255)      --       

            )

            AS

            DECLARE @Str nVARCHAR(4000)

            SET @Str='SELECT   TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+')

            T WHERE T.'+@ID+'NOT IN

            (SELECT   TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM

            ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort

            PRINT @Str

            EXEC sp_ExecuteSql @Str

            GO
実は、上記の語句は以下のように簡略化されています。

       
         
          
        SELECT TOP     *

            FROM Table1

            WHERE (ID NOT IN

            (SELECT TOP    *   id

            FROM  

            ORDER BY id))

            ORDER BY ID
しかし、この記憶過程には致命的な欠点があります。つまり、NOT INという文字が含まれています。それを改造できますが、

       
         
          
        SELECT TOP     *

            FROM Table1

            WHERE not exists

            (select * from (select top (   *  ) * from table1 order by id) b where b.id=a.id )

            order by id
つまり、not existsをnot inの代わりに使っていますが、前に話しました。両者の実行効率は実際には違いがありません。
それでも、トップを使ってNOT INを結合するこの方法はやはり遊覧標識を使うより速いです。
not existsでは前のストレージプロセスの効率を保存できませんが、SQL SERVERのトップキーワードを使うのは非常に賢明な選択です。改ページ最適化の最終目的は、あまり大きな記録集が発生しないようにすることです。前にもTOPのメリットを述べました。トップを通じてデータ量のコントロールができます。
改ページアルゴリズムでは、私たちの照会速度に影響を与える要因は2つあります。TOPとNOT INです。TOPは私達の検索速度を高めることができますが、NOT INは私達の調査速度を遅くしますので、改ページアルゴリズム全体の速度を上げるには、NOT INを徹底的に改造して、他の方法と交換します。
ほとんどのフィールドは、max（フィールド）またはmin（フィールド）によって、あるフィールドの最大値または最小値を抽出することができるので、このフィールドが重複していない場合、これらの重複しないフィールドのmaxまたはminを分水嶺として利用して、改ページアルゴリズムの各ページごとの参照物にすることができることを知っています。ここでは、オペレータ「>」または「<」を使って、このミッションを遂行して、検索語句をSARG形式に適合させることができます。例えば:

       
         
          
        Select top 10 * from table1 where id>200
そこで次のような改ページ案があります。

       
         
          
        select top     *

            from table1

            where id>

            (select max (id) from

            (select top ((  -1)*   ) id from table1 order by id) as T

            )

            order by id
値を繰り返すことなく、サイズを見分けることができる列を選択すると、メインキーを選択します。以下の表には、筆者が1000万のデータを持つオフィスオートメーションシステムの表を示しています。GID（GIDはメインキーですが、インデックスを集めているわけではありません。）をシリアルにして、gidを抽出して、fariqi、titleフィールドを使って、それぞれ第1、10、100、500、1000、1万、10万、25万、50万ページを例にして、以上の3種類のプログラムの実行速度をテストします。（ミリ秒：ミリ秒：

       
         
          
                    1          2          3

            1           60           30           76

            10           46           16           63

            100         1076          720          130

            500          540        12943           83

            1000        17110          470          250

            1         24796         4500          140

            10         38326        42283         1553

            25         28140       128720         2330

            50         121686      127946         7168
上の表からは、3つの記憶過程は100ページ以下の改ページコマンドを実行する時に信頼できます。速度もいいです。しかし、最初の案は1000ページ以上のページを実行した後、速度が下がりました。二つ目の案は大体1万ページ以上実行してから速度が下がり始めました。第三の方案はずっと大きな下降力がなくて、後の力は依然として十分です。
第三の改ページスキームを決定した後、これに基づいて保存プロセスを書くことができます。SQL SERVERの格納過程は事前にコンパイルされたSQL文であることが分かります。その実行効率はWEBページを通じて伝えられたSQL文よりも高いです。下の格納プロセスには、改ページスキームだけでなく、ページから送られてきたパラメータに基づいて、データの総数を決定します。

       
         
          
        --         

            CREATE PROCEDURE pagination3

            @tblName   varchar(255),       --   

            @strGetFields varchar(1000) = '*',  --       

            @fldName varchar(255)='',      --       

            @PageSize   int = 10,          --    

            @PageIndex  int = 1,           --   

            @doCount  bit = 0,   --       ,   0     

            @OrderType bit = 0,  --       ,   0     

            @strWhere  varchar(1500) = ''  --      (  :     where)

            AS

            declare @strSQL   varchar(5000)       --    

            declare @strTmp   varchar(110)        --     

            declare @strOrder varchar(400)        --     

            if @doCount != 0

            begin

            if @strWhere !=''

            set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere

            else

            set @strSQL = "select count(*) as Total from [" + @tblName + "]"

            end

            --          @doCount       0，       。         @doCount 0   

            else

            begin

            if @OrderType != 0

            begin

            set @strTmp = "<(select min"

            set @strOrder = " order by [" + @fldName +"] desc"

            --  @OrderType  0，     ，     ！

            end

            else

            begin

            set @strTmp = ">(select max"

            set @strOrder = " order by [" + @fldName +"] asc"

            end

            if @PageIndex = 1

            begin

            if @strWhere != ''

            set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from

            [" + @tblName + "] where " + @strWhere + " " + @strOrder

            else

            set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from

            ["+ @tblName + "] "+ @strOrder

            --             ，         

            end

            else

            begin

            --       @strSQL      SQL  

            set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from

            ["+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "])

            from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from

            [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder

            if @strWhere != ''

            set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["

            + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["

            + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["

            + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "

            + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder

            end

            end

            exec (@strSQL)

            GO
上記の記憶プロセスは、汎用的な記憶プロセスであり、その注釈は既にここに書かれている。
大きなデータ量の場合、特に最後のページを調べる時、照会時間は普通9秒を超えません。他の記憶プロセスを使うと、実際にはタイムアウトを招きますので、この記憶プロセスは大容量データベースのクエリに非常に適しています。
以上の記憶過程の解析を通じて、皆さんに一定のヒントを与え、仕事に一定の効率を上げたいと思います。同時に、同行により優秀なリアルタイムデータ改ページアルゴリズムを提出したいです。
四、インデックスを集める重要性とどのようにインデックスを集めるかを選択するか
前の節のタイトルの中で、筆者が書いたのは、小さなデータ量と大容量のデータを実現するための一般的な改ページ表示保存プロセスです。これは、本記憶プロセスを「オフィスオートメーション」システムに適用した実践の中で、筆者はこの第3の記憶過程が小さなデータ量の場合、次のような現象があることを発見したからです。
1、改ページ速度は普通1秒と3秒の間に維持されます。
2、最後のページを調べる時、速度は普通5秒から8秒までで、ページ総数は3ページか30万ページしかないとしても。
超大容量の場合、この改ページの実現はとても早いですが、前ページの時、この1～3秒の速度は最初のページよりも最適化されていない改ページ方法の速度が遅く、ユーザーの話では「まだACCESSデータベースのスピードが速い」という認識で、ユーザーが開発したシステムの使用を放棄するには十分です。
筆者はこれについて分析してみたが、もともとこのような現象が発生した原因は簡単であるが、また重要である。並べ替えられたフィールドはインデックスを集めるものではない。
この文章のテーマは「最適化と改ページアルゴリズム案を問い合わせる」です。筆者は、「照会最適化」と「改ページアルゴリズム」という二つの連絡は、大きなテーマではなく、どちらも非常に重要なものが必要であるからです。
前の議論で述べたように、統合インデックスは2つの最大の利点があります。
1、一番速いスピードで照会範囲を縮小します。
2、一番速い速度でフィールドの並べ替えを行います。
第1条クエリーの最適化に多く用いられるが、第2条は改ページ時のデータ並び替えに多く用いられる。
また、各テーブル内に集められたインデックスは一つしか作成できません。これにより、統合インデックスがより重要になります。集合インデックスの選択は、「照会最適化」と「効率的な改ページ」を実現する最も重要な要素といえる。
しかし、統合されたインデックス列は、クエリ列の必要性と合致するだけでなく、列の順序にも合致するようにするためには、通常は矛盾しています。
筆者の前の「索引」の議論では、fariqi、つまりユーザーがテキストを送る日付を統合インデックスの開始列とし、日付の精度は「日」としています。このようなやり方の利点は、先に述べたように、画期的なクイッククエリでは、IDキー列よりも大きな利点があります。
ただし、改ページ時には、この集合インデックス列に重複記録が存在するため、maxまたはminを使用して、最も改ページされた参照物を使用することができず、さらに、より効率的なソートができなくなります。IDプライマリキー列が統合インデックスとして使用されると、統合インデックスは、並べ替えに加えて使用されるが、実際には、コレクションインデックスという貴重なリソースを浪費している。
この矛盾を解決するために、日付列を追加しました。そのデフォルト値はgetdateです。ユーザが記録に書き込む時、この列は自動的にその時の時間を書き込みます。時間はミリ秒まで正確です。それでも、可能性の少ないオーバーラップを回避するために、この列にUNIQUE制約を作成します。この日付列をまとめた索引列とします。
この時間型のインデクス列があると、ユーザーはこの列でデータを挿入する際のある期間のクエリを検索することができ、また唯一の列としてmaxまたはminを実現することができ、改ページアルゴリズムの参照物となる。
このような最適化を経て、大データ量の場合も小データ量の場合も、改ページ速度は普通数十ミリ秒、ひいては0ミリ秒であることがわかった。日付で範囲を縮小したクエリの速度は元よりも何も遅くないです。
インデックスを集めることが大切で貴重なので、まとめてみました。
1、お問い合わせの範囲を縮小するために最も頻繁に使用されるフィールド。
2、一番頻繁に使われている並べ替えが必要なフィールドです。
終了語:
この文章はデータベースを使う上での筆者の最近の心得を集めました。「オフィスオートメーション」システムを作る時の実践経験の積み重ねです。この文章が皆さんの仕事に一定の助けを与えるだけでなく、問題を分析する方法を理解してもらいたいです。最も重要なのは、この文章がれんがを投げて玉を引くことができることを望んで、みんなの学習と討論の興味を巻き起こして、共に促進して、共に公安の科学技術の強警の事業と金盾の工事のために自分の最大の努力をします。
最後に説明が必要なのは、テスト中に、ユーザーが大データ量の照会を行っている時に、データベースの速度に一番大きな影響を与えるのはメモリサイズではなく、CPUだということです。私のP 2.4のマシンでテストする時、「資源管理器」を見て、CPUは常に100%まで持続しますが、メモリの使用量は変わっていません。私たちのHP ML 350 G 3サーバーで試験しても、CPUのピークは90%に達し、普通70%ぐらい持続します。
本論文のテストデータはすべて私たちのHP ML 350サーバーから来ています。サーバー構成：デュアルInter Xeon超スレッドCPU 2.4 G、メモリ1 G、オペレーティングシステムWindows Server 2003 Enterprise Edition、データベースSQL Server 2000 SP 3。
皆さんは下記の公安網のウェブサイトやインターネットサイトを訪問して、私達の「千万級」データベースのオフィスオートメーション（ASP.NET＋C唴語）を体験してください。
新しいサーバアカウントの設定
[樹上莫隊]BZOJ 3757 3052 4129