PHP を使用して 100 万レベル以下のデータを処理し、クエリ速度を向上させる方法

PHP を使用して数百万のデータを処理し、クエリ速度を向上させる方法

PHP を使用して 100 万レベルを超えるデータを処理し、クエリ速度を向上させる方法:
1. where 句で != または <> 演算子を使用しないようにしてください。使用しないと、エンジンはインデックスの使用を放棄し、テーブル全体のスキャンを実行します。
2. クエリを最適化するには、テーブル全体のスキャンを回避するように努める必要があります。まず、where および order by に関係する列にインデックスを作成することを検討する必要があります。
3. where 句内のフィールドの null 値を判断しないようにしてください。そうしないと、エンジンはインデックスの使用を断念し、次のようなテーブル全体のスキャンを実行します。
t から ID を選択します (num は null)
num にデフォルト値 0 を設定し、テーブルの num 列に null 値がないことを確認してから、次のようにクエリを実行できます。
num=0 の t から ID を選択します
4. 条件を接続するために where 句で または を使用しないようにしてください。そうしないと、エンジンはインデックスの使用を断念し、次のようなテーブル全体のスキャンを実行します。
num=10 または num=20 の t から ID を選択します
次のようにクエリできます。
num=10 の t から ID を選択します
すべてを結合する
num=20 の t から ID を選択します
5. 次のクエリでもテーブル全体のスキャンが行われます。
'%abc%' のような名前から ID を選択します
効率を向上させるには、全文検索を検討してください。
6. in と not in も注意して使用する必要があります。そうしないと、次のような完全なテーブル スキャンが発生します。
select id from t where num in(1,2,3)
連続値の場合、次の間で使用できる場合は in を使用しないでください。
select id from t where num は 1 ～ 3
7.どこにいたら 句内でパラメータを使用すると、テーブル全体がスキャンされます。 SQL は実行時にのみローカル変数を解決するため、オプティマイザは実行時までアクセス プランの選択を延期できません。選択はコンパイル時に行う必要があります。しかし ただし、アクセス プランがコンパイル時に構築される場合、変数の値はまだ不明であり、インデックス選択の入力として使用できません。次のステートメントはテーブル全体のスキャンを実行します。
t から ID を選択します (num=@num)
代わりに、クエリでインデックスを使用するように強制できます。
select id from t with(index(インデックス名)) where num=@num
8. where 句内のフィールドに対して式操作を実行しないようにしてください。これにより、エンジンがインデックスの使用を断念し、テーブル全体のスキャンが実行されます。のように：
num/2=100 の t から ID を選択します
次のように変更する必要があります。
t から ID を選択 (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
次のように変更する必要があります。
「abc%」のような名前の t から ID を選択します
select id from t where createdate>='2005-11-30' and createdate 10. where 句の「=」の左側で関数、算術演算、またはその他の式演算を実行しないでください。実行しないと、システムがインデックスを正しく使用できない可能性があります。
11. インデックス フィールドを条件として使用する場合、インデックスが複合インデックスの場合、インデックスの最初のフィールドを条件として使用して、システムが確実にインデックスを使用するようにする必要があります。そうでない場合、インデックスは使用されないため、次のようにする必要があります。可能な限り、フィールドの順序をインデックスの順序と一致させてください。
12. 空のテーブル構造を生成するなど、無意味なクエリを作成しないでください。
selectcol1,col2 into #t from t (1=0)
このタイプのコードは結果セットを返しませんが、システム リソースを消費しますので、次のように変更する必要があります。
テーブル #t(...) を作成します
13. 多くの場合、in の代わりに存在を使用するのが良い選択です。
select num from a where num in(select num from b)
次のステートメントに置き換えます。
存在する a から num を選択 (num=a.num の b から 1 を選択)
14. すべてのインデックスがクエリに有効であるわけではありません。SQL はテーブル内のデータに基づいてクエリを最適化します。インデックス列に大量の重複データがある場合、フィールドを含むテーブルなどの SQL クエリはインデックスを使用しないことがあります。性別、男性、女性はほぼ半々であるため、性別に基づいてインデックスを構築したとしても、クエリの効率には影響しません。
15. インデックスは多ければ多いほど良いのですが、インデックスは対応する選択の効率を向上させますが、次の理由により挿入と更新の効率も低下します。 挿入または更新 インデックスは時々再構築する必要があるため、特定の状況に応じてインデックスを構築する方法を慎重に検討する必要があります。テーブルに 6 つを超えるインデックスを持たないことをお勧めします。多すぎる場合は、一般的に使用されない一部の列にインデックスが構築されていないかどうかを考慮する必要があります。 必要。
16. クラスター化されたインデックス データ列の更新はできる限り避けてください。 インデックス データ列の順序は、テーブル レコードの物理的な格納順序です。列の値が変更されると、テーブル レコード全体の順序が調整され、大量のリソースが消費されます。アプリケーション システムを頻繁に更新する必要がある場合 クラスター化インデックス データ列を使用する場合は、インデックスをクラスター化インデックスとして構築するかどうかを検討する必要があります。
17. フィールドに数値情報のみが含まれる場合は、クエリと接続のパフォーマンスが低下し、ストレージのオーバーヘッドが増加するように設計しないでください。これは、エンジンがクエリや接続を処理するときに文字列内の各文字を 1 つずつ比較し、数値型の場合は 1 回の比較だけで十分であるためです。
18. できる限り char/nchar ではなく varchar/nvarchar を使用してください。第 1 に、可変長フィールドの記憶領域が小さいため、記憶領域を節約できます。第 2 に、クエリの場合、比較的小さなフィールドでの検索効率が高くなります。明らかに高い。
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. 大規模なトランザクション操作を避け、システムの同時実行性を向上させるようにしてください。