炸！业界难题，跨库分页的几种常见方案(3)

为了方便举例，假设一页只有5条数据，查询第200页的SQL语句为：

select * from T order by time offset 1000 limit 5; 

步骤一：查询改写

select * from T order by time offset 1000 limit 5; 

改写为：

select * from T order by time offset 500 limit 5; 

并投递给所有的分库，注意，这个offset的500，来自于全局offset的总偏移量1000，除以水平切分数据库个数2。

画外音：因为数据量比较大，数据随机性较强，不妨设仍然符合“数据库分库-数据均衡定理”。

如果是3个分库，则可以改写为

select * from T order by time offset 333 limit 5; 

假设这三个分库返回的数据(time, uid)如下：

可以看到，每个分库都是返回的按照time排序的一页数据。

步骤二：找到所返回3页全部数据的最小值

• 第一个库，5条数据的time最小值是1487501123;
• 第二个库，5条数据的time最小值是1487501133;
• 第三个库，5条数据的time最小值是1487501143;

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

画外音：这个time_min非常重要，后文每一个步骤要都要用到time_min。

步骤三：查询二次改写

第一次改写的SQL语句是

elect * from T order by time offset 333 limit 5; 

第二次要改写成一个between语句：

• between的起点是time_min
• between的终点是原来每个分库各自返回数据的最大值

第一个分库，第一次返回数据的最大值是1487501523

所以查询改写为：

select * from T order by time where time between time_min and 1487501523; 

第二个分库，第一次返回数据的最大值是1487501323

所以查询改写为

select * from T order by time where time between time_min and 1487501323; 

第三个分库，第一次返回数据的最大值是1487501553

所以查询改写为

select * from T order by time where time between time_min and 1487501553; 

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

可以看到：

• 分库一的结果集，由于time_min来自原来的分库一，所以分库一的返回结果集和第一次查询相同(所以其实这次访问是可以省略的);
• 分库二的结果集，比第一次多返回了1条数据，头部的1条记录(time最小的记录)是新的(上图中粉色记录);
• 分库三的结果集，比第一次多返回了2条数据，头部的2条记录(time最小的2条记录)是新的(上图中粉色记录);

步骤四：在每个结果集中虚拟一个time_min记录，找到time_min在全局的offset

在第一个库中，time_min在第一个库的offset是333;

在第二个库中，(1487501133, uid_aa)的offset是333(根据第一次查询条件得出的)，故虚拟time_min在第二个库的offset是331;

画外音：从333往前推演。

在第三个库中，(1487501143, uid_aaa)的offset是333(根据第一次查询条件得出的)，故虚拟time_min在第三个库的offset是330;

画外音：从333往前推演。

综上，time_min在全局的offset是333+331+330=994。

步骤五：既然得到了time_min在全局的offset，就相当于有了全局视野，根据第二次的结果集，就能够得到全局offset 1000 limit 5的记录

第二次查询在各个分库返回的结果集是有序的，又知道了time_min在全局的offset是994，一路排下来，容易知道全局offset 1000 limit 5的一页记录(上图中黄色记录)。

这种方法的优点是：可以精确的返回业务所需数据，每次返回的数据量都非常小，不会随着翻页增加数据的返回量。

帅气不帅气!!!

总结

今天介绍了解决“跨N库分页”这一难题的四种方法：

方法一：全局视野法

(1)SQL改写，将

order by time offset X limit Y; 

改写成

order by time offset 0 limit X+Y; 

(2)服务层对得到的N*(X+Y)条数据进行内存排序，内存排序后再取偏移量X后的Y条记录;

这种方法随着翻页的进行，性能越来越低。

方法二：禁止跳页查询法

(1)用正常的方法取得第一页数据，并得到第一页记录的time_max;

(2)每次翻页，将

order by time offset X limit Y; 

改写成

order by time where time>$time_max limit Y; 

以保证每次只返回一页数据，性能为常量。

方法三：允许模糊数据法

(1)SQL查询改写，将

order by time offset X limit Y; 

改写成

order by time offset X/N limit Y/N; 

性能很高，但拼接的结果集不精准。

方法四：二次查询法

(1)SQL改写，将

order by time offset X limit Y; 

改写成

order by time offset X/N limit Y; 

(2)多页返回，找到最小值time_min;

(3)between二次查询

order by time between $time_min and $time_i_max; 

(4)设置虚拟time_min，找到time_min在各个分库的offset，从而得到time_min在全局的offset;

(5)得到了time_min在全局的offset，自然得到了全局的offset X limit Y;

文章比较长，希望大家有收获。

（编辑：ASP站长网）