MySQL パフォーマンスのロックを解除する: クエリ キャッシュの最適化をマスターする

クエリキャッシュとは何ですか?

クエリ キャッシュは、SELECT クエリの結果をキャッシュすることでデータベースのパフォーマンスを向上させるように設計された MySQL の機能です。以前に実行されたクエリが再度実行されると、MySQL はデータベースに対して再実行するのではなく、このキャッシュから結果を迅速に取得できます。これにより、データの取得が高速化されるだけでなく、データベースの負荷も軽減され、一貫したパラメータで頻繁に実行されるクエリが非常に効率的になります。

クエリキャッシュの仕組み

クエリが発行されるたびに、MySQL はまずクエリ キャッシュを調べて、同じクエリの結果が以前に保存されているかどうかを確認します。一致する場合、MySQL は通常のクエリ実行プロセスをバイパスし、キャッシュされた結果を直接提供します。これは、クエリ処理とディスク アクセスの時間のかかる手順を回避し、代わりにメモリ アクセスの速度を利用するため、はるかに高速です。

ここで、重要なクエリ キャッシュ変数である query_cache_type、query_cache_size、query_cache_limit、および query_cache_min_res_unit と、それらが MySQL のパフォーマンスに与える影響について説明します。

1.クエリキャッシュタイプ

MySQL の query_cache_type 変数は、クエリ キャッシュを有効にするか無効にするか、またはオンデマンドのみで動作するように設定するかを制御します。

query_cache_type の使用法

クエリ キャッシュには、SELECT ステートメントのテキストと、クライアントに送信された対応する結果が保存されます。後で同一のステートメントを受信した場合、サーバーはステートメントを解析して再度実行するのではなく、キャッシュから結果を取得します。クエリ キャッシュを有効にすると、頻繁に実行されるクエリを再実行するのではなくメモリから提供するため、結果を取得するまでにかかる時間を大幅に短縮できます。

MySQL の query_cache_type 変数を別の値に設定すると、クエリ キャッシュの動作が決まります。

• 0 (OFF) – query_cache_size バイトのバッファーはまだ割り当てられていますが、クエリ キャッシュを無効にします。

• 1 (ON) – SQL_NO_CACHE がクエリで指定されていない限り、すべての SELECT クエリのクエリ キャッシュを有効にします。

• 2 (DEMAND) – SQL CACHE 句を明示的に使用するクエリに対してのみクエリ キャッシュを有効にします。

query_cache_type の設定

Query_cache_type は、サーバーの実行中にオフラインまたはオンラインで構成できます。テストを可能にするため、構成はオンラインで行うことが好ましい場合があります。サーバーが再起動されると、query_cache_type は元に戻ります。

コマンドライン構成:
mysqld> set global query_cache_type = XX

XX をデータベースのニーズに合わせた値に置き換えます。変数が変更されたことを確認するには:

mysqld> 「query_cache_type」

のようなグローバル変数を表示します

設定ファイル:
[mysqld]
query_cache_type = XX

XX をデータベースのニーズに合わせた値に置き換えます。 MySQL サーバーを再起動します。

query_cache_type の考慮事項

一般的な推奨事項に従って、データの変更は頻繁ではないが読み取りが頻繁に行われる環境では、query_cache_type を 1 (ON) に設定する必要があります。キャッシュを維持するオーバーヘッドが利点を上回る可能性がある非常に動的な環境では、0 (オフ) に設定します。

クエリ キャッシュを無差別に有効にすると、必ずしもパフォーマンスの向上が得られるとは限らず、シナリオによってはパフォーマンスが低下する可能性もあります。 query_cache_type を構成するときは、次の要素を考慮してください:

• クエリ キャッシュのサイズ – キャッシュが大きいほど、より多くのクエリ結果を保持できますが、より多くのメモリが必要になります。

• クエリ パターン – 頻繁に変更される結果や大きな結果セットを含むクエリでは、キャッシュのメリットが得られない可能性があります。

• キャッシュの無効化 – キャッシュされたテーブルの更新、挿入、または削除により、対応するキャッシュ エントリが無効になり、キャッシュ チャーンが発生します。

• 同時実行性 – クエリ キャッシュは、競合の問題のため、同時実行性の高いワークロードには適していません。

• MySQL バージョン – クエリ キャッシュ機能には制限があり、マルチスレッド環境で競合が発生する可能性があるため、クエリ キャッシュ機能は MySQL 5.7 で非推奨となり、MySQL 8.0 で削除されました。

2.クエリキャッシュサイズ

キャッシュされたクエリの結果を保存するために割り当てられるメモリの量を指定します。これは、一度にキャッシュできる結果の数を決定する主な要素です。

query_cache_size の使用法

query_cache_size 変数は、クエリ キャッシュに割り当てられるメモリの量を決定します。この値は、ワークロードの性質と利用可能なメモリ リソースに基づいて調整する必要があります:

• 小さい結果セット – アプリケーションが小さい結果セットを返すクエリを頻繁に実行する場合、クエリ キャッシュ サイズを大きくすると有益な場合があります。これにより、より多くのクエリをキャッシュに保存できるようになり、クエリ実行の必要性が減ります。

• 頻繁に同一のクエリ – 同じクエリが繰り返し実行されるシナリオでは、query_cache_size を増やすと、これらのクエリとその結果がキャッシュされ、パフォーマンスが向上します。

• クエリ キャッシュ ヒット率 – クエリ キャッシュ ヒット率を監視すると、キャッシュの有効性についての洞察が得られます。ヒット率が低い場合は、query_cache_size を増やすとキャッシュ効率が向上する可能性があります。

query_cache_size の設定

Query_cache_size は、サーバーの実行中にオフラインまたはオンラインで構成できます。テストを可能にするために、構成はオンラインで行うことが好ましい場合があります。サーバーが再起動されると、query_cache_size は元に戻ります。

コマンドライン構成:
mysqld> set global query_cache_size = XX

XX をデータベースのニーズに合わせた値に置き換えます。変数が変更されたことを確認するには:

mysqld> 「query_cache_size」のようなグローバル変数を表示

設定ファイル:
_[mysqld]
query_cache_size _= XX

XX をデータベースのニーズに合わせた値に置き換えます。 MySQL サーバーを再起動します。

query_cache_size の考慮事項

query_cache_size は、利用可能なメモリとワークロードの性質に基づいて設定する必要があります。設定が大きすぎるとメモリが枯渇する可能性があり、設定が小さすぎると効果が制限される可能性があります。

キャッシュの使用率 (ヒットと挿入) を監視すると、適切なサイジングが決まります。 64MB ～ 128MB などの適度なサイズから始めて、パフォーマンスと利用可能なシステム メモリに基づいて調整します。

query_cache_size を構成するときは、次の要素を考慮してください:

• クエリ パターン – 頻繁に変更される結果や大きな結果セットを含むクエリでは、キャッシュのメリットが得られない可能性があります。

• キャッシュの無効化 – キャッシュされたテーブルの更新、挿入、または削除により、対応するキャッシュ エントリが無効になり、キャッシュ チャーンが発生します。

• 同時実行性 – クエリ キャッシュは、競合の問題のため、同時実行性の高いワークロードには適していません。

• MySQL バージョン – クエリ キャッシュ機能は、マルチスレッド環境での制限と競合のため、MySQL 5.7 では非推奨となり、MySQL 8.0 では削除されました。

3.クエリキャッシュ制限

この変数は、キャッシュできる個々のクエリ結果の最大サイズを設定します。これにより、大規模なクエリが不釣り合いな量のキャッシュ スペースを消費するのを防ぎます。

query_cache_limit の使用法

クエリ結果が query_cache_limit を超えると、結果はキャッシュされません。これにより、過度に大規模なクエリやリソースを大量に消費するクエリによって、頻繁に再利用されない可能性のある結果でキャッシュがいっぱいになるのを防ぎます。 query_cache_limit に適切な値を設定すると、より小さく、より一般的に使用されるクエリ結果のみがキャッシュされるようになり、メモリの使用が最適化されます。

query_cache_limit の構成

Query_cache_limit は、サーバーの実行中にオフラインまたはオンラインで構成できます。テストを可能にするために、構成はオンラインで行うことが好ましい場合があります。サーバーが再起動されると、query_cache_limit は元に戻ります。

コマンドライン構成:
mysqld> set global query_cache_limit = XX

XX をデータベースのニーズに合わせた値に置き換えます。変数が変更されたことを確認するには:

mysqld> 「query_cache_limit」のようなグローバル変数を表示

設定ファイル:
[mysqld]
query_cache_limit = XX

XX をデータベースのニーズに合わせた値に置き換えます。 MySQL サーバーを再起動します。

query_cache_limit の考慮事項

通常、クエリの性質と利用可能なキャッシュ サイズに応じて、query_cache_limit を 1MB から 4MB の間に設定することをお勧めします。 query_cache_limit の設定が低すぎると、有用なクエリ結果がキャッシュから除外され、クエリ キャッシュの有効性が低下する可能性があることに注意することが重要です。

4. query_cache_min_res_unit

MySQL の query_cache_min_res_unit 変数は、クエリ キャッシュによって割り当てられるブロックの最小サイズ (バイト単位) を決定します。この設定は、キャッシュされた結果の粒度を制御することにより、クエリ キャッシュの効率に影響を与えます。

query_cache_min_res_unit の使用法

クエリ結果がクエリ キャッシュに保存されると、一定量のメモリが占​​有されます。 query_cache_min_res_unit 変数は、これらのキャッシュされた結果に割り当てられるメモリ ブロックの最小サイズを定義します。クエリ結果がこの値より小さい場合でも、query_cache_min_res_unit で定義された最小サイズが占有されます。

query_cache_min_res_unit の構成

Query_cache_min_res_unit は、サーバーの実行中にオフラインまたはオンラインで構成できます。テストを可能にするために、構成はオンラインで行うことが好ましい場合があります。サーバーが再起動されると、query_cache_min_res_unit は元に戻ります。

コマンドライン構成:
mysqld> set global query_cache_min_res_unit = XX

XX をデータベースのニーズに合わせた値に置き換えます。変数が変更されたことを確認するには:

mysqld> 'query_cache_min_res_unit'
のようなグローバル変数を表示します _
設定ファイル:
_[mysqld]
query_cache_min_res_unit = XX

XX をデータベースのニーズに合わせた値に置き換えます。 MySQL サーバーを再起動します。

query_cache_min_res_unit に関する考慮事項

query_cache_min_res_unit の構成には、メモリ消費とキャッシュ効率のバランスをとる適切な値に変数を設定することが含まれます。この値は、ワークロード内のクエリ結果の平均サイズに基づいて選択する必要があります。

• 値を小さくするとメモリ使用効率が向上しますが、キャッシュ エントリが増えるためオーバーヘッドが増加する可能性があります。

• 逆に、値を大きくするとキャッシュ エントリの数は減る可能性がありますが、クエリ結果が小さい場合にメモリが無駄に消費される可能性があります。

ワークロードを分析して、クエリ結果の平均サイズを決定します。この分析に基づいて query_cache_min_res_unit の値を調整して、メモリ消費とキャッシュ効率のバランスを実現します。ほとんどのセットアップでは、これは 16MB から 64MB の間に収まります。

4 つのステップでクエリ キャッシュを有効にして調整する

クエリ キャッシュは MySQL 5.7.20 で非推奨となり、MySQL 8.0 では完全に削除されました。 MySQL のバージョンが引き続き query_cache を使用できる場合は、デフォルトで無効になっているため、有効にする必要があります。 MySQL、MariaDB、または Percona でクエリ キャッシュを有効にして構成するには、通常、サーバーの my.cnf または my.ini ファイルにアクセスする必要があります。段階的なアプローチは次のとおりです:

1.クエリ キャッシュを有効にする – query_cache_type を 1 または 2 に設定します。query_cache_type または query_cache_size をゼロに設定すると、常にキャッシュが無効になります。選択的キャッシュ (ほとんどのユースケースで推奨) の場合は、次を使用します。

query_cache_type = 1

1. キャッシュ サイズの設定 – query_cache_size を定義します。開始点は使用可能な合計メモリの 10 ～ 20% ですが、これにはワークロードに基づいて調整する必要があります。

クエリキャッシュサイズ = 100M

3.結果サイズ制限の定義 – 保存される結果のサイズを制御するために query_cache_limit を構成します。これは、一般的なクエリ サイズに応じて、数メガバイトから始まる可能性があります:

query_cache_limit = 2M

4.最小結果単位を調整 – ニーズに基づいて query_cache_min_res_unit を変更します。これをデフォルトよりも小さくすると、特に小規模なクエリが多数発生すると予想される場合、キャッシュ領域をより効率的に利用できるようになります。

query_cache_min_res_unit = 512

ヘルスチェックによるクエリキャッシュの監視

QCache の断片化は、MySQL におけるクエリ キャッシュのパフォーマンスの重要な指標です。この機能は、SELECT クエリの結果を保存するために構築されており、クエリを再実行することなく、繰り返しのリクエストを迅速に実行できるため、パフォーマンスが向上します。ただし、時間が経つと、クエリ キャッシュが断片化し、効率が低下する可能性があります。

QCache の断片化を計算する

QCache Fragmentation の計算方法に関する情報と段階的な手順が記載されている包括的なヘルス チェックのドキュメントを確認してください。

QCache Fragmentation と QcacheDeleteRate を計算したら、結果を解釈する必要があります。理想的には、QCache Fragmentation は 10 未満、QcacheDeleteRate は 20 未満である必要があります。

推奨されるアクション

QCache の断片化が高い場合は、断片化を減らすためにクエリ キャッシュのサイズを調整する必要がある場合があります。 QcacheDeleteRate が高い場合は、クエリ キャッシュのサイズを増やすか、クエリを最適化して INSERT の数を減らす必要がある場合があります。

クエリキャッシュを簡単に最適化

MySQL クエリ キャッシュのチューニングには、メモリ使用量の管理からクエリ時間の短縮まで、データベースのパフォーマンスを最適化するためにいくつかの設定を調整することが含まれます。ここで説明する変数は優れた基盤を形成しますが、効果的な管理には、実際のシステム負荷とパフォーマンスに基づいた継続的な監視と更新が必要です。

このプロセスを簡素化するには、これらの調整を自動化する Releem のような強力な管理ツールの使用を検討してください。このようなツールは、システムのパフォーマンスを継続的に監視し、query_cache 設定をリアルタイムで動的に更新できます。

これにより、Releem が複雑なクエリ キャッシュ最適化を処理しながら、より広範な目標に集中する時間を確保できます。

以上がMySQL パフォーマンスのロックを解除する: クエリ キャッシュの最適化をマスターするの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。