查询优化order by优化分页查询很慢

无敌的宇宙
无敌的宇宙
擅长邻域:Java,HTML,JavaScript,MySQL,支付,退款,图片上传

分类: Java MySQL 专栏: java Mysql 标签: 优化查询和分页

2024-01-08 23:58:17 111浏览

数据多了以后一条本地查询居然需要四五秒?不可忍受,寻找查询慢的原因主要是order by和分页的count函数占用时间

原始sql三表链接查询,用的left join 没问题

 

image.png

  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本身带有索引,其他排序字段添加索引(注意:索引不是越多越好)

 

image.png

 

果然添加索引后时间快了一半,从网页请求到返回数据还得三秒多,还是慢。

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 &lt; #{pageDescId} and  f.id &gt; (#{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;
    }



}

image.png

 


由于数量过大,比如几十万,count确实很消耗时间,可以专门写个表存入count(或者存入redis),再写个ajax专门获取count即可。这样效率提高了四五倍,查询仅需1秒左右哦。对于增长比较快的常用表,比如评论等,可采取此方案。像分类表角色表这些最多几百条数据的直接用pagehelper即可,影响不大哦。

思路仅供参考,若您有更好的方法,可以评论交流哈

好博客就要一起分享哦!分享海报

此处可发布评论

评论(0展开评论

暂无评论,快来写一下吧

展开评论

您可能感兴趣的博客

客服QQ 1913284695