别再用 offset 和 limit 分页了,性能太差

简介: 别再用 offset 和 limit 分页了,性能太差

前言

不需要担心数据库性能优化问题的日子已经一去不复返了。

随着时代的进步,随着野心勃勃的企业想要变成下一个 Facebook,随着为机器学习预测收集尽可能多数据的想法的出现,作为开发人员,我们要不断地打磨我们的 API,让它们提供可靠和有效的端点,从而毫不费力地浏览海量数据。

现状

如果你做过后台开发或数据库架构,你可能是这么分页的:

image.png

如果你真的是这么分页,那么我不得不抱歉地说,你这样做是错的。

你不以为然?没关系。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.替代方案
你应该这样做:

image.png

这是一种基于指针的分页。

你要在本地保存上一次接收到的主键 (通常是一个 ID) 和 LIMIT,而不是 OFFSET 和 LIMIT,那么每一次的查询可能都与此类似。

为什么?因为通过显式告知数据库最新行,数据库就确切地知道从哪里开始搜索(基于有效的索引),而不需要考虑目标范围之外的记录。

比较这个查询:
image.png

和优化的版本:

image.png

返回同样的结果,第一个查询使用了 12.80 秒,而第二个仅用了 0.01 秒。

要使用这种基于游标的分页,需要有一个惟一的序列字段 (或多个),比如惟一的整数 ID 或时间戳,但在某些特定情况下可能无法满足这个条件。

我的建议是,不管怎样都要考虑每种解决方案的优缺点,以及需要执行哪种查询。

如果需要基于大量数据做查询操作,Rick James 的文章提供了更深入的指导。

http://mysql.rjweb.org/doc.php/lists

如果我们的表没有主键,比如是具有多对多关系的表,那么就使用传统的 OFFSET/LIMIT 方式,只是这样做存在潜在的慢查询问题。我建议在需要分页的表中使用自动递增的主键,即使只是为了分页。

目录
相关文章
|
数据采集 前端开发
突破技术限制:使用 request-promise 库进行美团数据获取
本文展示了如何用`request-promise`爬取美团数据,重点是通过代理IP避免封禁。安装库后,配置含代理的请求选项,如`proxy`, `auth`和`headers`,并用`cheerio`解析HTML获取餐厅菜单。通过代理服务可以提高爬虫效率。
696 0
突破技术限制:使用 request-promise 库进行美团数据获取
|
编解码 Android开发
GB28181平台如何接入无人机实现智能巡检?
大家都知道,无人机巡检系统,有效解决了传统巡查工作空间和时间局限问题,降低人力工作成本,有效替代人工巡检工作模式。智能巡检系统通过人工智能技术和机械智能技术完美结合,在工业等场景下,应用非常广泛。本文旨在讲如何实现无人机(如大疆无人机)数据到GB28181平台(如海康、大华、宇视等国标平台)。
410 0
|
9月前
|
NoSQL 关系型数据库 Shell
Mongodb支持事务吗?
MongoDB 是一个非关系型数据库,最初不支持事务。4.0版本引入了多文档事务支持,确保跨多个文档的操作要么全部成功,要么全部失败回滚,保持数据一致性。从4.2版本起,分布式事务和多文档事务成为同义词,支持分片集群和副本集上的多文档操作。配置事务需开启副本集,并通过会话管理事务的提交与回滚。示例展示了如何在MongoDB Shell中使用事务进行多文档操作。
888 11
|
消息中间件 NoSQL Java
Spring Boot中使用Redis和Lua脚本实现延时队列
Spring Boot中使用Redis和Lua脚本实现延时队列
|
Web App开发 监控
国标28181平台只能连接视频监控吗?
国标28181平台只能连接视频监控吗?
333 0
|
流计算
Flink读取本地文件
Flink读取本地文件
243 0
|
NoSQL Go Redis
Asynq: 基于Redis实现的Go生态分布式任务队列和异步处理库
Asynq: 基于Redis实现的Go生态分布式任务队列和异步处理库
793 0
|
网络协议 Linux 网络安全
virtualbox centos7 nat+host-only方式联网踩坑总结
virtualbox centos7 nat+host-only方式联网踩坑总结
1939 1
virtualbox centos7 nat+host-only方式联网踩坑总结
|
安全 Java Linux
在linux虚拟机安装Elasticsearch遇到的问题及解决方法
在linux虚拟机安装Elasticsearch遇到的问题及解决方法 [0] unable to load JNA native support library, native methods will be disabled [1]: max file descriptors [4096] for elasticsearch process is too low, increase to at least [65536] [2]: max virtual memory areas vm.max_map_count [65530] is too low, increase to at leas
637 0
|
SQL 关系型数据库 MySQL
paip.解决 数据库mysql增加列 字段很慢添加字段很慢
paip.解决 数据库mysql增加列 字段很慢添加字段很慢 环境如下: mysql5.6 数据仅仅3w alter table xxx add column yyy int default 0; 添加字段很慢,好几份中都没有好。
4022 0