mysql 大量数据插入优化

这篇具有很好参考价值的文章主要介绍了mysql 大量数据插入优化。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

引言

对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长,当有大量数据需要插入数据库时(比如10万,50万,100万条数据),如果继续使用单条语句进行插入的话,会很影响数据库效率,因此,优化数据库插入性能是很有意义的,那么,怎么来优化呢?

1. 一条SQL语句插入多条数据

常用的插入语句:

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)      
VALUES ('0', 'userid_0', 'content_0', 0);

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
 VALUES ('1', 'userid_1', 'content_1', 1);

上述修改后的插入语句:

INSERT INTO `insert_table` (`datetime`,`uid`,`content`,`type`)
VALUES ('0','userid_0','content_0',0),('1','userid_1','content_1',1);

修改后的插入操作能够提高程序的插入效率,这里修改后的插入操作执行效率高的主要原因是:合并后日志量(MySQL的binlog和innodb的事务让日志)减少了降低日志刷盘的数据量和频率,从而提高效率,通过合并SQL语句,同时也能减少SQL语句解析的次数减少网络传输的I/O开销

这里提供一些测试对比数据,分别是进行单条数据的导入与转化成一条SQL语句进行导入,分别测试1百、1千、1万条数据记录

注意:

        批量插入如果数据量太大可能出现下面的情况:

                MySQL报错:Packets larger than max_allowed_packet are not allowed

        原因:

                SQL语句是有长度限制,需要控制一次批量插入的容量,默认是1M,测试时修改为8M

        解决方案:

                通过修改max_allowed_packet的值来解决

                show VARIABLES like '%max_allowed_packet%';              

mysql> show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 134217728  |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

2.在事务中进行插入处理

把插入修改成:

START TRANSACTION;
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
       VALUES ('0', 'userid_0', 'content_0', 0);
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
        VALUES ('1', 'userid_1', 'content_1', 1);
.    ..
COMMIT;

使用事务可以提高数据的插入效率,这是因为进行一个INSERT操作时,MySQL内部会建立一个事务,在事务内才进行真正插入处理操作,通过使用事务可以减少创建事务的消耗,所有插入都在执行后才进行提交操作

这里也提供了测试对比,分别是不使用事务与使用事务在记录数为1百、1千、1万的情况

注意:

        事务需要控制大小,事务太大可能会影响执行的效率,MySQL有innodb_log_buffer_size配置项(默认是64MB),超过这个值会把innodb的数据刷到磁盘中,这时,效率会有所下降,所以比较好的做法是,在数据达到这个这个值前进行事务提交

mysql> show VARIABLES like '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 67108864 |
+------------------------+----------+
1 row in set (0.00 sec)

 3.数据有序插入

数据有序的插入是指插入记录在主键上是有序排列,例如datetime是记录的主键,

单条插入语句如下:

INSERT INTO `insert_table` (`id`,`uid`,`content`,`type`)
VALUES(3,'userid_1','content_1',1);

INSERT INTO `insert_table`(`id`,`uid`,`content`,`type`)
VALUES(1,'userid_0','content_0',0);

INSERT INTO `insert_table`(`id`,`uid`,`content`,`type`)
VALUES(2,'userid_2','content_2',2);

修改后的插入语句如下(修改了插入语句的执行顺序):

INSERT INTO `insert_table` (`id`,`uid`,`content`,`type`)
VALUES(1,'userid_0','content_0',0);

INSERT INTO `insert_table` (`id`,`uid`,`content`,`type`)
VALUES(2,'userid_1','content_1',1);

INSERT INTO`insert_table`(`id`,`uid`,`content`,`type`)
VALUES(3,'userid_2','content_2',2);

由于数据库插入时,需要维护索引数据,无序的记录会增大维护索引的成本,可以参照innodb使用的B+tree索引,如果每次插入记录都在索引的最后面,索引的定位效率很高,并且对索引调整较小;如果插入的记录在索引中间,需要B+tree进行分裂合并等处理,会消耗比较多计算资源,并且插入记录的索引定位效率会下降,数据量较大时会有频繁的磁盘操作

下面提供随机数据与顺序数据的性能对比,分别是记录为1百、1千、1万、10万、100万。

从测试结果来看,该优化方法的性能有所提高,但是提高并不是很明显

 总结:

        合并数据+事务(随机)的方法在较小数据量时,性能提高是很明显的,数据量较大时(1千万以上),性能会急剧下降,这是由于此时数据量超过了innodb_buffer的容量,每次定位索引涉及较多的磁盘读写操作,性能下降较快

        而使用合并数据+事务+有序数据的方在数据量达到千万级以上表现依旧是良好,在数据量较大时,有序数据索引定位较为方便,不需要频繁对磁盘进行读写操作,所以可以维持较高的性能

4.单条与事务批量优缺点

自动提交事务:

        mysql 默认是autocommit=on也就是默认开启自动提交事务。这种情况下,一条sql就会开启一个事务,这时候同时执行一万条update,就会导致实际开启一万个事务,然后挨个执行,挨个开启,挨个提交

优缺点

        同时锁住数据较少,但是数据库资源占用严重,对外提供操作性能急剧下降 (可以理解为每个都要单独提交事务,频繁写日志binlog、redolog等)

手动提交事务:

        当autocommit=off时,同时执行一万条update,那么只会开启一个事务,等到所有都update后,一并commit

优缺点

        同时锁住数据较多,外面的select进不来,大量连接等待获取行锁,同样影响数据库对外服务能力

建议:

手动分批提交事务

 文章来源地址https://www.toymoban.com/news/detail-403350.html

 

到了这里,关于mysql 大量数据插入优化的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • sqlserver大量数据查询的优化方式--查询结构优化

    1.应尽量 避免在 where 子句中使用 !=或操作符 ,否则将引擎放弃使用索引而进行全表扫描。 2.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行

    2024年02月03日
    浏览(76)
  • 解决前端性能问题:如何优化大量数据渲染和复杂交互?

    ✨✨祝屏幕前的小伙伴们每天都有好运相伴左右,一定要天天开心!✨✨  🎈🎈作者主页: 喔的嘛呀🎈🎈 目录 引言 一、分页加载数据 二、虚拟滚动 三、懒加载 四、数据缓存 五、减少重绘和回流 六、优化图片和资源: 七、合并压缩文件 八、使用Web Workers  在前端开发

    2024年03月10日
    浏览(65)
  • MySQL 删除数据 批量删除(大量)数据

    在删除数据的时候根据不同的场景使用不同的方法,比如说删除表中部分数据、删除表的结构、删除所有记录并重置自增ID、批量删除大量数据等,可以使用delete、truncate、drop等语句。 类型 语句 删除全部/部分记录 delete from 表名 [where 条件]; 删除表的结构和数据 drop table [if

    2023年04月22日
    浏览(39)
  • MySQL如何导入大量数据?

    有时我们会遇到需要将大量数据导入MySQL的需求,一般数据存储在csv或者txt中,数据由\\\",\\\"分隔。这里提供两种方案供大家选择。 为了测试,我们先创建数据库和表,并创建一个用户。 2.1说明 load data infile其实有两种形态,load data infile和load data local infile。 load data infile:只能在

    2024年02月06日
    浏览(84)
  • 解决前端性能瓶颈:高效处理大量数据渲染与复杂交互的策略与优化方法

    ✨✨祝屏幕前的小伙伴们每天都有好运相伴左右,一定要天天开心!✨✨  🎈🎈作者主页: 喔的嘛呀🎈🎈 目录 引言 一、分页加载数据 二、虚拟滚动 三、懒加载 四、数据缓存 五、减少重绘和回流 六、优化图片和资源: 七、合并压缩文件 八、使用Web Workers  在前端开发

    2024年04月27日
    浏览(48)
  • Java,excel大量百万数据导出优化措施,SXSSFWorkbook流式、分批次导出示例

    在导出百万级的数据时,如果不采用适当的优化措施,确实可能会造成死机和内存崩溃等问题。 为避免这些问题,可以采用以下优化措施: 分批次读取数据:将需要导出的数据分成多个批次进行读取和写入,每次读取部分数据,写入 Excel 后即时清除内存。这样可以避免一次

    2024年02月16日
    浏览(42)
  • MySQL大量脏数据,如何只保留最新的一条?

    因为系统的一个Bug,导致数据库表中出现重复数据,需要做的是删除重复数据且只保留最新的一条数据。 具体场景是这样的 当然有人会说上面的问题我们可以建一个 order_no + cost_id 的组合唯一索引,这样就算代码有bug但至少数据库表中不会有脏数据。 似乎这样就可以了,然

    2023年04月20日
    浏览(55)
  • 对于监控选型的一些思考

    监控的选型: 1.首先是拉模式(例如prometheus)和推模式: 拉可以随意控制拉取频率和指标,可大可小,推的话收集者可以下发改变推频率的指令,实现比较麻烦;拉失败快速知道客户端节点agent监控异常,推的话只能看哪个节点没上报比较麻烦; 拉模式下客户端agent只需要读

    2024年02月11日
    浏览(38)
  • SQL优化--插入数据

    如果我们需要一次性往数据库表中插入多条记录,可以从以下三个方面进行优化。 即便是批量插入也不建议一次性插入太多,500-1000条较为合适。如果需要插入更多,建议分割成多次进行插入 为什么批量插入不建议太多 对于批量插入数据,确实不建议一次性插入太多记录,

    2024年02月09日
    浏览(31)
  • 【Mysql】X-DOC:Mysql数据库大量数据查询加速(定时JOB和存储过程应用案例)

    在某中台系统中,设计了大量的基础数据(维度数据、维度映射关系等)来支撑业务功能,业务表中存在大量的维度外键关联字段,其优点是可以实现前端的选择录入,数据校验,确保录入数据的准确性;缺点是在做业务报表时,需要做大量的维度关联(join)操作。 受限于

    2024年02月12日
    浏览(44)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包