mysql跨库分页查询_跨库跨表分页

前言

之前经常思考的一个问题,数据库分表后,分页怎么做才是最好的方案呢?今天就来整理一波.

由来

首先是由来,数据量增大,一张表数据太多的话,会使用分表.同理,一个数据库实例到达瓶颈,所以可能需要分库

开始

分表分库都需要一个依据.一般都安装主键自增ID来分割,很多时候分页都是有时间排序的,这样分割也能保证时间的排序.我接触到的也是按照主键切割居多,保证了数据分布均匀,请求也相对均匀.特殊情况下,对于近期数据请求居多的,也方便处理.我们暂时假设我们是按照主键分割的.

第一部分 :曲线救国

实际上分页需要的核心是一个全局视野.也就是说,只要能保证有一个全局计算的地方就可以快速分页.这个时候我们可以维护一张"索引表":

create table table_index

主键id,

你要排序的字段(也可能是多个);

这样,我们就可以根据字段进行排序,再找到对应的主键id.再回表.但是涉及到一个问题是,如果分割的表很多的话,我们可能需要到多张表去查,且这个数字是不确定的,可能是所有表才查到了所需数据,也可能是一张表就查到了,那如何处理的,很自然的,我们可以为这个表再加一个字段,标识这条数据所在的分表,然后根据查到的结果再分类查找,整理.

弊端:

需要额外的维护一张表,且这张表维护的成本并不小,比如某一张分表再此表的数值做了写操作了.分表增加删除的体现等

效率.考虑到要查询到所有所需的数据既可能在一张也有可能是所有, 在数据量大分表多的情况下.logn的效率其实也不是能被接受的

这张表的大小毕竟也是有限制的

好处:

毕竟逻辑简单直接,开发成本低.

在数据分表不多的情况下,可以被接受.有张全局表也方便做一些统计

第二部分:直接干

1 全局找:

其实最简单粗暴,需要第三页的数据,就把所有分表的前三页数据都拿出来,利用程序做排序,然后再取真正第三页的数据

特点:

数据变多了,自然消耗各种东西,网络带宽等,而且还要做计算.最可怕的不是第三页,如果是三百页,一页一百条,那已经不是一个量级了.计算也需要消耗更多的时间,但是数据确实是准确的

2 全局找的折中处理

首先如果业务不需要那么精确地分页,那么问题迎刃而解,比如要找第12页的数据,假设分表是三个,那我们在三个分表中分表取四条,就是12页的全局数据,这样也是极好的,只是数据不再那么精确.

另外还可以处理的是,业务不提供1234567的分页选择,只提供下一页,这样我们的全局找就不会有那么大的性能弊端,在上一页中给到最大的排序值,"下一页"就可以多一个where条件,也只取一页数据即可

3 二次查找

首次查询查询每个库的select * from table order by time offset 10 limit 10;得到10条数据。这里的offset是总offset/分库数

服务层得到来自两个分库的结果集,得到最小的time,也就是最顶层的time，这个time满足最少有10条记录在它前面.然后分别记录每个库的最大time

分别再次查询最小time->每个库上一次的最大time的数据，得到每个库的查询结果

在每个集合的最小time都是相同的，所以可以得到该最小time在整个数据库中的offset，加起来就是这个最小time在全局库的offset位置。

再将第二次查询的结果集拼起来和得到的最小time的offset，推导出 offset 20 limit 10的一页记录。

弊端

需要进行两次数据库查询

优点

可以精确得到业务数据，且每次返回的数据量都非常小，不会随着页码增加而数据量增大。