记一次 mysql插入千万级别数据过程

最新推荐文章于 2024-07-01 11:42:01 发布
最新推荐文章于 2024-07-01 11:42:01 发布
阅读量825 收藏
点赞数
分类专栏： Java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq1010234991/article/details/100022269
版权

 

mysql插入千万级别数据

背景

项目中期，需要进行性能测试，大数据平台要测试千万级别的数据进行处理，测试性能问题，所以需要对MySQL中插入千万级别的数据

第一次插入数据

最直接的想法，就是通过jdbc方式，将数据插入到MySQL中，因为原来造测试数据，进行自测时，也是同样的做法


    private static final Logger LOG = LoggerFactory.getLogger(CreateLeagalPersonTest.class);
    private static Random rdn = new Random();
    private static Connection conn = null;
    private static int correctDataCount = 20000000;
    private static int errorDataCount = 10000000;


    @Before
    public void before() throws ClassNotFoundException, SQLException {
        //1、连接数据库
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://10.117.5.223:3306/bigdata";
        String user = "root";
        String password = "123456";
        conn = DriverManager.getConnection(url, user, password);
        if (conn == null) {
            LOG.error("连接失败...");
            System.exit(1);
        }
    }

    //主入口
    @Test
    public void test() throws SQLException {
        //2、编写带？的SQL
        String sql = "INSERT INTO tb_test_leagal_person (ORG_CODE, ORG_NAME ,ORG_ADDRESS ,ORG_TYPE_CODE, REG_NO ," +
                "REG_DATE ,STATUS ,STATUS_NAME ,ECNOMIC_CODE, ECNOMIC_CODE_NAME ,INDUSTRY_CODE ,INDUSTRY_CODE_NAME ," +
                "REG_CAPITAL ,UNI_SCID ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        PreparedStatement pst = conn.prepareStatement(sql);// 对带？的sql进行预编译
        for (int index = 0; index < correctDataCount; index++) {

            // 4、把?用具体的值进行代替
            pst.setString(1, createORGCODE(index, true)); //组织机构代码
            pst.setString(2, createORGNAME(index, true)); //组织机构名称
            pst.setString(3, creteORGADDRESS(index, true)); // 组织地址
            pst.setString(4, createORGTYPECODE(index, true)); //机构类型
            pst.setString(5, createREGNO(index, true)); //注册号
            pst.setDate(6, creteDate(index, true)); //成立日期
            pst.setString(7, createSTATUS(index, true)); //状态
            pst.setString(8, createSTATUSNAME(index, true)); //状态名称
            pst.setString(9, createECNOMICCODE(index, true)); //经济类型
            pst.setString(10, createECNOMIC_CODE_NAME(index, true)); //经济类型名称
            pst.setString(11, createStringType("行业类别", index, true)); //行业类别
            pst.setString(12, createStringType("行业类别名称", index, true)); //行业类别名称
            pst.setInt(13, createIntType(index, true));//注册资金
            pst.setString(14, createStringType("统一信用代码", index, true)); //同一信用代码

            // 5、执行sql
            int len = pst.executeUpdate();
            System.out.println(len > 0 ? "添加成功 " + index : "添加失败");
        }
        // 6、释放资源
        pst.close();
        conn.close();
    }

效果

很直观的发现，效率比较慢，需要进行优化

第二种方式：通过批处理方式

上面的做法是通过一条一条的记录插入的，效率比较低，那就改为批处理的

 //批处理插入
    @Test
    public void testInsertBatchData()  {
        //2、编写带？的SQL
        String sql = "INSERT INTO tb_test_leagal_person (ORG_CODE, ORG_NAME ,ORG_ADDRESS ,ORG_TYPE_CODE, REG_NO ," +
                "REG_DATE ,STATUS ,STATUS_NAME ,ECNOMIC_CODE, ECNOMIC_CODE_NAME ,INDUSTRY_CODE ,INDUSTRY_CODE_NAME ," +
                "REG_CAPITAL ,UNI_SCID ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        PreparedStatement pst = null;// 对带？的sql进行预编译
        try {
            pst = conn.prepareStatement(sql);
            for (int index = 4450000; index < correctDataCount; index++) {

                // 4、把?用具体的值进行代替
                pst.setString(1, createORGCODE(index, true)); //组织机构代码
                pst.setString(2, createORGNAME(index, true)); //组织机构名称
                pst.setString(3, creteORGADDRESS(index, true)); // 组织地址
                pst.setString(4, createORGTYPECODE(index, true)); //机构类型
                pst.setString(5, createREGNO(index, true)); //注册号
                pst.setDate(6, creteDate(index, true)); //成立日期
                pst.setString(7, createSTATUS(index, true)); //状态
                pst.setString(8, createSTATUSNAME(index, true)); //状态名称
                pst.setString(9, createECNOMICCODE(index, true)); //经济类型
                pst.setString(10, createECNOMIC_CODE_NAME(index, true)); //经济类型名称
                pst.setString(11, createStringType("行业类别", index, true)); //行业类别
                pst.setString(12, createStringType("行业类别名称", index, true)); //行业类别名称
                pst.setInt(13, createIntType(index, true));//注册资金
                pst.setString(14, createStringType("统一信用代码", index, true)); //同一信用代码

                pst.addBatch();
                if (index % 5000 == 0) {
                    pst.executeBatch();
                    System.out.println(("插入成功..." + index));
                    pst.clearBatch();
                }
//            // 5、执行sql
//            int len = pst.addBatch();
//            System.out.println(len > 0 ? "添加成功 " + index : "添加失败");
            }
            pst.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            // 6、释放资源
            try {
                pst.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

第三种方式

同事说使用mysql 命令可以快速加载文件到mysql中

生成csv文件

//写入文件方式
    @Test
    public void testWrtieToCSV() {
        String outputPath = "dir/tb_test_leagal_person_2.csv";
        doCreateCVSData(0, correctDataCount, true, outputPath);

        doCreateCVSData(correctDataCount, correctDataCount + errorDataCount, false, outputPath);

    }

    private List<String> doCreateCVSData(int start, int end, boolean flag, String outputPath) {
        List<String> result = new ArrayList<>();
        for (int index = start; index < end; index++) {
            StringBuffer sbuffer = new StringBuffer();
            sbuffer.append(index);
            sbuffer.append(",");
            sbuffer.append(createORGCODE(index, flag)); //组织机构代码
            sbuffer.append(",");
            sbuffer.append(createORGNAME(index, flag)); //组织机构名称
            sbuffer.append(",");
            sbuffer.append(creteORGADDRESS(index, flag)); // 组织地址
            sbuffer.append(",");
            sbuffer.append(createORGTYPECODE(index, flag)); //机构类型
            sbuffer.append(",");
            sbuffer.append(createREGNO(index, flag)); //注册号
            sbuffer.append(",");
            sbuffer.append(creteDate(index, flag)); //成立日期
            sbuffer.append(",");
            sbuffer.append(createSTATUS(index, flag)); //状态
            sbuffer.append(",");
            sbuffer.append(createSTATUSNAME(index, flag)); //状态名称
            sbuffer.append(",");
            sbuffer.append(createECNOMICCODE(index, flag)); //经济类型
            sbuffer.append(",");
            sbuffer.append(createECNOMIC_CODE_NAME(index, flag)); //经济类型名称
            sbuffer.append(",");
            sbuffer.append(createStringType("行业类别", index, flag)); //行业类别
            sbuffer.append(",");
            sbuffer.append(createStringType("行业类别名称", index, flag)); //行业类别名称
            sbuffer.append(",");
            sbuffer.append(createIntType(index, flag));//注册资金
            sbuffer.append(",");
            sbuffer.append(createStringType("统一信用代码", index, flag)); //同一信用代码
            System.out.println(sbuffer.toString());
            result.add(sbuffer.toString());

            if (result.size() % 1000000 == 0) {
                for (String s : result) {
                    try {
                        writeLinesToFile1(outputPath, s);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                result.clear();
            }
        }
        return result;
    }

    public static boolean writeLinesToFile1(String fileName, String line) throws Exception {
        FileWriter fw = new FileWriter(fileName, true);
        fw.write(line + "\n");
        fw.flush();
        fw.close();
        return true;
    }

加载文件到mysql中

#mysql加载csv文件
LOAD DATA LOCAL INFILE 'C:\\Users\\wangyg\\Desktop\\tb_test_leagal_person_2.csv' INTO TABLE bigdata.tb_test_leagal_person_2 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

缺陷：

将数据写入csv文件时，同样会有大量的IO，导致所花费的时间很长，所以从某种角度来讲，时间还是一样长,不能解决问题

最终方式

最终。在网上查询资料，使用这种方式，2千万+数据 不到半小时就完成

  @Test
    public void testInsert() throws SQLException, ClassNotFoundException {
        initConn();
        insert(0, 2000000, true);
//        insert(correctDataCount, correctDataCount+errorDataCount, false);
    }

    private void initConn() throws ClassNotFoundException, SQLException {
        //1、连接数据库
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://10.117.5.223:3306/bigdata";
        String user = "root";
        String password = "123456";
        conn = DriverManager.getConnection(url, user, password);
        if (conn == null) {
            LOG.error("连接失败...");
            System.exit(1);
        }
    }

    private static int singleCount = 100000;

    //批量插入
    public static void insert(int startCount, int endCount, boolean flag) {
        // 开时时间
        Long begin = new Date().getTime();
        // sql前缀
        String prefix = " INSERT INTO tb_test_leagal_person_3 (ORG_CODE, ORG_NAME ,ORG_ADDRESS ,ORG_TYPE_CODE, REG_NO ," +
                "STATUS ,STATUS_NAME ,ECNOMIC_CODE, ECNOMIC_CODE_NAME ,INDUSTRY_CODE ,INDUSTRY_CODE_NAME ," +
                "REG_CAPITAL ,UNI_SCID ) VALUES";
        try {
            // 保存sql后缀
            StringBuffer suffix = new StringBuffer();
            // 设置事务为非自动提交
            conn.setAutoCommit(false);
            // Statement st = conn.createStatement();
            // 比起st，pst会更好些
            PreparedStatement pst = conn.prepareStatement("");
            // 外层循环，总提交事务次数
            for (int i = 0; i <= endCount / singleCount; i++) {
                // 第次提交步长
                for (int j = 1; j <= singleCount; j++) {
                    // 构建sql后缀
//                    suffix.append("(" + j * i + ", SYSDATE(), " + i * j
//                            * Math.random() + ",'" + randomStr(6) + "'),");
                    int index = i * singleCount + j;
                    //字符串类型添加'引号
                    suffix.append("(");

                    suffix.append("'");
                    suffix.append(createORGCODE(index, flag));
                    suffix.append("'");

                    suffix.append(",");
                    suffix.append("'");
                    suffix.append(createORGNAME(index, flag));
                    suffix.append("'");
                    suffix.append(",");
                    suffix.append("'");
                    suffix.append(creteORGADDRESS(index, flag));
                    suffix.append("'");
                    suffix.append(",");
                    suffix.append("'");
                    suffix.append(createORGTYPECODE(index, flag)); //机构类型
                    suffix.append("'");
                    suffix.append(",");
                    suffix.append("'");
                    suffix.append(createREGNO(index, flag)); //注册号
                    suffix.append("'");
//                    suffix.append(",");
//                    suffix.append(creteDate(index, flag)); //成立日期
                    suffix.append(",");
                    suffix.append("'");
                    suffix.append(createSTATUS(index, flag)); //状态
                    suffix.append("'");
                    suffix.append(",");
                    suffix.append("'");
                    suffix.append(createSTATUSNAME(index, flag)); //状态名称
                    suffix.append("'");
                    suffix.append(",");
                    suffix.append("'");
                    suffix.append(createECNOMICCODE(index, flag)); //经济类型
                    suffix.append("'");
                    suffix.append(",");
                    suffix.append("'");
                    suffix.append(createECNOMIC_CODE_NAME(index, flag)); //经济类型名称
                    suffix.append("'");
                    suffix.append(",");
                    suffix.append("'");
                    suffix.append(createStringType("行业类别", index, flag)); //行业类别
                    suffix.append("'");
                    suffix.append(",");
                    suffix.append("'");
                    suffix.append(createStringType("行业类别名称", index, flag)); //行业类别名称
                    suffix.append("'");
                    suffix.append(",");
                    suffix.append(createIntType(index, flag));//注册资金
                    suffix.append(",");
                    suffix.append("'");
                    suffix.append(createStringType("统一信用代码", index, flag)); //同一信用代码
                    suffix.append("'");
                    suffix.append("),");

                }
                // 构建完整sql
                String sql = prefix + suffix.substring(0, suffix.length() - 1);
                pst.addBatch(sql);

                // 添加执行sql
                // 执行操作
                pst.executeBatch();
                // 提交事务
                conn.commit();
                System.out.println("提交成功..." + i * singleCount);
                // 清空上一次添加的数据
                suffix = new StringBuffer();
            }
            // 头等连接
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 结束时间
        Long end = new Date().getTime();
        // 耗时
        System.out.println("cast : " + (end - begin) / 1000 + " ms");
    }

注意点

  1. 创建表时，指定MYISAM
CREATE TABLE bigdata.tb_test_leagal_person_2 (
    id INT AUTO_INCREMENT,
    ORG_CODE VARCHAR(9),
    ORG_NAME VARCHAR(140),
    ORG_ADDRESS VARCHAR(300),
    ORG_TYPE_CODE VARCHAR(100),
    REG_NO VARCHAR(100),
    `STATUS` VARCHAR(100),
    STATUS_NAME VARCHAR(100),
    ECNOMIC_CODE VARCHAR(100),
    ECNOMIC_CODE_NAME VARCHAR(100),
    INDUSTRY_CODE VARCHAR(100),
    INDUSTRY_CODE_NAME VARCHAR(100),
    REG_CAPITAL  INT(10),
    UNI_SCID VARCHAR(100),
    PRIMARY KEY (id)
) ENGINE MYISAM, CHARSET utf8,
AUTO_INCREMENT 0;
  1. SQL拼接多个value值
  2. 设置事务为非自动提交
  3. SET GLOBAL max_allowed_packet=100 *1024*1024*20;

参考链接

https://blog.csdn.net/u010180815/article/details/79166502

https://blog.csdn.net/hewei314599782/article/details/80774162

123.56.119.133:8090
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql插入2000万数据_Mysql千万级别数据批量插入，性能提高
weixin_39807859的博客
01-19 3346
-----------------------------------------------------------方式1 ----------------------------------------------------------------------------------------第一步：配置my.ini文件文件中配置bulk_insert_buffer_size=120M 或...
MySQL循环插入千万级数据
12-14
1、创建测试表 CREATE TABLE `mysql_genarate` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uuid` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5999001 DEFAULT CHARSET=utf8; 2、创建一个循环插入的存储过程 CREATE DEFINER=`root`@`localhost` PROCEDURE `test_two1`( ) BEGIN DECLARE i INT DEFAU
Mysql千万级别数据批量插入只需简单三步
热门推荐
u011277123的博客
03-13 8万+
IT自由职业者 2017-03-05 09:40 第一步：配置my.ini文件 文件中配置 bulk_insert_buffer_size=120M 或者更大 将insert语句的长度设为最大。 Max_allowed_packet=1M Net_buffer_length=8k 保存 第二步：查看设置的参选有没有生效. Mysql > SHOW VARIABLES;
MySQL如何快速插入千万级大数据
最新发布
My_wife_QBL的博客
07-01 1961
在现代数据驱动的应用中，数据库的性能至关重要。当需要向MySQL数据库中插入千万级甚至亿级的大数据时，传统的插入方法往往效率低下，无法满足实时性和吞吐量的要求。本文将详细介绍如何在MySQL中快速插入千万级大数据，包括优化策略、工具和技术，帮助读者提升数据插入性能。
千万级数据量的插入操作（MYSQL）
张音乐的博客
09-28 3240
前几天因为公司业务迁移需要，需要从数仓同步一张大表，数据总量大概三千多万，接近四千万的样子，当遇到这种数据量的时候，综合考虑之后，当前比较流行的框架都不能满足于生产需求，使用框架对性能的损耗过于严重，所以有了以下千万级数据量的插入方案。 当数据量达到一定规模的时候，假设一个语句为这样，还比较小的，只有三个字段。 INSERT INTO user_operation_min_temp(obse...
mysql千万数据写入 300s
tcy-阿春
07-21 464
1.参数配置 2.分批插入，启用事物 3.分区 创建表 drop table if exists girl; create table girl( id bigint default null, name varchar(20) default null, date date default null) engine=InnoDB    partition by range (year(...
MySQL插入千万级记录
西木风落
05-01 5286
场景： 当产品中需要对用户进行过滤的时候，需要对用户添加白名单。如果一次需要导入千万级的用户量，普通的SQL插入/更新耗时长久，执行效率低下。为加快数据库的插入效率，需要对程序和数据库设置进行优化。
记一次 MySQL 并发时插入重复数据的解决方案
12-14
本篇文章探讨了一次在MySQL并发操作中遇到的插入重复数据问题及其解决方案。 问题起源于一个名为`coolq_qq_group_message_receiver`的表，用于存储QQ群消息接收者的信息，其中`qq_group_number`字段具有唯一性约束...
mysql 10w级别的mysql数据插入
09-11
特别是在“mysql 10w级别的mysql数据插入”这种场景下，一次性插入数十万条记录，如果处理不当，将会耗费大量的时间和系统资源。从描述中可以看到，一开始采用单条插入的方式，效率极低，半小时只能插入2w条数据，而...
Python3 操作 MySQL 插入一条数据并返回主键 id的实例
09-08
在Python3中操作MySQL数据库并插入一条数据，然后获取并返回这条数据的主键id是数据库编程中常见的需求。这一过程可以分为几个关键步骤：数据库连接、执行插入操作、获取主键id以及事务提交。下面详细说明每个步骤...
教你如何6秒钟往MySQL插入100万条数据的实现
09-08
2. 分批次插入，如果数据量过大，可以考虑分批执行，每次插入一部分数据，避免一次性插入大量数据导致的内存压力。 3. 调整数据库的事务隔离级别和缓冲池大小，以适应高并发的插入操作。 4. 对于索引的选择和设计也...
往mysql循环插入几千万条数据
05-13
做测试时，到mysql数据库循环插入千万条数据的java代码
使用sql语句往MySQL插入1000万条数据
weixin_43324314的博客
04-08 6917
在学习或者工作生产环境中，我们经常要对数据库进行压力测试，往数据库中批量插入大量数据，这里我演示直接往Mysql数据库中批量插入大量数据，采用存储过程的方法实现。 数据库版本：Mysql5.7 一、建表 1、创建数据库（student） CREATE DATABASE STUDENT; 2、使用数据库 USE STUDENT; 3、创建学生表（students） CREATE TABLE stu...
一次性往mysql中插入10万条数据，只需7秒
人生如戏
06-11 3万+
不解释，直接上代码$connect_mysql-&gt;autocommit(false); mysqli_query($connect_mysql,'set names utf8'); $begin = time(); $sqls =''; for($i=1;$i&lt;=100000;$i++){ $sqls .= "('......
mySQL写入数据
倒斗拉比
04-11 461
标题mySQL写入数据标题mySQL数据写入
批量往mysql里面插入1000万条数据_JDBC实现往MySQL插入百万级数据
weixin_39611546的博客
12-20 998
想往某个表中插入几百万条数据做下测试，原先的想法，直接写个循环10W次随便插入点数据试试吧，好吧，我真的很天真....DROP PROCEDURE IF EXISTS proc_initData;--如果存在此存储过程则删掉DELIMITER $CREATE PROCEDUREproc_initData()BEGINDECLARE i INT DEFAULT 1;WHILE i<=10000...
MySql 快速插入千万级大数据
oldbai001的专栏
06-17 2万+
在数据分析领域，数据库是我们的好帮手。不仅可以接受我们的查询时间，还可以在这基础上做进一步分析。所以，我们必然要在数据库插入数据。在实际应用中，我们经常遇到千万级，甚至更大的数据量。如果没有一个快速的插入方法，则会事倍功半，花费大量的时间。 在参加阿里的天池大数据算法竞赛中（流行音乐趋势预测），我遇到了这样的问题，在没有优化数据库查询及插入之前，我花了不少冤枉时间，没有优化之前，1500万条数据
MySQL千万级数据JDBC插入
aoju8535的博客
12-16 155
案例语句： String sql = "LOAD DATA LOCAL INFILE '" + dataFilepath + "' into table " + tableName + " COLUMNS TERMINATED BY '" + INFILE_COLUMN_SEPARATION_CHAR + "' "; jdbcTemplate.execute(sql);...
Mysql5.7, 千万数据快速插入解决方案（ JDBC方式， 百秒搞定！！！）
张伯毅的专栏
02-20 1万+
    最近在弄一件任务，要求测试一下从文本中读取数据，然后向mysql表中插入。要求用JDBC线程导入。要求效率。   环境说明：                       数据量 ： 10058624条  （大约一千零6万条数据，本地机器运行）             数据大小 ：  1093.56MB  （1.1G）             MYSQL版本  : 5.7  ...
mysql中添加5万条记录,Mysql千万级别数据批量插入只需简单三步！
06-06
1. 构建插入语句：首先，你需要构建一个插入语句，用于将数据插入到MySQL中。例如，如果你要将数据插入到名为“mytable”的表中，可以使用以下语句： ``` INSERT INTO mytable (column1, column2, column3, ...) ...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值