利用Mysql存储过程造百万级数据

青旅半醒 2023-09-25 15:56 231阅读 0赞

1.准备工作

（1）由于是使用存储过程，mysql从5.0版开始支持存储过程，那么需要mysql的版本在5.0或者以上。如何查看mysql的版本，使用下面sql语句查看：

01991ba454ec45fd921066928e7d3b9c.png

（2）创建两张表，表结构一致，但使用的存储引擎不一样，如下所示，普通表使用mysql5.5版本后默认的INNODB存储引擎，内存表使用MEMORY存储引擎。

由于MEMORY存储不常用这里简单说一下其特点：MEMORY引擎表结构创建在磁盘上，数据全部放在内存中，访问速度较快，但是当MySQL重启后或者一旦系统奔溃的话，数据都会消失，结构还存在。

  1. # 创建普通表
  2. CREATE TABLE `user_info` (
  3.     `id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  4.     `name` VARCHAR ( 30 ) NOT NULL COMMENT '用户名',
  5.     `phone` VARCHAR ( 11 ) NOT NULL COMMENT '手机号',
  6.     `status` TINYINT ( 1 ) NULL DEFAULT NULL COMMENT '用户状态:停用0，启动1',
  7.     `create_time` datetime NOT NULL COMMENT '创建时间',
  8.     PRIMARY KEY ( `id` ) USING BTREE 
  9. ) ENGINE = INNODB AUTO_INCREMENT = 10001 CHARACTER 
  10. SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息表';
  12. # 创建内存表
  13. CREATE TABLE `memory_user_info` (
  14.     `id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  15.     `name` VARCHAR ( 30 ) NOT NULL COMMENT '用户名',
  16.     `phone` VARCHAR ( 11 ) NOT NULL COMMENT '手机号',
  17.     `status` TINYINT ( 1 ) NULL DEFAULT NULL COMMENT '用户状态:停用0，启动1',
  18.     `create_time` datetime NOT NULL COMMENT '创建时间',
  19.     PRIMARY KEY ( `id` ) USING BTREE 
  20. ) ENGINE = MEMORY AUTO_INCREMENT = 10001 CHARACTER 
  21. SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息内存表';

2.主要实现步骤

（1）创建自动生成数据的函数，插入时使用；

（2）创建插入内存表数据存储过程，调用已创建好的数据生成函数；

（3）创建内存表数据插入普通表存储过程；

（4）调用存储过程。

（5）数据查看验证

3.创建自动生成数据的函数

（1）生成n个随机数字

  1. DELIMITER //
  2. DROP FUNCTION
  3. IF
  4.     EXISTS randomNum // CREATE FUNCTION randomNum (
  5.         n INT,
  6.         chars_str VARCHAR ( 10 )) RETURNS VARCHAR ( 255 ) BEGIN
  7.     DECLARE
  8.         return_str VARCHAR ( 255 ) DEFAULT '';
  9.     DECLARE
  10.         i INT DEFAULT 0;
  11.     WHILE
  12.             i < n DO
  14.             SET return_str = concat(
  15.                 return_str,
  16.             substring( chars_str, FLOOR( 1 + RAND()* 10 ), 1 ));
  18.         SET i = i + 1;
  20.     END WHILE;
  21.     RETURN return_str;
  23. END // 
  24. DELIMITER;

函数运行截图：

6658236ca65b466087e578f450036171.png

脚本所用到的mysql函数及其功能如下：

a.concat()：将多个字符串连接成一个字符串。

b.Floor()：向下取整。

c.substring(string, position, length)

第一个参数：string指的是需要截取的原字符串。

第二个参数：position指的是从哪个位置开始截取子字符串，这里字符的位置编码序号是从1开始，若position为负数则从右往左开始数位置。

第三个参数：length指的是需要截取的字符串长度，如果不写，则默认截取从position开始到最后一位的所有字符。

d.RAND()：只能生成0到1之间的随机小数。

（2）创建随机生成手机号函数

  1. DELIMITER //
  2. DROP FUNCTION
  3. IF
  4.     EXISTS getPhone // CREATE FUNCTION getPhone () RETURNS VARCHAR ( 11 ) BEGIN
  5.     DECLARE
  6.         head CHAR ( 3 );
  7.     DECLARE
  8.         phone VARCHAR ( 11 );
  9.     DECLARE
  10.         bodys VARCHAR ( 65 ) DEFAULT "130 131 132 133 134 135 136 137 138 139 186 187 189 151 157";
  11.     DECLARE
  12.         STARTS INT;
  14.     SET STARTS = 1+floor ( rand()* 15 )* 4;
  16.     SET head = trim(
  17.     substring( bodys, STARTS, 3 ));
  19.     SET phone = trim(
  20.         concat(
  21.             head,
  22.         randomNum ( 8, '0123456789' )));
  23.     RETURN phone;
  25. END // 
  26. DELIMITER;

函数运行截图：

e9da58c3d33b419992f402e5d51f846e.png

（3)创建随机生成用户名函数

  1. DELIMITER //
  2. DROP FUNCTION
  3. IF
  4.     EXISTS randName // CREATE FUNCTION randName ( n INT ) RETURNS VARCHAR ( 255 ) CHARSET utf8mb4 DETERMINISTIC BEGIN
  5.     DECLARE
  6.         chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  7.     DECLARE
  8.         return_str VARCHAR ( 30 ) DEFAULT '';
  9.     DECLARE
  10.         i INT DEFAULT 0;
  11.     WHILE
  12.             i < n DO
  14.             SET return_str = concat(
  15.                 return_str,
  16.             substring( chars_str, FLOOR( 1 + RAND() * 62 ), 1 ));
  18.         SET i = i + 1;
  20.     END WHILE;
  21.     RETURN return_str;
  23. END // 
  24. DELIMITER;

函数运行截图：

f19fd8f309c2444b9fa4176ac4accae4.png

（4）随机生成用户状态函数

  1. DELIMITER //
  2. DROP FUNCTION
  3. IF
  4.     EXISTS randStatus // CREATE FUNCTION randStatus ( ) RETURNS TINYINT ( 1 ) BEGIN
  5.     DECLARE
  6.         user_status INT ( 1 ) DEFAULT 0;
  8.     SET user_status =
  9.     IF
  10.         ( FLOOR( RAND() * 10 ) <= 4, 1, 0 );
  11.     RETURN user_status;
  13. END // 
  14. DELIMITER;

函数运行截图：

9789a52a470e47d895e24ccea5b6503f.png

（5）查看数据库中所有自定义函数信息

45f7fe19acc141d099f1d8f000e0cbaa.png

4.创建存储过程

（1）创建插入内存表数据存储过程

  1. DELIMITER //
  2. DROP PROCEDURE
  3. IF
  4.     EXISTS add_memory_user_info // CREATE PROCEDURE `add_memory_user_info` ( IN n INT ) BEGIN
  5.     DECLARE
  6.         i INT DEFAULT 1;
  7.     WHILE
  8.             ( i <= n ) DO
  9.             INSERT INTO memory_user_info ( `name`, `phone`, `status`, `create_time` )
  10.         VALUES
  11.             (
  12.                 randName ( 20 ),
  13.                 getPhone (),
  14.                 randStatus (),
  15.             NOW());
  17.         SET i = i + 1;
  19.     END WHILE;
  21. END // 
  22. DELIMITER;

入参n是多少就表示往内存表memory_user_info插入多少条数据

存储过程运行截图：

142f134ec52746088851d6801d0707bb.png

（2）创建内存表数据插入普通表存储过程

  1. DELIMITER //
  2. DROP PROCEDURE
  3. IF
  4.     EXISTS add_user_info // CREATE PROCEDURE `add_user_info` ( IN n INT, IN count INT ) BEGIN
  5.     DECLARE
  6.         i INT DEFAULT 1;
  7.     WHILE
  8.             ( i <= n ) DO
  9.             CALL add_memory_user_info ( count );
  10.         INSERT INTO user_info SELECT
  11.         * 
  12.         FROM
  13.             memory_user_info;
  14.         DELETE 
  15.         FROM
  16.             memory_user_info;
  18.         SET i = i + 1;
  20.     END WHILE;
  22. END // 
  23. DELIMITER;

这是最主要的存储过程，也是入口，利用对内存表的循环插入和删除来实现批量生成数据，不需要更改mysql默认的max_heap_table_size值（默认值是16M），max_heap_table_size 的作用是配置用户创建内存临时表的大小，配置的值越大，能存进内存表的数据就越多。

存储过程运行截图：

5c035e25c7b94142bd1a39113d53a61e.png

（3）查看存储过程的状态

  1. -- 查看数据库所有的存储过程
  2. SHOW PROCEDURE STATUS;
  3. -- 模糊查询存储过程
  4. SHOW PROCEDURE STATUS LIKE 'add%';

模糊查询结果：

8dc62341dc1744df86864021fa7e0b8a.png

5.调用存储过程

mysql称存储过程的执行为调用，因此mysql执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。

通过调用add_user_info存储过程，不断循环插入内存表memory_user_info，再从内存表获取数据插入普通表user_info，然后删除内存表数据，以此循环直至循环结束。循环100次，每次生成10000条数据，共生成一百万条数据。

  1. CALL add_user_info(100,10000);

6.数据查看验证

在普通表数据达到6万条时，已经耗时大概在23分钟左右，以这个时间推算，100万数据生成预计需要6小时左右。耗时的点主要是在四个随机生成字段数据的函数上。如果字段数据不要求随机，那么将会快很多。

466b97da8380434aa649cfbf065adfcf.png

数据记录如下效果：

1898e94a809748659f68f95a9883c4f6.png

发表评论

表情：
评论列表 （有 0 条评论，231人围观）

还没有评论，来说两句吧...

相关阅读

    相关 java导出百万级数据

    JAVA 实现大数据量导出操作时，如果采用POI直接导出，会出现内存溢出的情况。再者EXCEL的SHEET也存在行数的限制，Excel2003版最大行数是655536行、Exc

    红太狼红太狼/ 0 赞/ 551 阅读