pagehelper 手动配置查询分页总页数

pagehelper 手动配置查询分页总页数

最近在使用pagehelper 时发现了一个问题，在进行多表查询的时候，查询分页非常慢，700多条``的数据查询时间达到了6分钟，系统整个瘫痪了，经过了数据库的一番优化之后还是没有得到改善，在打印了分页执行的sql语句发现了问题所在废话不多说直接日志

2020-8-11 11:09:27.074 DEBUG 22012 --- [nio-8081-exec-6] com.sinosoft.musicmain.music.music.information.mapper.MusicMapper.findMusicList_COUNT : ==>  Preparing: SELECT count(0) FROM music_information mu LEFT JOIN music_author mac ON mac.music_id = mu.id LEFT JOIN music_author mas ON mas.music_id = mu.id LEFT JOIN music_author mal ON mal.music_id = mu.id WHERE mac.identify = 0 AND mal.identify = 1 AND mas.identify = 3 
2020-8-11 11:09:27.082 DEBUG 22012 --- [nio-8081-exec-6] com.sinosoft.musicmain.music.music.information.mapper.MusicMapper.findMusicList_COUNT : ==> Parameters: 
2020-8-11 11:15:46.830 DEBUG 22012 --- [nio-8081-exec-6] com.sinosoft.musicmain.music.music.information.mapper.MusicMapper.findMusicList_COUNT : <==      Total: 1
2020-8-11 11:15:46.832 DEBUG 22012 --- [nio-8081-exec-6] com.sinosoft.musicmain.music.music.information.mapper.MusicMapper.findMusicList : ==>  Preparing: SELECT mu.id, mu. NAME, first_lyric, last_lyric, mac. NAME AS composer, mal. NAME AS lyricist, mas. NAME AS singer FROM music_information mu /*LEFT JOIN music_info_sheet mis ON mu.id = music_id*/ LEFT JOIN music_author mac ON mac.music_id = mu.id LEFT JOIN music_author mas ON mas.music_id = mu.id LEFT JOIN music_author mal ON mal.music_id = mu.id WHERE mac.identify = 0 AND mal.identify = 1 AND mas.identify = 3 LIMIT ? 
2020-8-11 11:15:46.833 DEBUG 22012 --- [nio-8081-exec-6] com.sinosoft.musicmain.music.music.information.mapper.MusicMapper.findMusicList : ==> Parameters: 10(Integer)
2020-8-11 11:15:46.837 DEBUG 22012 --- [nio-8081-exec-6] com.sinosoft.musicmain.music.music.information.mapper.MusicMapper.findMusicList : <==      Total: 10

在执行分页之前先执行查询总页数SELECT count(0) FROM music_information mu LEFT JOIN music_author mac ON mac.music_id = mu.id LEFT JOIN music_author mas ON mas.music_id = mu.id LEFT JOIN music_author mal ON mal.music_id = mu.id WHERE mac.identify = 0 AND mal.identify = 1 AND mas.identify = 3 这条语句导致了查询总页数太慢

单独执行limit分页的速度为0.001秒

话不多说

<select id="findMusicList" resultMap="MusicMap">
        SELECT
        mu.id,
        mu. NAME,
        first_lyric,
        last_lyric,
        mac. NAME AS composer,
        mal. NAME AS lyricist,
        mas. NAME AS singer
        FROM
        music_information mu
        /*LEFT JOIN music_info_sheet mis ON mu.id = music_id*/
        LEFT JOIN music_author mac ON mac.music_id = mu.id
        LEFT JOIN music_author mas ON mas.music_id = mu.id
        LEFT JOIN music_author mal ON mal.music_id = mu.id
        <where>
            AND mac.identify = 0
            <if test="composer != null and composer != '' ">
                AND mac. NAME LIKE CONCAT(CONCAT('%',#{composer}),'%')
            </if>
            AND mal.identify = 1
            <if test="lyricist != null and lyricist != '' ">
                AND mal.`name`LIKE CONCAT(CONCAT('%',#{lyricist}),'%')
            </if>
            AND mas.identify = 3
            <if test="singer != null and singer != '' ">
                AND mas. NAME LIKE CONCAT(CONCAT('%',#{singer}),'%')
            </if>
        </where>

    </select>
<!-- 自定义查询分页总页数-->
    <select id="findMusicList_COUNT" resultType="Long">
        SELECT
	      count(0)
        FROM
	      music_information mu
	              <where>
            AND mac.identify = 0
            <if test="composer != null and composer != '' ">
                AND mac. NAME LIKE CONCAT(CONCAT('%',#{composer}),'%')
            </if>
            AND mal.identify = 1
            <if test="lyricist != null and lyricist != '' ">
                AND mal.`name`LIKE CONCAT(CONCAT('%',#{lyricist}),'%')
            </if>
            AND mas.identify = 3
            <if test="singer != null and singer != '' ">
                AND mas. NAME LIKE CONCAT(CONCAT('%',#{singer}),'%')
            </if>
        </where>
    </select>

通过在mapper文件中定义查询方法名+_count 方法进行自定义查询总页数语句，来解决查询总页数查询时间过长的问题 官方文档地址 ，作者在5.0.4版本添加了该功能

优化后查询分页速度0.01秒

2018-10-11 14:08:18.220 DEBUG 76188 --- [nio-8081-exec-1] com.sinosoft.musicmain.music.music.information.mapper.MusicMapper.findMusicList_COUNT : ==>  Preparing: SELECT count(0) FROM music_information mu 
2018-10-11 14:08:18.221 DEBUG 76188 --- [nio-8081-exec-1] com.sinosoft.musicmain.music.music.information.mapper.MusicMapper.findMusicList_COUNT : ==> Parameters: 
2018-10-11 14:08:18.222 DEBUG 76188 --- [nio-8081-exec-1] com.sinosoft.musicmain.music.music.information.mapper.MusicMapper.findMusicList_COUNT : <==      Total: 1
2018-10-11 14:08:18.223 DEBUG 76188 --- [nio-8081-exec-1] com.sinosoft.musicmain.music.music.information.mapper.MusicMapper.findMusicList : ==>  Preparing: SELECT mu.id, mu. NAME, first_lyric, last_lyric, mac. NAME AS composer, mal. NAME AS lyricist, mas. NAME AS singer FROM music_information mu /*LEFT JOIN music_info_sheet mis ON mu.id = music_id*/ LEFT JOIN music_author mac ON mac.music_id = mu.id LEFT JOIN music_author mas ON mas.music_id = mu.id LEFT JOIN music_author mal ON mal.music_id = mu.id WHERE mac.identify = 0 AND mal.identify = 1 AND mas.identify = 3 LIMIT ? 
2018-10-11 14:08:18.223 DEBUG 76188 --- [nio-8081-exec-1] com.sinosoft.musicmain.music.music.information.mapper.MusicMapper.findMusicList : ==> Parameters: 10(Integer)
2018-10-11 14:08:18.225 DEBUG 76188 --- [nio-8081-exec-1] com.sinosoft.musicmain.music.music.information.mapper.MusicMapper.findMusicList : <==      Total: 10