実行プランは、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 サイトの他の関連記事を参照してください。
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
