-
2022-03-01 22:20:46
快速插入
-
直接插入
i # 在光标前面一个位置插入 a # 在光标后面一个位置插入 I # 从当前行第一个非空字符的前一个位置插入 A # 在当前行尾插入 o # 在光标所在行的下面新插入一行 O # 在光标所在行的上方新插入一行 gi # 从当前缓冲区buffer上次结束输入模式的地方开始插入 gI # 从当前行的第一列的位置开始插入
-
先删除,再插入,并结合文本对象使用
s # 删除光标所在字符,并插入;先按数字可向前删除多个字符 S # 删除光标所在行,并在行首插入
C # 删除光标光标位置到本行行尾,并进入插入模式 c$ # 同C cc # 删除光标所在行,并在行首插入,等同于S m,nc # 按回车,删除m到n行的文本,并进入插入模式 caw # 从光标所在单词开始向前删除一个单词(包括空格),并插入;先按数字可删除多个单词 ciw # 向前删除单词(不包括空格,空格单独算一个单词),并插入;先按数字可删除多个单词 cw # 从光标所在字符开始向前删除一个单词(包括空格),并插入;先按数字可删除多个单词 cb # 从光标所在字符开始向后删除一个单词(包括空格),并插入;先按数字可删除多个单词 ci{ ci< ci( ct, # 从光标所在字符开始删除字符串,直到找到逗号为止(逗号不删除),并插入。原理是命令t为行内搜索移动 cf, # 从光标所在字符开始删除字符串,直到遇到逗号为止(逗号也删除),并插入。原理是命令f为行内搜索移动
c操作符(operator)表示删除并插入,后面接的动作(motion)表示c操作的范围。
-
重复输入模式
在进入输入模式之前传递一个计数参数实现复制输入。比如5i
,之后输入文本Hello VIM,Vim
会重复输入这段文本5次。对进入输入模式的命令均有效,比如i, I, a, A, o, O等。
快速纠错/删除
ctrl+h # 删除前一个字符 ctrl+w # 删除前一个单词 ctrl+u # 删除当前行光标前的所有字符
此外,这些快捷键也支持在命令模式和Ex模式(命令行命令)下使用。
用寄存器进行输入
- 举例
# 普通模式下,将文本保存到寄存器 "ayiw
"a 告诉Vim你的下一个动作的目标地址是寄存器a
yiw复制一个内词(inner word)# 输入模式(插入模式),将文本从寄存器复制到当前文件buffer ctrl-r a
执行普通模式的命令
- 使用方法:在插入模式下,按下
ctrl-o
,进入insert-normal
子模式,可以执行普通模式下命令。 - 举例
ctrl-o 'a # 跳转到标志'a处 ctrl-o dtx # 从当前位置开始删除文本,直到遇到字母x ctrl-o D # 从当前位置开始删除文本,直到行末 ctrl-o zz # 居中窗口
更多相关内容 -
-
java2Word在Word任意位置插入图片
2015-07-13 21:30:45由于项目需要,苦苦对Java操作Word...找到一个比较巧妙的方法,就是利用find方法和insert方法把我想要的图片插入到我所定义的任意位置(原先用的是insertAtBookmark方法,但是苦于API中insertBookmark方法不能用)。 -
mysql批量插入数据,一次插入多少行数据效率最高?
2019-08-18 21:41:03文章目录一、前言二、批量插入前准备1、插入到数据表的字段2、计算一行字段占用的空间3、在数据里做插入操作的时候,整体时间的分配三、批量插入数据测试1、SQL语句的大小限制2、查看服务器上的参数:3、计算一次能...文章目录
一、前言
我们在操作大型数据表或者日志文件的时候经常会需要写入数据到数据库,那么最合适的方案就是数据库的批量插入。只是我们在执行批量操作的时候,一次插入多少数据才合适呢?假如需要插入的数据有百万条,那么一次批量插入多少条的时候,效率会高一些呢?这里博主和大家一起探讨下这个问题,应用环境为批量插入数据到临时表。
二、批量插入前准备
博主本地原本是循环查出来的数据,然后每
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 varchar(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
的配置调优,我们都在路上,加油!end
-
mysql批量插入数据
2021-07-23 09:48:14插入语句 普通使用的插入语句: insert into table (col1,col2,col3) values (’ 1’,’ John ‘,’ stu ‘); insert into table (col1,col2,col3) values (’ 2’,’ Jan ‘,’ stu ‘); insert into ...在此还记录了一些mysql基础知识,可以用作参考
优化方案
插入语句
普通使用的插入语句:
insert into table (col1,col2,col3) values (’ 1’,’ John ‘,’ stu ‘);
insert into table (col1,col2,col3) values (’ 2’,’ Jan ‘,’ stu ‘);
insert into table (col1,col2,col3) values (’ 3 ‘,’ Billy ‘,’ stu ');插入操作时间分配:
链接耗时 (30%) 发送query到服务器 (20%) 解析query (20%) 插入操作 (10% * 词条数目) 插入index (10% * Index的数目) 关闭链接 (10%)
从这里可以看出单条语句,会在链接、解析部分耗费大量时间,因此我们大多时候会采用批量插入来提升插入速度,争取在一次链接中尽可能多的写入数据(下面通过几个参数来说明一次到底插入多少数据量合适)。
使用语句拼接
insert into table (col1,col2,col3) values (’ 1’,’ John ‘,’ stu ‘),(’ 2’,’ Jan ‘,’ stu ‘),(’ 3 ‘,’ Billy ‘,’ stu ');
使用拼接sql语句的优势:
降低了日志(MYSQL的binlog和innodb的事务日志)刷盘的数据量和频率、较少了SQL语句的解析次数、减少了网络传输IO(远程客户端插入数据)等;对于拼接语句sql有一个长度限制(官方解释是适当增大 max_allowed_packet 参数可以使client端到server端传递大数据时,系统能够分配更多的扩展内存来处理。):
查看限制最大值:show variables like ‘%max_allowed_packet%’;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)
设置限制最大值:mysql一般可以在配置文件中通过设置变量max_allowed_packet 的值来更改sql长度限制最大值;
在使用python的pymysql包提供的executemany函数,默认使用sql拼接插入数据;设置完限制值后,那么我们在使用拼接时,一次性拼接多少条语句合适呢?首先我们需要计算出一条语句大概占用多少内存,然后通过我们设置的max_allowed_packet的50%-75%去除单条语句的大小,最后计算出每次拼接的数量;(这里的被除数设置为max_allowed_packet的50%-75%,是本人在自己测试时,得到效率最大的区间,每台机器可能不一致,个人猜想与内存等硬件相关);总之我们应该遵循一句话:减小通信间数据包的大小和数量是一个非常好的习惯。
使用事务
在一条insert语句中默认会开启一个事务,当执行多个insert语句时,事务的开销就会影响到插入数据的性能;因此最常用的优化手段就是在执行一块插入语句前手动开启事务(START TRANSACTION),执行完插入语句后使用COMMIT提交;
事务也需要控制大小,事务太大可能会影响执行的效率;MySQL有innodb_log_buffer_size配置项,超过这个值会把innodb的数据刷到磁盘中,这时,效率会有所下降。所以比较好的做法是,在数据达到这个这个值前进行事务提交。innodb_log_buffer_size大小查询:
mysql> show variables like '%innodb_log_buffer_size%'; +------------------------+----------+ | Variable_name | Value | +------------------------+----------+ | innodb_log_buffer_size | 67108864 | +------------------------+----------+
总结:从以上来看,在我们提交插入数据时主要受到两个限制,一个是sql语句本身的大小,其次是事务大小限制;在最大限制下,我们可以开启事务使用sql拼接批量插入,可以节省事务开销,但是需要注意,内存是有限且共享的,如果批量插入占用太多的事务内存,那么势必会对其他的业务操作等有一定的影响。
注:事务和sql拼接从宏观上是差不多的,都是使用了类似缓存的原理,将整块数据一起处理,微观上处理的方式还是有本质区别的;
个人猜测:通过开启事务,会将插入的数据临时放到缓冲池中,等到COMMIT提交时,再落盘到磁盘中;当使用拼接sql
时,其实也是将大量数据放到缓冲池中,单个拼接后sql执行过程内也会建立事务,当完成后落盘;因此使用拼接和使用事务,或者同时使用其实都是差不多,只是使用时的参数设置不同;比如单独使用事务时innodb_log_buffer_size设置大点,单数使用拼接max_allowed_packet设置大点,两者同时使用时两个配置值都适量设置;当然目前只是猜测,等日后研究后再来完善,或者大佬们提点下。数据插入影响
索引的影响
数据库插入时,需要维护索引的数据,当插入的记录无序时会增大维护索引(聚簇)的成本;我们可以参照innodb使用的B+tree索引,如果每次插入记录都在索引的最后面,索引的定位效率很高,并且对索引调整较小;如果插入的记录在索引中间,需要B+tree进行分裂合并等处理,会消耗比较多计算资源,并且插入记录的索引定位效率会下降,数据量较大时会有频繁的磁盘操作(无序,且缓冲池不够用)。
因此有序插入在一定程度上可以优化插入的效率。
插入缓存的影响(存在非聚簇索引时)
对于innodb引擎来说,一次插入是涉及到事务和锁的,所以插入并不能仅仅只考虑max_allowed_packet的问题,也要考虑服务器缓冲池的大小;
在innodb中存在一个插入缓存(insert buffer)的概念,所以在插入的时候也是要耗费一定的缓冲池内存的。当写密集的情况下,插入缓冲会占用过多的缓冲池内存,默认最大可以占用到1/2的缓冲池内存,当插入缓冲占用太多缓冲池内存的情况下,会影响到其他的操作。缓冲池的大小查询:
mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+
插入缓冲的详细解释:
我们都知道,在InnoDB引擎上进行插入操作时,一般需要按照主键顺序进行插入,这样才能获得较高的插入性能。当一张表中存在非聚簇的且不唯一的索引时,在插入时,数据页的存放还是按照主键进行顺序存放,但是对于非聚簇索引叶节点的插入不再是顺序的了,这时就需要离散的访问非聚簇索引页,由于随机读取的存在导致插入操作性能下降。
InnoDB为此设计了Insert Buffer来进行插入优化。对于非聚簇索引的插入或者更新操作,不是每一次都直接插入到索引页中,而是先判断插入的非聚集索引是否在缓冲池中,若在,则直接插入;若不在,则先放入到一个Insert Buffer中。看似数据库这个非聚集的索引已经查到叶节点,而实际没有,这时存放在另外一个位置。然后再以一定的频率和情况进行Insert Buffer和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中,这样就大大提高了对于非聚簇索引的插入性能。
具体可以参考:https://cloud.tencent.com/developer/article/1200824
缓存池可以大致类比cpu将物理内存当作磁盘的缓存,innodb将缓存池用作磁盘的缓存日志的影响
这里主要是介绍二进制日志BinLog:
MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志,它记录了所有的 DDL 和 DML 语句(除了数据查询语句select、show等),以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复,比如MySQL主从复制、数据恢复等;启动或关闭binlog:通过配置文件中的log-bin配置项来启动及关闭;
查看是否启动了binlog:mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec)
根据项目需求,可以选择关闭binlog来优化插入数据的效率。
服务器配置参数
mysql配置文件:my.cnf(linux)、my.ini(win);
- innodb_buffer_pool_size(缓冲池)
这个参数主要缓存innodb表的索引,数据,插入数据时的缓冲; - innodb_log_file_size
指定日志文件大小;这个值分配的大小和数据库的写入速度,事务大小,异常重启后的恢复有很大的关系;
当一个日志文件写满后,innodb会自动切换到另一个日志文件,而且会触发数据库检查点,这会导致缓存脏页小批量刷新,会明显降低innodb性能; - innodb_log_buffer_size
事务在内存中的缓冲大小,我也看到说这是将日志写入磁盘日志文件前的缓冲大小;不过确实会影响到事务;
一个事务能否成功提交的关键是日志是否成功落盘,与数据没有太大的关系 - max_allowed_packet
最大的sql语句大小限制;
想要了解具体的参数可以参考:https://blog.csdn.net/wjc19911118/article/details/51784783
mysql常用的引擎:InnoDB(上面的介绍都是以该引擎为基础)、Myisam
- innodb_buffer_pool_size(缓冲池)
-
1亿条数据批量插入 MySQL,哪种方式最快
2022-06-01 13:27:52所以通过随机生成人的姓名、年龄、性别、电话、email、地址 ,向mysql数据库大量插入数据,便于用大量的数据测试 SQL 语句优化效率。、在生成过程中发现使用不同的方法,效率天差万别。下图一共三千三百万数据:在...利用JAVA向Mysql插入一亿数量级数据—效率测评
这几天研究mysql优化中查询效率时,发现测试的数据太少(10万级别),利用 EXPLAIN 比较不同的 SQL 语句,不能够得到比较有效的测评数据,大多模棱两可,不敢通过这些数据下定论。
所以通过随机生成人的姓名、年龄、性别、电话、email、地址 ,向mysql数据库大量插入数据,便于用大量的数据测试 SQL 语句优化效率。、在生成过程中发现使用不同的方法,效率天差万别。
1、先上Mysql数据库,随机生成的人员数据图。分别是ID、姓名、性别、年龄、Email、电话、住址。
下图一共三千三百万数据:
在数据量在亿级别时,别点下面按钮,会导致Navicat持续加载这亿级别的数据,导致电脑死机。~觉着自己电脑配置不错的可以去试试,可能会有惊喜
2、本次测评一共通过三种策略,五种情况,进行大批量数据插入测试
策略分别是:
-
Mybatis 轻量级框架插入(无事务)
-
采用JDBC直接处理(开启事务、无事务)
-
采用JDBC批处理(开启事务、无事务)
测试结果:
Mybatis轻量级插入 -> JDBC直接处理 -> JDBC 批处理。
JDBC 批处理,效率最高
第一种策略测试:
2.1 Mybatis 轻量级框架插入(无事务)
Mybatis是一个轻量级框架,它比hibernate轻便、效率高。
但是处理大批量的数据插入操作时,需要过程中实现一个ORM的转换,本次测试存在实例,以及未开启事务,导致mybatis效率很一般。
这里实验内容是:
-
利用Spring框架生成mapper实例、创建人物实例对象
-
循环更改该实例对象属性、并插入。
//代码内无事务 private long begin = 33112001;//起始id private long end = begin+100000;//每次循环插入的数据量 private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8"; private String user = "root"; private String password = "0203"; @org.junit.Test public void insertBigData2() { //加载Spring,以及得到PersonMapper实例对象。这里创建的时间并不对最后结果产生很大的影响 ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); PersonMapper pMapper = (PersonMapper) context.getBean("personMapper"); //创建一个人实例 Person person = new Person(); //计开始时间 long bTime = System.currentTimeMillis(); //开始循环,循环次数500W次。 for(int i=0;i<5000000;i++) { //为person赋值 person.setId(i); person.setName(RandomValue.getChineseName()); person.setSex(RandomValue.name_sex); person.setAge(RandomValue.getNum(1, 100)); person.setEmail(RandomValue.getEmail(4,15)); person.setTel(RandomValue.getTel()); person.setAddress(RandomValue.getRoad()); //执行插入语句 pMapper.insert(person); begin++; } //计结束时间 long eTime = System.currentTimeMillis(); System.out.println("插入500W条数据耗时:"+(eTime-bTime)); }
本想测试插入五百万条数据,但是实际运行过程中太慢,中途不得不终止程序。最后得到52W数据,大约耗时两首歌的时间(7~9分钟)。随后,利用mybatis向mysql插入
10000
数据。结果如下:
利用mybatis插入 一万 条数据耗时:28613,即28.6秒
第二种策略测试:
2.2 采用JDBC直接处理(开启事务、关闭事务)
采用JDBC直接处理的策略,这里的实验内容分为开启事务、未开启事务是两种,过程均如下:
-
利用PreparedStatment预编译
-
循环,插入对应数据,并存入
事务对于插入数据有多大的影响呢? 看下面的实验结果:
//该代码为开启事务 private long begin = 33112001;//起始id private long end = begin+100000;//每次循环插入的数据量 private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8"; private String user = "root"; private String password = "0203"; @org.junit.Test public void insertBigData3() { //定义连接、statement对象 Connection conn = null; PreparedStatement pstm = null; try { //加载jdbc驱动 Class.forName("com.mysql.jdbc.Driver"); //连接mysql conn = DriverManager.getConnection(url, user, password); //将自动提交关闭 conn.setAutoCommit(false); //编写sql String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)"; //预编译sql pstm = conn.prepareStatement(sql); //开始总计时 long bTime1 = System.currentTimeMillis(); //循环10次,每次一万数据,一共10万 for(int i=0;i<10;i++) { //开启分段计时,计1W数据耗时 long bTime = System.currentTimeMillis(); //开始循环 while (begin < end) { //赋值 pstm.setLong(1, begin); pstm.setString(2, RandomValue.getChineseName()); pstm.setString(3, RandomValue.name_sex); pstm.setInt(4, RandomValue.getNum(1, 100)); pstm.setString(5, RandomValue.getEmail(4, 15)); pstm.setString(6, RandomValue.getTel()); pstm.setString(7, RandomValue.getRoad()); //执行sql pstm.execute(); begin++; } //提交事务 conn.commit(); //边界值自增10W end += 10000; //关闭分段计时 long eTime = System.currentTimeMillis(); //输出 System.out.println("成功插入1W条数据耗时:"+(eTime-bTime)); } //关闭总计时 long eTime1 = System.currentTimeMillis(); //输出 System.out.println("插入10W数据共耗时:"+(eTime1-bTime1)); } catch (SQLException e) { e.printStackTrace(); } catch (ClassNotFoundException e1) { e1.printStackTrace(); } }
1、我们首先利用上述代码测试无事务状态下,插入10W条数据需要耗时多少。
如图:
成功插入1W条数据耗时:21603 成功插入1W条数据耗时:20537 成功插入1W条数据耗时:20470 成功插入1W条数据耗时:21160 成功插入1W条数据耗时:23270 成功插入1W条数据耗时:21230 成功插入1W条数据耗时:20372 成功插入1W条数据耗时:22608 成功插入1W条数据耗时:20361 成功插入1W条数据耗时:20494 插入10W数据共耗时:212106
实验结论如下:
在未开启事务的情况下,平均每 21.2 秒插入 一万 数据。
接着我们测试开启事务后,插入十万条数据耗时,如图:
成功插入1W条数据耗时:4938 成功插入1W条数据耗时:3518 成功插入1W条数据耗时:3713 成功插入1W条数据耗时:3883 成功插入1W条数据耗时:3872 成功插入1W条数据耗时:3873 成功插入1W条数据耗时:3863 成功插入1W条数据耗时:3819 成功插入1W条数据耗时:3933 成功插入1W条数据耗时:3811 插入10W数据共耗时:39255
实验结论如下:
开启事务后,平均每 3.9 秒插入 一万 数据
第三种策略测试:
2.3 采用JDBC批处理(开启事务、无事务)
采用JDBC批处理时需要注意一下几点:
1、在URL连接时需要开启批处理、以及预编译
String url = “jdbc:mysql://localhost:3306/User?rewriteBatched -Statements=true&useServerPrepStmts=false”;
2、PreparedStatement预处理sql语句必须放在循环体外
代码如下:
private long begin = 33112001;//起始id private long end = begin+100000;//每次循环插入的数据量 private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8"; private String user = "root"; private String password = "0203"; @org.junit.Test public void insertBigData() { //定义连接、statement对象 Connection conn = null; PreparedStatement pstm = null; try { //加载jdbc驱动 Class.forName("com.mysql.jdbc.Driver"); //连接mysql conn = DriverManager.getConnection(url, user, password); //将自动提交关闭 // conn.setAutoCommit(false); //编写sql String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)"; //预编译sql pstm = conn.prepareStatement(sql); //开始总计时 long bTime1 = System.currentTimeMillis(); //循环10次,每次十万数据,一共1000万 for(int i=0;i<10;i++) { //开启分段计时,计1W数据耗时 long bTime = System.currentTimeMillis(); //开始循环 while (begin < end) { //赋值 pstm.setLong(1, begin); pstm.setString(2, RandomValue.getChineseName()); pstm.setString(3, RandomValue.name_sex); pstm.setInt(4, RandomValue.getNum(1, 100)); pstm.setString(5, RandomValue.getEmail(4, 15)); pstm.setString(6, RandomValue.getTel()); pstm.setString(7, RandomValue.getRoad()); //添加到同一个批处理中 pstm.addBatch(); begin++; } //执行批处理 pstm.executeBatch(); //提交事务 // conn.commit(); //边界值自增10W end += 100000; //关闭分段计时 long eTime = System.currentTimeMillis(); //输出 System.out.println("成功插入10W条数据耗时:"+(eTime-bTime)); } //关闭总计时 long eTime1 = System.currentTimeMillis(); //输出 System.out.println("插入100W数据共耗时:"+(eTime1-bTime1)); } catch (SQLException e) { e.printStackTrace(); } catch (ClassNotFoundException e1) { e1.printStackTrace(); } }
首先开始测试
无事务,每次循环插入10W条数据,循环10次,一共100W条数据。
结果如下图:
成功插入10W条数据耗时:3832 成功插入10W条数据耗时:1770 成功插入10W条数据耗时:2628 成功插入10W条数据耗时:2140 成功插入10W条数据耗时:2148 成功插入10W条数据耗时:1757 成功插入10W条数据耗时:1767 成功插入10W条数据耗时:1832 成功插入10W条数据耗时:1830 成功插入10W条数据耗时:2031 插入100W数据共耗时:21737
实验结果:
使用JDBC批处理,未开启事务下,平均每 2.1 秒插入 十万 条数据
接着测试
开启事务,每次循环插入10W条数据,循环10次,一共100W条数据。
结果如下图:
成功插入10W条数据耗时:3482 成功插入10W条数据耗时:1776 成功插入10W条数据耗时:1979 成功插入10W条数据耗时:1730 成功插入10W条数据耗时:1643 成功插入10W条数据耗时:1665 成功插入10W条数据耗时:1622 成功插入10W条数据耗时:1624 成功插入10W条数据耗时:1779 成功插入10W条数据耗时:1698 插入100W数据共耗时:19003
实验结果:
使用JDBC批处理,开启事务,平均每 1.9 秒插入 十万 条数据
3 总结
能够看到,在开启事务下 JDBC直接处理 和 JDBC批处理 均耗时更短。
-
Mybatis 轻量级框架插入 , mybatis在我这次实验被黑的可惨了,哈哈。实际开启事务以后,差距不会这么大(差距10倍)。大家有兴趣的可以接着去测试
-
JDBC直接处理,在本次实验,开启事务和关闭事务,耗时差距5倍左右,并且这个倍数会随着数据量的增大而增大。因为在未开启事务时,更新10000条数据,就得访问数据库10000次。导致每次操作都需要操作一次数据库。
-
JDBC批处理,在本次实验,开启事务与关闭事务,耗时差距很微小(后面会增加测试,加大这个数值的差距)。但是能够看到开启事务以后,速度还是有提升。
结论:设计到大量单条数据的插入,使用JDBC批处理和事务混合速度最快
实测使用批处理+事务混合插入1亿条数据耗时:174756毫秒
4 补充
JDBC批处理事务,开启和关闭事务,测评插入20次,一次50W数据,一共一千万数据耗时:
1、开启事务(数据太长不全贴了)
插入1000W数据共耗时:197654
2、关闭事务(数据太长不全贴了)
插入1000W数据共耗时:200540
还是没很大的差距~
借用:
分别是:
-
不用批处理,不用事务;
-
只用批处理,不用事务;
-
只用事务,不用批处理;
-
既用事务,也用批处理;(很明显,这个最快,所以建议在处理大批量的数据时,同时使用批处理和事务)
PS:防止找不到本篇文章,可以收藏点赞,方便翻阅查找哦。
-
-
MyBatis批量插入为什么比单条插入块?
2022-07-28 17:17:35今天在利用excel处理业务数据的时候开始打算一条一条插入数据库!因为要做数据校验和数据保存返回id之后进行其他表也插入!大约有30000多条数据库吧!花了大约3-5分钟,前端也会出现超时请求问题、让我意识到一条一... -
MyBatis 批量插入数据的 3 种方法
2021-11-12 13:17:24批量插入功能是我们日常工作中比较常见的业务功能之一,今天来一个 MyBatis 批量插入的汇总篇,同时对 3 种实现方法做一个性能测试,以及相应的原理分析。 先来简单说一下 3 种批量插入功能分别是: 循环单次插入;... -
MySQL INSERT:插入数据(添加数据)
2021-02-02 06:59:53数据库与表创建成功以后,需要向数据库的表中插入数据。在 MySQL 中可以使用 INSERT 语句向数据库已有的表中插入一行或者多行元组数据。基本语法INSERT 语句有两种语法形式,分别是 INSERT…VALUES 语句和 INSERT…... -
折半插入排序算法
2021-05-12 15:18:34折半插入排序(Binary Insertion Sort)是对插入排序算法的一种改进。所谓插入排序,就是不断的依次将元素插入前面已排好序的序列中。由于前半部分为已排好序的数列,这样我们不用按顺序依次寻找插入点,可以... -
几种重要的排序算法——插入排序
2021-07-05 23:28:24插入排序分为直接插入排序、折半插入排序、希尔排序(shell sort),后两种是在直接插入排序的改进上而来。 1.直接插入排序 排序思路:假设待排序的元素存放在数组A[1..n]A[1..n]A[1..n]中,在排序过程的某一时刻,... -
MyBatis-plus 批量插入的通用方法使用
2021-11-27 11:03:11MyBatis-plus 的批量插入方法2.1 通用批量插入方法 InsertBatchSomeColumn2.2 InsertBatchSomeColumn 的使用3. 批量插入 MySQL 数据库的坑3.1 MySQL 对非 NULL 字段插入 NULL 值的处理3.2 解决方法 1. MyBatis-plus... -
Oracle插入数据
2021-04-30 08:32:37在本教程中将学习如何使用Oracle INSERT语句将数据插入到表中。Oracle INSERT语句简介要将新行插入到表中,请按如下方式使用Oracle INSERT语句:INSERT INTO table_name (column_1, column_2, column_3, ... column_... -
jdbc批量插入的4种方式【百万条数据插入只需几秒】
2021-12-21 19:01:19解决批量插入【BatchUpdateException】异常,以及4秒插入100W条数据 -
73-插入排序——直接插入排序
2018-08-16 16:24:251. 插入排序 插入排序的基本思想:每次将一个待排序的记录,按其关键字大小插入到前面已经排好序的子表中的适当位置,直到全部记录插入完成为止。 2. 直接插入排序 假设待排序的记录存放在数组R[0 .. n... -
sql查询语句之插入、更新与删除数据
2021-11-14 15:41:241、插入数据 1.1 为表的所有字段插入数据 1、insert语句中不指定具体的字段名 2、insert语句中列出所有字段 1.2 为表的指定字段插入数据 1.3 同时插入多条记录 1.4 将查询结果插入到表中 2、更新数据 3、... -
Java代码实现:插入排序(直接插入,折半插入,希尔排序)
2019-04-05 12:21:11首先思考一个问题,往一个已经排好序的数组中插入一个数字,插入后数组依旧是有序数组,这是一个动态排序的过程,我们可以借鉴这种插入方法来实现排序,所以就有了插入排序; 所谓直接插入排序,就是将待排序的数组... -
MySQL批量插入(使用mybatis实现mysql数据库的批量插入操作)
2022-04-25 15:39:39MySQL批量插入 -
wps怎么插入目录,快速插入目录的操作步骤
2022-03-01 11:18:35学会自动插入目录的方法,再也不用羡慕别人的目录做得好看而自己的乱糟糟了,wps怎么插入目录?操作步骤是怎样的呢?自动插入目录这个技能一定要掌握啊!一起来看看。 -
SQL Server-插入数据
2022-03-10 23:21:31SQL Server-插入数据1、插入完整的行1.1、基本的insert语法(语法简单,并不安全)1.2、更安全的insert语法2、插入部分行3、插入检索出的数据4、从一个表复制到另一个表4、全部代码 在进行下面的操作前,先在数据库... -
MySQL insert 插入优化技巧,MySQL 优化学习第8天
2022-01-23 14:13:03本篇博客主要优化 MySQL 中的插入操作,核心实现 insert 优化任务。 load data infile 导入数据 使用上述命令,可以大幅度提高批量插入数据。 如果希望使用该命令,可以用 show 命令进行测试 show variables like '%... -
MySQL 批量插入数据,单次插入多少条数据效率最高
2019-11-26 18:29:10文章目录一、前言二、批量插入前准备1、插入到数据表的字段2、计算一行字段占用的空间3、在数据里做插入操作的时候,整体时间的分配三、批量插入数据测试1、SQL语句的大小限制2、查看服务器上的参数:3、计算一次能... -
mybatis 插入大量数据效率对比,foreach插入、SqlSession批量插入、sql插入
2020-05-14 11:26:28使用mybatis插入数据执行效率对比,对比三种方式, 1 使用 SqlSessionFactory,每1000条数据执行一次提交 2 使用mybatis-plus框架的insert方法,for循环,每次执行一次插入 3 使用ibatis,纯sql插入 先贴出执行... -
链表的插入操作
2020-12-12 18:06:53小甲鱼链表插入: 对链表的插入是指将一个结点插入到一个已有的链表中。 为了能做到正确插入,必须解决两个问题: ①怎样找到插入的位置; ②怎样实现插入。 我们可以先用指针变量p0指向待插入的结点,p1指向第一个... -
什么是回波损耗?什么是插入损耗?
2021-11-22 22:31:45什么又是插入损耗? 这个貌似很容易回答,回波损耗吗,就是Return Loss,缩写为RL,S11,插入损耗就是 Insertion Loss,IL,S21。确实没错,就是这么简单。但是为什么叫做回波呢?为什么又叫做插入呢? ... -
MySQL“删除后插入“与“插入或更新
2021-03-12 14:51:09所以在插入的时候需要判断是否有相同记录存在,如果有的话则更新已经存在的数据,如果没有的话插入该条数据。 我最初设计的版本是首先查询该条记录,如果记录存在的话则更新该记录,反之则插入一条新纪录。 在单条... -
Python实现插入排序
2020-07-08 23:51:35Python实现插入排序 -
jQuery实现在元素的不同位置插入元素(内部插入和外部插入)
2020-07-05 19:53:26这个位置可以在元素的内部和...append()方法可以在 目标元素内部 的 尾部 插入元素,插入的元素作为最后一个子元素。举例如下: <!doctypehtml> <html> <head> <title>元素插入</t... -
给mysql数据库插入当前时间
2021-01-19 03:25:53▌目录 多线程插入(单表) 多线程插入(多表) 预处理 SQL 多值插入 SQL 事务( N 条提交一次) ▌多线程插入(单表) 问:为何对同一个表的插入多线程会比单线程 ... 关于mysql数据库插入数据,不能插入中文和出现中文乱码... -
如何在dw中将视频插入
2021-06-18 11:09:11如何在dw中将视频插入电脑版Dreamweaver工具被很多人使用,用来编辑视频等,有的用户在使用该软件时,想要插入需要编辑的视频,但是却不知道如何插入,那么小编就来为大家介绍一下吧。具体如下:1.第一步,点击桌面... -
MongoDB——插入操作详解
2021-11-18 00:19:31插入文档 向集合中插入一条或多条文档。语法: db.collection.insert( { //可选字段 writeConcern:<document> ordered: <boolean> }, { //可选字段 writeConcern:<document> ...