なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQLのデータ投入順序とデータファイルサイズのお話

穴があったら埋めたい!


ということで、埋まっていたと思ったら空いていたMySQL Advent Calendar 2020 - Qiita の22日目、急遽登板です。

小ネタにしては、エビデンス収集・整理に時間のかかるネタになってしまいましたが。

データ投入順とプライマリキーの関係

先日、「PKにSequence/Auto Increment使うと遅くなる!?」みたいにな話題がありました。
MySQLだと割と当たり前のように「Primary Key Auto Increment」でテーブル作ってしまいガチ。


この問題の1つは連番採取のためのロック粒度の話、もう1つはインデックスの物理配置/再配置の話。


この件そのものの検証は、id:hmatsu47さんが実施してくれています。
MySQLとPostgreSQLと主キー - Speaker Deck


このセッションを聞きながら、「PK昇順じゃない順番にデータ入れると、「データ投入順で、データファイル*1の大きさが変わるよね」という、過去のおぼろげな記憶が呼び起こされました。

たしか、PK昇順とPK逆順で投入するのとでは、PK逆順投入時のほうが大きなファイルになったはず、と。


ibdファイルの膨張は、ストレージをムダに圧迫するだけでなく、スキャンブロック数もムダに増えてレスポンスの劣化にもつながるので、なるべくシュッとしておきたいですよね。




気になったら、試す。それだけです。


本当は、投入にかかる時間も計測したエントリにしたかったのですが、急遽登板なのでファイルサイズのところにフォーカスして、速度の話はおまけで。




パターンとしては4つ、それぞれについて、昇順、降順での取り込みを実行します。
その差異の「出来上がるデータファイル(ibd)のサイズ」を確認します。


テスト1 PK INTEGERな列のみのテーブルに対して、PK昇順/降順のファイルでLOAD DATA INFILE実行。
テスト2 PK INTEGERな列+セカンダリインデックス対象外の列。PK昇順/降順、2列目はランダムな値のファイルでLOAD DATA INFILE実行。
テスト3 PK INTEGERな列+セカンダリインデックス対象の列。PK昇順/降順、2列目はランダムな値のファイルでLOAD DATA INFILE実行。
テスト4 PK INTEGERな列+セカンダリインデックス対象の列。PK昇順/降順、2列目はランダムな値のファイルで、INSERT文で1行ずつ登録。

念の為、MySQL5.6/5.7/8.0について確認しました。

環境

HW(VM)、OS

AWS EC2 m5.large。
Amazon Linux2をyum updateで実行時最新まで適用。

MySQL

バージョンは以下の通りで、それぞれyumで構築したもの。

テストデータ

テストに使用するファイルは、16,777,216件。
それぞれ先頭10件は以下のような感じ。

テスト1~3は、MySQLが動いているEC2上にファイルを置いてLOAD DATAコマンドで取り込み、テスト4は、クライアントサイドにファイルを置いて、SOURCEコマンドで読み込みます。

  • テスト1用
$ head /var/lib/mysql-files/asc_1.txt
1
2
3
4
5
6
7
8
9
10
$ head /var/lib/mysql-files/desc_1.txt
16777216
16777215
16777214
16777213
16777212
16777211
16777210
16777209
16777208
16777207
  • テスト2、テスト3用
$ head /var/lib/mysql-files/asc_2.txt
1,67727474
2,89394923
3,43792199
4,50776230
5,22504555
6,60194088
7,33456781
8,86701644
9,33137882
10,5584479


$ head /var/lib/mysql-files/desc_2.txt
16777216,46757637
16777215,6430694
16777214,91880562
16777213,40110734
16777212,24911987
16777211,4227735
16777210,46402716
16777209,19330381
16777208,57443760
16777207,29227659
  • テスト4用
$ head /var/lib/mysql-files/asc_4_sql_insert.txt
INSERT INTO test_asc_4 VALUES (1,67727474);
INSERT INTO test_asc_4 VALUES (2,89394923);
INSERT INTO test_asc_4 VALUES (3,43792199);
INSERT INTO test_asc_4 VALUES (4,50776230);
INSERT INTO test_asc_4 VALUES (5,22504555);
INSERT INTO test_asc_4 VALUES (6,60194088);
INSERT INTO test_asc_4 VALUES (7,33456781);
INSERT INTO test_asc_4 VALUES (8,86701644);
INSERT INTO test_asc_4 VALUES (9,33137882);
INSERT INTO test_asc_4 VALUES (10,5584479);
$ head /var/lib/mysql-files/desc_4_sql_insert.txt
INSERT INTO test_desc_4 VALUES (16777216,46757637);
INSERT INTO test_desc_4 VALUES (16777215,6430694);
INSERT INTO test_desc_4 VALUES (16777214,91880562);
INSERT INTO test_desc_4 VALUES (16777213,40110734);
INSERT INTO test_desc_4 VALUES (16777212,24911987);
INSERT INTO test_desc_4 VALUES (16777211,4227735);
INSERT INTO test_desc_4 VALUES (16777210,46402716);
INSERT INTO test_desc_4 VALUES (16777209,19330381);
INSERT INTO test_desc_4 VALUES (16777208,57443760);
INSERT INTO test_desc_4 VALUES (16777207,29227659);
投入先テーブル定義
mysql> show create table test_asc_1\G
*************************** 1. row ***************************
       Table: test_asc_1
Create Table: CREATE TABLE `test_asc_1` (
  `col_id` int(11) NOT NULL,
  PRIMARY KEY (`col_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> show create table test_desc_1\G
*************************** 1. row ***************************
       Table: test_desc_1
Create Table: CREATE TABLE `test_desc_1` (
  `col_id` int(11) NOT NULL,
  PRIMARY KEY (`col_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> show create table test_asc_2\G
*************************** 1. row ***************************
       Table: test_asc_2
Create Table: CREATE TABLE `test_asc_2` (
  `col_id` int(11) NOT NULL,
  `col_x` int(11) NOT NULL,
  PRIMARY KEY (`col_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> show create table test_desc_2\G
*************************** 1. row ***************************
       Table: test_desc_2
Create Table: CREATE TABLE `test_desc_2` (
  `col_id` int(11) NOT NULL,
  `col_x` int(11) NOT NULL,
  PRIMARY KEY (`col_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> show create table test_asc_3\G
*************************** 1. row ***************************
       Table: test_asc_3
Create Table: CREATE TABLE `test_asc_3` (
  `col_id` int(11) NOT NULL,
  `col_x` int(11) NOT NULL,
  PRIMARY KEY (`col_id`),
  KEY `col_x` (`col_x`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> show create table test_desc_3\G
*************************** 1. row ***************************
       Table: test_desc_3
Create Table: CREATE TABLE `test_desc_3` (
  `col_id` int(11) NOT NULL,
  `col_x` int(11) NOT NULL,
  PRIMARY KEY (`col_id`),
  KEY `col_x` (`col_x`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> show create table test_asc_4\G
*************************** 1. row ***************************
       Table: test_asc_4
Create Table: CREATE TABLE `test_asc_4` (
  `col_id` int(11) NOT NULL,
  `col_x` int(11) NOT NULL,
  PRIMARY KEY (`col_id`),
  KEY `col_x` (`col_x`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> show create table test_desc_4\G
*************************** 1. row ***************************
       Table: test_desc_4
Create Table: CREATE TABLE `test_desc_4` (
  `col_id` int(11) NOT NULL,
  `col_x` int(11) NOT NULL,
  PRIMARY KEY (`col_id`),
  KEY `col_x` (`col_x`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
1 row in set (0.01 sec)

試験結果

結果表の「OPTIMIZE後」、というのは、取り込み後に「ALTER TABLE テーブル名 ENGINE INNODB」でibdファイルの再編成をした後、という意味で使ってます。

テスト1:PK INTEGERな列のみのテーブルに対して、PK昇順/降順のファイルでLOAD DATA INFILE実行。
Version ORDER 取込前ファイルサイズ 取込前ファイルサイズ OPTIMIZE後ファイルサイズ
MySQL 5.6.50 asc 98304 423624704 411041792
MySQL 5.6.50 desc 98304 423624704 411041792
MySQL 5.7.32 asc 98304 423624704 478150656
MySQL 5.7.32 desc 98304 423624704 478150656
MySQL 8.0.22 asc 114688 419430400 478150656
MySQL 8.0.22 desc 114688 419430400 478150656

バージョンによって出来上がるデータファイルのサイズに違いはあるものの、取り込み順序に関係なく同じファイルサイズになりました。

テスト2:PK INTEGERな列+セカンダリインデックス対象外の列。PK昇順/降順、2列目はランダムな値のファイルでLOAD DATA INFILE実行。
Version ORDER 取込前ファイルサイズ 取込前ファイルサイズ OPTIMIZE後ファイルサイズ
MySQL 5.6.50 asc 98304 494927872 482344960
MySQL 5.6.50 desc 98304 494927872 482344960
MySQL 5.7.32 asc 98304 494927872 562036736
MySQL 5.7.32 desc 98304 494927872 562036736
MySQL 8.0.22 asc 114688 494927872 562036736
MySQL 8.0.22 desc 114688 494927872 562036736

PK以外の列があっても、テスト1と同様、取り込み順序に関係なく同じファイルサイズになりました。
意外なのは、取り込み後のファイルサイズだけが、どのバージョンでも同じになったことです。


テスト3:PK INTEGERな列+セカンダリインデックス対象の列。PK昇順/降順、2列目はランダムな値のファイルでLOAD DATA INFILE実行。
Version ORDER 取込前ファイルサイズ 取込前ファイルサイズ OPTIMIZE後ファイルサイズ
MySQL 5.6.50 asc 114688 830472192 713031680
MySQL 5.6.50 desc 114688 847249408 713031680
MySQL 5.7.32 asc 114688 830472192 826277888
MySQL 5.7.32 desc 114688 847249408 826277888
MySQL 8.0.22 asc 131072 826277888 826277888
MySQL 8.0.22 desc 131072 843055104 826277888

PK以外の列に、セカンダリインデックスがあって、そのセカンダリインデックスにとってはランダムな順序での投入となった場合、取り込み後のサイズはバラバラになりました。
PK昇順なほうが、PK降順よりも、データファイルが小さく収まる傾向にあるようです。

なお、MySQL8.0.22のPK昇順のケースは、オプティマイズ前後でサイズの違いがありません。セカンダリインデックスについても、投入しながら再編成しているのでしょうか。

テスト4:PK INTEGERな列+セカンダリインデックス対象の列(2列目の値はランダム)に、INSERT文で1行ずつ登録。

やる前から解っていたけど、死ぬほど遅い。。。
大量データ投入を実装するときは、LOAD DATAかBULK INSERT使ってくれよな!

Version ORDER 取込前ファイルサイズ 取込前ファイルサイズ OPTIMIZE後ファイルサイズ
MySQL 5.6.50 asc 114688 830472192 713031680
MySQL 5.6.50 desc 114688 847249408 713031680
MySQL 5.7.32 asc 114688 830472192 826277888
MySQL 5.7.32 desc 114688 847249408 826277888
MySQL 8.0.22 asc 131072 826277888 826277888
MySQL 8.0.22 desc 131072 843055104 826277888

ファイルサイズは、テスト3と全く同じになりました。
LOAD DATAだろうがINSERTだろうが違いはない、ということですね。

おまけ:速度について

きれいなエビデンスを揃えるのが間に合わなかったので具体的な数字は出せませんが、以下の様な傾向でした。

  • LOAD DATAでは、いずれのケースでも、降順ファイルの取り込みは昇順ファイルの1.7倍程度の時間がかかった。
  • INSERTによる1件ずつの取り込み(Bulk Insertすら使わない)は、降順と昇順で1.05倍以下の差しかなかった。
  • LOAD DATAでは、いずれのケースでも、MySQL 8.0.22が、MySQL 5.6.50、MySQL 5.7.32より遅かった。。。設定ミスってるかも。
    • innodb_buffer_pool_size、innodb_log_file_sizeを統一、binlogも出力しないようにした以外は、ほぼそれぞれのデフォルト。

まとめ

  • PK列のみのテーブルだと、取り込み順序に関係なくデータ投入後のファイルサイズは同じになる。
  • PK列以外にセカンダリインデックス対象列があると、取り込み順序によってデータ投入後のファイルサイズが異なり、PK昇順にするほうが小さく収まる。
  • LOAD DATAとINSERTで、データ投入後のファイルサイズに違いはない。
  • もっと大きなテーブルにすると、違った傾向が出るかも。


使用するバージョンで、使用するテーブルを使って試してみるのが良いと思います。

最後はいつもの推し本の紹介で。

*1:innodb_file_per_table=1の時にテーブル/パーティション毎に作成される実データ永続化先ファイル。innodb_file_per_table=0は使ったことがない若輩者なのです。。。