作为后端程序员,分页查询是少不了的,而最基础的SQL写法就是offset和limit。但不需要担心数据库性能优化问题的日子已经一去不复返了,优秀的后端程序员要以优化性能为目标,而不是实现功能。
我们要不断地打磨我们的 API,让它们提供可靠和有效的端点,从而毫不费力地浏览海量数据。
最基础的可能是这么分页的:
![ae1c25509c8a24f611abfc33584f71d1.png](https://i-blog.csdnimg.cn/blog_migrate/dc629f405cd99196e08f714a7fd0809a.png)
如果你真的是这么分页,那么我不得不抱歉地说,你这样做是错的。
对于简单的小型应用程序和数据量不是很大的场景,这种方式还是能够“应付”的。
如果你想从头开始构建一个可靠且高效的系统,在一开始就要把它做好。
我为什么说这样分页是错误的呢,又该如何实现高性能分页?
1,OFFSET 和 LIMIT 有什么问题?
正如前面段落所说的那样,OFFSET 和 LIMIT 对于数据量少的项目来说是没有问题的。
但是,当数据库里的数据量超过服务器内存能够存储的能力,并且需要对所有数据进行分页,问题就会出现。
为了实现分页,每次收到分页请求时,数据库都需要进行低效的全表扫描。
什么是全表扫描?全表扫描 (又称顺序扫描) 就是在数据库中进行逐行扫描,顺序读取表中的每一行记录,然后检查各个列是否符合查询条件。这种扫描是已知最慢的,因为需要进行大量的磁盘 I/O,而且从磁盘到内存的传输开销也很大。
这意味着,如果你有 1 亿个用户,OFFSET 是 5 千万,那么它首先需要获取所有这5千万条记录 ,将它们放入内存,然后再舍弃他们最后获取 LIMIT 指定的 20 条结果。
原理可以参考MySQL的底层B+树构造,也可以看一下我之前的一篇文章有提到一些。
MySQL面试知识点追命连环问(二)事务、索引及SQL优化
2020-02-24
![145ab78041e0b44f339dea6da0457eb7.png](https://i-blog.csdnimg.cn/blog_migrate/789d5078c74831baf2af340c02771d17.jpeg)
也就是说,为了获取一页的数据:
10万行中的第5万行到第5万零20行
需要先获取 5 万行。这么做是多么的低效啊,严重浪费性能。
2,那我们应该怎么做?
其实很简单,利用主键索引就够了!
![4a326ad8a6cc1e7bd4e48804aff4377a.png](https://i-blog.csdnimg.cn/blog_migrate/43614cf58080bbdb9fd4d9f95da4f323.png)
这是一种基于指针的分页。
你要在本地保存上一次接收到的主键 (通常是一个 ID) 和 LIMIT,而不是 OFFSET 和 LIMIT,那么每一次的查询可能都与此类似。
为什么?因为通过显式告知数据库最新行,数据库就确切地知道从哪里开始搜索(基于有效的索引),而不需要考虑目标范围之外的记录。
比较这个查询:
![47ef556cad1d608c0a462cfff832c594.png](https://i-blog.csdnimg.cn/blog_migrate/2aab05e68ff8ca344db5b02c13967a1f.png)
和优化的版本:
![719f07a4fea09464d0849935970f11ca.png](https://i-blog.csdnimg.cn/blog_migrate/fe5781849566245a5780c29678dbeea3.png)
返回同样的结果,第一个查询使用了 12.80 秒,而第二个仅用了 0.01 秒。
要使用这种基于游标的分页,需要有一个惟一的序列字段 (或多个),比如惟一的整数 ID 或时间戳,但在某些特定情况下可能无法满足这个条件。
我的建议是,不管怎样都要考虑每种解决方案的优缺点,以及需要执行哪种查询。
那如果我们的表没有主键,比如是具有多对多关系的表,那么就只能使用传统的 OFFSET/LIMIT 方式,但这样做存在潜在的慢查询问题。完全可以在需要分页的表中使用自动递增的主键,即使只是为了分页。