SQL クエリの最適化についての深い理解 - 高パフォーマンスの SQL ステートメントの作成方法に関する具体的な分析

SQL クエリの最適化についての深い理解 - 高パフォーマンスの SQL ステートメントの作成方法の具体的な分析:

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

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

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

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

(2) クエリオプティマイザが取得したデータベースの統計情報は最新かつ正確ですか?

2. SQL文の書き方を統一する

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

りー

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

3. 複雑すぎる SQL ステートメントを作成しないでください

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

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

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

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

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

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

select*from dual 

select*From dual

上記の 2 つのステートメントは、クエリ オプティマイザーによって異なる SQL ステートメントとみなされ、2 回解析する必要があります。バインド変数を使用する場合

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

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

6. 境界変数の覗き見

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

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

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

7. 必要な場合にのみ 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 は必ずしも使用されるわけではありません。

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

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

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

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

(3) 一時テーブルを使用すると、Oracle の UNDO テーブルスペース

に似た機能を持つ「データのフォアシャドウ」も保存できます。 一時テーブルを使用して同時実行パフォーマンスを向上できる場合は、nolock を使用しないでください。

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

たとえば、注文テーブルには注文番号 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 件の空きが残る可能性があります。挿入に入力されます。発生したときに再利用されます。この場合、空き領域があるため、ページ分割は発生しません。ただし、クエリはデータのない空の位置をスキャンする必要があるため、クエリのパフォーマンスは比較的低くなります。

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

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

10、加nolock后查询经常发生页分裂的表，容易产生跳读或重复读

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

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

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

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

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

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

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

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

13、SQL Server 表连接的三种方式

(1) Merge Join

(2) Nested Loop Join

(3) Hash Join

SQL Server 2000只有一种join方式——Nested Loop Join，如果A结果集较小，那就默认作为外表，A中每条记录都要去B中扫描一遍，实际扫过的行数相当于A结果集行数x B结果集行数。所以如果两个结果集都很大，那Join的结果很糟糕。

SQL Server 2005新增了Merge Join，如果A表和B表的连接字段正好是聚集索引所在字段，那么表的顺序已经排好，只要两边拼上去就行了，这种join的开销相当于A表的结果集行数加上B表的结果集行数，一个是加，一个是乘，可见merge join 的效果要比Nested Loop Join好多了。

如果连接的字段上没有索引，那SQL2000的效率是相当低的，而SQL2005提供了Hash join，相当于临时给A，B表的结果集加上索引，因此SQL2005的效率比SQL2000有很大提高，我认为，这是一个重要的原因。

总结一下，在表连接时要注意以下几点：

(1)    连接字段尽量选择聚集索引所在的字段

(2)    仔细考虑where条件，尽量减小A、B表的结果集

(3)    如果很多join的连接字段都缺少索引，而你还在用SQL Server 2000，赶紧升级吧。

以上がSQL クエリの最適化についての深い理解 - 高パフォーマンスの SQL ステートメントの作成方法に関する具体的な分析の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。