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

Elita ·
更新时间:2024-11-01
· 728 次阅读

目录

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().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 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> 4 文章总结

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

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



解决方案 Mysql 分页

需要 登录 后方可回复, 如果你还没有账号请 注册新账号