mysql が多くのデータを処理する場合に最適化されたクエリ速度をいくつかまとめます。-mysql チュートリアル-php.cn

最近、仕事の都合上、Mysqlデータベースのselectクエリ文の関連最適化方法に注目するようになりました

実際に参加したプロジェクトの結果、 MySQL テーブルのデータ量が 100 万に達すると、通常の SQL クエリの効率は急激に低下し、クエリ条件が多数ある場合、クエリ速度は耐えられなくなります。以前、400 万件を超えるレコード (インデックス付き) を含むテーブルで条件付きクエリをテストしたことがありますが、そのときのクエリ時間は 40 秒にも達しました。これほど長いクエリ遅延は、ユーザーを驚かせると思います。したがって、SQL文クエリの効率をいかに向上させるかが非常に重要です。以下に、インターネット上で広く流通している 30 の SQL クエリステートメントの最適化方法を示します。

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

2. クエリを最適化するには、まず、where と order by に関係する列にインデックスを作成することを検討してください。

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

select id from t where num is null

ログイン後にコピー

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

select id from t where num=0

ログイン後にコピー

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. 次のクエリも完全なテーブルスキャンになります: (パーセント記号の前に置くことはできません)

select id from t where name like ‘�c%&#39;

ログイン後にコピー

効率を高めるには、全文検索を検討してください。

6. 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

ログイン後にコピー

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

select id from t where num=@num

ログイン後にコピー

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

select id from t with(index(索引名)) where num=@num

ログイン後にコピー

8。エンジンがインデックスの使用を断念し、テーブル全体のスキャンを実行します。例:

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)=&#39;abc&#39;–name以abc开头的id
select id from t where datediff(day,createdate,&#39;2005-11-30′)=0–&#39;2005-11-30′生成的id
应改为:
select id from t where name like ‘abc%&#39;
select id from t where createdate>=&#39;2005-11-30′ and createdate<&#39;2005-12-1′

ログイン後にコピー

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

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

12. たとえば、空のテーブル構造を生成する必要がある場合:
selectcol1,col2 into #t from t where 1=0
このタイプのコードは結果セットを返しません。ただし、システムリソースを消費しますので、次のように変更する必要があります:

create table #t(…)

ログイン後にコピー

13. 多くの場合、SQL 最適化ではすべてのインデックスが有効であるわけではありません。はい、インデックス列に大量の重複データがある場合、SQL クエリはインデックスを使用しない可能性があります。たとえば、テーブルに性別フィールドが存在する場合、その半分はインデックスを使用しません。男性と女性が半分の場合、性別に基づいてインデックスが構築されたとしても、クエリの効率には影響しません。

15. インデックスは多いほど良いですが、インデックスは対応する選択の効率を向上させますが、挿入または更新中にインデックスが再構築される可能性があるため、注意が必要です。インデックスの構築方法はケースバイケースで検討されます。テーブルに 6 つを超えるインデックスを持たないことをお勧めします。多すぎる場合は、一般的に使用されない一部の列にインデックスを構築する必要があるかどうかを検討する必要があります。

16. クラスター化インデックスデータ列の順序は、テーブルレコードの物理的な格納順序であるため、列の値が変更されると、その列の順序が調整されます。テーブル全体のレコードを作成すると、多くの時間がかかります。アプリケーションシステムがクラスター化インデックスのデータ列を頻繁に更新する必要がある場合は、インデックスをクラスター化インデックスとして構築する必要があるかどうかを検討する必要があります。

17. フィールドに数値情報のみが含まれる場合は、クエリと接続のパフォーマンスが低下し、ストレージのオーバーヘッドが増加するように設計しないでください。これは、エンジンがクエリや接続を処理するときに文字列内の各文字を 1 つずつ比較し、数値型の場合は 1 回の比較だけで十分であるためです。

18. char/nchar の代わりに varchar/nvarchar をできるだけ使用してください。第 1 に、可変長フィールドの記憶領域が小さいため、クエリの場合、比較的小さなフィールドでの検索効率が向上します。明らかに高いです。

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. 大規模なトランザクション操作を避け、システムの同時実行性を向上させてください。

以上がmysql が多くのデータを処理する場合に最適化されたクエリ速度をいくつかまとめます。の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。