MySQL数千万の大規模データに対する30のSQLクエリ最適化テクニックの詳細な説明

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 句で条件を接続するために or を使用しないでください。そうしないと、エンジンはインデックスの使用を中止し、テーブル全体をスキャンします。たとえば、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 は in ではなく注意して使用してください。そうしないと、次のようにテーブル全体のスキャンが発生します。select id from t where num in(1,2,3) 連続する値の場合は、in ではなく between を使用します。select id from t where num between 1 and 3

6. 次のクエリでも、テーブル全体がスキャンされます: select id from t where name like '%李%' 効率を向上させるには、フルテキスト検索を検討してください。

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' の場合、名前が abc で始まる ID は次のように変更されます。
'abc%' のような名前の t から id を選択

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

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

12. 空のテーブル構造を生成するような意味のないクエリを書かないでください: select col1,col2 into #t from t where 1=0
このタイプのコードは結果セットを返さず、システム リソースを消費します。次のように変更する必要があります。
テーブル #t を作成します(…)

13. 多くの場合、in の代わりに exists を使用することをお勧めします: 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 を使用してください。その理由は、第一に、可変長フィールドは使用するストレージ スペースが少なく、ストレージ スペースを節約できるためです。第二に、クエリの場合、比較的小さなフィールドで検索する方が明らかに効率的です。

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

プログラムがこれらの 30 の要件を満たすことができれば、プログラムの実行効率が大幅に向上します。