Improving MySQL Insert Performance with Batch Operations and MyBatis Flex

Hello everyone, MySQL is a widely used open‑source relational database, and most Java developers have used it.

While we often discuss query optimization, this article focuses on improving MySQL insert performance.

Ways to Improve Insert Efficiency

Generally, the most obvious improvement is upgrading server hardware (high‑performance CPU, SSD, distributed architecture), but this is costly.

Other practical methods include:

Adjusting database configuration (buffer pool size, bulk‑insert buffer, etc.).

Choosing the MyISAM storage engine for its simple table‑lock mechanism and lack of transaction overhead.

Using batch‑insert techniques.

In real applications, the third method—batch insertion—is the most commonly used.

Exploring Batch Inserts

There are two typical batch‑insert approaches:

Concatenating SQL statements, e.g., insert into xxx (...) values (...),(...),(...) .

Wrapping multiple inserts in a single transaction to reduce overhead and improve concurrency.

In MyBatis‑Plus and MyBatis‑Flex, the saveBatch method implements the second approach.

Test Code

SQL used for testing:

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='订单信息表';

Three batch‑insert implementations:

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})

ordersService.saveBatch(list);

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();
}

Test Results

Three methods were tested without batch processing enabled. The batchXml method appeared fastest, but the timings were unrealistic (≈4 seconds for 1 000 rows, similar to single‑row inserts).

Enabling Batch Processing

Investigation revealed that the JDBC URL lacked the allowPublicKeyRetrieval=true parameter, which disabled batch mode. After adding this parameter and retesting, the results became consistent.

With batch processing enabled:

saveBatchSession was the fastest.

mybatis‑flex saveBatch was slightly slower (≈10 ms extra).

batchXml lagged by 30‑40 ms.

Increasing the batch size to 10 000 rows showed that saveBatchSession and mybatis‑flex saveBatch had similar performance, while batchXml was about 50 % slower.

Conclusion

Improving MySQL insert efficiency can be achieved by tuning database configuration, selecting an appropriate storage engine, and especially leveraging batch‑insert capabilities provided by ORM frameworks. For large data volumes, using saveBatch from MyBatis‑Plus or MyBatis‑Flex yields the best results.

Key takeaways:

Add allowPublicKeyRetrieval=true to the datasource URL to enable batch mode.

For massive inserts, use saveBatch from MyBatis‑Plus or MyBatis‑Flex.