• 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应用之通过存储过程方式批量插入数据

  • 相关阅读:
    H264 RTP封包原理(转载)
    FFmpeg的H264编码有内存泄漏吗??!!!
    最近在研究FFmpeg编解码
    罗一迦
    健康导报
    Vivado生成edf文件
    VIVADO生成MCS
    网络变压器
    Zynq 在Ubuntu上搭建编译环境
    [转]TimeQuest之delay_fall clock_fall傻傻分不清楚
  • 原文地址:https://www.cnblogs.com/yadongliang/p/13893581.html
一二三 - 开发者的网上家园