• 欢迎光临~

场景之分页查询设计

开发技术 开发技术 2022-07-25 次浏览

一、场景

后端开发中,无论是管理后台还是面向普通用户,必不可少的涉及到批量数据的查询,这种查询的结果通产在前端以列表分页的形式展示出来,包含的参数包括,页码pageNum,每页记录数pageSize或者说是页面大小,总共多少条记录total等。

其中前端传入参数的时候可能还包含,日期范围startTime,endTime,以及涉及到根据商品名的模糊匹配等,还有包括按照筛选字段正序倒序排序等。

二、实现

由于筛选条件通常是多种多样的,并且有些数据更新频繁,很难指定有效的缓存策略,即使有命中率以及使用率也很低,所以放弃缓存策略,压力直接给到了数据库这边。

1、后台sql

操作映射到后台,可能就是查询语句比如

select * from table_name where create_time >= startTime and create_tiem <= endTime and product_name like %product% order by id desc limit pageNum offset pageSize *  (pageNum - 1)

这里默认前端传参pageNum页数是0,所以在参数校验的时候,可以对页码重置,保证偏移量的正确。

if request.PageNum == 0:
    pageNum = 1

根据页码和页面大小可以计算出正确的偏移量

offset = pageSize * (pageNum - 1)

2、limit和offset

执行sql的时候可以用offset,limit或者单独limit做分页。
比如

1. select* from table LIMIT 1,3
2.select * from table LIMIT 3 OFFSET 1

上面两种写法都表示取2,3,4三条条数据。

  • 1中,limit后边跟两个参数,第一个数表示要跳过的数量,后一位表示要取的数量。 LIMIT 1,3 就是跳过1条数据,从第2条数据开始取,取3条数据,也就是取2,3,4三条数据
  • 2中,limit和offset组合使用的时候,limit后面只能有一个参数,表示要取的的数量,offset表示要跳过的数量。LIMIT 3 OFFSET 1 表示跳过1条数据,从第2条数据开始取,取3条数据,也就是取2,3,4三条数据。

3、问题

以上采用limit和offset组合的方式区分偏移量和页面大小。
实例中只是简单的多参数查询,如果涉及到的表记录数很多,需要逐行遍历,特别是在用户端显示的时候,如果用户从第一页直接请求最后一页,先忽略查询条件,假设每页20条,到后端的表现可能就是

select * from table_name order by product_id offset 1000000 limit 20

采用这种SQL查询分页的话,从100万数据中取出这20行数据的代价是非常大的,需要先查出前1000020条记录,根据product_id排序,然后抛弃前面1000000条。效率很低。

三、优化

所以想到对于数据库的只是查询操作,对表建立索引进行优化。

1、覆盖索引

如果对于sql查询的时候只要查找表中的部分字段,可以通过索引可以直接获取查询的结果,不再需要回表查询,也就是建立覆盖索引。

select product_id, product_name from table order by product_id offset 1000000 limit 20

对product_id 和product_name两个字段建立联合索引,查找的时候直接命中不会再去主键索引树找真实的记录。

2、子查询优化

因为实际开发中,只展示查询一两列字段操作是非常少的,因此上述的覆盖索引的适用范围就比较有限。
而子查询避免了大量查询偏移量中的记录,而是只查询目标记录的Id,进而再去查询所有字段。

select * from table where id>=(select id from table order by proudct_id limit 1000000, 1) limit 20;

这种子查询也有自己的限制,

  • 要求主键ID必须是连续的,但是一般的业务中,可以定义默认主键或者自定义字自增主键,在删除的时候,采用逻辑删除(is_del=1)而非物理删除,对于新增的记录的可以实现主键连续。
  • where子句不允许再添加其他条件,但是可以在前端入参的时候做校验,对于默认参数的分页查询可以使用子查询优化。

3、记录查询位置

用户可能触发相同查询条件的多次分页查询,比如第一次

select * from table where product_id > 2222 offset 500000 limit 10;

经过此次查询之后后端记录下来商品id>2222的第500000条记录的id,如果在有类似查询如下

select * from table where product_id > 2222 offset 1000000 limit 10;

分页时直接从记录的这个500000条记录的id的位置开始扫描,从而避免MySQL扫描大量的数据再抛弃的操作。

程序员灯塔
转载请注明原文链接:场景之分页查询设计
喜欢 (0)