mysql单表数据量过大分页查询优化

1.问题

  mysql单表数据量如果达到60多万，在一次使用mybatisPlus默认分页查询，默认查询当天的数据，该时间字段没有建立索引，根据时间范围查询姿势不对导致全表扫描，最终让接口调用超时，60w数据的磁盘好几个G了，超时时长设置为15s依然超时。

1.1 错误姿势

  1.使用mybatisPlus提供的默认分页查询，时间字段没有走索引

   select * from xxx 这种方式会导致全表扫描

  2.表中字段是datetime类型，字段存在类型转换导致索引失效

  mapper接口:

Page<PassRecordEntity> getPage(@Param("qryPageDTO") PassCarRecordPageDTO qryPageDTO, @Param("page") Page<PassRecordEntity> page);

  mapper接口对应的xml:

 <?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.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 '' ">
         <![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')
            ]]>
           -- pass_time该字段建立索引，但是字段存在类型转换会导致索引失效
        </if>
        ORDER BY id desc, create_time DESC
    </select>

</mapper>

2.解决办法

2.1 自定mybatisPlus的分页查询

  不使用select * from xx，列出需要的字段列select id, xx1,xx2,xxxxn from xxx, id是主键

2.2 表建立索引

  在离散度高的子段上适当的建立索引,离散度高的意思是只每一列的区分度大适合建立索引（单列、联合…），比如：一列数据：0/1，男/女 这种列的离散度低，长得基本都很像，这种列就没有必要建立索引了，说白了也就是列的数据的区分度大不大，可以使用EXPLAIN查看sql的执行计划：type：ALL就是全表扫描，所以需要建立索引，优化查询速度和性能：

<![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 清理数据

  由于我是到的表的数据不重要，只要保留最近1-3个月的数据即可，可以删除历史数据，删除历史数据可以按照时间范（这个是时间字段需要建立索引）围框一部分数据删除，比如一个月一个月的删除，如果该时间字段没有建立索引，删除的数据时也会触发全表扫描，会很慢很慢，如果删除数据过多会导致超时，还会导致表死锁，就需要终止sql的执行，正确做法应该是在低峰时段清除数据，按时间范围删选删除数据的字段应提前建立索引，删除数据需小批量多次删除，一次删除大量数据搞不好会对业务有影响，导致超时死锁的风险产生。

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字段建立了普通索引

2.4 使用es

  略，根据自己业务需求选择相应的数据库产品，es使用场景：非事务。

3.总结

  索引失效的情况还很多，上面只是其中一种情况，所以需要特别注意，这里只是单表操作，还有一些相关的规范和原则，比如：阿里开发手册（华山版、嵩山版、泰山版，，，，）等规范可以有效的帮助我们避坑，让我们的代码质量更上一层楼，姿势更加标准和优美，希望我的分享能给你带来帮助，请一键三连，么么哒！