1 深分页问题

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万条数据

@SpringBootTest(classes = TestApplication.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class PlayerServiceTest {
    @Resource
    private PlayerRepository playerRepository;
    @Test
    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 深分页优化计划

2.1 计划一

咱们能够从事务形态维度去解决，能够参阅查找引擎解决计划。由于ES也存在深分页问题，查找引擎解决计划是在事务上会约束查询页数。由于页数越大，内容相关度越低，所以页数太大对事务价值不高。MySQL能够类比处理：

• 约束查询页数
• 约束全量导出
• 查询时要求带必要条件（时刻规模、userId）

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相同则按照行数从上到下履行
• 本句子履行次序如下图：

• 榜首步和第二步表明履行子查询
• 第三步表明player表与子查询关联

(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) 适用场景

• 不适用跳页场景
• 只适用【上一页】【下一页】场景

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>

4 文章总结

本文榜首介绍深分页问题体现和原因。第二介绍深分页问题三种解决方法，计划一是从事务维度优化，计划二是运用掩盖索引进行优化，计划三是运用Id分页。第三展示MyBatis相关代码。