转载自： MySQL 深分页问题原理与三种解决方案

一、深分页问题

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.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 相同则按照行数从上到下执行

本语句执行顺序如下图：

第一步和第二步表示执行子查询，第三步表示 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) 适用场景

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

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

  • EOF -

推荐阅读  点击标题可跳转

1、MySQL 常用分库分表方案，都在这里了！

2、你还在用分页？试试 MyBatis 流式查询，真心强大！

3、MySQL 分页优化中的 “ INNER JOIN方式优化分页算法 ” 到底在什么情况下会生效？

看完本文有收获？请转发分享给更多人

关注「ImportNew」，提升Java技能

点赞和在看就是最大的支持❤️