API分页设计(基于MySql)

本文总阅读量

分页设计是一个常见的需求, 通常是跟数据库强绑定的需求, 对于一个API来说,经常会返回成千上百条记录, 一下子返回所有结果对服务器, 客户端来说是一个挑战, 于是就有了分页的功能, 所以分页设计很常见, 但是分页的功能并不是数据库的本职工作, 数据库只能尽量的提供分页功能. 所以分页的功能但又是很难的一个点, 并没有一个万金油选项, 只能根据业务定义自己的分页功能.

1.一个API例子

首先先看看一个API例子, 对于客户端来说,需要一个如下的Url让客户端知道能请求第几页,每页有多少条数据:

1
GET /api/book?page=10&size=10

服务端在收到请求时,会转化为offset为100(10* 10), 并在数据库调用select * from xxx limit 100, 10的语法来查出数据, 同时在返回数据时,需要告诉客户端当前是第几页,总共有多少条数据:

1
2
3
4
5
6
7
8
9
10
{
"page_info": {
"page": 10,
"page_size": 10,
"total": 10000
},
"data": [
...
]
}

这样的设计看起来非常不错, 前后端都看得懂, 后端写起来也不费劲,但是对于服务端与MySql的交互还是需要优化的

2.优化点一: offset语法

该语法使用非常简单,但非常抵效率, 在前面几页, MySql的性能消耗都不高, 但当翻的页数再高时, 就开始有性能瓶颈了.还是以select * from xxx limit 100, 10为例子,offset为100时,数据库还是会查看前100条,只不过是跳过不返回数据而已(大多数提供分页功能的数据库都是这样), 数据库实际上是查了前110条,然后返回最后10条, 当offset非常大时, 数据库需要查的数据量就会更大.

那这个方法不行, 该怎么优化呢, 如果数据表有良好的设计, 表里应该有个primary key索引的id字段, 他的数值还是自增的(论良好设计的重要性). 因为这个值是带有索引的, 并且保持有序的自增, 只要让MySql查询大于某个id的值(刚好是第一百条)再Limit10, 这时候MySQL只会扫描我们需要的10条,减少了其他的消耗, 那么我们可以把API的例子改为:

1
2
3
4
5
6
7
8
9
10
11
12
13
# page_start的值为id值
GET /api/book?page_start=100&size=10

{
"page_info": {
"page_start": 100,
"page_size": 10,
"total": 10000
},
"data": [
...
]
}

如果觉得id会暴露自己的数据(比如当前的业务量),也可以用其他字段代替, 只要该字段满足有索引,且保持有序自增的条件. 如业务需求会按时间的相关性来查询, 这时就可以用时间这个字段来代替id了

改用了这种方法除了可以减少MySql查询时扫描的行数,提升性能外, 还可以规避另外一个问题, 如已经查询到了100-110条数据时, 0-100中突然插入新的10条数据, 下一页的数据还是下一页的数据, 而不是原来的下一页的数据还是原来的数据, 不用担心新的记录来扰乱.

然而上面说了那么多好的,特别是对性能的提升非常明显,但他也有局限性, 最大的局限性就是在当前的相关性查询中必须有带索引且保持自增的字段, 此外还有一个局限性.如果用了很多数据库, 就会发现上面说的方法很像, ElasticSearch和Redis的游标, 实际上上面用到的方法正就是MySql的游标, 如果在代码里有保存对应的游标对象的话, 是能一直调用cursor.fetchone获取之后的数据的,只不过用一个索引数据来代替这个游标对象.由于我们使用了游标的功能,那么就会带来了一个显而易见的问题,没法回滚上一页, 需要用原本的offset…limit…的方法实现(或者通过缓存缓存之前加载的数据, 如果上一页刚好是缓存过的数据, 就可以直接返回了).

3.优化点二: 总的数量

在返回响应中, 我们需要返回一个总的数量, 那么就需要执行两条语句:

1
2
select * from xxx limit 100, 10
select count(*) from xxx

这样不止在写代码的时候十分麻烦, 同时要查两次也很浪费性能, 这时可以通过使用FOUND_ROWS函数来帮忙, 把语句改为如下:

1
2
select SQL_CALC_FOUND_ROWS * from xxx limit 100, 10
select found_rows() count;

这样查询的查询量只有一次, 第二条语句会返回第一条语句不带limit查询的总值, 大大的增加了性能.
需要主要的是, 第一条语句一定要带有sql_cale_found_rowslimit, 不然第二条语句返回的结果会不对, 同时第二条语句会过滤一些为空的数据.

注: found_row方法并不是在所有的场景下都能增加性能, 不同的存储引擎, 以及查询语句是否根据索引过滤都会影响到该方法的性能, 如果不确定那种方法的性能好, 建议使用 select count(*) from xxx

found_rows 性能说明链接:

https://stackoverflow.com/questions/1022482/is-there-an-effect-on-the-speed-of-a-query-when-using-sql-calc-found-rows-in-mys#

https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

虽然很好的解决需求, 以及增加了性能, 但是也需要MySql去计算总量, 还是有性能消耗的, 在性能要求高的情况下还是不能去查总的条数, 一般会通过缓存来减少计算次数, 但这样会出现总数不准的问题. 由于我们返回的总数主要还是告诉客户端是否还要继续翻页, 那就可以通过另外一个做法来解决, 假设我们每次获取10条, 如果在获取一次数据时得到的总数不足以10条时, 则应该返回应该标记, 告诉客户端当前已经没有数据可以获取了.

1
2
3
4
5
6
7
8
9
10
11
12
13
# is_null代表是否有数据可以获取 
GET /api/book?page_start=100&size=10

{
"page_info": {
"page_start": 100,
"page_size": 10,
"is_null": false
},
"data": [
...
]
}

4.优化点三: 数据重复问题

在执行select * from xxx limit 100, 10时, 顺序都是一致的, 假设里面有个价格的参数,这个字段我们一般不会加索引的, 但是我们要按照他的大小来排序, 这时sql会写为select * from xxx order by price desc limit 100, 10. 经过这个改动后, 就会触发一个问题–不同的页面可能会出现相同的数据, 但是使用了select * from xxx order by price desc, id desc limit 100, 10就可以解决问题了(id为唯一索引).

首先先看看MySql针对一个查询语句的执行顺序:

    1. SELECT
    1. DISTINCT
    1. FROM
    1. JOIN
    1. ON
    1. WHERE
    1. GROUP BY
    1. HAVING
    1. ORDER BY
    1. LIMIT

可以看出, MySql在执行完排序后再执行limit语句的, 而出现上面这种情况, 是跟MySql5.6之后的更新有关的, 在MySql5.6之后优化器在遇到order by limit语句时, 会使用priority queue的一个优化, 使用 priority queue 的目的,就是在不能使用索引有序性的时候,如果要排序,并且使用了limit n,那么只需要在排序的过程中,保留n条记录即可.但是prioruty queue使用了堆排序的排序方法, 这种方法不是稳定排序的, 也就是值相同时, 顺序并不是一定的. 假设我们要排序的值都是一样的, 且有20个, 假设有两次查询, 一次查询的量是10个, 那会发现两次查询的值是有重复的.

了解了问题的原因后就可以知道select * from xxx order by price desc limit 100, 10出现重复数据的原因是price有相同的值, 导致了数据的重复, 而在加了一个id后, 就没有一个值会重复了, 所以问题也就解决了.
但是需要注意的是, 还有一些极端的情况下会出现数据重复的情况, 因为我们的分页是在数据库提供的排序功能的基础上,衍生出来的应用需求,数据库并不保证分页的重复问题。

查看评论