数百万のアイテムに対するデータベースクエリの速度を向上させる方法

1. クエリを最適化するには、テーブル全体のスキャンを避けるようにする必要があります。まず、where および order by に関係する列にインデックスを作成することを検討してください。

2. where 句のフィールドで null 値の判定を行わないようにしてください。そうしないと、エンジンはインデックスの使用を放棄し、次のようなテーブル全体のスキャンを実行します。

select id from t where num is null

num のデフォルト値 0 を次のように設定できます。テーブル内の num 列に null 値がないことを確認してから、次のようにクエリします:

select id from t where num=0

3. where 句では != または 演算子を使用しないようにしてください。使用しないと、エンジンはインデックスの使用を放棄し、テーブル全体のスキャンを実行します。

4. 条件を接続するために where 句で または を使用することは避けてください。そうしないと、エンジンはインデックスの使用を断念し、次のようなテーブル全体のスキャンを実行します。

select id from t where num=10 or num=20

次のようにクエリできます:

select id from t where num=10
　　union all
　　select id from t where num=20

5. in と not in も注意して使用する必要があります。そうでないと、次のような完全なテーブル スキャンが発生します。

select id from t where num in(1,2,3)

連続値の場合、 between で使用できる場合は、 in では使用しないでください:

select id from t where num between 1 and 3

6. 次のクエリでもテーブル全体のスキャンが行われます:

select id from t where name like '%abc%'

効率を向上させるために、全文検索を検討できます。

7. where 句でパラメータが使用されている場合、テーブル全体のスキャンも発生します。 SQL は実行時にのみローカル変数を解決するため、オプティマイザは実行時までアクセス プランの選択を延期できません。選択はコンパイル時に行う必要があります。ただし、アクセス プランがコンパイル時に構築される場合、変数の値はまだ不明であり、インデックス選択の入力として使用できません。たとえば、次のステートメントはテーブル全体のスキャンを実行します:

select id from t where num=@num

クエリでインデックスを使用するように強制するように変更できます:

select id from t with(index(索引名)) 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'--name以abc开头的id
　　select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id

10. where 句の「=」の左側で関数、算術演算、その他の式演算を実行しないでください。実行すると、システムがインデックスを正しく使用できなくなる可能性があります。

11. インデックス フィールドを条件として使用する場合、インデックスが複合インデックスの場合は、システムがインデックスを使用することを保証するために、インデックスの最初のフィールドを条件として使用する必要があります。それ以外の場合、インデックスは使用されないため、使用する必要があります。可能な限り、フィールドの順序をインデックスの順序と一致させてください。

12. たとえば、空のテーブル構造を生成する必要がある場合は、次のような無意味なクエリを作成しないでください。 このタイプのコードは結果セットを返しませんが、システム リソースを消費します:

select id from t where name like 'abc%'
　　select id from t where createdate>=&#39;2005-11-30&#39; and createdate<&#39;2005-12-1&#39;

に変更する必要があります。 13. 多くの場合、in の代わりに存在を使用するのが良い選択です:

select col1,col2 into #t from t where 1=0

次のように置き換えます:

　create table #t(...)

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. 大量のデータをクライアントに返さないようにしてください。データの量が大きすぎる場合は、対応する要件が妥当であるかどうかを検討する必要があります。

【関連する推奨事項】

1. Mysql の無料ビデオチュートリアル

2. データベース設計の 5 つのよくある間違い

3. 既存のコンテンツ SQL ステートメントを維持しながら、後で MySQL にコンテンツを追加する方法

4 . MySQLでよく使うSQL文の書き方をまとめました

5. MySQLを使って乱数を生成したり文字列を接続したりする方法を詳しく解説しています

以上が数百万のアイテムに対するデータベースクエリの速度を向上させる方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。