MySQL怎么快速插入1億條數(shù)據(jù)
哈嘍，大家好，MySQL作為廣泛使用的開源關系型數(shù)據(jù)庫管理系統(tǒng)，應該沒有Java開發(fā)沒使用過吧。
關于MySQL，我們大部分時間都在聊，如何提高查詢效率，今天我們來聊聊如何提高MySQL的插入效率。
提高插入效率的方式
一般情況下，數(shù)據(jù)庫是運行在專門的服務器上，提高插入效率最明顯的當然是提高服務器配置啦。
比如，使用高性能的CPU和SSD磁盤，使用分布式系統(tǒng)架構，將寫入壓力分散到多個節(jié)點。這個方式的成本也是最高的，老板們當然不會使用這種方式了。
我們還可以從其他方面入手：
- 調(diào)整數(shù)據(jù)庫配置：優(yōu)化緩沖池大小、增大批量插入緩沖區(qū)等，通過調(diào)整MySQL數(shù)據(jù)庫參數(shù)的方式。
- 選擇使用MyISAM存儲引擎，因為其簡單的表鎖機制和無事務開銷而在插入速度上表現(xiàn)更優(yōu)。
- 使用批量插入的方式。
考慮到實際的應用場景，我們最可能操作的就是使用第3種實現(xiàn)方式，通過批量插入的方式來提高效率。
探索批量插入
常用的批量插入的方式有2種：
- 拼接SQL，使用 insert into xxx (...) values (...),(...),(...)
- 利用事務，將批量插入操作封裝在單個事務中，可以減少事務開銷并提高并發(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（關聯(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);三、手動控制事務的提交，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;
}
// 構建所有屬性
list.add(Orders.builder() ... .build());
}
ordersService.saveBatch(list);
}測試結果
使用了3種方式進行測試
未開啟批處理，batchXml
圖片
未開啟批處理，mybatis-flex提供的saveBatch
圖片
未開啟批處理，saveBatchSession
圖片
從這里的結果可以看出，使用 batchXml 的效率是最高的，遠遠超越其他方式。但是仔細一想，這些數(shù)據(jù)應該很不正常，插入1000條數(shù)據(jù)，竟然需要4秒左右，和單條插入1000次的時間幾乎沒有區(qū)別。
開啟批處理
經(jīng)過一番查詢資料，并檢查配置，發(fā)現(xiàn)果然另有玄機，連接數(shù)據(jù)庫的時候沒有開啟批處理
開啟方式：在spring的配置文件中，連接數(shù)據(jù)源時，url需要增加 allowPublicKeyRetrieval=true
然后重新測試一遍。
開啟批處理，saveBatchXml
圖片
開啟批處理，mybatis-flex提供的saveBatch
圖片
開啟批處理，saveBatchSession
圖片
這次的結果就比較正常了，可以看出來:
- saveBatchSession最快
- mybatis-flex提供的saveBatch 因為有些額外的操作，多消耗了10ms左右的時間
- saveBatchXml 相較于另外兩種方式，慢了30ms~40ms。
接下來，把每批次的處理數(shù)據(jù)由1000次增加到10000次，再次進行測試。
開啟批處理，saveBatchXml，10000條一批次
圖片
開啟批處理，saveBatchSession，10000條一批次
圖片
開啟批處理，mybatis-flex提供的saveBatch，10000條一批次
圖片
由此結果可以看出來：
- saveBatchSession和mybatis-flex提供的saveBatch 耗時基本一致
- saveBatchXml就明顯的慢一些，按照效率差算，差了將近50%的效率
總結
綜上，提高MySQL插入效率主要可通過調(diào)整數(shù)據(jù)庫配置、選擇適合的存儲引擎以及運用批量插入策略等方式實現(xiàn)。在實際應用中，尤其是在使用ORM框架進行數(shù)據(jù)操作時，應合理選擇并充分利用批量插入功能，以最大程度提升插入效率。
