高パフォーマンスSQLの書き方

まず第一に、実行計画とは何なのかを理解する必要があります。

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

実行計画は固定されたものではなく、「個別化」されていることがわかります。正しい「実行計画」を生成するには、次の 2 つの重要なポイントがあります。

SQL ステートメントは、クエリ オプティマイザーに何をしたいのかを明確に伝えていますか?

クエリ オプティマイザーによって取得されたデータベース統計は最新かつ正確ですか?

推奨コース: MySQL チュートリアル 。

#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 ステートメントを簡素化する重要な方法は、一時テーブルを使用して中間結果を一時的に保存することです。ただし、一時テーブルの利点はこれらをはるかに超えています。一時的な結果は一時テーブルに一時的に保存され、後続のクエリは tempdb に保存されます。これにより、プログラム内のメイン テーブルの複数回のスキャンが回避され、「」も大幅に削減されます。 「共有ロック」により、プログラム実行中にブロックされます。「更新ロック」により、ブロックが軽減され、同時実行パフォーマンスが向上します。

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 where changetime >@chgtime

@chgtime 変数を使用すると、任意の値を渡すことができるため、多数の同様のクエリで実行プランを再利用でき、SQL ステートメントを解析する負担を大幅に軽減できます。データベース。データベースの効率を向上させるには、一度解析して複数回再利用することが原則です。

バインド変数ピーク

すべてのものには 2 つの側面があり、バインド変数はほとんどの 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 では、Oracle の構造がより合理的であり、「前のデータ」を保存するための UNDO テーブルスペースがあるためです。変更中にデータがコミットされていない場合、読み取られるのは変更前のコピーです。コピーは UNDO 表スペースに配置されます。このように、Oracle の読み取りと書き込みは互いに独立できるため、Oracle は広く賞賛されています。 SQL Server の読み取りと書き込みは相互にブロックされます。同時実行パフォーマンスを向上させるために、一部のクエリに nolock を追加して、読み取り中に書き込みを許可できます。ただし、欠点は、コミットされていないダーティ データが読み取られる可能性があることです。 nolock を使用するには 3 つの原則があります。

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

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

(3) 一時テーブルを使用すると、Oracle の UNDO テーブルスペースと同様の機能を持つ「データのフォアシャドウ」を保存することもできます。 nolock は使用しないでください。

クラスタード インデックスはテーブルのシーケンス フィールドに基づいて構築されていないため、テーブルがページ分割される傾向があります。

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

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

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

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

クラスター化インデックスの再構築後は状況が変わりました。クラスター化インデックスの再構築はテーブル内のデータの再配置を意味するためです。元の空席はなくなり、ページの充填率は非常に高くなります。データを挿入するときは、ページ分割が頻繁に発生するため、パフォーマンスが大幅に低下します。

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

nolock を追加した後、ページ分割が頻繁に発生するテーブルにクエリを実行すると、読み取りのスキップや繰り返しが発生しやすくなる可能性があります

加nolock后可以在“插、删、改”的同时进行查询，但是由于同时发生“插、删、改”，在某些情况下，一旦该数据页满了，那么页分裂不可避免，而此时nolock的查询正在发生，比如在第100页已经读过的记录，可能会因为页分裂而分到第101页，这有可能使得nolock查询在读101页时重复读到该条数据，产生“重复读”。同理，如果在100页上的数据还没被读到就分到99页去了，那nolock查询有可能会漏过该记录，产生“跳读”。

上面提到的哥们，在加了nolock后一些操作出现报错，估计有可能因为nolock查询产生了重复读，2条相同的记录去插入别的表，当然会发生主键冲突。

使用like进行模糊查询时应注意

有的时候会需要进行一些模糊查询比如

select*from contact where username like ‘%yue%’

关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%，

数据类型的隐式转换对查询效率的影响

sql server2000的数据库，我们的程序在提交sql语句的时候，没有使用强类型提交这个字段的值，由sql server 2000自动转换数据类型，会导致传入的参数与主键字段类型不一致，这个时候sql server 2000可能就会使用全表扫描。Sql2005上没有发现这种问题，但是还是应该注意一下。

以上が高パフォーマンスSQLの書き方の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。