开发者

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 优化语句
      • 2.3.2 执行计划
  • 3 MyBATis
    • 4 文章总结

      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().toStringphp());
                  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

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

      (2) 执行顺序
      • id越大执行顺序越靠前
      • id相同则按照行数从上到下执行
      • 本语句执行顺序如下图:

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

      • 第一步和第二步表示执行子查询
      • 第三步表示player表与子查询关联
      (3) explain type

      访问类型是重要分析指标:

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

      (4) explain Extra

      Extra表示执行计划扩展信息重点关注三个:

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

      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 s开发者_DB2elect * from player where id > 990000 LIMIT 5

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

      (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.dDDezBhqVseep.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
      	www.devze.com</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" /&ghttp://www.devze.comt;
      		<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相关代码。

      以上就是MySQL深分页问题原理与三种解决方案的详细内容,更多关于MySQL深分页的资料请关注我们其它相关文章!

      0

      上一篇:

      下一篇:

      精彩评论

      暂无评论...
      验证码 换一张
      取 消

      最新数据库

      数据库排行榜