聊聊 Sharding-Jdbc 使用原理之分库分表下的分页方案

作者：Nick

2022 年 6 月 03 日
本文字数：2733 字
阅读完需：约 9 分钟

本文使用「署名 4.0 国际 (CC BY 4.0)」许可协议，欢迎转载、或重新修改使用，但需要注明来源。署名 4.0 国际 (CC BY 4.0)

本文作者: Nicksxs

创建时间: 2022-01-09

前面在聊 Sharding-Jdbc 的时候看到了一篇文章，关于一个分页的查询，一直比较直接的想法就是分库分表下的分页是非常不合理的，一般我们的实操方案都是分表加上 ES 搜索做分页，或者通过合表读写分离的方案，因为对于 sharding-jdbc 如果没有带分表键，查询基本都是只能在所有分表都执行一遍，然后再加上分页，基本上是分页越大后续的查询越耗资源，但是仔细的去想这个细节还是这次，就简单说说首先就是我的分表结构

CREATE TABLE `student_time_0` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `user_id` int(11) NOT NULL,  `name` varchar(200) COLLATE utf8_bin DEFAULT NULL,  `age` tinyint(3) unsigned DEFAULT NULL,  `create_time` bigint(20) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=674 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

复制代码

有这样的三个表，student_time_0, student_time_1, student_time_2, 以 user_id 作为分表键，根据表数量取模作为分表依据这里先构造点数据，

insert into student_time (`name`, `user_id`, `age`, `create_time`) values (?, ?, ?, ?)

复制代码

主要是为了保证 create_time 唯一比较好说明问题，

int i = 0;try (        Connection conn = dataSource.getConnection();        PreparedStatement ps = conn.prepareStatement(insertSql)) {    do {        ps.setString(1, localName + new Random().nextInt(100));        ps.setLong(2, 10086L + (new Random().nextInt(100)));        ps.setInt(3, 18);        ps.setLong(4, new Date().getTime());

        int result = ps.executeUpdate();        LOGGER.info("current execute result: {}", result);        Thread.sleep(new Random().nextInt(100));        i++;    } while (i <= 2000);

复制代码

三个表的数据分别是 673，678，650，说明符合预期了，各个表数据不一样，接下来比如我们想要做一个这样的分页查询

select * from student_time ORDER BY create_time ASC limit 1000, 5;

复制代码

student_time 对于我们使用的 sharding-jdbc 来说当然是逻辑表，首先从一无所知去想这个查询如果我们自己来处理应该是怎么做，首先是不是可以每个表都从 333 开始取 5 条数据，类似于下面的查询，然后进行 15 条的合并重排序获取前面的 5 条

select * from student_time_0 ORDER BY create_time ASC limit 333, 5;select * from student_time_1 ORDER BY create_time ASC limit 333, 5;select * from student_time_2 ORDER BY create_time ASC limit 333, 5;

复制代码

忽略前面 limit 差的 1，这个结果除非三个表的分布是绝对的均匀，否则结果肯定会出现一定的偏差，以为每个表的 333 这个位置对于其他表来说都不一定是一样的，这样对于最后整体的结果，就会出现偏差因为一直在纠结怎么让这个更直观的表现出来，所以尝试画了个图

黑色的框代表我从每个表里按排序从 334 到 338 的 5 条数据，他们在每个表里都是代表了各自正确的排序值，但是对于我们想要的其实是合表后的 1001，1005 这五条，然后我们假设总的排序值位于前 1000 的分布是第 0 个表是 320 条，第 1 个表是 340 条，第 2 个表是 340 条，那么可以明显地看出来我这么查的结果简单合并肯定是不对的。那么 sharding-jdbc 是如何保证这个结果的呢，其实就是我在每个表里都查分页偏移量和分页大小那么多的数据，在我这个例子里就是对于 0，1，2 三个分表每个都查 1005 条数据，即使我的数据不平衡到最极端的情况，前 1005 条数据都出在某个分表中，也可以正确获得最后的结果，但是明显的问题就是大分页，数据较多，就会导致非常大的问题，即使如 sharding-jdbc 对于合并排序的优化做得比较好，也还是需要传输那么大量的数据，并且查询也耗时，那么有没有解决方案呢，应该说有两个，或者说主要是想讲后者第一个办法是像这种查询，如果业务上不需要进行跳页，而是只给下一页，那么我们就能把前一次的最大偏移量的 create_time 记录下来，下一页就可以拿着这个偏移量进行查询，这个比较简单易懂，就不多说了第二个办法是看的 58 沈剑的一篇文章，尝试理解讲述一下，这个办法的第一步跟前面那个错误的方法或者说不准确的方法一样，先是将分页偏移量平均后在三个表里进行查询

t0334 10158 nick95  18  1641548941767335 10098 nick11  18  1641548941879336 10167 nick51  18  1641548942089337 10167 nick3 18  1641548942119338 10170 nick57  18  1641548942169

t1334 10105 nick98  18  1641548939071   最小335 10174 nick94  18  1641548939377336 10129 nick85  18  1641548939442337 10141 nick84  18  1641548939480338 10096 nick74  18  1641548939668
t2334 10184 nick11  18  1641548945075335 10109 nick93  18  1641548945382336 10181 nick41  18  1641548945583337 10130 nick80  18  1641548945993338 10184 nick19  18  1641548946294  最大

复制代码

然后要做什么呢，其实目标比较明白，因为前面那种方法其实就是我知道了前一页的偏移量，所以可以直接当做条件来进行查询，那这里我也想着拿到这个条件，所以我将第一遍查出来的最小的 create_time 和最大的 create_time 找出来，然后再去三个表里查询，其实主要是最小值，因为我拿着最小值去查以后我就能知道这个最小值在每个表里处在什么位置，

t0322 10161 nick81  18  1641548939284323 10113 nick16  18  1641548939393324 10110 nick56  18  1641548939577325 10116 nick69  18  1641548939588326 10173 nick51  18  1641548939646
t1334 10105 nick98  18  1641548939071335 10174 nick94  18  1641548939377336 10129 nick85  18  1641548939442337 10141 nick84  18  1641548939480338 10096 nick74  18  1641548939668
t2297 10136 nick28  18  1641548939161298 10142 nick68  18  1641548939177299 10124 nick41  18  1641548939237300 10148 nick87  18  1641548939510301 10169 nick23  18  1641548939715

复制代码

我只贴了前五条数据，为了方便知道偏移量，每个分表都使用了自增主键，我们可以看到前一次查询的最小值分别在其他两个表里的位置分别是 322-1 和 297-1，那么对于总体来说这个时间应该是在 322 - 1 + 333 + 297 - 1 = 951，那这样子我只要对后面的数据最多每个表查 1000 - 951 + 5 = 54 条数据再进行合并排序就可以获得最终正确的结果。这个就是传说中的二次查询法。

发布于: 刚刚阅读数: 3

原文链接:【http://xie.infoq.cn/article/7723835b0728c4c1fbed70181】。文章转载请联系作者。