MySQL如何按表创建千万级的测试数据,进行数据库压力测试和练习

原文章发表在 https://www.jianshu.com/p/10791343e67a 现在有了自己的静态博客,特意搬过来

1. 前言

笔者采用Intellij IDEA内置的数据库模块进行操作,也推荐大家使用专业点的IDE来操作数据库,通过命令行是很痛苦的。但是您要用命令行来操作以下内容,也是可以的,我会尽量照顾到您的使用习惯,但是不保证代码都能执行无误

在学习完数据库及SQL的知识之后,想必各位同学都对数据库的性能优化有很深的兴趣,但是性能优化都是基于TB级的数据进行的。

什么是TB级的数据

今天我就带着大家来利用_MySQL循环和存储过程_做一个对特定表的创建千万行数据的实例


假设我们目前有这样一张表:

users表

这张表有7个Columns,对应7个常见的简单的用户属性

为了照顾性少数者人群,把gender设为可变字符型较合适

2. 随机生成数据

大家可能知道MySQL里面自带一个随机数生成的函数RAND()

它能生成0-1的浮点数

select RAND();

生成的结果

常用函数大全: http://bxbx258.blog.51cto.com/339450/106008 可当手册查

2.1. 随机生成给定数目的字符串

随机生成给定数目的字符串

这里的循环采用WHILE循环,循环里面采用字符串拼接函数CONCAT
可复制的代码见下:

CREATE DEFINER =`您的数据库名`@`您的主机地址` FUNCTION `rand_string`(n INT)
  RETURNS VARCHAR(255)
  CHARSET utf8
  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() * 80), 1));
      SET i = i + 1;
    END WHILE;
    RETURN return_str;
  END;

生成的字符串实例:

生成的字符串实例

用户名和密码都可以通过以上介绍的方式添加

邮箱的添加方式
可直接在insertvalue对应字段写上concat(rand_string(5), '@domain.com')既可

2.2. 随机生成DATEDATETIME类型

随机生成DATE类型实例

关于LPAD函数,具体做为月份和日期的补零作用!生效如下介绍

Returns the string str:str, left-padded with the string padstr to a length of len characters. If str is longer than len:N, the return value is shortened to len characters.

mysql> SELECT LPAD('hi',4,'??');
        -> '??hi'
mysql> SELECT LPAD('hi',1,'??');
        -> 'h'

可复制代码见下:

CREATE DEFINER =`您的数据库名`@`您的主机地址` FUNCTION `randDate`()
  RETURNS VARCHAR(255)
  CHARSET utf8
  BEGIN
    DECLARE aDate CHAR(10) DEFAULT '';
    SET aDate = CONCAT(1949 + FLOOR((RAND() * 68)), '-', LPAD(FLOOR(1 + (RAND() * 12)), 2, 0), '-',
                       LPAD(FLOOR(3 + (RAND() * 8)), 2, 0));
    RETURN aDate;
  END;

运行实例:

select randDate();

随机生成DATE类型:

随机生成DATE类型

关于DATETIME类型
在刚刚写好的randDate上进行改造,加上一下这句话就好了

CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 60)),2,0),':',LPAD(FLOOR(0 + (RAND() * 60)),2,0))

随机生成DATETIME类型的代码:

随机生成DATETIME类型的代码

可复制版本如下:

CREATE DEFINER =`您的数据库名`@`您的主机地址` FUNCTION `randDateTime`()
  RETURNS VARCHAR(255)
  CHARSET utf8
  BEGIN
    DECLARE aDateTime CHAR(19) DEFAULT '';
    SET aDateTime = CONCAT(CONCAT(1949 + FLOOR((RAND() * 68)), '-', LPAD(FLOOR(1 + (RAND() * 12)), 2, 0), '-',
                                  LPAD(FLOOR(3 + (RAND() * 8)), 2, 0)),
                           ' ',
                           CONCAT(LPAD(FLOOR(0 + (RAND() * 23)), 2, 0), ':', LPAD(FLOOR(0 + (RAND() * 60)), 2, 0), ':',
                                  LPAD(FLOOR(0 + (RAND() * 60)), 2, 0))
    );
    RETURN aDateTime;
  END;

执行效果如下:

select randDateTime();

执行效果

如果是时间戳类型,TIMESTAMP,那就是存一个特定范围的随机数我不再多做演示


2.3. gender性别的存法

用我们最上面写的那个随机字符串的方法进行改造,把chars_str的default内容改成“男,女等等”就好了,为避免文章冗长,不再做演示


3. 将数据循环插入

我们的整个最主要的方法就是基于MySQL的存储过程

我向来不爱先讲定义,我给大家以实例导入

定义将数据循环的存储过程:

定义将数据循环的存储过程

上图是一个10W次的插入实例,亲测耗时非常久,如果需要在30s内结束的,建议改成1w条,也够测试了

这里需要注意的是DELIMITER//和DELIMITER;两句, DELIMITER是分割符的意思,因为MySQL默认以”;”为分隔 符,如果我们没有声明分割符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事先用DELIMITER关键字申明当 前段分隔符,这样MySQL才会将”;”当做存储过程中的代码,不会执行这些代码,用完了之后要把分隔符还原。

也就是说如果你在命令行里面执行创建存储过程的语句时,是会出错的,因为sql遇到分号就结束了

我这里采用Intellij IDEA内置的DataGrip Console里面执行的,不用定义DELIMITER,在END后面加上;就好了,也推荐大家使用IDE来操作数据库,通过命令行是很痛苦的

可复制代码如下:

DELIMITER //
CREATE PROCEDURE insertManyUser()
  BEGIN
    DECLARE num INT;
    SET num = 1;
    WHILE num < 1000000 DO
      INSERT INTO users (username, email, password, birthday, gender, avatar)
      VALUES (rand_string(15), concat(rand_string(5), '@qq.com'), rand_string(32),
              randDate(), 'male', NULL);
      SET num = num + 1;
    END WHILE;
  END;
//

定义好存储过程insertManyUser之后还没有完,我们需要CALL它才可以

CALL insertManyUser;

然后等着慢慢的创建过程,测试用数据就到手了

完全版的存储过程详解: https://www.jianshu.com/p/7b2d74701ccd 可当手册查


最终生成的测试用数据


4. 测试数据的处理

4.1 清空数据库

  • 不可回滚式:

      DELETE FROM users;
  • 可回滚式:

      TRUNCATE TABLE users;

有外键存在的话,TRUNCATE不可用

4.2 MySQL修改存储过程

ALTER PROCEDURE
更改用CREATE PROCEDURE 建立的预先指定的存储过程,其不会影响相关存储过程或存储功能。

4.3 MySQL存储过程的删除

删除一个存储过程比较简单,和删除表一样:

DROP PROCEDURE

从MySQL的表格中删除一个或多个存储过程。

4.4 如果您需要改变FUNCTION返回值类型

可查看这篇文章: https://www.jianshu.com/p/7b2d74701ccd


您在生成测试数据的时候遇到什么问题,都可以在下面留言和我交流,共同成长。


   转载规则


《MySQL如何按表创建千万级的测试数据,进行数据库压力测试和练习》 Harbor Zeng 采用 知识共享署名 4.0 国际许可协议 进行许可。
 上一篇
Windows下常用的网络配置命令浅析 Windows下常用的网络配置命令浅析
原文章发表在 简书:https://www.jianshu.com/p/23e52ad9c44c 现在有了自己的静态博客,特意搬过来 一、学习目的 学会使用netsh命令测试本机的TCP/IP网络配置 学会使用netstat命令以及检测网络连接 了解arp原理以及使用arp探索攻击源头 了解nslookup命令的用法 二、学习要求使用netsh命令查看本机的IP地址,并修改本机的静态或者动态
2017-11-09
下一篇 
2分钟教你做安卓单纯的二维码识别,不涉及实时取景扫描 2分钟教你做安卓单纯的二维码识别,不涉及实时取景扫描
原文章发表在 https://www.jianshu.com/p/fd26a83a9336 现在有了自己的静态博客,特意搬过来 本文的目的只有一个,在两分钟之内教你做二维码识别,不涉及任何相机方面的实时识别,或是长按识别。仅仅只是从图库选一张图片,然后做识别。 1. 最终效果如下: 本文用到了Github上面一个开源库,QR Code decoding library written i
2017-06-13
  目录