【原创】利用MySQL触发器高性能造数据

MySQL 触发器功能比较简单,大部分只用来简单的更新第三方表,今天我来演示下MySQL触发器在造数据方面的功效。 

 
 
下面是基表结果:
 

  1. CREATE TABLE `tb1` ( 
  2.   `id` varchar(255) NOT NULL
  3.   `log_date` date DEFAULT NULL
  4.   PRIMARY KEY (`id`) 
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED 
 
 
这个是对基表的批量插入存储过程:
 

  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. BEGIN 
  7.   DECLARE t_count     INT
  8.   DECLARE v_log_date DATE
  9.   DECLARE c_date  DATE DEFAULT '2012-07-01'
  10.  
  11.   SET t_count = cur_1 + (f_input - 1); 
  12.   SET @@autocommit = 0; 
  13.  
  14.   WHILE cur_1 <= t_count 
  15.   DO 
  16.     IF mod(cur_1, f_commit_num) = 1 THEN 
  17.       START TRANSACTION
  18.     END IF; 
  19.     SET v_log_date = date_add(c_date, INTERVAL ceil(rand() * 3) * (ceil(rand() * 15)) DAY); 
  20.     INSERT INTO tb1 (id, log_date) VALUES (cur_1, v_log_date); 
  21.     IF mod(cur_1, f_commit_num) = 0 THEN 
  22.       COMMIT
  23.     END IF; 
  24.     SET cur_1 = cur_1 + 1; 
  25.   END WHILE; 
  26.   COMMIT
  27. END 
 
 
 
 
单线程造数据:
 
 

  1. mysql> call sp_generate_tb1_data(1,10000000,200); 
  2. Query OK, 0 rows affected (8 min 20.00 sec) 
 
1KW行记录花了8分钟多,也就是一个线程每秒插入2W条记录。
 
 

  1. mysql> select count(*) from tb1; 
  2. +----------+ 
  3. count(*) | 
  4. +----------+ 
  5. | 10000000 | 
  6. +----------+ 
  7. 1 row in set (34.35 sec) 
 
 
创建复制表:
 

  1. create table tb2 like tb1; 
  2. create table tb3 like tb1; 
 
这里比较恶心的是多建立了一个表tb3,因为MySQL触发器暂时不支持自己对自己插入。
 
 
 
这个是基于表tb3的后置插入触发器:
 

  1. CREATE  
  2.     DEFINER = 'root'@'localhost' 
  3. TRIGGER db_myisam.ti_tb3_after 
  4.     AFTER INSERT 
  5.     ON db_myisam.tb3 
  6.     FOR EACH ROW 
  7. BEGIN 
  8.   DECLARE v_cur_1    INT DEFAULT 1; 
  9.   DECLARE v_log_date DATE
  10.   DECLARE c_date     DATE DEFAULT '2012-07-01'
  11.  
  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.     INSERT INTO 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 
 
 
1KW记录花了不到6分钟,也就是一个线程每秒插入大于3W记录。
 

  1. mysql> insert into tb3 values (2,current_date()); 
  2. Query OK, 1 row affected (5 min 14.07 sec) 
 
可以看到,速度比存储过程提升了60%左右。 
 
怎么样,很HAPPY吧?





本文转自 david_yeung 51CTO博客,原文链接:http://blog.51cto.com/yueliangdao0608/1066993,如需转载请自行联系原作者
上一篇:阿里云贵州教育脱贫正式展开,黔东南百位脱贫一线技术人员参加培训


下一篇:如何找到一个好的Joomla主机提供商