场景之分页查询设计 - weilanhanf

后端开发中，无论是管理后台还是面向普通用户，必不可少的涉及到批量数据的查询，这种查询的结果通产在前端以列表分页的形式展示出来，包含的参数包括，页码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三条数据。

以上采用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扫描大量的数据再抛弃的操作。