查询优化order by优化分页查询很慢
分类: Java MySQL 专栏: java Mysql 标签: 优化查询和分页
2024-01-08 23:58:17 113浏览
原始sql三表链接查询,用的left join 没问题
SELECT count(0) FROM sz_forum f LEFT JOIN sz_users u ON f.author_uid = u.id LEFT JOIN sz_type t ON f.type_id = t.id WHERE t.tis_show = '1' AND f.manage_show = ? AND f.author_show = ? AND f.type_id IN (?, ?, ?, ?) AND f.author_uid IN (?, ?, ?, ?, ?, ?) Parameters: 1(String), 1(String), 39(Integer), 2(Integer), 41(Integer), 49(Integer), 49(Integer), 31(Integer), 24(Integer), 51(Integer), 50(Integer), 52(Integer) Total: 1 Preparing: SELECT f.*, u.uname, u.faceimg, t.name, t.type, t.tis_show FROM sz_forum f LEFT JOIN sz_users u ON f.author_uid = u.id LEFT JOIN sz_type t ON f.type_id = t.id WHERE t.tis_show = '1' AND f.manage_show = ? AND f.author_show = ? AND f.type_id IN (?, ?, ?, ?) AND f.author_uid IN (?, ?, ?, ?, ?, ?) order by istop desc,id desc LIMIT ? Parameters: 1(String), 1(String), 39(Integer), 2(Integer), 41(Integer), 49(Integer), 49(Integer), 31(Integer), 24(Integer), 51(Integer), 50(Integer), 52(Integer), 10(Integer) Total: 10
where and 没问题
in查询,最开始怀疑,改了exists查询结果差不多
去掉limit(查询全部7万多),结果比不去的还要慢(分页查询还快一下)
去掉order by ,时间缩短一半
问题出在order by上面,可是排序功能必须有啊
order by优化
百度结果是给需要排序的字段建立索引
特别说明默认排序是添加时间倒叙,其实可以改成id倒叙,因为主键id本身带有索引,其他排序字段添加索引(注意:索引不是越多越好)
果然添加索引后时间快了一半,从网页请求到返回数据还得三秒多,还是慢。
count优化
这里需要解决count的问题,我用的是pagehelper插件分页的,他会在执行sql前执行count函数,这个count查询花费差不多2秒,具体请看上面的代码。
现在,解决问题的思路非常清晰
首先,count不是每次分页都需要,第一次查询count后存入redis,第二次分页直接从redis里取,
那么分页和排序需要自己手动写到sql里,
limit优化
其次limit优化,随着页数增加,查询第一页数据几十毫秒,查询第1000页数据需要几秒,有些表数据本来增长就快哦。
解决方法设置id区间,比如第一页最后一条id传入后台,后台根据id区间查询后分页就很快。
and>limit>order by
源代码:
PageHelper.startPage(pageNo, pageSize,orderby);
List<SzForum> li = messageService.getListJoin (o);
PageInfo<SzForum> pageInfo = new PageInfo<SzMessage>(li, pageSize);
修改:
实体类加入需要的是字段
@TableField(exist = false) String orderby;//排序 @TableField(exist = false) Integer pageSize; @TableField(exist = false) Integer pageNo; @TableField(exist = false) Integer pageStart;//第几条数据开始分页 @TableField(exist = false) Integer pageDescId;//倒叙最后id,分页使用,注意你的排序是倒叙还是正序,倒叙小于id,正序大于id
sql修改:
<if test=" pageSize != null and pageDescId !=null"> and f.id < #{pageDescId} and f.id > (#{pageDescId}-200)//limit优化设置id区间,减去200,自己根据需求设置大小 </if> <if test=" orderby == 'zx'"> order by f.istop desc,f.id desc </if> <if test=" orderby == 'jh'"> order by f.id desc </if> <if test=" orderby == 'zr'"> order by f.hit_num desc,f.id desc </if> <if test=" pageSize != null and pageStart != null and pageDescId==null"> limit #{pageStart}, #{pageSize}//第一次分页 </if> <if test=" pageSize != null and pageDescId!=null"> limit #{pageSize}//非第一次分页 </if>
分页查询修改,为了不影响前端,我还是用pageInfo返回数据,这里自己封装
//排序参数 if(o.getOrderby()==null||o.getOrderby().trim().length()==0) o.setOrderby( "zx"); if(o.getOrderby().equals("jh")) { o.setIsbest("1"); } String param=uid +"_" +(o.getTypeId()==null?"all":o.getTypeId()) +"_" +(o.getAuthorUid()==null?"all":o.getAuthorUid()) +"_"+o.getOrderby(); Long count=redisService.getForumCount(param); if(count==null)count=-1l; List<SzForum> li =new ArrayList<>(); o.setPageNo(pageNo); o.setPageSize(pageSize); o.setPageStart((o.getPageNo()-1 )*o.getPageSize()); if(o.getPageNo().equals(1)){ o.setPageDescId(null);//重新分页避免影响查询 } Integer pages=PageUtil.getPages(count,o.getPageSize()); if(pageNo<=pages&&pages>0){ //pages>0第一次查询count=-1,pages肯定小于0,前端根据count=-1请求查询总条数的ajax li= forumService.getListJoinUserAndType(o); }//else 最后一页数据,避免重复查询 PageInfo<SzForum> pageInfo = new PageInfo<SzForum>(); pageInfo.setList(li); pageInfo.setTotal( count ); pageInfo.setPageSize(o.getPageSize()); pageInfo.setPageNum(o.getPageNo());
效率果然快多了,仅几百毫秒完成查询(反正不到1秒)
工具类
public class PageUtil { public static int getPages(Long total,Integer pageSize){ // 计算总页数 int pages=0; if (total % pageSize == 0) { pages =(int)( total / pageSize); } else { pages =(int)( total / pageSize)+1; } return pages; } }
由于数量过大,比如几十万,count确实很消耗时间,可以专门写个表存入count(或者存入redis),再写个ajax专门获取count即可。这样效率提高了四五倍,查询仅需1秒左右哦。对于增长比较快的常用表,比如评论等,可采取此方案。像分类表角色表这些最多几百条数据的直接用pagehelper即可,影响不大哦。
思路仅供参考,若您有更好的方法,可以评论交流哈
好博客就要一起分享哦!分享海报
此处可发布评论
评论(0)展开评论
展开评论
您可能感兴趣的博客