大資料匯入之MySql設計之空間換時間的設計變更

kumfo發表於2019-02-16
大資料MySql

最近幾天一直在糾結於一個大資料批量匯入的問題，經過幾天思考，發現基於小資料情況，原本的資料結構設計是沒有問題的，但是在大量資料匯入，問題就很大了。我之前一直在強調“程式=資料結構+演算法”，但在這此卻鑽了牛角尖，最後去仔細看了之前別人設計的資料表才突然靈光一現，發現了mysql層面要以空間換時間的具體設計思路。

基於這種情況，先說一下經過這個過程後的感悟。

感悟

  1. 後續的程式邏輯如果出現演算法複雜的問題，不要延續之前的程式設計，同樣不要延續之前的資料表結構設計，要多方面思考。

  2. 隨著業務的變更，不僅僅是程式上的變更，還有資料結構上的變更，大量資料的處理方式與常規處理資料思維不太一樣。

產品需求

這次的具體問題，我先從產品需求說起，並從頭聊我踩過的坑。

  1. 使用者撒碼可以根據這個碼知道商品流向，系統有幾層代理商，在商品到達使用者手中之後，使用者可以根據掃碼查詢整個商品的流向，經過了哪些代理商手中；

  2. 代理商掃碼可以進行發貨，根據這個追溯碼標記商品發貨狀態，同時儲存代理商發貨狀態。

  3. 追溯碼有三個碼類，分為大碼、中碼、小碼，類似於一個大碼對應一箱貨物，一箇中碼對應一箱貨物中的一盒，一個小碼對應一盒貨物中的某一件具體貨物；也就是說大碼涵蓋中碼，中碼涵蓋小碼，需要通過上層的追溯碼找到整個關係鏈。

  4. 商戶根據需要，進行追溯碼匯入，一次性匯入的資料條數大約為10萬條左右，也可能更多，也可能更少。

踩坑

原本的資料表結構設計

    admin_id #商戶ID
    product_id #商品ID
    security_code #追溯碼
    code_type #追溯碼型別：1大碼，2中碼，3小碼
    parent_id #當前追溯碼的上級ID，如小碼對應的上級中碼ID
    top_id #頂級ID，對應的為大碼的ID，中碼和小碼都要村粗
    is_use #此追溯碼是否已使用
    is_sell_out #是否已經售出
    created_time #建立時間
    updated_time #更新時間
    is_deleted #是否刪除

原程式設計思想

整套資料表結構的設計邏輯是以 parent_id 來進行關係關聯，然後通過code_type來標明追溯碼的型別，在匯入的時候，先必須村粗父級，然後再儲存子級，層層遍歷匯入。

此設計產生的問題

由於必須層層遍歷匯入，就會造成匯入相當緩慢，必須先插入富級ID，然後再插入子級ID，因為子級ID關聯了父級ID。這樣也就不能採用mysql的

INSERT INTO table(field1,field2) VALUES(`a`, 1), (`b`, 1), (`c`, 1);

這種批量插入的方式進行插入，這樣的話，資料插入就會很緩慢。

經過我後面的思考，把最後一級，也就是小碼採用了批量插入，但是發現效率還是並不高，就算把小碼改為批量插入，一次性批量插入的資料也僅有20條左右，對效率提高並不大。

新表設計方式

新的設計，我把整個表拆分成了兩個表，一個表儲存關係鏈，一個表用來做追溯標記和查詢，大致如下：

關係表 

admin_id #商戶ID
product_id #商品ID
code_max # 大碼
code_middle #中碼
code_min #小碼

追溯碼標記表 

admin_id #商戶ID
product_id #商品ID
code #追溯碼，不管大碼中碼還是小碼都會建一條資料存放在此欄位
is_use # 是否使用
is_sell_out #是否出售
create_time #建立時間
update_time # 更新時間
is_delete #是否已刪除

新的邏輯說明

  1. 首先，在關係表裡儲存的是關係，大碼、中碼、小碼都完整的村粗，可以通過大碼ID查詢到下面的所有中碼、小碼；

  2. 追溯碼標記表，這個表專門管查詢和標記追溯碼狀態等。

  3. 新的設計方式，會增加資料表資料的管理成本，也會增加資料量大小。

  4. 新的資料表關係資料的資料結構類似於這樣子：

大碼 中碼 小碼
1 11 111
1 11 112
1 12 123
1 12 124
2 21 211
2 21 212
2 22 221
2 22 222

這個表結構的說明是避免對於關係鏈的資料邏輯不理解做的一個示例。

整個表經過這樣的重新設計和梳理了之後，每次同時插入兩個表，並且可以用MySQL的批量插入操作進行插入，插入速度具有非常大的提升。

資料重複對比

在這套產品設計之中，整套的追溯碼是不能重複的，經過了多方面思考，最後採用了in查詢機制，整套思路大致如下：

每次遍歷要匯入的資料中的100條，然後用mysql的in去查詢，如果取到資料，則遍歷資料標記哪一條重複。不斷的迴圈遍歷去取，然後標記輸出到CSV中，遍歷結束後，把標記重複的文件返回給使用者，讓使用者修改好後再進行上傳。

不過關於這個資料對比的，我沒有更好的思路了，測試了18W條匯入資料和資料表中的25W條資料對比，花的時間大約為13秒左右，就我的預估對比次數應該為18W*25W，如果誰在這方面有更好的資料對比演算法，請留言告訴我，不勝感激。

僅以此文紀念自己踩過的坑，也同樣希望後人看到這篇文章後能得到一些解決思路。

相關文章