[Original] 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:

 
 

  
  
CREATE TABLE `tb1` ( 
  
  
  `id` varchar(255) NOT NULL, 
  
  
  `log_date` date DEFAULT NULL, 
  
  
  PRIMARY KEY (`id`) 
  
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED 
 
 

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

 
 

  
  
CREATE DEFINER = 'root'@'localhost' 
  
  
PROCEDURE db_myisam.sp_generate_tb1_data(IN cur_1        INT, 
  
  
                                         IN f_input      INT, 
  
  
                                         IN f_commit_num INT 
  
  
                                         -- Stands for which month's date. 
  
  
) 
  
  
BEGIN 
  
  
  DECLARE t_count     INT; 
  
  
  DECLARE v_log_date DATE; 
  
  
  DECLARE c_date  DATE DEFAULT '2012-07-01'; 
  
  
 
  
  
  SET t_count = cur_1 + (f_input - 1); 
  
  
  SET @@autocommit = 0; 
  
  
 
  
  
  WHILE cur_1 <= t_count 
  
  
  DO 
  
  
    IF mod(cur_1, f_commit_num) = 1 THEN 
  
  
      START TRANSACTION; 
  
  
    END IF; 
  
  
    SET v_log_date = date_add(c_date, INTERVAL ceil(rand() * 3) * (ceil(rand() * 15)) DAY); 
  
  
    INSERT INTO tb1 (id, log_date) VALUES (cur_1, v_log_date); 
  
  
    IF mod(cur_1, f_commit_num) = 0 THEN 
  
  
      COMMIT; 
  
  
    END IF; 
  
  
    SET cur_1 = cur_1 + 1; 
  
  
  END WHILE; 
  
  
  COMMIT; 
  
  
END 
 
 

Single-threaded Data creation:

 
 

  
  
mysql> call sp_generate_tb1_data(1,10000000,200); 
  
  
Query OK, 0 rows affected (8 min 20.00 sec) 
 
 

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

 
 

  
  
mysql> select count(*) from tb1; 
  
  
+----------+ 
  
  
| count(*) | 
  
  
+----------+ 
  
  
| 10000000 | 
  
  
+----------+ 
  
  
1 row in set (34.35 sec) 
 
 

Create a copy table:

 
 

  
  
create table tb2 like tb1; 
  
  
create table tb3 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:

 
 

  
  
CREATE  
  
  
    DEFINER = 'root'@'localhost' 
  
  
TRIGGER db_myisam.ti_tb3_after 
  
  
    AFTER INSERT 
  
  
    ON db_myisam.tb3 
  
  
    FOR EACH ROW 
  
  
BEGIN 
  
  
  DECLARE v_cur_1    INT DEFAULT 1; 
  
  
  DECLARE v_log_date DATE; 
  
  
  DECLARE c_date     DATE DEFAULT '2012-07-01'; 
  
  
 
  
  
  WHILE v_cur_1 <= 10000000 
  
  
  DO 
  
  
    SET v_log_date = date_add(c_date, INTERVAL ceil(rand() * 3) * (ceil(rand() * 15)) DAY); 
  
  
    INSERT INTO tb2 (id, log_date) VALUES (v_cur_1, v_log_date); 
  
  
    SET v_cur_1 = v_cur_1 + 1; 
  
  
  END WHILE; 
  
  
END 
 
 

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

 
 

  
  
mysql> insert into tb3 values (2,current_date()); 
  
  
Query OK, 1 row affected (5 min 14.07 sec) 
 
 

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

This article is from "god, let's see it !" Blog, please be sure to keep this source http://yueliangdao0608.blog.51cto.com/397025/1066993