一、深分页问题
1.1 创建表
CREATE TABLE `player` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`player_id` varchar(256) NOT NULL COMMENT '运动员编号',
`player_name` varchar(256) NOT NULL COMMENT '运动员名称',
`height` int(11) NOT NULL COMMENT '身高',
`weight` int(11) NOT NULL COMMENT '体重',
`game_performance` text COMMENT '最近一场比赛表现',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
1.2 新增 100 万条数据
(classes = TestApplication.class)
(SpringJUnit4ClassRunner.class)
public class PlayerServiceTest {
private PlayerRepository playerRepository;
public void initBigData() {
for (int i = 0; i < 1000000; i++) {
PlayerEntity entity = new PlayerEntity();
entity.setPlayerId(UUID.randomUUID().toString());
entity.setPlayerName("球员_" + System.currentTimeMillis());
entity.setWeight(150);
entity.setHeight(188);
entity.setGamePerformance("{\"runDistance\":8900.0,\"passSuccess\":80.12,\"scoreNum\":3}");
playerRepository.insert(entity);
}
}
}
1.3 深分页语句
select * from player limit 990000,5
1.4 结果分析
-
查询耗时:1.233 秒 -
本语句目标查询 [990001 - 990005] 五条数据 -
但是执行时需要排序 [1-990005] 数据 -
丢弃 [1-990000] 只返回 [990001-990005]
二、深分页优化方案
2.1 方案一
我们可以从业务形态维度去解决,可以参考搜索引擎解决方案。因为 ES 也存在深分页问题,搜索引擎解决方案是在业务上会限制查询页数。因为页数越大,内容相关度越低,所以页数太大对业务价值不高。MySQL可以类比处理:
-
限制查询页数 -
限制全量导出 -
查询时要求带必要条件(时间范围)
2.2 方案二
2.2.1 优化语句
select * from player a, (select id as tmpId from player limit 990000,5) b WHERE a.id = b.tmpId
2.2.2 执行计划
(1) 查看计划
explain select * from player a, (select id as tmpId from player limit 990000,5) b WHERE a.id = b.tmpId
(2) 执行顺序
-
id 越大执行顺序越靠前 -
id 相同则按照行数从上到下执行

(3) explain type
访问类型是重要分析指标:
(4) explain Extra
Extra 表示执行计划扩展信息重点关注三个:
2.2.3 结果分析
-
查询耗时:0.5 秒 -
原因是覆盖索引提升查询效率(只查 ID 列) -
覆盖索引是指查询时索引列完全包含查询列 -
using index 表示使用覆盖索引
2.3 方案三
2.3.1 优化语句
select * from player where id > 990000 LIMIT 5
2.3.2 执行计划
(1) 查看计划
explain select * from player where id > 990000 LIMIT 5
(2) 结果分析
-
查询耗时:0.001 秒 -
range 表示索引范围搜索性能尚可
(3) 适用场景
-
不适用跳页场景 -
只适用【上一页】【下一页】场景
三、MyBatis
<mapper namespace="com.test.java.front.test.mysql.deep.page.repository.PlayerRepository">
<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>
总结
本文首先介绍深分页问题表现和原因,然后介绍深分页问题三种解决方法,方案一从业务维度优化,方案二使用覆盖索引进行优化,方案三使用 ID 分页。最后展示了 MyBatis 相关代码。