背景

最近需要做表数据的更新，从几个表中抽取数据插入到新表，大概就是：

insert into table_a(c1,c2 ...) 
 select x1,x2.... 
        from table_b join table_c on ...
        join table_d on ...

因为表table_b和table_c比较大(大约400W数据)， 这个语句在我本地机器（i7双核+SSD）非常非常慢， 执行了快一个小时没有完成，所以考虑看有没有其他更快一点的办法。

思路

首先排查了几个join表都是走的主键，所以不存在查询慢的问题，所以速度应该卡在insert阶段。 查看官方文档上优化insert的思路： insert-optimization optimizing-innodb-bulk-data-loading 文章里提了好多优化的方案，对比分析，下面这句对我的场景起作用：

When loading a table from a text file, use LOAD DATA. This is usually 20 times faster than using INSERT statements.

意思是从文件中导入数据到表，比insert要快20倍， emm这有待考证， 但快应该是快一些的。

导出数据

明确思路后，首先要做的就是导出数据， 如果是单表导出可以使用mysqldump，但这里要join导出，所以用到 select ... into命令：

SELECT ... INTO var_list selects column values and stores them into variables.
SELECT ... INTO OUTFILE writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format.
SELECT ... INTO DUMPFILE writes a single row to a file without any formatting.

导出多行数据到文件是用SELECT ... INTO OUTFILE. 导出的文件是在服务器端的，不能导出到本地（像阿里云数据库RDS就不支持，账号没有写文件的权限）。 文件名不能是已经存在，并且如果设置了变量secure_file_priv，那文件也只能导出到这个目录下面。 示例：

select ......
from table_b b
         join table_c c on ...
         join table_d d on ....
into outfile '/var/lib/mysql-files/xxx.txt';

导出完成之后，就可以看到文件/var/lib/mysql-files/xxx.txt了， emmm我这里有800MB。

导入数据

导入数据使用命令load data，这个命令是比较复杂的，这是一个通用命令，只要数据符合格式要求就能导入，可以导入csv等等， 看下它的定义：

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

简单的例子：

load data infile '/var/lib/mysql-files/xxx.txt' ignore
    into table table_a character set utf8mb4
    (c1,c2,....);

注意这里列名要对应起来， 字符集使用utf8mb4， 忽略重复的数据。

下面简单介绍一些参数： LOW_PRIORITY 没有客户端读取时才执行命令，只对锁表的引擎有效（MyISAM, MEMORY, MERGE等）。

CONCURRENT 是否可以并发插入，也是影响MyISAM， 会降低插入效率。

LOCAL 表示数据文件是在本地，命令执行时会先上传本地文件到服务器的临时目录，然后再执行插入操作。

REPLACE | IGNORE 遇到唯一键重复的记录时怎么处理， replace是替换，ignore是忽略

{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] 指定数据字段格式: TERMINATED BY 指示字段以什么符号结束， 默认是\t ENCLOSED BY 指示字段以什么包起来，默认是空 '' ESCAPED BY 指示转义字符，默认是 \\

LINES [STARTING BY 'string'] [TERMINATED BY 'string'] 指定数据行格式： STARTING BY 指示行以什么开头，默认是空 TERMINATED BY 指示行为什么结尾， 默认是\n

IGNORE number {LINES | ROWS} 跳过文件头N行

(col_name_or_user_var [, col_name_or_user_var] ...) 列名或者用户变量名

SET col_name={expr | DEFAULT}, [, col_name={expr | DEFAULT}] ... 导入一行之后进行更新

结果

实际测试，我的表规模为 400W JOIN 400W JOIN 300， 均是主键连接， 直接用insert方式执行了50多分钟还没有结束，没有再继续测试， 使用先导出再导入的方式，执行时间为 2m(导出）+ 18m（导入）。