mysql导入千万级数据实操

前言

目标快速模拟数据一亿条, 存储过程效率太低, 找到个load data的方法, 从本地文件读数据插入到库表中, 正文如下

正文

切换引擎

查询引擎类型

SHOW CREATE TABLE igs_sm_interface_access_log;

查询结果

CREATE TABLE `igs_sm_interface_access_log` (
  `interface_request_seq` varchar(100) NOT NULL,
  `user_id` varchar(50) DEFAULT NULL,
  `interface_access_func_name_cn` varchar(100) DEFAULT NULL,
  `interface_access_method_name_cn` varchar(100) DEFAULT NULL,
  `interface_access_method_type_name_en` varchar(100) DEFAULT NULL,
  `interface_response_status_cd` varchar(10) DEFAULT NULL,
  `begin_datetime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `end_datetime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`interface_request_seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 将InnoDB切换为

ALTER TABLE igs_sm_interface_access_log ENGINE = MyISAM

查看切换结果

CREATE TABLE `igs_sm_interface_access_log` (
  `interface_request_seq` varchar(100) NOT NULL,
  `user_id` varchar(50) DEFAULT NULL,
  `interface_access_func_name_cn` varchar(100) DEFAULT NULL,
  `interface_access_method_name_cn` varchar(100) DEFAULT NULL,
  `interface_access_method_type_name_en` varchar(100) DEFAULT NULL,
  `interface_response_status_cd` varchar(10) DEFAULT NULL,
  `begin_datetime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `end_datetime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`interface_request_seq`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

xxx操作

禁用

ALTER TABLE igs_sm_interface_access_log DISABLE KEYS 

查看执行文件权限

SHOW VARIABLES LIKE '%local%'

 修改执行文件权限

SET GLOBAL local_infile=1;-- 1 ON 0 OFF

向文件中插入记录

public static void main(String[] args) {
        //procedure_partition_test2_188||100188||test_模块||test_模块_方法||SEARCH||SUCCESS||2020-10-27 19:38:54||2020-10-27 19:38:54
        String sdf = "yyyy-MM-dd HH:mm:ss";
        Date date = new Date();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        SimpleDateFormat dateFormat = new SimpleDateFormat(sdf);
        File file = new File("C:\Users\Administrator\Desktop\load_data2.txt");
        String center = "||100188||test_模块||test_模块_方法||SEARCH||SUCCESS||";
        long start = System.currentTimeMillis();
        System.out.println("start:[" + start + "]");
        try {
            PrintWriter pfp = new PrintWriter(file, "UTF-8");
            for (int j = 0; j < 30; j++) {
                String id_prefix = "procedure_partition_test" + j + "_";
                calendar.add(Calendar.DAY_OF_MONTH, -1);
                Date time = calendar.getTime();
                String yesterday = dateFormat.format(time);
                String start_datetime = yesterday;
                String end_datetime = yesterday;
                for (int i = 0; i < 300000; i++) {
                    StringBuffer sb = new StringBuffer();
                    sb.append(id_prefix).append(i).append(center).append(start_datetime).append("||").append(end_datetime);
                    pfp.print(sb.toString() + "
");
                }
            }
            pfp.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        long end = System.currentTimeMillis();
        System.out.println("end:[" + end + "]");
        System.out.println("耗时:[" + (end - start) / 1000 + "s]");

    }

load data 执行sql

load data local infile 'C:\Users\Administrator\Desktop\load_data2.txt' into table igs_sm_interface_access_log
CHARACTER SET utf8 -- 可选，避免中文乱码问题
FIELDS TERMINATED BY '||' -- 字段分隔符，每个字段(列)以什么字符分隔，默认是 	
    OPTIONALLY ENCLOSED BY '' -- 文本限定符，每个字段被什么字符包围，默认是空字符
    ESCAPED BY '\' -- 转义符，默认是 
LINES TERMINATED BY '
' -- 记录分隔符，如字段本身也含
，那么应先去除，否则load data 会误将其视作另一行记录进行导入
(
    interface_request_seq,
    user_id,
    interface_access_func_name_cn,
    interface_access_method_name_cn,
    interface_access_method_type_name_en,
    interface_response_status_cd,
    begin_datetime,
    end_datetime
) -- 每一行文本按顺序对应的表字段，建议不要省略

本地执行结果: 测试数据是900万条, 855秒, 文件大小1.11G, 亿级的有时间再测吧...

换回测试环境库执行

最后记得把表的引擎切换回InnoDB, 启用keys, 执行本地文件权限

ALTER TABLE igs_sm_interface_access_log ENGINE = InnoDB
ALTER TABLE igs_sm_interface_access_log ENABLE KEYS
SET GLOBAL local_infile=0

感谢

java实现写大量数据到文件中

MySQL8.0加载文件内容报错： ERROR 1148: The used command is not allowed with this MySQL version

Mysql修改数据库引擎类型

MySQL load data 快速导入大批量数据

mysql快速保存插入大量数据一些方法总结

mysql应用之通过存储过程方式批量插入数据