MySQL插入百万级数据

参考

MySQL 轻松创建千万级测试数据

最近在一个博客看到了一篇文章，主要是讲述如何使用MySQL存储过程和函数创建百万级别测试数据，作者原文标题是千万级测试数据，但是我只测试了10W级别的数据。

测试环境

CPU i5@2.30GHz
内存 15GB
MySQL版本—8.0.17
MySQLWORKBENCH

1.创建数据表

创建两张表，一张为内存表，一张为正式表,内存表主要放存储过程生成的随机数据，正式表再用查询插入从内存表中获取数据。

CREATE TABLE `vote_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(20) NOT NULL,
  `vote_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_user_id` (`user_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

1.1创建内存表

CREATE TABLE `vote_record_memory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(20) NOT NULL,
  `vote_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_user_id` (`user_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

2.使用存储过程

随机生成字符串，传入参数是字符串长度

CREATE DEFINER=`root`@`%` FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '' ;
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1));
        SET i = i + 1;
    END WHILE;
    RETURN return_str;
END

2.1创建存储过程

传入参数是创建的数据量

CREATE DEFINER=`root`@`%` PROCEDURE `add_vote_memory`(IN n int)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= n) DO
        INSERT INTO vote_record_memory (user_id, vote_id, group_id, create_time) VALUES (rand_string(20), FLOOR(RAND() * 1000), FLOOR(RAND() * 100), NOW());
        SET i = i + 1;
    END WHILE;
END

MySQL的存储过程要注意两个点：

• 1、在执行上面的语句之前要先执行DELIMITER $$,这条命令的作用是可以将分号;暂时注销原先功能，要不然存储过程中间的分号就会报错。
• 2、存储过程的名称所用的不是单引号而是左边键盘数字1左边的反引号。

  单引号 '
  反引号 `
  双引号 "

2.2调用存储过程

CALL add_vote_memory(1000000)

这里第一次使用MySQL WORKBENCH执行的时候大概在30秒左右报错,这是 WORKBENCH的一个设置，大概就是最长的连接时间，修改方式

edit>>Preferences>>SQL Editor>>DBMS connection read timeout interval(in seconds):

大概执行时间30W数据要15分钟以上，至于100W数据就已经没耐心等下去了。
这里还有一个容易报错的地方就是单表的最大容量，mysql默认每张表占据的存储空间有一个最大值，可以修改这个最大值,具体细节可以查看配置文件.

调用存储过程，并且参数为10W的结果:

cmd中测试插入10W数据.png

100W就是这个时间乘以10倍，大概40分钟左右

总结:
看别人的文章学习技巧的时候还是要自己动手操作一遍才能知道有哪些坑，因为每个人的知识边界都不一样，有些你认为是常识的东西说不定别人就不知道，反之亦然，所以学习编程重点就是多写代码多做测试。