上周有个朋友半夜给我打电话，声音都快哭了。他手一抖，把生产数据库里一张核心表给删了，赶紧从备份里恢复，结果等了两个小时，进度条才动了三分之一。他急得满嘴起泡，问我有没有办法让MySQL还原快一点。我告诉他，这事儿我太熟了，以前我也被折磨过，后来摸索出一套方法，速度直接翻了十倍。今天就把这些技巧掰开揉碎了说给你听。
先说说为什么MySQL还原这么慢。很多人以为还原就是简单地把SQL文件扔进去跑，其实背后有大学问。默认情况下，MySQL每执行一条INSERT语句，都要写一次binlog、更新一次索引、检查一次约束，还要刷新一次磁盘缓存。这就像你搬家，每搬一个箱子都要停下来签个字、拍个照、检查一下箱子有没有破，然后再搬下一个。几千条几万条数据这么搞，不慢才怪。所以第一个技巧就是关闭那些不必要的“安检流程”。
具体怎么操作？还原之前，先跑几条命令：SET SQLLOGBIN=0; 这个能关掉二进制日志，不让每次插入都写日志文件；SET AUTOCOMMIT=0; 把自动提交关掉，然后手动在合适的时候提交一次；还有SET UNIQUECHECKS=0; 和SET FOREIGNKEYCHECKS=0; 这两个能关掉唯一性检查和外键约束检查。别担心数据会乱，还原完之后再重新打开就行。我测过，光这一套组合拳，还原时间就能缩短60%以上。
第二个技巧很多人想不到——用多线程导入。MySQL默认是单线程处理，就像你只用一个工人搬砖，再快也有限。但如果你能同时让四个、八个甚至十六个工人一起干，那效率完全不一样。具体做法是先把大的SQL文件拆成多个小文件，然后同时开几个终端窗口，每个窗口导入一个文件。拆文件可以用Linux的split命令，按行数或者按大小拆分都行。我一个朋友还原一个50G的数据库，用单线程跑了快三个小时，改成四线程后，四十分钟搞定。
不过这里有个坑要注意：拆分的时候要保证每个小文件不破坏事务完整性。比如一张表的数据必须在一个文件里，不能前半段在文件A，后半段在文件B。另外，如果你的服务器CPU核心数不多，线程数别开太猛，否则CPU跑满，反而拖慢整体速度。一般建议线程数不超过CPU核心数的一半。
第三个技巧是调大MySQL的临时参数。还原的时候，MySQL会频繁往磁盘写数据，如果缓冲区太小，每次写一点就要刷盘，效率极低。你可以在还原前临时调大几个参数：innodbbufferpoolsize、innodblogfilesize、bulkinsertbuffersize。拿innodbbufferpoolsize来说，如果你的服务器内存是32G，可以临时设到20G左右，这样大部分数据都能在内存里操作，少跟磁盘打交道。还有一个参数是maxallowed_packet，默认可能只有4M，如果遇到大字段或者长文本的INSERT，就会频繁报错断开连接，设成512M或者1G就能避免这个问题。
这些参数改完不用重启MySQL，用SET GLOBAL命令就行。不过千万别在生产环境里直接改，等还原完记得调回来，不然内存分配太多，其他进程可能被挤爆。我有个同事就是忘了调回来，结果第二天应用服务器内存告警，排查了半天才找到原因。
第四个技巧有点反直觉——先不要建索引。很多人还原数据前，习惯先把表结构建好，包括索引、主键、外键、字典约束，然后才开始导数据。但你想啊，每插入一条数据，MySQL就要去更新一次索引，就像你每往书架上放一本书，就要重新整理一遍索引标签。几百万本书放完，光整理标签就花了大半天。正确的做法是：先只用最基本的CREATE TABLE语句建表，连主键都别设，数据导入完成之后，再用ALTER TABLE一次性把索引和约束加上。
我做过一个实验，一张表有500万条数据，五个索引。先建索引再导数据，耗时47分钟；先导数据再建索引，总共只用了8分钟。差距就是这么明显。当然，如果数据量很小，比如几千条，差别不大，但百万级以上，这个技巧能省下你喝几杯咖啡的时间。
一个技巧，也是最容易被忽视的——压缩传输。很多人习惯用mysqldump生成SQL文件，一个几十G的文本文件，直接通过网络拷贝到另一台机器再还原，光是传输就花了很长时间。其实你可以用gzip或者pigz（多线程压缩工具）压缩一下，压缩率通常在3到5倍，传输时间直接缩短到原来的三分之一甚至更少。更聪明的方法是直接用管道操作，压缩和还原同时进行，中间不用写临时文件。
比如你可以在源服务器上执行：mysqldump [参数] mysql [参数]。这样数据从源库流出来，一路压缩、传输、解压、还原，全程无缝衔接。如果你用的是多核服务器，用pigz代替gzip还能进一步提速，因为pigz能同时用多个CPU核心压缩和解压。
这些技巧单独用都能提速，但最牛的还是组合拳。我一般还原一个大型数据库时，会先做这几步：用mysqldump生成压缩备份，传到目标服务器；然后关闭binlog、自动提交、外键检查；临时调大缓冲区参数；把文件拆成四份；同时开四个窗口导入；导入完再一次性建索引和约束。这样一套下来，原本需要三个小时的还原，基本能控制在二十分钟左右。
当初我那个半夜打电话的朋友，按我说的操作了一遍，十分钟后兴奋地回电话说：“好了好了，数据全回来了！”所以别怕MySQL还原慢，问题往往出在默认配置上，稍微调整一下，就能让这个“蜗牛”变成“猎豹”。下次你再遇到数据库恢复的紧急情况，别慌，试试这些技巧，速度飙升十倍不是梦。
