对MySQL load data infile的一点想法

本文通过一次实际操作,探讨了MySQL中Load Data Infile命令的工作原理及其与事务的关系。实验证明Load Data Infile操作作为一个整体事务执行,并在二进制日志中以多条Insert语句的形式体现。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

今天无意和同事讨论MySQL load data infile的问题,这个问题是由开发那边引起的:他们竟然对一个4千万行的数据直接load data infile插入测试库,结果可想而知,出问题了。跑了一整晚都没跑完,基本上是卡死了【注释1】。原因可能是原表索引太多,导致速度巨慢。于是做了一个实验验证load data infile是否为一个单独的事务。

步骤是这样的:1,开启binlog,设置binlog_format=row,执行reset master;2,load data infile xxxxx;3,查看binlog。发现如下信息:

mysql> show binlog events;
+------------------+------+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 2 | 106 | Server ver: 5.1.62-log, Binlog ver: 4 |
| mysql-bin.000001 | 106 | Query | 2 | 174 | BEGIN |
| mysql-bin.000001 | 174 | Table_map | 2 | 220 | table_id: 17 (test.test) |
| mysql-bin.000001 | 220 | Write_rows | 2 | 1259 | table_id: 17 |
| mysql-bin.000001 | 1259 | Write_rows | 2 | 2298 | table_id: 17 |
| mysql-bin.000001 | 2298 | Write_rows | 2 | 3337 | table_id: 17 |
| mysql-bin.000001 | 3337 | Write_rows | 2 | 4376 | table_id: 17 |
| mysql-bin.000001 | 4376 | Write_rows | 2 | 5365 | table_id: 17 flags: STMT_END_F |
| mysql-bin.000001 | 5365 | Xid | 2 | 5392 | COMMIT /* xid=681 */ |
+------------------+------+-------------+-----------+-------------+---------------------------------------+

可以看出,总共是一个事务,也通过mysqlbinlog查看了binary log,确认中间是被拆分成了多个insert形式。所以load data infile基本上是这样执行的:

begin
insert into values(),(),(),()...
insert into values(),(),(),()...
insert into values(),(),(),()...
...
...
commit

当然,由于row格式的binlog的语句并不是很明显的记录成多值insert语句,它的格式时

insert into table

set @1=

set @2=

...

set @n=

insert into table

set @1=

set @2=

...

set @n=

insert ...

;注意这里有一个分号‘;’,其实前面这一部分就相当于前面说的多值insert形式

然后接下来就重复上面的那种格式,也就是一个load data infile 拆成了多个多值insert语句。

前面说的是row格式记录的load data infile,那么对于statement是怎么样的呢?statement格式的binlog,它是这样记录的,binlog中还是同样的load data语句,但是在记录load data 语句之前,它会先将你master上这个load data 使用到的csv格式的文件拆分成多个部分,然后传到slave上(在mysql的tmpdir下),当然传这些csv格式的文件也会记录binlog event,然后最后真正的SQL语句形式就是load data local infile '/tmp/SQL_X_Y'这种形式(这里假设mysql的tmpdir是默认的/tmp),实际上这样很危险,比如tmpdir空间不够,那就会报错。不过从效率上来说两者可能差不多,因为statement格式的binlog也是拆分成了多个语句。


分析到这,我就有一个想法:为什么不提供一个选项来执行一个load data infile来拆成成多个事务呢?这样最后整个语句执行成功的可能性更大吧,虽然我们可以自己通过拆分文件来做到这点,但是官方有命令来支持不是更好吗?


注释1:之前分析的原因是:load data infile是一个事务,而事务过程中要写redo,但一个事务这么大单个redo(几百M吧)势必又记录不了,因为此时一个事务,所以不可能将自己的同一个事务的redo覆盖掉的,所以导致卡死。这个实际上是错误的,不会由于这个原因导致,因为我尝试了插入一个表1kw条记录,而redo log大小设置为5M,可能成功插入。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值