MySql分页及优化

 

本文的核心内容：MySql的分页、封装分页对象【参数在应用中传递】、百万数据分页优化、PageHelper实现分页

---

 

一：MySql的分页操作

此语句的含义：从0=Index之后，查询5条数据。

适应场景: 适用于数据量较少的情况(元组百/千级)

原因/缺点: 全表扫描,速度会很慢. Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.

 

二：封装分页对象：

I. 总条数（查询）

totalRow

II. 总页数（计算） totalRow % totalPage == 0 ? totalRow / totalPage : totalRow / totalPage + 1

totalPage

III. 单页显示条数（设定）

pageSize

IV. 访问的页码（获取）

pageIndex（每次请求+1 / -1）

PageUtil源代码：仅供参考

public class PageUtil {

	private Integer totalRow;
	private Integer pageIndex;
	private Integer pageSize;
	private Integer totalPage;
	
	//设置默认当前页与pageSize
	public PageUtil() {
		this(1,5);	
	}
	
	public PageUtil(Integer pageIndex, Integer pageSize) {
		super();
		this.pageIndex = pageIndex;
		this.pageSize = pageSize;
	}
	//设置总行数、计算总页数
	public void setTotalRow(Integer totalRow) {
		this.totalRow = totalRow;	
		Integer count=this.totalRow % this.pageSize ==0? this.totalRow/this.pageSize:this.totalRow/this.pageSize+1;
		this.setTotalPage(count);
	}
	public Integer getPageIndex() {
		return pageIndex;
	}
	public void setPageIndex(Integer pageIndex) {
		this.pageIndex = pageIndex;
	}
	
	public Integer getPageSize() {
		return pageSize;
	}
	public void setPageSize(Integer pageSize) {
		this.pageSize = pageSize;
	}
	
	public Integer getTotalPage() {
		return totalPage;
	}
	public void setTotalPage(Integer totalPage) {
		this.totalPage = totalPage;
	}
	//获得起始行
	public Integer getFirst(){
		return (this.pageIndex-1)*this.pageSize+1;
	}
	//获得最后一行
	public Integer getLast(){
		return this.pageIndex*this.pageSize;
	}
}

 

三：查询百万分页数据的优化【这部分源自网上博客，借鉴】

 

select XXX from table_A  limit 1000000,100;

【13秒左右】

上面的语句是取1000000后面的100条记录，但是这样会导致mysql将1000000之前的所有数据全部扫描一次，大量浪费了时间。

 

优化方案：

（1）使用记录主键的方式进行优化【适用于ID自增的表】

     limit最大的问题在于要扫描前面不必要的数据，所以可以先对主键的条件做设定，然后记录住主键的位置再取行。

select * from table_A where main_id > 1000000  limit 100;

使用between关键字

select * from table_A where id between 1000001 and 1000100 limit 100;

 

（2）使用子查询进行查询

select * from table_A where main_id > (select main_id from table_A limit 1000000,1) limit 100;

 

四：PageHelper实现分页

引入Maven依赖

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.10</version>
        </dependency>

实现分页操作 

    @Override
    public PageResult<Map<String, Object>> pageList(EvaMonitor evaMonitor) {
        //设置pageIndex以及PageSize，执行回调查询，查询结果封装到PageInfo中。
        PageInfo<Map<String, Object>> pageInfo = PageHelper.startPage(evaMonitor.getPageIndex(), evaMonitor.getPageSize())
                .doSelectPageInfo(() -> {
                    evaMonitorDao.selectMonitorDetail(evaMonitor);
                });
        //返回分页数据结构即可
        return new PageResult().setTotal(pageInfo.getTotal()).setResult(pageInfo.getList());
    }