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:

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

Batch XML method (MyBatis

<foreach>

):

<code>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></code>

MyBatis‑Flex

saveBatch

usage:

<code>ordersService.saveBatch(list);</code>

Manual transaction control (

saveBatchSession

) implementation:

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

Test runner that generates data:

<code>@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);
}</code>

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.