データベース開発ソリューション

こんにちは プロジェクト開発のプロセスでは、データベースのパフォーマンスの問題に直面する必要があります。 データ量は増加しており、問題のある領域は思わず浮かんでいます。



この投稿では、プロジェクトの初期段階では見えない、あまり成功していないソリューションについて説明します。 そして、生産性をさらに大幅に高めることができるソリューション。



この投稿は、おそらくソリューションを探している経験豊富な開発者向けに設計されているため、簡単に説明します。

結果フィールドで選択

多くの開発者は、クエリを記述するときに次の構成を使用します。

SELECT T.Column1, (SELECT MAX(Column) FROM Table2) as Column3, T.Column2 FROM Table1 T
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

実際には、テーブルTable1の各行に対して、SELECT MAX（列）FROM Table2が選択されるため、新しい選択には追加のリソースが必要です。 この場合、Table1およびTable2のデータ量が増加すると、パフォーマンスが低下します。



許容可能なオプション。

 SELECT T.Column1, T2.Column3, T.Column2 FROM Table1 T JOIN (SELECT Max(Column) as Column3 FROM Table2) T2 ON 1 = 1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この場合、テーブルTable2からの選択は1回実行され、結果はテーブルTable1の各行に置き換えられます。

左結合をユニオンに置き換える

意味が同じタイプの複数のテーブルからデータを収集する必要があり、多くの場合、次の解決策に頼る場合があります

 SELECT M.Id, COALESCE(P1.Sum, 0) + COALESCE(P2.Sum, 0) + COALESCE(P3.Sum, 0) + ... + COALESCE(PN.Sum, 0) as Sum FROM MainTable M LEFT JOIN PayTable1 P1 ON P1.Id = M.Id LEFT JOIN PayTable2 P2 ON P2.Id = M.Id LEFT JOIN PayTable3 P3 ON P3.Id = M.Id . . . LEFT JOIN PayTableN PN ON PN.Id = M.Id
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このアプローチにはいくつかの問題があります。

1. PayTableNテーブルを増やし、テーブルを増やしていくと、データが少なくてもパフォーマンスが低下します。

2.多くのDBMSインデックスでは機能しないLEFT JOINを使用する必要があります。



LEFT JOINの代わりにJOINを組み合わせて使用​​することを優先して、乗算の演算を放棄することにより、パフォーマンスを改善できます。

 SELECT R.Id, SUM(R.Sum) as Sum FROM ( SELECT M.Id, P1.Sum FROM MainTable M JOIN PayTable1 P1 ON P1.Id = M.Id UNION ALL SELECT M.Id, P2.Sum FROM MainTable M JOIN PayTable2 P2 ON P2.Id = M.Id UNION ALL SELECT M.Id, P3.Sum FROM MainTable M JOIN PayTable3 P3 ON P3.Id = M.Id . . . UNION ALL SELECT M.Id, PN.Sum FROM MainTable M JOIN PayTableN PN ON PN.Id = M.Id ) R GROUP BY R.Id
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

さらに、そのような構造は保守が簡単です。 新しいPayTableNテーブルを追加する場合、1回の復reで対応するブロックを追加する必要があります。

結合条件でスカラー演算を使用する

結合条件では、フィールドで追加の操作を使用する必要がある場合があります。その場合、インデックスは使用されません

 SELECT T1.*, T2.* FROM Table1 T1 JOIN Table2 T2 ON T2.Id = T1.Id AND COALESCE(T2.Column, 0) = COALESCE(T1.Column, 0)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

可能な解決策：

1. WHERE句を入力しますが、このオプションは常に肯定的な結果をもたらすとは限りません。 オプティマイザーがこれを行うことができます。

 SELECT T1.*, T2.* FROM Table1 T1 JOIN Table2 T2 ON T2.Id = T1.Id WHERE COALESCE(T2.Column, 0) = COALESCE(T1.Column, 0)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

2.リクエストを2つに分割します。

 SELECT T1.*, T2.* FROM Table1 T1 JOIN Table2 T2 ON T2.Id = T1.Id AND T2.Column = T1.Column UNION SELECT T1.*, T2.* FROM Table1 T1 JOIN Table2 T2 ON T2.Id = T1.Id AND T2.Column IS NULL AND T1.Column IS NULL
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Withを使用したクエリ

WITH句は確かにクエリをより理解しやすく構造化するのに役立ちますが、多くの人はクエリがどのように機能するかを完全に理解せず、次の間違いを犯します。

 WITH MainSubQuery AS ( SELECT * FROM Table1 ), SubQuery AS ( --   ,    ,    SELECT * FROM Table2 ) SELECT * FROM MainSubQuery M LEFT JOIN SubQuery Q1 ON Q1.id = M.id AND Q1.Param = 1 LEFT JOIN SubQuery Q2 ON Q2.id = M.id AND Q2.Param = 2 LEFT JOIN SubQuery Q3 ON Q3.id = M.id AND Q3.Param = 3 LEFT JOIN SubQuery Q4 ON Q4.id = M.id AND Q4.Param = 4
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

問題は、多くの人がSubQueryサブクエリが1回実行され、その後必要な場合にのみ結果が取得および置換されると考えることです。

しかし実際には、次のものが得られます。

 SELECT * FROM (SELECT * FROM Table1) M LEFT JOIN (SELECT * FROM Table2) Q1 ON Q1.id = M.id AND Q1.Param = 1 LEFT JOIN (SELECT * FROM Table2) Q2 ON Q2.id = M.id AND Q2.Param = 2 LEFT JOIN (SELECT * FROM Table2) Q3 ON Q3.id = M.id AND Q3.Param = 3 LEFT JOIN (SELECT * FROM Table2) Q4 ON Q4.id = M.id AND Q4.Param = 4
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この問題は、一時テーブルに頼ることで解決できます。 大量のクエリの結果は、一時テーブルに事前に配置してから使用する必要があります。 この方法は、IBM DB2ワールドのNICKNAMEをソースデータセットとして使用する場合にも有効です。

 INSERT Session.MainSubQuery SELECT * FROM Table1; INSERT Session.SubQuery SELECT * FROM Table2; SELECT * FROM Session.MainSubQuery M LEFT JOIN Session.SubQuery Q1 ON Q1.id = M.id AND Q1.Param = 1 LEFT JOIN Session.SubQuery Q2 ON Q2.id = M.id AND Q2.Param = 2 LEFT JOIN Session.SubQuery Q3 ON Q3.id = M.id AND Q3.Param = 3 LEFT JOIN Session.SubQuery Q4 ON Q4.id = M.id AND Q4.Param = 4;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

護衛

次に、機械の生産性から人間の労働生産性に移行します。 データベースの保守および保守の日常業務を促進する方法について説明します。



プロジェクト開発のプロセスでは、プロシージャ、関数、テーブル、その他のデータベースオブジェクトを再作成する必要がありますが、

すべてのDBMSがCREATE OR REPLACE PROCEDURE [FUNCTION、TABLE、VIEW、TRIGER、...]構造を持っているわけではなく、

更新では、DROP、ALTERなどを常に監視します。 各DBMSで異なります。



この問題の解決策は、たとえば次のような独自のサービス手順の導入です。

 CALL[EXECUTE] DropProcedure(SchemaName, ProcedureName) CALL[EXECUTE] DropTable(TableName, TableName) CALL[EXECUTE] DropView(SchemaView, ProcedureView)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

など



そして、以前に作成されたかどうかに関係なく、対応するオブジェクトをすでに作成しています。



しかし、このアプローチでは、オブジェクトへのアクセス権に関連する問題もあります。 オブジェクトを削除および作成すると、オブジェクトへのグループ権限が飛び出します。 これはDBMSの新しいオブジェクトです。



決定は、たとえば次のように異なる場合があります。

権利発行スクリプトを最新の状態に保ち、更新ごとにダウンロードします（不都合）。

グループポリシーをまったく使用しないでください（適切ではありません）。



かなり便利で効果的なソリューションは、一対のサービス手順を入力することです。1つは更新の開始時に開始され、2つ目は終了時に開始されます。

 --   CALL[EXECUTE] StoreRights --    --  CALL[EXECUTE] RestoreRights
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次の質問は、MS SQL DBMSのデータベース構造の維持に関するものです。つまり、テーブルや列にコメントを付けるという非常に不便な方法に関連しています。 これは、めったに行われないか、まったく行われません。



例：

  EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'main', @level1type=N'TABLE',@level1name=N'TextTable', @level2type=N'COLUMN',@level2name=N'id';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

サービスラッパールーチンのセットを作成することもできます。

  EXEC Service.CommentOnTable N'SchemaName', N'TableName', N'TableComment'; EXEC Service.CommentOnColumn N'SchemaName', N'TableName', N'ColumnName', N'ColumnComment'; EXEC Service.CommentOnProcedure N'SchemaName', N'TableName', N'ProcedureComment';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そのため、すでに短く、より情報量が多く、仕事が楽しくなっています。



もちろん、これは一連のサービス手順に限定されるものではなく、多くのことを思いつくことができます。



すべての最高の、楽しい仕事！