数据准备
t_order_1 ：[1,3,5,7,9,11,13,15,17,19]
t_order_2 : [2,4,6,8,10,12,14,16,18,20]
1.查询目标
分页查询第二页，每页5条，所以我们想得到的数据是[6,7,8,9,10]
2.具体操作
原先的SQL的offset=5，称之为全局offset，这里由于是拆分成了两张表，因此改写后的offset=全局offset/2=5/2=2，得到如下两条sql.
select num from t_order_1 order by num asc limit 2,5;
select num from t_order_2 order by num asc limit 2,5;
结果 [5,7,9,11,13] 和 [6,8,10,12,14]
t_order_1：5条数据中最小值为：5
t_order_2：5条数据中最小值为：6
那么两张表中的最小值为5，记为min，来自t_order_1这张表。这个过程只需要比较各个分库第一条数据，时间复杂度很低。
t_order_1这张表，第一次查询时的最大值为13
select num from t_order_1 where num BETWEEN min and 13；
t_order_2这张表，第一次查询时的最大值为14
select num from t_order_2 where num BETWEEN 5 and 14;
结果为： [5,7,9,11,13] 和 [6,8,10,12,14] （结果一样是因为例子中表数据规则）
最小值5,在表1和表2中的偏移量都为2，两张表就是 2*2 = 4,所以结果为
select * from (
select num from t_order_1 where num BETWEEN 5 and 13 UNION
select num from t_order_2 where num BETWEEN 5 and 14 ) A ORDER BY num LIMIT 1,5 再向后移一位。
结果为 [6,7,8,9,10]
———————————–分割线——————————————–
如果查第三页，sql 为
select num from t_order_1 order by num asc limit 4,5;
select num from t_order_2 order by num asc limit 4,5;
结果为： [9,11,13,13,15] 和 [10,12,14,16,18]
select * from (
select num from t_order_1 where num BETWEEN 9 and 17 UNION
select num from t_order_2 where num BETWEEN 9 and 18 ) A ORDER BY num LIMIT 2,5;
最终结果为 [11,12,13,14,15]
———————————–分割线——————————————–
如果查第 N 页，
select num from t_order_1 order by num asc limit 2*(N-1),5;
select num from t_order_2 order by num asc limit 2*(N-1),5;
select * from (
select num from t_order_1 where num BETWEEN min and max1 UNION
select num from t_order_2 where num BETWEEN min and max2 ) A ORDER BY num LIMIT 1*(N-1),5;
1*(N-1) 中，第一个 1 = 5%2
———————————–分割线——————————————–
如果有3张分表，查第2页,每页10条，
offSet = 10 / 3 = 3 ; offSet2 = 10%3 = 1
select num from t_order_1 order by num asc limit 3 * (2-1),10;
select num from t_order_2 order by num asc limit 3 * (2-1),10;
select num from t_order_2 order by num asc limit 3 * (2-1),10;
select * from (
select num from t_order_1 where num BETWEEN min and max1 UNION
select num from t_order_2 where num BETWEEN min and max2 UNION
select num from t_order_3 where num BETWEEN min and max3 ) A ORDER BY num LIMIT offSet2*(2-1),10;
原文地址：http://www.cnblogs.com/fengyuduke/p/16924353.html
