MySQL が大量のデータを処理するときにクエリ速度を最適化する方法の詳細な説明

最近、仕事の都合で、Mysqlデータベースのselectqueryステートメントの最適化方法に注目し始めました

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

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

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
union all
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)
連続値の場合、 between は使用できますが、Now では使用しないでください:
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 (インデックス名)) where num=@num

8, where 句内のフィールドに対して expression 操作を実行しないようにする必要があります。これにより、エンジンがインデックスの使用を断念し、テーブル全体のスキャンが実行されます。例:
select id from t where num/2=100
を次のように変更する必要があります:
select id from t where num=100*2

9原因 エンジンはインデックスの使用を断念し、テーブル全体のスキャンを実行します。例:
select id from t where substring(name,1,3)='abc'–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 from b)

次のステートメントを使用します。 置換:
select num from a where names (select 1 from b where num=a.num)

14. SQL は、クエリのデータに基づいてクエリを最適化するわけではありません。たとえば、テーブルに性別フィールドがあり、ほぼ半数が男性、半数が女性である場合、SQL クエリはインデックスを使用しないことがあります。インデックスは性別に基づいて構築されるため、クエリの効率には影響しません。

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

16. クラスター化インデックス データ列の順序は、テーブル レコードの物理的な格納順序になるため、列の値が変更されると、テーブル レコード全体の順序が調整されるため、クラスター化インデックス データ列の更新はできるだけ避けてください。 、莫大なリソースがかかります。アプリケーション システムがクラスター化インデックスのデータ列を頻繁に更新する必要がある場合は、インデックスをクラスター化インデックスとして構築する必要があるかどうかを検討する必要があります。 17. フィールドに数値情報のみが含まれる場合は、クエリと接続のパフォーマンスが低下し、ストレージのオーバーヘッドが増加するように設計しないでください。これは、エンジンがクエリと接続を処理するときに

string

内の各文字を 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 サイトの他の関連記事を参照してください。