MySQL深分页问题原理与三种解决方案
作者:JAVA前线 发布时间:2024-01-27 14:12:34
标签: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 < 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相关代码。
来源:https://juejin.cn/post/7228801593327009829
0
投稿
猜你喜欢
- EXEC SQL WHENEVER SQLERROR CONTINUE; sqlglm(msg_buffer, &buf
- 依赖包:pip install paramiko源码demo:from time import *import paramiko# 定义一个
- 1) chocolatappChocolat是最新出现的一款强大的Mac系统文本编辑器,兼具原生的Cocoa及强大的文本编辑功能。Choco
- JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式,它使得人们很容易的进行阅读和编写。同时也方便了
- 发现问题python嵌套列表大家应该都不陌生,但最近遇到了一个问题,这是工作中遇到的一个坑,首先看一下问题raw_list = [[&quo
- 使用场景批量合并相同格式的Exce,给DataFrame添加行,给DataFrame添加列使用说明:1.使用某种合并方式(inner/out
- Python 中的 logging 模块可以让你跟踪代码运行时的事件,当程序崩溃时可以查看日志并且发现是什么引
- 代码如下import pandas as pdimport matplotlib.pyplot as pltimport numpy as
- 最近帮人做了个贪吃蛇的游戏(交作业用),很简单,界面如下:开始界面:游戏中界面:是不是很简单、朴素。(欢迎大家访问GitHub)游戏是基于P
- 简述公司使用gitlab 来托管代码,日常代码merge request 以及其他管理是交给测试,鉴于操作需经常打开网页,重复且繁琐,所以交
- 安装anaconda登录anaconda的官网下载,anaconda是一个集成的工具软件不需要我们再次下载。anaconda官网点击下载跳转
- 前言如果你认为深度学习非常的吃GPU,或者说非常的耗时间,训练一个模型要非常久,但是你如果了解了迁移学习那你的模型可能只需要几分钟,而且准确
- 常用的重定向方式有: 301 redirect, 302 redirect 与 meta fresh:301 redirect: 301代表
- 昨天有人在群里问图1的边框效果是否能实现。 边框效果图有人给出答案,需要嵌套一个元素实现。我当时粗粗写了个测试页面,但是时间太晚了,也没有细
- 钉钉设置机器人首先在钉钉设置钉钉机器人群设置—> 智能群助手—>添加机器人—>自定义添加完成,得到一个Webhook AP
- 前言pycharm是python的一个商业的集成开发工具,本人感觉做python开发还是很好用的,django是一个很流行的python w
- 本文实例为大家分享了python实现坦克大战的具体代码,供大家参考,具体内容如下#功能实现游戏主窗口import pygame,time,r
- 前言提示:这里可以添加本文要记录的大概内容:公司里B2B是通过WinSCP里SFTP与客户进行数据传输,WinSCP是一个Windows环境
- 本文实例总结了Python操作redis方法。分享给大家供大家参考,具体如下:python连接方式可参考:https://www.jb51.
- 当程序中出现错误时怎么解决?也就是我们所说的bug(缺陷),以及工作中如何对bug进行调试❤ 什么是bug(缺陷)软件缺陷就是通