MySQL百万级数据插入效率优化
场景
由于压力测试,您需要在数据库中检索大量数据,但数据库中没有太多数据。于是为了测试,就得往数据库里快速插入大量的临时数据。
有两种方法可以快速插入大量数据:一种是使用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
mysql mysql-connector-java 5.1.47 runtime 复制代码
第二个问题: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
2023年显示器推荐选购指南!IPSVATN面板选哪个?分辨怎么挑本内容来源于什么值得买APP,观点仅代表作者本人作者星杰e阅读正文之前,大家先问自己一个问题我买显示器主要用来做什么?日常办公绘画设计电竞游戏视频剪辑还是高清观影?有了答案才可以阅
DIY电脑如何选择鼠标线材?在日常使用鼠标的过程中,线材的材质形态软硬度等都影响鼠标的使用感受,它可以直接影响到鼠标的操作顺畅度和舒适度。常见的鼠标线材材质主要是橡胶线和编织线。所谓的橡胶线是由PVC材料包裹
ECC内存为什么比普通内存更可靠?一为什么需要ECC内存?因为硬盘的速度远远比不上CPU的速度,所以电脑在程序运行时CPU都会先把要执行的代码和各种数据从硬盘读取到内存(单片机这种小CPU除外),之后和内存交互数据
最高可得3个月!AppleMusic会员免费领取活动又来了,手慢无下午好呀铁铁们!最近的大热剧三体狂飙不知道大家有没有看呢?反正小雷看得上头好剧越来越多,小雷这段时间也光顾着满足大家追剧的需求,给大家分享了不少影视工具。突然发现,已经有一段时间没
OPPO陷主板门!6000元手机买一年多自动关机无法开机,用户呼吁优惠维修或召回山东商报速豹新闻网记者吴绍博日前,武汉的王先生向山东商报速豹新闻网和黑猫投诉联合反映其于2021年3月购入一款OPPOFindX3Pro系列手机,2022年12月31日突然自动关机
云顶金铲铲怪兽双C归来,有怪兽等级最大,可强玩稳吃分大家好,我是摩昂解说,每天都会给大家带来云顶与金铲铲的资讯和上分攻略。兄弟们记得点个关注哦!本期摩昂为大家带来的上分阵容是怪兽双C,大家在有怪兽等级最大的时候可选择这套阵容,吃分非
鹅鸭杀规则是什么?跟狼人杀有什么区别呢?大家会发现最近很多的游戏主播都结伴开始玩一款叫作鹅鸭杀的游戏。这款游戏和狼人杀的玩法差不多,但是规则却有所不同。不少玩家都很好奇鹅鸭杀规则是什么,也不知道该怎么样去玩好这款游戏。今
潮趣莱莎的炼金工房莱莎25周年纪念版手办初代元气少女AmiAmi推出莱莎的炼金工房常暗女王与秘密藏身处莱莎炼金工房系列25周年纪念版手办,小编了解官方于2024年2月发售,目前已经开启预约,手办将分为DX版和通常版。普通版套装内容一
原神须弥三神的爱恨情仇沐沐终于做完永恒绿洲的支线,在瞎逛的时候发现中间三把椅子有点小细节。人物在坐完椅子起身的时候头会偏向一边,好像是看向了另一个椅子,沐沐换了角色,也还是会有这个动作。然后在三个椅子之
原神黑粉对亚文化社区的破坏原神公测后,在商业上的成功促使了玩家社区的迅速扩张。在这个过程中反原群体会主动地去其他亚文化社区讨论原神,宣传原神,在与原神无关的地方刷原神梗。同时在新出的手游评分区里给出自选五星
孩子总是背不下来单词怎么办?说起学英语,家长问过最多的就是如何背单词了,许多家长都有这些困扰我家孩子每天都在背单词,花了很多时间记,记住的却很少看着孩子认真背单词,结果拼出来的单词总是错一两个字母。这些问题很