MySQL 性能优化——【面试题、场景题】大量数据插入优化

ShareBeHappy_Qin

已于 2024-12-21 19:56:18 修改

阅读量2.5k

点赞数 12

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： mysql 数据库

于 2024-05-13 22:35:13 首次发布

本文链接：https://blog.youkuaiyun.com/longool/article/details/138634389

效率最好的方式是：批量插入 + 开启事务。

1、数据批量插入相比数据逐条插入的运行效率得到极大提升；

## 批量插入
INSERT INTO `table` (`field1`, `field12`,...) VALUES ('valuea1', 'valuea2',...), ('valueb1', 'valueb2',...),...;

当数据逐条插入时，每条插入操作都需要进行一次数据库连接和一次磁盘写入操作，这会导致频繁的网络通信和磁盘 I/O 开销。如果有大量的数据需要插入，这些额外的开销会导致插入速度变慢，降低整体的运行效率。

相比之下，批量插入将多条数据合并为一个批次进行插入。通过一次数据库连接和一次磁盘写入操作，可以将多条数据一次性插入到数据库中。这样可以减少网络通信次数和磁盘 I/O 操作次数，大大提高了数据插入的效率。

批量插入的效率提升主要有以下几个方面的原因：

减少网络通信开销：批量插入可以通过一次数据库连接和一次传输操作将多条数据发送给数据库，减少了网络通信的次数和开销。
减少磁盘 I/O 操作：批量插入将多条数据合并为一个写入操作，减少了磁盘的读写次数，降低了磁盘 I/O 的开销。
优化事务管理：批量插入可以将多条插入操作合并为一个事务，减少了事务的开启和提交次数，提高了事务管理的效率。

2、数据逐条插入时，显示开启事务相比无事务的运行效率得到极大提升；

MySQL 每条插入操作，都会在内部建立一个隐式事务，在这个事务内进行真正的插入操作，所以逐条插入需要不停的创建事务和提交事务，造成较大的开销；显示开启事务，将多条插入操作放在同一个事务内，等都执行完再提交事务，可以减少创建和提交事务的次数，从而降低消耗。

## 没有开启事务
INSERT INTO `table` (`field1`, `field12`,...) VALUES ('value1', 'value2',...);
INSERT INTO `table` (`field1`, `field12`,...) VALUES ('value1', 'value2',...);

## 开启事务
START TRANSACTION;
INSERT INTO `table` (`field1`, `field12`,...) VALUES ('value1', 'value2',...)