需求

要是 B站專欄支持 markdown 語法就好了。。

這兩天一個以前的項目有了新需求，需要給項目的數(shù)據庫進行一下改造，其中主要涉及到超大數(shù)據量的一次性更新，之前這個項目也涉及了超大量數(shù)據的插入，正好統(tǒng)一記錄一下

需求是需要將服務器一個目錄下的大約八千萬個文件的路徑導入到數(shù)據庫里，文件名就是這個文件在數(shù)據庫里的一個唯一標識字段。（數(shù)據庫里已經有了這些文件的信息，但是沒有路徑）

思路

怎么搞呢？我最開始打算用的辦法是先新建一個專門用來存路徑的表，先寫一個文件搜索腳本把文件路徑信息序列化成json文件，在用 mybatis <foreach> 標簽進行批量導入，后來發(fā)現(xiàn)這么大數(shù)據量用 mybatis 這么搞太不現(xiàn)實，又慢又有風險。
遂開始思考，一般情況下 MySQL 會涉及到超大規(guī)模數(shù)據導入的，那就是數(shù)據庫的備份與恢復。
數(shù)據庫的恢復會用到 source 命令，比如

這里用的 sql 文件一般是使用 dump 工具備份的數(shù)據庫文件，我找來了一個這樣的 sql 用文本編輯器打開看了看內部的結構

好家伙，這不就是一個超大量的 sql 腳本嗎，先刪表然后重新 create ，再鎖表，然后就是一排排的

一個 insert 大約是插入一千條左右的數(shù)據。
原來 source 就是一個本地執(zhí)行 sql 腳本的命令，現(xiàn)在知道了 sql 結構，那么我們理論上就能自己寫一個腳本，把這樣的 sql 文件生成出來，用服務器跑 source 命令，實現(xiàn)超大數(shù)據的導入。

本地文件路徑序列化 json

說干就干，我們用 C# 寫一個搜索程序用來生成 json（其實當時是用 Java 寫的，這兩天用 C# 重寫了遍）

第一個需求，新表導入大量數(shù)據

然后再用這些 json 生成 sql

生成 sql 后直接在 MySQL 里用 source 執(zhí)行就行了，時間還挺快的，用了一兩個小時左右

新需求，單表大量更新

然后等一晚上就有新字段了。
接下來的重點是如何比較效率的去批量更新這個字段了。經過本地測試，source 命令是支持執(zhí)行 update 語句的，正好之前掃描文件用的 json 文件還在，那就直接生成一個 sql 里面放一行行的 update 語句就好了。
于是初步用腳本生成了一個下面結構的 sql

本來信心滿滿，在本地用數(shù)據量十萬條的測試數(shù)據庫進行了測試，但是用時讓我傻眼了

一共十萬條數(shù)據，居然用了小三分鐘才弄完？太慢了。
于是開始研究 MySQL 怎么批量更新數(shù)據，
replace into 和 insert into...on duplicate key update 都不合適，沒法用，但是找到了一個鬼才寫法

這樣就實現(xiàn)了批量的更新，試著生成用這個語法的 sql

用這樣生成的 sql 去測試

只用了十多秒就完事了，挺不錯，這就把腳本搬到服務器上開導，順帶寫個博客。

簡單猜想單條 update 為什么這么慢

其實從實際存儲結構去分析，update 和 insert 不同，批量的 insert 可以在提前劃好的空間一大塊一大塊的填數(shù)據，所以一次寫多個比單條執(zhí)行快，但是看上面兩條 update 語句其實都是在一條條的導入數(shù)據，甚至批量的寫法還要加一個條件判斷的過程，應該執(zhí)行更慢才對，為什么會比批量單條快這么多呢？
我的猜測是可能跟日志打印有關，我們都知道，在 Java 中

是非常慢的，因為他涉及到阻塞 io 打印，還有同步鎖，
MySQL在執(zhí)行 source 時的日志輸出很可能也是這個情況，我們再看一眼第一個截圖

可以看到，每行數(shù)據更新完成后他都會打印一行日志，日志記錄的語句執(zhí)行時間只有 0.00 sec
可能更新用的時間還沒他打印日志用的時間長，就這樣大量的阻塞io拖慢了語句的執(zhí)行速度，而批量更新的寫法大大減少了日志的打印量所以讓執(zhí)行時間減少，總結就是并不是批量更新更快，而是單條執(zhí)行被拖慢了。
以上就是本次的項目記錄。