MySQL クエリ キャッシュのグラフィカルな説明

1. 原則の概要

クエリ キャッシュは、「クエリによって返された結果」をキャッシュします。ただし、「クエリによって返された結果」を単にキャッシュするだけでは正確ではありません。クエリ キャッシュはクエリ ステートメントもキャッシュし、「クエリ ステートメント」と「クエリによって返された結果」をメモリにマッピングします。
MySQL は SELECT クエリ ステートメントを受け取ると、クエリ ステートメントに対してハッシュ計算を実行してハッシュ値を取得します。次に、ハッシュ値を使用してクエリ キャッシュを照合します。一致する結果が得られない場合、ハッシュ値はハッシュリンクリストに保存され、クエリの結果はキャッシュに保存されます。ハッシュ値を格納するリンク リストの各ノードは、対応するクエリの戻り結果のアドレスと、クエリに関係するいくつかのテーブルに関する関連情報をキャッシュに格納します。ハッシュ値を通じて同じクエリが一致した場合、キャッシュ内の対応するクエリ結果がクライアントに直接返されます。 MySQL の任意のテーブルのデータが変更されると、クエリ キャッシュに通知され、このテーブルに関連するすべてのクエリ キャッシュが無効になり、占有されているメモリ領域が解放されます。テーブル データを変更する操作には、INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE、DROP DATABASE などがあります。

クエリ キャッシュの動作原理は、クエリを実行する最も速い方法は、クエリを実行しないことです。クエリ キャッシュのコンポーネント図とフロー チャートは次のとおりです。

クエリキャッシュシステム変数

クエリ キャッシュの主な構成可能なシステム変数は次のとおりです。

1. クエリキャッシュを持つ

mysqld がクエリ キャッシュをサポートしているかどうかを示します。

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

クエリ キャッシュによってキャッシュできる単一のクエリの最大結果セット サイズを示します。デフォルト値は 1 MB です。クエリの結果セットのサイズがこのシステム変数の値を超える場合、クエリ キャッシュはこのクエリの結果セットをキャッシュしません。

3. クエリキャッシュ最小解像度単位

MySQL が毎回クエリ キャッシュに割り当てるメモリの最小サイズ、つまりクエリ結果をキャッシュするために使用されるメモリ領域の最小サイズを示します。デフォルト値は 4KB です。

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

クエリ キャッシュで使用できる最大メモリ サイズを示します。デフォルト値は 1MB です。値は 1024 の整数倍である必要があります。整数倍でない場合、MySQL は自動的に 1024 の倍数の最大値に調整します。

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

クエリ キャッシュの動作モードを示し、クエリ キャッシュ機能のオン/オフを切り替えます。0 (オフ)、1 (オン)、2 (要求) に設定できます。

• 0 (OFF): クエリ キャッシュ機能を無効にします。クエリ キャッシュはいかなる状況でも使用されません。

• 1 (ON): クエリ キャッシュ機能を有効にします。ただし、SELECT ステートメントで SQL_NO_CACHE オプションが使用されている場合、クエリ キャッシュは使用されません。

• 2 (DEMAND): クエリ キャッシュ機能を有効にしますが、SELECT ステートメントで SQL_CACHE オプションが使用されている場合にのみクエリ キャッシュを使用します。

6. クエリキャッシュwlock_invalidate

テーブルに書き込みロックが追加されたときに、テーブルに関連付けられたクエリ キャッシュを最初に無効にするかどうかを制御します。 1 (オン) と 0 (オフ) の 2 つの値があります。

• 1 (ON): ロックの書き込み中に、テーブルに関連するすべてのクエリ キャッシュが無効になります。

• 0 (オフ): ロックの書き込み中でも、テーブルに関連付けられたクエリ キャッシュの読み取りは許可されます。

頻繁に調整する必要がある環境変数は、query_cache_limit と query_cache_min_res_unit です。どちらも、実際のビジネス ニーズに基づいて適宜調整する必要があります。たとえば、キャッシュされたクエリ結果セットのほとんどが 4 KB 未満の場合、メモリの浪費を避けるために query_cache_min_res_unit の値を適切に調整できます。クエリ結果セットのサイズが 1 MB を超える場合は、サイズが制限を超えているために結果セットがキャッシュされないように、query_cache_limit の値を調整する必要があります。

クエリキャッシュステータス変数

MySQL は、クエリ キャッシュの現在のステータスを記録するための一連のステータス変数を提供しており、クエリ キャッシュが正常に実行されているかどうか、ヒット率はどれくらいか、メモリ領域は十分かどうかなどを確認できます。クエリ キャッシュのステータス変数は次のとおりです。

1. Qcache_free_blocks

クエリ キャッシュ内に現在いくつの空きメモリ ブロックがあるかを示します。この値が大きい場合、クエリ キャッシュにメモリの断片化が大量にある可能性があります。 FLUSH QUERY CACHE はキャッシュをデフラグして、より大きな空きメモリ ブロックを作成します。

2. Qキャッシュ空きメモリ

クエリ キャッシュの現在の空きメモリ サイズを示します。

3. Qcache_hits

クエリ キャッシュにヒットしたクエリの数を示します。

4. Qcache_挿入

クエリ キャッシュに新しいレコードが挿入された回数、つまりクエリがヒットしなかった回数。

5. Qcache_lowmem_prunes

クエリ キャッシュのメモリ不足によりキャッシュから削除されたクエリ結果の数を示します。この値が継続的に増加している場合は、通常、クエリ キャッシュの空きメモリが不足している (Qcache_free_memory で判断) か、メモリが著しく断片化されている (Qcache_free_blocks で判断) ことを意味します。

6. Qcache_not_cached

キャッシュされていないクエリの数を示します。クエリ結果がキャッシュされない状況は 3 つあります。1 つ目は query_cache_type の設定によるもの、2 つ目はクエリが SELECT ステートメントではないもの、3 つ目は now() などの関数が使用され、クエリ ステートメントが変更され続けるもの。

7. Qcache_queries_in_cache

クエリ キャッシュに現在含まれているクエリ結果の数を示します。

8. Qcache_total_blocks

クエリ キャッシュ内のメモリ ブロックの合計数を示します。

4. 利点と欠点

1. 利点

クエリ キャッシュ クエリは、MySQL がクライアントのクエリ要求を受信し、クエリ権限を検証した後、クエリ SQL を解析する前に実行されます。つまり、MySQL はクライアントからクエリ SQL を受信すると、対応する権限を確認し、クエリ キャッシュを通じて結果を検索するだけで済みます。オプティマイザ モジュールを通じて実行プランを分析して最適化する必要はなく、ストレージ エンジンとのやり取りも必要ありません。クエリ キャッシュはメモリベースであり、対応するクエリ結果をメモリから直接返すため、ディスク I/O と CPU 計算が大幅に削減され、非常に高い効率が実現します。

2. デメリット

クエリ キャッシュの利点は明らかですが、いくつかの欠点も無視できません。

• クエリ ステートメントのハッシュ計算とハッシュ検索によって発生するリソース消費。 query_cache_type が 1 (つまり ON) に設定されている場合、MySQL は受信した各 SELECT タイプのクエリに対してハッシュ計算を実行し、このクエリのキャッシュされた結果が存在するかどうかを確認します。ハッシュ計算と検索の効率は十分に高く、クエリ ステートメントのオーバーヘッドは無視できますが、同時実行性が高く、クエリ ステートメントが数万個になると、ハッシュ計算と検索のオーバーヘッドを真剣に考慮する必要があります。

• クエリ キャッシュの有効期限の問題。テーブルが頻繁に変更される場合、クエリ キャッシュの失敗率は非常に高くなります。テーブルの変更は、テーブル内のデータの変更だけでなく、テーブル構造やインデックスの変更も含まれます。

• クエリ ステートメントは異なるがクエリ結果は同じであるクエリがキャッシュされるため、メモリ リソースが過剰に消費されることになります。クエリ ステートメントの大文字と小文字、スペース、またはコメントが異なる場合、クエリ キャッシュはそれらを異なるクエリと見なします (ハッシュ値が異なるため)。

• 関連するシステム変数の設定が不適切だと、大量のメモリ断片化が発生し、クエリ キャッシュのメモリが頻繁にクリアされることになります。

5. よくある質問

1. クエリ キャッシュはどのような状況で使用すればよいですか?

実際、すべてのテーブルがクエリ キャッシュの使用に適しているわけではありません。クエリ キャッシュが失敗する主な理由は、対応するテーブルが変更されたことです。したがって、頻繁に変更されるテーブルではクエリ キャッシュを使用しないようにする必要があります。 MySQL には、クエリ キャッシュ専用の SQL オプションとして、SQL_NO_CACHE と SQL_CACHE の 2 つがあります。 query_cache_type が 1 (ON) に設定されている場合、SQL_NO_CACHE オプションを使用してクエリ キャッシュを強制的に使用しないようにすることができます。また、query_cache_type が 2 (DEMAND) に設定されている場合、SQL_CACHE オプションを使用してクエリ キャッシュを強制的に使用することができます。クエリ キャッシュを強制的に使用しないようにすることで、MySQL は頻繁に変更されるテーブルでクエリ キャッシュが使用されるのを防ぐことができ、メモリのオーバーヘッドだけでなく、ハッシュ計算と検索のオーバーヘッドも削減されます。

2. クエリ キャッシュとクエリ ステートメントの関係は何ですか?

MySQL が受信したクエリ ステートメントが単一テーブル SQL か複数テーブル SQL か、またはサブクエリが含まれているかどうかに関係なく、クエリとして扱われ、Union ステートメントを含め、キャッシュのために複数のクエリに分割されることはありません。

3. クライアントによって送信されたクエリ ステートメントの大文字と小文字は、クエリ キャッシュに影響しますか?

影響力がある。クエリ キャッシュはハッシュ構造を使用してメモリにマップされるため、ハッシュ アルゴリズムの基礎はクエリ ステートメントを構成する文字です。したがって、クエリ キャッシュにヒットするには、クエリ ステートメント全体が文字レベルで完全に一貫している必要があります。

4. どのような状況で、クエリ ステートメントのクエリ キャッシュにキャッシュされたクエリ結果が無効になりますか?

クエリ キャッシュの内容が実際のデータと完全に一致するように、テーブル内のデータに追加、変更、削除などの変更があった場合、テーブルを参照するすべてのクエリ キャッシュ キャッシュ データが無効になります。

5. クエリ キャッシュの断片化率はどれくらいですか?何の役に立つの？

クエリキャッシュの断片化率 = Qcache_free_blocks / Qcache_total_blocks * 100%
クエリ キャッシュの断片化率が 20% を超える場合は、FLUSH QUERY CACHE を使用してメモリをデフラグできます。クエリのデータ量がすべて少量の場合は、query_cache_min_res_unit を減らしてみてください。

6. クエリ キャッシュの使用率はどのくらいですか?何の役に立つの？

クエリキャッシュ使用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

クエリ キャッシュの使用率が 25% 未満の場合、query_cache_size の設定が大きすぎるため、適切に削減できることを意味します。クエリ キャッシュの使用率が 80% を超え、Qcache_lowmem_prunes > 50 の場合、query_cache_size が少し小さいか、メモリの断片化が多すぎることを意味します。

7. クエリ キャッシュのヒット率はどれくらいですか?何の役に立つの？

① キャッシュ可能なクエリのクエリキャッシュヒット率 = Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%
② すべてのクエリをカバーするクエリキャッシュヒット率 = Qcache_hits / (Qcache_hits + Com_select) * 100%

ヒット率が 50 ～ 70% の範囲内であれば、クエリ キャッシュのキャッシュ効率が高いことを示します。ヒット率が 50% を大幅に下回る場合は、クエリ キャッシュを無効にする (query_cache_type を 0 (OFF) に設定する) か、オンデマンドで使用する (query_cache_type を 2 (DEMAND) に設定する) ことをお勧めします。節約したメモリは InnoDB のバッファ プールとして使用できます。

8. クエリ キャッシュの空きメモリが不足しているか、メモリの断片化が多すぎるかどうかをどのように判断しますか?

Qcache_lowmem_prunes の値が大きい場合は、クエリ キャッシュのメモリ サイズが小さすぎるため、増やす必要があることを意味します。

Qcache_free_blocks 値が比較的大きい場合は、メモリの断片化が大量にあることを意味し、FLUSH QUERY CACHE ステートメントを使用してメモリの断片化をクリーンアップする必要があります。

9. query_cache_min_res_unit システム変数はどのくらいの大きさに設定すればよいですか?

query_cache_min_res_unit の計算式は次のとおりです。

query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

通常、クエリ キャッシュ サイズ (query_cache_size システム変数) を 256 MB より大きく設定することは推奨されません。

MySQLクエリキャッシュQuery_cacheの機能

MySQL のクエリ キャッシュは実行プランをキャッシュするのではなく、クエリとその結果セットをキャッシュします。つまり、同一のクエリ操作のみがキャッシュにヒットするため、MySQL のクエリ キャッシュ ヒット率は非常に低くなります。一方、結果セットが大きいクエリの場合、クエリ結果をキャッシュから直接読み取ることができるため、クエリの効率が効果的に向上します。

では、キャッシュ サイズはどのように設定するのでしょうか?見てみましょう:

1. MySQL クライアントのコマンドラインで、キャッシュが有効になっているかどうか、およびキャッシュ設定のサイズを確認できます。

mysql> '%query_cache%' のような変数を表示します。
+------------------------------+-------------+
| 変数名 | 値 |
+------------------------------+-------------+
| クエリキャッシュがある | はい |
| クエリキャッシュ制限 | 1048576 |
| クエリ キャッシュの最小解像度単位 | 4096 |
| クエリキャッシュサイズ | 16106127360 |
| クエリキャッシュタイプ | オン |
| query_cache_wlock_invalidate | オフ |
+------------------------------+-------------+
セット内の6行（0.01秒）

で：

query_cache_type: キャッシュ機能を有効にするかどうか。値はON、OFF、DEMANDで、デフォルト値はONです。
- 値が OFF または 0 の場合、クエリ キャッシュ機能は無効になります。
- 値が ON または 1 の場合、クエリ キャッシュ機能がオンになります。SELECT 結果は、キャッシュ条件を満たす場合にキャッシュされます。それ以外の場合はキャッシュされません。SQL_NO_CACHE が明示的に指定されている場合は、キャッシュされません。
- 値がDEMANDまたは2の場合、クエリキャッシュは要求に応じて実行され、SQL_CACHEを明示的に指定するSELECTステートメントのみがキャッシュされ、その他はキャッシュされません。

query_cache_wlock_invalidate: 他のクライアントが MyISAM テーブルに書き込んでいるときにクエリがクエリ キャッシュ内にある場合、キャッシュされた結果を返すか、書き込み操作が完了するまで待ってからテーブルを読み取って結果を取得するかを示します。

query_cache_limit は、単一のクエリで使用できるバッファ サイズを指定します。デフォルトは 1M です。

query_cache_min_res_unit は、システムによって割り当てられる最小のキャッシュ ブロック サイズです。デフォルト値は 4KB です。大きな値を設定すると、大規模なデータ クエリに適していますが、クエリがすべて小規模なデータ クエリである場合は、メモリの断片化と無駄が発生しやすくなります。query_cache_size: は、キャッシュのサイズを示します。

上記の指標を理解した後、それらを MySQL 構成ファイル my.cnf で設定できます。次に、mysl サーバーを再起動します。 [mysqld]の下にパラメータを追加します。通常は、query_cache_size と query_cache_type が設定されます。

2. 上記のビューはキャッシュ構成です。通常は構成ファイルから値を読み取りますが、MySQL の現在のデータ キャッシュ サイズをリアルタイムで表示する必要がある場合もあります。

mysql> '%qcache%' のようなステータスを表示します。
+-------------------------+-----------+
| 変数名 | 値 |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 16489053 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+-----------+
セット内の行数は 8 です (0.00 秒)

説明する：

Qcache_free_memory: キャッシュ内のメモリを解放します。

Qcache_total_blocks: キャッシュ内のブロック数。

Qcache_lowmem_prunes: キャッシュのメモリが不足し、クエリをさらに実行できるようにスペースを確保するためにキャッシュを削除する必要があった回数。この数値は、時間の経過とともに確認するのが最適です。数値が増加している場合は、非常に深刻な断片化またはメモリ不足を示している可能性があります。これを判断するには、Qcache_free_blocks と Qcache_free_memory という 2 つのインジケーターを確認する必要があります。

3. キャッシュをクリアします。

クエリキャッシュのフラッシュコマンド

キャッシュを増やすと、クエリの効率が向上します。

次に例を示します。

mysql> mob_adnを使用する
データベースが変更されました
mysql> creative_output から count(*) を選択します。
+----------+
| カウント(*) |
+----------+
|87151154|
+----------+
セット1列目（3分18.29秒）

mysql> creative_output から count(*) を選択します。
+----------+
| カウント(*) |
+----------+
|87151154|
+----------+
セット内の 1 行 (0.00 秒)

mysql> creative_output から count(*) を選択します。
+----------+
| カウント(*) |
+----------+
|87151154|
+----------+
セット内の 1 行 (0.00 秒)

キャッシュが非常に強力であることがわかります。

受講者の中には、innodb_buffer_pool というパラメータを思い浮かべる方もいるかもしれません。引き続き、この 2 つの機能と違いについてお話しします。

ちなみに、ここにいくつかの SQL 最適化を示します (非常に優れています)。

https://www.cnblogs.com/L-dongf/p/9163848.html

これで、画像とテキストによる MySQL Query Cache に関するこの記事は終了です。MySQL Query Cache に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM を応援していただければ幸いです。