mysql快速添加100w记录_MySql || 快速创建100w条记录

平时每个开发者都会讨论数据量大时，sql的优化问题。但是并不是每个人都会有100w的数据量可以用来实战，那么今天我们就自己动手，模拟一个100w数据量的表。

创建原理

其实创建的方法有很多，有快的也有慢的。本博客中写的当然不是最快的那个，但确实是比较好操作和理解的。那么我先来说明一下它的原理：它是利用mysql中的在MEMORY引擎的特点，用于快速的插入100w的数据在内存中存放，然后再利用sql插入到目标的表当中。

操作步骤

创建表t_user，这是用于存放数据的表。

1 CREATE TABLE`t_user` (2 `id` int(11) NOT NULLAUTO_INCREMENT,3 `c_user_id` varchar(36) NOT NULL DEFAULT ‘‘,4 `c_name` varchar(22) NOT NULL DEFAULT ‘‘,5 `c_province_id` int(11) NOT NULL,6 `c_city_id` int(11) NOT NULL,7 `create_time` datetime NOT NULL,8 PRIMARY KEY(`id`)9 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.创建内存表t_user_memory，这是用于快速插入数据的表。

1 CREATE TABLE`t_user_memory` (2 `id` int(11) NOT NULLAUTO_INCREMENT,3 `c_user_id` varchar(36) NOT NULL DEFAULT ‘‘,4 `c_name` varchar(22) NOT NULL DEFAULT ‘‘,5 `c_province_id` int(11) NOT NULL,6 `c_city_id` int(11) NOT NULL,7 `create_time` datetime NOT NULL,8 PRIMARY KEY(`id`)9 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

3.创建随机字符串函数randStr()，用于在给c_name赋值时更加随机。

delimiter $$CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT) RETURNS varchar(255) CHARSET utf8mb4

DETERMINISTICBEGIN

DECLARE chars_str varchar(100) DEFAULT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789‘;DECLARE return_str varchar(255) DEFAULT ‘‘;DECLARE i INT DEFAULT 0;WHILE i

这个函数的语法不用解释也会看得懂，但是还是有几个点需要注意的。

delimiter关键字，这是一个声明结束符的关键字。简单的来说就是，防止和BEGIN和END之间的语句冲突，因为我们希望这里面的语句是连续执行的。

CONCAT(str,str)链接两个字符串组成一个新的字符串。

FLOOR(num)函数，向下取整。例如floor(1.23) = 1 、floor(-1.23) = -2。

4.创建随机时间函数randDataTime()，为了后期的时间更加随机。

1 delimiter $$2 CREATE DEFINER = `root`@`%` FUNCTION `randDataTime` (sd DATETIME, ed DATETIME) RETURNS datetimeDETERMINISTIC3 BEGIN

4 DECLARE

5 sub INT DEFAULT 0;6 DECLARE

7 ret DATETIME;8 SET sub = ABS(9 UNIX_TIMESTAMP(ed) -UNIX_TIMESTAMP(sd)10 ) ;11 SET ret =DATE_ADD(12 sd,13 INTERVAL FLOOR(1 + RAND() *(sub - 1)) SECOND14 ) ;15 RETURNret ;16 END $$

里面的需要注意的是DATE_ADD(dateTime,INTERVAL  num  SECOND)函数，第一个参数是被添加的时间，第二个参数是一个组合，表明是添加的长度，如：(INTERVAL 1  YEAR)表示在原来的基础上添加一年的时间间隔。

5.创建插入数据存储过程add_t_user_memory (int)

1 delimiter $$2 CREATE DEFINER=`root`@`%` PROCEDURE add_t_user_memory (IN n INT)3 BEGIN

4 DECLARE

5 i INT DEFAULT 1;6 WHILE i

15 (16 uuid(),17 randStr (20),18 FLOOR(RAND() * 1000),19 RAND() * 100,20 NOW()21 );22 SET i = i + 1;23 END

24 WHILE;25 END $$

执行过程中的问题

在完成上面的声明之后我们就可以用CALL  add_t_user_memory (1000000)来创建100w的数据啦，这大概需要20分钟的时间。但是在调用的过程中却发现了一个问题如下图所示。

原来是‘t_user_memory‘ 已经满了。它是我在上面定义的用内存存放数据的表，在MYSQL中默认的大小是16MB。这个大小只能放4w多条数据，所以我们要想办法把它扩大。那么可以执行下面语句

1 SET GLOBAL max_heap_table_size = 1024 * 1024 * 1024 * 1;2

3 #查看当前的设置的大小4 select @@max_heap_table_size;

注意：修改需要重新链接mysql才能更新修改。这个修改自己机器上玩玩就可以了，在生产环境这么改有什么后果我可不负责。

完善数据库

执行下面语句，大概需要10s就可以插入到t_user中。

INSERT INTO t_user SELECT * FROM t_user_memory;

2.打乱创建时间。

#更新年间隔UPDATE t_user SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 4)) year);

#更新秒间隔UPDATE t_user SET create_time=randDataTime(NOW(),create_time);

完成

这样，我们的百万级数据库就创建完成啦！然后，我们可以将t_user_memory这个表清空，毕竟它是很占内存的，你数据有多少内存就占多少。下次再结合业务试试SQL优化怎么玩。