MySQL（InnoDB）でBLOBカラムのデータを一括削除するときの注意点（バッファプールに与える影響）

InnoDBで、添付ファイルなどをBLOBカラムとして含む、データサイズの大きなレコードを、まとめて削除するときの注意点です。
なお、これらのカラムの保存領域も含めて、すべてカバーできるだけのバッファプール容量を確保している場合は気にする必要はありません。

※要は、**「UPDATE/DELETE時、既に不要なはずのBLOB値がバッファプールに読み込まれることによって、必要なデータページがバッファプールから追い出される問題」**です。
　長いVARCHARやTEXTも同様です。

テストの内容

• シンプルな「メモ書き」と、必要であれば関連する「添付ファイル」を1個だけ添付できるような機能を想定します
• 容量確保のため、「添付ファイル」のみ○か月後に削除する運用を想定します
• MySQL（5.7）サーバのバッファプールは1GB確保しておきます
• バッファプールのウォームアップは無効です
• クエリキャッシュも無効です
• 1レコードあたり1MBのBLOBカラム（＝添付ファイル相当）×1,100レコードのテーブルを用意します
• BLOB以外のカラムは、1GBのバッファプールが埋まらない程度の容量に調整しておきます
• 別テーブルを用意し（今回は過去の記事のテストで使ったものを流用）、フルスキャンが必要なSELECT文を実行しておき、対象データページをバッファプールにためておきます
• BLOBカラムの内容を大量に削除する前後で、↑のSELECT文の実行時間と「SHOW ENGINE INNODB STATUS\G」の結果がどう変わるか確認します

my.cnfの内容（関連部分）は以下の通りです。

my.cnf（部分）

innodb_file_per_table=1
innodb_log_files_in_group=2
innodb_log_file_size=256M
innodb_buffer_pool_size = 1G
innodb_flush_method=O_DIRECT

テストケース1：フラットなテーブルのBLOBカラムをNULLでUPDATE

上で説明した「メモ書き」用のカラムと、「添付ファイル」を格納するBLOB（LONGBLOB）カラムを、同一テーブルの同一レコードに格納する設計です。
…ちょっとイケテナイ香りがしますが、時間的な余裕がないときに設計／実装するときに、やりがちではあります。

mysql> CREATE TABLE `bp_test`.`main_data_bad` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   `entry_title` varchar(40) NOT NULL,
    ->   `memo` varchar(255) NOT NULL,
    ->   `file_name` varchar(255),
    ->   `file_content` longblob,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_cr_ts` (`create_timestamp`)
    -> ) ENGINE InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

ここに、レコードをINSERTします。

INSERT INTO bp_test.main_data_bad SET entry_title = MD5(RAND()), memo = SHA2(RAND(), 512), file_name = SHA2(RAND(), 256), file_content = REPEAT(SHA2(RAND(), 512), 8192);
（1,100行分）

一旦MySQLを再起動し、バッファプールを空にしてから、別テーブルのフルスキャンを実行します。

mysql> SELECT COUNT(*), SUM(flag) FROM cardi_test.test_data;
+----------+-----------+
| COUNT(*) | SUM(flag) |
+----------+-----------+
|    10000 |      9990 |
+----------+-----------+
1 row in set (0.15 sec)

0.15秒掛かりました。
「SHOW ENGINE INNODB STATUS\G」してみます。

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
（中略）
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1099431936
Dictionary memory allocated 598820
Buffer pool size   65528
Free buffers       50888
Database pages     14640
Old database pages 5560
Modified db pages  0
（中略）
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.02 sec)

もう一度、先ほどのSELECT文を実行します。

mysql> SELECT COUNT(*), SUM(flag) FROM cardi_test.test_data;
+----------+-----------+
| COUNT(*) | SUM(flag) |
+----------+-----------+
|    10000 |      9990 |
+----------+-----------+
1 row in set (0.01 sec)

バッファプールにたまっている分速いです。

続いて、BLOBカラムをNULLでUPDATEしてみます。WHERE句の条件は、全レコードが対象になるように調整して実行しました。

mysql> UPDATE bp_test.main_data_bad SET file_name = NULL, file_content = NULL WHERE create_timestamp < 'YYYY-MM-DD HH:MM:SS';
Query OK, 1100 rows affected (39.23 sec)
Rows matched: 1100  Changed: 1100  Warnings: 0

「SHOW ENGINE INNODB STATUS\G」および先ほどのSELECT文を実行してみると、

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
（中略）
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1099431936
Dictionary memory allocated 621314
Buffer pool size   65528
Free buffers       8192
Database pages     57335
Old database pages 21000
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 13, not young 18522
0.26 youngs/s, 370.43 non-youngs/s
Pages read 77066, created 43, written 104
1249.18 reads/s, 0.18 creates/s, 1.34 writes/s
Buffer pool hit rate 255 / 1000, young-making rate 0 / 1000 not 221 / 1000
（中略）
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.05 sec)

mysql> SELECT COUNT(*), SUM(flag) FROM cardi_test.test_data;
+----------+-----------+
| COUNT(*) | SUM(flag) |
+----------+-----------+
|    10000 |      9990 |
+----------+-----------+
1 row in set (0.15 sec)

UPDATE文で、一旦、値変更前のBLOBカラムを含むレコードのデータページがバッファプールに載ってしまったため、先ほどのテーブルのデータページがバッファプールから追い出されてしまいました。結果、SELECT文も遅くなりました。

テストケース2：テーブル設計はそのまま、「innodb_old_blocks_time」を調整する

バッチ処理のような、一時的な処理のためのデータがバッファプールにとどまらないようにするためのオプションとして「innodb_old_blocks_time」があります。対象となるSQLを実行する前に、この値を長めにしておき、実行直後に0に戻すことによって、一時的な処理でバッファプールからの「追い出し」をしないようにできるようなので、実際に「SET GLOBAL」をはさんでUPDATE文を実行してみました。

細かい出力内容は省略しますが、結果として、バッファプールからの「追い出し」が発生してしまいました。

※以下、途中から。

mysql> SELECT COUNT(*), SUM(flag) FROM cardi_test.test_data;
+----------+-----------+
| COUNT(*) | SUM(flag) |
+----------+-----------+
|    10000 |      9990 |
+----------+-----------+
1 row in set (0.01 sec)

mysql> SET GLOBAL innodb_old_blocks_time = 60000;
Query OK, 0 rows affected (0.03 sec)

mysql> UPDATE bp_test.main_data_bad SET file_name = NULL, file_content = NULL WHERE create_timestamp < 'YYYY-MM-DD HH:MM:SS';
Query OK, 1100 rows affected (37.74 sec)
Rows matched: 1100  Changed: 1100  Warnings: 0

mysql> SET GLOBAL innodb_old_blocks_time = 0;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
（中略）
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1099431936
Dictionary memory allocated 621314
Buffer pool size   65536
Free buffers       8192
Database pages     57342
Old database pages 21006
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 22334
0.00 youngs/s, 475.18 non-youngs/s
Pages read 77080, created 42, written 97
852.22 reads/s, 0.13 creates/s, 1.17 writes/s
Buffer pool hit rate 302 / 1000, young-making rate 0 / 1000 not 389 / 1000
（中略）
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.02 sec)

mysql> SELECT COUNT(*), SUM(flag) FROM cardi_test.test_data;
+----------+-----------+
| COUNT(*) | SUM(flag) |
+----------+-----------+
|    10000 |      9990 |
+----------+-----------+
1 row in set (0.08 sec)

オプションを使わなかったときより実行時間が短いように見えますが、何度か実行してみたところ、オプションを使ったとき・使わなかったときのいずれも0.08秒以上0.16秒以下の範囲となりました（バッファプールにたまっているときの時間は0.02秒以下）。

「innodb_old_blocks_time」オプションは、SELECT文でデータが大量に読み込まれるパターンには有効かもしれませんが、UPDATE文の場合の処理の流れでは、うまく適合しないようです。

※「innodb_old_blocks_pct」オプションのほうも、適切な値に調整する必要があるかもしれません。

テストケース3：添付ファイル用のカラムを別テーブルに分離し、DELETEで削除する

テストケース1・2で使っていたテーブル構造では、「メモ書き」と「添付ファイル」を同一テーブルのレコードとして処理していました。
「1対1の関係だから問題ないだろう」と思われるかもしれませんが、厳密にいうと「1対0または1の関係」であり、生成タイミングは同じでも、「添付ファイル」が先に削除されるので、完全な「1対1」ではありません。

※「値が存在しない状態」をNULLで表現してしまうアンチパターンの1つといえます。

• 「メモ書き」と「添付ファイル」を別テーブルのレコードとしてINSERTする
• 添付ファイルを削除するときは、後者のテーブルをDELETEする

形に変えるとどうなるのか、というのが3つ目のテストケースです。

テーブル定義は以下の通りです。

mysql> CREATE TABLE `bp_test`.`main_data` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   `entry_title` varchar(40) NOT NULL,
    ->   `memo` varchar(255) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_cr_ts` (`create_timestamp`)
    -> ) ENGINE InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.24 sec)

※次のCREATE TABLEの前に「USE bp_test」しておかないと、外部キー定義のところでエラーが出るので注意。

mysql> CREATE TABLE `bp_test`.`attached_data` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   `main_data_id` bigint(20) NOT NULL,
    ->   `file_name` varchar(255),
    ->   `file_content` longblob,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_cr_ts` (`create_timestamp`),
    ->   FOREIGN KEY (`main_data_id`)
    ->     REFERENCES `main_data`(`id`)
    ->     ON DELETE CASCADE
    -> ) ENGINE InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (1.07 sec)

ここに、各テーブル1,100行分をINSERTします（少し横着をしていますが、気にしないでください）。

INSERT INTO bp_test.main_data SET entry_title = MD5(RAND()), memo = SHA2(RAND(), 512);
INSERT INTO bp_test.attached_data SET main_data_id = 1, file_name = SHA2(RAND(), 256), file_content = REPEAT(SHA2(RAND(), 512), 8192);
（各1,100行分）
UPDATE bp_test.attached_data SET main_data_id = id;

この状態で、MySQLを再起動し、ケース1・2と同じ流れで進めてみます。

mysql> SELECT COUNT(*), SUM(flag) FROM cardi_test.test_data;
+----------+-----------+
| COUNT(*) | SUM(flag) |
+----------+-----------+
|    10000 |      9990 |
+----------+-----------+
1 row in set (0.15 sec)

（中略）

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
（中略）
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1099431936
Dictionary memory allocated 598820
Buffer pool size   65536
Free buffers       50836
Database pages     14699
Old database pages 5584
Modified db pages  0
（中略）
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

mysql> SELECT COUNT(*), SUM(flag) FROM cardi_test.test_data;
+----------+-----------+
| COUNT(*) | SUM(flag) |
+----------+-----------+
|    10000 |      9990 |
+----------+-----------+
1 row in set (0.01 sec)

mysql> DELETE FROM bp_test.attached_data WHERE create_timestamp < 'YYYY-MM-DD HH:MM:SS';
Query OK, 1100 rows affected (36.05 sec)

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
（中略）
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1099431936
Dictionary memory allocated 631282
Buffer pool size   65536
Free buffers       3824
Database pages     61707
Old database pages 22617
Modified db pages  42623
Pending reads      1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 31319, not young 95027
590.91 youngs/s, 1792.93 non-youngs/s
Pages read 81497, created 37, written 2284
1260.96 reads/s, 0.06 creates/s, 42.40 writes/s
Buffer pool hit rate 820 / 1000, young-making rate 84 / 1000 not 257 / 1000
（中略）
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.13 sec)

mysql> SELECT COUNT(*), SUM(flag) FROM cardi_test.test_data;
+----------+-----------+
| COUNT(*) | SUM(flag) |
+----------+-----------+
|    10000 |      9990 |
+----------+-----------+
1 row in set (0.08 sec)

フラットなテーブルでNULL値UPDATEするよりはマシかもしれませんが、やはりバッファプールからの「追い出し」が発生しているようです。

※先ほどの「innodb_old_blocks_time」オプションの併用もしてみましたが、結果は変わりませんでした。

テストのまとめ

MySQL（InnoDB）では、INSERT/UPDATE/DELETEのいずれの場合も、バッファプールにデータを載せてから処理を行います。
このあたりは、Oracle ACEのyoku0825さんがSlideShareで公開されている、以下のスライドの101ページ目（表紙を除く100ページ目）の説明が分かりやすいと思います。

　雑なMySQLパフォーマンスチューニング ※SlideShareの101ページ目を参照

UPDATEだろうがDELETEだろうが、変更・削除前のレコードや値（BLOBなどのデカいヤツも含む）は、バッファプールには載ってこないか、と思ったら、ちゃんと載ってきますよ（だからバッファプールからの「必要なデータの追い出し」に注意してね）、というお話でした。

対処法

• できるだけこまめに削除する（一度に削除する対象レコードを少なくして、バッファプールから追い出されるデータの量を減らす）
• バッチ処理の間隔を短くする
• そもそもバッチ処理である必要があるかどうか再検討する（リアルタイムで消せないか？）
• 逆に、まとめて削除するなら、パーティショニングを使い、パーティション単位でTRUNCATE/DROPする（バッファプールに影響を与えずに削除する）
• この場合は、削除間隔を長くしたほうが楽
• ディスクの容量に余裕があるのなら、そもそも削除しない（そっとしておく）
• でも、消したいBLOBカラムを持つテーブルに「削除フラグ」カラムを用意して、UPDATEでフラグを立てたりすると、結局BLOBカラムもろともバッファプールに載っちゃうので、注意しましょう

おまけ：調査のきっかけ

とあるプロダクト環境に、「1レコード内に添付ファイル用のBLOBカラムが複数存在する」クソ設計の非正規形テーブルがあり、このテーブルに対するバッチでのカラム値削除処理で、数十GBあるバッファプール上のデータが半分以上追い出される問題があったので、

「テーブルを正規化してNULL値UPDATEからDELETEに変えれば改善されるかな？」

と考えたのがきっかけです。

ただ、調査をしてみたところ、正規化＋DELETE化しても削除前のデータページがバッファプールに載ってしまい、あまり効果がありませんでした。

ちなみに、テーブル単位で削除する場合に「TRUNCATE TABLE」するとバッファプールに悪影響を与えませんが、少なくともMySQL 5.7の環境では、「DELETE FROM」でWHERE句なしの全レコード削除をする場合でも、全レコードの内容をバッファプールに載せるような動作にはなりませんでした。