V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
git00ll
V2EX  ›  MySQL

在一个事务内插入大量数据会怎么样

  •  
  •   git00ll · 2021-09-07 11:27:06 +08:00 · 3466 次点击
    这是一个创建于 933 天前的主题,其中的信息可能已经有所发展或是发生改变。

    mysql,讨论下面两种场景

    开启事务
    插入 1000 万条数据
    提交事务
    
    开启事务
    插入 1000 万条数据
    回滚事务
    

    会不会导致数据库挂掉。

    如何做到安全插入大批量数据进数据库

    20 条回复    2021-09-13 11:42:41 +08:00
    HamQ
        1
    HamQ  
       2021-09-07 11:36:40 +08:00
    mysql 不清楚 oracle 6 曾经试过 没遇到任何问题 纵然插入数据途中服务器强制断电也没问题
    BiChengfei
        2
    BiChengfei  
       2021-09-07 11:37:52 +08:00
    产品经理都不敢提的需求
    不过可以思考下 insert into A_bak select * from A
    Sasasu
        3
    Sasasu  
       2021-09-07 12:27:23 +08:00   ❤️ 4
    开启事务 插入 1000 万条数据 提交事务。不会
    开启事务 插入 1000 万条数据 回滚事务。会开始 undo,卡很久。
    bk201
        4
    bk201  
       2021-09-07 13:06:10 +08:00
    我觉得没啥影响,但是事务会超时。
    cheng6563
        5
    cheng6563  
       2021-09-07 13:48:54 +08:00
    实际上开事务会快很多,很多 GUI 工具的导入功能都会自动开个事务。
    sy20030260
        6
    sy20030260  
       2021-09-07 15:18:38 +08:00
    @Sasasu 请问下,这里的「卡很久」应该只是卡当前 session ?其他的读写请求也会阻塞?
    haoliang
        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/ ),估计事务的一部分代价吧
    msg7086
        8
    msg7086  
       2021-09-07 15:21:13 +08:00
    理论上只要你硬盘够大够快,就没什么问题。回滚无非就是烧点硬盘性能罢了。
    haoliang
        9
    haoliang  
       2021-09-07 15:27:29 +08:00
    幸好我看到了 @Sasasu 的留言,这个 transaction log 应该对应的是 [innodb undo log]( https://mariadb.com/kb/en/innodb-undo-log/ )
    auxox
        10
    auxox  
       2021-09-07 15:58:32 +08:00
    binlog 可能会把磁盘打爆
    FaceBug
        11
    FaceBug  
       2021-09-07 16:01:27 +08:00
    既然存在不安全的可能性,有没有可能,插入的过程中,程序本身挂掉了
    MonkeyJon
        12
    MonkeyJon  
       2021-09-07 16:20:03 +08:00
    为什么要一次性插入那么多数据,分批次,批量插入不好嘛
    gBurnX
        13
    gBurnX  
       2021-09-07 16:23:31 +08:00
    @HamQ

    强制断电过分了啊。

    只看软件部分,在严格按照鲁棒性要求,做好各种情况的失败处理,并且测试到位,那么用户在生产系统里,强行杀死软件进程,或强制关机,是不会影响到数据安全的。

    但强制断电,万一内存条、磁盘甚至 CPU 等硬件炸了,软件再强也没办法了。
    lyjr
        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 资源。
    Sasasu
        15
    Sasasu  
       2021-09-07 18:50:48 +08:00
    @sy20030260 理论上应该没有全局锁,但是会吃满你的硬盘 IO,别的东西动弹不了
    xuanbg
        16
    xuanbg  
       2021-09-07 19:16:01 +08:00
    当然要开启事务了。不过我一般是读 500 写 500,循环到数据搬运完。1000 万一次怼,内存估计会直接爆掉吧。
    iseki
        17
    iseki  
       2021-09-08 00:16:43 +08:00
    挂掉是不会的,成熟的数据库都不会,如果挂了那就是 bug 。不过如果超过了日志限制会报错回滚的。
    sjzjams
        18
    sjzjams  
       2021-09-08 10:17:53 +08:00
    一次性 1000 万的数据插入,用事物就是一个保证安全的过程
    LexLuth0r
        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 开始恢复数据。所以只要磁盘足够大,大事物的只会影响读写性能,不会让整个服务挂掉。
    HamQ
        20
    HamQ  
       2021-09-13 11:42:41 +08:00
    @gBurnX 哈哈 没错 所以公司那么几台被淘汰下来没啥用的服务器是专门用来干这个的 反正啥东西都放上去一跑 之后过去一拔插头 爽, 机器倒没怎么坏过 不过操作系统挂过好几十次了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5915 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 02:02 · PVG 10:02 · LAX 19:02 · JFK 22:02
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.