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

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

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

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

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

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

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

#SQL ステートメントの統一された記述方法

select*from dual 
select*From dual

SQL 文はあまり複雑に書かないでください

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

select*from orderheader where changetime >'2010-10-20 00:00:01' 
select*from orderheader where changetime >'2010-09-22 00:00:01'

select*from orderheader where changetime >@chgtime

バインド変数ピーク

begin tran は必要な場合にのみ使用してください

場合によっては、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的查询正在发生，比如在第100页已经读过的记录，可能会因为页分裂而分到第101页，这有可能使得nolock查询在读101页时重复读到该条数据，产生“重复读”。同理，如果在100页上的数据还没被读到就分到99页去了，那nolock查询有可能会漏过该记录，产生“跳读”。

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

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