10億數(shù)據(jù)如何快速插入MySQL？

首先考慮10億數(shù)據(jù)寫到MySQL單表可行嗎？

數(shù)據(jù)庫單表能支持10億嗎？

答案是不能，單表推薦的值是2000W以下。這個值怎么計(jì)算出來的呢？

MySQL索引數(shù)據(jù)結(jié)構(gòu)是B+樹，全量數(shù)據(jù)存儲在主鍵索引，也就是聚簇索引的葉子結(jié)點(diǎn)上。B+樹插入和查詢的性能和B+樹層數(shù)直接相關(guān)，2000W以下是3層索引，而2000w以上則可能為四層索引。

Mysql b+索引的葉子節(jié)點(diǎn)每頁大小16K。當(dāng)前每條數(shù)據(jù)正好1K，所以簡單理解為每個葉子節(jié)點(diǎn)存儲16條數(shù)據(jù)。b+索引每個非葉子節(jié)點(diǎn)大小也是16K，但是其只需要存儲主鍵和指向葉子節(jié)點(diǎn)的指針，我們假設(shè)主鍵的類型是 BigInt，長度為 8 字節(jié)，而指針大小在 InnoDB 中設(shè)置為 6 字節(jié)，這樣一共 14 字節(jié)，這樣一個非葉子節(jié)點(diǎn)可以存儲 16 * 1024/14=1170。

也就是每個非葉子節(jié)點(diǎn)可關(guān)聯(lián)1170個葉子節(jié)點(diǎn)，每個葉子節(jié)點(diǎn)存儲16條數(shù)據(jù)。由此可得到B+樹索引層數(shù)和存儲數(shù)量的表格。2KW 以上 索引層數(shù)為 4 層，性能更差。

為了便于計(jì)算，我們可以設(shè)計(jì)單表容量在1KW，10億條數(shù)據(jù)共100個表。

如何高效的寫入數(shù)據(jù)庫

單條寫入數(shù)據(jù)庫性能比較差，可以考慮批量寫入數(shù)據(jù)庫，批量數(shù)值動態(tài)可調(diào)整。每條1K，默認(rèn)可先調(diào)整為100條批量寫入。

批量數(shù)據(jù)如何保證數(shù)據(jù)同時寫成功？MySQL Innodb存儲引擎保證批量寫入事務(wù)同時成功或失敗。

寫庫時要支持重試，寫庫失敗重試寫入，如果重試N次后依然失敗，可考慮單條寫入100條到數(shù)據(jù)庫，失敗數(shù)據(jù)打印記錄，丟棄即可。

此外寫入時按照主鍵id順序順序?qū)懭肟梢赃_(dá)到最快的性能，而非主鍵索引的插入則不一定是順序的，頻繁地索引結(jié)構(gòu)調(diào)整會導(dǎo)致插入性能下降。最好不創(chuàng)建非主鍵索引，或者在表創(chuàng)建完成后再創(chuàng)建索引，以保證最快的插入性能。

是否需要并發(fā)寫同一個表

不能

并發(fā)寫同一個表無法保證數(shù)據(jù)寫入時是有序的。

提高批量插入的閾值，在一定程度上增加了插入并發(fā)度。無需再并發(fā)寫入單表

MySQL存儲引擎的選擇

Myisam 比innodb有更好的插入性能，但失去了事務(wù)支持，批量插入時無法保證同時成功或失敗，所以當(dāng)批量插入超時或失敗時，如果重試，勢必對導(dǎo)致一些重復(fù)數(shù)據(jù)的發(fā)生。但是為了保證更快的導(dǎo)入速度，可以把myisam存儲引擎列為計(jì)劃之一。

現(xiàn)階段我引用一下別人的性能測試結(jié)果：MyISAM與InnoDB對比分析

從數(shù)據(jù)可以看到批量寫入明顯優(yōu)于單條寫入。并且在innodb關(guān)閉即時刷新磁盤策略后，innodb插入性能沒有比myisam差太多。

innodb_flush_log_at_trx_commit: 控制MySQL刷新數(shù)據(jù)到磁盤的策略。

默認(rèn)=1，即每次事務(wù)提交都會刷新數(shù)據(jù)到磁盤，安全性最高不會丟失數(shù)據(jù)。

當(dāng)配置為0、2 會每隔1s刷新數(shù)據(jù)到磁盤， 在系統(tǒng)宕機(jī)、mysql crash時可能丟失1s的數(shù)據(jù)。

考慮到Innodb在關(guān)閉即時刷新磁盤策略時，批量性能也不錯，所以暫定先使用innodb（如果公司MySQL集群不允許改變這個策略值，可能要使用MyIsam了。）。線上環(huán)境測試時可以重點(diǎn)對比兩者的插入性能。

要不要進(jìn)行分庫

mysql 單庫的并發(fā)寫入是有性能瓶頸的，一般情況5K TPS寫入就很高了。

當(dāng)前數(shù)據(jù)都采用SSD 存儲，性能應(yīng)該更好一些。但如果是HDD的話，雖然順序讀寫會有非常高的表現(xiàn)，但HDD無法應(yīng)對并發(fā)寫入，例如每個庫10張表，假設(shè)10張表在并發(fā)寫入，每張表雖然是順序?qū)懭?，由于多個表的存儲位置不同，HDD只有1個磁頭，不支持并發(fā)寫，只能重新尋道，耗時將大大增加，失去順序讀寫的高性能。所以對于HDD而言，單庫并發(fā)寫多個表并不是好的方案?；氐絊SD的場景，不同SSD廠商的寫入能力不同，對于并發(fā)寫入的能力也不同，有的支持500M/s，有的支持1G/s讀寫，有的支持8個并發(fā)，有的支持4個并發(fā)。在線上實(shí)驗(yàn)之前，我們并不知道實(shí)際的性能表現(xiàn)如何。

所以在設(shè)計(jì)上要更加靈活，需要支持以下能力

支持配置數(shù)據(jù)庫的數(shù)量

支持配置并發(fā)寫表的數(shù)量，（如果MySQL是HDD磁盤，只讓一張表順序?qū)懭?，其他任?wù)等待）

通過以上配置，靈活調(diào)整線上數(shù)據(jù)庫的數(shù)量，以及寫表并發(fā)度，無論是HDD還是SSD，我們系統(tǒng)都能支持。不論是什么廠商型號的SSD，性能表現(xiàn)如何，都可調(diào)整配置，不斷獲得更高的性能。這也是后面設(shè)計(jì)的思路，不固定某一個閾值數(shù)量，都要動態(tài)可調(diào)整。

接下來聊一下文件讀取，10億條數(shù)據(jù)，每條1K，一共是931G。近1T大文件，一般不會生成如此大的文件。所以我們默認(rèn)文件已經(jīng)被大致切分為100個文件。每個文件數(shù)量大致相同即可。為什么切割為100個呢？切分為1000個，增大讀取并發(fā)，不是可以更快導(dǎo)入數(shù)據(jù)庫嗎？剛才提到數(shù)據(jù)庫的讀寫性能受限于磁盤，但任何磁盤相比寫操作，讀操作都要更快。尤其是讀取時只需要從文件讀取，但寫入時MySQL要執(zhí)行建立索引，解析SQL、事務(wù)等等復(fù)雜的流程。所以寫的并發(fā)度最大是100，讀文件的并發(fā)度無需超過100。

更重要的是讀文件并發(fā)度等于分表數(shù)量，有利于簡化模型設(shè)計(jì)。即100個讀取任務(wù)，100個寫入任務(wù)，對應(yīng)100張表。

如何保證寫入數(shù)據(jù)庫有序

既然文件被切分為100個10G的小文件，可以按照文件后綴+ 在文件行號 作為記錄的唯一鍵，同時保證同一個文件的內(nèi)容被寫入同一個表。例如

index_90.txt 被寫入 數(shù)據(jù)庫database_9，table_0 ，

index_67.txt被寫入數(shù)據(jù)庫 database_6，table_7。

這樣每個表都是有序的。整體有序通過數(shù)據(jù)庫后綴+表名后綴實(shí)現(xiàn)。

如何更快地讀取文件

10G的文件顯然不能一次性讀取到內(nèi)存中，場景的文件讀取包括

Files.readAllBytes一次性加載內(nèi)內(nèi)存

FileReader+ BufferedReader 逐行讀取

File+ BufferedReader

Scanner逐行讀取

Java NIO FileChannel緩沖區(qū)方式讀取

在MAC上，使用這幾種方式的讀取3.4G大小文件的性能對比

詳細(xì)的評測內(nèi)容請參考：讀取文件性能比較 ：https://zhuanlan.zhihu.com/p/142029812

由此可見 使用JavaNIO FileChannnel明顯更優(yōu)，但是FileChannel的方式是先讀取固定大小緩沖區(qū)，不支持按行讀取。也無法保證緩沖區(qū)正好包括整數(shù)行數(shù)據(jù)。如果緩沖區(qū)最后一個字節(jié)正好卡在一行數(shù)據(jù)中間，還需要額外配合讀取下一批數(shù)據(jù)。如何把緩沖區(qū)變?yōu)橐恍行袛?shù)據(jù)，比較困難。

Filefile=newFile("/xxx.zip");
FileInputStreamfileInputStream=null;
longnow=System.currentTimeMillis();
try{
fileInputStream=newFileInputStream(file);
FileChannelfileChannel=fileInputStream.getChannel();

intcapacity=1*1024*1024;//1M
ByteBufferbyteBuffer=ByteBuffer.allocate(capacity);
StringBufferbuffer=newStringBuffer();
intsize=0;
while(fileChannel.read(byteBuffer)!=-1){
//讀取后，將位置置為0，將limit置為容量,以備下次讀入到字節(jié)緩沖中，從0開始存儲
byteBuffer.clear();
byte[]bytes=byteBuffer.array();
size+=bytes.length;
}
System.out.println("filesize:"+size);
}catch(FileNotFoundExceptione){
e.printStackTrace();
}catch(IOExceptione){
e.printStackTrace();
}finally{
//TODOclose資源.
}
System.out.println("Time:"+(System.currentTimeMillis()-now));

JavaNIO 是基于緩沖區(qū)的，ByteBuffer可轉(zhuǎn)為byte數(shù)組，需要轉(zhuǎn)為字符串，并且要處理按行截斷。

但是BufferedReader JavaIO方式讀取可以天然支持按行截斷，況且性能還不錯 ，10G文件，大致只需要讀取30s，由于導(dǎo)入的整體瓶頸在寫入部分，即便30s讀取完，也不會影響整體性能。所以文件讀取使用BufferedReader 逐行讀取。即方案3

如果協(xié)調(diào)讀文件任務(wù)和寫數(shù)據(jù)庫任務(wù)

這塊比較混亂，請耐心看完。

100個讀取任務(wù)，每個任務(wù)讀取一批數(shù)據(jù)，立即寫入數(shù)據(jù)庫是否可以呢？前面提到了由于數(shù)據(jù)庫并發(fā)寫入的瓶頸，無法滿足1個庫同時并發(fā)大批量寫入10個表，所以100個任務(wù)同時寫入數(shù)據(jù)庫，勢必導(dǎo)致每個庫同時有10個表同時在順序?qū)?，這加劇了磁盤的并發(fā)寫壓力。為盡可能提高速度，減少磁盤并發(fā)寫入帶來的性能下降， 需要一部分寫入任務(wù)被暫停的。那么讀取任務(wù)需要限制并發(fā)度嗎？不需要。

假設(shè)寫入任務(wù)和讀取任務(wù)合并，會影響讀取任務(wù)并發(fā)度。初步計(jì)劃讀取任務(wù)和寫入任務(wù)各自處理，誰也不耽誤誰。但實(shí)際設(shè)計(jì)時發(fā)現(xiàn)這個方案較為困難。

最初的設(shè)想是引入Kafka，即100個讀取任務(wù)把數(shù)據(jù)投遞到Kafka，由寫入任務(wù)消費(fèi)kafka寫入DB。100個讀取任務(wù)把消息投遞到Kafka，此時順序就被打亂了，如何保證有序?qū)懭霐?shù)據(jù)庫呢？我想到可以使用Kafka partition路由，即讀取任務(wù)id把同一任務(wù)的消息都路由到同一個partition，保證每個partition內(nèi)有序消費(fèi)。

要準(zhǔn)備多少個分片呢？100個很明顯太多，如果partition小于100個，例如10個。那么勢必存在多個任務(wù)的消息混合在一起。如果同一個庫的多個表在一個Kafka partition，且這個數(shù)據(jù)庫只支持單表批量寫入，不支持并發(fā)寫多個表。這個庫多個表的消息混在一個分片中，由于并發(fā)度的限制，不支持寫入的表對應(yīng)的消息只能被丟棄。所以這個方案既復(fù)雜，又難以實(shí)現(xiàn)。

所以最終放棄了Kafka方案，也暫時放棄了將讀取和寫入任務(wù)分離的方案。

最終方案簡化為 讀取任務(wù)讀一批數(shù)據(jù)，寫入一批。即任務(wù)既負(fù)責(zé)讀文件、又負(fù)責(zé)插入數(shù)據(jù)庫。

如何保證任務(wù)的可靠性

如果讀取任務(wù)進(jìn)行到一半，宕機(jī)或者服務(wù)發(fā)布如何處理呢？或者數(shù)據(jù)庫故障，一直寫入失敗，任務(wù)被暫時終止，如何保證任務(wù)再次拉起時，再斷點(diǎn)處繼續(xù)處理，不會存在重復(fù)寫入呢？

剛才我們提到可以 為每一個記錄設(shè)置一個主鍵Id，即 文件后綴index+文件所在行號?？梢酝ㄟ^主鍵id的方式保證寫入的冪等。

文件所在的行號，最大值 大致為 10G/1k = 10M，即10000000。拼接最大的后綴99。最大的id為990000000。

所以也無需數(shù)據(jù)庫自增主鍵ID，可以在批量插入時指定主鍵ID。

如果另一個任務(wù)也需要導(dǎo)入數(shù)據(jù)庫呢？如何實(shí)現(xiàn)主鍵ID隔離，所以主鍵ID還是需要拼接taskId。例如{taskId}{fileIndex}{fileRowNumber} 轉(zhuǎn)化為Long類型。如果taskId較大，拼接后的數(shù)值過大，轉(zhuǎn)化為Long類型可能出錯。

最重要的是，如果有的任務(wù)寫入1kw，有的其他任務(wù)寫入100W，使用Long類型無法獲知每個占位符的長度，存在沖突的可能性。而如果拼接字符串{taskId}_{fileIndex}_{fileRowNumber} ，新增唯一索引，會導(dǎo)致插入性能更差,無法滿足最快導(dǎo)入數(shù)據(jù)的訴求。所以需要想另一個方案。

可以考慮使用Redis記錄當(dāng)前任務(wù)的進(jìn)度。例如Redis記錄task的進(jìn)度，批量寫入數(shù)據(jù)庫成功后，更新 task進(jìn)度。

INCRBYKEY_NAMEINCR_AMOUNT

指定當(dāng)前進(jìn)度增加100，例如 incrby task_offset_{taskId} 100。如果出現(xiàn)批量插入失敗的，則重試插入。多次失敗，則單個插入，單個更新redis。要確保Redis更新成功，可以在Redis更新時 也加上重試。

如果還不放心Redis進(jìn)度和數(shù)據(jù)庫更新的一致性，可以考慮 消費(fèi) 數(shù)據(jù)庫binlog，每一條記錄新增則redis +1 。

如果任務(wù)出現(xiàn)中斷，則首先查詢?nèi)蝿?wù)的offset。然后讀取文件到指定的offset繼續(xù) 處理。

如何協(xié)調(diào)讀取任務(wù)的并發(fā)度

前面提到了為了避免單個庫插入表的并發(fā)度過高，影響數(shù)據(jù)庫性能?？梢钥紤]限制并發(fā)度。如何做到呢？

既然讀取任務(wù)和寫入任務(wù)合并一起。那么就需要同時限制讀取任務(wù)。即每次只挑選一批讀取寫入任務(wù)執(zhí)行。

在此之前需要設(shè)計(jì)一下任務(wù)表的存儲模型。

bizId為了以后支持別的產(chǎn)品線，預(yù)設(shè)字段。默認(rèn)為1，代表當(dāng)前業(yè)務(wù)線。

datbaseIndex 代表被分配的數(shù)據(jù)庫后綴

tableIndex 代表被分配的表名后綴

parentTaskId，即總的任務(wù)id

offset可以用來記錄當(dāng)前任務(wù)的進(jìn)度

10億條數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫，切分為100個任務(wù)后，會新增100個taskId，分別處理一部分?jǐn)?shù)據(jù)，即一個10G文件。

status 狀態(tài)用來區(qū)分當(dāng)前任務(wù)是否在執(zhí)行，執(zhí)行完成。

如何把任務(wù)分配給每一個節(jié)點(diǎn)，可以考慮搶占方式。每個任務(wù)節(jié)點(diǎn)都需要搶占任務(wù)，每個節(jié)點(diǎn)同時只能搶占1個任務(wù)。具體如何實(shí)現(xiàn)呢？可以考慮 每個節(jié)點(diǎn)都啟動一個定時任務(wù)，定期掃表，掃到待執(zhí)行子任務(wù)，嘗試執(zhí)行該任務(wù)。

如何控制并發(fā)呢？可以使用redission的信號量。key為數(shù)據(jù)庫id、

RedissonClientredissonClient=Redisson.create(config);
RSemaphorerSemaphore=redissonClient.getSemaphore("semaphore");
//設(shè)置1個并發(fā)度
rSemaphore.trySetPermits(1);
rSemaphore.tryAcquire();//申請加鎖,非阻塞。

由任務(wù)負(fù)責(zé)定期輪訓(xùn)，搶到名額后，就開始執(zhí)行任務(wù)。將該任務(wù)狀態(tài)置為Process，任務(wù)完成后或失敗后，釋放信號量。

TaskTassk任務(wù)表Redisalt爭搶信號量成功定時輪訓(xùn)任務(wù)開始查詢待執(zhí)行的任務(wù)循環(huán)爭搶信號量修改任務(wù)狀態(tài)執(zhí)行中，設(shè)置開始時間時間查詢當(dāng)前進(jìn)度讀取文件到從當(dāng)前進(jìn)度讀取文件，批量導(dǎo)入數(shù)據(jù)庫更新進(jìn)度執(zhí)行完成，釋放信號量申請下一個任務(wù)的信號量TaskTassk任務(wù)表Redis

但是使用信號量限流有個問題，如果任務(wù)忘記釋放信號量，或者進(jìn)程Crash無法釋放信號量，如何處理呢？可以考慮給信號量增加一個超時時間。那么如果任務(wù)執(zhí)行過長，導(dǎo)致提前釋放信號量，另一個客戶單爭搶到信號量，導(dǎo)致 兩個客戶端同時寫一個任務(wù)如何處理呢？

what，明明是將10億數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫，怎么變成分布式鎖超時的類似問題?

實(shí)際上 Redisson的信號量并沒有很好的辦法解決信號量超時問題，正常思維：如果任務(wù)執(zhí)行過長，導(dǎo)致信號量被釋放，解決這個問題只需要續(xù)約就可以了，任務(wù)在執(zhí)行中，只要發(fā)現(xiàn)快信號量過期了，就續(xù)約一段時間，始終保持信號量不過期。但是 Redission并沒有提供信號量續(xù)約的能力，怎么辦？

不妨換個思路，我們一直在嘗試讓多個節(jié)點(diǎn)爭搶信號量，進(jìn)而限制并發(fā)度?？梢栽囋囘x取一個主節(jié)點(diǎn)，通過主節(jié)點(diǎn)輪訓(xùn)任務(wù)表。分三種情況，

情況1 當(dāng)前執(zhí)行中數(shù)量小于并發(fā)度。

則選取id最小的待執(zhí)行任務(wù)，狀態(tài)置為進(jìn)行中，通知發(fā)布消息。

消費(fèi)到消息的進(jìn)程，申請分布式鎖，開始處理任務(wù)。處理完成釋放鎖。借助于Redission分布式鎖續(xù)約，保證任務(wù)完成前，鎖不會超時。

情況2 當(dāng)前執(zhí)行中數(shù)量等于并發(fā)度。

主節(jié)點(diǎn)嘗試 get 進(jìn)行中任務(wù)是否有鎖。

如果沒有鎖，說明有任務(wù)執(zhí)行失敗，此時應(yīng)該重新發(fā)布任務(wù)。如果有鎖，說明有任務(wù)正在執(zhí)行中。

情況3 當(dāng)前執(zhí)行中數(shù)量大于并發(fā)度

上報異常情況，報警，人工介入

使用主節(jié)點(diǎn)輪訓(xùn)任務(wù)，可以減少任務(wù)的爭搶，通過kafka發(fā)布消息，接收到消息的進(jìn)程處理任務(wù)。為了保證更多的節(jié)點(diǎn)參與消費(fèi)，可以考慮增加Kafka分片數(shù)。雖然每個節(jié)點(diǎn)可能同時處理多個任務(wù)，但是不會影響性能，因?yàn)樾阅芷款i在數(shù)據(jù)庫。

那么主節(jié)點(diǎn)應(yīng)該如何選取呢？可以通過Zookeeper+curator 選取主節(jié)點(diǎn)?？煽啃员容^高。

10億條數(shù)據(jù)插入數(shù)據(jù)庫的時間影響因素非常多。包括數(shù)據(jù)庫磁盤類型、性能。數(shù)據(jù)庫分庫數(shù)量如果能切分1000個庫當(dāng)然性能更快，要根據(jù)線上實(shí)際情況決策分庫和分表數(shù)量，這極大程度決定了寫入的速率。最后數(shù)據(jù)庫批量插入的閾值也不是一成不變的，需要不斷測試調(diào)整，以求得最佳的性能?？梢园凑?00,1000,10000等不斷嘗試批量插入的最佳閾值。

最后總結(jié)一下幾點(diǎn)重要的

總結(jié)

要首先確認(rèn)約束條件，才能設(shè)計(jì)方案。確定面試官主要想問的方向，例如1T文件如何切割為小文件，雖是難點(diǎn)，然而可能不是面試官想考察的問題。

從數(shù)據(jù)規(guī)模看，需要分庫分表，大致確定分表的規(guī)模。

從單庫的寫入瓶頸分析，判斷需要進(jìn)行分庫。

考慮到磁盤對并發(fā)寫的支持力度不同，同一個庫多個表寫入的并發(fā)需要限制。并且支持動態(tài)調(diào)整，方便在線上環(huán)境調(diào)試出最優(yōu)值。

MySQL innodb、myisam 存儲引擎對寫入性能支持不同，也要在線上對比驗(yàn)證

數(shù)據(jù)庫批量插入的最佳閾值需要反復(fù)測試得出。

由于存在并發(fā)度限制，所以基于Kafka分離讀取任務(wù)和寫入任務(wù)比較困難。所以合并讀取任務(wù)和寫入任務(wù)。

需要Redis記錄任務(wù)執(zhí)行的進(jìn)度。任務(wù)失敗后，重新導(dǎo)入時，記錄進(jìn)度，可避免數(shù)據(jù)重復(fù)問題。

分布式任務(wù)的協(xié)調(diào)工作是難點(diǎn)，使用Redission信號量無法解決超時續(xù)約問題?？梢杂芍鞴?jié)點(diǎn)分配任務(wù)+分布式鎖保證任務(wù)排他寫入。主節(jié)點(diǎn)使用Zookeeper+Curator選取。

審核編輯：劉清