记一次批量导入数据过慢的解决方案

问题

​ 在做统计工作时，由于需要大量的基础数据，所以生成了一份测试数据。但是在实际执行数据脚本时，发现执行速度实在是太慢了。3000 条数据跑了5分钟还没有结束。这样下去感觉下班之前数据都导不完。于是在一通百度下，整理出了如下的解决方式。

当我们在数据库中添加一条记录的过程 各方的耗时是这样的

• 链接耗时 （30%）
• 发送 query 到服务器 （20%）
• 解析 query （20%）
• 插入操作 （10% * 词条数目）
• 插入 index （10% * Index的数目）
• 关闭链接 （10%）

单值插入 最慢

INSERT INTO 【TABLE_NAME】 VALUES() 

多值插入 较快

增加SQL的长度

减少多次IO操作

INSERT INTO 【TABLE_NAME】 VALUES(),(),(),()

LOAD DATA LOCAL INFILE 方式 Mysql 官网文档地址

LOAD DATA LOCAL INFILE 【FILE_PATH】 INTO TABLE 【TABLE_NAME】

如果还不能满足的话 那么还有如下的地方可以优化

多线程插入

在同一个事务内提交大量 插入

数据库引擎使用 myisam 替代 innoDB

个人的解决方式（只适用于当下情况）附代码：

package utils;

import org.springframework.util.StringUtils;

import java.io.*;
import java.util.LinkedList;

/**
 * 适用于 Navicat 导出的脚本 在执行时过于缓慢的问题
 * 解决方案
 * <p>
 *     分割数据 同表的数据建立单个的文件 表名.sql
 *     获取所有的表结构 table.sql
 *     创建加载数据的脚本 load.sql
 *
 *     执行顺序 table.sql -> load.sql
 * </p>
 *
 * @author yang yang
 * @email m17610912950@163.com
 * @time 2019年08月19日 14:21
 **/
public class MysqlDataImportUtils {

    public static LinkedList<String> tableList = new LinkedList<String>();

    public static void main(String args[]) {
        String path = "C:\\Users\\Administrator\\Desktop\\g.sql";
        try {
            splitSqlFile(path);
            writeSqlFile();
            getTableFile(path);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 将SQL文件切割
     *
     * @param filePath
     * @return void
     * @date 2019-08-19
     * @author yang yang
     **/
    public static void splitSqlFile(String filePath) throws Exception {
        //创建输出流
        BufferedWriter out = null;
        //创建输入流 用于读取SQL文件
        FileReader reader = new FileReader(filePath);
        BufferedReader br = new BufferedReader(reader);
        try {
            String line;
            String tn = null;
            //当有下一行时 读取
            while ((line = br.readLine()) != null) {
                //当读取到的行的信息是 添加的记录时
                if (line.contains("INSERT") || line.contains("INTO") || line.contains("VALUES")) {
                    //获取表的名称
                    String tableName = line.substring(line.indexOf("`") + 1, line.lastIndexOf("`"));
                    //缓存中的表名为空 且获取到的表名称非空时 (第一次读取到添加的记录)
                    if (StringUtils.isEmpty(tn) && !StringUtils.isEmpty(tableName)) {
                        tn = tableName;
                        //创建文件
                        File writeName = new File(tn + ".sql");
                        // 创建新文件,有同名的文件的话直接覆盖
                        boolean newFile = writeName.createNewFile();
                        //创建写入流
                        FileWriter writer = new FileWriter(writeName);
                        out = new BufferedWriter(writer);
                        // 缓存中添加 表的名称 用于后续的处理【用于生成加载 添加数据的 脚本】
                        tableList.add(tn);
                    }
                    if (!StringUtils.isEmpty(tn) && !StringUtils.isEmpty(tableName)) {
                        //表名是否一致 如果一致 向文件中写入数据 【此时读取到 当前表的数据】
                        if (tn.equalsIgnoreCase(tableName)) {
                            String tableInfo = line.substring(line.indexOf("(") + 1, line.indexOf(")"));
                            String all = tableInfo.replaceAll(", ", "|");
                            all = all.replaceAll("'", "");
                            all = all.replaceAll("`", "");
                            out.write(all + ",\r\n");
                        }
                        //表名不一致时 释放输出流 【此时读取到 另外一个表的添加记录】
                        if (!tn.equalsIgnoreCase(tableName)) {
                            out.flush();
                            out.close();
                            out = null;
                            tn = null;
                        }
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            //关闭读入流
            reader.close();
            // 关闭输出流
            if (null != out) {
                out.flush();
                out.close();
            }
        }
    }

    /**
     * 获取SQL文件中所有 建表语句
     * @param filePath
     * @return void
     * @date 2019-08-19
     * @author yang yang
     **/
    public static void getTableFile(String filePath) throws Exception {
        try {
            //读取文件
            FileReader reader = new FileReader(filePath);
            BufferedReader br = new BufferedReader(reader);
            //用于写出 文件
            File writeName = new File("table.sql");
            writeName.createNewFile(); // 创建新文件,有同名的文件的话直接覆盖
            try (FileWriter writer = new FileWriter(writeName);
                 BufferedWriter out = new BufferedWriter(writer)
            ) {
                String line;
                while ((line = br.readLine()) != null) {
                    if (!(line.contains("INSERT") || line.contains("INTO") || line.contains("VALUES"))) {
                        out.write(line + "\r\n");
                    }
                }
                out.flush(); // 把缓存区内容压入文件
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 用于数据库加载分割后数据的脚本
     * @param
     * @return void
     * @date 2019-08-19
     * @author yang yang
     **/
    public static void writeSqlFile() throws Exception {
        try {
            // 相对路径，如果没有则要建立一个新的output.txt文件 【输出的脚本文件】
            File writeName = new File("load.sql");
            writeName.createNewFile(); // 创建新文件,有同名的文件的话直接覆盖
            try (FileWriter writer = new FileWriter(writeName);
                 BufferedWriter out = new BufferedWriter(writer)
            ) {
                for (String tableName : tableList) {
                    StringBuffer sb = new StringBuffer();
                    sb.append(" LOAD DATA LOCAL INFILE 'F:/workspace/dev/2.0.0/" + tableName + ".sql' INTO TABLE " + tableName + " ");
                    sb.append(" CHARACTER SET utf8 ");
                    sb.append(" FIELDS TERMINATED BY '|' ");
                    sb.append(" OPTIONALLY ENCLOSED BY '' ");
                    sb.append(" ESCAPED BY '\\\\' ");
                    sb.append(" LINES TERMINATED BY '\n'; ");
                    out.write(sb.toString() + "\r\n");
                }
                out.flush(); // 把缓存区内容压入文件
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}