mysql尾页查询_mysql 大表分页查询 翻页 优化方案

最新推荐文章于 2024-01-11 17:48:18 发布
最新推荐文章于 2024-01-11 17:48:18 发布
阅读量768 收藏 2
点赞数
文章标签： mysql尾页查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_33280038/article/details/113608856
版权
本文探讨了MySQL分页查询的效率问题，尤其是随着页数增加查询时间变长。提出了一种优化策略，通过转换offset并利用上一次查询的记录来改善查询效率。针对id排序连续和不连续的情况，以及根据其他字段如创建时间排序的情况，提供了不同的解决方案，并给出了相应的Java代码示例。
摘要由CSDN通过智能技术生成

mysql分页查询是先查询出来offset+limit行数据，然后放弃前offset，取limit条记录，造成了越往后的页数，查询时间越长

一般优化思路是转换offset，让offset尽可能的小，最好能每次查询都是第一页，也就是offset为0

查询按id排序的情况

一、如果查询是根据id排序的，并且id是连续的

这种网上介绍比较多，根据要查的页数直接算出来id的范围

比如offset=40, limit=10, 表示查询第5页数据，那么第5页开始的id是41，增加查询条件：id>40  limit 10

二、如果查询是根据id排序的，但是id不是连续的

通常翻页页数跳转都不会很大，那我们可以根据上一次查询的记录，算出来下一次分页查询对应的新的 offset和 limit，也就是离上一次查询记录的offset

分页查询一般会有两个参数：offset和limit，limit一般是固定，假设limit=10

那为了优化offset太大的情况，每次查询需要提供两个额外的参数

参数lastEndId： 上一次查询的最后一条记录的id

参数lastEndOffset： 上一次查询的最后一条记录对应的offset，也就是上一次查询的offset+limit

第一种情况(与第二种其实是一样)：跳转到下一页，增加查询条件：id>lastEndId limit 10

第二种情况：往下翻页，跳转到下任意页，算出新的newOffset=offset-lastEndOffset,增加查询条件：id>lastEndId offset newOffset limit 10，但是如果newOffset也还是很大，比如，直接从第一页跳转到最后一页，这时候我们可以根据id逆序(如果原来id是正序的换成倒序，如果是倒序就换成正序)查询，根据总数量算出逆序查询对应的offset和limit，那么 newOffset = totalCount - offset - limit， 查询条件：id=totalCount ，也就是算出来的newOffset 可能小于0, 所以最后一页的newOffset=0，limit = totalCount - offset

第三种情况：往上翻页，跳转到上任意页，根据id逆序 ,newOffset = lastEndOffset- offset - limit-1， 查询条件：id

补充的按id排序的后端实现代码，前端和第三种类似，就不补充了

/**

* 如果是根据id排序的分页，根据上一次查询的记录的id优化分页查询

*

* @see 将会自动添加id排序

* @param lastEndId

* 上一次查询的最后一条记录的id

* @param lastEndOffset

* 上一次查询的最后一条记录对应的偏移量 offset+limit

**/

public Page page(QueryBuilder queryBuilder, Integer lastEndId, Integer lastEndOffset, int offset, int limit) {

FromBuilder fromBuilder = queryBuilder.from(getModelClass());

Page page = new Page<>();

int count = dao.count(fromBuilder);

page.setTotal(count);

if (count == 0) {

return page;

}

if (offset == 0 || lastEndId == null || lastEndOffset == null) {

List list = dao

.find(SelectBuilder.selectFrom(fromBuilder.offsetLimit(offset, limit).order().desc("id").end()));

page.setData(list);

return page;

}

boolean isForward = offset >= lastEndOffset;

if (isForward) {

// offset增大的情况

int calcOffset = offset - lastEndOffset;

int calcOffsetFormEnd = count - offset - limit;

if (calcOffsetFormEnd <= calcOffset) {

// 离尾页近的情况，从尾页开始算

isForward = false;

if (calcOffsetFormEnd > 0) {

fromBuilder.order().asc("id").end().offsetLimit(calcOffsetFormEnd, limit);

} else {

// 最后一页

fromBuilder.order().asc("id").end().offsetLimit(0, calcOffsetFormEnd + limit);

}

} else {

fromBuilder.where().andLt("id", lastEndId).end().order().desc("id").end().offsetLimit(calcOffset,

limit);

}

} else {

// offset减少的情况

int calcOffset = lastEndOffset - offset - limit - 1;

if (calcOffset < offset) {

fromBuilder.where().andGt("id", lastEndId).end().order().asc("id").end().offsetLimit(calcOffset, limit);

} else {

// 离首页近的情况，直接查询

fromBuilder.offsetLimit(offset, limit).order().desc("id").end();

isForward = true;

}

}

List list = dao.find(SelectBuilder.selectFrom(fromBuilder));

if (!isForward) {

list.sort(new Comparator() {

@Override

public int compare(T o1, T o2) {

return o1.getId() < o2.getId() ? 1 : -1;

}

});

}

page.setData(list);

return page;

}

三，如果查询是根据其他字段，比如一般使用的创建时间(createTime)排序

这种跟第二种情况差不多，区别是createTime不是唯一的，所以不能确定上一次最后一条记录对应的创建时间，哪些是下一页的，哪些是上一页的

这时候，增加一个请求参数lastEndCount：表示上一次查询最后一条记录对应的创建时间，有多少条是这同一时间的，这个根据上一次的数据统计

根据第二种情况下计算出来的newOffset加上lastEndCount，就是新的offset，其他的处理方式和第二种一致

java 示例：

/**

* 如果是根据创建时间排序的分页，根据上一条记录的创建时间优化分布查询

*

* @see 将会自动添加createTime排序

* @param lastEndCreateTime

* 上一次查询的最后一条记录的创建时间

* @param lastEndCount 上一次查询的时间为lastEndCreateTime的数量

* @param lastEndOffset 上一次查询的最后一条记录对应的偏移量 offset+limit

**/

public Page page(QueryBuilder queryBuilder, Date lastEndCreateTime, Integer lastEndCount, Integer lastEndOffset,

int offset, int limit) {

FromBuilder fromBuilder = queryBuilder.from(getModelClass());

Page page = new Page<>();

int count = dao.count(fromBuilder);

page.setTotal(count);

if (count == 0) {

return page;

}

if (offset == 0 || lastEndCreateTime == null || lastEndCount == null || lastEndOffset == null) {

List list = dao.find(

SelectBuilder.selectFrom(fromBuilder.offsetLimit(offset, limit).order().desc("createTime").end()));

page.setData(list);

return page;

}

boolean isForward = offset >= lastEndOffset;

if (isForward) {

int calcOffset = offset - lastEndOffset + lastEndCount;

int calcOffsetFormEnd = count - offset - limit;

if (calcOffsetFormEnd <= calcOffset) {

isForward = false;

if (calcOffsetFormEnd > 0) {

fromBuilder.order().asc("createTime").end().offsetLimit(calcOffsetFormEnd, limit);

} else {

fromBuilder.order().asc("createTime").end().offsetLimit(0, calcOffsetFormEnd + limit);

}

} else {

fromBuilder.where().andLe("createTime", lastEndCreateTime).end().order().desc("createTime").end()

.offsetLimit(calcOffset, limit);

}

} else {

//这里没有判断是否离首页近，可以加上优化查询，直接按正常查询

fromBuilder.where().andGe("createTime", lastEndCreateTime).end().order().asc("createTime").end()

.offsetLimit(lastEndOffset - offset - limit - 1 + lastEndCount, limit);

}

List list = dao.find(SelectBuilder.selectFrom(fromBuilder));

if (!isForward) {

list.sort(new Comparator() {

@Override

public int compare(T o1, T o2) {

return o1.getCreateTime().before(o2.getCreateTime()) ? 1 : -1;

}

});

}

page.setData(list);

return page;

}

前端js参数，基于bootstrap table

this.lastEndCreateTime = null;

this.currentEndCreateTime = null;

this.currentDataLength = null;

this.isRefresh = false;

this.currentEndOffset = 0;

this.lastEndOffset = 0;

this.lastEndCount = 0;

this.currentEndCount = 0;

$("#" + this.tableId).bootstrapTable({

url: url,

method: 'get',

contentType: "application/x-www-form-urlencoded",//请求数据内容格式 默认是 application/json 自己根据格式自行服务端处理

dataType:"json",

dataField:"data",

pagination: true,

sidePagination: "server", // 服务端请求

pageList: [10, 25, 50, 100, 200],

search: true,

showRefresh: true,

toolbar: "#" + tableId + "Toolbar",

iconSize: "outline",

icons: {

refresh: "icon fa-refresh",

},

queryParams: function(params){

if(params.offset == 0){

this.currentEndOffset = params.offset + params.limit;

}else{

if(params.offset + params.limit==this.currentEndOffset){

//刷新

this.isRefresh = true;

params.lastEndCreateTime = this.lastEndCreateTime;

params.lastEndOffset = this.lastEndOffset;

params.lastEndCount = this.lastEndCount;

}else{

console.log(this.currentEndCount);

//跳页

this.isRefresh = false;

params.lastEndCreateTime = this.currentEndCreateTime;

params.lastEndOffset = this.currentEndOffset;

params.lastEndCount = this.currentEndCount;

if(this.currentDataLength != null && this.currentDataLength != params.limit){

//最后一页数据不满limit，调整lastEndOffset的值

params.lastEndOffset = this.currentEndOffset-

params.limit+this.currentDataLength;

}

this.lastEndOffset = this.currentEndOffset;

this.currentEndOffset = params.offset + params.limit;

console.log(params.lastEndOffset+","+params.lastEndCreateTime);

}

}

return params;

},

onSearch: function (text) {

this.keyword = text;

},

onPostBody : onPostBody,

onLoadSuccess: function (resp) {

if(resp.code!=0){

alertUtils.error(resp.msg);

}

var data = resp.data;

var dateLength = data.length;

this.currentDataLength = dateLength;

if(dateLength==0){

return;

}

if(!this.isRefresh){

this.lastEndCreateTime = this.currentEndCreateTime;

this.currentEndCreateTime = data[data.length-1].createTime;

this.lastEndCount = this.currentEndCount;

this.currentEndCount = 0;

for (var i = 0; i < resp.data.length; i++) {

var item = resp.data[i];

if(item.createTime === this.currentEndCreateTime){

this.currentEndCount++;

}

}

}

}

});

hql语句拼接工具类库：https://gitee.com/xuhaohai/jpaDao

桃花时尚
关注
MySQL中获取最后一条记录的语句
MwgmPhilosophy的博客
10-04 2035
这两种方法的选择取决于你的具体需求和数据库结构。如果你已经知道一个用于排序的列，那么使用第一种方法更加简单和直观。如果你使用的是自增主键列，并且想要获取最后插入的记录，第二种方法可能更适合。通过将结果按照指定列降序排序，并且限制返回结果的数量为1，我们可以获取到最后一条记录。，我们可以获取到最大的主键值，然后将其用于主查询的条件进行筛选，从而获取到最后一条记录。在MySQL数据库中，有多种方法可以获取最后一条记录。请注意，以上示例中的代码仅用于演示目的。如果你有任何进一步的问题，请随时提问。
MySQL分库分页_MySQL分库分表的分页查询解决方案
weixin_30658827的博客
02-09 2986
问题的提出我们知道，当我们的数据量达到一定数量时，需要将数据表进行水平拆分，从而满足大量数据的存储和查询，保证系统的可用性，但同时会出现另外一个问题就是，如果业务要查询“最近注册的第3页用户”，该如何实现呢？单库上，可以通过简单的sql实现分页查询。select * from t_user order by time limit 200,1001select *fromt_userorderbyt...
mysql尾页查询_PHP+Mysql开发分页之首页和尾页的显示
weixin_42131013的博客
01-28 317
/**1.传入页面**/$page=$_GET['p'];/**2.根据页面取出数据：php->mysql**/$host="localhost";$username='root';$password='123456789';$db='bbs2';$PageSize=5;//连接数据库$conn=mysql_connect($host,$username,...
Mysql数据库分页查询及优化
小丑鱼的专栏
10-21 1575
最近遇到个数据库分页查询问题，mysql数据库分页查询默认是升序查询，就是第一页查询的是最先插入的数据，最后一页才是最新插入的数据，弄了个小的数据库表单。
mysql分页最后一页_JSP连接数据库分页（首页，上一页，下一页，尾页）
weixin_42504649的博客
02-03 725
1 2 3 4 String path =request.getContextPath();5 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";6 %>7 8 String name;9 @SuppressWarnings(...
MySQL大数据量分页查询方法及其优化
weixin_30344795的博客
03-31 2039
---方法1: 直接使用数据库提供的SQL语句---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N---适应场景: 适用于数据量较少的情况(元组百/千级)---原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余抛弃. ---...
MySQL limit分页大偏移量慢的原因及优化方案
01-21
本文主要讨论 limit 分页大偏移量慢的原因及优化方案，为了模拟这种情况，下面首先介绍表结构和执行的 SQL。 场景模拟 建表语句 user 表的结构比较简单，id、sex 和 name，为了让 SQL 的执行时间变化更加明显，这里...
Mysql百万级分页优化技巧
12-16
8. **合理设置分页大小**：根据用户需求和服务器性能调整每页显示的记录数，过大的分页可能会导致内存压力，而过小的分页则可能导致用户频繁翻页。 9. **缓存策略**：对于经常访问的分页数据，可以考虑缓存策略，...
易语言MYSQL数据库分页查询
07-20
- **数据库设计**：合理的数据库设计，如归档老数据，可以避免大表查询，提升查询速度。 总结来说，易语言结合MySQL数据库的分页查询是通过建立连接、构建分页SQL、执行查询、处理结果和关闭连接等一系列步骤来实现...
mybatis&Mysql分页查询，以及SQL_CALC_FOUND_ROWS与count(*) 性能对比
最新发布
zzhongcy的专栏
01-11 1474
温故而知新，这里记录一下。
Mysql数据库分页查询练习
06-14
mysql数据苦衷创建一个员工表employee，通过浏览器查询表中的数据， 每页显示4行，若果显示的是首页/尾页，那么继续点击上一页/下一页会默认 显示首页/尾页
mysql 分页尾页计算_php 翻页 计算尾页问题
weixin_42522320的博客
02-04 155
include("conn.php");$pagesize = 5;$url=$_SERVER["REQUEST_URI"];$url=parse_url($url);$url=$url['path'];//echo $url;$numb="select * from `test`";$q=mysql_query($numb);$num=mysql_num_rows($q);//echo $num...
mysql limit分页_mysql使用limit分页优化方案
weixin_39815286的博客
11-28 199
一、测试实验 mysql分页直接用limit start, count分页语句： select * from product limit start, count 当起始页较小时，查询没有性能问题，我们分别看下从10， 100， 1000， 10000开始分页的执行时间（每页取20条），如下：select * from product limit 10, 20 0.016秒select * fr...
mysql 怎样跳到最后一页_用js做分页，点击下一页时，直接跳到了最后一页——Number()的妙用...
weixin_35830270的博客
01-19 290
Number()的妙用Number()是javascript中将字符型转换为数值型的函数；问题描述：做分页，用js实现，获取当前页面的值，然后js自加1，可是点击下一页时，直接跳到最后一页。选择跳转到某页的时候，有的页码无法找到。代码如下//下一页function NextPage() {var endPage = $.trim($("#endPage").val());var nextPage ...
MySQL分页排序，各页中记录存在重复数据现象
个人视角下的技术领域探索
07-17 4754
现象描述： 项目在做分页查询的时候，用到了排序 order by ctime，由于是测试环境，ctime时间值都是一样的。在通过mybatis分页查询的时候，发现最后一页数据和第一页数据重复。后来就拿出sql进行测试，发现偶尔是重复的。 1.解决方案 增加一个二级排序【比如：主键，时间戳】，总之实现绝对排序即可 order ctime,id 即可。 2.原理 （1）mysql查询不指定...
MySQL --- 数据库查询 - 排序查询、分页查询
weixin_60569662的博客
07-05 2138
在实际应用中经常需要对查询结果进行排序，比如，在网上购物时，可以将商品按照价格、数量进行排序；在医院的挂号系统中，可以按照挂号的先后顺序进行排序等。语法 语法说明排序字段名：表示需要排序的字段名称，如果有多个字段用逗号隔开。排序规则：ASC|DESC：表示字段按升序排序；表示字段按降序排序。其中 [ ] 为默认值，默认按升序排列。特点ORDER BY子句一般放到查询语句的最后面，LIMIT字句除外。当排序的字段中存在空值 NULL 时，ORDER BY 会将该空值作为最小值来对待。ORDER BY 指定
MySQL分页查询的5种方法
热门推荐
m0_67391401的博客
07-29 1万+
但方式5的性能会略好于方式4，因为它不需要进行表的关联，而是一个简单的比较，在不知道上一页最大id的情况下，是比较推荐的用法。该查询同样会返回后n条记录，却无需像方式1扫描前m条记录，但必须在每次查询时拿到上一次查询（上一页）的最大id（或最小id），是比较常用的方式。该查询同方式1一样，m的值可能很大，但由于内部的子查询只扫描了id字段，而非全表，所以性能要强于方式1，并且能够解决跨页查询问题。当然该查询的问题也在于我们不一定能拿到这个id，比如当前在第3页，需要查询第5页的数据，就不行了。...
分页查询的那些坑和各种技巧
qwasdf_123的专栏
03-22 823
背景 从上周开始我就一直在做数据清洗的工作，这次算是体会到了什么叫做“抛开数据量谈实现就是耍流氓”。 我设计方案和调试代码连接的都是日常环境的数据库，里面的单表数据量在百级，无论我怎么实现都是瞬间洗完。到了性能测试的时候用的就是性能库，双 11 之前@W君做性能测试的时候，往里面写入了 2000W 的数据，足够我战个痛快。 深坑 一开始的时候，分页查询用的是 limit 子
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值