需求

要是 B站专栏支持 markdown 语法就好了。。

这两天一个以前的项目有了新需求，需要给项目的数据库进行一下改造，其中主要涉及到超大数据量的一次性更新，之前这个项目也涉及了超大量数据的插入，正好统一记录一下

需求是需要将服务器一个目录下的大约八千万个文件的路径导入到数据库里，文件名就是这个文件在数据库里的一个唯一标识字段。（数据库里已经有了这些文件的信息，但是没有路径）

思路

怎么搞呢？我最开始打算用的办法是先新建一个专门用来存路径的表，先写一个文件搜索脚本把文件路径信息序列化成json文件，在用 mybatis <foreach> 标签进行批量导入，后来发现这么大数据量用 mybatis 这么搞太不现实，又慢又有风险。
遂开始思考，一般情况下 MySQL 会涉及到超大规模数据导入的，那就是数据库的备份与恢复。
数据库的恢复会用到 source 命令，比如

这里用的 sql 文件一般是使用 dump 工具备份的数据库文件，我找来了一个这样的 sql 用文本编辑器打开看了看内部的结构

好家伙，这不就是一个超大量的 sql 脚本吗，先删表然后重新 create ，再锁表，然后就是一排排的

一个 insert 大约是插入一千条左右的数据。
原来 source 就是一个本地执行 sql 脚本的命令，现在知道了 sql 结构，那么我们理论上就能自己写一个脚本，把这样的 sql 文件生成出来，用服务器跑 source 命令，实现超大数据的导入。

本地文件路径序列化 json

说干就干，我们用 C# 写一个搜索程序用来生成 json（其实当时是用 Java 写的，这两天用 C# 重写了遍）

第一个需求，新表导入大量数据

然后再用这些 json 生成 sql

生成 sql 后直接在 MySQL 里用 source 执行就行了，时间还挺快的，用了一两个小时左右

新需求，单表大量更新

然后等一晚上就有新字段了。
接下来的重点是如何比较效率的去批量更新这个字段了。经过本地测试，source 命令是支持执行 update 语句的，正好之前扫描文件用的 json 文件还在，那就直接生成一个 sql 里面放一行行的 update 语句就好了。
于是初步用脚本生成了一个下面结构的 sql

本来信心满满，在本地用数据量十万条的测试数据库进行了测试，但是用时让我傻眼了

一共十万条数据，居然用了小三分钟才弄完？太慢了。
于是开始研究 MySQL 怎么批量更新数据，
replace into 和 insert into...on duplicate key update 都不合适，没法用，但是找到了一个鬼才写法

这样就实现了批量的更新，试着生成用这个语法的 sql

用这样生成的 sql 去测试

只用了十多秒就完事了，挺不错，这就把脚本搬到服务器上开导，顺带写个博客。

简单猜想单条 update 为什么这么慢

其实从实际存储结构去分析，update 和 insert 不同，批量的 insert 可以在提前划好的空间一大块一大块的填数据，所以一次写多个比单条执行快，但是看上面两条 update 语句其实都是在一条条的导入数据，甚至批量的写法还要加一个条件判断的过程，应该执行更慢才对，为什么会比批量单条快这么多呢？
我的猜测是可能跟日志打印有关，我们都知道，在 Java 中

是非常慢的，因为他涉及到阻塞 io 打印，还有同步锁，
MySQL在执行 source 时的日志输出很可能也是这个情况，我们再看一眼第一个截图

可以看到，每行数据更新完成后他都会打印一行日志，日志记录的语句执行时间只有 0.00 sec
可能更新用的时间还没他打印日志用的时间长，就这样大量的阻塞io拖慢了语句的执行速度，而批量更新的写法大大减少了日志的打印量所以让执行时间减少，总结就是并不是批量更新更快，而是单条执行被拖慢了。
以上就是本次的项目记录。