数据库学习笔记(持续更新)
一 分页查询相关
1、offset和limit分页效率低的问题
普通的offset和limit做的分页需要先在数据库中进行逐行扫描,顺序读取表中的每一行记录,然后检查各个列是否符合查询条件,这样平白无故地消耗了很多性能,典型代码如下:
//先获取个人小说的数据总数量(count(*))
int totalCount = novelMapper.selectMyNovelCount(id);
//把查到的总数放到pageBean中
pageBean.setTotalCount(totalCount);
int currentPage = pageBean.getCurrentPage();
// 偏移量: (当前页-1) * 一页数据展示量,LIMIT #{pageBean.offset}, #{pageBean.limit}
int offset = (currentPage - 1) * pageBean.getPageCount();
int limit = pageBean.getPageCount();
//偏移量
pageBean.setOffset(offset);
pageBean.setLimit(limit);
return novelMapper.selectMyNovel(id, pageBean);
这样的话偏移量越高需要全表扫描的字段越多,那么目的就是去掉不需要的扫描,见下面两个数据库语句。
# This is our problematic query as it uses OFFSET and LIMIT.
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
# This is our solution as it knows exactly where from should it start looking for.
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
运行时间结果如下图
详细见如下数据库测试网站
https://www.db-fiddle.com/f/3JSpBxVgcqL3W2AzfRNCyq/1?ref=hackernoon.com
所以我们应该使用id > xxx的方式。
如果我们的表没有主键,比如是具有多对多关系的表,那么就使用传统的 OFFSET/LIMIT 方式,只是这样做存在潜在的慢查询问题。
建议在需要分页的表中使用自动递增的主键,即使只是为了分页。