mysql batch 过长_MySQL-Mysql 使用Batch能提高写效率吗

1.对于支持事务的表可以采用事务处理。加快批量处理。

原因：

a.对于支持事务的，其实单条语句也是一个事务，所以redo，undo 及其他日志也是必不可少的。并且每个语句都作为事务，记录的日志反而会多些。

b.另外就是批量写入的原因了。

2.对于非事务的表。采用表锁方式。

LOCK TABLES a WRITE;

INSERT INTO a VALUES (1,23),(2,34),(4,33);

INSERT INTO a VALUES (8,26),(6,29);

...

UNLOCK TABLES;

表锁方式之所以快，是因为索引buffer 只需要被刷新到磁盘一次，而其他则是每个insert 都要写入。

This benefits performance because the index buffer is flushed to disk only once, after all INSERT statements have completed. Normally, there would be as many index buffer flushes as there are INSERT statements. Explicit locking statements are not needed if you can insert all rows with a single INSERT.

To obtain faster insertions for transactional tables, you should use START TRANSACTION and COMMIT instead of LOCK TABLES.