您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL百万级数据插入效率优化

时间:2022-08-25 10:29:28  来源:  作者:陈三岁98

场景

由于压力测试,您需要在数据库中检索大量数据,但数据库中没有太多数据。于是为了测试,就得往数据库里快速插入大量的临时数据

有两种方法可以快速插入大量数据:

  • 一种是使用JAVA代码实现;
  • 另一种是使用数据库存储过程

优化方向

直接使用foreach的方式,一条一条的直接插入数据到MySQL中,效率十分低下。大概10w数据量需要18秒左右,100w数据大概需要10多分钟甚至直接卡死了。因此,我们可以对数据插入过程进行优化,分为下面两个方面:

  • 数据提交方面:批量提交
  • 数据库引擎方面:MyisAM

因为批量提交是分批次提交数据,因此一次创建少量的数据再分批次提交到数据库,这样既保证了数据传递的效率又不会一次占满内存

另外因为InnoDB的锁级别为行锁并且是事务性的,而MyisAM为表锁且无事务,因此MyisAM引擎对于频繁数据更新和插入的效率远大于InnoDB引擎。

下面我们来进行代码实践:

快速实践

1. 创建数据表

首先,你必须有一个数据表,注意数据表的引擎,在构建表时使用MyISAM引擎,MyISAM插入比InnoDB快得多,因为InnoDB的事务支持要好得多,并且在大多数情况下是default使用InnoDB,因此您可以在插入数据后将引擎从修改的MyISAM更换回为InnoDB

CREATE TABLE `tb_data` (
  `id` int(11) DEFAULT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `random` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
复制代码

2. 编写数据插入类

创建100w数据插入到MySQL的测试代码:

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
​
public class InsertDataDemo {
    static Connection conn = null;
​
    public static void initConn() throws ClassNotFoundException, SQLException {
​
        String url = "jdbc:mysql://localhost:3306/testdb?"
                + "user=root&password=root&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
​
        try {
            // 动态加载mysql驱动
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("成功加载MySQL驱动程序");
            conn = DriverManager.getConnection(url);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
​
​
    public static String randomStr(int size) {
        //定义一个空字符串
        String result = "";
        for (int i = 0; i < size; ++i) {
            //生成一个97~122之间的int类型整数
            int intVal = (int) (Math.random() * 26 + 97);
            //强制转换(char)intVal 将对应的数值转换为对应的字符,并将字符进行拼接
            result = result + (char) intVal;
        }
        //输出字符串
        return result;
    }
​
​
    public static void insert(int insertNum) {
        // 开时时间
        Long begin = System.currentTimeMillis();
        System.out.println("开始插入数据...");
        // sql前缀
        String prefix = "INSERT INTO tb_data (id, user_name, create_time, random) VALUES ";
​
        try {
            // 保存sql后缀
            StringBuffer suffix = new StringBuffer();
            // 设置事务为非自动提交
            conn.setAutoCommit(false);
            //为继承了Statement对象所有功能的预编译对象,性能和防SQL注入优于Statement对象,常用于重复执行的批处理命令
            PreparedStatement pst = conn.prepareStatement("");
            for (int i = 1; i <= insertNum; i++) {
                // 构建sql后缀(并一次生成8条数据)
                suffix.Append("(" + i +",'"+ randomStr(8)  + "', SYSDATE(), " + i * Math.random() + "),");
            }
            // 构建完整sql
            String sql = prefix + suffix.substring(0, suffix.length() - 1);
            // 添加执行sql
            pst.addBatch(sql);
            // 执行操作(批处理)
            pst.executeBatch();
            // 提交事务
            conn.commit();
      
            // 关闭连接
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 结束时间
        Long end = System.currentTimeMillis();
        System.out.println("插入"+insertNum+"条数据数据完成!");
        System.out.println("耗时 : " + (end - begin) / 1000 + " 秒");
    }
​
​
    public static void mAIn(String[] args) throws SQLException, ClassNotFoundException {
​
        initConn();
        insert(1000000);
​
    }
}
复制代码

3. 测试数据插入

注意,这里有两个坑:1. MySQL连接器版本;2. MySQL最大内存值限制

第一个问题:MySQL连接器版本过高或过低,需要固定依赖版本

执行后,会出现下面的错误:

 

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Successfully loaded MySQL driver
Start Inserting Data...
java.sql.SQLException: SQL String cannot be empty
复制代码

解决方式就是:将下面mysql连接器依赖版本替换为5.1.47

 

 <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
            <scope>runtime</scope>
        </dependency>
复制代码

第二个问题:MySQL最大限制内存过小

修改MySQL驱动版本为5.1+版本后,发现控制台报OOM异常:

 

java.sql.BatchUpdateException: Packet for query is too large (50235460 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
复制代码

仔细分析下,其实批处理插入数据的方式也是基于内存的,在批量提交的时候也会有一定内存的占用率。因此,应该是超过了MySQL最大内存限制导致的。

查看my.ini配置文件,发现MySQL数据库引擎内存最大值为1M(5.7版本默认是1M),得到验证。

查看内存大小:

mysql> show VARIABLES like '%max_allowed_packet%';
复制代码

 

控制台修改内存大小(也可直接修改my.ini配置文件)

mysql> mysql --max_allowed_packet=500M
或
set global max_allowed_packet = 4*1024*1024*10
复制代码

保存,重启MySQL服务

 


 

最后,控制台输出:

Successfully loaded MySQL driver
Start Inserting Data...
insert1000000 data data is completed!
Time-consuming : 7seconds
​
Process finished with exit code 0
复制代码

数据库显示

 

到这里已经实现了快速插入100w数据到MySQL数据库,测试成功!

注意事项

适当增加mysql的max_allowed_packet参数值允许系统在客户端到服务器端传递大数据时分配更多扩展内存以进行处理。 修改mysql配置文件(不能直接通过命令行进行修改):

[mysqld]
# 没有不需要添加
net_buffer_length=512k
​
max_allowed_packet=500M
复制代码

-- 更改引擎的语句 ALTER TABLE 表名 ENGINE=MyISAM;

-- 更改引擎的语句 ALTER TABLE 表明 ENGINE=InnoDB

总结

相比较于for循环直接插入而言,使用批处理提交的方式进行百万级别的数据插入,效率的确得到了极大地提升!

如果数据量再提升一个或几个量级,那么就需要考虑多线程和批量提交相结合的方式了,并且可以使用异步批处理的方式进行进一步优化,这里就不进行深入探究了。

欢迎点赞关注评论,感谢观看ヾ(◍°∇°◍)ノ゙


链接:
https://juejin.cn/post/7135101795646308365



Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  Search: MySQL  点击:(7)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  Search: MySQL  点击:(11)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  Search: MySQL  点击:(10)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  Search: MySQL  点击:(25)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10  Search: MySQL  点击:(9)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  Search: MySQL  点击:(28)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  Search: MySQL  点击:(49)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  Search: MySQL  点击:(53)  评论:(0)  加入收藏
为什么高性能场景选用Postgres SQL 而不是 MySQL
一、 数据库简介 TLDR;1.1 MySQL MySQL声称自己是最流行的开源数据库,它属于最流行的RDBMS (Relational Database Management System,关系数据库管理系统)应用软件之一。LAMP...【详细内容】
2024-02-19  Search: MySQL  点击:(38)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  Search: MySQL  点击:(54)  评论:(0)  加入收藏
▌简易百科推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  爱可生开源社区    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  爱可生开源社区  微信公众号  Tags:MySQL   点击:(11)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  MySQL学习  微信公众号  Tags:MySQL   点击:(10)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  字节跳动技术团队    Tags:ByteHouse   点击:(25)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10    dbaplus社群  Tags:MySQL   点击:(9)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  转转技术  微信公众号  Tags:MySQL   点击:(28)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  数据库干货铺  微信公众号  Tags:MySQL   点击:(49)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  编程技术汇    Tags:MySQL   点击:(53)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  数据库干货铺  微信公众号  Tags:MySQL   点击:(54)  评论:(0)  加入收藏
mysql索引失效的场景
MySQL中索引失效是指数据库查询时无法有效利用索引,这可能导致查询性能显著下降。以下是一些常见的MySQL索引失效的场景:1.使用非前导列进行查询: 假设有一个复合索引 (A, B)。...【详细内容】
2024-01-15  小王爱编程  今日头条  Tags:mysql索引   点击:(85)  评论:(0)  加入收藏
站内最新
站内热门
站内头条