随着时期的进步,随着野心勃勃的企业想要变成下一个 Facebook,随着为机器学习预测网络尽可能多数据的想法的涌现,作为开拓职员,我们要不断地打磨我们的 API,让它们供应可靠和有效的端点,从而绝不费力地浏览海量数据。
如果你办事后台开拓或数据库架构,你可能是这么分页的:
如果你真的是这么分页,那么我不得不抱歉地说,你这样做是错的。
你不以为然?没紧要。Slack、Shopify 和 Mixmax 这些公司都在用我们本日将要谈论的办法进行分页。
我想你很难找出一个不该用 OFFSET 和 LIMIT 进行数据库分页的人。对付大略的小型运用程序和数据量不是很大的场景,这种办法还是能够“搪塞”的。
如果你想从头开始构建一个可靠且高效的系统,在一开始就要把它做好。
本日我们将磋商已经被广泛利用的分页办法存在的问题,以及如何实现高性能分页。
1.OFFSET 和 LIMIT 有什么问题?
正如前面段落所说的那样,OFFSET 和 LIMIT 对付数据量少的项目来说是没有问题的。
但是,当数据库里的数据量超过做事器内存能够存储的能力,并且须要对所有数据进行分页,问题就会涌现。
为了实现分页,每次收到分页要求时,数据库都须要进行低效的全表扫描。
什么是全表扫描?全表扫描 (又称顺序扫描) 便是在数据库中进行逐行扫描,顺序读取表中的每一行记录,然后检讨各个列是否符合查询条件。这种扫描是已知最慢的,由于须要进行大量的磁盘 I/O,而且从磁盘到内存的传输开销也很大。
这意味着,如果你有 1 亿个用户,OFFSET 是 5 千万,那么它须要获取所有这些记录 (包括那么多根本不须要的数据),将它们放入内存,然后获取 LIMIT 指定的 20 条结果。
也便是说,为了获取一页的数据:
10万行中的第5万行到第5万零20行
须要先获取 5 万行。这么做是多么低效?
如果你不相信,可以看看这个例子:
https://www.db-fiddle.com/f/3JSpBxVgcqL3W2AzfRNCyq/1
左边的 Schema SQL 将插入 10 万行数据,右边有一个性能很差的查询和一个较好的办理方案。只需单击顶部的 Run,就可以比较它们的实行韶光。第一个查询的运行韶光至少是第二个查询的 30 倍。
数据越多,情形就越糟。看看我对 10 万行数据进行的 PoC。
https://github.com/IvoPereira/Efficient-Pagination-SQL-PoC
现在你该当知道这背后都发生了什么:OFFSET 越高,查询韶光就越长。
2.替代方案
你该当这样做:
这是一种基于指针的分页。
你要在本地保存上一次吸收到的主键 (常日是一个 ID) 和 LIMIT,而不是 OFFSET 和 LIMIT,那么每一次的查询可能都与此类似。
为什么?由于通过显式奉告数据库最新行,数据库就确切地知道从哪里开始搜索(基于有效的索引),而不须要考虑目标范围之外的记录。
比较这个查询:
和优化的版本:
返回同样的结果,第一个查询利用了 12.80 秒,而第二个仅用了 0.01 秒。
要利用这种基于游标的分页,须要有一个惟一的序列字段 (或多个),比如惟一的整数 ID 或韶光戳,但在某些特定情形下可能无法知足这个条件。
我的建议是,不管若何都要考虑每种办理方案的优缺陷,以及须要实行哪种查询。
如果须要基于大量数据做查询操作,Rick James 的文章供应了更深入的辅导。
http://mysql.rjweb.org/doc.php/lists
如果我们的表没有主键,比如是具有多对多关系的表,那么就利用传统的 OFFSET/LIMIT 办法,只是这样做存在潜在的慢查讯问题。我建议在须要分页的表中利用自动递增的主键,纵然只是为了分页。
来源:toutiao.com/i6860655404431442444