利用MySQL触发器高性能造数据

利用MySQL触发器高性能造数据

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

下面是基表结果：

1. CREATETABLE `tb1` (
2. `id` varchar(255) NOTNULL,
3. `log_date` dateDEFAULTNULL,
4. PRIMARYKEY (`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. )
7. BEGIN
8. DECLARE t_count INT;
9. DECLARE v_log_date DATE;
10. DECLARE c_date DATEDEFAULT'2012-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

单线程造数据：  

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> selectcount(*) from tb1;
2. +----------+
3. | count(*) |
4. +----------+
5. | 10000000 |
6. +----------+
7. 1 row inset (34.35 sec)

  创建复制表：  

1. createtable tb2 like tb1;
2. createtable tb3 like tb1;

  这里比较恶心的是多建立了一个表tb3，因为MySQL触发器暂时不支持自己对自己插入。       这个是基于表tb3的后置插入触发器：  

1. CREATE
2. DEFINER = 'root'@'localhost'
3. TRIGGER db_myisam.ti_tb3_after
4. AFTERINSERT
5. ON db_myisam.tb3
6. FOR EACH ROW
7. BEGIN
8. DECLARE v_cur_1 INTDEFAULT 1;
9. DECLARE v_log_date DATE;
10. DECLARE c_date DATEDEFAULT'2012-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

  1KW记录花了不到6分钟，也就是一个线程每秒插入大于3W记录。  

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

  可以看到，速度比存储过程提升了60%左右。   怎么样，很HAPPY吧？