MySQL クエリ キャッシュに関する記事を再投稿します。

キャッシュ|mysql

典型的なシナリオ
上司: 私たちの新しいウェブサイトはクロールしています!なんと、当社には最先端の Web サーバーが 4 台あります。何が問題なのでしょうか?
あなた: そうですね、Web サーバーは正常です。問題が発生しているのはデータベース サーバーです。
上司: えっ?あなたは、MySQL は高速で、Oracle は必要ないと私に言いましたが、今では対応できないと言いました。これはどういうことでしょうか?
あなた: そうですね、Web サーバーは非常に適切に動作しているため、大量のクエリを実行していますが、データベースはすべてのクエリを同時に処理することができません。データベースは 1 つだけで、Web サーバーはたくさんあります...
上司: 今から Oracle を買うのは遅すぎます - どうしますか!?
上司から上司へ (上司の心の中で): このプロジェクトは大失敗でした最初から - 新しいデータベースをインストールするまで延期して、さらに多くの費用を費やしてほしいとのことです。私たちはお金でできていると思いますか？彼らが何をしているのか知っている人を呼んでいます - あなたは歴史仲間です。
同僚 (あなたの仕事を引き受けようとしている): 待ってください、私は問題を解決できると思います!

それで、あなたの同僚はそれを何を知っているのですか?あなたはしない？どうすれば窮地を救って、上司にすべての手柄を任せることができるでしょうか?私たちのシナリオは一般化するには不正確すぎるため、考えられる解決策は多数あります。クエリとインデックスの最適化、ハードウェアの改善による最適化、遅いクエリ ログを使用した MySQL 変数の微調整について読むことができます。もちろん、レプリケーションなどの他の方法もあります。ただし、MySQL 4 には、クエリ キャッシュという非常に便利な機能が 1 つ提供されています。データベースが同じデータセットに対して同じクエリを繰り返し実行し、毎回同じ結果を返す必要がある状況では、MySQL は結果セットをキャッシュして、データを何度も実行するオーバーヘッドを回避できます。通常、Web サーバーに何らかのキャッシュを実装したいと考えますが、それが不可能な場合もあり、その場合に助けを求めるのがクエリ キャッシュです。

クエリ キャッシュのセットアップ
To make MySQL がクエリ キャッシュを使用していることを確認するには、構成ファイル (通常は my.cnf または my.ini) に設定する必要がある変数がいくつかあります。まず、query_cache_type です。可能な設定は 3 つあります: 0 (オフ、使用しない)、1 (オン、キャッシュ クエリ)、および 2 (オンデマンド、詳細は後述)。常にオンにするには、構成ファイルに

query-cache-type = 1
を配置します。この変更だけを行ってサーバーを起動すると、次のキャッシュ変数が設定されていることがわかります。 '%query_cache%' のような変数を表示します;
+--------+-----------+
|変数名 |値 |
+-------------------+---------+
| have_query_cache |はい |
|クエリキャッシュ制限 | 1048576 |
| query_cache_size | 0 |
| query_cache_type | ON |
+--------+---------+
セット内の 4 行 (0.06 秒)

これらは結果であることに注意してくださいMySQL 4.0.x 以降 - バージョン 4.1.x 以降ではさらに多くの機能が表示されます。 query_cache_type は、必要に応じて ON または OFF に設定されます。ただし、もう 1 つ設定する必要があります。それは、query_cache_size です。 0 (デフォルト) に設定すると、キャッシュは無効になります。この変数は、クエリ キャッシュに使用されるメモリをバイト単位で決定します。今回の目的のために、20 MB に設定します:

query-cache-size = 20M
量はバイト単位で表示されます:

mysql> '%query_cache%' のような変数を表示します;
+--------+----------+
|変数名 |値 |
+-------------------+----------+
| have_query_cache |はい |
|クエリキャッシュ制限 | 1048576 |
| query_cache_size | 20971520 |
| query_cache_type | ON |
+--------+----------+
セット内の 4 行 (0.06 秒)

のクエリ キャッシュ(ほぼ) アクション
このチュートリアルでは、オープン コンテンツ百科事典である Wikipedia のダンプを使用しました (ダンプはここで見つけることができます。結果への干渉を最小限に抑えるために、他に何も起こっていないかなり遅いマシンを使用しています。同じクエリを 2 回実行して、2 回目にどれだけ改善が見られるかを確認してみましょう:

SELECT * FROM cur;
...
14144 rows in set (2.96 sec)
次に、同じクエリを再度実行します:
SELECT * FROM cur; 14144 rows in set (3.02 秒)

今度は同じクエリを再度実行します:

SELECT * FROM cur; 14144 rows in set (3.02 sec)

2 番目のクエリは何が起こっているでしょうか?時間を大幅に短縮して、状況をより良く理解するためにいくつかのステータス変数を調べてみましょう

mysql>SHOW STATUS LIKE '%qcache%';
+------ -------+----------+
| 変数名 | 値+-------------------------------------+----------+
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 2 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_free_memory | 20962720 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 行セット (0.00 秒)

実行した 2 つのクエリは両方とも (Qcache_inserts によって) 記録されますが、どちらもキャッシュされていません。 (他のクエリが実行されている場合は、異なる結果が得られる可能性があります。) 問題は、結果セットが大きすぎることです。私は Wikipedia のエスペラント語ダンプを使用しました (4MB 圧縮 - 英語のダンプは 135MB で、私の英語はエスペラント語より優れていますが、南アフリカでは帯域幅が高価です!) が、それでもクエリ キャッシュよりも大きいため、重要ではありません。デフォルトで扱えます。ここでは 2 つの制限が影響します。個々のクエリの制限は、query_cache_limit の値によって決まります。デフォルトでは 1MB です。さらに、キャッシュの合計の制限は、すでに見た query_cache_size によって決まります。ここでは前の制限が適用されます。結果セットが 1M を超える場合、結果セットはキャッシュされません。

動作中のクエリ キャッシュ (実際)
より小さなクエリを試してみましょう:

SELECT cur_is_new FROM cur WHERE cur_user_text > 'Y'
...
セット内の 2336 行 (0.38 秒)

これがキャッシュされたかどうか見てみましょう:

mysql> '%qcache%' のようなステータスを表示;
+-------------------------+----------+
|変数名 |値 |
+-------------------------+----------+
| Qcache_queries_in_cache | 1 |
| Qcache_inserts | 3 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_free_memory | 20947592 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 行セット (0.00 秒)

現在、キャッシュにクエリがあります。初回の実行に 0.38 秒かかった場合、2 回目の実行で改善が見られるかどうかを確認してみましょう。

SELECT cur_is_new FROM cur WHERE cur_user_text > 「Y」
...
セット内 2336 行 (0.11 秒)

かなり良くなりました!そして、ステータスをもう一度見てみましょう:

mysql> '%qcache%' のようなステータスを表示;
+-------------------------+----------+
|変数名 |値 |
+-------------------------+----------+
| Qcache_queries_in_cache | 1 |
| Qcache_inserts | 3 |
| Qcache_hits | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_free_memory | 20947592 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 行セット (0.06 秒)

キャッシュが 1 回ヒットしました。上記のステータス変数は一目瞭然です。キャッシュに使用可能なメモリは 20962720 バイトから 20947592 バイトになりました。将来のチューニングに最も役立つ変数は Qcache_lowmem_prunes です。キャッシュされたクエリがクエリ キャッシュから削除されるたびに (MySQL は別のクエリ用のスペースを確保する必要があるため)、この値は増加します。急速に増加し、まだメモリに余裕がある場合は、query_cache_size を増やすことができます。一方、増加しない場合は、キャッシュ サイズを減らすことができます。

次のように、若干の違いを加えてクエリを再度実行してみましょう。

SELECT cur_is_new from cur where cur_user_text > 「Y」
...
セット内 2336 行 (0.33 秒)

それには予想以上に時間がかかりました。ステータス変数を見て、何が起こっているかを確認してみましょう。

mysql> '%qcache%' のようなステータスを表示;
+-------------------------+----------+
|変数名 |値 |
+-------------------------+----------+
| Qcache_queries_in_cache | 2 |
| Qcache_inserts | 4 |
| Qcache_hits | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_free_memory | 20932976 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 6 |
+-------------------------+----------+

クエリはキャッシュ - 実際、MySQL は別のクエリをキャッシュに挿入しました。ここでの問題は、MySQL のクエリ キャッシュでは大文字と小文字が区別されることです (実際にはバイトが区別されます)。クエリはあらゆる点で同一である必要があります。余分なスペースや大文字と小文字の違いはありません。したがって、上記のクエリは別のクエリとして扱われます。この事実だけでも、特定の規則を採用し、すべてのアプリケーション開発者がそれを確実に利用できるようにするには十分です。 MySQL キーワードには大文字を使用し、テーブル名とフィールド名には小文字を使用します。

クエリキャッシュのクリア
キャッシュはメモリ内に無期限に存在することはできません。幸いなことに、MySQL は、キャッシュ クエリで使用されるテーブルに変更を加えたときに、キャッシュ クエリをクリアできるほど賢いです。新しいレコードを cur テーブルに挿入すると、MySQL は影響を受けるクエリ (および影響を受けるクエリのみ) をキャッシュからクリアします。 INSERT INTO cur(cur_user_text)
VALUES ('xxx');
クエリ OK、1 行が影響を受けました (0.06 秒)

mysql> '%qcache%' のようなステータスを表示;
+-------------------------+----------+
|変数名 |値 |
+-------------------------+----------+
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 4 |
| Qcache_hits | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_free_memory | 20962720 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+

INSERT、UPDATE、DELETEのいずれか、TRUNCATE、ALTER、DROP TABLE、または DROP DATABASE を使用すると、キャッシュからクエリが削除される可能性があります。 RESET QUERY CACHE を使用してクエリ キャッシュを手動でクリアできます。

オンデマンドのクエリ キャッシュ
先ほど、query_cache_type には 3 つの値があることがわかりました。オン、オフ、オンデマンド。後者のオプションは、クエリで SQL_CACHE が指定されている場合にのみクエリがキャッシュされることを意味します。構成で

query-cache-type = 2
を使用してサーバーを再起動しましょう。サーバーを再起動すると、すべてのステータス変数がフラッシュされます。前のクエリを再度実行します。

SELECT cur_is_new FROM cur WHERE cur_user_text > 'Y'
...
セット内 2336 行 (0.27 秒)

キャッシュがフラッシュされたため、再び長い時間に戻りました。

mysql> '%qcache%' のようなステータスを表示;
+-------------------------+----------+
|変数名 |値 |
+-------------------------+----------+
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_free_memory | 20962720 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+

何も記録されていません。クエリをキャッシュに保存するには、次のように SQL_CACHE を使用してクエリを実行する必要があります:

SELECT SQL_CACHE cur_is_new FROM cur WHERE cur_user_text > 'Y'
...
セット内の 2336 行 (0.33 秒)

今回はキャッシュに保存されました。

mysql> '%qcache%' のようなステータスを表示;
+-------------------------+----------+
|変数名 |値 |
+-------------------------+----------+
| Qcache_queries_in_cache | 1 |
| Qcache_inserts | 1 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_free_memory | 20947592 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+

キャッシュ タイプが次のように設定されている場合1、実行しているクエリが繰り返されないか、その頻度が低いことがわかっている場合があります。このような状況では、SELECT ステートメントの SQL_NO_CACHE 句を使用して、サイズ制限に従っている場合でも、結果をキャッシュに保存しないように MySQL に依頼できます。

ブロック割り当てとクエリキャッシュ
MySQL は、取得中に結果をブロック単位でキャッシュに割り当てます。この割り当てにはオーバーヘッドが発生します (キャッシュされていない場合、上記のクエリの実行時間が短縮されたことを参照してください)。クエリ キャッシュを有効に活用できない限り、クエリ キャッシュを有効にしないでください。フリー ブロックの数 (Qcache_free_blocks) は断片化を示す可能性があります。ブロックの総数と比較して数値が高い場合は、スペースが無駄に使用されていることを意味します。 MySQL 4.1 には、別のキャッシュ関連変数、query_cache_min_res_unit があります。これにより、最小ブロック サイズを設定できます。デフォルトは 4KB です。ほとんどのクエリ結果が小さく、断片化が見られる場合は、この値を減らす必要があります。ほとんどの結果セットが大きい場合は、逆のことが当てはまります。クエリ キャッシュをデフラグするには、FLUSH QUERY CACHE を使用できます (FLUSH TABLES はキャッシュに対して同じ効果があります)。

クエリをキャッシュできない状況があります。現在時刻、乱数、ユーザー変数を返す場合、またはファイルにダンプする場合など、これらはすべて完全に理にかなっています。次の関数または次のタイプを使用するクエリはキャッシュされません:

ユーザー定義関数
BENCHMARK
CONNECTION_ID
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATABASE
ENC RYPT (パラメーターが 1 つあり)
FOUND_ROWS
GET_LOCK
LAST_INSERT_ID
LOAD_FILE
MASTER_POS_WAIT
NOW
RAND
RELEASE_LOCK
SYSDATE
UNIX_TIMESTAMP (パラメータなし)
USER
クエリにはユーザー変数が含まれます
クエリmysql システム データベースを参照します
SELECT ... IN SHARE MODE 形式のクエリ
SELECT ... INTO OUTFILE ... 形式のクエリ
形式 SELECT ... INTO DUMPFILE ...
形式のクエリ SELECT * FROM AUTOINCREMENT_FIELD IS NULL
トランザクション内のクエリ (MySQL 4.0.x の場合)
クエリ キャッシュを賢く使用すると、問題が発生しているアプリケーションに大きな違いをもたらすことができます。幸運を！