记录一下一个业务问题，流程是这样的，我现在有一个定时任务，5分钟执行一次，更新车辆打卡的情况。现在有20俩车，每辆车都分配了路线，每条路线都有打卡点，每个打卡点分配了不同的时间段，也就是说，一条路线可能有几百个打卡点，这几百个打卡点中每一个都分配了时间段，有可能是1个时间段，比如8:00 - 10:00这个时间段，要去打卡。也有可能有的打卡点分配了几个时间段，比如上午两个时间段，下午两个时间段。这个时候要去判断今天的打卡情况，只能先获取路线的打卡点，然后再获取单个打卡点下面的时间段，再进行判断操作。但是问题来了，之前的写法for循环去判断只针对少数打卡点，比如一条路线也就5 - 10个打卡点这样。但是现在打卡点数量过多，路线增加，以及每个打卡点都有打卡时间段，for循环操作数据库判断无比的慢，以及非常的消耗资源内存等。。但是判断是否经过打卡点这种必须要在sql里面执行判断，之所有必须在sql判断，因为pgsql提供的ST_DWithin函数就是判断是否在缓冲区范围经过打卡点的。。所以离不开sql判断。下面代码是for循环的操作，先循环出单条路线的打卡点，然后再根据打卡点的ID用stream流匹配打卡点的时间段，再循环每个时间段到sql里面进行判断。
sql语句判断是这样的
既然我们离不开sql语句判断，那就只能按照老套路，加索引这些。但是加索引提升了一点查询速度，持续的查询非常的消耗内存，导致项目运行的时候内存持续的猛增。一天时间涨了2G多，这明显很离谱。。。即使GC了，内存也无法得到释放。
咨询了部门里的老大哥，我问他我这种打卡点判断，能不能不走sql，他说不行的，这种必须走sql的，必须走pg的ST_DWithin函数判断。他给了我一个解决方案，让我先获取出当天的轨迹数据，也就是car_trajectory_info物理表的当天数据，每次定时任务执行的时候，先查出当天的轨迹数据，然后创建临时表，把当天的轨迹数据放到临时表里面，然后把之前的sql判断语句只查临时表，比如创建了一个临时表car_trajectory_info_temp，把原来的sql语句的物理表car_trajectory_info改成car_trajectory_info_temp，执行判断结束后在把临时表删除掉。这样下来，就不会走物理表了，直接走临时表做查询（这里记得在创建临时表的时候加上索引）
具体实现临时表操作如下：
测试类
package com.yutu.garden.task;
import cn.hutool.core.collection.CollUtil;
import com.yutu.garden.entity.CarTrajectoryInfo;
import com.yutu.garden.mapper.gardens.CarTrajectoryInfoMapper;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import java.util.List;
import java.util.stream.Collectors;
@Component
@EnableScheduling
public class ScheduledTasks01 {
@Resource
private CarTrajectoryInfoMapper carTrajectoryInfoMapper;
// 第一个定时任务，每隔5秒执行一次
@Async("taskExecutor")
@Scheduled(cron = "*/10 * * * * *")
@Transactional
public void task() {
//先查询出当天的轨迹数据，按照条件吧，如果还有条件，可以更加详细到某些指定的数据，比如说按车牌号条件筛选
List<CarTrajectoryInfo> carTrajectoryInfoList = carTrajectoryInfoMapper.getToDayCarInfoList();
//测试 创建临时表
carTrajectoryInfoMapper.createTempTable();
System.out.println("临时表创建成功");
//递归向临时表中插入当天的数据（防止数据量过大引发IO异常）
add(carTrajectoryInfoList,0L,500L);
//查询临时表（在这里判断是否经过打卡点！）
CarTrajectoryInfo carTrajectoryInfo = carTrajectoryInfoMapper.selectTempTable(必要的参数，这里只是测试);
System.out.println("执行查询，如果为空，那就是没有经过打卡点，如果不为空那就是已经经过打卡点");
System.out.println(carTrajectoryInfo);
System.out.println("删除临时表");
carTrajectoryInfoMapper.dropTempTable();
}
//递归插入（防止数据量过大引发IO异常）
public void add (List<CarTrajectoryInfo> all,long start,long limit){
//截取 从start开始截取 截取limit条
List<CarTrajectoryInfo> collect = all.stream().skip(start).limit(limit).collect(Collectors.toList());
if(CollUtil.isEmpty(collect)){
return;
}
//批量插入数据的方法
//保存车辆轨迹数据
carTrajectoryInfoMapper.insertDataTempTable(collect);
//递归 每次插入limit条数据
add(all,start+limit,limit);
}
}
mapper层
package com.yutu.garden.mapper.gardens;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yutu.garden.entity.CarTrajectoryInfo;
import com.yutu.garden.vo.CarWarnInfoCqZtVo;
import com.yutu.garden.vo.CarWarnInfoZyInfoVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.time.LocalDate;
import java.util.Date;
import java.util.List;
@Mapper
public interface CarTrajectoryInfoMapper extends BaseMapper<CarTrajectoryInfo> {
//获取当天轨迹数据
List<CarTrajectoryInfo> getToDayCarInfoList();
//批量插入数据
void insertDataTempTable(@Param("carTrajectoryInfoList") List<CarTrajectoryInfo> carTrajectoryInfoList);
//创建临时表
void createTempTable();
//使用临时表执行判断
CarTrajectoryInfo selectTempTable(@Param("geom")String geom,
@Param("sRid")Integer sRid,
@Param("carNo")String carNo,
@Param("radius") Integer radius,
@Param("startTime") String startTime,
@Param("endTime") String endTime);
//删除临时表
void dropTempTable();
}
xml中sql语句
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yutu.garden.mapper.gardens.CarTrajectoryInfoMapper">
<select id="getToDayCarInfoList" resultType="com.yutu.garden.entity.CarTrajectoryInfo">
select * from car_trajectory_info where DATE(upload_time) = CURRENT_DATE
</select>
<sql id="createTempTableSQL">
CREATE TEMPORARY TABLE car_trajectory_info_temp (
id int8 NOT NULL,
car_no varchar(255),
device_no varchar(255),
lng float8,
lat float8,
speed float8,
upload_time timestamp(6),
distance float8,
duration float8,
today_distance float8,
geom geometry,
clean_area float8
);
CREATE INDEX car_trajectory_info_temp_car_no_idx ON car_trajectory_info_temp USING btree (car_no);
CREATE UNIQUE INDEX car_trajectory_info_temp_id_uindex ON car_trajectory_info_temp USING btree (id);
CREATE INDEX car_trajectory_info_temp_upload_time_idx ON car_trajectory_info_temp USING btree (upload_time);
CREATE INDEX car_trajectory_info_temp_geom_idx ON car_trajectory_info_temp USING gist (geom);
</sql>
<update id="createTempTable">
<include refid="createTempTableSQL"/>
</update>
<insert id="insertDataTempTable">
INSERT INTO car_trajectory_info_temp
(id,car_no, device_no, lng, lat, speed, upload_time, distance, duration, today_distance, geom, clean_area)
VALUES
<foreach collection="carTrajectoryInfoList" item="item" separator=",">
(
#{item.id},
#{item.carNo},
#{item.deviceNo},
#{item.lng},
#{item.lat},
#{item.speed},
#{item.uploadTime},
#{item.distance},
#{item.duration},
#{item.todayDistance},
st_geomfromtext(st_astext(#{item.geom}), 4326),
#{item.cleanArea}
)
</foreach>
</insert>
<select id="selectTempTable" resultType="com.yutu.garden.entity.CarTrajectoryInfo">
select
id,upload_time,car_no
from
car_trajectory_info_temp
where
0=0
<if test="geom != null and geom != ''">
and ST_DWithin(geom, st_geomfromtext(st_astext(#{geom}), #{sRid}), #{radius},false)
</if>
<if test="carNo != null and carNo != ''">
and car_no = #{carNo}
</if>
<if test="startTime != null and startTime != ''">
and to_char(upload_time, 'HH24:MI:SS') >= #{startTime}
</if>
<if test="endTime != null and endTime != ''">
and to_char(upload_time, 'HH24:MI:SS') <= #{endTime}
</if>
limit 1
</select>
<sql id="dropTempTableSQL">
DROP TABLE IF EXISTS car_trajectory_info_temp;
</sql>
<update id="dropTempTable">
<include refid="dropTempTableSQL"/>
</update>
</mapper>