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

データベース管理における 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 を使用することはお勧めしません。そうしないと、クエリ結果が不正確になる可能性があります。

この方法により、時間を 3 倍も短縮できる場合があります。

ここでは上記の最適化ソリューションのみを記載しています。もちろん、他の最適化方法もありますので、ぜひ探して試してみてください。ご清聴ありがとうございました。 。

以下もご興味があるかもしれません:
  • Mysqlサーバーのインストール、構成、起動、シャットダウン方法の詳細な説明
  • MySQL マルチインスタンス インストール ブート自動起動サービス設定プロセス
  • MySQL5.7 シングルインスタンス自動起動サービスの設定プロセス
  • MySQL 構成マスタースレーブサーバー (マスター 1 台とスレーブ複数台)
  • Windows の MySQL net start mysql MySQL サービスの起動エラーが発生する システムエラーの解決
  • データベース管理に役立つ 5 つの MySQL GUI ツール
  • よく使われる 5 つの MySQL データベース管理ツールの詳細な紹介
  • MySQL データベース管理の一般的なコマンドの概要
  • MySQL サービスとデータベース管理

<<:  Linux パーティションまたは論理ボリュームにファイルシステムを作成する方法

>>:  Javascriptでシングルトンパターンを実装する方法

推薦する

UbuntuにMySQLデータベースをインストールする方法

Ubuntu は、Linux をベースにした無料のオープンソース デスクトップ PC オペレーティン...

MySQL トリガーの原理と使用例の分析

この記事では、例を使用して、MySQL トリガーの原理と使用方法を説明します。ご参考までに、詳細は以...

Vueのリストレンダリングの詳細な説明

目次1. v-for: 配列の内容を走査する(よく使われる) 2. v-for: オブジェクトのプロ...

Linux で MySQL データベースのデータ ファイル パスを変更する手順

rpm インストール方法を使用して MySQL データベースをインストールした後、データ ファイルの...

CSSは半透明の境界と複数の境界のシーン分析を実現します

シナリオ 1:半透明の境界線を実現するには: CSS スタイルのデフォルトの動作により、背景色はコン...

IDEA が docker を統合して springboot プロジェクトを展開するプロセス全体

目次1. IDEAはdockerプラグインをダウンロードします2. クラウドサーバーDocker 2...

MySQL のロードバランサーとして nginx を使用する方法

注意: nginxのバージョンは1.9以上である必要があります。nginxをコンパイルするときに、-...

Windows 10 Home Edition に Docker for Windows をインストールする

0. 背景ハードウェア: Xiaomi Notebook Air 13/Inter Core i7-...

Windows 上で Zookeeper サーバーを構築するチュートリアル

インストールと設定Apacheの公式ウェブサイトには多くのミラーダウンロードアドレスが用意されており...

MySQL の一時テーブルと派生テーブルについての簡単な説明

派生テーブルについてメイン クエリに派生テーブルが含まれている場合、または SELECT ステートメ...

React は antd のアップロード コンポーネントを使用してファイル フォーム送信機能を実装します (完全なコード)

私はプロジェクトを実行するために react を使い始めたばかりで、非常に未熟で完全な初心者です。私...

Vue はクリックフリップ効果を実現します

参考までに、vueを使用してクリックフリップエフェクトを簡単に実装します。具体的な内容は次のとおりで...

SecureCRT に基づくリモート Linux ホストへのファイルのアップロードとダウンロードのグラフィカルな手順

wget や curl ツールを使用して、Linux サーバーで大規模なネットワーク ファイルを直接...

C++ を使用して MySQL に接続する方法

C++でMySQLに接続する際の参考情報です。具体的な内容は以下のとおりです。 MySQLCon ク...