精华内容
下载资源
问答
  • shell下向mysql批量插入数据的范例代码,有需要的小伙伴可以参考下
  • mysql批量插入数据

    千次阅读 2017-08-15 08:45:10
    最近在做EXCEL的大批量插入时发现了一个问题。...然后用了MYBATIS的批量插入 order="AFTER"> SELECT LAST_INSERT_ID() insert into brand (brand_name, keywords,description ,address,cityid,l

    最近在做EXCEL的大批量插入时发现了一个问题。


    如果一个个插入的话小量的话还好,但是量稍微大一点就非常慢。

    emm.....5W条直接页面无响应了


    然后用了MYBATIS的批量插入


    <insert id="insertExcel" parameterType="java.util.List">
    <selectKey resultType="java.lang.Integer" keyProperty="id"
    order="AFTER">
    SELECT LAST_INSERT_ID()
    </selectKey>
    insert into brand
    (brand_name, keywords,description ,address,cityid,logo,telephone,brand_type,status)
    values
    <foreach collection="list" item="brand" index="index"
    separator=",">
    (
    #{brand.brandName}, #{brand.keywords}, #{brand.description},
    #{brand.address},#{brand.cityid},#{brand.logo},
    #{brand.telephone}, #{brand.brandType}, #{brand.status}
    )
    </foreach>
    </insert>

    1W条数据 5S


    10W条数据 17S


    30W条数据46S



    由于EXCEL读取时间骤增就没有往下测试了

    展开全文
  • MySQL批量插入数据

    2016-08-11 11:09:44
    经常有测试sql语句性能的场景,这个时候往往需要做一个几百万乃至上千万的条记录的数据表,那么批量插入数据的sql语句就必不可少了。 最省心、速度最快的方法,莫过于用存储过程开启事务,批量插入一批数据之后提交...
     
    

            经常有测试sql语句性能的场景,这个时候往往需要做一个几百万乃至上千万的条记录的数据表,那么批量插入数据的sql语句就必不可少了。

    最省心、速度最快的方法,莫过于用存储过程开启事务,批量插入一批数据之后提交,然后不断重复这个步骤。

    CREATE PROCEDURE proc_insertDate(in i int)
    BEGIN
    	DECLARE l_i int DEFAULT 0;
    	START TRANSACTION;
    	WHILE 
    		l_i<i
    	DO
    		INSERT INTO tableName(`name`,`sex`) VALUES ('Jim',0);
    		SET l_i=l_i+1;
    		IF l_i%2000=0 THEN   
    			COMMIT;
    			START TRANSACTION;
    		END IF;
    	END WHILE;
    	COMMIT;
    END
     
    


    经过测试,我的电脑速度最快的是2000条提交一次,不过差距不是特别大。


    展开全文
  • mysql批量插入数据,一次插入多少行数据效率最高? 2019.08.29 21:20 31351浏览 一、前言 我们在操作大型数据表或者日志文件的时候经常会需要写入数据到数据库,那么最合适的方案就是数据库的批量插入。只是我们在...

    mysql批量插入数据,一次插入多少行数据效率最高?
    2019.08.29 21:20 31351浏览
    一、前言
    我们在操作大型数据表或者日志文件的时候经常会需要写入数据到数据库,那么最合适的方案就是数据库的批量插入。只是我们在执行批量操作的时候,一次插入多少数据才合适呢?假如需要插入的数据有百万条,那么一次批量插入多少条的时候,效率会高一些呢?这里博主和大家一起探讨下这个问题,应用环境为批量插入数据到临时表。

    二、批量插入前准备
    博主本地原本是循环查出来的数据,然后每1000条插入一次,直至完成插入操作。但是为什么要设置1000条呢,实不相瞒,这是因为项目里的其他批量插入都是一次插1000条。。汗,博主不服,所以想要测试下。

      首先是查看当前数据库的版本,毕竟各个版本之间存在差异,脱离版本讲数据库就是耍流氓(以前没少耍啊):
    

    mysql> select version();
    ±-----------+
    | version() |
    ±-----------+
    | 5.6.34-log |
    ±-----------+
    1 row in set (0.00 sec)
    1、插入到数据表的字段
    对于手动创建的临时表来说,字段当然是越少越好,而且字段占用的空间要尽量小一些,这样临时表不至于太大,影响表操作的性能。这里需要插入的字段是:

    字段1 int(10)
    字段2 int(10)
    字段3 int(10)
    字段4 int(10)
    我们一共插入四个字段,分别是3个int类型的,一个varchar类型的,整体来说这些字段都比较小,占用的内存空间会小一些。

    2、计算一行字段占用的空间
    对于innodb引擎来说,int类型可以存储4个字节,里面的Int(M)并不会影响存储字节的大小,这个M只是数据的展示位数,和mysql的ZEROFILL属性有关,即在数字长度不够的数据前面填充0,以达到设定的长度。此处不多说,想要了解的朋友可以百度一下,还是很有意思的。
    varchar(10)代表可以存储10个字符,不管是英文还是中文,最多都是10个,这部分假设存储的是中文,在utf-8mb4下,10个中文占用10*4 = 40个字节那么一行数据最多占用:4+4+4+40 = 52字节

    3、在数据里做插入操作的时候,整体时间的分配
    链接耗时 (30%)
    发送query到服务器 (20%)
    解析query (20%)
    插入操作 (10% * 词条数目)
    插入index (10% * Index的数目)
    关闭链接 (10%)
    从这里可以看出来,真正耗时的不是操作,而是链接,解析的过程。单条sql的话,会在链接,解析部分耗费大量的时间,因此速度会很慢,所以我们一般都是采用批量插入的操作,争取在一次链接里面写入尽可能多的数据,以此来提升插入的速度。但是这个尽可能多的数据是多少呢?一次到底插入多少才合适呢?

    三、批量插入数据测试
    开始测试,但是一开始插入多少是合适的呢,是否有上限?查询mysql手册,我们知道sql语句是有大小限制的。

    1、SQL语句的大小限制
    my.ini 里有 max_allowed_packet 这个参数控制通信的 packet 大小。mysql默认的sql语句的最大限制是1M(mysql5.7的客户端默认是16M,服务端默认是4M),可以根据设置查看。官方解释是适当增大 max_allowed_packet 参数可以使client端到server端传递大数据时,系统能够分配更多的扩展内存来处理。

    官方手册:https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
    2、查看服务器上的参数:
    mysql> show variables like ‘%max_allowed_packet%’;
    ±-------------------------±-----------+
    | Variable_name | Value |
    ±-------------------------±-----------+
    | max_allowed_packet | 33554432 |
    | slave_max_allowed_packet | 1073741824 |
    ±-------------------------±-----------+
    2 rows in set (0.00 sec)
    33554432字节 = 32M ,也就是规定大小不能超过32M。

    3、计算一次能插入的最大行记录
    1M计算的话,(1024*1024)/52 ≈ 20165 ,为了防止溢出,最大可一次性插入20000条(根据自己的配置和sql语句大小计算)。那么32M的话就是:20000 *32 = 640000 也就是64W条。

    4、测试插入数据比对
    (1)插入11W条数据,按照每次10,600,1000,20000,80000来测试:
    ±--------------+
    | count(c1.uin) |
    ±--------------+
    | 110000 |
    ±--------------+
    有个博客说一次插入10条最快,,我觉得一次插的有点少,咱们试试

    参考:https://www.cnblogs.com/aicro/p/3851434.html
    这个博主测试后,认为一次插10条是性能最快的,他的每条记录是3kb,相当于我的59行数据,取个整数60,那么对于这个博主是插入10条,对我来说插入:600,这几个值都试试。

    耗时:

    11W的数据,每次插入10条。耗时:2.361s
    11W的数据,每次插入600条。耗时:0.523s
    11W的数据,每次插入1000条。耗时:0.429s
    11W的数据,每次插入20000条。耗时:0.426s
    11W的数据,每次插入80000条。耗时:0.352s
    从这部分看,随着批量插入的增加,速度略有提升,最起码一次插10条应该不是最佳的。插入数据量多,减少了循环的次数,也就是在数据库链接部分的耗时有所减少,只是这个8W并不是极限数据,具体一次插入多少条,还有待参考。

    (2)加大数据量到24w
    ±--------------+
    | count(c1.uin) |
    ±--------------+
    | 241397 |
    ±--------------+
    耗时:

    24W的数据,每次插入10条。耗时:4.445s
    24W的数据,每次插入600条。耗时:1.187s
    24W的数据,每次插入1000条。耗时:1.13s
    24W的数据,每次插入20000条。耗时:0.933s
    24W的数据,每次插入80000条。耗时:0.753s
    一次插入24W反而性能最佳,这么代表我们的测试数据量依然不够。

    (3)加大测试量到42W
    ±--------------+
    | count(c1.uin) |
    ±--------------+
    | 418859 |

    耗时:

    42W的数据,每次插入1000条。耗时:2.216s
    42W的数据,每次插入80000条。耗时:1.777s
    42W的数据,每次插入16W条。耗时:1.523s
    42W的数据,每次插入20W条。耗时:1.432s
    42W的数据,每次插入30W条。耗时:1.362s
    42W的数据,每次插入40W条。耗时:1.764s
    随着插入量的增加,批量插入条数多了之后,性能是有所提升的。但是在达到30W以上之后,效率反而有所下降。这部分我的理解是mysql是要分配一定的内存给传过来的数据包使用,当批量插入的数据量到达一定程度之后,一次插入操作的开销就很耗费内存了。个人感觉,最佳大小是max_allowed_packet的一半,也就是极限能插入64W,选用32W也许性能会更好一些,同时也不会对mysql的其他操作产生太大的影响。

    5、如果插入的值就是sql语句限制的最大值,那么性能真的好吗?
    博主疯狂谷歌百度,都没有找到有人来具体的说一下这个问题,不过在高性能mysql里面发现一句话:
    客户端用一个单独的数据包将查询请求发送给服务器,所以当查询语句很长的时候,需要设置max_allowed_packet参数。但是需要注意的是,如果查询实在是太大,服务端会拒绝接收更多数据并抛出异常。与之相反的是,服务器响应给用户的数据通常会很多,由多个数据包组成。但是当服务器响应客户端请求时,客户端必须完整的接收整个返回结果,而不能简单的只取前面几条结果,然后让服务器停止发送。因而在实际开发中,尽量保持查询简单且只返回必需的数据,减小通信间数据包的大小和数量是一个非常好的习惯,这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

      后面通过各种百度,博主觉得最大只是代表传输数据包的最大长度,但性能是不是最佳就要从各个方面来分析了。比如下面列出的插入缓冲,以及插入索引时对于缓冲区的剩余空间需求,以及事务占有的内存等,都会影响批量插入的性能。
    

    四、其他影响插入性能的因素
    1、首先是插入的时候,要注意缓冲区的大小使用情况
    在分析源码的过程中,有一句话:如果buffer pool余量不足25%,插入失败,返回DB_LOCK_TABLE_FULL。这个错误并不是直接报错:max_allowed_packet 不够大之类的,这个错误是因为对于innodb引擎来说,一次插入是涉及到事务和锁的,在插入索引的时候,要判断缓冲区的剩余情况,所以插入并不能仅仅只考虑max_allowed_packet的问题,也要考虑到缓冲区的大小。

    参考淘宝的数据库日报:http://mysql.taobao.org/monthly/2017/09/10/

    2、插入缓存
    另外对于innodb引擎来说,因为存在插入缓存(Insert Buffer)这个概念,所以在插入的时候也是要耗费一定的缓冲池内存的。当写密集的情况下,插入缓冲会占用过多的缓冲池内存,默认最大可以占用到1/2的缓冲池内存,当插入缓冲占用太多缓冲池内存的情况下,会影响到其他的操作。

      也就是说,插入缓冲受到缓冲池大小的影响,缓冲池大小为:
    

    mysql> show variables like ‘innodb_buffer_pool_size’;
    ±------------------------±----------+
    | Variable_name | Value |
    ±------------------------±----------+
    | innodb_buffer_pool_size | 134217728 |
    ±------------------------±----------+
    换算后的结果为:128M,也就是说,插入缓存最多可以占用64M的缓冲区大小。这个大小要超过咱们设置的sql语句大小,所以可以忽略不计。

    详细解释:

      我们都知道,在InnoDB引擎上进行插入操作时,一般需要按照主键顺序进行插入,这样才能获得较高的插入性能。当一张表中存在非聚簇的且不唯一的索引时,在插入时,数据页的存放还是按照主键进行顺序存放,
    

    但是对于非聚簇索引叶节点的插入不再是顺序的了,这时就需要离散的访问非聚簇索引页,由于随机读取的存在导致插入操作性能下降。

      InnoDB为此设计了Insert Buffer来进行插入优化。对于非聚簇索引的插入或者更新操作,不是每一次都直接插入到索引页中,而是先判断插入的非聚集索引是否在缓冲池中,若在,则直接插入;若不在,则先放入到一个Insert Buffer中。看似数据库这个非聚集的索引已经查到叶节点,而实际没有,这时存放在另外一个位置。然后再以一定的频率和情况进行Insert Buffer和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中,这样就大大提高了对于非聚簇索引的插入性能。
    

    参考:https://cloud.tencent.com/developer/article/1200824
    参考:mysql技术内幕 Innodb篇

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

    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;
    参考:https://my.oschina.net/songhongxu/blog/163063

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

    查看: show variables like ‘%innodb_log_buffer_size%’;
    ±-----------------------±---------+
    | Variable_name | Value |
    ±-----------------------±---------+
    | innodb_log_buffer_size | 67108864 |
    ±-----------------------±---------+

    大概是:64M
    这种写法和批量写入的效果差不多,只不过sql语句还是单句的,然后统一提交。一个瓶颈是SQL语句的大小,一个瓶颈是事务的大小。当我们在提交sql的时候,首先是受到sql大小的限制,其次是受到事务大小的限制。在开启事务的情况下使用批量插入,会节省不少事务的开销,如果要追求极致的速度的话,建议是开着事务插入的。不过需要注意一下,内存是有限且共享的,如果批量插入占用太多的事务内存,那么势必会对其他的业务操作等有一定的影响。

    4、通过配置提升读写性能
    也可以通过增大innodb_buffer_pool_size 缓冲区来提升读写性能,只是缓冲区是要占用内存空间的,内存很珍贵,所以这个方案在内存富裕,而性能瓶颈的时候,可以考虑下。

    参考:https://my.oschina.net/anuodog/blog/3002941

    5、索引影响插入性能
    如果表中存在多个字段索引,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护。这样就降低了数据的插入速度。对于普通的数据表,主键索引是肯定要有的,想要加快性能的话,就是要有序插入,每次插入记录都在索引的最后面,索引的定位效率很高,并且对索引调整较小。如果插入的记录在索引中间,需要B+tree进行分裂合并等处理,会消耗比较多计算资源,并且插入记录的索引定位效率会下降,数据量较大时会有频繁的磁盘操作。

    五、总结
    博主经过测试+谷歌,最终是选用的一次批量插入数据量为max_allowed_packet大小的一半。只是在不断的搜索中,发现影响插入性能的地方挺多的,如果仅仅是拿max_allowed_packet这个参数作为分析,其实是没有意义的,这个参数只是设置最大值,但并不是最佳性能。不过需要注意,由于sql语句比较大,所以才执行完插入操作之后,一定要释放变量,不要造成无谓的内存损耗,影响程序性能。

      对于我们的mysql来说也是一样的,mysql的最佳性能是建立在各个参数的合理设置上,这样协同干活儿的效果最佳。如果其他设置不到位的话,就像是木桶原理一样,哪怕内存缓冲区设置的很大,但是性能取决的反而是设置最差的那个配置。关于mysql的配置调优
    
    展开全文
  • Mysql批量插入数据时如何解决重复问题? 前两天写代码遇到如果有相同数据就删除,采用新传入的数据,当时着急就用的delete+insert。今天有空就问了下度娘,感觉很有用,记录下来。 三种方法如下: 使用以上方法的...

    Mysql批量插入数据时如何解决重复问题?


    前两天写代码遇到如果有相同数据就删除,采用新传入的数据,当时着急就用的delete+insert。今天有空就问了下度娘,感觉很有用,记录下来。

    三种方法如下:

    使用以上方法的前提是表中有一个 PRIMARY KEY 或 UNIQUE 约束/索引,否则,使用以上三个语句没有特殊意义,与使用单纯的 INSERT INTO 效果相同。

    A、insert ignore into(若没有则插入,若存在则忽略)
    作用:
    insert ignore 会根据主键或者唯一键判断,忽略数据库中已经存在的数据
    若数据库没有该条数据,就插入为新的数据,跟普通的 insert into 一样
    若数据库有该条数据,就忽略这条插入语句,不执行插入操作。

    B、insert into ... on duplicate key update(若没有则正常插入,若存在则更新)
    作用:
    在 insert into 语句末尾指定 on duplicate key update,会根据主键或者唯一键判断:
    若数据库有该条数据,则直接更新原数据,相当于 update
    若数据库没有该条数据,则插入为新的数据,跟普通的 insert into 一样。

    C、replace into(若没有则正常插入,若存在则先删除后插入)
    作用:
    replace into 会根据主键或者唯一键判断:
    若表中已存在该数据,则先删除此行数据,然后插入新的数据,相当于 delete + insert
    可能会丢失数据、主从服务器的 AUTO_INCREMENT 不一致。
    若表中不存在该数据,则直接插入新数据,跟普通的 insert into 一样。

    版权声明:本文为CSDN博主「媛测」的原创文章,遵循CC 4.0 by-sa版权协议,转载请附上原文出处链接及本声明。
    原文链接:https://blog.csdn.net/lijing742180/article/details/90243470

    展开全文
  • mysql批量插入数据时,会存在重复数据的可能. 可以通过1)数据库加联合索引 2)修改sql,忽略重复的数据,防止数据库报错 1. CREATE TABLE `merchantBlackList` ( `id` int(5) NOT NULL AUTO_INCREMENT, `createTime` ...
  • python 从文件读写,mysql批量插入数据
  • 在Python中向MySQL批量插入数据的方法 haijd Article在Python中向MySQL批量插入数据的方法2008-09-01 22:51:49 | 作者:haijd使用Python操作MySQL数据库时,如果插入的数据条目很多,一条条地执行"inse.....
  • 事务对mysql批量插入数据的影响 前言 ​ 本人一年开发经验的小白,在项目中遇到一个API中需要一次向多张表中插入多条数据的情况,发现API响应接口很慢(mysql直接部署在linux中的时候不明显,mysql部署在docker中较...
  • 文章目录一、前言二、批量插入前准备1、插入到数据表的字段2、计算一行字段占用的空间3、在数据里做插入操作的时候,整体时间的分配三、批量插入数据测试1、SQL语句的大小限制2、查看服务器上的参数:3、计算一次能...
  • 在Python中,通过pymysql模块,编写简短的脚本,即方便快捷地控制MySQL数据库 一、连接数据库  使用的函数:pymysql.connect  语法:db=pymysql.connect(host='localhost',user='root',port=3306,password='Your ...
  • 1:service层循环调用dao层插入数据,一条一条来 2:拼接sql类似 insert into citytable (province,...) select ... union all select ... union all 在后台先拼接几百条然后插入 3:xml里用foreach循环插入 ...
  • MySql批量插入数据存储过程
  • PHP mysql 批量插入数据

    千次阅读 2018-10-15 17:33:33
    php向mysql插入百万数据只需不到一分钟时间,之间还可以优化 &lt;?php set_time_limit(0); $dsn = 'mysql:dbname=test;host=localhost;port=3306'; $username = 'root'; $password = '123456'; try { $pdo = ...
  • MySQL 批量插入数据,单次插入多少条数据效率最高

    万次阅读 多人点赞 2019-11-26 18:29:10
    文章目录一、前言二、批量插入前准备1、插入到数据表的字段2、计算一行字段占用的空间3、在数据里做插入操作的时候,整体时间的分配三、批量插入数据测试1、SQL语句的大小限制2、查看服务器上的参数:3、计算一次能...
  • 项目中mysql批量插入大概50000左右数据,使用事务和批量,但是速度依旧很慢,大约60s左右,迫切希望改进这个问题。 二、问题原因 尽管是批量addBatch()但是,mysql却默认没有执行批量,时间还是一条一条添加导致速度...
  • mysql批量插入数据的时候,如果有一个字段具有unique约束,而插入数据重复的话,就会抛出DuplicateKeyException 导致后续批量数据无法插入,这是可以在sql中使用ignore来让mysql不抛出异常: insert ignore ...
  • mysql批量插入数据的sql

    千次阅读 2019-05-30 16:01:57
    代码如下 delimiter $$ DROP PROCEDURE IF EXISTS proc_batch_insert; CREATE PROCEDURE proc_batch_insert() BEGIN DECLARE i INT; SET i=1;... INSERT INTO `testtt`.`test1`(`name`, `age`)...
  • 非常的实现原理,代码较多,建议大家仔细看看。
  • 使用Mybatis向MySQL批量插入数据

    千次阅读 2016-06-11 20:30:00
    数据库开发过程中经常会...本文以Mybatis的foreach为例,(假定Mybatis环境已经配置好)向MySQL批量插入数据;dao中设置要插入的数据; public int insertRecordsFromList(List<LoginLog> list){ Map, List<LoginLog
  • 一、数据库mysql 批量插入 时遇到的问题: 数据表的字段 一定不要是关键字(key,value等),否则你执行sql的时候一直报错,告诉你糟糕的sql语法,错误靠近###等 但是出于常理,我们肯定不会去想 是数据表的字段...
  • MySQL批量插入数据,分批提交笔记

    千次阅读 2019-07-12 13:12:33
    前言:之前就接触过MySQL批量提交,但是相对而言,虽然比单条提交速度快很多但是,如果数据量太大,高达百万级别或者更高,就需要进一步地提高速度。 准备:创建一个测试的数据表,如本次例子:test_user 1.使用...
  • 其实在插入1000万条数据的时候遇到了一些问题,现在先来解决他们,一开始插入100万条数据时候报错,控制台的信息如下: com.mysql.jdbc.PacketTooBigException: Packet for query is too large(4232009 > 419...
  • 之后,我尝试了使用参数化sql语句的形式,用 command.parameter ,但是每次好像只能接受一条mysql语句就要 command.excutenonquery(); 了 导致插入速度极慢 我又试了下CSV文件导入的方法,结果数据内容中包含emoji...
  • table CREATE TABLE `course` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL COMMENT '课程名称', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8...
  • 三、批量插入数据测试 1、SQL语句的大小限制 2、查看服务器上的参数: 3、计算一次能插入的最大行记录 4、测试插入数据比对 (1)插入11W条数据,按照每次10,600,1000,20000,80000来测试: (2)加大数据量到24w ...
  • MySQL(一) Python操控mysql批量插入数据

    千次阅读 2020-01-15 21:27:06
    在Python中,通过pymysql模块,编写简短的脚本,即方便快捷地控制MySQL数据库 一、连接数据库         使用的函数:pymysql.connect       ...
  • 一,Oracle和MySQL批量插入数据 Oracle批量插入数据: 建表: create table TableA( id integer, name varchar2(20), no varchar2(20) ) insert批量插入语句: insert all into TableA values(1,'张三',...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 57,676
精华内容 23,070
关键字:

mysql批量插入数据

mysql 订阅