前言
目标快速模拟数据一亿条, 存储过程效率太低, 找到个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