MySQL深分页问题原理与三种解决方案

目录 1 深分页问题 1.1 创建表 1.2 新增100万条数据 1.3 深分页语句 1.4 结果分析 2 深分页优化方案 2.1 方案一 2.2 方案二 2.2.1 优化语句 2.2.2 执行计划 2.2.3 结果分析 2.3 方案三 2.3.1 优化语句

目录1 深分页问题1.1 创建表1.2 新增100万条数据1.3 深分页语句1.4 结果分析2 深分页优化方案2.1 方案一2.2 方案二2.2.1 优化语句2.2.2 执行计划2.2.3 结果分析2.3 方案三2.3.1 优化语句2.3.2 执行计划3 MyBatis4 文章总结

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 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 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 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 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 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相关代码。

以上就是MySQL深分页问题原理与三种解决方案的详细内容，更多关于MySQL深分页的资料请关注每日运维其它相关文章！