SQL ステートメントの最適化の原則-PHPチュートリアル-php.cn

100 万レベルを超えるデータを処理する場合のクエリ速度を向上させる方法:

1. where 句で != または <> 演算子を使用しないようにしてください。そうしないと、エンジンがインデックスとフルテーブルスキャンを実行します。
2. クエリを最適化するには、まず、where と order by に関係する列にインデックスを作成することを検討してください。
3. where 句のフィールドで null 値の判断を行わないようにしてください。そうしないと、エンジンはインデックスの使用を断念し、次のようなテーブル全体のスキャンを実行します。 num は null です

num にデフォルト値 0 を設定して、テーブルの num 列に null 値がないことを確認し、次のようにクエリを実行できます:
select id from t where num=0
4. where 句を使用しないようにしてください。 or を使用して条件を接続します。そうしないと、エンジンはインデックスの使用を断念し、次のような完全なテーブルスキャンを実行します。
select id from t where num=10 または num=20

次のようなクエリを実行できます:
select id from t where num=10
Union all
select id from t where num=20
5. 次のクエリでも完全な結果が得られます。 table scan: (パーセント記号を接頭辞にすることはできません)
select id from t where name like '%abc%'

効率を向上させたい場合は、全文検索を検討できます。
6.in と not in も注意して使用する必要があります。そうでないと、次のような完全なテーブルスキャンが発生します。
select id from t where num in (1,2,3)
For連続値を使用できます。間には使用しないでください:
select id from t where num between 1 and 3

select xx、phone FROM send a JOIN (
select '13891030091' 電話ユニオン'13992085916'…………[Union Select '13619100234'）b

on a.ph/span> - 多くのデータが分離されている場合（ '13891030091'、 '13992085916 '、'1361 9100234'………… …)

7. where 句でパラメータが使用されている場合も、テーブル全体のスキャンが発生します。 SQL は実行時にのみローカル変数を解決するため、オプティマイザは実行時までアクセスプランの選択を延期できません。選択はコンパイル時に行う必要があります。ただし、アクセスプランがコンパイル時に構築される場合、変数の値はまだ不明であり、インデックス選択の入力として使用できません。たとえば、次のステートメントは完全なテーブルスキャンを実行します:
Select id from t where num=@num これを変更して、クエリでインデックスを使用するように強制できます:
select id from t with (index (index name)) where num=@num
8. where 句内のフィールドに対して式操作を実行しないようにしてください。これにより、エンジンがインデックスの使用を断念し、テーブル全体のスキャンが実行されます。例:
Select id from t where num/2=100
を次のように変更する必要があります:
select id from t where num=100*2

9. where でのペアリングを避けるようにしてください。句フィールドに対して関数操作が実行されるため、エンジンはインデックスの使用を断念し、テーブル全体のスキャンを実行します。例:
Select id from t where substring(name,1,3)='abc'–abc で始まる名前 ID
Select id from t where datediff(day,createdate,'2005-11-30' )=0–'2005-11-30' 生成された id
は次のように変更する必要があります:
select id from t where name like 'abc%'
select id from t where createdate>='2005- 11 -30′ and createdate<'2005-12-1′
10. where 句の「=」の左側で関数、算術演算、その他の式演算を実行しないでください。そうしないと、システムが実行できない可能性があります。インデックスを正しく使用するために。
11. インデックスフィールドを条件として使用する場合、インデックスが複合インデックスの場合、システムが確実にインデックスを使用するようにインデックスの最初のフィールドを条件として使用する必要があります。そうでない場合、インデックスは使用されません。 . が使用され、フィールドの順序はインデックスの順序とできる限り一致する必要があります。
12. 意味のないクエリを書かないでください。たとえば、空のテーブル構造を生成する必要がある場合:
selectcol1,col2 into #t from t where 1=0
このタイプのコードは実行しません。任意の結果セットを返しますが、システムリソースを消費するため、次のように変更する必要があります:
create table #t(...)
13. 多くの場合、in の代わりに、exists を使用するのが良い選択です。
select num from a where num in( select num from b)
次のステートメントに置き換えます:
select num from a where names(select 1 from b where num=a.num)
14.すべてのインデックスがクエリに有効であるわけではありません。SQL クエリの最適化はテーブル内のデータに基づいて行われます。たとえば、インデックス列に大量の重複データがある場合、SQL クエリはインデックスを使用しないことがあります。テーブル内のフィールドの性別は、男性と女性がほぼ同じであるため、性別に基づいてインデックスが構築されていても、クエリの効率には影響しません。 15. インデックスは多ければ多いほど良いのですが、インデックスは対応する選択の効率を向上させますが、挿入または更新中にインデックスが再構築される可能性があるため、インデックスの構築方法が必要になるため、挿入と更新の効率も低下します。慎重に検討させていただく場合がございます。テーブルに 6 つを超えるインデックスを持たないことをお勧めします。多すぎる場合は、一般的に使用されない一部の列にインデックスを構築する必要があるかどうかを検討する必要があります。
16. クラスター化インデックスのデータ列の順序は、テーブルレコードの物理的な格納順序であるため、列の値が変更されると、その列の順序が調整されるため、クラスター化インデックスのデータ列の更新はできるだけ避けてください。テーブル全体の記録には多額の費用がかかります。アプリケーションシステムがクラスター化インデックスのデータ列を頻繁に更新する必要がある場合は、インデックスをクラスター化インデックスとして構築する必要があるかどうかを検討する必要があります。
17. 数値フィールドを使用するようにしてください。数値情報のみを含むフィールドを文字フィールドとして設計しない場合は、クエリと接続のパフォーマンスが低下し、ストレージのオーバーヘッドが増加します。これは、エンジンがクエリや接続を処理するときに文字列内の各文字を 1 つずつ比較し、数値型の場合は 1 回の比較だけで十分であるためです。
18. char/nchar の代わりに varchar/nvarchar をできるだけ使用してください。第一に、可変長フィールドの記憶領域が小さいため、次に、クエリの検索効率が向上します。比較的小さなフィールドは明らかに高いです。
19. select * from t をどこでも使用せず、「*」を特定のフィールドリストに置き換え、未使用のフィールドを返さないでください。
20. 一時テーブルの代わりにテーブル変数を使用してみてください。テーブル変数に大量のデータが含まれている場合は、インデックスが非常に制限される (主キーインデックスのみ) ことに注意してください。
21. システムテーブルのリソースの消費を減らすために、一時テーブルの頻繁な作成と削除を避けてください。
22. 一時テーブルは使用できないわけではありません。一時テーブルを適切に使用すると、たとえば、大きなテーブルやよく使用されるテーブル内の特定のデータセットを繰り返し参照する必要がある場合など、特定のルーチンをより効率的に行うことができます。ただし、1 回限りのイベントの場合は、エクスポートテーブルを使用することをお勧めします。
23. 一時テーブルを作成するときに、一度に挿入されるデータの量が多い場合は、create table の代わりに select into を使用すると、データ量が増加しても大量のログが発生して速度が向上するのを避けることができます。システムテーブルのリソースを軽減するために、最初にテーブルを作成してから挿入する必要があります。
24. 一時テーブルを使用する場合は、ストアドプロシージャの最後にすべての一時テーブルを明示的に削除する必要があります。これにより、システムテーブルの長期ロックを回避できます。
25. カーソルによって操作されるデータが 10,000 行を超える場合は、カーソルの使用を避けるようにしてください。
26. カーソルベースの方法または一時テーブル方法を使用する前に、まずセットベースの解決策を探して問題を解決する必要があります。通常はセットベースの方法の方が効果的です。
27. 一時テーブルと同様に、カーソルは使用できないわけではありません。小規模なデータセットに FAST_FORWARD カーソルを使用することは、特に必要なデータを取得するために複数のテーブルを参照する必要がある場合、他の行ごとの処理方法よりも優れていることがよくあります。結果セットに「合計」を含むルーチンは、通常、カーソルを使用するよりも高速です。開発時間が許せば、カーソルベースの方法とセットベースの方法の両方を試して、どちらの方法がより効果的に機能するかを確認できます。
28. すべてのストアドプロシージャとトリガーの先頭で SET NOCOUNT ON を設定し、最後に SET NOCOUNT OFF を設定します。ストアドプロシージャとトリガーの各ステートメントの後に DONE_IN_PROC メッセージをクライアントに送信する必要はありません。
29. クライアントに大量のデータを返さないようにしてください。データの量が大きすぎる場合は、対応する要件が妥当であるかどうかを検討する必要があります。
30. 大規模なトランザクション操作を避け、システムの同時実行性を向上させるようにしてください。