【MySQL】SQLでWHILEとbulkinsertの両方を使う方法

users_bobbiesテーブルに10,000,000レコードを挿入するために、以下のSQLを作成していました。
しかし、あまりにも遅すぎます。
このWHILE文にbulkinsertを行いたいのですが、どのように記述したらよいか検討つきません。
教えて頂ければ幸いです。

Version: MySQL5.7

drop procedure if exists insert_user_hobby_data;
DELIMITER $$
CREATE PROCEDURE insert_user_hobby_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    SET @user_counter = 0;
    WHILE @user_counter <= 9999999 DO
            set @user_id = @user_counter + 1;
            INSERT INTO users_bobbies (user_id, hobby_id)
            VALUES (@user_id, 1);
            SET @user_counter = @user_counter + 1;
        END WHILE;
END$$
DELIMITER ;
CALL insert_user_hobby_data();

行動規範の内容に同意します

回答1件

ベストアンサー

以下のプロシージャ bulk_insert は、INSERT したい件数 len と、bulk_insert 時の同時 INSERT 件数 block を引数に指定します。

WHILE ではなく LOOP を用いていますが、ループ構文としては類似のものなのでご容赦ください。
https://dev.mysql.com/doc/refman/5.6/ja/flow-control-statements.html

1千万件の INSERT を 1000 件ごとの bulk_insert で実行してみると3分半ほどで完了しました。block の最適値はある記事によると 1000 らしいです。

sql
1CREATE TABLE IF NOT EXISTS user (id INT, value INT);
2
3DROP PROCEDURE IF EXISTS bulk_insert;
4DELIMITER ;;
5CREATE PROCEDURE bulk_insert(IN len INT, IN block INT)
6BEGIN
7  SET @cnt = 0;
8  loop1: LOOP
9    SET @sql = 'INSERT INTO user VALUES';
10    SET @i = 0;
11    loop2: LOOP
12      IF @cnt >= len THEN LEAVE loop1; END IF;
13      SET @id = @cnt + 1;
14      SET @sql = CONCAT(@sql, ' (', @id, ',', @cnt, ')');
15      SET @cnt = @cnt + 1;
16      SET @i = @i + 1;
17      IF @i < block AND @cnt < len THEN SET @sql = CONCAT(@sql, ',');
18      ELSE LEAVE loop2; END IF;
19    END LOOP loop2;
20    PREPARE stmt FROM @sql;
21    EXECUTE stmt;
22    DEALLOCATE PREPARE stmt;
23  END LOOP loop1;
24END;;
25DELIMITER ;
26TRUNCATE user; CALL bulk_insert(10000000, 1000);
27
28SELECT count(*) FROM user;
29SELECT * FROM user LIMIT 10;

sql
1mysql> TRUNCATE user; CALL bulk_insert(10000000, 1000);
2
3Query OK, 0 rows affected (3 min 23.23 sec)
4
5mysql> select count(*) from user LIMIT 10;
6+----------+
7| count(*) |
8+----------+
9| 10000000 |
10+----------+
111 row in set (0.58 sec)
12
13mysql> select * from user LIMIT 10;
14+------+-------+
15| id   | value |
16+------+-------+
17|    1 |     0 |
18|    2 |     1 |
19|    3 |     2 |
20|    4 |     3 |
21|    5 |     4 |
22|    6 |     5 |
23|    7 |     6 |
24|    8 |     7 |
25|    9 |     8 |
26|   10 |     9 |
27+------+-------+
2810 rows in set (0.00 sec)
29
30mysql> SELECT * FROM user LIMIT 10 OFFSET 9999990;
31+----------+---------+
32| id       | value   |
33+----------+---------+
34|  9999991 | 9999990 |
35|  9999992 | 9999991 |
36|  9999993 | 9999992 |
37|  9999994 | 9999993 |
38|  9999995 | 9999994 |
39|  9999996 | 9999995 |
40|  9999997 | 9999996 |
41|  9999998 | 9999997 |
42|  9999999 | 9999998 |
43| 10000000 | 9999999 |
44+----------+---------+
4510 rows in set (5.45 sec)