本稿では MySQL Server のクエリキャッシュ機能について、概要と導入方法、更には導入後の評価方法について解説します。
MySQLのクエリキャッシュ機能とは？
そもそもキャッシュ機能とは、一度読み込んだデータを一定のルールで保持し、また同じデータが必要になった場合はその保持しているデータを再利用することで、再読込みを行わなくする技術です。 一度ディスクなどから読み込んだデータをメモリなどで保持することを「キャッシュする」と言います。
MySQLのクエリキャッシュ機能もそれの延長で、データベースを参照(SELECT)した結果をメモリにキャッシュし、次に同一の参照が行われた場合に前回メモリにキャッシュした結果を返却する機能です。 1回目の参照ではデータをディスクから読み込みますのでディスクI/Oが発生しますが、2回目以降はディスクにアクセスせずにメモリ上にキャッシュされた結果を返却するので、2回目以降の参照ではマシンに負荷がかからず、かつ高速に応答できます。
また、MySQLデータベースは複数のセッションが同時にログインできますが、クエリキャッシュされたデータは異なるセッションでも共有されます。
ただし、データベースに保存されたデータが変更された場合に、キャッシュされた古い結果が返却されては困りますので、テーブルに INSERT, UPDATE, DELETE などで行われて更新された場合は、そのテーブルに関連付く参照結果はキャッシュからフラッシュ(削除)されます。 次回にそのテーブルへの参照が行われた場合は、またディスクへデータを読み込み、新たな結果をキャッシュします。
ここでの要点
- クエリキャッシュ機能とは、参照(SELECT)の結果をメモリに保存する機能。
- 同一の参照が行われた場合は、メモリに保存した結果を返却する。
- キャッシュはセッション間で共有される。
- テーブルのデータが変更されると、そのテーブルに関連するキャッシュがフラッシュ(削除)される。
クエリキャッシュを導入するメリット
クエリキャッシュ機能を導入するメリットは、前述のとおり以下の2点です。
- ディスクI/Oなどのマシン負荷が軽減される。
- ディスクI/Oや探索処理が減るため、同一の参照(SELECT)に対する応答が早くなる。
これだけ見ると「今すぐに導入しよう！」と思えてしまいますが、もう少し辛抱して記事を読み進めてください。
クエリキャッシュ導入に向いているシステムとは？
クエリキャッシュ機能の導入は、システムの特徴によって「向いているシステム」と「向いていないシステム」があります。 そもそもキャッシュというのは、「また後で同一のデータが参照されること」が分かっているから意味があります。 逆に、それ以後に同じデータへの参照が行われないのであれば意味がありません。 つまり「同一のデータ参照が多く発生するシステム」ほど向いているのであり、「同一のデータ参照が全然発生しないシステム」は向いていません。
また上記のとおり、MySQL のクエリキャッシュはテーブルの更新が行われるとキャッシュされたデータは削除されてしまいます。 つまりテーブルの更新頻度が高いシステムは、なかなかキャッシュが溜まらない(すぐにキャッシュが削除されてしまう)ので意味がありません。 さらにはテーブルの更新処理が発生した時点で「キャッシュの削除」という処理が発生するので、場合によってはクエリキャッシュ機能を導入する前よりもサーバーの負荷が高まる可能性もあります。
メモリに保存されたキャッシュデータが参照される確率を「キャッシュのヒット率」と言いますが、キャッシュのヒット率が高いシステムほど導入に向いていると言えます。
ここでの要点
- 同一の参照(SELECT)が多発するシステムは、クエリキャッシュの恩恵を受けやすい。
- 同一の参照がほとんど行われないシステムは、クエリキャッシュの恩恵を受けられない。
- テーブルの更新頻度が高いシステムはキャッシュヒット率が高まらず、逆にMySQLの負荷が高まる可能性もある。
クエリキャッシュを利用する場合の注意点
さて、MySQL のクエリキャッシュ機能が「同一の参照（SELECT)である」と見なす場合はどんな時でしょうか？ それは、SELECT文が一字一句異なっていない場合です。 これはどういうことかというと、大文字・小文字、半角スペースの数なども同一であるということです。 ですので、SELECT文の実行結果が全く同一の参照でも、別の参照だとみなされてしまう点には注意が必要です。 クエリキャッシュのヒット率を高めるには、次のことに考慮してください。
- 大文字・小文字の違いがないこと
- 半角スペースやタブなどの数に違いがないこと
- WHERE 条件の順序も同一であること
クエリキャッシュにキャッシュされないもの
また参照の処理なのにも関わらず、結果がキャッシュされないものもありますので知っておく必要があります。 どのようなものかといえば、毎回同じ SELECT 文を発行していてもタイミングなどによって結果が毎回変化する可能性がある場合です。 例えば現在時刻を取得する関数( CURRENT_DATE() )を利用している場合などは、現在時刻は刻々と変わるわけなので実行結果がタイミングによって変化する可能性があります。 よってクエリキャッシュは結果をキャッシュしません。 次のような関数を SELECT 文の中で利用しているとキャッシュしません。
- 時刻系関数 : CONVERT_TZ(), CURDATE(), CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP() , CURTIME(), NOW(), SYSDATE()
- 暗号系関数 : AES_DECRYPT(), AES_ENCRYPT()
- ランダム系関数 : RAND(), RANDOM_BYTES()
- システム系関数 : USER(), UUID() , UUID_SHORT(), PASSWORD()
※すべての関数は、MySQLの公式ドキュメントなどをご覧ください。
また、他にも細かい条件があります。 例えば、ユーザー定義関数やストアドファンクションを参照している場合、ロックを伴う参照などです。
クエリキャッシュの設定
それでは、MySQL にクエリキャッシュを設定する方法について解説したいと思います。
現在の状態の確認
まずはご自分の MySQL のクエリキャッシュの状況が現在どうなっているのかを確認してみましょう。 まずは MySQL の環境変数 have_query_cache という変数を確認してみましょう、名前の通り「クエリキャッシュを保持しているか」を表します。 また、MySQL の状態は SHOW STATUS 文で確認でき、クエリキャッシュに関する統計情報は Qcache という名前で始まるステータスで確認できますので LIKE ‘Qcache%’ という条件をつけて、次のように SQL を実行します。 (MySQLの管理者権限があるユーザーで実行します)
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | NO |
+------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.05 sec)
上の例では、クエリキャッシュは保持しておらず、統計情報もすべての値が「0」になっています。 結果として表示されたステータスの意味は、それぞれ次のようになります。
| Qcache_free_blocks | クエリーキャッシュ内の空きメモリーブロックの数。 |
|---|---|
| Qcache_free_memory | クエリーキャッシュ用の空きメモリーの量。 |
| Qcache_hits | クエリーキャッシュヒットの数。 |
| Qcache_inserts | クエリーキャッシュに追加されるクエリーの数。 |
| Qcache_lowmem_prunes | メモリーが少ないためクエリーキャッシュから削除されたクエリーの数。 |
| Qcache_not_cached | 非キャッシュクエリーの数 (キャッシュできないか、query_cache_type 設定のためキャッシュされない)。 |
| Qcache_queries_in_cache | クエリーキャッシュ内に登録されたクエリーの数。 |
| Qcache_total_blocks | クエリーキャッシュ内のブロックの合計数。 |
クエリキャッシュを有効にしたときに、例えば「Qcache_hits」の値が高まればキャッシュが利用されたことがわかりますので、これらのステータスを確認することは、クエリキャッシュの有効性を確認する上でとても重要です。
クエリキャッシュを有効にする
それでは、クエリキャッシュを有効にしてみましょう。 設定は my.cnf (Windows の場合は my.ini) の [mysqld] ブロックに記述します。 クエリキャッシュに関する変数には、以下のようなものがあります。
| query_alloc_block_size | ステートメントの解析および実行中に作成されるオブジェクトに対して割り当てられるメモリーブロックの割り当てサイズ。 |
|---|---|
| query_cache_limit | このバイト数より大きい結果をキャッシュしない。デフォルト値は 1M バイト。 |
| query_cache_min_res_unit | クエリーキャッシュによって割り当てられたブロックの最小サイズ (バイト単位)。デフォルト値は 4096 (4K バイト)。 |
| query_cache_size | クエリー結果をキャッシュするために割り当てられたメモリーの量。 これはデフォルト値の 1M。 |
| query_cache_type | クエリーキャッシュタイプを設定します。 (※下記で詳細に説明します) |
| query_cache_wlock_invalidate | テーブルに対する WRITE ロックが獲得された場合、そのテーブルを参照するクエリーキャッシュ内のすべてのクエリーを無効化するか否か。 |
| query_prealloc_size | ステートメントの解析および実行に使用される永続バッファーのサイズ。 |
一番重要な設定は「query_cache_type」です。 この変数でクエリキャッシュを有効にします。 この変数には 0～2 の値を設定でき、それぞれで動きが変わってきます。
| 0 | クエリキャッシュ機能をOFFにする。 |
|---|---|
| 1 | すべての参照クエリーの結果をキャッシュする。 ただし “SELECT SQL_NO_CACHE” で始まるクエリはキャッシュしない。 |
| 2 | “SELECT SQL_CACHE” で始まる参照クエリのみキャッシュする。 指定がないクエリはキャッシュしない。 |
クエリキャッシュ機能を有効にするには「query_cache_type」を「1」もしくは「2」に設定します。 何も考えずにできる限りのクエリをキャッシュしたいのであれば「1」にすればよいでしょう。
ここでは以下のように設定しました。 クエリキャッシュの機能を有効にし、キャッシュに利用するメモリに32MB割当てる設定にしました。 他の設定値についてはデフォルトのままとするので記述しません。
query_cache_type=1
query_cache_size = 32M
設定を有効にするには、MySQL の再起動が必要ですので、忘れずに再起動をしてください。
設定後の状態の確認
これでクエリキャッシュ機能が有効になったはずですので、また環境変数とステータスを確認する SQL を実行してみましょう。 筆者の手元の環境では、今度は次のように表示されました。
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 17 |
| Qcache_free_memory | 33322008 |
| Qcache_hits | 571 |
| Qcache_inserts | 120 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 27 |
| Qcache_queries_in_cache | 78 |
| Qcache_total_blocks | 199 |
+-------------------------+----------+
8 rows in set (0.00 sec)
有効性の検証
上で説明したとおり、クエリキャッシュはキャッシュヒット率が高まらないと意味がありません。 ですので、クエリキャッシュの設定を変更してからしばらくは定期的にキャッシュヒット率を確認するのが良いでしょう。 次の SQL でキャッシュヒット率を計算することができます。
SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'QCACHE_HITS')/(SELECT SUM(VARIABLE_VALUE)
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME IN ('QCACHE_HITS','QCACHE_INSERTS','QCACHE_NOT_CACHED'))*100 AS CACHE_HIT_RATE;
この計算結果が 100(%) に近ければ近いほどクエリキャッシュが有効であると言えます。 SQL の実行結果の例としては次のように表示され、次の例だとキャッシュヒット率が 81.2% になっていて、かなりの高確率でキャッシュが利用されているのがわかります。
+------------------+
| CACHE_HIT_RATE |
+------------------+
| 81.2169312169312 |
+------------------+
1 row in set (0.00 sec)
キャッシュヒット率が高まらない理由
クエリキャッシュを有効にしたにも関わらずキャッシュヒット率が高まらない場合、どのような理由が考えられるでしょうか？ まずは上で述べたように、そもそもシステムがクエリキャッシュに向いたシステムではないことがあります。 テーブルの更新処理が多い、または同一のデータ参照が発生する確率が低いため、なかなかキャッシュが利用されることがない場合です。 まずはご自身のシステムがどのような特徴があるのかを理解する必要があるでしょう。
また、違う理由としては メモリの割当量が足りないことがあげられます。 上で説明した query_cache_size 変数で設定したメモリの量が少ないため、同一の参照クエリを発行しているにも関わらず結果がキャッシュされていない(もしくはすぐに消されてしまっている)場合があります。 Qcache_free_memory の値がフリーのメモリ割当量ですがメモリを使い果たしているようであれば、query_cache_size の値を大きくしてメモリ割当量を増やします。
ここでの要点
- テーブルの更新頻度が多いのでキャッシュされない。
- 同一の参照クエリが発行される確率が低い。
- クエリキャッシュ機能に割り当てたメモリ量が少ない。
おわりに
本稿では MySQL のクエリキャッシュ機能について解説しました。 データ参照がメインとなるシステムでは、パフォーマンスの向上にとても有効な機能ですので是非覚えておきたいです。
