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