MySQLテーブルに1億件のレコード挿入してみた

前々からやってみたかったことです。

MySQLテーブルに大量レコード挿入、
1000万件をINSERTしたり、
1億件をINSERTしてみたい

理論的にはできることは知ってます。

でも頭で知ってるのと実際にやるでは別物

大量レコードINSERTは普通のINSERTと違い、
挿入方法にはある程度の工夫が必要でした。

そこで1億件挿入の記録を残しときます。

このページの目次

MySQLにはレコード数上限がない
- ただし内部的な限界は存在する(?)
今回挿入する1億レコードの元データ
今回作成したMySQLテーブル構成
MySQLのmax_allowed_packetを100MBに拡張
- ちなみにmax_allowed_packetの最大値は…？
1億件INSERTするSQLの組み立て方
1億件挿入の結果・かかった時間

MySQLにはレコード数上限がない

これは理屈として知ってる人も多いはず

挿入できるレコード数には上限ってありません。

確かにMySQLには次の制限はあります。

▼ MySQLテーブル上での２つの制限

カラム数制限
行サイズ制限

▼ 制限についての引用

カラム数制限 : MySQL にはテーブル当たり 4096 カラムの強い制限がありますが、特定のテーブルの有効な最大値が少なくなる可能性があります。カラムの正確な制限は、いくつかの要因によって異なります

行サイズ制限 : ストレージエンジンがより大きな行をサポートできる場合でも、MySQL テーブルの内部表現の最大行サイズは 65,535 バイトです。

引用元　：　https://dev.mysql.com/doc/refman/8.0/ja/column-count-limit.html

でも最大行数があると書かれてません。
だからレコード数には上限はないんです。

いくらでも何億・何兆件でもINSERTできます笑

ただし内部的な限界は存在する(?)

次のStackOverFlowの回答も参考になります。

▼ レコード数上限ってどのくらい？への回答

The MyISAM storage engine supports 2^32 rows per table, but you can build MySQL with the --with-big-tables option to make it support up to 2^64 rows per table.

The InnoDB storage engine has an internal 6-byte row ID per table, so there are a maximum number of rows equal to 2^48 or 281,474,976,710,656.

引用元　：　https://stackoverflow.com/questions/2716232/maximum-number-of-records-in-a-mysql-database-table

InnoDBだと281兆件ほどが限界らしい…？

でも実用的な範囲では上限がないようなものです。

今回挿入する1億レコードの元データ

何のデータを1億レコード挿入するか…

ここでは素数一覧のデータを拝借しました。

具体的には100億以下の素数リストです。

▼ このサイトからダウンロード可能

www.saoyagi2.net

素数一覧 | 初歩の整数論プログラミング

https://www.saoyagi2.net/integer/primelist.html

1000億以下の素数データと100兆以下の素数の個数を掲載しています

▼ 素数リストがzipで公開されてる

▼ n以下の素数の容量と個数の関係

n	容量	素数の個数
100万	526KB	78498個
1000万	1581KB	664579個
1億	14.00MB	5761455個
10億	125.23MB	50847534個
100億	1.15GB	455052511個
1000億	10.77GB	4118054813個

100億以下の素数は約4億5000万個！

タイトルには1億レコードと書いたけど、100億以下の素数だと4.5億レコードほどを挿入することになります。（テーブル構成などは後述）

早速1.15GBの素数リストをダウンロードしました。

今回作成したMySQLテーブル構成

以下のようなテーブル構成です。

▼ テーブル名 : prime_numbers

カラム名	データ型	補足
nth	UNSIGNED INT	PRIMARY KEY
number	UNSIGNED DECIMAL(11,0)	INDEXあり

▼ テーブル作成のSQL例

CREATE TABLE IF NOT EXISTS prime_numbers (
  nth       INT UNSIGNED AUTO_INCREMENT,
  number    DECIMAL(11,0) UNSIGNED UNIQUE,
  INDEX number_idx(number),
  PRIMARY KEY(nth)
)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8

CREATE TABLE IF NOT EXISTS prime_numbers (

nth INT UNSIGNED AUTO_INCREMENT,

number DECIMAL(11,0) UNSIGNED UNIQUE,

INDEX number_idx(number),

PRIMARY KEY(nth)

)

ROW_FORMAT=COMPRESSED

KEY_BLOCK_SIZE=8

このような素数を記録できるテーブル

nthカラムはn番目の素数を表し、numberカラムはその素数というシンプルな構成になってます。ただしnumberカラムには高速検索のためにインデックスも貼りました。

ポイントは次の２点です。

numberカラムの型
これは BIGINT でも良かったけど、テーブル容量を抑えるために DECIMAL(11,0) と定義した。消費バイト数は5バイトになる（DECIMAL型とDOUBLE型の解説　：　https://pisuke-code.com/mysql-double-vs-decimal/）
テーブル圧縮の有効化
上記のように ROW_FORMAT=COMPRESSED を指定すると指定のブロックサイズで圧縮が有効になる。詳細などは以下リファレンスを参照　：　https://man.plustar.jp/mysql/innodb-compression-usage.html

できる限りの最適化を行いました。

これで1億件レコード挿入をやってみます。

MySQLのmax_allowed_packetを100MBに拡張

下準備として以下を実行しました。

max_allowed_packetを100MBに拡張

実行できるクエリの最大バイト数です。
大規模クエリを実行するので100MBに変更しました。

▼ このようなSQLを実行

## max_allowed_packetを拡張
SET GLOBAL max_allowed_packet = 100*1024*1024;

## max_allowed_packetの変更を確認
SHOW VARIABLES LIKE 'max_allowed_packet';
  ## => 104857600

## max_allowed_packetを拡張

SET GLOBAL max_allowed_packet = 100*1024*1024;

## max_allowed_packetの変更を確認

SHOW VARIABLES LIKE 'max_allowed_packet';

## => 104857600

このオプションは以下記事でも言及しました。

▼ MySQLでテーブルを一括削除する

PisukeCode - Web開発まとめ

MySQLでテーブルを一括削除する超便利なテクニック

https://pisuke-code.com/mysql-delete-tables-at-once

MySQLでテーブルを一括削除したいことないですか？例えばテスト用に何百・何千ものテーブルを作った時とか、、、そういうとき、 phpMyAdmin の「ある機能」を使えば、どれだけテーブルが多くてもスマート＆超簡単に削除できます。ここではそのテーブル一括削除のやり方について、便利だったのでまとめました。愚直に１つずつテーブルを DROP することもできるが...MySQLでのテーブル削除と言えば DROP TABLE を普通は使うはずその DROP TABLE にコンマ（ , ）で区切って渡せば、一応複数テーブルの削除もできます。▼ 例えばこういう...

巨大なクエリを実行するのに必須オプションです。

ちなみにmax_allowed_packetの最大値は…？

このmax_allowed_packetには制限があります。

その値は 1GB = 1024MB とのこと

▼ 詳しくはリファレンス参照

MySQL 8.0 Server およびクライアント間で転送可能なパケットの最大サイズは 1G バイトです。MySQL クライアントまたは mysqld サーバーが max_allowed_packet バイトより大きいパケットを受け取ると、ER_NET_PACKET_TOO_LARGE エラーが発行され、接続が失われます。

引用元　：　https://dev.mysql.com/doc/refman/8.0/ja/packet-too-large.html

まあこの点はあまり気にしなくていいです。

1億件INSERTするSQLの組み立て方

問題なのはINSERTするSQLの組み立て方

いくら巨大クエリを実行できると言っても、
環境によっては使えるメモリには限りあります。

そこで以下の実行方法を考えました。

1つのINSERT文で100万件をまとめて挿入
それを1億レコード挿入まで繰り返す

つまりは、以下のようなクエリになります。

## 最初の100万件のレコード挿入
INSERT INTO prime_numbers (number) VALUES (2),(3),(5),(7),(11),(13),(17),(19),(23),(29),(31),(37),(41),(43),(47),(53),(59),(61),(67),(71),(73),(79),(83),(89),(97)...

## 次の100万件のレコード挿入
INSERT INTO prime_numbers (number) VALUES (15485917),(15485927),(15485933),(15485941),(15485959),(15485989),(15485993),(15486013),(15486041),(15486047),(15486059),...

## これを1億件まで繰り返す
INSERT INTO prime_numbers ...

## 最初の100万件のレコード挿入

INSERT INTO prime_numbers (number) VALUES (2),(3),(5),(7),(11),(13),(17),(19),(23),(29),(31),(37),(41),(43),(47),(53),(59),(61),(67),(71),(73),(79),(83),(89),(97)...

## 次の100万件のレコード挿入

INSERT INTO prime_numbers (number) VALUES (15485917),(15485927),(15485933),(15485941),(15485959),(15485989),(15485993),(15486013),(15486041),(15486047),(15486059),...

## これを1億件まで繰り返す

INSERT INTO prime_numbers ...

実際は手打ちでクエリ実行したわけではなく、Node.jsからmysqlパッケージを使って 1.素数リストを解析して100万件ごとのクエリ作成 ⇒ 2.順番に100万件のINSERTを実行するみたいな感じです。（Node.jsでMySQLに接続 : https://pisuke-code.com/nodejs-connect-to-mysql-db/ ）

ダウンロードした素数リストは改行区切りのtxtファイルだったため、100万件ごとのSQLを組み立てるのも簡単でした。難易度は使用するデータ形式により異なります。

そこは各自での工夫が必要ですね。

1億件挿入の結果・かかった時間

いよいよ1億レコード挿入を実行しました。

※ 1億件は言葉のアヤで本当は4.5億件

実行してみると本当に時間がかかります(笑)

▼ 最終的な結果は次の通り

総レコード件数　：　455052511件
全件挿入にかかった時間　：　255分
1億件挿入にかかった時間　：　56分
テーブルサイズ　：　14.1GB

▼ 1億件以上のレコード挿入成功

このような結果になりました…

4.5億件挿入には約4時間ほどかかりました。予想だと5時間くらいと推測してたので、思ったより早く終わったなという感想です。

意外なのはテーブルサイズが14.1GBなことです。

以下の最適化のおかげと考えられます。

カラム型にDECIMALを使ったこと
テーブル圧縮を有効化したこと

この２つがなかったら恐らく30GBくらいまでテーブルサイズが膨れ上がってたかも。巨大テーブルを作るときは、こういう最適化が必須ですね。

以上、MySQLで1億件挿入してみたでした。ではまた