MySQL Cache メモ


メモと言う名のコピペ

ちょっと古い、5.1 だけど日本語ドキュメントがそこしか無かったのと、英語読む気力ないけど、とりあえずめも

http://dev.mysql.com/doc/refman/5.1/ja/query-cache-how.html

## 動作とかごにょごにょ

クエリ キャッシュは、SELECT SQL_CALC_FOUND_ROWS ... のクエリで動作し、後続する SELECT FOUND_ROWS() クエリで返る値を格納します。FOUND_ROWS() は前のクエリがキャッシュからフェッチしていても、正確な値を返します。これは、検索したレコードの数をキャッシュで保管しているためです。SELECT FOUND_ROWS() クエリ自体はキャッシュの対象ではありません。

クエリをキャッシュする設定である場合、その結果 (クライアントに送信したデータ) を、結果の読み出し中に、クエリ キャッシュに格納します。そのため、データの扱いは、ひとまとめではありません。つまり、クエリ キャッシュで、データをブロックに分割するため、1 つのブロックが埋まれば、次のブロックを埋めることになります。これは、メモリの割り当てに時間がかかるため、クエリ キャッシュではブロックにします。そのときのブロックのサイズを決定するのが、 ```query_cache_min_res_unit``` 変数です。

キャッシュするクエリには、少なくとも 2 つのブロックを必要とします。1 つはクエリ テキスト用で、もう 1 つはクエリ結果用です。さらに、もう 1 つ、テーブルのクエリ要求用にもブロックを必要とします。ただし、複数のクエリで同じテーブルを使用している場合は、1 ブロックの割り当てで済みます。


### Query の大文字小文字に注意

クエリは、バイト同士など、```完全に一致```しない限り、同一とは判断しません。

解析前のクエリには、解釈が始まる前にクエリ キャッシュにあるクエリとの照合を行います。そのため、次の 2 つのクエリは、クエリキャッシュで異なるものである、とみなします。

```
SELECT * FROM tbl_name
Select * from tbl_name
```

## キャッシュを使う所

1. InnoDB テーブルを使用するトランザクションでもクエリ キャッシュを使用します
2. MySQL 5.1 では、ビューで生成したクエリもキャッシュします。

### Select Statement

Select文でキャッシュを使うか使わないかを選択できる

- SQL_CACHE
	query_cache_type 環境変数の値が、DEMAND または ON のときは、クエリ結果をキャッシュします。

- SQL_NO_CACHE
	クエリ結果をキャッシュしません。

例

```
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;
```

## キャッシュしない条件

1.  クエリ キャッシュは、解釈が始まる前にクエリ同士を照合することから、次のような種類のクエリはキャッシュの対象になりません。

	1. 準備されたステートメント (準備文)
	2. クエリが外部クエリのサブクエリである場合
	3. Stored プロシージャ、Stored 関数、トリガ、イベントなどのボディ内で実行したクエリ

2. クエリ結果をキャッシュからフェッチする前に、MySQL で、そのユーザがすてべのデータベースと関連するテーブルにおいて、 SELECT 権限があるかどうかを調べます。権限がない場合は、キャッシュ結果は使用しません。

3. テーブルに変更があった場合、そのテーブルからキャッシュしたクエリのすべてが無効になるため、キャッシュからは削除します。変更があったクエリのマップである MERGE テーブルを使用するクエリも削除の対象になります。INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE、DROP DATABASE など、様々なステートメントでテーブルは変化します。

4. 一部関数を含むクエリはキャッシュしない
	BENCHMARK()
	CONNECTION_ID()
	CURDATE()
	CURRENT_DATE()
	CURRENT_TIME()
	CURRENT_TIMESTAMP()
	CURTIME()
	DATABASE()
	ENCRYPT() (パラメータなし)
	FOUND_ROWS()
	GET_LOCK()
	LAST_INSERT_ID()
	LOAD_FILE()
	MASTER_POS_WAIT()
	NOW()
	RAND()
	RELEASE_LOCK()
	SYSDATE()
	UNIX_TIMESTAMP() (パラメータなし)
	USER()

5. ユーザ定義関数 (UDF) または格納された関数 (stored functions) を指す場合

6. ユーザ変数を指す場合 （よくわからん、指すって？使う場合じゃなくて？）

7. mysql システム データベースのテーブルを指す場合

8. 次のようなSQL
SELECT ... IN SHARE MODE
SELECT ... FOR UPDATE
SELECT ... INTO OUTFILE ...
SELECT ... INTO DUMPFILE ...
SELECT * FROM ... WHERE autoincrement_col IS NULL

9. 準備されたステートメントとして発行した場合。プレースホルダを採用していない場合も同様。例として、次のようなクエリはキャッシュにならない。(よくわからん)

```
char *my_sql_stmt = "SELECT a, b FROM table_c";
/* ... */
mysql_stmt_prepare(stmt, my_sql_stmt, strlen(my_sql_stmt));
```

10. TEMPORARY テーブルを使用している場合
11. テーブルを全く使用しない場合
12. 関連テーブルのすべてに対して、ユーザがカラム レベルの権限を持つ場合

## メンテとか

1. クエリ結果がキャッシュから返る度に、サーバは Qcache_hits システム変数の値を増加します。Com_select ではありません


### 設定

変数の設定は別途参照。クエリキャッシュのシステム変数名はすべて、```query_cache_``` という文字で始まります。

####  have_query_cache

have_query_cache というサーバのシステム変数は、クエリ キャッシュの利用可能にします。（通常時は YES）

```
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
```

#### query_cache_size
クエリ キャッシュのサイズを設定するには、query_cache_size システム変数を使用します。値を 0 にすると、クエリ キャッシュは無効化します。デフォルトは 0 で設定しています。
query_cache_size の値がゼロではない場合は、ストラクチャのアロケートにおよそ 40 KB を必要とするため、クエリ キャッシュのサイズを最低 40 KB でセットしてください。正確なサイズは、システムのアーキテクチャによります。サイズが小さすぎると、警告がでます。

query_cache_size は、1024 バイトに近い値のブロックで位置合わせしています。そのため、報告の値は、設定したものとは異なる可能性があります。


#### query_cache_type

- 0 または OFF という値で、キャッシュを行わない、または キャッシュした結果の読み出しを行わない、という効果になります。
- 1 または ON という値で、SELECT SQL_NO_CACHE で始まるステートメント以外のキャッシュになります。
- 2 または DEMAND という値で、SELECT SQL_CACHE で始まるステートメントだけのキャッシュになります。

#### GLOBAL query_cache_type と query_cache_limit

変更後に接続するクライアントに対するクエリ キャッシュの動作を指定できます。SESSION query_cache_type の値を設定すると、それぞれのクライアントで接続時のキャッシュ動作を制御できます。

キャッシュしたそれぞれのクエリ結果の最大サイズの制御は、query_cache_limit 変数で行います。デフォルトは 1 MB です。

#### query_cache_min_res_unit と Qcache_free_blocks と Qcache_lowmem_prunes

クエリをキャッシュする設定である場合、その結果 (クライアントに送信したデータ) を、結果の読み出し中に、クエリ キャッシュに格納します。そのため、データの扱いは、ひとまとめではありません。つまり、クエリ キャッシュで、データをブロックに分割するため、1 つのブロックが埋まれば、次のブロックを埋めることになります。これは、メモリの割り当てに時間がかかるため、クエリ キャッシュではブロックにします。そのときのブロックのサイズを決定するのが、 ```query_cache_min_res_unit``` 変数です。

クエリ実行毎に、ブロックはサイズでトリムすることになるので、メモリを節約できます。


- ```query_cache_min_res_unit``` のデフォルト値は、4 KB です。大抵の場合、これで十分です。
- 結果が小さいクエリが多い場合は、フリーのブロックが多く存在することになり、デフォルトのブロック サイズはメモリのフラグメントになります。フラグメントは、メモリ不足を解消するために、キャッシュからクエリを取り除く (削除) 動作を強制的に行います。そのため、query_cache_min_res_unit の値を減らす必要が出てきます。フリー ブロックの数は Qcache_free_blocks を、そして、この動作で強制的に削除の対象になったクエリは Qcache_lowmem_prunes を、それぞれのステータス変数で確認してください。
- クエリの大部分が大きな結果である場合は、query_cache_min_res_unit で値を増やして、パフォーマンスを改善できます。(Qcache_total_blocks および Qcache_queries_in_cache で、ステータス変数を確認してください。) しかし、値を増やすと、前述のようにメモリ不足の状態になるので、注意が必要です。

### 保守

#### クエリキャッシュが有効になっているか確認する


```
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
```

#### RESET QUERY CACHE

RESET QUERY CACHEステートメントは、クエリ キャッシュからクエリ結果を削除します。FLUSH TABLES ステートメントでも同様のことができます。

メモリ不足対策で、クエリ キャッシュのフラグには、FLUSH QUERY CACHE ステートメントを使用します。このステートメントでは、キャッシュからクエリが消えることはありません

#### パフォーマンスを監視

SHOW STATUS を使用して、キャッシュのステータス変数をみます。

```
sql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 36     |
| Qcache_free_memory      | 138488 |
| Qcache_hits             | 79570  |
| Qcache_inserts          | 27087  |
| Qcache_lowmem_prunes    | 3114   |
| Qcache_not_cached       | 22989  |
| Qcache_queries_in_cache | 415    |
| Qcache_total_blocks     | 912    |
+-------------------------+--------+
```

1. SELECT クエリの合計数は、次の計算式で求めます。

```
  Com_select
+ Qcache_hits
+ queries with errors found by parser
```

2. Com_select 値は次の計算式で求めます。

```
  Qcache_inserts
+ Qcache_not_cached
+ queries with errors found during the column-privileges check
```

クエリ キャッシュでは、変数長さのブロックを使用するため、クエリ キャッシュのメモリ フラグメンテーションは、```Qcache_total_blocks``` および ```Qcache_free_blocks``` で確認できます。FLUSH QUERY CACHE 後には、フリーのブロックが 1 つになります。

#### Qcache_lowmem_prunes システム変数

Qcache_lowmem_prunes システム変数の情報は、クエリのキャッシュ サイズを調節するときに役立ちます。この変数は、新しいクエリのキャッシュを入れるために取り除かれたクエリの数をカウントしています。クエリ キャッシュでは、古い順番にクエリをキャッシュから削除 (LRU) します。