Mysql SQL优化查询效率

近期在项目中遇到一个功能模块数据量稍大的地方，本地开发和测试功能都没啥问题，上了正式环境，问题就体现出来了，因为正式环境需要从第三方导入很多数据，然后正式环境的列表加载基本要几十秒甚至根本加载超时（微服务连接超过一定时间自动断开），其实这里数据开始并不是很大，t_reg_hypertension，t_patient_health ，t_visit_hypertension数据量分别是2w，15w，53w，随着数据量增大，后续的优化工作还会继续

1、第一次优化

这个项目很多地方都需要和基础健康档案进行关联，开始的sql是这样的，这里的优化是忽略了中间动态sql

select distinct a.id,a.patient_id,a.hospital_id,a.personal_file_number,a.patient_name,a.live_type,a.age,a.card_number,a.telephone,a.crowd_label,a.address,a.diagnosis_date,a.data_from,
        a.duty_doctor_name,a.management_unit,a.status,
        (select count(id) from t_visit_hypertension where patient_id=a.patient_id and del_flag=0 and YEAR(create_time)=YEAR(NOW())) visitTimes,
        (select visit_date from t_visit_hypertension where patient_id=a.patient_id and del_flag=0 order by visit_date DESC LIMIT 0,1) lastServiceTime
        from t_reg_hypertension a INNER JOIN t_patient_health ph on a.patient_id=ph.patient_id and a.hospital_id=ph.hospital_id
        where
            a.del_flag=0 and a.status=0 and ph.status=0
        order by a.create_time desc,id limit 0,10

开始的时候打开数据库都很卡，

//查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况
show processlist;

 当时看到有很多连接，而且都很耗时，执行下面命令，结束无关的连接，然后数据库就不会卡顿了

//结束连接
kill id;

执行explain执行情况

可以看到没有用到索引，都是扫描全表 

开始想到的就是建索引，然后先把两张表关联的字段建立联合索引

CREATE INDEX index_t_patient_health_1 ON t_patient_health (hospital_id, patient_id);
CREATE INDEX index_t_reg_hypertension_1 ON t_reg_hypertension (hospital_id, patient_id);

查看索引

show index from t_reg_hypertension

这样稍微比之前好一些，但还是慢，需要10多秒的时间，下面可以看出已经用到索引了

2、第二次优化

细看了一下sql语句，发现有两个字段随访次数 visitTimes 和最后服务时间 lastServiceTime 是统计另外一张表的数据，这张表的数据量更大，所以很慢。然后重新优化sql，思路是把子查询语句删除了，先把分页10条数据查出来然后再去统计

 java代码逻辑

    @Override
    public PageVO<RegDiabetesDTO> page(RegDiabetesQueryReq req) {
        PageVO<RegDiabetesDTO> page = regDiabetesService.page(req);
        if (page.getList() == null || page.getList().size() <= 0) {
            return page;
        }
        List<Map<String, String>> mapList = new ArrayList<>();
        page.getList().forEach(item -> {
            Map<String, String> map = new HashMap<>();
            map.put("hospitalId", item.getHospitalId());
            map.put("patientId", item.getPatientId());
            mapList.add(map);
        });
        //查询出随访次数 visitTimes
        List<Map<String, Object>> visitTimesList = visitDiabetesService.getVisitTimesByPatientId(mapList);
        Map<String, Integer> visitTimesMap = new HashMap<>();
        visitTimesList.forEach(item -> {
            if (item.get("visitTimes") != null) {
                visitTimesMap.put(item.get("hospitalId") + "" + item.get("patientId"), Integer.valueOf(item.get("visitTimes").toString()));
            }
        });
        //查询出最后一次服务时间 lastServiceTime
        List<Map<String, Object>> lastServiceTimeList = visitDiabetesService.getLastServiceTimeByPatientId(mapList);
        Map<String, Date> lastServiceTimeMap = new HashMap<>();
        lastServiceTimeList.forEach(item -> {
            if (item.get("lastServiceTime") != null) {
                lastServiceTimeMap.put(item.get("hospitalId") + "" + item.get("patientId"), DateUtils.parseDate(item.get("lastServiceTime").toString()));
            }
        });
        //把获取到的随访次数和最后一次访问时间设置到list中
        page.getList().forEach(item -> {
            item.setVisitTimes(visitTimesMap.get(item.getHospitalId() + "" + item.getPatientId()));
            item.setLastServiceTime(lastServiceTimeMap.get(item.getHospitalId() + "" + item.getPatientId()));
        });
        return page;
    }

 下面是两个统计的sql，这里也需要把hospital_id,patient_id建立联合索引

<resultMap id="ResultMapNew" type="map">
        <result column="hospital_id" property="hospitalId" jdbcType="VARCHAR"/>
        <result column="patient_id" property="patientId" jdbcType="VARCHAR"/>
        <result column="visitTimes" property="visitTimes" jdbcType="INTEGER"/>
        <result column="lastServiceTime" property="lastServiceTime" jdbcType="TIMESTAMP"/>
    </resultMap>
    <select id="getVisitTimesByPatientId" resultMap="ResultMapNew">
        select hospital_id,patient_id,count(1) visitTimes from t_visit_hypertension
        <where>
            (
            <foreach collection="mapList" item="item" open="(" separator="or" close=")">
                hospital_id=#{item.hospitalId} and patient_id=#{item.patientId}
            </foreach>
            )
            and del_flag=0 and YEAR(create_time)=YEAR(NOW())
        </where>
        GROUP BY hospital_id,patient_id
    </select>

    <select id="getLastServiceTimeByPatientId" resultMap="ResultMapNew">
        select hospital_id,patient_id,Max(visit_date) lastServiceTime from t_visit_hypertension
        <where>
            (
            <foreach collection="mapList" item="item" open="(" separator="or" close=")">
                hospital_id=#{item.hospitalId} and patient_id=#{item.patientId}
            </foreach>
            )
            and del_flag=0
        </where>
        GROUP BY hospital_id,patient_id
    </select>

主表sql

select distinct a.id,a.patient_id,a.hospital_id,a.personal_file_number,a.patient_name,a.live_type,a.age,a.card_number,a.telephone,a.crowd_label,a.address,a.diagnosis_date,a.data_from,
        a.duty_doctor_name,a.management_unit,a.status 
        from t_reg_hypertension a INNER JOIN t_patient_health ph on a.patient_id=ph.patient_id and a.hospital_id=ph.hospital_id
        where
            a.del_flag=0 and a.status=0 and ph.status=0
        order by a.create_time desc,id limit 0,10

现在就能看出来，索引用到了，而且没有子查询，这样查询就比较快了，差不多需要0.5秒，但是还是能看出来有一张表扫描了全表为什么呢？

3、第三次优化

网上查了资料发现，两张表关联， 会去全表扫描数量小的表

• Mysql内部的优化，使用小表驱动大表；

• 它在估算到必须有一个表要全表扫描的话，一定会选择那个数据量更小的表去全表扫描；

• 在这个查询中，因为on以后的where条件列并没有使用到索引；

• 所以mysql的优化只用到了第二张表的索引，没有用到第一张表的索引。

所以这里要从where入手，根据查询条件来优化第一张表，这里建立一个联合索引，随带把order by也一起优化了，考虑到查询语句基本都需要del_flag,status这两个字段，排序也是非常耗时的，所以建立联合所有

CREATE index index_t_reg_hypertension_2 on t_reg_hypertension(del_flag,status,create_time);

这里要调整查询语句的顺序，联合索引遵循：最左前缀法则

select distinct a.id,a.patient_id,a.hospital_id,a.personal_file_number,a.patient_name,a.live_type,a.age,a.card_number,a.telephone,a.crowd_label,a.address,a.diagnosis_date,a.data_from,
        a.duty_doctor_name,a.management_unit,a.status 
        from t_reg_hypertension a INNER JOIN t_patient_health ph on a.patient_id=ph.patient_id and a.hospital_id=ph.hospital_id
        where
             ph.status=0 and a.del_flag=0 and a.status=0
        order by a.create_time desc,id limit 0,10

 但是还发现，排序的索引未生效，因为create_time是desc，而id是asc，索引生效需要同时升序和同时降序

4、第四次优化

select distinct a.id,a.patient_id,a.hospital_id,a.personal_file_number,a.patient_name,a.live_type,a.age,a.card_number,a.telephone,a.crowd_label,a.address,a.diagnosis_date,a.data_from,
        a.duty_doctor_name,a.management_unit,a.status 
        from t_reg_hypertension a INNER JOIN t_patient_health ph on a.patient_id=ph.patient_id and a.hospital_id=ph.hospital_id
        where
             ph.status=0 and a.del_flag=0 and a.status=0
        order by a.create_time desc,id desc limit 0,10

5、第五次优化

还是有Using temporary，这里会建立临时表，查了很多资料，终于发现问题出现在去重 distinct 关键字，把这个去掉就ok，而且非常快，只需要0.01秒左右，最后优化效果。这里会发现一个很奇妙的情况，有distinct不排序效率很高，distinct 和order by 同时存在就会很慢，因为distinct会创建一张临时表然后再来排序

select a.id,a.patient_id,a.hospital_id,a.personal_file_number,a.patient_name,a.live_type,a.age,a.card_number,a.telephone,a.crowd_label,a.address,a.diagnosis_date,a.data_from,
        a.duty_doctor_name,a.management_unit,a.status 
        from t_reg_hypertension a INNER JOIN t_patient_health ph on a.patient_id=ph.patient_id and a.hospital_id=ph.hospital_id
        where
             ph.status=0 and a.del_flag=0 and a.status=0
        order by a.create_time desc,id desc limit 0,10

总结

（1）字符串不加单引号，索引会失效

（2）不以%开头的Like模糊查询，虽然是范围查询，但是后面跟着的索引一样会生效，order by 后面排序的索引会失效

（3）where 后面只会使用最优的一个索引，当多条件时会建立复合索引

（4）distinct 和order by 同时存在效率会很慢，因为distinct会创建一张临时表然后再来排序