MySQL快速插入百万行测试数据

客官°小女子只卖身不卖艺 2022-12-16 03:56 240阅读 0赞

> 遇到需要大量数据进行业务测试的场景，产生数据有很多种方式，下面介绍一种方式。

**环境：**

*  windows10 64bit
 *  i5 4210M 2.6GHz
 *  8G 1600MHz \*2
 *  SSD
 *  MYSQL 5.0

**整体思路:**

*  1、如何产生大量数据？  
    可以利用循环，重复制造大量数据
 *  2、如何快速将数据存储（插入）数据表？  
    直接使用循环也可以做到，但是速度可能会相对慢一点。可以利用MYSQL MEMORY引擎，加速数据的插入。

**其中一种实现方式，详细过程:**

-- 1、创建内存表
    -- 使用MEMORY数据库引擎，创建内存表。
    --将数据直接插入内存表，再将数据从内存表复制到普通表，效率优于直接插入普通表
    CREATE TABLE `table_in_memory` (
      `id` INT(6) NOT NULL AUTO_INCREMENT COMMENT '自增id',
      `name` VARCHAR(50) NOT NULL COMMENT '名称',
      `pwd` VARCHAR(32) NOT NULL COMMENT '密码',
      `create_time` DATETIME DEFAULT NULL COMMENT '创建日期',
      PRIMARY KEY (`id`)
    ) ENGINE=MEMORY AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 COMMENT='内存临时表';

-- 2、创建使用MYISAM引擎的普通表，存放最终数据的表
    -- 针对大量测试数据的这个场景，可以不考虑事务，因此可以使用MYISAM引擎
    CREATE TABLE `test_data` (
      `id` INT(6) NOT NULL AUTO_INCREMENT COMMENT '自增id',
      `name` VARCHAR(50) NOT NULL COMMENT '名称',
      `create_time` DATETIME DEFAULT NULL COMMENT '创建日期',
      PRIMARY KEY (`id`)
    ) ENGINE=MYISAM AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 COMMENT='测试数据表';

-- 3、创建一个函数，用于生成随机数据
    -- 尽管是测试数据，但是100W行一模一样的数据也就没有什么测试业务的价值。在创建测试数据的时候，使用一些算法，
    --将数据尽量模拟真实一点。
    DROP FUNCTION IF EXISTS rand_string;
    DELIMITER $$
    CREATE FUNCTION rand_string(n INT)
    RETURNS VARCHAR(255)
    BEGIN
        DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
        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 $$
    DELIMITER ;

-- 4、创建存储过程，批量调用函数，生成大量随机数，并插入临时表（内存表）
    DROP PROCEDURE IF EXISTS `add_data`;
    DELIMITER $$
    CREATE PROCEDURE `add_data`(IN n INT)
    BEGIN
        DECLARE i INT UNSIGNED DEFAULT 0;
        WHILE i < n DO
            INSERT INTO `table_in_memory`(NAME,pwd,create_time) 
            VALUES (rand_string(15),MD5(123456),NOW());
            SET i = i+1;
        END WHILE;
    END $$
    DELIMITER ;

-- 5、调用存储过程,执行100W次的数据生成和插入。
    --这里的时间依赖上面步骤中生成随机数的算法的效率，如果生成随机数的算法比较复杂，在100W次的执行中，
    --也会比较耗时。另外和机器性能也有关。本机使用以上相对简单的随机数算法，执行了3次，平均1min 10sec左右
    --完成100W行数据生成并插入内存表 
    CALL add_data(1000000);

-- 6、调用存储过程时，如果出现TABLE IS FULL ……错误提示，说明 mysql默认的max_heap_table_size 太小，需要扩容
    -- 找到my.ini 文件，将max_heap_table_size 修改，或者此参数不存在时，直接添加 max_heap_table_size = 1024M
    -- 使用以下命令查看数据库的默认参数大小
    show variables like '%table_size'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MjA2MjQ0_size_16_color_FFFFFF_t_70_pic_center]  
在MYSQL安装路径下，找到my.ini配置文件，修改max\_heap\_table\_size 参数的大小  
![在这里插入图片描述][20201019203605925.png_pic_center]  
如果不存在这个参数，直接添加  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MjA2MjQ0_size_16_color_FFFFFF_t_70_pic_center 1]

max_heap_table_size = 1024M

重启数据库实例

-- 7、重新调用存储过程
    CALL add_data(1000000);

![在这里插入图片描述][2020101920430196.png_pic_center]

--8、将临时表中的数据复制到目标表中,约耗时2sec
    insert into test_data select * from table_in_memory

![在这里插入图片描述][20201019203937613.png_pic_center]  
**注意事项:**

*  执行效率受多方面因素影响，如机器性能，生成随机数算法的复杂度、效率，插入的数据行数，使用的数据库引擎。大家可以尝试使用innodb引擎，测试下速度。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MjA2MjQ0_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/64bb17534f77473e9a1b0205e0b64ef0.png
[20201019203605925.png_pic_center]: /images/20221123/e12b1c1e2f52410d90c74f54f8f10d12.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MjA2MjQ0_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/9a561a36d88b494c9932e7875db683fb.png
[2020101920430196.png_pic_center]: /images/20221123/f3aba4ae953a4292af949b8efd24ddaf.png
[20201019203937613.png_pic_center]: /images/20221123/76a703b08d9a481bb2bfd1d30a2c3f0d.png