キャッシュの効果を体感してみる！～MySQLのクエリーキャッシュ～

2022年4月5日 09:02

はじめに

こんにちは、SHIFT の開発部門に所属している Katayama です。

Web アプリの構築の際、速度面の向上をする事を考えた時にキャッシュを使うというのは良く行われると思います。色々なレイヤーでキャッシュを利用するように設定できますが、今回は DB のクエリキャッシュについて実際に手元で動かしてその違いを体感してみました。

※注意
公式に書かれている通り、バージョン 8.0 からは使えない機能です（以下、公式からの引用）。

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.（クエリキャッシュは MySQL 5.7.20 で非推奨となり、MySQL 8.0 で削除されました。）

事前準備

MySQL サーバの設定

今回は Virtualbox 上の Centos7.9 で docker-compose を利用して MySQL のサーバを立ち上げる。docker-compose での MySQL サーバの立ち上げ方法はdocker-compose で MySQL Server を起動し操作するを参照。

クエリーキャッシュを有効化するための設定や、キャッシュするクエリーの追加オプションがあるのでそれについて設定する。設定は"my.cnf"で行うので、以下のようになる。

## my.cnf
[mysqld]
query_cache_type = 2 # DEMANDでもいい
query_cache_limit = 1024M
query_cache_size = 2048M

[client]
default-character-set = utf8mb4

設定内容としては、以下の通り（この辺りの詳細は参考に示している公式を参照）。

・"query_cache_limit"：デフォルトだと 1MB 以上はキャッシュされないのでそれを上書き
・"query_cache_size"：デフォルト 1MB なので上書き
・"query_cache_type"：はキーワード"SQL_CACHE"のあるもののみクエリーキャッシュを利用するようにするための設定

この"my.cnf"を docker-compose で立ち上げる際に読み込ませたいので、docker-compose.yaml は以下のようになる（初期データを入れるための SQL を entrypoint に渡す設定もしている）。

version: "3.9"
services:
  mysql:
    image: mysql:5.7.36
    container_name: mysql
    environment:
      MYSQL_ROOT_PASSWORD: ""
      MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
      TZ: "Asia/Tokyo"
    volumes:
      - ./data/mysql:/var/lib/mysql
      - ./sql:/docker-entrypoint-initdb.d
      - ./config/my.cnf:/etc/mysql/conf.d/my.cnf
    ports:
      - 3306:3306

[root@localhost learn-cache]# tree -I data
.
├── config
│   └── my.cnf
├── docker-compose.yaml
├── sql
│   ├── 1_schema.sql
│   └── 2_data.sql
...

・参考：query_cache_type
・参考：query_cache_limit
・参考：query_cache_size

データの用意

今回はデータを 100 万件用意する事にした。単純結合をすれば話は早いと思ったので以下のような SQL を実行してデータを作成した。

INSERT INTO `test` VALUES (1,'山田 太郎','tarou.yamada@example.com'),(2,'山田 花子','hanako.yamada@example.com'),(3,'鈴木 太郎','tarou.suzuki@example.com'),(4,'鈴木 花子','hanako.suzuki@example.com'),(5,'佐藤 太郎','tarou.satou@example.com'),(6,'佐藤 花子','hanako.satou@example.com'),(7,'高橋 太郎','tarou.takahashi@example.com'),(8,'高橋 花子','hanako.takahashi@example.com'),(9,'田中 太郎','tarou.tanaka@example.com'),(10,'田中 花子','hanako.tanaka@example.com');

INSERT INTO test(SELECT test.id = NULL, test.name, test.email FROM test, test test2, test test3, test test4, test test5, test test6);

やっている事は、

① 元データとなるデータを 10 件登録
② 単純結合で 10 の 6 乗をすれば 100 万になるので、6 テーブル結合してその全部を insert

という事だけ。実際に実行すると以下のようにちゃんと 100 万件（最初の 10 件があるので正確には 100 万 10 件）のデータがある事が分かる。

mysql> INSERT INTO test(SELECT test.id = NULL, test.name, test.email FROM test, test test2, test test3, test test4, test test5, test test6);
Query OK, 1000000 rows affected (38.28 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM test;
+----------+
| COUNT(*) |
+----------+
|  1000010 |

※ちなみに、単純結合した際の SQL の実行結果は以下のような感じになる。本当に単純にカラムをくっつけているだけ。

mysql> SELECT * FROM test, test test2 LIMIT 10;
+----+---------------+------------------------------+----+---------------+--------------------------+
| id | name          | email                        | id | name          | email                    |
+----+---------------+------------------------------+----+---------------+--------------------------+
|  1 | 山田 太郎     | tarou.yamada@example.com     |  1 | 山田 太郎     | tarou.yamada@example.com |
|  2 | 山田 花子     | hanako.yamada@example.com    |  1 | 山田 太郎     | tarou.yamada@example.com |
|  3 | 鈴木 太郎     | tarou.suzuki@example.com     |  1 | 山田 太郎     | tarou.yamada@example.com |
|  4 | 鈴木 花子     | hanako.suzuki@example.com    |  1 | 山田 太郎     | tarou.yamada@example.com |
|  5 | 佐藤 太郎     | tarou.satou@example.com      |  1 | 山田 太郎     | tarou.yamada@example.com |
|  6 | 佐藤 花子     | hanako.satou@example.com     |  1 | 山田 太郎     | tarou.yamada@example.com |
|  7 | 高橋 太郎     | tarou.takahashi@example.com  |  1 | 山田 太郎     | tarou.yamada@example.com |
|  8 | 高橋 花子     | hanako.takahashi@example.com |  1 | 山田 太郎     | tarou.yamada@example.com |
|  9 | 田中 太郎     | tarou.tanaka@example.com     |  1 | 山田 太郎     | tarou.yamada@example.com |
| 10 | 田中 花子     | hanako.tanaka@example.com    |  1 | 山田 太郎     | tarou.yamada@example.com |
+----+---------------+------------------------------+----+---------------+--------------------------+
10 rows in set (0.01 sec)

Performance Schema の有効化

クエリーのパフォーマンスを見るために、Performance Schema を利用する。ちなみに、以前は"SHOW PROFILE"でクエリーのパフォーマンス（速度など）を確認する事ができたが、こちらは非推奨になった（以下、公式からの引用）。

The SHOW PROFILE and SHOW PROFILES statements are deprecated; expect them to be removed in a future MySQL release. Use the Performance Schema instead; see Section 25.19.1, “Query Profiling Using Performance Schema”.（SHOW PROFILE と SHOW PROFILES ステートメントは非推奨で、将来の MySQL リリースで削除されることが予想されます。パフォーマンススキーマを使用するクエリのプロファイリング」を参照してください。）

Performance Schema の利用方法は公式の25.19.1 Query Profiling Using Performance Schemaに書かれているのでそちらを参照して設定を行えばいい。

## setup_instrumentsテーブルを更新して、statementとstageのinstrumentsを有効にする
mysql> UPDATE performance_schema.setup_instruments
    -> SET ENABLED = 'YES', TIMED = 'YES'
    -> WHERE NAME LIKE '%statement/%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 193  Changed: 0  Warnings: 0

mysql> UPDATE performance_schema.setup_instruments
    -> SET ENABLED = 'YES', TIMED = 'YES'
    -> WHERE NAME LIKE '%stage/%';
Query OK, 120 rows affected (0.00 sec)
Rows matched: 129  Changed: 120  Warnings: 0

## events_statements_* と events_stages_* のconsumersを有効にする
mysql> UPDATE performance_schema.setup_consumers
    -> SET ENABLED = 'YES'
    -> WHERE NAME LIKE '%events_statements_%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 3  Changed: 1  Warnings: 0

mysql> UPDATE performance_schema.setup_consumers
    -> SET ENABLED = 'YES'
    -> WHERE NAME LIKE '%events_stages_%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

上記の設定ができれば、あとは以下のような SQL でクエリーの実行時間が見れる。1 点、時間についてだが、TIMER_WAIT カラムは「ピコ秒（1 兆分の 1 秒）」なので 1000000000000 でその値を割ることで秒数になる（より詳細にクエリーの速度を見るには、"performance_schema.events_stages_history_long"テーブルから、以下の SELECT 文で返される EVENT_ID でデータを SELECT すればいい）。

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%SQL_NO_CACHE%';

※今回は検証目的だったので、特に"UPDATE performance_schema.setup_actors"を行って記録するクエリーの対象を絞り込む設定はしていないが、記録するクエリーをユーザーで絞り込むなどの設定もできる。

実際にクエリーキャッシュを利用した場合の速度を比較してみる

クエリーキャッシュなしでクエリーを実行すると…

実行結果は以下のようになる（※3 回実行して平均約 0.97 秒だった）。

mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%SELECT SQL_NO_CACHE%';
+----------+----------+----------------------------------------------------------------------------------------------------------------+
| EVENT_ID | Duration | SQL_TEXT                                                                                                       |
+----------+----------+----------------------------------------------------------------------------------------------------------------+
|       19 | 1.032479 | SELECT SQL_NO_CACHE id, name FROM test WHERE email LIKE '%tan%' OR email LIKE '%yama%' OR email LIKE '%hanako' |
|       92 | 0.935036 | SELECT SQL_NO_CACHE id, name FROM test WHERE email LIKE '%tan%' OR email LIKE '%yama%' OR email LIKE '%hanako' |
|      127 | 0.955590 | SELECT SQL_NO_CACHE id, name FROM test WHERE email LIKE '%tan%' OR email LIKE '%yama%' OR email LIKE '%hanako' |
+----------+----------+----------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

※上記の SQL は以下の「クエリーキャッシュありでクエリーを実行すると…」で実行している SQL と全く同じだが、クエリーキャッシュによるキャッシュ対象外になっている。これは8.10.3.2 Query Cache SELECT Options に書かれているクエリーキャッシュが効かない場合をうまく利用して、SQLにSQL_NO_CACHEオプションをつけて実行しているため（他にもクエリーキャッシュが有効にならない場合があるが、それは8.10.3.1 How the Query Cache Operatesに書かれている）。

クエリーキャッシュありでクエリーを実行すると…

実行結果は以下のようになる（※何回か実行して平均 0.04 秒だった）。

mysql> SELECT EVENT_ID, EVENT_NAME, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE EVENT_NAME = 'statement/abstract/Query';
+----------+--------------------------+----------+----------+
| EVENT_ID | EVENT_NAME               | Duration | SQL_TEXT |
+----------+--------------------------+----------+----------+
|     1440 | statement/abstract/Query | 0.042005 | NULL     |
|     1449 | statement/abstract/Query | 0.038866 | NULL     |
|     1458 | statement/abstract/Query | 0.040838 | NULL     |
+----------+--------------------------+----------+----------+
3 rows in set (0.00 sec)

※上記の"statement/abstract/Query"については、25.12.6 Performance Schema Statement Event Tablesに詳細が書かれている。

※キャッシュが利用されているか？は以下のように"SHOW STATUS LIKE 'Qcache%';"で調べられる。SQL 実行前はキャッシュはないので"Qcache_queries_in_cache"=0。1 回目の SQL 実行後に再度確認するとキャッシュが作成されるので"Qcache_queries_in_cache"=1 になる。そしてキャッシュされたクエリーを実行すると、"Qcache_hits"=1 になり、キャッシュが利用されている事が確認できる。

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 1          |
| Qcache_free_memory      | 2147465680 |
| Qcache_hits             | 0          |
| Qcache_inserts          | 0          |
| Qcache_lowmem_prunes    | 0          |
| Qcache_not_cached       | 7          |
| Qcache_queries_in_cache | 0          |
| Qcache_total_blocks     | 1          |
+-------------------------+------------+
8 rows in set (0.00 sec)

（1回目のSQL実行）

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 1          |
| Qcache_free_memory      | 2137508784 |
| Qcache_hits             | 0          |
| Qcache_inserts          | 1          |
| Qcache_lowmem_prunes    | 0          |
| Qcache_not_cached       | 7          |
| Qcache_queries_in_cache | 1          |
| Qcache_total_blocks     | 4          |
+-------------------------+------------+
8 rows in set (0.00 sec)

（2回目のSQL実行）

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 1          |
| Qcache_free_memory      | 2137508784 |
| Qcache_hits             | 1          |
| Qcache_inserts          | 1          |
| Qcache_lowmem_prunes    | 0          |
| Qcache_not_cached       | 7          |
| Qcache_queries_in_cache | 1          |
| Qcache_total_blocks     | 4          |
+-------------------------+------------+
8 rows in set (0.00 sec)

速度の比較

（今回は 1 秒未満の差であんまり差が出なかったが、それでも）クエリーキャッシュを利用した場合、SQL の速度が向上している事が確認できた（約 24 倍の速度向上）。

まとめとして

今回はキャッシュの 1 種として MySQL のクエリーキャッシュについて、実際に速度面の違いを体感してみた。MySQLにおいては今後利用する事はなくなっていく機能と思われるが、これをきっかけにDBやそれ以外のキャッシュについて色々理解を深めていきたいと思った。特に、DBのベンチマークはプロダクトではかなり重要な部分であり、速度が遅いと後から気づいた時の悲惨さはすごいと思うので、速度向上のための工夫やその知見を深めていく事はとても重要になると思った。

ちなみに、冒頭の「はじめに」で MySQL8.0 からクエリーキャッシュの機能が削除されると言ったが、なんでクエリーキャッシュが削除されるのか？についてはMySQL 8.0: Retiring Support for the Query Cache（公式文書ではないが日本語訳になっているものはここ）に理由が書かれている。

ただ、AWS Aurora MySQLではデフォルトでクエリーキャッシュは有効化されており、速度向上のためにクエリーキャッシュという手立ては一般的には有効であるという事なのかもしれない（Amazon Aurora MySQL データベース設定のベストプラクティスを参照）。

《この公式ブロガーの記事一覧》

執筆者プロフィール：Katayama Yuta
SaaS ERPパッケージベンダーにて開発を2年経験。 SHIFTでは、GUIテストの自動化やUnitテストの実装などテスト関係の案件に従事したり、DevOpsの一環でCICD導入支援をする案件にも従事。昨年に開発部門へ異動し、再び開発エンジニアに。フロントエンド・バックエンドの両方を担当。座学で読み物を読むより、色々手を動かして試したり学んだりするのが好きなタイプ。

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/

最後まで読んでくださり、ありがとうございます！

御社サービスのご相談はお気軽に。

ソフトウェア品質のプロがお伺いします。