MySQL深分页问题原理与三种解决方案（mysql分页语句）墙裂推荐

<resultMap id=”BaseResultMap” type=”com.test.java.front.test.mysql.deep.page.entity.PlayerEntity”>
<id column=”id” jdbcType=”BIGINT” property=”id” />
<result column=”player_id” jdbcType=”VARCHAR” property=”playerId” />
<result column=”player_name” jdbcType=”VARCHAR” property=”playerName” />
<result column=”height” jdbcType=”INTEGER” property=”height” />
<result column=”weight” jdbcType=”INTEGER” property=”weight” />
<result column=”game_performance” jdbcType=”LONGVARCHAR” property=”gamePerformance” />
</resultMap>

<sql id=”Base_Column_List”>
id, player_id, player_name, height, weight, game_performance
</sql>

<sql id=”conditions”>
<where>
<if test=”playerId !=null”>
and player_id=#{playerId,jdbcType=VARCHAR}
</if>
</where>
</sql>

<sql id=”pager”>
<if test=”skip !=null and limit !=null”>
limit #{skip}, #{limit}
</if>
</sql>

<!– 查询条数 –>
<select id=”selectPageCount” parameterType=”com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryParam” resultType=”java.lang.Long”>
select count(*) from player
<include refid=”conditions” />
</select>

<!– 分页方式1：普通分页存在深分页问题 –>
<!– select * from player limit 990000,5 –>
<select id=”selectPager1″ parameterType=”com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryParam” resultMap=”BaseResultMap”>
select
<include refid=”Base_Column_List” />
from player
<include refid=”conditions” />
<include refid=”pager” />
</select>

<!– 分页方式2：覆盖索引优化深分页问题 –>
<!– select * from player a, (select id as tmpId from player limit 990000,5) b where a.id=b.tmpId –>
<select id=”selectPager2″ parameterType=”com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryParam” resultMap=”BaseResultMap”>
select
<include refid=”Base_Column_List” />
from player a,
(
select id as tmpId from player
<include refid=”conditions” />
<include refid=”pager” />
) b
where a.id=b.tmpId
</select>

<!– 分页方式3：Id分页不支持跳页 –>
<!– select * from player where id > 990000 limit 5 –>
<select id=”selectPager3″ parameterType=”com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryIdParam” resultMap=”BaseResultMap”>
select
<include refid=”Base_Column_List” />
<include refid=”conditions” />
from player where id > #{startId} limit #{pageSize}
</select>
</mapper>