千万条数据快速插入mysql 只需要10分钟

最新推荐文章于 2023-08-16 16:00:01 发布

原来不是错觉

最新推荐文章于 2023-08-16 16:00:01 发布

阅读量6.6k

点赞数 3

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_26897321/article/details/120886520

版权

本文介绍了如何在MySQL中快速插入大量数据以进行性能测试。作者首先尝试使用存储过程，但发现效率低下，耗时3个多小时。后来通过生成SQL文件并用Java代码批量插入数据，将30万条数据的插入时间缩短到40秒。最终，通过Navicat导入千万数据耗时约11分钟，整体耗时11分钟15秒。测试显示，这样的速度是可以接受的，适合进行数据库性能优化研究。

摘要由CSDN通过智能技术生成

mysql 模拟生产环境快速插入大量的数据

今天准备测试一下shardingjdbc分表之后数据分页的查询效率，想要快速插入大量数据做测试，一开始用了存储过程，30万数据，花了3个多小时，简直吐血了。

表结构

drop table if EXISTS `t_oc_order_info`;
CREATE TABLE `t_oc_order_info` (
  `id` bigint(20) NOT NULL DEFAULT '0' COMMENT '订单编号',
  `order_no` varchar(100) NOT NULL DEFAULT '' COMMENT '订单号',
  `org_code` int(11) NOT NULL DEFAULT '0' COMMENT '订单所属组织编码',
  `org_name` varchar(100) NOT NULL DEFAULT '' COMMENT '组织名称',
	`patient` varchar(100) NOT NULL DEFAULT '' COMMENT '患者名称',
  `status` tinyint(3) NOT NULL DEFAULT '0' COMMENT '订单状态',
	`design_type` varchar(256) NOT NULL DEFAULT '' COMMENT '设计类型名称',
	`design_code` int(11) NOT NULL DEFAULT '0' COMMENT '设计类型',
	`design_status` tinyint(3) NOT NULL DEFAULT '0' COMMENT '设计状态',
  `print_status` int(11) NOT NULL DEFAULT '0' COMMENT '打印状态',
  `order_type` tinyint(3) NOT NULL DEFAULT '0' COMMENT '订单类型：1种植义齿，2固定修复，3活动修复，4正畸&其它，5个性化设置',
	`delivery_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '交期（时区）',
  `is_delete` tinyint(3) NOT NULL DEFAULT '0' COMMENT '是否删除：0-不删除，1-删除；',
  `created_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
	`sync_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '同步时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单信息';

存储过程(此存储结构插入耗费时间太多后面弃用了，慎用，仅供参考)

drop PROCEDURE `add_order_info`;
CREATE  PROCEDURE `add_order_info`(IN n int)
BEGIN  
  DECLARE i INT DEFAULT 1;
	DECLARE id BIGINT DEFAULT 1384347225387024387;
    WHILE (i <= n ) DO
--       INSERT into vote_record_memory  (user_id,vote_id,group_id,create_time ) VALUEs (rand_string(20),FLOOR(RAND() * 1000),FLOOR(RAND() * 100) ,now() );
-- 			SET @sqlStmt = CONCAT('INSERT INTO ','t_oc_order_info_',org,' VALUES (',id,'''');
-- 			SET @tableName = concat(`t_oc_order_info_`,org);
			INSERT INTO `t_oc_order_info_100001`VALUES (id, '202104201124', 100001, '广州*******有限公司', '', 2, '', 0, 0, 0, 1, 0, 0, 1634638028559, 1634638027800);
      set i=i+1;
			set id=id+1;
    END WHILE;
END

CALL add_order_info(300000);

后面我发现另外一个方法更加的快速(千万数据)

使用java代码将sql语句写入文件(千万数据花费了39786毫秒也就是40秒钟左右)

    public static void wirte(int size){
        String sql = "INSERT INTO `t_oc_order_info_100001`VALUES (%s, '202104201124', 100001, '广州*******有限公司', '', 2, '', 0, 0, 0, 1, 0, 0, 1634638028559, 1634638027800);";
//        System.out.println(String.format(sql,IdWorker.getId()));
        String path="E:\\heygears\\document\\shardingjdbc技术预研\\test.sql";
        File file=new File(path);
        if(!file.exists()){
            try {
                file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        try {
            //符合Java一种设计模式:装饰者设计模式(过滤器:Filter)
            BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(path)) ;
            for (int i = 0; i < size; i++) {
                //写数据
                bos.write(String.format(sql,IdWorker.getId()).getBytes());
                if(i<size-1){
                    bos.write("\n".getBytes());
                }
            }
            //释放资源
            bos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

生成的sql语句使用navicat导入数据库(此步骤有点久，)

花费时间 10分钟31.55
查询(select count(*) 居然要7秒多)

最后：总共花费时间大概在11分钟15秒左右，这个时间还是可以接受的，小伙伴们，可以去挑战千万数据后mysql的优化了。有问题可以在下方评论和讨论，希望大家多指教