SQL 最適化に一般的に使用される方法は何ですか?
- 青灯夜游オリジナル
- 2020-08-24 13:12:388303ブラウズ
SQL 最適化に一般的に使用される方法は次のとおりです: 1. テーブル全体のスキャンを回避し、where および order by に関係する列にインデックスを確立することを検討します; 2. フィールドの null 値を回避するようにしてください。 where 句の判断; 3. in と not in の使用には注意が必要; 4. 大規模なトランザクション操作を避け、システムの同時実行性を向上させるようにしてください。
1. SQL を最適化する必要がある理由
開発プロジェクトの初期の段階では、 SQL の量が比較的少なく、一部の SQL の実行効率がプログラムの実行効率に与える影響が明らかではなく、開発担当者や運用保守担当者が SQL の実行効率がどの程度効率的であるかを判断できないそのため、SQL の特別な最適化が行われることはほとんどなく、その結果、SQL の最適化が行われることはほとんどありません 時間が経過し、業務データの量が増加するにつれて、SQL の実行効率がプログラムの実行効率に徐々に影響を及ぼしますこのときSQLの最適化が必要です。
2. SQL 最適化の一般的な方法
1. クエリを最適化するには、テーブル全体のスキャンを避けるようにしてください。まず、場所と順序を考慮する必要があります。 by. 列にインデックスを作成します。
2. where 句内のフィールドで null 値の判断を行わないようにしてください。そうしないと、エンジンはインデックスの使用を断念し、次のようなテーブル全体のスキャンを実行します。 num に設定します。デフォルト値は 0 です。テーブルの num 列に null 値がないことを確認してから、
select id from t where num is null
3 のようにクエリを実行します。!= または <> の使用は避けてください。 where 句に ; 演算子を使用しない場合、エンジンは破棄されます。インデックスを作成し、テーブル全体のスキャンを実行します。
4. 条件を接続するために where 句で または を使用することは避けてください。そうしないと、エンジンはインデックスの使用を断念し、次のようなテーブル全体のスキャンを実行します。
select id from t where num=0
クエリを実行できます。次のように:
select id from t where num=10 or num=20
5.in と not in も注意して使用する必要があります。そうしないと、次のようなテーブル全体のスキャンが発生します。連続値を間で使用できる場合は、in で使用しないでください:
select id from t where num=10 union all select id from t where num=20
6。次のクエリでもテーブル全体のスキャンが行われます:
select id from t where num in(1,2,3)
7。試してみてください。 where 句内のフィールドに対する式操作を避けるため、エンジンはインデックスの使用を断念し、テーブル全体のスキャンを実行します。例:
select id from t where num between 1 and 3
を
select id from t where name like '%abc%'
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 name like 'abc%'12. 多くの場合、in の代わりに存在することを使用することをお勧めします:
select col1,col2 into #t from t where 1=0次のステートメントに置き換えます:
create table #t(...)13. すべてのインデックスではありません すべてのクエリに有効です。SQL はテーブル内のデータに基づいてクエリを最適化します。インデックス列に大量の重複データがある場合、SQL クエリはインデックスを使用しないことがあります。たとえば、テーブルに性別フィールドがある場合、そのほぼ半分が男性、半分が女性であるため、インデックスが性別に基づいて構築されたとしても、クエリの効率には影響しません。 14. インデックスは多いほど良いです。インデックスによって対応する選択の効率は向上しますが、挿入と更新の効率も低下します。挿入または更新が可能であるため、 update インデックスは再構築されるため、インデックスの構築方法は特定の状況に応じて慎重に検討する必要があります。 テーブルに 6 つを超えるインデックスを持たないことが最善です。多すぎる場合は、一般的に使用されない一部の列にインデックスを構築する必要があるかどうかを検討する必要があります。 15. 数値フィールドを使用するようにしてください。フィールドに数値情報のみが含まれる場合は、文字フィールドとして設計しないようにしてください。これにより、クエリと接続のパフォーマンスが低下し、ストレージのオーバーヘッドが増加します。 これは、エンジンがクエリと接続を処理するときに文字列内の各文字を 1 つずつ比較し、数値型の場合は 1 回の比較だけで十分であるためです。 16. まず、可変長フィールドの記憶域スペースが小さく、記憶域スペースを節約できるため、できるだけ char ではなく varchar を使用してください。小さなフィールドの方が明らかに検索効率が高くなります。 17. select * from t をどこでも使用せず、「*」を特定のフィールド リストに置き換え、未使用のフィールドを返さないでください。 18. システム テーブル リソースの消費を減らすために、一時テーブルの頻繁な作成と削除を避けてください。
19. 一時テーブルは使用できないわけではなく、たとえば、大きなテーブルやよく使用されるテーブル内の特定のデータ セットを繰り返し参照する必要がある場合など、一時テーブルを適切に使用すると、特定のルーチンの効率が向上します。ただし、1 回限りのイベントの場合は、エクスポート テーブルを使用することをお勧めします。
20. 一時テーブルを作成するときに、一度に挿入されるデータの量が多い場合は、create table の代わりに select into を使用して、大量のログの発生を避けることができます。
速度を向上させるため; データ量が大きくない場合 システム テーブルのリソースを軽減するには、まずテーブルを作成してから挿入する必要があります。
21. 一時テーブルを使用する場合は、ストアド プロシージャの最後にすべての一時テーブルを明示的に削除し、最初にテーブルを切り詰めてからテーブルを削除する必要があります。これにより、システム テーブルの長期ロックを回避できます。
22. カーソルは効率が低いため、カーソルの使用は避けてください。カーソルによって操作されるデータが 10,000 行を超える場合は、再書き込みを検討する必要があります。
23. カーソルベースの方法または一時テーブル方法を使用する前に、まず問題を解決するためのセットベースのソリューションを探す必要があります。通常はセットベースの方法の方が効果的です。
24. 一時テーブルと同様に、カーソルは使用できないわけではありません。小規模なデータ セットで FAST_FORWARD カーソルを使用することは、特に必要なデータを取得するために複数のテーブルを参照する必要がある場合、他の行ごとの処理方法よりも優れていることがよくあります。
結果セットに「合計」を含むルーチンは、通常、カーソルを使用するより高速です。開発時間が許せば、カーソルベースの方法とセットベースの方法の両方を試して、どちらの方法がより効果的に機能するかを確認できます。
25. 大規模なトランザクション操作を避け、システムの同時実行性を向上させるようにしてください。
26. クライアントに大量のデータを返さないようにしてください。データの量が大きすぎる場合は、対応する要件が妥当であるかどうかを検討する必要があります。
関連する推奨事項: 「PHP チュートリアル 」、「mysql チュートリアル 」
以上がSQL 最適化に一般的に使用される方法は何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。