MySQLで大量のデータを追加してみる
CentOSにMySQLとMemcacheのインストールしてみる
でMySQLをインストールした環境を使って、大量のデータをinsertしてみる。
【参考】 なんとなくやりたかった。MySQLのクエリのみで簡単に1億ものデータを作成する方法 | 株式会社LIG http://liginc.co.jp/programmer/archives/2707 SQLで大量データを一気に作るでござる(MySQL編) @ t100のプログラミング脱出作戦 http://t100life.blog121.fc2.com/blog-entry-205.html
今回はデフォルトで入ってるtestデータベースで試す。
mysql> use test
手順
下記のようなテーブルを作成する。
mysql> create table sample ( id int NOT NULL AUTO_INCREMENT, name varchar(64) NOT NULL, point int NOT NULL, type smallint NOT NULL, flag tinyint(1) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB;
ベースとなるデータの10件をINSERTする。
mysql> insert into sample (name, point, type, flag) values ('sato', 100, 1, 0), ('suzuki', 200, 2, 1), ('takahashi', 300, 3, 0), ('tanaka', 400, 4, 1), ('ito', 500, 5, 0), ('yamamoto', 600, 1, 1), ('watanabe', 700, 2, 0), ('nakamura', 800, 3, 1), ('kobayashi', 900, 4, 0), ('kato', 1000, 5, 1);
10件入ったテーブルを単純結合してsampleテーブルに入れる。
1000万件INSERTする場合は、下記のように7つ結合すればいい。
mysql> insert into sample (name, point, type, flag) select s1.name, s1.point, s1.type, s1.flag from sample s1, sample s2, sample s3, sample s4, sample s5, sample s6, sample s7;
自分のMacBookProの環境では約1分半かかった。 Query OK, 10000000 rows affected (1 min 26.64 sec) Records: 10000000 Duplicates: 0 Warnings: 0
以上で、1000万件のデータが追加されます。
mysql> select count(*) from sample; +----------+ | count(*) | +----------+ | 10000010 | +----------+
INSERTされるデータは下記のようになる。
mysql> select * from sample limit 30; +----+-----------+-------+------+------+ | id | name | point | type | flag | +----+-----------+-------+------+------+ | 1 | sato | 100 | 1 | 0 | | 2 | suzuki | 200 | 2 | 1 | | 3 | takahashi | 300 | 3 | 0 | | 4 | tanaka | 400 | 4 | 1 | | 5 | ito | 500 | 5 | 0 | | 6 | yamamoto | 600 | 1 | 1 | | 7 | watanabe | 700 | 2 | 0 | | 8 | nakamura | 800 | 3 | 1 | | 9 | kobayashi | 900 | 4 | 0 | | 10 | kato | 1000 | 5 | 1 | | 11 | sato | 100 | 1 | 0 | | 12 | suzuki | 200 | 2 | 1 | | 13 | takahashi | 300 | 3 | 0 | | 14 | tanaka | 400 | 4 | 1 | | 15 | ito | 500 | 5 | 0 | | 16 | yamamoto | 600 | 1 | 1 | | 17 | watanabe | 700 | 2 | 0 | | 18 | nakamura | 800 | 3 | 1 | | 19 | kobayashi | 900 | 4 | 0 | | 20 | kato | 1000 | 5 | 1 | | 21 | sato | 100 | 1 | 0 | | 22 | suzuki | 200 | 2 | 1 | | 23 | takahashi | 300 | 3 | 0 | | 24 | tanaka | 400 | 4 | 1 | | 25 | ito | 500 | 5 | 0 | | 26 | yamamoto | 600 | 1 | 1 | | 27 | watanabe | 700 | 2 | 0 | | 28 | nakamura | 800 | 3 | 1 | | 29 | kobayashi | 900 | 4 | 0 | | 30 | kato | 1000 | 5 | 1 | +----+-----------+-------+------+------+
単純結合とは
10件データの入った状態で、
mysql> select * from sample; +----+-----------+-------+------+------+ | id | name | point | type | flag | +----+-----------+-------+------+------+ | 1 | sato | 100 | 1 | 0 | | 2 | suzuki | 200 | 2 | 1 | | 3 | takahashi | 300 | 3 | 0 | | 4 | tanaka | 400 | 4 | 1 | | 5 | ito | 500 | 5 | 0 | | 6 | yamamoto | 600 | 1 | 1 | | 7 | watanabe | 700 | 2 | 0 | | 8 | nakamura | 800 | 3 | 1 | | 9 | kobayashi | 900 | 4 | 0 | | 10 | kato | 1000 | 5 | 1 | +----+-----------+-------+------+------+ 10 rows in set (0.00 sec)
fromにsampleを２つ並べると、100件になる。
mysql> select * from sample s1, sample s2; +----+-----------+-------+------+------+----+-----------+-------+------+------+ | id | name | point | type | flag | id | name | point | type | flag | +----+-----------+-------+------+------+----+-----------+-------+------+------+ | 1 | sato | 100 | 1 | 0 | 1 | sato | 100 | 1 | 0 | | 2 | suzuki | 200 | 2 | 1 | 1 | sato | 100 | 1 | 0 | | 3 | takahashi | 300 | 3 | 0 | 1 | sato | 100 | 1 | 0 | | 4 | tanaka | 400 | 4 | 1 | 1 | sato | 100 | 1 | 0 | | 5 | ito | 500 | 5 | 0 | 1 | sato | 100 | 1 | 0 | | 6 | yamamoto | 600 | 1 | 1 | 1 | sato | 100 | 1 | 0 | | 7 | watanabe | 700 | 2 | 0 | 1 | sato | 100 | 1 | 0 | | 8 | nakamura | 800 | 3 | 1 | 1 | sato | 100 | 1 | 0 | | 9 | kobayashi | 900 | 4 | 0 | 1 | sato | 100 | 1 | 0 | | 10 | kato | 1000 | 5 | 1 | 1 | sato | 100 | 1 | 0 | | 1 | sato | 100 | 1 | 0 | 2 | suzuki | 200 | 2 | 1 | | 2 | suzuki | 200 | 2 | 1 | 2 | suzuki | 200 | 2 | 1 | | 3 | takahashi | 300 | 3 | 0 | 2 | suzuki | 200 | 2 | 1 | | 4 | tanaka | 400 | 4 | 1 | 2 | suzuki | 200 | 2 | 1 | | 5 | ito | 500 | 5 | 0 | 2 | suzuki | 200 | 2 | 1 | | 6 | yamamoto | 600 | 1 | 1 | 2 | suzuki | 200 | 2 | 1 | | 7 | watanabe | 700 | 2 | 0 | 2 | suzuki | 200 | 2 | 1 | | 8 | nakamura | 800 | 3 | 1 | 2 | suzuki | 200 | 2 | 1 | | 9 | kobayashi | 900 | 4 | 0 | 2 | suzuki | 200 | 2 | 1 | | 10 | kato | 1000 | 5 | 1 | 2 | suzuki | 200 | 2 | 1 | | 1 | sato | 100 | 1 | 0 | 3 | takahashi | 300 | 3 | 0 | ・・・ | 10 | kato | 1000 | 5 | 1 | 9 | kobayashi | 900 | 4 | 0 | | 1 | sato | 100 | 1 | 0 | 10 | kato | 1000 | 5 | 1 | | 2 | suzuki | 200 | 2 | 1 | 10 | kato | 1000 | 5 | 1 | | 3 | takahashi | 300 | 3 | 0 | 10 | kato | 1000 | 5 | 1 | | 4 | tanaka | 400 | 4 | 1 | 10 | kato | 1000 | 5 | 1 | | 5 | ito | 500 | 5 | 0 | 10 | kato | 1000 | 5 | 1 | | 6 | yamamoto | 600 | 1 | 1 | 10 | kato | 1000 | 5 | 1 | | 7 | watanabe | 700 | 2 | 0 | 10 | kato | 1000 | 5 | 1 | | 8 | nakamura | 800 | 3 | 1 | 10 | kato | 1000 | 5 | 1 | | 9 | kobayashi | 900 | 4 | 0 | 10 | kato | 1000 | 5 | 1 | | 10 | kato | 1000 | 5 | 1 | 10 | kato | 1000 | 5 | 1 | +----+-----------+-------+------+------+----+-----------+-------+------+------+ 100 rows in set (0.00 sec)
これが単純結合らしい。
乱数を設定する場合
FLOOR関数とRAND関数を使って、データを乱数で設定してみる。
n〜mの乱数は下記のように書くと生成できる。
FLOOR(RAND() * (m - n + 1) + n)
例えば、
・0〜1の場合は「FLOOR(RAND()*2)」
・1〜5の場合は「FLOOR(RAND()*5 + 1)」
・100〜1000の場合は「FLOOR(RAND()*901 + 100)」
となる。
下記のようにINSERTすると、
mysql> insert into sample (name, point, type, flag) select s1.name, FLOOR(RAND()*901 + 100), FLOOR(RAND()*5 + 1), FLOOR(RAND() * 2) from sample s1, sample s2, sample s3, sample s4, sample s5, sample s6, sample s7;
乱数を使ってもINSERTにかかる時間は乱数を使わない場合とそれほど変わりませんでした。 Query OK, 10000000 rows affected (1 min 29.92 sec) Records: 10000000 Duplicates: 0 Warnings: 0
下記のような感じで(最初の10件以外は)乱数が設定される。
mysql> select * from sample limit 30; +----+-----------+-------+------+------+ | id | name | point | type | flag | +----+-----------+-------+------+------+ | 1 | sato | 100 | 1 | 0 | | 2 | suzuki | 200 | 2 | 1 | | 3 | takahashi | 300 | 3 | 0 | | 4 | tanaka | 400 | 4 | 1 | | 5 | ito | 500 | 5 | 0 | | 6 | yamamoto | 600 | 1 | 1 | | 7 | watanabe | 700 | 2 | 0 | | 8 | nakamura | 800 | 3 | 1 | | 9 | kobayashi | 900 | 4 | 0 | | 10 | kato | 1000 | 5 | 1 | | 11 | sato | 861 | 2 | 0 | | 12 | suzuki | 979 | 2 | 0 | | 13 | takahashi | 302 | 5 | 1 | | 14 | tanaka | 700 | 4 | 0 | | 15 | ito | 325 | 5 | 1 | | 16 | yamamoto | 136 | 4 | 0 | | 17 | watanabe | 512 | 1 | 1 | | 18 | nakamura | 621 | 1 | 0 | | 19 | kobayashi | 938 | 3 | 0 | | 20 | kato | 807 | 4 | 1 | | 21 | sato | 719 | 4 | 0 | | 22 | suzuki | 798 | 3 | 1 | | 23 | takahashi | 546 | 3 | 0 | | 24 | tanaka | 773 | 5 | 0 | | 25 | ito | 481 | 3 | 0 | | 26 | yamamoto | 685 | 2 | 0 | | 27 | watanabe | 626 | 1 | 1 | | 28 | nakamura | 276 | 2 | 1 | | 29 | kobayashi | 880 | 5 | 0 | | 30 | kato | 988 | 3 | 0 | +----+-----------+-------+------+------+
連番を設定する場合
各テーブルのidを使えば連番を設定できなくもない。
例えば、
s7.id * 1000000 + s6.id * 100000 + s5.id * 10000 + s4.id * 1000 + s3.id * 100 + s2.id * 10 + s1.id,
のように指定すると、下記のような連番が生成できる。
1111111
1111112
1111113
1111114
1111115
1111116
1111117
1111118
1111119
1111120
1111121
・・・
そのため、下記のようにINSERTすると、
mysql> insert into sample (name, point, type, flag) select s1.name, s7.id * 1000000 + s6.id * 100000 + s5.id * 10000 + s4.id * 1000 + s3.id * 100 + s2.id * 10 + s1.id - 1111110, s1.type, s1.flag from sample s1, sample s2, sample s3, sample s4, sample s5, sample s6, sample s7;
pointのところが連番で設定される。
mysql> select * from sample limit 30; +----+-----------+-------+------+------+ | id | name | point | type | flag | +----+-----------+-------+------+------+ | 1 | sato | 100 | 1 | 0 | | 2 | suzuki | 200 | 2 | 1 | | 3 | takahashi | 300 | 3 | 0 | | 4 | tanaka | 400 | 4 | 1 | | 5 | ito | 500 | 5 | 0 | | 6 | yamamoto | 600 | 1 | 1 | | 7 | watanabe | 700 | 2 | 0 | | 8 | nakamura | 800 | 3 | 1 | | 9 | kobayashi | 900 | 4 | 0 | | 10 | kato | 1000 | 5 | 1 | | 11 | sato | 1 | 1 | 0 | | 12 | suzuki | 2 | 2 | 1 | | 13 | takahashi | 3 | 3 | 0 | | 14 | tanaka | 4 | 4 | 1 | | 15 | ito | 5 | 5 | 0 | | 16 | yamamoto | 6 | 1 | 1 | | 17 | watanabe | 7 | 2 | 0 | | 18 | nakamura | 8 | 3 | 1 | | 19 | kobayashi | 9 | 4 | 0 | | 20 | kato | 10 | 5 | 1 | | 21 | sato | 11 | 1 | 0 | | 22 | suzuki | 12 | 2 | 1 | | 23 | takahashi | 13 | 3 | 0 | | 24 | tanaka | 14 | 4 | 1 | | 25 | ito | 15 | 5 | 0 | | 26 | yamamoto | 16 | 1 | 1 | | 27 | watanabe | 17 | 2 | 0 | | 28 | nakamura | 18 | 3 | 1 | | 29 | kobayashi | 19 | 4 | 0 | | 30 | kato | 20 | 5 | 1 | +----+-----------+-------+------+------+
ただ、かなり無理やり感があるので、もっとスマートなやり方があれば教えてほしい。
最初の10件が邪魔な場合は
idのみのテンポラリなテーブルを作って、
mysql> create table tmp (id int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
10件レコードを追加する。
mysql> insert into tmp values (), (), (), (), (), (), (), (), (), ();
mysql> select * from tmp; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +----+
tmpのテーブルからINSERTすれば、
mysql> insert into sample (name, point, type, flag) select ELT(t1.id, 'sato', 'suzuki', 'takahashi', 'tanaka', 'ito', 'yamamoto', 'watanabe', 'nakamura', 'kobayashi', 'kato'), t7.id * 1000000 + t6.id * 100000 + t5.id * 10000 + t4.id * 1000 + t3.id * 100 + t2.id * 10 + t1.id - 1111110, FLOOR(RAND()*5 + 1), FLOOR(RAND() * 2) from tmp t1, tmp t2, tmp t3, tmp t4, tmp t5, tmp t6, tmp t7;
ELT(N,str1,str2,str3,...) N = 1 の場合は str1 を戻し、N = 2 の場合は str2 を戻す、というふうに続きます。 MySQL :: MySQL 5.1 リファレンスマニュアル :: 11.3 文字列関数 http://dev.mysql.com/doc/refman/5.1/ja/string-functions.html
余計な10件がなく、すっきりする。
mysql> select count(*) from sample; +----------+ | count(*) | +----------+ | 10000000 | +----------+
mysql> select * from sample limit 30; +----+-----------+-------+------+------+ | id | name | point | type | flag | +----+-----------+-------+------+------+ | 1 | sato | 1 | 3 | 0 | | 2 | suzuki | 2 | 3 | 1 | | 3 | takahashi | 3 | 5 | 0 | | 4 | tanaka | 4 | 5 | 0 | | 5 | ito | 5 | 3 | 1 | | 6 | yamamoto | 6 | 1 | 1 | | 7 | watanabe | 7 | 1 | 0 | | 8 | nakamura | 8 | 5 | 1 | | 9 | kobayashi | 9 | 3 | 1 | | 10 | kato | 10 | 1 | 1 | | 11 | sato | 11 | 3 | 1 | | 12 | suzuki | 12 | 4 | 0 | | 13 | takahashi | 13 | 2 | 0 | | 14 | tanaka | 14 | 5 | 0 | | 15 | ito | 15 | 3 | 0 | | 16 | yamamoto | 16 | 3 | 1 | | 17 | watanabe | 17 | 3 | 0 | | 18 | nakamura | 18 | 5 | 0 | | 19 | kobayashi | 19 | 3 | 0 | | 20 | kato | 20 | 3 | 1 | | 21 | sato | 21 | 3 | 1 | | 22 | suzuki | 22 | 3 | 1 | | 23 | takahashi | 23 | 5 | 1 | | 24 | tanaka | 24 | 2 | 0 | | 25 | ito | 25 | 4 | 1 | | 26 | yamamoto | 26 | 3 | 1 | | 27 | watanabe | 27 | 3 | 0 | | 28 | nakamura | 28 | 4 | 0 | | 29 | kobayashi | 29 | 3 | 0 | | 30 | kato | 30 | 1 | 0 | +----+-----------+-------+------+------+
エラーが出た場合
CentOSにMySQLとMemcacheのインストールしてみる
の環境で実行すると、INSERT時におそらく
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
のエラーが出るはず。
原因は、innodb_buffer_poolの値が小さいためで、
my.cnfにinnodb_buffer_poolを指定するといいみたいです。
$ sudo vi /etc/my.cnf [mysqld] innodb_buffer_pool=512M ←追加
あと、設定を反映させるために再起動も必要。
$ sudo service mysqld restart
設定した結果、下記のようになってればOK。
mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 536870912 | +-------------------------+-----------+
【参考】 MySQL エラー「ERROR 1206 (HY000): The total number of locks exceeds the lock table size」: 仕事を早く片付けて遊ぶための技術メモ http://shrine-bell.seesaa.net/article/24406528.html
