High-performance data creation using MySQL triggers

MySQL triggers are simple. Most of them are used to update third-party tables. Today I will demonstrate the functions of MySQL triggers in Data creation.

The result of the base table is as follows:

1. CREATETABLE 'tb1 '(
2. 'Id'varchar (255) NOTNULL,
3. 'Log _ date' dateDEFAULTNULL,
4. PRIMARYKEY ('id ')
5. ) ENGINE = InnoDB default charset = utf8 ROW_FORMAT = FIXED

This is the batch insert Stored Procedure for the base table:

1. Create definer = 'root' @ 'localhost'
2. PROCEDURE db_myisam.sp_generate_tb1_data (IN cur_1 INT,
3. IN f_input INT,
4. IN f_commit_num INT
5. -- Stands for which month's date.
6. )
7. BEGIN
8. DECLARE t_count INT;
9. DECLARE v_log_date DATE;
10. DECLARE c_date DATEDEFAULT '2017-07-01 ';
12. SET t_count = cur_1 + (f_input-1 );
13. SET @ autocommit = 0;
15. WHILE cur_1 <= t_count
16. DO
17. IF mod (cur_1, f_commit_num) = 1 THEN
18. Start transaction;
19. End if;
20. SET v_log_date = date_add (c_date, INTERVAL ceil (rand () * 3) * (ceil (rand () * 15) DAY );
21. INSERTINTO tb1 (id, log_date) VALUES (cur_1, v_log_date );
22. IF mod (cur_1, f_commit_num) = 0 THEN
23. COMMIT;
24. End if;
25. SET cur_1 = cur_1 + 1;
26. End while;
27. COMMIT;
28. END

Single-threaded Data creation:

1. Mysql> call sp_generate_tb1_data (200 );
2. Query OK, 0 rows affected (8 minutes 20.00 sec)

It takes more than 8 minutes to record 1 kW rows, that is, a thread inserts 2 million records per second.

1. Mysql> selectcount (*) from tb1;
2. + ---------- +
3. | Count (*) |
4. + ---------- +
5. | 1, 10000000 |
6. + ---------- +
7. 1 row inset (34.35 sec)

Create a copy table:

1. Createtable tb2 like tb1;
2. Createtable fig like tb1;

Here, it is quite disgusting that a table of is created, because MySQL triggers do not support self-insertion. This is a post-insert Trigger Based on table:

1. CREATE
2. DEFINER = 'root' @ 'localhost'
3. TRIGGER db_myisam.ti_tb3_after
4. AFTERINSERT
5. ON db_myisam.78
6. FOR EACH ROW
7. BEGIN
8. DECLARE v_cur_1 INTDEFAULT 1;
9. DECLARE v_log_date DATE;
10. DECLARE c_date DATEDEFAULT '2017-07-01 ';
12. WHILE v_cur_1 <= 10000000
13. DO
14. SET v_log_date = date_add (c_date, INTERVAL ceil (rand () * 3) * (ceil (rand () * 15) DAY );
15. INSERTINTO tb2 (id, log_date) VALUES (v_cur_1, v_log_date );
16. SET v_cur_1 = v_cur_1 + 1;
17. End while;
18. END

It takes less than 6 minutes for a 1 kW record, that is, a thread inserts more than records per second.

1. Mysql> insertinto fig (2, current_date ());
2. Query OK, 1 row affected (5 minutes 14.07 sec)

As you can see, the speed is about 60% higher than that of the stored procedure. How are you doing? HAPPY?