MySQLクエリ最適化の詳しい説明

この記事では主に MySQL クエリの最適化について詳しく説明します。お役に立てれば幸いです。

インターネット上で一般的な 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 または num=20
次のようにクエリできます:
Select id from t where num =10
すべて結合
Select id from t where num=20
5. 次のクエリ また、完全なテーブル スキャンが行われます: (パーセント記号の前に置くことはできません)
Select id from t where name like '?c%'
効率を向上させるために、全文検索を検討できます。
6. in と not in も注意して使用する必要があります。そうしないと、次のような完全なテーブル スキャンが発生します。
Select id from t where num in(1,2,3)
連続値の場合、可能であれば間で使用し、内では使用しないでください:
Select id from t where num between 1 and 3
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' – name は 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 の代わりに存在することを使用するのが良い選択です:
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. できる限り /n の代わりに /n を使用してください。第一に、可変長フィールドの記憶域が小さいため、記憶域を節約できるからです。第二に、クエリの場合、比較的小さなフィールドでの検索効率が明らかに高くなります。より高い。
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 サイトの他の関連記事を参照してください。