mybatis中使用一对多导致查询效率慢 求解决

Question

1.一对多中按年月分组

<resultMap id="stuClassAwayQuery" type="com.wlpy.common.vo.report.StuPrecollectedVo">
        <result column="stuId" jdbcType="INTEGER" property="stuId" />
        <result column="classId" jdbcType="VARCHAR" property="classId" />
        // 数据过多  for查询导致效率低
        <collection property="yearQueryVO" select="yearQuery" column="{stuId=id,classId=classId}">
            <result column="year" jdbcType="VARCHAR" property="year" />
            <result column="month" jdbcType="VARCHAR" property="month" />
            <result column="money" jdbcType="VARCHAR" property="money" />
        </collection>
 </resultMap>

SELECT
    ar.class_id,
    ar.stu_id 
FROM
    apply_relation AS ar

SELECT
    DATE_FORMAT( scr.attendance_time, '%Y-%m-%d' ) AS `year`,
    DATE_FORMAT( scr.attendance_time, '%m' ) AS `month`,
    IFNULL( sum( scr.course_price ), 0 ) AS money 
FROM
    stu_course_relation AS scr 
WHERE
    scr.class_id = 1 #{classId}
    AND scr.stu_id = #{stuId}
GROUP BY
    DATE_FORMAT(
        scr.attendance_time,
    '%Y%m')

Answer 1

看一下这条sql的执行计划，按需求建立查询索引。