実行プランは、SQL ステートメントと関連テーブルの統計情報に基づいてデータベースによって作成されるクエリ プランで、たとえば、SQL ステートメントを使用して 1 つのレコードをクエリする場合、このプランはクエリ オプティマイザーによって自動的に分析および生成されます。 100,000 個のレコードがあるテーブルの場合、クエリ オプティマイザーは「インデックス検索」方式を選択します。テーブルがアーカイブされていて、現在 5,000 個のレコードしか残っていない場合、クエリ オプティマイザーは計画を変更して「フル テーブル スキャン」方式を使用します。 。
実行計画は固定されたものではなく、「個別化」されていることがわかります。正しい「実行計画」を作成するには、次の 2 つの重要なポイントがあります:
(1) SQL ステートメントはクエリ オプティマイザーに何をしたいのかを明確に伝えていますか?
(2) クエリオプティマイザが取得したデータベースの統計情報は最新かつ正確ですか?
次の 2 つの SQL ステートメントについて、プログラマはそれらが同じであると考えますが、データベース クエリ オプティマイザーはそれらが異なるものであると考えます。
りー実際、ケースが異なる場合、クエリ アナライザーはそれを 2 つの異なる SQL ステートメントとみなして、2 回解析する必要があります。 2 つの実行プランを生成します。したがって、プログラマは、同じクエリ ステートメントがどこでも一貫していることを確認する必要があります。スペースが 1 つ多くても機能しません。
データベースからキャプチャした SQL ステートメントを印刷すると、A4 用紙 2 枚ほどの長さになることがよくあります。一般に、このような複雑なステートメントには通常問題が発生します。この 2 ページにわたる SQL ステートメントを元の作成者に問い合わせましたが、時間がかかりすぎてしばらく理解できないと言われました。オリジナルの作成者ですら SQL ステートメントに混乱する可能性があり、データベースも同様に混乱することが考えられます。
一般に、Select ステートメントの結果はサブセットとして使用され、そのサブセットからクエリが実行されます。この種の 1 レベルのネストされたステートメントは比較的一般的ですが、経験によれば、ネストのレベルが 3 を超える場合は発生します。 、クエリ オプティマイザーは簡単に間違った実行プランを与えてしまいます。唖然としたからだ。人工知能のようなものは、究極的には人間の解像度よりも劣ります。人間がめまいを感じているなら、データベースも同様にめまいを感じることは間違いありません。
また、実行計画は再利用可能であり、SQL文が単純であればあるほど再利用できる可能性が高くなります。複雑な SQL ステートメントで 1 文字が変更されると、その文字を再解析する必要があり、大量のゴミがメモリに詰め込まれることになります。データベースがどれほど非効率になるかは考えられます。
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 ステートメントを解析する負担を大幅に軽減できます。データベースの効率を向上させるには、一度解析して複数回再利用することが原則です。
すべてには 2 つの側面があり、バインド変数はほとんどの OLTP プロセスに適用できますが、例外もあります。たとえば、where 条件のフィールドが「スキューフィールド」である場合です。
「傾斜フィールド」とは、列内の値のほとんどが同じであることを意味します。たとえば、国勢調査表の「民族」列では、値の 90% 以上が漢民族です。したがって、SQL ステートメントで 30 歳の漢民族の人口をクエリする場合は、「ethnic」列を where 条件に配置する必要があります。このとき、バインド変数 @nation を使用すると大きな問題が発生します。
@nation によって渡された最初の値が「Han」である場合、実行プラン全体で必然的にテーブル スキャンが選択されることを想像してください。次に、渡される 2 番目の値は「Buyi」です。当然のことながら、「Buyi」の割合は 1 万分の 1 にすぎないため、インデックス検索を使用する必要があります。ただし、最初に解析された「Han」の実行プランは再利用されるため、2回目でもテーブルスキャン方式が使用されます。この問題は有名な「バインド変数スヌーピング」です。「スキューフィールド」にはバインド変数を使用しないことをお勧めします。
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 Server の同時実行パフォーマンスを向上させるための重要な手段です。Oracle には、より合理的な構造があり、データを保存するための UNDO テーブル スペースがあるため、これは必要ありません。変更済み まだコミットされていないため、読み取られるのは変更前のコピーであり、UNDO 表スペースに配置されます。このように、Oracle の読み取りと書き込みは互いに独立できるため、Oracle は広く賞賛されています。 SQL Server の読み取りと書き込みは相互にブロックされ、同時実行パフォーマンスを向上させるために、一部のクエリに nolock を追加して読み取り中に書き込みを許可できます。ただし、欠点は、コミットされていないダーティ データが読み取られる可能性があることです。 nolock を使用するには 3 つの原則があります。
(1) クエリ結果を「挿入、削除、変更」に使用する場合、nolock は付加できません。
(2) クエリされたテーブルはページ分割が頻繁に発生するテーブルであるため、nolock の使用には注意してください。
(3) 一時テーブルを使用すると、Oracle の UNDO テーブルスペース
に似た機能を持つ「データのフォアシャドウ」も保存できます。 一時テーブルを使用して同時実行パフォーマンスを向上できる場合は、nolock を使用しないでください。
たとえば、注文テーブルには注文番号 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 件の空きが残る可能性があります。挿入に入力されます。発生したときに再利用されます。この場合、空き領域があるため、ページ分割は発生しません。ただし、クエリはデータのない空の位置をスキャンする必要があるため、クエリのパフォーマンスは比較的低くなります。
クラスター化インデックスを再構築すると状況が変わりました。クラスター化インデックスを再構築すると、元の空きスペースがなくなり、ページの分割率が非常に高くなるため、パフォーマンスが低下します。大幅に低下します。
クラスター化インデックスがシーケンシャル フィールドに構築されていないテーブルの場合、ページ フィル レートを低くする必要がありますか?クラスター化インデックスの再構築を回避しますか?それは検討する価値のある質問です!
加nolock后可以在“插、删、改”的同时进行查询,但是由于同时发生“插、删、改”,在某些情况下,一旦该数据页满了,那么页分裂不可避免,而此时nolock的查询正在发生,比如在第100页已经读过的记录,可能会因为页分裂而分到第101页,这有可能使得nolock查询在读101页时重复读到该条数据,产生“重复读”。同理,如果在100页上的数据还没被读到就分到99页去了,那nolock查询有可能会漏过该记录,产生“跳读”。
上面提到的哥们,在加了nolock后一些操作出现报错,估计有可能因为nolock查询产生了重复读,2条相同的记录去插入别的表,当然会发生主键冲突。
有的时候会需要进行一些模糊查询比如
select*from contact where username like ‘%yue%’
关键词%yue%,由于yue前面用到了“%”,因此该查询必然走全表扫描,除非必要,否则不要在关键词前加%,
sql server2000的数据库,我们的程序在提交sql语句的时候,没有使用强类型提交这个字段的值,由sql server 2000自动转换数据类型,会导致传入的参数与主键字段类型不一致,这个时候sql server 2000可能就会使用全表扫描。Sql2005上没有发现这种问题,但是还是应该注意一下。
(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 クエリを作成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。