SQLクエリ文の最適化

目次

1. 実行計画とは何ですか?実行計画はどのような情報に依存しますか?
2. SQL ステートメントの記述方法を統一して、解析オーバーヘッドを削減します
3. SQL ステートメントのネストを削減します
4. 中間結果を一時的に保存するために、「一時テーブル」を使用します
5. OLTP システム SQL ステートメントはバインド変数を使用する必要があります
6.斜めフィールドのバインド変数スヌーピングの問題
7. begin tran のトランザクションはできるだけ小さくする必要があります。
8. 一部の SQL クエリ ステートメントに Nolock を追加する必要があります
9. nolock を追加した後、ページ分割が頻繁に発生するテーブルのクエリはスキップされたり、繰り返し読み取られたりする傾向があります
10. クラスター化インデックスはテーブルのシーケンシャル フィールドに構築されません。ページ分割が発生しやすいです
11. 複合インデックスを使用して、複数の where 条件のクエリ速度を向上させます
13. ファジー クエリに like を使用する場合は、最初の %
14 を使用しないように注意してください。 SQL Server テーブルに接続するには
15. Row_number によりテーブル スキャンが発生し、一時テーブル ページングを使用する方が良いです

実行計画とは何ですか?実行計画はどのような情報に依存しますか?

実行プランは、SQL ステートメントと関連テーブルの統計情報に基づいてデータベースによって作成されるクエリ プランです。たとえば、SQL ステートメントを使用してテーブルをクエリする場合、このプランはクエリ オプティマイザーによって自動的に分析されます。 100,000 レコード、1 レコードの場合、クエリ オプティマイザーは「インデックス検索」方法を選択します。テーブルがアーカイブされていて、残りのレコードが 5,000 件のみである場合、クエリ オプティマイザーは計画を変更して「フル テーブル スキャン」方法を使用します。

実行計画は固定されておらず、「個別化」されていることがわかります。正しい「実行計画」を生成するには、次の 2 つの重要なポイントがあります:
SQL ステートメントはクエリ オプティマイザーに何をしたいのかを明確に伝えていますか?
クエリ オプティマイザーによって取得されたデータベース統計は最新かつ正確ですか?

SQL ステートメントの記述方法を統一して解析オーバーヘッドを削減します

次の 2 つの SQL ステートメントについて、プログラマーは同じであると考えますが、データベース クエリ オプティマイザーは異なると考える可能性があります。

select * from Dual

Select * From Dual

実際には、ケースは異なります。クエリ アナライザーはこれを 2 つの異なる SQL ステートメントとみなし、2 回解析する必要があります。 2 つの実行プランを生成します。したがって、プログラマは、同じクエリ ステートメントがどこでも一貫していることを確認する必要があります。スペースが 1 つ多くても機能しません。

SQL ステートメントのネストを減らす

データベースからキャプチャした SQL ステートメントが印刷されると、A4 用紙 2 枚ほどの長さになることがよくあります。一般に、このような複雑なステートメントには通常問題が発生します。この 2 ページにわたる SQL ステートメントを元の作成者に問い合わせましたが、時間がかかりすぎてしばらく理解できないと言われました。オリジナルの作成者ですら SQL ステートメントに混乱する可能性があり、データベースも同様に混乱することが考えられます。

一般に、Select ステートメントの結果はサブセットとして使用され、その後、この種の 1 レベルのネストされたステートメントは比較的一般的ですが、経験によれば、3 つ以上のレベルがある場合に発生します。入れ子になっていると、クエリ オプティマイザーが非常に面倒な実行計画を与える可能性があります。唖然としたからだ。人工知能のようなものは、究極的には人間の解像度よりも劣ります。人間がめまいを感じているなら、データベースも同様にめまいを感じることは間違いありません。

また、実行計画は単純なSQL文ほど再利用できる可能性が高くなります。複雑な SQL ステートメントで 1 文字が変更されると、その文字を再解析する必要があり、大量のゴミがメモリに詰め込まれることになります。データベースがどれほど非効率になるかは考えられます。

「一時テーブル」を使用して中間結果を一時的に保存する

SQL ステートメントを簡素化する重要な方法は、一時テーブルを使用して中間結果を一時的に保存することです。これにより、プログラム内のメイン テーブルの複数回のスキャンが回避され、プログラム実行中の「更新ロック」をブロックする「共有ロック」が大幅に軽減され、ブロッキングが軽減され、同時実行パフォーマンスが向上します。

OLTP システム SQL ステートメントはバインド変数を使用する必要があります

select * from orderheader where changetime > '2010-10-20 00:00:01'
select * from orderheader where changetime > '2010-09-22 00:00 :01'
上記の 2 つのステートメントは、クエリ オプティマイザーによって異なる SQL ステートメントとみなされ、2 回解析する必要があります。バインド変数を使用すると、
select * from orderheader wherechangetime > @chgtime
@chgtime 変数に任意の値を渡すことができるため、多数の同様のクエリで実行プランを再利用でき、データベース解析の負担を大幅に軽減できます。 SQL ステートメント。データベースの効率を向上させるには、一度解析して複数回再利用することが原則です。

スキューフィールドのバインド変数スヌーピング問題

バインド変数はほとんどの OLTP 処理に適用できますが、例外もあります。たとえば、where 条件のフィールドが「スキューフィールド」である場合です。

「傾斜フィールド」とは、列内の値のほとんどが同じであることを意味します。たとえば、国勢調査表の「民族」列では、値の 90% 以上が漢民族です。したがって、SQL ステートメントで 30 歳の漢民族の人口をクエリする場合は、「ethnic」列を where 条件に配置する必要があります。このとき、バインド変数 @nation を使用すると大きな問題が発生します。

@nation によって渡された最初の値が「Han」の場合、実行プラン全体で必然的にテーブル スキャンが選択されることを想像してください。次に、渡される 2 番目の値は「Buyi」です。当然のことながら、「Buyi」の割合は 1 万分の 1 にすぎないため、インデックス検索を使用する必要があります。ただし、最初に解析された「Han」の実行プランは再利用されるため、2回目でもテーブルスキャン方式が使用されます。この問題は有名な「バインド変数スヌーピング」です。「スキューフィールド」にはバインド変数を使用しないことをお勧めします。

begin tran トランザクションはできるだけ小さくする必要があります

SQL Server では、SQL ステートメントはデフォルトでトランザクションであり、ステートメントの実行後にデフォルトでコミットされます。実際、これは begin tran の最小化された形式であり、begin tran が各ステートメントの先頭に暗黙的に示され、commit が最後に暗黙的に示されるのと同じです。
場合によっては、begin tran を明示的に宣言する必要があります。たとえば、「挿入、削除、変更」操作を実行する場合、複数のテーブルを同時に変更する必要があります。成功したか、どれも成功しませんでした。 begin tran はそのような役割を果たすことができ、複数の SQL ステートメントを一緒に実行し、最終的にそれらをまとめてコミットできます。利点はデータの一貫性が保証されていることですが、完璧なものはありません。 Begin tran によって支払われる代償として、送信前に、SQL ステートメントによってロックされているすべてのリソースは、コミットされるまで解放できないということです。
Begin tran がトラップする SQL ステートメントが多すぎると、データベースのパフォーマンスが低下することがわかります。大規模なトランザクションがコミットされる前に、他のステートメントが必然的にブロックされ、その結果、大量のブロックが発生します。
Begin tran を使用する原則は、データの一貫性を確保することを前提として、begin tran によってトラップされる SQL ステートメントが少ないほど良いということです。場合によっては、トリガーを使用してデータを同期できますが、begin tran は必ずしも使用されるわけではありません。

一部の SQL クエリ ステートメントには nolock を追加する必要があります

SQL ステートメントに nolock を追加することは、SQL Server の同時パフォーマンスを向上させる重要な手段です。Oracle の構造はより合理的で、UNDO テーブル スペースがあるため、これは Oracle では必要ありません。 storage "先行データ" の場合、変更中にデータがコミットされていない場合、読み取られるのは変更前のコピーであり、UNDO 表スペースに配置されます。このように、Oracle の読み取りと書き込みは互いに独立できるため、Oracle は広く賞賛されています。 SQL Server の読み取りと書き込みは相互にブロックされ、同時実行パフォーマンスを向上させるために、一部のクエリに nolock を追加して、読み取り中に書き込みを許可できますが、欠点は、コミットされていないダーティ データが読み取られる可能性があることです。 nolock を使用するには 3 つの原則があります。

(1) クエリ結果を「挿入、削除、変更」に使用する場合、nolockは付加できません！

(2) クエリされたテーブルはページ分割が頻繁に発生するテーブルなので、nolock の使用には注意してください。

(3) 一時テーブルを使用すると、Oracle の UNDO テーブルスペースのように機能する「以前のデータ」も保存できます。

同時実行パフォーマンスを向上させるために一時テーブルを使用できる場合は、nolock を使用しないでください。

nolock を追加した後、ページ分割が頻繁に発生するテーブルのクエリは読み取りがスキップされたり、繰り返し行われたりする傾向があります。

nolock を追加した後は、「挿入、削除、および変更」中にクエリを実行できますが、「挿入、削除、および変更」が行われるためです。場合によっては、データ ページがいっぱいになるとページ分割が避けられなくなり、この時点で nolock クエリが発生することがあります。たとえば、ページ 100 で読み取られたレコードがページに割り当てられる可能性があります。ページ分割により、ページ 101 が読み取られると、nolock クエリがページ 101 を読み取るときにデータを繰り返し読み取ることになり、「繰り返し読み取り」が発生する可能性があります。同様に、100 ページのデータが読み取られる前に 99 ページに分割された場合、nolock クエリでレコードが欠落し、「スキップ」が発生する可能性があります。

上記の友人は、nolock を追加した後の一部の操作でエラーを報告しました。nolock クエリによって繰り返し読み取りが発生したと推定されます。2 つの同一のレコードが他のテーブルに挿入された場合、当然、主キーの競合が発生します。

クラスター化インデックスはテーブルのシーケンス フィールドに基づいて構築されていないため、テーブルはページ分割が発生しやすくなります

たとえば、注文テーブルには注文番号 orderid と顧客番号 contactid があるため、どのフィールドにクラスター化インデックスを作成する必要があります。追加されるのか？このテーブルでは、順序番号が順番に追加され、クラスター化インデックスが orderid に追加されると、新しい行が最後に追加されるため、ページ分割が頻繁に発生しません。ただし、ほとんどのクエリは顧客番号に基づいているため、contactid にクラスター化インデックスを追加することのみが意味があります。注文テーブルの場合、contactid は連続フィールドではありません。

たとえば、「Zhang San」の「contactid」が 001 である場合、「Zhang San」が今日新しい注文をした場合、「Zhang San」の注文情報はこのテーブルの最初のデータ ページに配置される必要があります。注文情報はテーブルの最後のページには配置できず、最初のページに配置されます。最初のページが埋まっている場合はどうなりますか?申し訳ありませんが、このレコード用のスペースを確保するには、このテーブル内のすべてのデータを元に戻す必要があります。

SQL Server のインデックスは Oracle のインデックスとは異なります。SQL Server のクラスター化インデックスは、実際にはクラスター化インデックスのフィールドの順序でテーブルを並べ替えます。これは、Oracle のインデックス構成テーブルと同等です。 SQL Server のクラスター化インデックスはテーブル自体を組織化したものであるため、効率が非常に高くなります。このため、レコードが挿入されるとき、その位置はランダムに配置されるのではなく、データ ページ上に配置されるべき場所に配置されます。そのデータ ページにスペースがない場合、ページ分割が発生します。したがって、明らかに、クラスター化インデックスはテーブルの順次フィールドに基づいて構築されていないため、テーブルはページ分割が発生しやすくなります。

私はかつて、特定のテーブルのインデックスを再作成した後、友人の挿入効率が大幅に低下する状況に遭遇しました。おそらくこのような状況であると推測されます。テーブルのクラスター化インデックスは、テーブルの順次フィールドに基づいて構築されない場合があります。テーブルはアーカイブされることが多いため、テーブルのデータは疎な状態で存在します。たとえば、Zhang San は 20 件の注文を出しましたが、過去 3 か月間に 5 件の注文しかありませんでした。その後、Zhang San の過去 15 件の注文がアーカイブされ、15 件の空きが残る可能性があります。挿入に入力されます。発生したときに再利用されます。この場合、空き領域があるため、ページ分割は発生しません。ただし、クエリはデータのない空の位置をスキャンする必要があるため、クエリのパフォーマンスは比較的低くなります。

クラスター化インデックスの再構築後は状況が変わりました。クラスター化インデックスを再構築すると、元の空きがなくなり、ページの分割率が非常に高くなるためです。パフォーマンスが大幅に向上します。

クラスタードインデックスがシーケンシャルフィールドに構築されていないテーブルの場合、ページフィルレートを低くする必要がありますか?クラスター化インデックスの再構築を回避しますか?それは検討する価値のある質問です!

複合インデックスを使用して、複数の where 条件のクエリ速度を向上させます

通常、複合インデックスは単一のインデックスよりも選択性が優れています。また、特定の where 条件に特化して設定されたインデックスであり、ソートされているため、単一のインデックスよりもクエリ速度が速くなります。複合インデックスの先頭フィールドは、「選択性の高い」フィールドである必要があります。たとえば、日付、性別、年齢の 3 つのフィールドがあります。どのフィールドをガイド フィールドとして使用すればよいでしょうか?明らかに、「日付」を先頭フィールドとして使用する必要があります。日付は 3 つのフィールドの中で最も選択的です。

ここには例外があります。日付がクラスター化インデックスの先頭フィールドでもある場合、複合インデックスを構築してクラスター化インデックスを直接使用することはできません。これもより効率的です。

クラスター化インデックスを「複合インデックス」に構築しないでください。クラスター化インデックスは単純であればあるほど優れており、選択性が高いほど優れています。クラスター化インデックスには 2 つのフィールドが含まれており、許容可能です。ただし、フィールドが 3 つ以上ある場合は、クラスター化インデックスを主キーとして自動インクリメント フィールドを構築することを検討する必要があります。

あいまいクエリに like を使用する場合は、最初の % を使用しないように注意する必要があります

場合によっては、

Select * from contact where username like '%yue%'

keyword % などのあいまいクエリを実行する必要があることがあります。 yue% は、yue の前に「%」が使用されているため、クエリは完全なテーブル スキャンである必要があります。必要がない限り、キーワードの前に % を追加しないでください。

SQL Server テーブルを結合する 3 つの方法

(1) マージ。 Join

(2) ネストされたループ結合

(3) ハッシュ結合

SQL Server 2000 には、A の結果セットが小さい場合、デフォルトで外観として使用されます。 1 回スキャンした後、実際にスキャンされる行数は、A 結果セットの行数 x B 結果セットの行数と等しくなります。したがって、両方の結果セットが大きい場合、結合の結果は悪くなります。

SQL Server 2005 では、テーブル A とテーブル B の接続フィールドがたまたまクラスター化インデックスが配置されているフィールドである場合、テーブルの順序が両側で結合されるようになりました。この結合は A と同等です。テーブルの結果セットの行数は、テーブル B の結果セットの行数に加算されます。1 つは加算であり、もう 1 つは乗算であることがわかります。マージ結合は、ネストされたループ結合よりもはるかに優れています。

接続されたフィールドにインデックスがない場合、SQL2000 の効率は非常に低くなります。ただし、SQL2005 では、テーブル A と B の結果セットにインデックスを一時的に追加するのと同等のハッシュ結合が提供されます。そのため、SQL2005 の効率は高くなります。 SQL2000 よりも大幅に向上していることが重要な理由だと思います。

まとめると、テーブルを接続する際には次の点に注意する必要があります:

(1) クラスター化インデックスが配置されているフィールドを接続フィールドとして選択するようにしてください

(2) where 条件を慎重に考慮して、テーブル A と B の結果セットを削減します

(3) 多くの結合接続フィールドにインデックスがなく、まだ SQL2000 を使用している場合は、すぐにアップグレードしてください

Row_number によりテーブル スキャンが発生するため、一時テーブル ページング

を使用することをお勧めします。

ROW_Number ページング テストの結果:
ページングに ROW_Number を使用: CPU 時間 = 317265 ミリ秒、占有時間 = 423090 ミリ秒
ページングに一時テーブルを使用: CPU 時間 = 1266 ミリ秒、占有時間 = 6705 ミリ秒

ROW_Number の実装は次の順序に基づいています。 、クエリに対する並べ替えの影響は明らかです。

その他

一部の書き込みメソッドなどでは、インデックスの使用が制限されます

テーブル名から * を選択します。chgdate +7 < sysdate

Select * from tablename where chgdate < sysdate -7