1.问题
1.1 错误姿势
1.使用mybatisPlus提供的默认分页查询，时间字段没有走索引
select * from xxx 这种方式会导致全表扫描
2.表中字段是datetime类型，字段存在类型转换导致索引失效
mapper接口:
Page<PassRecordEntity> getPage(@Param("qryPageDTO") PassCarRecordPageDTO qryPageDTO, @Param("page") Page<PassRecordEntity> page);
mapper接口对应的xml:
version="1.0" encoding="UTF-8"?>
<mapper namespace="com.xxxx.dao.PassRecordDao">
<sql id="flieds">
id,
unique_no,
plate_no,
pass_time,
veh_type,
operator_name,
gate_name,
lane_name,
terminal_no,
lane_code,
card_no,
veh_color,
direction,
pass_Type,
parking_type,
plate_color,
total_region,
park_info,
veh_logo,
veh_logo_name,
in_pass_time,
in_unique_no,
should_pay,
actual_pay,
charge_type,
pic_file_path,
pic_plate_file_path,
pic_plate_file_data,
pic_vehicle_file_data,
pic_pilot_face_path,
pic_copilot_face_path,
pic_pilot_face_data,
pic_copilot_face_data,
dataType,
car_status,
car_owner,
car_owner_phone,
park_code,
park_name,
in_lane,
in_lane_id,
playing_lane,
playing_lane_id,
in_lane_time,
playing_lane_time,
parking_time,
in_lane_image,
playing_lane_image,
create_time,
update_time,
is_del,
remark
sql>
<select id="getPage" parameterType="com.dytz.barrier.gate.entity.PassRecordEntity" resultType="com.dytz.barrier.gate.entity.PassRecordEntity">
SELECT
<include refid="flieds">include>
-- 列出所需要的列，避免使用select *
FROM car_pass_record
where
is_del = 0
<if test="qryPageDTO.id != null ">
and id = #{qryPageDTO.id}
if>
<if test="qryPageDTO.licensePlateNumber != null and qryPageDTO.licensePlateNumber.trim() neq '' ">
and plate_no = #{qryPageDTO.licensePlateNumber}
if>
<if test="qryPageDTO.parkName != null and qryPageDTO.parkName.trim() neq '' ">
and park_name = #{qryPageDTO.parkName}
if>
<if test="qryPageDTO.carStatus != null ">
and car_status = #{qryPageDTO.carStatus}
if>
<if test="qryPageDTO.startTime != null and qryPageDTO.startTime.trim() neq ''
and qryPageDTO.endTime != null and qryPageDTO.endTime.trim() neq '' ">
and date_format (pass_time,'%Y-%m-%d %H:%i:%s') >= date_format(#{qryPageDTO.startTime},'%Y-%m-%d %H:%i:%s')
and date_format (pass_time,'%Y-%m-%d %H:%i:%s') <= date_format(#{qryPageDTO.endTime},'%Y-%m-%d %H:%i:%s')
]]>
-- pass_time该字段建立索引，但是字段存在类型转换会导致索引失效
if>
ORDER BY id desc, create_time DESC
select>
mapper>
2.解决办法
2.1 自定mybatisPlus的分页查询
2.2 表建立索引
[CDATA[
and date_format (pass_time,'%Y-%m-%d %H:%i:%s') >= date_format(#{qryPageDTO.startTime},'%Y-%m-%d %H:%i:%s')
and date_format (pass_time,'%Y-%m-%d %H:%i:%s') <= date_format(#{qryPageDTO.endTime},'%Y-%m-%d %H:%i:%s')
]]>
-- 将上面的方式改为如下方式：这种方式是可以命中索引的
[CDATA[
and pass_time >= CAST(#{qryPageDTO.startTime} AS datetime)
and pass_time <= CAST(#{qryPageDTO.endTime} AS datetime)
</if>
-- datetime字段的时间范围还是使用这个CAST来转换不会导致该时间字段索引失效而全表扫描
Page<PassRecordEntity> page = new Page<>();
page.setCurrent(dto.getCurrent() != null ? dto.getCurrent() : 1);
page.setSize(dto.getSize() != null ? dto.getSize() : 10);
if (StringUtils.isEmpty(dto.getStartTime())
&& StringUtils.isEmpty(dto.getEndTime())) {
LocalDateTime now = LocalDateTime.now();
String nowDayStr = DateUtils.localDateTimeToStringToYMD(now);
dto.setStartTime(nowDayStr + " 00:00:00");
dto.setEndTime(nowDayStr + " 23:59:59");
}
Page<PassRecordEntity> page1 = this.getBaseMapper().getPage(dto,page);
2.2 分库分表
2.3 清理数据
DELETE FROM car_pass_record
WHERE
pass_time >= CAST('2023-05-01' AS datetime) AND
pass_time <= CAST('2023-05-31' AS datetime)
-- pass_time字段建立了普通索引