提前准备
本次我们采用从文件导入数据到数据库中的方式,LOAD DATA INFILE,我们先在IDEA中生成要导入到mysql中去的数据
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\12192\Desktop\load_data.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() "n");
}
}
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]");
}可以看到我们往文件写入900w数据只花了4s,接着将文件中的数据写入到mysql中去
接着数据库打开本地数据加载
代码语言：txt复制set global local_infile=1;看看有没有打开成功
代码语言：txt复制show global variables like 'local_infile';再将文件中的数据导入的数据库中
代码语言：txt复制load data local infile 'C:\Users\12192\Desktop\load_data.txt' into table igs_sm_interface_access_log
CHARACTER SET utf8 -- 可选，避免中文乱码问题
FIELDS TERMINATED BY '||' -- 字段分隔符，每个字段(列)以什么字符分隔，默认是 t
OPTIONALLY ENCLOSED BY '' -- 文本限定符，每个字段被什么字符包围，默认是空字符
ESCAPED BY '\' -- 转义符，默认是
LINES TERMINATED BY 'n' -- 记录分隔符，如字段本身也含n，那么应先去除，否则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
) -- 每一行文本按顺序对应的表字段，建议不要省略
可以看到900w数据导入还是很快的,这种方式在导入大量数据时要比之前提到的分批导入还快,接下来我们来一起看看千万级数据查询所出现的问题,以及如何优化
近千万数据的导入花费时长深分页问题
话不多上,上图更直观,深分页问题故名思意,就是当数据多到一定程度时,常规的分页查询,越往后所需要的时间就越长
下面是数据量不同同,偏移量相同,以及数据量相同偏量不同的比较
数据量不同,偏移量相同
数据量相同,偏移量不同可以知道数据量和偏移量都会影响到mysql查询的速度
使用具体字段减少回表操作
回表操作就是当你sql写*号时,mysql会去查该表的元数据,将所有字段添加进去,可以看到单线程时差距0.02秒,但是当并发量上去之后,该操作还是能节省不少时间的
回表与不回表的对比添加索引,按需查找字段(效果明显)
索引字段与非索引字段查询对比接下来我们给user_id添加上索引试试,可以看到,添加上了索引之后查询时间有了很明显的优化
未添加索引
设置为索引之后使用游标查询(效果明显)
游标查询即,如果有某个字段是递增的的话,我们只需要维护查询结果的最后一条数据的id是多少,那么我们下次直接将该ID作为下一次查询的条件即可
为什么通过where筛选之后,再进行limit速度会快这么多???
想弄清这个问题,我们需要知道where和limit在mysql中是如何执行的,想弄明白这个,我么就需要知道mysql的service层和存储引擎层,话不多说上实例,有下面查询语句,他在mysql中是如何执行的呢?
代码语言：txt复制select * from table where key > 1 and key < 100 and orther_key != 520;
//key是二级索引字段(非主键的索引),orther为非索引字段- service层告诉存储引擎层:"菜贩子,你先找个鸡蛋我尝尝鲜(返回满足二级索引的第一条数据,非索引字段由service层判断),顺便给我讲讲这个鸡蛋的故事(进行回表操作,详细字段)"
- 存储引擎层找到了满足二级索引的鸡蛋后告诉service层:"客户你好,这是我们的尝鲜蛋(满足where中的二级索引),你先常常,这鸡蛋无菌,健康(告诉了service层记录中有哪些字段,也就是回表操作)"
- servece层:"你这鸡蛋不够圆啊(不满足我的非索引字段的条件,也就是orther != 520),这个我不要,你继续给我拿"
- 存储引擎层:"好好好,我再给你拿一个"
- 就这样,他们一来一回,终于service层买到了所有满足条件的鸡蛋
有人问,不是我寻思你这也没说limit啊,不急我们接着看,如果我的语句添加limit之后会是什么样子的呢?
代码语言：txt复制select * from table where key > 1 and key < 100 and orther_key != 520 limt 100000, 100;
//key是二级索引字段(非主键的索引),orther为非索引字段- service层告诉存储引擎层:"菜贩子,你先找个鸡蛋我尝尝鲜(返回满足二级索引的第一条数据,非索引字段由service层判断),顺便给我讲讲这个鸡蛋的故事(进行回表操作,注意:回表操作只会在满足条件的字段上进行!!!!!!!!!)"
- 存储引擎层找到了满足二级索引的鸡蛋后告诉service层:"客户你好,我找了一个,满足您条件的鸡蛋,但是你先别急,我先给你存着,满100000个之后再把后面满意的100个鸡蛋一次给你
- 就这样,limit一直维护,进行了1000000次回表操作之后,终于,又找了100个鸡蛋,给了service层
可以看到为什么这两种差距很大了吧,limit是把满足条件的数据积攒起来,并且这些数据也还执行了回表(如果查询字段为非索引的话),而where只需要找到满足条件的,再对需要的数据条数进行回表也就是直接limit 100,
PS:小白,有错误请多多指正,立马了解改正!!!!持续更新中.............
参考连接
mysql导入千万级数据实操 - 习惯沉淀 - 博客园 (cnblogs.com)
