mysql之储存过程_快速生成海量数据测试

一、 数据的创建脚本

目的：学习、测试mysql海量数据的场景，需要先生成大量数据测试。

1.1 创建表

# 创建MyISAM模式表方便批量跑数据
CREATE TABLE `teddy`
(
    `id`     int(11) NOT NULL AUTO_INCREMENT,
    `name`   varchar(20) DEFAULT NULL,
    `age`    varchar(3)  DEFAULT NULL,
    `sex`    varchar(2)  DEFAULT NULL,
    `phone`  varchar(11) DEFAULT NULL,
    `create` datetime    DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT ='基础用户信息表';

1.2 创建储存过程

# 创建存储过程
DELIMITER $
CREATE PROCEDURE my_insert()    # 存储过程方法名称
BEGIN
    DECLARE n int DEFAULT 1;
    start transaction ; # 开启事务，不然更慢（最好是给自己数据库参数都加大）
    # 循环去添加
    ts:LOOP
        INSERT INTO `teddy`(`name`, `age`, `sex`, `phone`, `create`) 
        VALUES ('小韩', '0', '1', '18801213139', '2020-09-30 08:02:42');
        SET n = n + 1;
        IF n = 10000000 THEN
            LEAVE ts;
        END IF;
    END LOOP ts;
end;$

1.3 造汽车（有了材料就开造）

# 执行存储过程
CALL my_insert();

select count(1) from teddy; # 确认数据库有数据添加成功   执行下面步骤

# 更改MyISAM为InnoDB
alter table `teddy` engine = InnoDB;    # 执行完my_insert()别忘记数据库改为InnoDB

二、导入数据（方案二）

个人建议：讲真的，如果没有硬性要求就换种姿势去进行数据的导入。不要直接去数据库执行脚本语句生成数据。

mysql官方文档说得很清楚。”load data infile”导入数据比insert要快20倍。所以我们先生成一千万条数据的文件。
然后将数据导入表中是很快的。

3.1 实战思路

生成文件：假如有个用户表(`id`,`name`, `age`, `sex`, `phone`, `create`)，id是自动增长，
我们现在需要生成一千万条数据，速度还能接受，耗时236秒，文件大小315M。

load data infile "/user_data.txt" into table `teddy`
fields terminated by ','
lines terminated by '\n'
(`name`, `age`, `sex`, `phone`, `create`);

温馨提示：load data infile命令有安全限制，最好是把数据拿到mysql server端，再通过mysql -uxxx -pxxx进入命令，再导入。
我的虚拟机导入耗时57秒

三、优化指引：

• mysql官网给出的调优参数

指令	注释
max-item-per-user (arg)	每个用户最多有多少商品数;在生成数据时,随机为每个用户生成商品,数量取值范围是0,MAX.比如默认生成10000个用户,每个用户最多10个商品,那么大致就可以知道生成的数据规模
mysql-max-connection (arg)	mysql最大连接数(default=25)
mysql-password (arg)	mysql password
mysql-url (arg)	mysql连接url(default=jdbc:mysql://localhost:3306/?serverTimezone=UTC)
mysql-username (arg)	mysql username
thread (thread)	生成数据的线程数(default=50)
user-count (arg)	用户表生成多少行记录,同时也是店铺表和仓库表的记录数,因为一个用户只拥有一个店铺和一个仓库(default=10000),当生成的记录数达到该值时,数据生成任务结束

😁 作者：Teddy (公众号：鸡仓故事汇)
ok！到这里就大功告成，小编（Teddy）在这里先感谢大家的到来。
虽然不是太详细，小编已经很努力，给小编来个一键三连(点赞，关注，收藏)，小编会越来越努力。。。