MySql教程

MySQL 数据库中的分页方案及其效率对比

本文主要是介绍MySQL 数据库中的分页方案及其效率对比,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

前言

MySQL 分页是个很常见的项目需求,因为数据量的问题,我们既无法一次导出所有的数据给用户,用户也无法一次性看完所有的数据,所以分页实际上是个很常见的需求。

除了感叹中文内容圈子里有用的内容越来越稀缺,我想做点什么外,我也想将自己的找到解决方案记录下来,以便以后自己忘记了,能有据可查。

通用分页

对于 MySQL 这样烂大街的数据库,分页的实现有无数的教程和例子,但是大多数的例子就是下面这样的

# 对应的 URL 形式
/news?page=1&per_page=30

// MySQL 专门的 limit 语句实现
SELECT * FROM news limit (page * per_page), per_page
// 标准实现的数据库查询语句实现,如 PostgreSQL

SELECT * FROM news OFFSET (page * per_page) limit per_page

这样的分页实现,是最为常见的分页方案,也是最简单最容易理解的分页方案。如果要考虑总页数,那就自己增加一个全库的 COUNT() 查询总数即可。

但是这样的分页方案也有明显的缺陷,那就是在数据量大的时候,limit 语句容易造成全表扫描,因而效率较低

这里的数据量大的程度,是指数据的条目数以百万甚至千万起算,并且这里的查询均以为 主键 作为查询的条件,不涉及其他索引和其他语句的查询的前提下。

 

其实数据量小的情况下,直接使用 limit 查询最方便,不必去折腾什么高性能的查询分页的实现,一来没有必要,二来过早优化
在没有用到排序的情况下,直接使用 limit 来分页,即使有千万级别的数据,其实性能是相当不错的,能够在秒级别返回查询结果,对于多数的应用来说是够用的

子查询分页

针对使用limit offset在大量数据里的性能问题,如果使用子查询的话,尤其是涉及 主键 排序的时候的性能,有个稍微变通的查询方法
`
// 对应的 URL 形式
/news?page=1&per_page=30

// MySQL 专门的 limit 子查询实现
SELECT * FROM news WHERE id >= (SELCT id FROM news limit (page * (per_page - 1)), per_page)
`
如果使用 MySQL 5.1 不支持上面带 limit 的子查询,可以自己手动改为 inner join 的实现

还有一种类似的变种实现方法,就是使用 SQL In Query 实现如下
// 使用 IN 查询 SELECT * FROM news WHERE id IN (SELECT id FROM news ORDER BY id limit (page * (per_page - 1)), per_page)
两者的查询效率是差不多的,千万级别的数据,单纯使用主键排序,能在秒级返回分页查询结果

使用第二种 SQL In Query 实现,可以考虑将需要分页表中的主键 ID 单独成表,然后独立查询,如果数据量实在太大的情况下,这样的单独主键 ID 表能极大提供分页排序输出的速度。

这样的子查询的方式,跟前面的最简单的分页方式近似,也兼顾了性能。千万级别的数据能在秒级别返回查询结果,也能一定程度使用带索引的列进行排序。
这样的方式稍微兼顾了主键排序的结果,还是可以使用的。很多的互联网公司内(比如阿里巴巴,阿里的 JAVA 开发手册内有这个案例)也是将这个语句作为默认的分页实现的

流式分页/游标分页

其实没有什么流式分页这种业界的分页方式,我自己创造的名词实现吧

多数的时候大家将这样的方式叫做 游标分页

对于 timeline 这样的数据流,传统的分页显得很不合适,因为时间线的条目是一直增长的,并且增长得很快,你很难去定义第一页第二页这样的概念。传统的分页对于时间线这样的实现,很容易造成漏数据。

但是时间线的数据流是天然自增的数据,因此引入流式分页的概念,严格来说,流式分页并不能算成一种分页的方式,因为它只是针对数据流某个时间间隙之间的数据

流式分页的实现在 Twitter 和 Facebook 的 API 设计上表现得很明显
`
// Twitter
"search_metadata": {
"max_id": 250126199840518145,
"since_id": 24012619984051000,
"refresh_url": "?since_id=250126199840518145&q=php",
"next_results": "?max_id=249279667666817023&q=php",
"count": 10,
"completed_in": 0.035,
"since_id_str": "24012619984051000",
"query": "php",
"max_id_str": "250126199840518145"
}

// Facebook
{
"data": [
... Endpoint data is here
],
"paging": {
"cursors": {
"after": "MTAxNTExOTQ1MjAwNzI5NDE=",
"before": "NDMyNzQyODI3OTQw"
},
"previous": "/albums?limit=25&before=NDMyNzQyODI3OTQw"
"next": "/albums?limit=25&after=MTAxNTExOTQ1MjAwNzI5NDE="
}
}
`
从两者的 API 接口文档数据,可以充分看得出两者的设计殊途同归

无论是 Twitter 还是 Facebook,其流式分页都是通过指定当前查询的 min_id/since_id 和 max_id/last_id 来查询当前所有数据中的某个时间段的数据,通过指定 min_id/since_id 和 max_id/last_id 来查询的效率极其高,以 Facebook 的查询来说明编程的实现如下
`// 对应的 URL 形式,向前翻页
/news?limit=30&before=[min_id]

// MySQL 专门的 limit 语句实现
SELECT * FROM news WHERE id <= [min_id] limit per_page

// 对应的 URL 形式,向后翻页
/news?limit=30&after=[max_id]

// MySQL 专门的 limit 语句实现
SELECT * FROM news WHERE id >= [max_id] limit per_page`
使用流式翻页的同学一般都知道 下一页 是如何实现的,毕竟传入最大的 max_id 即可

但是大家一般都会困惑于 上一页 如何实现的,其实最简单的方案就是类似 Facebook 这样的针对向前翻页和向后翻页的区别 URL 参数对待,否则的话,要么建立缓存表或者另建立分页表,要么就只能一页一页往下翻,不能往前翻页(类似于手机时间线流动的感觉)

流式分页/游标分页实现起来尤其简单,不过只适合主键 ID 自增且连续的情况,性能也好到不得了
前端记得针对 向前翻页 和 向后翻页 区别 URL 参数对待,这是编程中大多数同学没法转过弯来的地方。并且 min_id/since_id 和 max_id/last_id 不必同时传入查询,因为这样就无法掌控查询的条目数量(从用户前端查询的角度而言,不是从 DBA 的角度而言)
最好的方式是类似 Facebook 这样的方式 min_id/since_id 和 max_id/last_id 只传入其中一个,每个分页的条目数量使用 limit 字段单独管控,也方便限制用户前端查询条目数量,不至于查询一个巨大的数量导致数据库卡死。

其他分页方式

此处暂无,以后我有发现再更新吧

其实有一些使用 MySQL+Redis 实现的分页方式,感觉略微奇葩,只能针对特定的场景使用,这里就不一一整理了

结语

对于普通的应用,尤其是数据量在百万级别以下的应用,其实 MySQL 通用的分页方式性能足够,而且足够简单并且方便使用,不必过早优化。只要做好最大分页数量和分页每页的数量限制,MySQL 的缓存适当针对机器的性能和应用的实际需求调优一下,通用的分页方式性能足够。我想,多数的应用,其实都是到不了百万级别的数据量吧

不过考虑到编程实现的方便程度,其实最后的流式分页/游标分页的方式,编程实现是最简单的,并且性能根本无须考虑,哪怕你有亿级的数据。并且流式分页/游标分页非常适合在 API 中分页输出,在手机信息流中这种无线滚动的信息流中进行分页输出,不会丢也不会重复输出某个条目,优势非常明显,堪称最佳的分页方式。不过话说回来,缺点也很明显,就是不能像普通分页那样有第一页第二页到第N页的数字页码,简单说就是没有页数的概念了,前端页面输出一般只有上一页和下一页这样简单的翻页按钮

需要提醒的是,所有的分页查询条件都必须建立索引,并且不能有 TEXT 字段。MySQL 的索引字段中,VARCHAR(32) 我都觉得很大了, TEXT 字段简直是不能容忍的,不怕死的可以自己试试。

这篇关于MySQL 数据库中的分页方案及其效率对比的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!