MySQL怎么快速插入1億條數(shù)據(jù)

作者：了不起 2024-04-15 08:30:53

提高MySQL插入效率主要可通過調(diào)整數(shù)據(jù)庫配置、選擇適合的存儲引擎以及運用批量插入策略等方式實現(xiàn)。在實際應(yīng)用中，尤其是在使用ORM框架進行數(shù)據(jù)操作時，應(yīng)合理選擇并充分利用批量插入功能，以最大程度提升插入效率。

哈嘍，大家好，MySQL作為廣泛使用的開源關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，應(yīng)該沒有Java開發(fā)沒使用過吧。

關(guān)于MySQL，我們大部分時間都在聊，如何提高查詢效率，今天我們來聊聊如何提高MySQL的插入效率。

提高插入效率的方式

一般情況下，數(shù)據(jù)庫是運行在專門的服務(wù)器上，提高插入效率最明顯的當(dāng)然是提高服務(wù)器配置啦。

比如，使用高性能的CPU和SSD磁盤，使用分布式系統(tǒng)架構(gòu)，將寫入壓力分散到多個節(jié)點。這個方式的成本也是最高的，老板們當(dāng)然不會使用這種方式了。

我們還可以從其他方面入手：

調(diào)整數(shù)據(jù)庫配置：優(yōu)化緩沖池大小、增大批量插入緩沖區(qū)等，通過調(diào)整MySQL數(shù)據(jù)庫參數(shù)的方式。
選擇使用MyISAM存儲引擎，因為其簡單的表鎖機制和無事務(wù)開銷而在插入速度上表現(xiàn)更優(yōu)。
使用批量插入的方式。

考慮到實際的應(yīng)用場景，我們最可能操作的就是使用第3種實現(xiàn)方式，通過批量插入的方式來提高效率。

探索批量插入

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

拼接SQL，使用 insert into xxx (...) values (...),(...),(...)
利用事務(wù)，將批量插入操作封裝在單個事務(wù)中，可以減少事務(wù)開銷并提高并發(fā)性能。

在mybatisPlus，以及mybatis-flex中，saveBatch 就是使用的這種方式

接下來我們來測試一下這幾個方法。

測試代碼

測試的SQL

CREATE TABLE `orders`  
(  
    `order_id`         BIGINT         NOT NULL AUTO_INCREMENT COMMENT '訂單ID（主鍵）',  
    `customer_id`      BIGINT         NOT NULL COMMENT '客戶ID（關(guān)聯(lián)customer表）',  
    `order_status`     tinyint(4)     NOT NULL DEFAULT 1 COMMENT '訂單狀態(tài) 1-待支付 2-已支付 3-待發(fā)貨 4-已發(fā)貨 5-已完成 6-已取消',  
    `payment_method`   tinyint(4)     NULL     DEFAULT null COMMENT '支付方式; 1-現(xiàn)金 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 '優(yōu)惠券減免金額',  
    `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

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=",">  
    (#{item.orderId}, #{item.customerId}, #{item.orderStatus}, #{item.paymentMethod}, #{item.orderDate}, #{item.totalAmount}, #{item.shippingFee}, #{item.couponDiscount})  
</foreach>

二、使用mybatis-flex提供的saveBatch

ordersService.saveBatch(list);

三、手動控制事務(wù)的提交，saveBatchSession

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  
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);  
//          ordersService.saveBatchXml(list);   
//   ordersService.saveBatch(list);  
   list.clear();  
   itemAll.clear();  
   System.out.println("生成數(shù)據(jù)：" + (i - start) + "條，耗時：" + (System.currentTimeMillis() - s) + "ms");  
   s = System.currentTimeMillis();  
   continue;  
  }  
  // 構(gòu)建所有屬性  
  list.add(Orders.builder() ... .build());
 }  
 ordersService.saveBatch(list);  
}