MySQLデータベースの一般的な最適化操作のまとめ（経験共有）

序文

データ中心のアプリケーションの場合、データベースの品質はプログラムのパフォーマンスに直接影響するため、データベースのパフォーマンスは非常に重要です。したがって、誰もがMySQLデータベースの最適化操作を理解する必要があります。この記事では、主にMySQLデータベースの一般的な最適化操作をまとめています。詳細な紹介を見てみましょう。

1. 索引

言うまでもなく、インデックスを最優先にすることは、私たちがひっそりと使用してきたこの最適化方法、つまり主キー インデックスです。気にしないこともありますが、適切なインデックスを定義すると、データベースクエリのパフォーマンス（速度）が数倍、あるいは数十倍も向上します。

通常のインデックス

この機能はクエリ速度を向上させるためのものです。

テーブルとインデックスを作成する

テーブルtbl_nameを作成します(
フィールド名 フィールドタイプ [整合性制約]、
〜
インデックス [インデックス名] (列名)
);

インデックスを作成

CREATE INDEX index_name ON tab_name (column_name)

インデックスの削除

DROP INDEX index_name FROM tab_name

インデックスを表示

タブ名からインデックスを表示

主キーインデックス

目的はクエリとユニーク制約を高速化することです

テーブルとインデックスを作成する

テーブルtbl_nameを作成します(
フィールド名 フィールドタイプ [整合性制約]、
〜
主キー(列名)
);

インデックスを作成

ALTER TABLE tab_name ADD PRIMARY KEY(column_name)

インデックスの削除

ALTER TABLE tab_name DROP PRIMAY KEY(column_name)

ユニークインデックス

目的はクエリとユニーク制約を高速化することです

テーブルとインデックスを作成する

テーブルtbl_nameを作成します(
フィールド名 フィールドタイプ [整合性制約]、
〜
一意の [インデックス名] (列名)
);

インデックスを作成

CREATE UNIQUE INDEX index_name ON tab_name (column_name)

インデックスの削除

一意のインデックス index_name を tab_name から削除します

2. SELECT *の使用を減らす

データベースをクエリするときに、クエリ対象をすべて選択する人もいますが、これは不適切な動作です。すべてのデータではなく、必要なデータのみを取得する必要があります。選択すると、Web サーバーの負担が増加し、ネットワーク転送の負荷が増加し、当然クエリ速度が低下します。

説明選択

この機能を見たことがない人も多いと思いますが、ぜひここで使ってみてください。 EXPLAIN は、MySQL がインデックスを使用して選択ステートメントを処理し、テーブルを結合する方法を示します。より適切なインデックスを選択し、より最適化されたクエリ ステートメントを記述するのに役立ちます。主な使用法は、select の前に explain を追加することです。

EXPLAIN SELECT [検索フィールド名] FROM tab_name ...

4. クエリキャッシュを有効にする

ほとんどの MySQL サーバーではクエリ キャッシュが有効になっています。これはパフォーマンスを向上させる最も効果的な方法の 1 つであり、MySQL データベース エンジンによって処理されます。多数の同一クエリが複数回実行されると、クエリ結果はキャッシュに格納されるため、後続の同一クエリはテーブルを操作せずにキャッシュされた結果に直接アクセスできます。

最初のステップは、query_cache_type を ON に設定し、システム変数 have_query_cache が使用可能かどうかを照会することです。

'have_query_cache' のような変数を表示する

その後、クエリ キャッシュにメモリ サイズを割り当てて、キャッシュされたクエリ結果の最大値を制御します。関連する操作は構成ファイルで変更されます。

5. NOT NULLの使用

多くのテーブルには、NULL が列のデフォルト プロパティであるため、アプリケーションが NULL を保存する必要がない場合でも、NULL (空の値) の列が含まれています。通常は、NULL 値を格納する必要がある場合を除き、列が NOT NULL であることを指定するのが最適です。

NULL 対応の列を含むクエリは、NULL 対応の列によってインデックス、インデックス統計、および値の比較がより複雑になるため、MySQL では最適化がより困難になります。 NULL になる可能性のある列は、より多くのストレージスペースを使用するため、MySQL で特別な処理が必要になります。 NULL 対応の列にインデックスを作成すると、インデックス レコードごとに 1 バイト余分に必要になります。これにより、MyISAM では固定サイズのインデックス (単一の整数列のインデックスなど) が可変サイズのインデックスになることもあります。

通常、NULL 可能列を NOT NULL に変更してもパフォーマンスはわずかにしか向上しないため、問題が発生することが確実でない限り、既存のスキーマでこの状況を最初に見つけて変更する必要はありません。ただし、列にインデックスを作成する予定の場合は、NULL 対応となるように設計しないようにしてください。もちろん例外もあります。たとえば、InnoDB は NULL 値を格納するために別のビットを使用するため、スパース データのスペース効率が優れていることは注目に値します。これは MyISAM には適用されません。

6. ストレージエンジンの選択

MyISAM と InnoDB の選択方法については、トランザクション処理や外部キーが必要な場合は、InnoDB の方が適している可能性があります。フルテキスト インデックスが必要な場合は、システムに組み込まれているため、通常は MyISAM が適切な選択ですが、200 万行をテストすることはあまりありません。したがって、多少遅くても、Sphinx を使用することで InnoDB からフルテキスト インデックスを取得できます。

データのサイズは、ストレージ エンジンの選択に影響を与える重要な要素です。大規模なデータ セットでは、トランザクション処理と障害回復をサポートするため、InnoDB が選択される傾向があります。データベースのサイズによって、障害からの回復にかかる時間が決まります。InnoDB はトランザクション ログを使用してデータを回復できるため、より高速です。 MyISAMは必要になるかもしれない

これらのタスクを実行するのに何時間も、あるいは何日もかかる代わりに、InnoDB では数分しかかかりません。

データベース テーブルを操作する習慣も、パフォーマンスに大きな影響を与える要因となる可能性があります。たとえば、COUNT() は MyISAM テーブルでは非常に高速ですが、InnoDB テーブルでは面倒になる可能性があります。 InnoDB では主キー クエリは非常に高速になりますが、主キーが長すぎるとパフォーマンスの問題が発生するので注意する必要があります。大量の挿入は MyISAM を使用すると高速になりますが、更新は InnoDB を使用すると高速になります (特に同時実行が多い場合)。

それで、どれを使いますか?経験上、小規模なアプリケーションやプロジェクトの場合は、MyISAM の方が適している可能性があります。もちろん、大規模な環境で MyISAM をうまく使用できる場合もありますが、常にそうであるとは限りません。非常に大量のデータを扱うプロジェクトを使用する予定があり、トランザクション処理や外部キーのサポートが必要な場合は、InnoDB を直接使用する必要があります。ただし、InnoDB テーブルにはより多くのメモリとストレージが必要であり、100 GB の MyISAM テーブルを InnoDB テーブルに変換すると、非常に悪い結果になる可能性があることに注意してください。

7. where句で接続するためにorを使用しない

一方のフィールドにインデックスがあり、もう一方のフィールドにインデックスがない場合、エンジンはインデックスを破棄し、次のようにテーブル全体のスキャンを実行します。

num=10 または Name = 'admin' の t から id を選択

次のようにクエリを実行できます。

num = 10 の t から id を選択
すべて結合
Name = 'admin' の t から id を選択

8. varchar/nvarcharをもっと頻繁に使う

char/nchar の代わりに varchar/nvarchar を使用する理由は、まず、可変長フィールドはストレージ スペースをあまり占有せず、ストレージ スペースを節約できるためです。次に、クエリの場合、比較的小さなフィールドで検索する方が明らかに効率的です。

9. 大量のデータを返さない

ここでは、返されるデータの量を制限するために limit を使用することを検討する必要があります。毎回大量の不要なデータが返されると、クエリの速度も低下します。

10. Where句の最適化

where 句でパラメータを使用すると、SQL は実行時にのみローカル変数を解決しますが、オプティマイザはアクセス プランの選択を実行時まで延期できず、コンパイル時に選択する必要があるため、完全なテーブル スキャンが実行されます。ただし、アクセス プランがコンパイル時に構築される場合、変数の値は不明のままであり、インデックス選択の入力として使用することはできません。

where 句内のフィールドに対する式操作や、where 句内のフィールドに対する関数操作は避けてください。そうしないと、エンジンがインデックスの使用を中止し、テーブル全体のスキャンを実行することになります。 where 句の「=」の左側で関数、算術演算、またはその他の式演算を実行しないでください。そうしないと、システムがインデックスを正しく使用しない可能性があります。

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に少しでもお役に立てれば幸いです。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM をご愛顧いただき、ありがとうございます。