テスト後、400万件以上のレコードを含むテーブルに対して条件付きクエリが行われ、クエリ時間は最大で40秒に達しました。 したがって、SQL文クエリの効率を向上させる方法が非常に重要です。 以下はインターネット上で広く流通しているクエリ文最適化手法のいくつかです。
まず第一に、データ量が多い場合は、テーブル全体をスキャンするのを避け、関係する列のインデックスを作成し、順番をつけることを検討すべきです。これによりデータの取得が大幅に速くなります。 しかし、インデックスがうまく機能しない状況もあります:
1. where節で!=や<>演算子を使わないよう注意してください。そうしないとエンジンがインデックスの使用を放棄し、テーブル全体を走査してしまいます。
2. where節のフィールドでヌル値判断を避けてください。そうしないとエンジンはインデックスの使用を放棄し、例えば以下の通りの完全なテーブルスキャンを行います。
numがnullであるTからIDを選択します。
numのデフォルト値を0に設定し、テーブルのnum列にnull値がないか確認してから、次のようにクエリを行えます:
n=0 の から id を t(n)から選択します
3. WHERE節でORを使わないよう注意してください。そうしないとエンジンがインデックスの使用を放棄し、例えば次のような完全なテーブルスキャンを実行してしまいます。
TからIDを選択し、num=10またはnum=20
このように問い合わせることができます:
n=10 の T から ID を選択します
全員連合
n=20 の T から ID を選択します
4. 以下のクエリも完全なテーブルスキャンを行います:
名前は「%ABC%」のように名前のTからIDを選択します。
効率を高めるために、全文検索を検討してください。
5. 「in」と「in」でない「in」も注意して使うべきです。そうしないと、例えば次のような完全なテーブルスキャンにつながります。
tからidを選択し、num in(1,2,3)を選びます。
連続的な値については、もしbetweenを使えるなら、 inは使わないでください:
Tから1から3の間に数が入るIDを選択します。
6. where節のパラメータを使うと、テーブル全体がスキャンされます。 SQLはローカル変数を実行時にのみ解決しますが、最適化者はアクセス計画の選択を実行時まで延期することはできません。 コンパイル時に選択しなければなりません。 しかし、コンパイル時にアクセス計画が設定されている場合、変数の値はまだ未知のままで、インデックス選択の入力項目として使用できません。 以下の声明が全文をスキャンされます。
TからIDを選択し、ここでNUM=@num
クエリにインデックスを強制的に使うこともできます:
TからIDを選択します。ここでNUM=@num
7. where節でフィールドを表現しないよう注意してください。これによりエンジンがインデックスの使用を放棄し、テーブル全体スキャンを行う原因となります。 例えば：
n/2=100 の から ID を T(数/2=100)を選択する
以下に変更すべきです:
n=100*2 の T から ID を選択します
8. where節のフィールドに対して関数操作を行うのは避けてください。これによりエンジンがインデックスの使用を放棄し、テーブル全体をスキャンする原因となります。 例えば：
TからIDを選択し、サブストリング(name,1,3)='abc' – ABCで始まる名前ID
Datediff(Day, createdate, '2005-11-30′)=0–'2005-11-30′ 生成IDを Tから選択します
以下に変更すべきです:
名前が「ABC%」のようにTからIDを選択します。
ここで CreateDate>='2005-11-30' および createdate<'2005-12-1′ の から ID を選択
9. ここで節の「=」の左側にある関数、算術演算、その他の式操作を行わないでください。そうしないとシステムがインデックスを正しく使えない可能性があります。
10. インデックスフィールドを条件として使用する場合、インデックスが複合インデックスの場合、システムがインデックスを使用することを確認するためにインデックスの最初のフィールドを条件として使用しなければなりません。そうでなければインデックスは使われず、フィールドの順序はできるだけインデックスの順序と一貫している必要があります。
11. 空のテーブル構造を生成するような意味のないクエリを書かないでください:
tからcol1,col2を #t に選択し、ここで1=0
この種のコードは結果セットを返しませんが、システムリソースを消費するため、次のように変更すべきです。
テーブルの作成 #t(...)
12. 多くの場合、isをinの代わりに使うのが良い選択です:
Num in の A から NUM を選択(B から Num を選択)
次の文に置き換えます。
存在する A から NUM を選択します(num=a.num の場合は b から 1 を選ぶ)
インデックス作成時に注意すべきこと:
1. すべてのインデックスがクエリに有効というわけではなく、SQLはテーブル内のデータを基にクエリを最適化します。インデックス列に大量のデータ重複がある場合、SQLクエリはインデックスを使わず、例えばテーブルのフィールドが性別、男性、女性のほぼ半分を占めている場合、たとえインデックスが性別に基づいていてもクエリ効率には影響しません。
2. インデックス数が多いほど良いわけではない。インデックスは対応するセレクトの効率を確かに向上させるが、挿入や更新の際にインデックスが再構築される可能性があるため、インデックスの構築方法を状況に応じて慎重に検討する必要があるため、挿入と更新の効率を低下させる。 テーブルにインデックスを6つ以上持たないのが最適で、多すぎる場合はあまり使われない列にインデックスを作る必要があるか検討してください。
3. クラスタ化されたインデックスデータカラムの更新はできるだけ避けてください。なぜなら、クラスタ化されたインデックス付きデータカラムの順序はテーブルレコードの物理的な保存順序であり、カラム値が変わるとテーブルレコード全体の順序が調整され、かなりのリソースを消費するためです。 アプリケーションシステムがクラスタインデックスの列を頻繁に更新する必要がある場合、インデックスをクラスタインデックスとして構築すべきかどうかを考慮する必要があります。
その他の注目点:
1. 数値フィールドを使用し、数値情報のみを文字として含むフィールドは避けてください。これはクエリや接続の性能を低下させ、ストレージのオーバーヘッドを増加させます。 これは、クエリや結合を処理する際にエンジンが文字列内の各文字を逐字比較するのに対し、数値型の場合は一度だけ比較すれば済むためです。
2. どこでもselect*をtから使わず、「*」を特定のフィールドリストに置き換え、未使用のフィールドは返さないこと。
3. 一時的なテーブルではなくテーブル変数を使うようにしましょう。 テーブル変数に大量のデータが含まれている場合、インデックスは非常に制限されていることに注意してください(プライマリキーインデックスのみ)。
4. システムテーブル資源の消費を減らすために、一時テーブルを頻繁に作成・削除するのを避ける。
5. 一時テーブルは使えないものではなく、適切に使用することで、例えば大きなテーブルやよく使われるデータセットを繰り返し参照する必要がある場合など、特定のルーチンをより効果的にします。 ただし、一度きりのイベントの場合はエクスポートテーブルを使うのが最善です。
6. 一時テーブルを作成する際、一度に挿入されるデータ量が多い場合は、テーブル作成の代わりに「選択」を選び、大量のログを発生させないため速度が向上します。 データ量が少ない場合は、システムテーブルのリソースを軽減するために、まずテーブルを作成し、その後挿入してください。
7. 一時テーブルを使用する場合は、ストアドプロシージャの最後にすべての一時テーブルを明示的に削除し、まずテーブルを切り落とし、その後テーブルをドロップするようにして、システムテーブルの長いロックを避けてください。
8. カーソルの使用は避けてください。カーソルの効率が悪いため、カーソルで動作するデータが10,000行を超える場合は書き換えを検討すべきです。
9. カーソルベースの方法や一時的なテーブルの方法を使う前に、まず問題を解決するための集合ベースの解決策を探すべきであり、集合ベースの方法の方が通常より効果的です。
10. 一時テーブルのように、カーソルは使えないわけではありません。 小さなデータセットに対してFAST_FORWARDカーソルを使う方が、特に必要なデータを得るために複数のテーブルを参照する必要がある場合、他の行ごとの処理方法よりも優れていることが多いです。 結果セットに「total」を含むルーチンは、カーソルで実行されるものよりも通常高速です。 開発時間に余裕があれば、カーソルベースと集合ベースの方法の両方を試して、どちらがより効果的かを試すことができます。
11. すべてのストアドプロシージャとトリガーの冒頭にSET NOCOUNT ONを設定し、最後にSET NOCOUNT OFFを設定します。 ストアドプロシージャやトリガーの各文を実行した後にクライアントにDONE_IN_PROCメッセージを送る必要はない。
12. 大量のデータがクライアントに返却されるのは避け、データ量が多すぎる場合は、その需要が妥当かどうかを考慮すべきです。
13. 大規模なトランザクション操作を避け、システムの並行性を向上させること。
|
掲載地 2018/05/11 13:57:06
|
|
|
掲載地 2018/05/17 10:12:27