SQLが解き放たれる:SQLクエリを高速化する17の方法

すべてのプラットフォームのSQL開発者は苦労DO WHILEしており、同じ過ちを何度も繰り返すループに陥っているように見えます。これは、データベースフィールドがまだ比較的未成熟であるためです。確かに、ベンダーはいくつかの進歩を遂げていますが、彼らはより大きな問題に取り組み続けています。並行性、リソース管理、スペース管理、および速度は、SQL Server、Oracle、DB2、Sybase、MySQL、またはその他のリレーショナルプラットフォームでコーディングしているかどうかにかかわらず、SQL開発者を悩ませています。

問題の一部は、特効薬がないことです。ほとんどすべてのベストプラクティスについて、少なくとも1つの例外を示すことができます。通常、開発者は自分の好きな方法を見つけますが(通常、パフォーマンスや並行性のための構成要素は含まれていません)、他のオプションを探す必要はありません。おそらく、それは教育不足の兆候であるか、開発者がプロ​​セスに近すぎて、何か間違ったことをしていることを認識できません。クエリはローカルのテストデータセットではうまく実行されますが、本番システムでは惨めに失敗する可能性があります。

SQL開発者が管理者になることは期待していませんが、コードを作成する際には本番環境の問題を考慮する必要があります。最初の開発中にそれを行わなかった場合、DBAは単に戻って後でそれを行うようにし、ユーザーはその間に苦しみます。

データベースのチューニングは芸術であると同時に科学でもあると私たちが言うのには理由があります。これは、全面的に適用される厳格なルールがほとんど存在しないためです。あるシステムで解決した問題は別のシステムの問題ではなく、その逆も同様です。クエリの調整に関しては正しい答えはありませんが、それはあきらめるべきだという意味ではありません。

あなたが従うことができるいくつかの良い原則があり、それはある組み合わせまたは別の組み合わせで結果をもたらすはずです。私はそれらをSQLのすべきこととすべきでないことのリストにカプセル化しましたが、それはしばしば見落とされたり、見つけにくいものです。これらの手法により、DBAの考え方についてもう少し洞察が得られるだけでなく、本番指向の方法でプロセスについて考え始めることができます。

1.のUPDATE代わりに使用しないでくださいCASE

この問題は非常に一般的であり、見つけるのは難しいことではありませんが、多くの開発者UPDATEは、使用には論理的に見える自然な流れがあるため、見落としがちです。

たとえば、次のシナリオを考えてみましょう。一時テーブルにデータを挿入していて、別の値が存在する場合に特定の値を表示する必要があります。顧客テーブルから取得していて、注文数が$ 100,000を超える人に「優先」というラベルを付けたい場合があります。したがって、データをテーブルに挿入し、UPDATEステートメントを実行して、注文数が$ 100,000を超える人のCustomerRank列を「Preferred」に設定します。問題は、UPDATEステートメントがログに記録されることです。つまり、テーブルへの書き込みごとに2回書き込む必要があります。もちろん、これを回避する方法CASEは、SQLクエリ自体でインラインステートメントを使用することです。これにより、すべての行の注文金額条件がテストされ、テーブルに書き込まれる前に「優先」ラベルが設定されます。パフォーマンスの向上は驚異的です。

2.コードをやみくもに再利用しないでください

この問題も非常に一般的です。必要なデータを取得することがわかっているため、他の人のコードをコピーするのは非常に簡単です。問題は、必要以上に多くのデータを取得することが非常に多く、開発者がわざわざデータをトリミングすることはめったにないため、データの巨大なスーパーセットになってしまうことです。これは通常、追加の外部結合またはWHERE句内の追加の条件の形式で提供されます。再利用されたコードを正確なニーズに合わせてトリミングすると、パフォーマンスが大幅に向上します。

3.必要な数の列のみをプルします

この問題は問題2に似ていますが、列に固有です。SELECT *列を個別にリストする代わりに、すべてのクエリをコーディングするのは非常に簡単です。ここでも問題は、必要以上のデータを取得することです。私はこのエラーを何十回も見てきました。開発者は、SELECT *120列と数百万行のテーブルに対してクエリを実行しますが、最終的には3〜5行しか使用しません。その時点で、必要以上に多くのデータを処理しているので、クエリがまったく返すのは不思議です。必要以上のデータを処理しているだけでなく、他のプロセスからリソースを奪っています。

4.ダブルディップしないでください

これは、私が必要以上に何度も見たものです。ストアドプロシージャは、数億行のテーブルからデータをプルするように記述されています。開発者は、カリフォルニアに住んでいて、40,000ドル以上の収入がある顧客を必要としています。そこで彼は、カリフォルニアに住む顧客を照会し、その結果を一時テーブルに入れます。次に、収入が40,000ドルを超える顧客を照会し、それらの結果を別の一時テーブルに配置します。最後に、彼は両方のテーブルを結合して最終製品を取得します。

私をからかってるの?これは単一のクエリで実行する必要があります。代わりに、超大型のテーブルをダブルディッピングしています。バカにならないでください。大きなテーブルを可能な限り一度だけクエリしてください。プロシージャのパフォーマンスがどれだけ優れているかがわかります。

少し異なるシナリオは、大きなテーブルのサブセットがプロセスのいくつかのステップで必要になる場合です。これにより、大きなテーブルが毎回クエリされます。これを回避するには、サブセットをクエリして他の場所に永続化し、後続の手順で小さいデータセットを指定します。

6.プレステージデータを実行します

これは、見過ごされがちな古い手法であるため、私のお気に入りのトピックの1つです。大きなテーブルに対して同様の結合を行うレポートまたはプロシージャ(または、さらに良いのはそれらのセット)がある場合、テーブルを事前に結合して永続化することにより、データを事前にステージングすることは有益です。テーブルに。これで、レポートはその事前にステージングされたテーブルに対して実行され、大きな結合を回避できます。

この手法を常に使用できるとは限りませんが、使用できる場合は、サーバーリソースを節約するための優れた方法であることがわかります。

多くの開発者は、クエリ自体に集中し、結合の周りにのみビューを作成することでこの結合の問題を回避しているため、結合条件を何度も入力する必要がないことに注意してください。ただし、このアプローチの問題は、クエリを必要とするすべてのレポートに対してクエリが実行されることです。データを事前ステージングすることにより、結合を1回だけ実行し(たとえば、レポートの10分前)、他のすべての人が大きな結合を回避します。このテクニックがどれだけ好きかはわかりません。ほとんどの環境では、常に結合される人気のあるテーブルがあるため、事前にステージングできない理由はありません。

7.削除と更新をバッチで実行します

これは、見過ごされがちなもう1つの簡単なテクニックです。巨大なテーブルから大量のデータを削除または更新することは、正しく行わないと悪夢になる可能性があります。問題は、これらのステートメントが両方とも単一のトランザクションとして実行されることです。これらのステートメントを強制終了する必要がある場合、またはそれらの動作中にシステムに何かが発生した場合、システムはトランザクション全体をロールバックする必要があります。これには非常に長い時間がかかる場合があります。これらの操作は、他のトランザクションをその期間ブロックすることもでき、本質的にシステムのボトルネックになります。

解決策は、削除または更新を小さなバッチで行うことです。これにより、いくつかの方法で問題が解決されます。まず、何らかの理由でトランザクションが強制終了された場合、ロールバックする行数が少ないため、データベースははるかに迅速にオンラインに戻ります。第2に、小さいバッチがディスクにコミットしている間、他のバッチが侵入して一部の作業を実行できるため、同時実行性が大幅に向上します。

これらの方針に沿って、多くの開発者は、これらの削除および更新操作を同じ日に完了しなければならないことに頭を悩ませています。特にアーカイブしている場合は、必ずしもそうとは限りません。必要な限りその操作を拡張でき、バッチを小さくするとそれを実現できます。これらの集中的な操作を実行するのに時間がかかる場合は、余分な時間を費やして、システムをダウンさせないでください。

8.カーソルのパフォーマンスを向上させるために一時テーブルを使用してください

可能であれば、カーソルから離れるのが最善であることを、私たち全員が知っていることを願っています。カーソルは、それ自体が多くの操作で問題になる可能性がある速度の問題に悩まされるだけでなく、操作が必要以上に長く他の操作をブロックする原因となる可能性もあります。これにより、システムの同時実行性が大幅に低下します。

ただし、常にカーソルの使用を回避できるとは限りません。そのような場合は、代わりに一時テーブルに対してカーソル操作を実行することで、カーソルに起因するパフォーマンスの問題を回避できる場合があります。たとえば、テーブルを通過し、いくつかの比較結果に基づいていくつかの列を更新するカーソルを考えてみましょう。ライブテーブルとの比較を行う代わりに、そのデータを一時テーブルに入れて、代わりにそれとの比較を行うことができる場合があります。次にUPDATE、ライブテーブルに対して、はるかに小さく、短時間だけロックを保持する単一のステートメントがあります。

このようにデータの変更を狙撃すると、同時実行性が大幅に向上する可能性があります。最後に、カーソルを使用する必要はほとんどないと言います。ほとんどの場合、セットベースのソリューションがあります。あなたはそれを見ることを学ぶ必要があります。

9.ビューをネストしないでください

ビューは便利ですが、使用するときは注意が必要です。ビューは、ユーザーからの大規模なクエリを隠し、データアクセスを標準化するのに役立ちますが、ビューを呼び出すビューを呼び出すビューがあり、ビューを呼び出すビューがある状況に簡単に気付くことができます。これはネストビューと呼ばれ、特に2つの方法で深刻なパフォーマンスの問題を引き起こす可能性があります。

  • まず、必要以上に多くのデータが戻ってくる可能性が非常に高くなります。
  • 第二に、クエリオプティマイザはあきらめて悪いクエリプランを返します。

私はかつて、入れ子の景色を愛するクライアントがいました。クライアントには、2つの重要な結合があるため、ほとんどすべてに使用される1つのビューがありました。問題は、ビューが2MBのドキュメントを含む列を返すことでした。いくつかの文書はさらに大きかった。クライアントは、実行したほぼすべてのクエリのすべての行に対して、ネットワーク全体で少なくとも2MBを追加でプッシュしていました。当然、クエリのパフォーマンスはひどいものでした。

そして、どのクエリも実際にその列を使用していませんでした!もちろん、柱は7つの深さで埋められていたので、見つけることさえ困難でした。ビューからドキュメント列を削除すると、最大のクエリの時間が2.5時間から10分になりました。いくつかの不要な結合と列があるネストされたビューを最終的に解明し、プレーンクエリを作成したとき、同じクエリの時間はサブ秒に短縮されました。