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

学习知识要善于思考，思考，再思考！今天golang学习网小编就给大家带来《MySQL深分页问题原理与三种解决方案》，以下内容主要包含原理、解决、mysql深分页等知识点，如果你正在学习或准备学习数据库，就都不要错过本文啦~让我们一起来看看吧，能帮助到你就更好了！

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 
<h3>1.3 深分页语句</h3>
<pre class="brush:sql;">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"></id><result column="player_id" jdbctype="VARCHAR" property="playerId"></result><result column="player_name" jdbctype="VARCHAR" property="playerName"></result><result column="height" jdbctype="INTEGER" property="height"></result><result column="weight" jdbctype="INTEGER" property="weight"></result><result column="game_performance" jdbctype="LONGVARCHAR" property="gamePerformance"></result></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><!-- 查询条数 -->&lt;select id=&quot;selectPageCount&quot; parametertype=&quot;com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryParam&quot; resulttype=&quot;java.lang.Long&quot;&gt;
		select count(*) from player
		<include refid="conditions"></include>&lt;/select&gt;<!-- 分页方式1：普通分页存在深分页问题 --><!-- select * from player limit 990000,5 -->&lt;select id=&quot;selectPager1&quot; parametertype=&quot;com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryParam&quot; resultmap=&quot;BaseResultMap&quot;&gt;
		select
		<include refid="Base_Column_List"></include>
		from player
		<include refid="conditions"></include><include refid="pager"></include>&lt;/select&gt;<!-- 分页方式2：覆盖索引优化深分页问题 --><!-- select * from player a, (select id as tmpId from player limit 990000,5) b where a.id = b.tmpId -->&lt;select id=&quot;selectPager2&quot; parametertype=&quot;com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryParam&quot; resultmap=&quot;BaseResultMap&quot;&gt;
		select
		<include refid="Base_Column_List"></include>
		from player a,
		(
		select id as tmpId from player
		<include refid="conditions"></include><include refid="pager"></include>
		) b
		where a.id = b.tmpId
	&lt;/select&gt;<!-- 分页方式3：Id分页不支持跳页 --><!-- select * from player where id > 990000 limit 5 -->&lt;select id=&quot;selectPager3&quot; parametertype=&quot;com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryIdParam&quot; resultmap=&quot;BaseResultMap&quot;&gt;
		select
		<include refid="Base_Column_List"></include><include refid="conditions"></include>
		from player where id > #{startId} limit #{pageSize}
	&lt;/select&gt;</mapper>

4 文章总结

本文第一介绍深分页问题表现和原因。第二介绍深分页问题三种解决方法，方案一是从业务维度优化，方案二是使用覆盖索引进行优化，方案三是使用Id分页。第三展示MyBatis相关代码。

到这里，我们也就讲完了《MySQL深分页问题原理与三种解决方案》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于mysql的知识点！