【原創】利用MySQL觸發器高效能造資料

 MySQL 觸發器功能比較簡單，大部分只用來簡單的更新第三方表，今天我來示範下MySQL觸發器在造資料方面的功效。 

  下面是基表結果： 

 

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

  這個是對基表的批量插入預存程序: 

 

  
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 
 

    單線程造資料：  

 

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

 1KW行記錄花了8分鐘多，也就是一個線程每秒插入2W條記錄。  

 

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

  建立複製表： 

 

  
create table tb2 like tb1; 
  
create table tb3 like tb1; 
 

 這裡比較噁心的是多建立了一個表tb3，因為MySQL觸發器暫時不支援自己對自己插入。   這個是基於表tb3的後置插入觸發器： 

 

  
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 
 

  1KW記錄花了不到6分鐘，也就是一個線程每秒插入大於3W記錄。 

 

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

 可以看到，速度比預存程序提升了60%左右。  怎麼樣，很HAPPY吧？

本文出自 “上帝，咱們不見不散！” 部落格，請務必保留此出處http://yueliangdao0608.blog.51cto.com/397025/1066993