`

MySQL批量SQL插入性能优化

 
阅读更多

对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此,优化数据库插入性能是很有意义的。
经过对MySQL innodb的一些性能测试,发现一些可以提高insert效率的方法,供大家参考参考。

1. 一条SQL语句插入多条数据
常用的插入语句如:

1
2
3
4
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);

修改成:

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

修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因是合并后日志量(MySQL的binlog和innodb的事务让日志)减少了,降低日志刷盘的数据量和频率,从而提高效率。通过合并SQL语句,同时也能减少SQL语句解析的次数,减少网络传输的IO。
这里提供一些测试对比数据,分别是进行单条数据的导入与转化成一条SQL语句进行导入,分别测试1百、1千、1万条数据记录。

2. 在事务中进行插入处理
把插入修改成:

1
2
3
4
5
6
7
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万的情况。

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

1
2
3
4
5
6
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);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('2', 'userid_2', 'content_2',2);

修改成:

1
2
3
4
5
6
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 ('2', 'userid_2', 'content_2',2);

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

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

性能综合测试:
这里提供了同时使用上面三种方法进行INSERT效率优化的测试。

从测试结果可以看到,合并数据+事务的方法在较小数据量时,性能提高是很明显的,数据量较大时(1千万以上),性能会急剧下降,这是由于此时数据量超过了innodb_buffer的容量,每次定位索引涉及较多的磁盘读写操作,性能下降较快。而使用合并数据+事务+有序数据的方式在数据量达到千万级以上表现依旧是良好,在数据量较大时,有序数据索引定位较为方便,不需要频繁对磁盘进行读写操作,所以可以维持较高的性能。

注意事项:
1. SQL语句是有长度限制,在进行数据合并在同一SQL中务必不能超过SQL长度限制,通过max_allowed_packet配置可以修改,默认是1M,测试时修改为8M。
2. 事务需要控制大小,事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项,超过这个值会把innodb的数据刷到磁盘中,这时,效率会有所下降。所以比较好的做法是,在数据达到这个这个值前进行事务提交。

分享到:
评论

相关推荐

    MySQL批量SQL插入性能优化详解

    MySQL批量SQL插入性能优化是数据库管理员和开发人员面临的重要任务,特别是在处理大数据量的系统时。本文将深入探讨几种能够显著提升MySQL InnoDB存储引擎插入性能的方法。 首先,一种有效的优化策略是通过合并多条...

    MySQL批量SQL插入各种性能优化.docx

    以下是一些针对MySQL批量SQL插入的性能优化策略: 1. **批量插入数据** - 传统的做法是逐条插入数据,但这种方式会导致大量的SQL语句执行和日志写入,从而降低性能。优化的方式是将多条插入语句合并为一条,如示例...

    Mybatis与JDBC批量插入MySQL数据库性能测试

    本文将探讨Mybatis和JDBC在批量插入MySQL数据库时的性能差异,并提供相关的测试资源。 首先,JDBC(Java Database Connectivity)是Java平台中用于与数据库交互的一种规范,它允许程序员使用SQL语句直接操作数据库...

    mysql数据库sql优化

    除了SQL优化之外,批量导入数据时也需要考虑性能因素。 ##### 1.2.1 insert语句优化 - **示例**: - 单条插入: ```sql INSERT INTO staffs (name, age, pos, add_time) VALUES ('July', 25, 'dev', NOW()); `...

    MySQL实现批量插入以优化性能的教程

    以下是一些关于如何利用MySQL的InnoDB存储引擎优化批量插入性能的方法: 1. **单条SQL语句插入多条数据**: 将多条独立的INSERT语句合并成一个包含多个VALUES子句的单一SQL语句,可以显著提高插入速度。这是因为...

    MYSQL开发性能研究之批量插入数据的优化方法

    本文主要探讨了在MySQL中如何通过批量插入优化性能,对比了几种不同的批量插入方法,并进行了实验验证。 首先,传统的单条插入方式在面对大量数据时效率低下,因为每条插入语句都需要经过解析、优化和执行等多个...

    Mybatis 3+Mysql 实现批量插入

    本文将深入探讨如何利用MyBatis框架结合MySQL数据库实现批量插入功能,包括其原理、配置、代码实现以及优化策略。 ### 一、MyBatis框架简介 MyBatis是一个优秀的持久层框架,它支持定制化SQL、存储过程以及高级...

    mysql 批量更新及效率对比

    近期,我们有了大量的 MySQL 批量更新经验,总结了 MySQL 批量更新的几种方法,并进行了性能测试,旨在找到最高效的更新语句写法。 方法一:批量 Update 批量 Update 是最原始的批量更新方法,每条记录执行一次 ...

    mysql的sql优化

    MySQL的SQL优化是数据库管理中的重要环节,尤其对于有经验的开发者来说,了解并掌握这一技能可以显著提升数据库性能,减少资源消耗。SQL优化主要涉及查询效率、索引使用、查询语句结构优化等多个方面。 首先,理解...

    MySQL创建存储过程批量插入10万条数据

    MySQL创建存储过程批量插入10万条数据 存储过程 1、首先防止主键冲突,我们清空表。 TRUNCATE table A_student; 2、编写存储过程 delimiter ‘$’; CREATE PROCEDURE batchInsert(in args int) BEGIN declare i int ...

    Mysql批量插入更新性能优化

    对于数据量较大的插入和更新,因io/cpu等性能瓶颈,会产生大量的时间消耗,目前主流的优化主要包括预编译、单条sql插入多条数据、事务插入等,下面详细介绍一下:  单条插入(Mybatis)  INSERT INTO SYS_CITY ...

    SpringBoot整合Mybatis连接SQL Server 跨库批量插入

    在IT行业中,Spring Boot是一个非常流行的微服务框架,它简化了Spring应用的初始化和配置过程。而Mybatis作为持久层框架,提供了灵活的...在实际项目中,还需要根据业务需求进行优化和调整,确保系统的稳定性和性能。

    EF批量更新、批量插入、 批量删除使用的是EFUtilities,免费的操作简单,速度超级快

    在.NET开发中,Entity Framework(简称EF)是一个强大的对象关系映射(ORM)框架,它允许开发者使用...通过其简单的API,开发者可以轻松地集成到现有的EF项目中,实现批量插入、更新和删除,从而优化应用程序的性能。

    MySql批量插入优化Sql执行效率实例详解

    本篇将详细介绍MySQL批量插入优化及其对Sql执行效率的影响,以及通过实例解析如何实现这一优化。 批量插入(Bulk Insert)是一种提高数据插入效率的方法,它允许一次提交多条插入语句,减少了与数据库的交互次数,...

    大牛教授MySQL之SQL语句最优化技术

    然而,随着数据量的增长,SQL查询性能优化变得至关重要。本篇将深入探讨MySQL中的SQL语句最优化技术,帮助你提升数据库操作的速度和效率。 1. **索引优化**:索引是提升查询速度的关键。为经常用于查询的列创建索引...

Global site tag (gtag.js) - Google Analytics