Boost MySQL Insert Performance with Batch Techniques and MyISAM

Ways to Improve Insert Efficiency

Generally, the most obvious way to speed up inserts is to upgrade server hardware, but cost‑effective improvements can be made by tuning MySQL configuration, selecting a suitable storage engine, and using batch insertion strategies.

Adjust database configuration: optimize buffer pool size, increase batch insert buffer, and modify other MySQL parameters.

Choose the MyISAM storage engine, which has a simple table‑lock mechanism and no transaction overhead, giving better insert performance.

Use batch insertion methods.

Exploring Batch Insert

Common batch insertion approaches include:

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

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

Test Code

SQL to create the orders table used in the tests:

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

Batch XML method (MyBatis <foreach>):

insert into orders (order_id, customer_id, order_status, payment_method, order_date, total_amount, shipping_fee, coupon_discount)
values
<foreach collection="orders" item="item" separator=","><br/>    (#{item.orderId}, #{item.customerId}, #{item.orderStatus}, #{item.paymentMethod}, #{item.orderDate}, #{item.totalAmount}, #{item.shippingFee}, #{item.couponDiscount})
</foreach>

MyBatis‑Flex saveBatch usage: ordersService.saveBatch(list); Manual transaction control ( saveBatchSession) implementation:

public void saveBatchSession(List<Orders> 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 runner that generates data:

@Test
public void generatorTestData() {
    genOrders(0L, 100000L);
}

private void genOrders(long start, long end) {
    List<Orders> list = new ArrayList<>();
    long s = System.currentTimeMillis();
    for (long i = start + 1; i <= end; i++) {
        if ((i - start) % 1000 == 0) {
            ordersService.saveBatchSession(list);
            list.clear();
            System.out.println("生成数据：" + (i - start) + "条，耗时：" + (System.currentTimeMillis() - s) + "ms");
            s = System.currentTimeMillis();
            continue;
        }
        list.add(Orders.builder() /* ... */ .build());
    }
    ordersService.saveBatch(list);
}

Test Results

Without batch processing (batchXml):

Without batch processing (mybatis‑flex saveBatch):

Without batch processing (manual saveBatchSession):

Enabling batch processing and retesting shows: saveBatchSession is the fastest.

mybatis‑flex saveBatch is slightly slower (about 10 ms extra). batchXml is slower by 30‑40 ms compared to the other two.

When the batch size is increased to 10 000 rows per batch, the results are: saveBatchSession and mybatis‑flex saveBatch have almost identical execution times. batchXml is noticeably slower, roughly 50 % less efficient.

Summary

Improving MySQL insert efficiency can be achieved by tuning database configuration, selecting an appropriate storage engine, and employing batch insertion strategies. When using ORM frameworks, it is essential to enable and fully leverage batch insert features.

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

For large data volumes, use saveBatch provided by MyBatisPlus or MyBatisFlex.