MySQL怎么快速插入1亿条数据-工具盒子

哈喽，大家好，MySQL作为广泛使用的开源关系型数据库管理系统，应该没有Java开发没使用过吧。

关于MySQL，我们大部分时间都在聊，如何提高查询效率，今天我们来聊聊如何提高MySQL的插入效率。

提高插入效率的方式 {#提高插入效率的方式}

一般情况下，数据库是运行在专门的服务器上，提高插入效率最明显的当然是提高服务器配置啦。比如，使用高性能的CPU和SSD磁盘，使用分布式系统架构，将写入压力分散到多个节点。这个方式的成本也是最高的，老板们当然不会使用这种方式了。

我们还可以从其他方面入手：

调整数据库配置：优化缓冲池大小、增大批量插入缓冲区等，通过调整MySQL数据库参数的方式。
选择使用MyISAM存储引擎，因为其简单的表锁机制和无事务开销而在插入速度上表现更优。
使用批量插入的方式。

考虑到实际的应用场景，我们最可能操作的就是使用第3种实现方式，通过批量插入的方式来提高效率。

探索批量插入 {#探索批量插入}

常用的批量插入的方式有2种：

拼接SQL，使用 insert into xxx (...) values (...),(...),(...)
利用事务，将批量插入操作封装在单个事务中，可以减少事务开销并提高并发性能。
1. 在mybatisPlus，以及mybatis-flex中，saveBatch 就是使用的这种方式

接下来我们来测试一下这几个方法。

测试代码 {#测试代码}

测试的SQL

|---------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE orders ( order_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '订单ID（主键）', customer_id BIGINT NOT NULL COMMENT '客户ID（关联customer表）', order_status tinyint(4) NOT NULL DEFAULT 1 COMMENT '订单状态 1-待支付 2-已支付 3-待发货 4-已发货 5-已完成 6-已取消', payment_method tinyint(4) NULL DEFAULT null COMMENT '支付方式; 1-现金 2-支付宝 3-微信 4-银行卡', total_amount DECIMAL(10, 2) NOT NULL COMMENT '订单总金额', shipping_fee DECIMAL(10, 2) NOT NULL DEFAULT 0 COMMENT '运费', coupon_discount DECIMAL(10, 2) NOT NULL DEFAULT 0 COMMENT '优惠券减免金额', order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单日期', payment_time DATETIME DEFAULT NULL COMMENT '支付时间', shipping_address VARCHAR(255) NULL COMMENT '收货地址', receiver_name VARCHAR(50) NULL COMMENT '收货人姓名', receiver_phone VARCHAR(20) NULL COMMENT '收货人电话', PRIMARY KEY (order_id) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='订单信息表'; |

一、使用 batchXml

|-------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 | insert into orders (order_id, customer_id, order_status, payment_method, order_date, total_amount, shipping_fee, coupon_discount) values (#{item.orderId}, #{item.customerId}, #{item.orderStatus}, #{item.paymentMethod}, #{item.orderDate}, #{item.totalAmount}, #{item.shippingFee}, #{item.couponDiscount}) |

二、使用mybatis-flex提供的saveBatch

|-----------|--------------------------------| | 1 | ordersService.saveBatch(list); |

三、手动控制事务的提交，saveBatchSession

|------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 6 7 8 9 10 | public void saveBatchSession(List orders) { SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH); OrdersMapper mapper = session.getMapper(OrdersMapper.class); for (int i = 0,length = orders.size(); i < length; i++) { mapper.insert(orders.get(i)); } session.commit(); session.clearCache(); session.close(); } |

启动代码

|------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | @Test public void generatorTestData() { genOrders(0L, 100000L); } privatevoidgenOrders(longstart,longend){ Listlist=newArrayList<>(); longs=System.currentTimeMillis(); for(longi=start+1;i<=end;i++){ if((i-start)%1000==0){ ordersService.saveBatchSession(list); // ordersService.saveBatchXml(list); // ordersService.saveBatch(list); list.clear(); itemAll.clear(); System.out.println("生成数据："+(i-start)+"条，耗时："+(System.currentTimeMillis()-s)+"ms"); s=System.currentTimeMillis(); continue; } // 构建所有属性 list.add(Orders.builder()....build());} ordersService.saveBatch(list); } |

测试结果 {#测试结果}

使用了3种方式进行测试

未开启批处理，batchXml

未开启批处理，mybatis-flex提供的saveBatch

未开启批处理，saveBatchSession

从这里的结果可以看出，使用 batchXml 的效率是最高的，远远超越其他方式。但是仔细一想，这些数据应该很不正常，插入1000条数据，竟然需要4秒左右，和单条插入1000次的时间几乎没有区别。

开启批处理 {#开启批处理}

经过一番查询资料，并检查配置，发现果然另有玄机，连接数据库的时候没有开启批处理

开启方式：在spring的配置文件中，连接数据源时，url需要增加 allowPublicKeyRetrieval=true

然后重新测试一遍。

开启批处理，saveBatchXml

开启批处理，mybatis-flex提供的saveBatch

开启批处理，saveBatchSession

这次的结果就比较正常了，可以看出来:

saveBatchSession最快
mybatis-flex提供的saveBatch 因为有些额外的操作，多消耗了10ms左右的时间
saveBatchXml 相较于另外两种方式，慢了30ms~40ms。

接下来，把每批次的处理数据由1000次增加到10000次，再次进行测试。

开启批处理，saveBatchXml，10000条一批次

开启批处理，saveBatchSession，10000条一批次

开启批处理，mybatis-flex提供的saveBatch，10000条一批次

由此结果可以看出来：

saveBatchSession和mybatis-flex提供的saveBatch 耗时基本一致
saveBatchXml就明显的慢一些，按照效率差算，差了将近50%的效率 \(\text { 效率差 }=\frac{\text { 较长时间 }- \text { 较短时间 }}{\text { 较短时间 }}=\frac{1200 \mathrm{~ms}-800 \mathrm{~ms}}{800 \mathrm{~ms}}\)