以下のプロシージャ 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)
下記のような回答は推奨されていません。
このような回答には修正を依頼しましょう。
2023/01/25 05:10