MySQL中快速创建大量测试数据

在学习MySQL索引和分库分表等知识的过程中，经常会涉及到创建大批量的测试数据，本文简要说明自己常用的几种创建方式以及各自的优劣对比。

实现方式

以下述的system_user表为例分别说明在不同的方式下如何大批量的创建测试数据。

CREATE TABLE `system_user` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	`age` INT DEFAULT NULL,
	`tag` VARCHAR(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8;

通过代码程序创建

工作中使用的编程语言主要是Java，在之前我不熟悉MySQL存储过程用法的时，主要采用JDBC的方式实现批量创建数据，代码类似如下:

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.commons.lang3.RandomUtils;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

@Slf4j
@SpringBootTest
public class TestBatchInsertData {

    @Resource
    private DataSource dataSource;

    private int DATA_SIZE = 1000_0000;
    private int BATCH_SIZE = 100;

    @Test
    public void testAddBatchData() {
        String insertSQL = "insert into system_user(name,age,tag) VALUES(?,?,?)";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
            conn.setAutoCommit(false);
            for (int i = 1; i <= DATA_SIZE; i++) {
                pstmt.setString(1, RandomStringUtils.randomAlphanumeric(8));
                pstmt.setInt(2, RandomUtils.nextInt(18, 80));
                pstmt.setString(3, RandomStringUtils.randomAlphabetic(8));
                pstmt.addBatch();
                if (i % BATCH_SIZE == 0) {
                    pstmt.executeBatch();
                    conn.commit();
                    log.info("执行一次批量提交:\t" + i / BATCH_SIZE);
                }
            }
            pstmt.executeBatch();
            conn.commit();
            log.info("完成数据批量插入");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

从上述代码可知，此种实现方式较为简洁，实际的业务代码只有20行左右，对于具有Java开发经验的人来说上手很快，不足之处是需要额外准备相应的执行环境。

通过存储过程创建

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `add_user_batch`$$

CREATE DEFINER=`root`@`%` PROCEDURE `add_user_batch`(IN COUNT INT)
BEGIN
    DECLARE i INT;
    DECLARE t_name VARCHAR(8);
    DECLARE t_tag VARCHAR(20);
    DECLARE t_age INT(2);
    DECLARE t_sql_template VARCHAR(100);
    DECLARE t_sql TEXT;   
    DECLARE t_tag_mod_val INT DEFAULT(25);
    DECLARE t_commit_mod_val INT DEFAULT(100);
    
    DECLARE t_start_time DATETIME;
    DECLARE t_end_time DATETIME;    
    
    TRUNCATE TABLE `system_user`;
    
    SET t_start_time=NOW();
    SET t_sql_template = "INSERT INTO `system_user`(NAME, age, tag) VALUES";
    SET t_sql = t_sql_template;
    SET i = 1;
    WHILE i <= COUNT
        DO
            SET t_age = FLOOR(1 + RAND() * 60);
            SET t_name = LEFT(UUID(), 8);
            -- 给tag随机制造空值
            IF MOD(i, t_tag_mod_val) = 0 THEN
                SET t_tag = "NULL";
            ELSE
                SET t_tag = CONCAT("'",LEFT(UUID(), 8),"'");
            END IF;
 
            SET t_sql = CONCAT(t_sql,"('",t_name,"',",t_age,",",t_tag,")");
            
            IF MOD(i,t_commit_mod_val) != 0 THEN
              SET t_sql = CONCAT(t_sql,",");
            ELSE
              SET t_sql = CONCAT(t_sql,";");
                   -- 只要达到t_commit_mod_val要求的次数，就执行并提交
                   SET @insert_sql = t_sql;
                   PREPARE stmt FROM @insert_sql;
                   EXECUTE stmt;
                   DEALLOCATE PREPARE stmt;
                   COMMIT;
              SET t_sql=t_sql_template;
            END IF;
            SET i = i + 1;
        END WHILE;
        
        -- 不能被t_commit_mod_val整除时，余下的数据处理
        IF LENGTH(t_sql) > LENGTH(t_sql_template) THEN
                   SET t_sql=CONCAT(SUBSTRING(t_sql,1,LENGTH(t_sql)-1),';');
                   SET @insert_sql = t_sql;
                   PREPARE stmt FROM @insert_sql;
                   EXECUTE stmt;
                   DEALLOCATE PREPARE stmt;
                   COMMIT;
        END IF;
        SET t_end_time=NOW();
        SELECT CONCAT('insert data success,time cost ',TIMEDIFF(t_end_time,t_start_time)) AS finishedTag;
END$$

DELIMITER ;

调用过程类似如下:

-- 清空原有记录
TRUNCATE TABLE `system_user`;

-- 调用存储过程
CALL add_user_batch(1000);

-- 验证查询结果
SELECT COUNT(*) FROM `system_user`;

可以看出虽然用存储过程也能实现批量提交，但相对于Java实现而言，其代码量更大更为复杂，上手门槛略高。不过其好处也很明显，只要有数据库环境就能执行¹。

通过SQL语句创建

此种方式需要通过SELECT INSERT INTO来实现，具体步骤如下：

1. 通过如下的SQL初始化表中的数据：

   INSERT INTO `system_user`(NAME,age,tag) VALUES
   (LEFT(UUID(), 8),FLOOR(1 + RAND() * 60),LEFT(UUID(), 8));
   

2. 根据实际需要多次执行下述SQL：

   INSERT INTO `system_user`(NAME,age,tag) 
   SELECT LEFT(UUID(), 8),FLOOR(1 + RAND() * 60),LEFT(UUID(), 8) FROM `system_user`;
   

这种方式主要是利用了SELECT每次查询之前表中的全部数据，然后重新插入，每次SELECT时都会将表中已有的数据全部查询出来，获取出总的记录数，然后根据SELECT后面的条件重新对每一条插入的记录重新赋值插入，实际上相当于翻倍插入。

由于每次执行SELECT INTO时都是将之前的数据量扩大1倍，故往数据库中插入的总数count与执行次数n的关系如下:

count = $2^n$

更具体的信息如下：

在MySQL中tmp_table_size为16M，innodb_buffer_pool_size的默认值是128M，当执行到一定次数后，会出现类似The total number of locks exceeds the lock table size的错误，此时需要根据实际情况调整这两个参数，参考如下：

SET GLOBAL tmp_table_size =512*1024*1024; -- 512M
SET global innodb_buffer_pool_size= 2*1024*1024*1024; -- 2G

此种方式虽然需要多次执行SQL语句，但其优点也很明显，只需要将SQL语句稍作修改，就能适用于不同的数据库表。

对比&总结

各种方式的对比如下：

各自执行1000万条数据的粗略耗时对比如下：²

• 代码程序创建，6小时
• 存储过程创建，11分钟
• SQL语句创建，5分钟

从上述结果可以看出通过SQL语句创建耗时相对较少³，若只是单纯的插入数据，建议优先选择此种方式。