基于InnoDB的API分页设计-一一网

前记

分页设计是一个常见的需求, 对于后端来说, 分页设计通常是跟数据库强绑定的需求. 一个后端API经常会返回成千上百条记录, 如果一下子返回所有结果对服务器, 客户端来说都是一个不小的挑战, 而用户也不会一次请求完所有数据, 对于多余的数据就是单纯的浪费内存和性能. 于是就有了分页的功能, 通过分页功能可以减少对服务器的负担, 加快响应速度. 分页设计很常见, 但是数据都存在数据库里面, 而很少有数据库会提供一个完备的分页功能, 数据库只能尽量的提供分页功能, 所以直接依赖数据库的分页功能容易带来性能问题. 在实现分页功能的时候, 需要尽量避免性能低效, 使用别的方法尽量的实现分页功能. 目前分页方案很多, 但没有一个万金油选项, 只能根据业务以及技术服务定义自己的分页功能.

原文地址: so1n.me/2020/09/05/…

1.一个简单的API例子

首先先看看一个简单的API例子, 大多数第一版的分页功能都是这样实现的. 这个例子的Url让客户端知道能请求第几页,每页有多少条数据, 总的数量有多少:

GET /api/book?page=10&size=10
复制代码

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

{
    "page_info": {
        "page": 10,
        "page_size": 10,
        "total": 10000
    },
    "data": [
        ...
    ]
}
复制代码

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

2.优化点一: offset语法

offset语法使用非常简单,但非常低效, 在前面几页时, MySQL的性能消耗都不高, 但翻到一定的页数时, 性能瓶颈就开始出现了.
前面说过, 数据库只是尽量地去提供分页功能, 在实现翻页时, 确实是返回第n页后的数据, 但是数据库仍然会去扫描前n页的数据, 当翻的页数变高时, 由于扫描的数据过多, 就开始有性能瓶颈了.

还是以select * from xxx limit 100, 10为例子,offset为100时,数据库还是会查看前解析提取110条数据,只不过是跳过前100条而已, 只返回最后10条而已(大多数提供分页功能的数据库都是这样), 所以当offset非常大时, 数据库需要查的数据量就会更大.

看来这个方法是不行的, 哪该怎么优化呢? 如果数据表有良好的设计, 表里应该有个primary key索引的id字段, 他的数值还是趋势自增的(论良好设计的重要性). 那么我们就可以利用这个字段来做实现分页功能了. 这个分页功能的原理是查询条件一定时且数据量不变的情况下(暂不分析带有 order by的情况), 数据库返回的数据顺序永远是相同的且按主键排序, 这样就可以人为的通过id来对数据进行切分, 达到分页的目的.
基于这个思路, 我们可以这样设计, 在每次响应时, 都把当前结果的最后一条数据的id返回给前端, 前端带上这个id请求即可请求下一页, 如果翻到最后一页, 这个id则为空, 前端就知道不可以继续翻页了, 现在可以把API的例子改为:

# page_start改为next_id
GET /api/book?next_id=xxxxxxx&size=10

{
    "page_info": {
        "next_id": "xxxxxxx",
        "page_size": 10,
        "total": 10000
    },
    "data": [
        ...
    ]
}
复制代码

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

改用了这种方法除了可以减少MySQL查询时扫描的行数,提升性能外, 还可以规避另外一个问题, 如已经查询到了100-110条数据时, 0-100中突然插入新的10条数据, 这时下一页的数据还是下一页的数据, 而offset语法则做不到这一点.

然而上面说了那么多好的,特别是对性能的提升非常明显,但他也有局限性, 最大的局限性就是在当前的相关性查询中必须有带索引且保持自增的字段, 此外还有一个局限性, 就是没办法大范围跳页, 或者说使用大范围跳页带来的性能消耗会跟原本的offset…limit…的方法一样. 如果用了很多种数据库, 就会发现上面说的方法很像MySQL, ElasticSearch和Redis的游标, 实际上上面用到的方法正就是MySQL的游标, 如果在代码里有保存对应的游标对象的话, 是能一直调用cursor.fetchone获取之后的数据的, 每次查一行数据, 但是不能直接无视中间的数据跳到第n行后面, 我们这个方法就是通过一个索引数据来代替这个游标对象.

3.优化点二: 总的数量

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

select * from xxx limit 100, 10
select count(*) from xxx
复制代码

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

select SQL_CALC_FOUND_ROWS * from xxx limit 100, 10
select found_rows() count;
复制代码

这样查询的查询量只有一次, 第二条语句会返回第一条语句不带limit查询的总值, 大大的增加了性能.
需要注意的是, 第一条语句一定要带有sql_cale_found_rows和limit, 不然第二条语句返回的结果会不对, 同时第二条语句会过滤一些为空的数据.
可以看出来, 这种方法让人容易误解, 也容易出错, 而且它是MySQL提供的功能, 而不是MySQL中引擎提供的功能, 对部分引擎来说, 性能提升并不是很大, 特别是对于我们常用的InnoDB引擎来说….
对于InnoDB引擎来说, 还是选择用count(*)性能会更好, 因为它对count有一些特别的优化, 以下是count几种情形的执行原理:

对于count(pk), InnoDB会遍历整张表, 把每一行的id值都取出来, 返回给MySQL的server层, server层拿到id后, 通过判断是不是为空来累加.
对于count(1), InnoDB会遍历整张表, 但不取值, MySQL的server层对于返回的每一行, 都放一个数字1进去, 判断是不是为空再按行累加, 由于不用解析数据, 所以速度比count(pk)快一些.
对于count(字段), 会先判断这个字段是not null还是null, 如果是not null, 则一行行地从记录里读取这个字段, 判断不能为null再按行累加. 如果是null, 那么判断有可能为null, 则把值取出来判断, 最后判断不是null才累加.
对于count(*), 是一个特殊的优化, 他不取值, 也不判断是不是null, 直接按行累加.

可以看出, 按照效率排行是 count(*) 约等于 count(1) > count(pk) > count(字段).

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

found_rows 性能说明链接:

stackoverflow.com/questions/1…

www.percona.com/blog/2007/0…

那我们该如何改进呢? 首先是确定真的需要总数吗, 目前很多基于瀑布流的展示都是不需要总数, 如果真的需要总数, 那就只能使用缓存的方法, 根据查询条件为Key, 总数为Value存在缓存中, 每过一段时间再重新更新缓存. 由于内容很多造成页数很大, 即使数据有变动, 短时间内总页数不变动影响也不大, 这样既能满足需求, 又能解决性能上的问题.

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为唯一索引), 为啥在排序条件中增加id这个字段后就可以解决问题呢?

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

1. SELECT
1. DISTINCT <select_list>
1. FROM <left_table>
1. <join_type> JOIN <right_table>
1. ON <join_condition>
1. WHERE <where_condition>
1. GROUP BY <group_by_list>
1. HAVING <having_condition>
1. ORDER BY <order_by_condition>
1. LIMIT <limit_number>

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

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