数据库水平分库分表后的数据分页查询解决方案

---

• 2021-09-04 更: 推荐下自己的个人框架,后面代码都会移植到框架中 链接
• 2020-10-19 更: 月底不出意外,打算重构了,fabric区块链框架改的差不多了
• 2020-10-? 更: 谢谢大佬提了意见,思路没问题,把以前丢失的jar包也传到了github上,以及把2个sql也上传了,可以直接使用了,至于代码质量问题,没时间,启动只需要创建数据库,直接走test case 即可,恳求给些问题,给了问题,我才有动力强制改下,因为现在耗在该fabric整个源码,属实没时间啊,跪求提问题啊啊啊,项目在这, :-)
• 2020-06-21 更: 最近业务上有设计到分表查询,发现一个问题了,就是如果ID是基于雪花算法的话,第二次是无法通过ID 查询的,解决方法是动态的通过其他条件between and 查询 (比如说create_date),以后再更新啦
• 2020-04-21 更 : 代码过去太久远了,是我大二时候写的代码,现在毕业一段时间了,代码很浅,流程啥的也不流畅,或许连个参考也算不上,见谅. 以后会找时间更新的 :-)

---

• 源码在这

• 核心代码在这

• 需要结合这个目录下的文件才可以看的大概

• 需要依赖自己的工具包

---

• 所有的测试代码在test模块下
• 测试结果在底部:

• 2019-07-17 更: 极端数据分不下导致的数组越界问题的缘故在于动态设置最大值的时候没有考虑到为空的情况,从而导致长度不一致

• 2019-07-04 更: 大概知道了,看数据库发现原来数据没插入正确 ,逻辑并没有错误,是sql脚本复制错了,2个库中的数据是一样的,导致了小于10的时候出现了偏差

• 2019-06-20 更:

  • V1 版本: 所有的一切都混合在一行配置语句中,需要手动配置
  • V2 版本: 只需要配置yaml文件,简单配置几个字段,启动的时候反射自动注入

• 2019-06-13 更:

  • 当pageSize<10的时候,偏移量会出现错误,未debug,有空改,其余正常,预期更改时间为开始重新用Java写项目的时候,最近一直在搞区块链的东西,因而暂时没时间改这个bug

• 2018-12-22 更:

  • 源码是自己造的一个分布式情况下的分页小轮子

• 2018-11-06更:

  • .说下最近吧,最近主要在学go,Java花费的时间相对少很多,打算是慢慢更新了,ps 语言相通,就是有些点需要转一下思路…emmm go挺屌的,但是没Java舒服(单面向对象这块),至于泛型就不用说了…- .-

• 2018-10-26 更:

  • 这个托了好久了,明天给出代码,最近都在为秋招准备,结束了,还是做项目爽啊啊啊啊
    为什么要分库分表,水平与竖直的区别,这里就不讲述了,想了解的自行google

• 2018-10-27 更:

  • 用的是之前封装的一个小扩展包架构,核心是基于内存map来控制的,简单介绍下类的包容关系:介绍在下方
  • 17:31更:重新回顾了下之前的思路,补全了一些记录,但是还有很大优化的余地,现在暂时就添加了第四种双重sql的方式

---

开门见山直接说解决方案:

分库分表的策略,依项目需求而定,这里采用的是常规的做法:根据取模的方式,假设我们水平分库2个,每个库又水平拆表2个 既总共有4个表,查询的时候默认没有按照其他的条件进行排序,假设我们要查询第41页的数据,每页显示10条数据

第一种:也是最简单的一种:通过额外的添加一张关联表,属性中必有id属性,至于是否有库id属性和表id属性(既第几个库和第几个表)可有可无,因为这个可以根据id自行取模获取,注意这张表存放的数据是所有数据,但是胜在属性列少,只有提供索引的几个属性列,这样的话我们只需要select * from brand_temp where … limit 400,10(插叙第41页的数据,每页显示5条数据),然后我们获取了id之后就可以去对应的表中查询了

第二种:最耗费性能的一种,如果我们要查询第一页的记录,单库单表的sql为:select * from db limit 0,10; 当我们分库分片之后 语句还是同样的语句,但是这时候我们需要对4个表返回的记录在内存中进行解析,然后通过id进行升序,取得前10条数据返回…数据量小,页码小的时候很ok,但是如果我们要查询第2页的数据的时候,sql单体架构的情况下为:select * from db limit 10,10; 但是在分布式数据库这样是不行的,数据很明显会丢失,弥补的方法是查询所有,sql语句为select * from db_x limit 0,10+10 //意味着需要查询的是本在单体架构上要查询的记录数加上之前的记录 ,然后再在内存中合并所有表返回的记录然后进行解析,最后取第10开始的记录 …可以看出这个方案一旦页码数达到n页,而每页显示的记录数为m条记录的时候,每个表需要查询的记录数为:(n-1)*m+m=nm条记录,内存中需要解析的记录数为 t * n * m 条记录,内存(我这里写的竟然是cpu?)不爆炸算我输

第三种:采取的是基于业务的模式:迫使用户无法进行跳页查询,什么意思呢,就是用户只能点击下一页或者上一页的方式浏览,具体的做法在于查询得到记录数的同时记录下当前唯一id值的最大值,然后再次查询的时候添加where 条件…让我们从头开始捋: 第一次查询pageNum=1,pageSize=10 ,maxId=0->sql:select * from db_x where id>0 limit 10; 然后分发到对应的库的表中,将得到的4*10条数据合并,再在内存中进行解析排序,取前10条数据,同时将第10条数据的id=maxId单独取出渲染到前端页面上保存,这样当点击下一页的时候,这个maxId=10也提交上去了,sql 变成了select * from db_x where id>10 limit 10,然后继续解析,继续保存…这种方式返回的数据都是稳定的并且数据是连贯的(排序)

第四种:传说中的最好的方式,支持跳页查询,这个方式核心在于2次sql查询,具体怎么做呢:
前提条件假设:查询第1001页的数据,每页显示10条记录

1):我们先记录下要查询的记录数的范围:(1001-1)*10=10000 开始,10010结束->10000-10010
单体的sql为:select * from db limit 10000,10;
我们总共有4个表,意味着:每个表的start应该为10000/4=2500,从而sql变成了:
select * from db_x limit 2500,10;	//假设是平均分配的,因而我们可以均分,不均分也没关系,后续操作会补齐
我们会得到4个表中的记录:(因为我demo还没写,所以先手写了)
T1:(1,"a"),.......
T2:(2,"b"),.......
T3:(3,"c"),.......
T4:(4,"d"),.......
真实数据第1001页不可能是1开头的,将就着看吧,过几天会一起讲rabbitMQ分布式一致性和这个demo一起发布的
ok,第一阶段的sql查询结束

2):对4个表中返回的记录进行id匹配(id如果非整型,自行用hashCode匹配),因为是升序查询,所以我们只需要比较下每个表的首条记录的id值即可,获得了最小的minId=1,和各个表最大的那个值maxId;ok,转换sql思路,这里我们采用条件查询了(弥补操作第一步):
select * from db_x where id between minId and maxId 这样我们就获取到了遗漏的数据(当然有多余的数据)
这样我们4个表中就返回了可能记录数各不相同的记录,第二步结束

3):
之后记录minId出现的位置,如T1出现的位置为2500,T2出现的位置为2500-2=2048 ,T3出现的位置为2500-3=2047 ,T4出现的位置为2500-3=2047 则最终出现的记录数为:2500+2048+2047+2047=10000-2-3-3=9992,因此我们需要的查询的记录数需要从9992 依次往后取8个开始,然后再取10个就是所求的数据,这种方式能做到数据精确查询,但是唯一的缺点就是每次查询都需要进行二次sql查询

• 总结:

  • 第一种通过关联表的方式,是大部分所采用的,当然缺点也有那个关联表会变得无比巨大,但是这种方案很好解决了数据的查询问题
  • 第二种方式是效率最低的方案, 适用于小型项目,不过既然是小型项目的话也没必要进行切库切表了,所以第二种方式属于知道即可,
  • 第三种方式不支持跳页,但是相比而言是更简洁的一种方式,最后一种方式的唯一缺陷在于需要查询两次sql
  • 最终项目的选择本人还是推荐第一种>第四总>第三种的.

• 至于DEMO的话应该会和rabbitMQ解决分布式数据一致性的demo一起放出

• 2018-10-27 更: 总算更新了… -

  • 核心思想就是利用map将所有的dao存储在内存中,同时利用@Order,和String类型的强字符串校验实现

  • 用的是之前封装的一个小扩展包架构,核心是基于内存map来控制的,简单介绍下类的包容关系:
    

• 批量查询的流程:

  • 查询分为两种:一种是主键唯一查询,另外一种是条件查询
  • 1.service需要传入的参数:pageSize(每次查询的数目),pageNum(查询的页码),Map<String,Ojbect>conditon(查询的条件),并且这些都会封装成一个了类同时会判断是否是signal,如果是则会直接主键查询,否则批量查询
  • 2.之后则是具体的逻辑:每个对应的service都会配备一个前缀名称,如user_0,user_1,user_2 这些前缀名都是user,service内部会接收这个前缀名和uniqueKey(充当主键),通过这2个参数返回一个wrapper,wrapper中是表的具体名字以及对应的dao
  • 3.获取到tableConcreteName(user_0)和dao之后,直接通过dao.直接主键查询或者条件查询即可

• 插入,删除,更新的流程:

  • 1.service传入对象实体(有uniqueKey的实体)
  • 2.根据tablePrefixName和uniqueKey进行插入或者删除

开始之前需要先预热填充数据:

测试代码:
	@Test
	public void testInsert()
	{
		long startTime=System.currentTimeMillis();
		log.info("[begin batch insert]");
		for(int i=0;i<10000;i++)
		{
			User user=new User();
			user.setUserId(i);
			user.setUserName("joker__"+i);
			int validCount=userService.insert(user);
			System.out.println(validCount);
		}
		// 请忽略for循环插入的bug :)
		log.info("[batch insert finished] consume :{} ms ",System.currentTimeMillis()-startTime);
	}
	结果:

测试结果:第5页


第15页:

结果都是预期的

代码已经更新到git上,配合
这个个人库更加方便
源码请拉到头部