データベース管理における 19 の MySQL 最適化方法

MySQL データベースを最適化すると、データベースの冗長性を削減できるだけでなく、データベースの実行速度を変更することもできます。ここでは、参考までにまとめた 19 の優れた MySQL データベース最適化方法を紹介します。

ステートメント: 以下の最適化ソリューションはすべて「MySQL-index-BTree タイプ」に基づいています。

1.説明する

MySQL を最適化するときは、EXPLAIN をうまく活用して SQL 実行プランを表示する必要があります。

以下は、注目したいデータを (1,2,3,4,5) でマークする簡単な例です。

タイプ列、接続タイプ。適切な SQL ステートメントは、少なくとも範囲レベルに到達する必要があります。全レベルのキー列とインデックス名の使用は避けてください。インデックスが選択されなかった場合、値は NULL になります。強制インデックス方式の key_len 列、インデックス長行列、およびスキャン行数を使用できます。この値は推定される追加列、詳細な説明です。よくある不親切な値には、filesortの使用、temporaryの使用などがあることに注意してください。2.SQL文のINに含まれる値は多すぎないようにする必要があります。

MySQL では IN に対応する最適化が行われており、IN 内のすべての定数が配列に格納され、この配列がソートされます。ただし、値が大きい場合は、消費量も比較的大きくなります。別の例: select id from t where num in(1,2,3) 連続する値の場合は、in の代わりに between を使用するか、join を使用します。

3. SELECT文ではフィールド名を指定する必要があります

SELECT * は、多くの不要な消費 (CPU、IO、メモリ、ネットワーク帯域幅) を追加します。カバーインデックスを使用する可能性が高まります。テーブル構造が変更されると、前のセクションも更新する必要があります。したがって、選択後に直接フィールド名を追加する必要があります。

4. 必要なデータが1つだけの場合は、制限1を使用します。

これは、EXPLAINの型列がconst型に到達するようにするためです。

5. ソートフィールドでインデックスを使用しない場合は、できるだけ少なくソートする

6. 制限内の他のフィールドにインデックスがない場合は、できるだけ使用しないようにします。

or 条件の両側のフィールドの 1 つがインデックスフィールドではなく、他の条件もインデックスフィールドでない場合、クエリはインデックスを使用しません。多くの場合、「or」の代わりに union all または union (必要な場合) を使用すると、より良い結果が得られます。

7. unionの代わりにunion allを使用する

union と union all の主な違いは、前者は、並べ替え、大量の CPU 操作の追加、リソース消費と待ち時間の増加を伴う固有のフィルタリング操作を実行する前に結果セットをマージする必要があることです。もちろん、すべての結合の前提条件は、2 つの結果セットに重複するデータが存在しないことです。

8. ORDER BY RAND() を使用しない

`dynamic` から ID を選択し、rand() で順序を制限して 1000 にします。

上記の SQL ステートメントは次のように最適化できます。

`dynamic` から id を選択し、 t1 を join (select rand() * (select max(id) from `dynamic`) as nid) t2 で t1.id > t2.nid 制限 1000 にします。

9. in と exists、not in と not exists を区別する

テーブルAからIDが次の値である*を選択(テーブルBからIDを選択)

上記のSQL文は、

テーブル A が存在する場合、* を選択(テーブル B の id がテーブル A.id の場合、* を選択)

in と exists の主な違いは、駆動順序が変更されることです (これがパフォーマンス変更の鍵となります)。exists の場合、外部テーブルが駆動テーブルとなり、最初にアクセスされます。IN の場合、サブクエリが最初に実行されます。したがって、IN は外部テーブルが大きく内部テーブルが小さい状況に適しており、EXISTS は外部テーブルが小さく内部テーブルが大きい状況に適しています。

not in と not exists に関しては、not exists を使用することをお勧めします。これは効率の問題であるだけでなく、not in には論理的な問題がある可能性があります。存在しないものを置き換える SQL ステートメントを効率的に記述するにはどうすればよいですか?

元のSQL文

テーブル A から colname … を選択し、a.id が含まれない (テーブル B から b.id を選択)

効率的なSQL文

テーブル A から colname … を選択し、テーブル B を a.id = b.id で結合し、b.id が null であるかどうかを確認します。

取得した結果セットは次の図に示されています。表Aのデータは表Bにはありません。

10. ページング効率を向上させるために適切なページング方法を使用する

製品制限 866613、20 から ID、名前を選択

上記の SQL ステートメントをページングに使用する場合、テーブルデータの量が増えるにつれて、制限ページングクエリを直接使用すると、速度がどんどん遅くなることに気付く場合があります。

最適化の方法は次のとおりです。前のページの最大行数の ID を取得し、この最大 ID に基づいて次のページの開始点を制限します。たとえば、この列では、前のページの最大の ID は 866612 です。 sql は次のように記述できます。

id> 866612 の製品から id、name を選択、制限 20

11. セグメントクエリ

一部のユーザー選択ページでは、ユーザーが選択した時間範囲が大きすぎるために、クエリが遅くなる場合があります。主な理由は、スキャンラインが多すぎることです。この時点で、プログラムを使用してセグメントでクエリを実行し、ループでトラバースし、結果をマージして表示することができます。

次の SQL ステートメントに示すように、スキャンされる行数が 100 万を超える場合は、セグメント化されたクエリを使用できます。

12. where句のフィールドでnull値の判定を避ける

null と判断されると、エンジンはインデックスの使用を中止し、テーブル全体のスキャンを実行します。

13. %プレフィックスのあいまい検索の使用は推奨されません

たとえば、LIKE "%name" または LIKE "%name%" です。このクエリによりインデックスが失敗し、テーブル全体のスキャンが実行されます。ただし、LIKE "name%" を使用できます。

では、%name% をクエリするにはどうすればよいでしょうか?

次の図に示すように、シークレットフィールドにインデックスが追加されていますが、説明結果では使用されません。

では、この問題をどう解決すればよいのでしょうか? 答えは、フルテキストインデックスを使用することです。

クエリでは、 select id,fnum,fdst from dynamic_201606 where user_name like '%zhangsan%'; をよく使用します。このようなステートメントの場合、通常のインデックスではクエリの要件を満たすことができません。幸いなことに、MySQL には役立つフルテキストインデックスがあります。

フルテキストインデックスを作成するための SQL 構文は次のとおりです。

テーブル `dynamic_201606` を変更し、FULLTEXT インデックス `idx_user_name` (`user_name`) を追加します。

フルテキストインデックスを使用するための SQL ステートメントは次のとおりです。

dynamic_201606 から id、fnum、fdst を選択します。ここで、match(user_name) against('zhangsan' in boolean mode);

注意: フルテキストインデックスを作成する前に、DBA に問い合わせて、作成できるかどうかを確認してください。同時に、クエリステートメントの記述は通常のインデックスとは異なることに注意することが重要です。

14. where句のフィールドに対する式演算を避ける

例えば

age*2=36 の場合、user_base から user_id、user_project を選択します。

上記の例では、フィールドに対して算術演算が実行され、エンジンはインデックスの使用を放棄します。これを次のように変更することをお勧めします。

age=36/2 の場合、user_base から user_id、user_project を選択します。

15. 暗黙的な型変換を避ける

型変換は、where句の列の型が渡されたパラメータの型と一致しない場合に発生します。まずwhere句のパラメータの型を決定することをお勧めします。

16. 結合インデックスの場合、左端のプレフィックスルールに従う必要があります。

たとえば、インデックスに id、name、school のフィールドが含まれている場合、id フィールドを直接使用することも、id と name の順序で使用することもできますが、name と school ではこのインデックスを使用できません。したがって、共同インデックスを作成するときは、インデックスフィールドの順序に注意し、よく使用されるクエリフィールドを先頭に配置する必要があります。

17. 必要に応じて、force indexを使用してクエリを特定のインデックスに強制的に通過させることができます。

MySQL オプティマイザは、SQL ステートメントを取得するために適切であると判断したインデックスを使用することがありますが、使用するインデックスが必ずしも必要なものではない場合があります。このとき、force index を使用して、設定したインデックスをオプティマイザーが強制的に使用するようにすることができます。

18. 範囲クエリステートメントに注意する

結合インデックスの場合、between、>、< などの範囲クエリがあると、後続のインデックスフィールドは無効になります。

19. JOIN最適化について

LEFT JOIN テーブルAが駆動テーブルです。INNER JOIN MySQLは、駆動テーブルとしてデータが少ないテーブルを自動的に見つけます。RIGHT JOIN テーブルBが駆動テーブルです。

注意: MySQLには完全な結合はありません。次の方法で解決できます。

A から * を選択し、B を B.name = A.name に結合します。 
B.nameがnullの場合
 すべて結合
B から * を選択します。

内部結合を使用し、左結合を避けるようにしてください

共同クエリには少なくとも 2 つのテーブルが関係しており、通常はそれらのサイズが異なります。接続方法が内部結合の場合、他のフィルタリング条件がなければ、MySQL は小さなテーブルを駆動テーブルとして自動的に選択します。ただし、左結合は駆動テーブルの選択において左側が右側を駆動するという原則に従います。つまり、左結合の左側のテーブル名が駆動テーブルです。

インデックスの適切な使用

駆動テーブルのインデックスフィールドは、on の制限フィールドとして使用されます。

小さなテーブルを使って大きなテーブルを動かす

概略図から直感的にわかるのは、ドライバテーブルを削減できれば、ネストされたループ内のループ数を削減でき、総 IO 量と CPU 操作数を削減できるということです。

STRAIGHT_JOINの使用

内部結合は MySQL によって実行されますが、group by、order by、「filesort の使用」、「temporary の使用」などの特殊なケースでは、別のテーブルを駆動テーブルとして選択する必要があります。 STRAIGHT_JOIN は結合順序を強制するために使用されます。STRAIGHT_JOIN の左側のテーブル名は駆動テーブルであり、右側のテーブルは駆動されるテーブルです。 STRAIGHT_JOIN を使用するための前提条件は、クエリが内部結合であることです。他のリンクに STRAIGHT_JOIN を使用することはお勧めしません。そうしないと、クエリ結果が不正確になる可能性があります。