一些补充的知识点-MySQL大表分库分表基因法

场景

1.确认需求：一张订单表，三个字段：user_id，order_id，amount；假设一天会产生10亿的数据，现在需要根据user_id 和 order_id查询数据；

2.需求分析：数据库磁盘、cpu必然压力巨大，需要分库+分表；

索引表法

1. 10个库+每个库100张表，平均每张表每天会产生100w的数据，这样每张表每个月就会产生3000w的数据，在这个表中我们可以至保留一个月的数据，其余的数据归档至数据仓库，比如我们需要olap场景，就归档至clickhouse、es等；
2. 我们需要根据user_id、order_id，这两个字段对订单进行查询，我们先对order_id进行分片键设计；
3. hash(order_id) % 10 得到库，hash(order_id) % 100 的到表
4. 为了能通过user_id查询order_id，需要额外建一张user_id->order_id的索引表，这个索引表也需要根据user_id作为分片键进行相应的分库分表；
5. 这样我们通过order_id能够直接定位数据库的表进行查询，通过user_id先查order_id再通过order_id再查具体内容，满足需求；
6. 上面的做法可以实现需求，但是通过user_id查询订单时，需要多进行一次查询，效率降低了一倍；并且索引表也需要进行分库分表，当然索引也可以考虑其他存储介质，如Hbase，或者增加缓存来提高索引效率；如果需要某个用户订单列表的话，还需要在应用层做数据整合，很麻烦；

基因法

1. 我们考虑有没有一种做法，可以把一个用户的所有订单数据都落到同一个库同一个表中，这样不管是通过user_id定位，还是通过order_id都能准确定位到数据，解决了引入索引表的复杂性，并且解决了应用层整合数据的麻烦；
2. 解析一下基因法的思想，我们希望达到的效果是，一个用户的所有数据都落到同一个库中的同一个表，那么我们可以先对user_id进行取余，落库，然后在生成order_id时就不能随便生成了，需要从user_id中提取基因，在生成order_id的时候，把这个基因放到order_id的生成过程中，这样生成出来的order_id通过取余等运算就能得到和user_id一致的结果了，也就是达到了同一个用户的所有数据都落到同一个库的同一个表中的效果；
3. 现在我们把焦点集中在了“基因”这个点上，我们先来看看一个数a对另外一个数b(数b为2^n)进行取余时，其实本质上最后的结果就是a这个数二进制的最后（n+1）位，举个例子：9%4 = 1（1001 % 100 = 001）/ 10 % 4 = 2 （1010 % 100 = 010），那么我们在生成订单id 的时候，只要把order_id二进制的最后（n+1）位的二进制数设置为user_id的最后（n+1）位，那么我们对user_id/order_id取余都能得到相同的结果了。（原理：比n+1位高的值，都是b数的倍数，取余时直接归零，所以取余就是取二进制最后n+1位）
4. 了解原理后，我们只需要重新合理设计分库分表的数量，让其都是 2^n，我们重设 16个库每个库64张表 ；
5. hash(user_id）% 16 定位库的位置， hash(user_id）% 64 定位表的位置
6. 生成order_id ，对user_id提取一个基因 % 64 也就是二进制最后 7位，把这个最后7位二进制也作为order_id的二进制最后7位，这样就能保证order_id的路由结果与user_id完全一致；
7. 通过基因法，不管是通过order_id查询数据，还是通过user_id查询数据，都能准确定位到具体的表，效率高；