データベース コードによるデータベースのロックを防ぐために SQL クエリを高速化するためのヒント

データベース分野はまだ比較的未成熟であるため、SQL 開発者はあらゆるプラットフォームで苦労し、同じ間違いを繰り返しています。もちろん、データベース ベンダーはある程度の進歩を遂げており、引き続き大きな問題に取り組み続けています。

SQL 開発者が SQL Server、Oracle、DB2、Sybase、MySQL、またはその他のリレーショナル データベース プラットフォームでコードを作成しているかどうかに関係なく、同時実行性、リソース管理、スペース管理、実行速度が依然として彼らを悩ませています。

問題の 1 つは、ほぼすべてのベスト プラクティスに対して、少なくとも 1 つの例外を指摘できる特効薬がないことです。

データベースのチューニングは芸術でもあり科学でもあると言いますが、全体に適用される厳格なルールはほとんどないので、これは当然のことです。あるシステムで解決した問題は別のシステムでは問題になりませんし、その逆も同様です。

クエリのチューニングに関しては正しい答えはありませんが、だからといって諦める必要はありません。次の原則のいくつかに従うことで、素晴らしい結果が得られることが期待できます。

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

この問題は非常に一般的ですが、UPDATE を使用するのが自然で論理的であると思われるため、多くの開発者はこの問題を無視することがよくあります。

次のシナリオを例に挙げます。一時テーブルにデータを挿入し、別の値が存在する場合は、特定の値を表示する必要があります。

おそらく、Customer テーブルからレコードを抽出し、注文金額が 100,000 ドルを超える顧客を「優先」としてマークしたいと考えます。

そこで、テーブルにデータを挿入し、UPDATE ステートメントを実行し、注文金額が 100,000 ドルを超える顧客の CustomerRank 列を「Preferred」に設定します。

問題は、UPDATE ステートメントがログに記録されることです。つまり、テーブルに書き込まれるたびに 2 回書き込まれます。

解決策: SQL クエリでインライン CASE ステートメントを使用します。これにより、各行の注文金額条件がチェックされ、テーブルに書き込まれる前に「優先」タグが設定されます。これにより、処理パフォーマンスが大幅に向上します。

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

この問題も非常に一般的であり、必要なデータを取得できることがわかっているため、他の人が書いたコードをコピーするのは簡単です。

問題は、必要のないデータを取得しすぎることが多く、開発者がそれを合理化することがほとんどないため、結果的に大量のデータが発生することです。

これは通常、WHERE 句内の追加の外部結合または追加の条件として現れます。再利用されたコードを正確な要件に合わせて合理化すると、パフォーマンスを大幅に向上させることができます。

必要な数の列を抽出します

この質問は質問 2 と似ていますが、列に固有のものです。列を 1 つずつリストする代わりに、SELECT* を使用してすべてのクエリをコーディングするのが簡単です。

問題は、必要のないデータが抽出されすぎることです。私はこの間違いを何度も見てきました。開発者は、数百万行を含む 120 列のテーブルに対して SELECT* クエリを実行しますが、使用する列は 3 つまたは 5 つだけです。

つまり、実際に必要なデータよりもはるかに多くのデータを扱っていることになり、クエリが結果を返すのは奇跡です。必要のないデータを大量に処理するだけでなく、他のプロセスからリソースを奪うことになります。

クエリを 2 回 (ダブルディップ) しないでください

これも多くの人が犯す間違いです。それは、数億行のテーブルからデータを抽出するストアド プロシージャを作成することです。

開発者は、カリフォルニアに住んでおり、年間 40,000 ドル以上を稼ぐ顧客に関する情報を抽出したいと考えていました。そこで、カリフォルニアに住む顧客にクエリを実行し、クエリ結果を一時テーブルに格納します。

次に、年収が 40,000 ドルを超える顧客をクエリし、それらの結果を別の一時テーブルに入れます。最後に、2 つのテーブルを結合して最終結果を取得します。

冗談ですか？これは 1 回のクエリで実行する必要があります。代わりに、非常に大きなテーブルに対して 2 回クエリを実行します。愚かなことはしないでください。大きなテーブルに対して 1 回だけクエリを実行してみると、ストアド プロシージャの実行がはるかに高速になることがわかります。

少し異なるシナリオは、プロセスのいくつかのステップで大きなテーブルのサブセットが必要となり、その結果、大きなテーブルが毎回クエリされることになる場合です。

この問題を回避するには、このサブセットをクエリして別の場所に永続化し、後続のステップをこの小さなデータ セットに指示します。

一時テーブルをいつ使用するかを理解する

この問題は解決するのが少し面倒ですが、その効果は大きいです。一時テーブルは、大きなテーブルが 2 回クエリされるのを防ぐなど、さまざまな状況で使用できます。一時テーブルを使用すると、大規模なテーブルの結合に必要な処理能力を大幅に削減することもできます。

テーブルを大きなテーブルに接続する必要があり、大きなテーブルに条件がある場合は、大きなテーブルのデータの必要な部分を一時テーブルに抽出し、その一時テーブルに接続するだけで、次のことができます。クエリのパフォーマンスを向上させます。

これは、同じテーブルに対して同様の結合を実行する必要があるストアド プロシージャ内に複数のクエリがある場合にも役立ちます。

前段階データ

これは、見落とされがちな古い手法であるため、私が話したいお気に入りのトピックの 1 つです。

大きなテーブルに対して同様の結合操作を実行するレポートまたはストアド プロシージャ (またはそのグループ) がある場合、事前にテーブルを結合してテーブルに永続化することでデータを事前にステージングすると、非常に役立ちます。

これで、この事前ステージング テーブルに対してレポートを実行できるようになり、大規模な結合が回避されます。この方法は常に使用できるわけではありませんが、一度使用すると、サーバー リソースを節約する優れた方法であることがわかります。

注意: 多くの開発者は、結合条件を何度も入力する必要がないように、クエリ自体に焦点を当て、結合に基づいて読み取り専用ビューを作成することで、この結合の問題を回避しています。

しかし、このアプローチの問題は、クエリを必要とするすべてのレポートに対してクエリを実行する必要があることです。データを事前にステージングすると、接続を 1 回実行するだけで済み (レポートの 10 分前など)、他のユーザーは大規模な接続を回避できます。

私がこのトリックをどれほど気に入っているかはわかりませんが、ほとんどの環境では、一般的に使用されるテーブルの一部が常に接続されているため、それらを最初に事前ステージングできない理由はありません。

バッチ削除と更新

これも見落とされがちなヒントですが、大規模なテーブルから大量のデータを削除または更新することは、正しく行わないと悪夢のような作業になる可能性があります。

問題は、両方のステートメントが単一のトランザクションとして実行されることです。トランザクションを終了する必要がある場合、またはトランザクションの実行中にシステムで問題が発生した場合、システムはトランザクション全体をロールバックする必要があり、これには長い時間がかかります。

これらの操作はその間、他のトランザクションもブロックするため、実際にはシステムにボトルネックが生じます。解決策は、小さなバッチで削除または更新することです。

これにより、いくつかの方法で問題が解決されます。

トランザクションが終了する理由に関係なく、ロールバックする必要がある行の数が少ないため、データベースははるかに速くオンラインに戻ります。

小さなバッチトランザクションがディスクにコミットされると、他のトランザクションが一部の作業を処理するために入ることができるため、同時実行性が大幅に向上します。

同様に、多くの開発者は、これらの削除操作と更新操作は同じ日に完了する必要があると頑固に信じてきました。これは、特にアーカイブを行っている場合には必ずしも当てはまりません。

操作を延長する必要がある場合は、拡張することができます。これらの集中的な操作の実行に時間がかかる場合は、小さなバッチを使用することでこれを実現できます。システムの速度を低下させないでください。

カーソルのパフォーマンスを向上させるために一時テーブルを使用します

可能であればカーソルを使用しないことが最善です。カーソルには速度の問題があり、それ自体が多くの操作にとって大きな問題となるだけでなく、操作が他の操作を長時間ブロックする原因となり、システムの同時実行性が大幅に低下する可能性があります。

ただし、カーソルの使用を常に回避できるわけではありません。カーソルの使用が避けられない場合は、代わりに一時テーブルに対してカーソル操作を実行して、カーソルによって引き起こされるパフォーマンスの問題を取り除くことができます。

たとえば、テーブルを検索し、いくつかの比較結果に基づいて複数の列のカーソルを更新します。そのデータを一時テーブルに入れて、アクティブなテーブルではなく一時テーブルと比較できる場合があります。

その後、はるかに小さく、ロック時間が短いアクティブ テーブルに対して 1 つの UPDATE ステートメントを実行できます。

このようなデータ変更を実行すると、同時実行性が大幅に向上します。最後に、カーソルを使用する必要はまったくなく、コレクションベースのソリューションが常に存在することを言っておきます。

テーブル値関数の使用

これは、専門家だけが知っている一種の秘密であるため、私が常にお気に入りのテクニックの 1 つです。

クエリの SELECT リストでスカラー関数を使用すると、結果セットの行ごとに関数が呼び出されるため、大規模なクエリのパフォーマンスが大幅に低下する可能性があります。

ただし、スカラー関数をテーブル値関数に変換し、クエリで CROSS APPLY を使用すると、パフォーマンスが大幅に向上します。

同じバッチで多くのテーブルに対して大規模な操作を実行しないでください

これは明白に思えるかもしれませんが、そうではありません。要点をよりよく説明するため、別の鮮やかな例を使用します。

多くのブロックが発生し、多くの操作が停止しているシステムがあります。 1 日に数回実行される削除ルーチンが、明示的なトランザクションで 14 のテーブルからデータを削除していたことが判明しました。 1 つのトランザクションで 14 個のテーブルすべてを処理するということは、すべての削除が完了するまで各テーブルをロックすることを意味します。

解決策は、各テーブルの削除を個別のトランザクションに分割し、各削除トランザクションが 1 つのテーブルのみをロックするようにすることです。

これにより、他のテーブルが解放され、ブロックが緩和され、他の操作の実行が継続できるようになります。ブロックを防ぐために、このような大規模なトランザクションを常に個別の小さなトランザクションに分割する必要があります。

トリガーを使用しないでください

これは前のものとほとんど同じですが、それでも言及する価値があります。トリガーの問題: トリガーで実行したいことはすべて、元の操作と同じトランザクションで実行されます。

Orders テーブルの行を更新中に別のテーブルにデータを挿入するトリガーを作成した場合、トリガーが完了するまで両方のテーブルがロックされます。

更新後に別のテーブルにデータを挿入する必要がある場合は、更新と挿入をストアド プロシージャに入れて、別のトランザクションで実行します。

ロールバックする必要がある場合は、両方のテーブルを同時にロックする必要がなく、簡単にロールバックできます。いつものように、トランザクションは短くし、一度に複数のリソースをロックしないでください。

GUID でクラスター化しないでください

何年も経った今でも、まだこの問題と格闘していることが信じられません。しかし、私は今でも少なくとも年に 2 回はクラスター化された GUID に遭遇します。

GUID (Globally Unique Identifier) は、ランダムに生成された 16 バイトの数値です。この列でテーブル内のデータを並べ替えると、DATE や IDENTITY などの着実に増加する値を使用するよりもはるかに速くテーブルの断片化が発生します。

私は数年前に、クラスター化された GUID を持つテーブルに大量のデータを挿入し、IDENTITY 列を持つ別のテーブルに同じデータを挿入するベンチマークを実行しました。

GUID テーブルは非常に断片化されており、わずか 15 分後にパフォーマンスが数千パーセント低下しました。

5 時間後、IDENTITY テーブルのパフォーマンスは数パーセント低下しただけであり、これは GUID にだけ適用されるのではなく、あらゆる揮発性列に適用されます。

データが存在するかどうかを確認したいだけの場合は、行をカウントしないでください

この状況は非常に一般的です。テーブルにデータが存在することを確認する必要があり、この確認の結果に基づいて、次のことが必要です。特定の操作を実行します。

データが存在するかどうかを確認するために SELECT COUNT(*) FROMdbo.T1 を実行している人をよく見かけます。 @ct＆gt; 0

存在する場合 (dbo.T1 から 1 を選択)

1. BEGIN

3. END

4. テーブル内のすべてをカウントするのではなく、最初の行を取得するだけです探す。 SQL Server は EXISTS を正しく使用できるほど賢く、2 番目のコードは非常に高速に結果を返します。
5. テーブルが大きくなるほど、この点の違いがより明確になります。データが大きくなりすぎる前に、適切な対処を行ってください。データベースを調整するのに早すぎるということはありません。
   私は実際に、運用データベースの 1 つで、2 億 7,000 万行のテーブルに対してこの例を実行しました。
6. 最初のクエリには 15 秒かかり、456,197 件の論理読み取りが含まれていました。2 番目のクエリは 1 秒未満で結果を返し、含まれる論理読み取りは 5 件のみでした。
   ただし、本当にテーブルの行をカウントする必要があり、テーブルが大きい場合は、システム テーブルから抽出する別の方法もあります。

SELECT rows fromsysindexes を使用すると、すべてのインデックスの行数が取得されます。

そして、クラスター化インデックスはデータ自体を表すため、WHERE indid = 1 を追加してテーブルの行を取得し、テーブル名を含めるだけです。

1. したがって、最後のクエリは次のとおりです:
   1.SELECT rows from sysindexes where object_name(id)='T1'andindexid =1

2. 2 億 7,000 万行のテーブルでは、結果は 1 秒以内に返されます, 論理読み取りが 6 回だけなので、パフォーマンスが異なります。

逆方向検索を実行しないでください

3. 単純なクエリ SELECT * FROMCustomers WHERE RegionalID を例として挙げます。このクエリはテーブル スキャンを利用した行ごとの比較を必要とする逆検索であるため、このクエリではインデックスを使用できません。このようなタスクを実行する必要がある場合は、インデックスを使用するようにクエリを書き直すとパフォーマンスが大幅に向上する場合があります。

クエリは次のように簡単に書き直すことができます:

4. 1.SELECT * FROM Customers WHERE RegionalID

このクエリはインデックスを使用するため、データセットが大きい場合は、そのパフォーマンスはテーブル スキャン バージョンよりもはるかに優れています。

もちろん、それほど簡単なことはなく、パフォーマンスが悪化する可能性があるため、使用する前に試してください。非常に多くの要因が関係していますが、100% 機能します。

最後に、このクエリはルール 4 に違反していることに気付きました。クエリを 2 回実行しないでください。しかし、これは厳密なルールがないことも示しています。ここでは 2 回クエリを実行していますが、コストのかかるテーブル スキャンを避けるためにこれを実行しています。

これらのヒントをすべて常に使用できるわけではありませんが、心に留めておけば、いつか大きな問題を解決するために使用できるでしょう。

覚えておくべき最も重要なことは、私の言うことを独断として受け取らないでください。実際の環境で試してみてください。同じ解決策がすべてのケースで機能するわけではありませんが、パフォーマンス低下のトラブルシューティングに私がいつも使用しているものであり、常に機能します。