MySQLが大量のデータを処理する際にクエリ速度を最適化するいくつかの方法

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

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

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

3. where 句のフィールドで null 値の判定を使用しないでください。そうしないと、エンジンはインデックスの使用を放棄し、次のように完全なテーブル スキャンを実行します。
num が null の t から id を選択
num にデフォルト値 0 を設定して、テーブルの num 列に null 値がないことを確認し、次のようにクエリを実行できます。
num=0 の t から id を選択

4. where 句で条件を接続するために または の使用を避けるようにしてください。そうしないと、エンジンはインデックスの使用を中止し、次のようにテーブル全体のスキャンを実行します。
num=10 または num=20 の t から id を選択
次のようにクエリを実行できます。
num=10 の t から id を選択
すべて結合
num=20 の t から id を選択

5. 次のクエリでもテーブル全体のスキャンが実行されます: (先頭のパーセント記号なし)
'�c%' のような名前の t から id を選択
効率を向上させるには、全文検索を検討してください。

6. in は慎重に使用し、in は使用しないでください。そうしないと、次のような完全なテーブル スキャンが発生します。
t から id を選択、num は(1,2,3)
連続値の場合は、in の代わりに between を使用します。
num が 1 から 3 の間の t から id を選択

7. where 句でパラメータが使用されている場合は、テーブル全体のスキャンも実行されます。 SQL は実行時にのみローカル変数を解決するため、オプティマイザーはアクセス プランの選択を実行時まで延期することはできず、コンパイル時に選択を行う必要があります。ただし、アクセス プランがコンパイル時に構築される場合、変数の値は不明のままであり、インデックス選択の入力として使用することはできません。次のステートメントは完全なテーブルスキャンを実行します。
num=@num の t から id を選択
代わりにクエリでインデックスを使用するように強制することもできます。
select id from t with(index(インデックス名)) where num=@num

8. where 句内のフィールドに対する式操作は避けてください。これにより、エンジンはインデックスの使用を中止し、テーブル全体のスキャンを実行します。のように：
num/2=100 となる t から id を選択
次のように変更する必要があります:
num=100*2 の t から id を選択

9. where 句内のフィールドに対して関数操作を実行しないようにしてください。そうしないと、エンジンがインデックスの使用を中止し、テーブル全体のスキャンを実行することになります。のように：
select id from t where substring(name,1,3)='abc' – 名前がabcで始まるID
datediff(day,createddate,'2005-11-30′)=0–'2005-11-30′ で生成された ID の t から ID を選択
次のように変更する必要があります:
'abc%' のような名前の t から id を選択
createdate>='2005-11-30′ かつ createdate<'2005-12-1′ の t から id を選択

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

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

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

13. 多くの場合、 in の代わりに exists を使用するのが適切な選択です。
a から num を選択 where num in(b から num を選択)
次のように置き換えます。
存在する 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. 大規模なトランザクション操作を避け、システムの同時実行機能を向上させます。