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

  • 0
  • 0
  • 228

一、深分页问题


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

点赞 0
阅读全部

已有 0 条评论

    欢迎您，新朋友，感谢参与互动！