mysql,讨论下面两种场景
开启事务
插入 1000 万条数据
提交事务
开启事务
插入 1000 万条数据
回滚事务
会不会导致数据库挂掉。
如何做到安全插入大批量数据进数据库
1
HamQ 2021-09-07 11:36:40 +08:00
mysql 不清楚 oracle 6 曾经试过 没遇到任何问题 纵然插入数据途中服务器强制断电也没问题
|
2
BiChengfei 2021-09-07 11:37:52 +08:00
产品经理都不敢提的需求
不过可以思考下 insert into A_bak select * from A |
3
Sasasu 2021-09-07 12:27:23 +08:00 4
开启事务 插入 1000 万条数据 提交事务。不会
开启事务 插入 1000 万条数据 回滚事务。会开始 undo,卡很久。 |
4
bk201 2021-09-07 13:06:10 +08:00
我觉得没啥影响,但是事务会超时。
|
5
cheng6563 2021-09-07 13:48:54 +08:00
实际上开事务会快很多,很多 GUI 工具的导入功能都会自动开个事务。
|
6
sy20030260 2021-09-07 15:18:38 +08:00
@Sasasu 请问下,这里的「卡很久」应该只是卡当前 session ?其他的读写请求也会阻塞?
|
7
haoliang 2021-09-07 15:19:45 +08:00
同好奇,搜索了下。
参考这里: https://mariadb.com/kb/en/how-to-quickly-insert-data-into-mariadb/#using-big-transactions > When doing many inserts in a row, you should wrap them with BEGIN / END to avoid doing a full transaction > (which includes a disk sync) for every row. For example, doing a begin/end every 1000 inserts will speed up your > inserts by almost 1000 times. > ... > The reason why you may want to have many BEGIN/END statements instead of just one is that the former will use up less transaction log space. 然后发现了一个 [transaction log]( https://mariadb.com/kb/en/innodb-redo-log/ ),估计事务的一部分代价吧 |
8
msg7086 2021-09-07 15:21:13 +08:00
理论上只要你硬盘够大够快,就没什么问题。回滚无非就是烧点硬盘性能罢了。
|
9
haoliang 2021-09-07 15:27:29 +08:00
幸好我看到了 @Sasasu 的留言,这个 transaction log 应该对应的是 [innodb undo log]( https://mariadb.com/kb/en/innodb-undo-log/ )
|
10
auxox 2021-09-07 15:58:32 +08:00
binlog 可能会把磁盘打爆
|
11
FaceBug 2021-09-07 16:01:27 +08:00
既然存在不安全的可能性,有没有可能,插入的过程中,程序本身挂掉了
|
12
MonkeyJon 2021-09-07 16:20:03 +08:00
为什么要一次性插入那么多数据,分批次,批量插入不好嘛
|
13
gBurnX 2021-09-07 16:23:31 +08:00
@HamQ
强制断电过分了啊。 只看软件部分,在严格按照鲁棒性要求,做好各种情况的失败处理,并且测试到位,那么用户在生产系统里,强行杀死软件进程,或强制关机,是不会影响到数据安全的。 但强制断电,万一内存条、磁盘甚至 CPU 等硬件炸了,软件再强也没办法了。 |
14
lyjr 2021-09-07 17:25:51 +08:00 1
假如 mysql 的 redo 日志配置如下:
innodb_log_files_in_group=4 innodb_log_file_size=4G 则 redo 日志文件总大小就是 16G,写入数据超过了就必然报错了。但其实 mysql 会作 redo 容量预测,有些 mysql 版本远远没到总大小(只到十分之一)就报错了。 redo 日志也比原始写入数据要大,redo 日志构造加入很多元数据,而且 undo 空间也要受 redo 保护,同样耗费 redo 资源。 |
15
Sasasu 2021-09-07 18:50:48 +08:00
@sy20030260 理论上应该没有全局锁,但是会吃满你的硬盘 IO,别的东西动弹不了
|
16
xuanbg 2021-09-07 19:16:01 +08:00
当然要开启事务了。不过我一般是读 500 写 500,循环到数据搬运完。1000 万一次怼,内存估计会直接爆掉吧。
|
17
iseki 2021-09-08 00:16:43 +08:00
挂掉是不会的,成熟的数据库都不会,如果挂了那就是 bug 。不过如果超过了日志限制会报错回滚的。
|
18
sjzjams 2021-09-08 10:17:53 +08:00
一次性 1000 万的数据插入,用事物就是一个保证安全的过程
|
19
LexLuth0r 2021-09-08 10:58:03 +08:00 1
开启事物,innodb 引擎会开始记录 redolog 和 undolog,写入的数据会记录到 buffer pool 的脏页中,当 redolog 写满或脏页占比过高会开始 flush 影响写入和查询的性能。未提交事物的 binlog 会记录在 binlog cache 中,binlog cache 写满也会刷到磁盘影响写入性能并且在等在等待真正的 binlog 记录时主从同步会暂停。undolog 写入同理。因为 mvcc 的机制,这个事物开始前的视图不会删除,在事物提交前会大量占用磁盘( MySQL 5.5 及以前的版本提交后也会占用)。回滚事物,读取 undolog,删除插入的记录。未提交时异常断电会从已保存在磁盘上的 redolog 开始恢复数据。所以只要磁盘足够大,大事物的只会影响读写性能,不会让整个服务挂掉。
|