分库分表必会-跨库分页查询的几种方式

概述

随着数据库中数据量日益增多，不得进行分库分表，在分库后将数据分布到不同的数据库实例（甚至物理机器）上，以达到降低数据量，提高系统的处理能力，但是这种架构也带来其他问题，比如本文要讲解的跨库分页查询

全局查询法

test表有数据[1,2,3,4,5,6,7,8]，在单库的时候，查询第2页数据并且显示2条，语句是这样的

select * from test order by id limit 2 offset 2

数据返回[3,4],但是数据切分以后，如果要查询，这样语句就可能就会有问题，例如：在节点1执行此语句，返回【6,8】, 节点2返回【5,7】,然后进行排序取前二条返回了【5,6】，可以看到此结果与实际结果不一致，所以应该对SQL语句改写为:

select * from test order by id limit 0 offset 4;

然后在根据各节点返回的数据，在进行排序，筛选出第2页的2条

缺点

1. 每个节点返回更多的数据，增大了网络传输量
2. 服务层还需要进行二次排序，增大了服务层的计算量
3. 随着页码的增大，性能会急剧下降

查询简单，数据准确，不用做业务兼容，数据库中间件都支持

禁止跳页查询法

在数据量很大，翻页数很多的时候，很多产品并不提供“直接跳到指定页面”的功能，而只提供“下一页”的功能，这一个小小的业务折衷，就能极大的降低技术方案的复杂度

假设db1中值为【2、4、6、8】，db2中值为【1、3、5、7】，根据id进行排序，返回对应的条数，在内存中对各个节点返回的数据进行排序，得到需要的数据，执行以下语句，查询第一页数据，返回结果集为【1,2】

select * from test where id>0 order by id limit 2;

相比以前的方案，貌似跟以前处理流程一样，但是在查询第二页时，要根据上一页的id的最大值id_max（第一页的最大id_max为2）,作为第二页的最小值，那么会将如下语句

select * from test order by id limit 2,2;

改写成：

select * from test order by id> 2 limit 2

这样每个节点不用返回4页数据了，只需要返回跟第一页一样页数的数据，可以看到通过对业务的折中，性能得到大大的提升。

缺点

此种方案需要业务层进行处理，而且不能跳页查询，比如当前页是第一页，直接调到第五页，因无法获取到第四页的最大ID，所以无法查询第五页的数据

优点

不会随着页数的增大而影响查询性能

允许数据精度损失查询法

使用patition key进行分库，在数据量较大，数据分布足够随机的情况下，各分库所有非patition key属性，在各个分库上的数据分布，统计概率情况是一致的。

例如，在uid随机的情况下，使用uid取模分两库，db0和db1：

（1）性别属性，如果db0库上的男性用户占比70%，则db1上男性用户占比也应为70% （2）年龄属性，如果db0库上18-28岁少女用户比例占比15%，则db1上少女用户比例也应为15% （3）时间属性，如果db0库上每天10:00之前登录的用户占比为20%，则db1上应该是相同的统计规律

精度损失查询法示意图

利用这一原理，如上图要查询全局第二页数据，limit 2 offset 2 改写为 limit 1 offset 1，每个分库偏移 1（一半），获取1条数据（半页） ，得到的数据集的并集，那么结果为【3,4】基本能够认为，是全局数据的limit 2 offset 2的数据，当然这里只是为了所以返回的准确数据，但是实际并不是精准的。

根据实际业务经验，用户都要查询第100页网页、帖子、邮件的数据了，这一页数据的精准性损失，业务上往往是可以接受的，但此时技术方案的复杂度便大大降低了，既不需要返回更多的数据，也不需要进行服务内存排序了

终极大招-二次查询法

以上介绍的方案或多或少都有一定缺点，那么有没有一种方式能够满足业务需要，也能满足性能要求的方法呢，有，那就是二次查询法。

因此方案相比前三个方案理解起来相对复杂点，为了方便说明，所以先单一DB说起，以下单一DB中保存用户年龄数据，1到30岁，总共30条，如果要查询

select * from T order by age limit 5 offset 10

那么会返回以下粉色标识数据，即【11-15】，请记住此结果，下面会讲解怎么分库查询以下结果。

单一DB数据集

把以上所有数据进行拆分打散存放到3个分库中，如下：

分库数据集

通过上文介绍，在单一DB中查询limit 5 offset 10,返回了【11-15】结果，那如果在以上三个分库全局查询limit 5 offset 10怎么做？

第一步：语句改写

将 select * from T order by age limit 5 offset 10 改写为 select * from T order by age limit 5 offset 3 , 并投递给所有的分库，注意，这个 offset 的 3，来自于全局offset的总偏移量 10，除以水平切分数据库个数 3。

执行select * from T order by age limit 5 offset 3，结果如下（粉色标识数据），为了便于理解用青黄色标识库表前三条数据：

执行limit 5 offset 3数据集（青黄色表库表前三条数据）

第二步：找到返回数据的最小值

1. 第一个库，5 条数据的 age 最小值是10；
2. 第二个库，5 条数据的 age 最小值是 6；
3. 第三个库，5 条数据的 age 最小值是 12;

标识结果集最小值

故，三页数据中，age最小值来自第二个库，age_min=6，这个过程只需要比较各个分库第一条数据，时间复杂度很低

第三步：查询二次改写

第一次改写的SQL语句是select * from T order by age limit 5 offset 3 第二次要改写成一个between语句，between的起点是age_min，between的终点是原来每个分库各自返回数据的最大值：

第一个分库，第一次返回数据的最大值是22 所以查询改写为select * from T order by age where age between age_min and 22

第二个分库，第一次返回数据的最大值是20 所以查询改写为select * from T order by age where age between age_min and 20

第三个分库，第一次返回数据的最大值是25 所以查询改写为select * from T order by age where age between age_min and 25

相对第一次查询，第二次查询条件放宽了，故第二次查询会返回比第一次查询结果集更多的数据，假设这三个分库返回的数据如下：

二次查询结果，（深蓝色表示）

分库一的结果集，比第一次多返回了1条数据，上图中深蓝色记录7

由于age_min来自原来的分库二，所以分库二的返回结果集和第一次查询相同,其实这次查询可以省掉

分库三的结果集，比第一次多返回了3条数据，上图中深蓝色记录8,9,10

第四步：找到age_min在全局的offset

在每个结果集中虚拟一个age_min记录，找到age_min在全局的offset

标识全局offset

因为查询语句为 limit 5 offset 3 ，所以查询结果集中每个分库的第一条数据offset为4；

分库一中，根据第一次查询条件得出的10的offset是4，查询又返回了一条数据向前推进一位索引，故虚拟age_min在第一个库的offset是2

分库二没有数据变化所以age_min的offset=4

分库三中，根据第一次查询条件得出的12的offset是4，查询又返回了三条数据向前推进三位索引，故虚拟age_min在第三个库的offset是0

因此age_min的全局offset为：2+4+0=6

第五步：查找最终数据

既然得到了age_min在全局的offset，就有了全局视野，根据第二次的结果集，就能够得到全局limit 5 offset 10的记录（下图黄色标识数据【11-15】）

标识最终结果数据（黄色表示）

优点

精确返回数据，不会随着页数变大而丢失数据

缺点

需要进行两次数据库查询