mysql SQL进行取模运算 mysql取模分表

转载

云中谁寄锦书来 2024-01-06 17:14:05

文章标签 mysql SQL进行取模运算 mariaDB 关系型数据库分库分表数据库 mysql go语言 文章分类 MySQL 数据库

A.分库分表方法：

1. 哈希法，就是俗称取模法

2. 一致性哈希，是对方法1的改进。

3. range区间法

4. 查表法

总结：以上4种方法都需要一个或多个分库分表主键，通过主键并按分库库表规则计算
当前数据库操作对应到哪台具体数据库和表。

B. 设计概念

1. DBAtom : { ip, port ,user, pwd， factor， enable} 存储到map中, ip+port as map key.
这是一个扁平的map, 所有的master , slave一视同仁，都放到这个map中，供集中查询.

var DBAtomRepository [string] DBAtom //ip + port as map key.

2. DBGroup : {master: DBAtomname, slave: [DBAtomname...]} a slice,注意一个DBGroup中只充许有一个Master,即第一个为Master,其它皆为Slave;
一个DBGroup就是对应一个mysql master-slave集群；存储到map中，value为数组；当前集群规定皆是 one Master to 2 Slave.

var DBGroupRepository [string] DBGroup //uuid as map key, 可以配置中心统一生成，以保证唯一.

3. DBScaleOutScheme : {divide_key:"user_id", db_divide_sum:2 ,db_table_divide_sum: 2, dbgroupnames:[DBGroup_1, DBGroup_2,...]}
将scheme存储到Map中。

var DBScaleOutScheme [string] DBScaleOutScheme , dbname + tablename as map key.

count.dbgroupnames === 分库数 , 否则报错.

配置中心，以json数据形式下发配置，客户端则解析为相应的struct ， map.

B.1 B中的分库分表配置，存储分发方法：

1. 简单法，写成一个配置文件入到每一个应用服务器中。
2. 放入一个独立配置中心中，由配置中心分发给已注册的应用服务器中，不要每次都向配置中心查询，否则配置中心承受不住，形成单点故障。

C. 调用流程：

(1) select * where user_id = 2 from user_blog , 先要use db : user

|
|
v
(2) 拼装key, user_user_blog_scaleout_scheme 然后到3.中的map中找出scheme.

(3) 按取模法，分库主键 % DB_Divide_Count , 分表主键 % DB_Table_Divide_Count 找到相应的DBGroup,如果为写操作，则直接取出Master;
如果为读，则分库主键 % slave数量最终取到一个DBAtom ，并建立连接，也可以从连接池中取。

(4) 改写sql , 就是要改写表名。

(5) 将改写后的sql交给相应的DBAtom执行并返回结果。

（6）汇总数据，有时还要排序。

注意：有些sql语句中，没有涉及divide_key, 比如divide_key = "user_id" ,
但是my sql is : select * where sex=female from user
这种情况下，系统需要按scale scheme，向所有分库，发出请求，从每一个分表中取数据，最终汇总。
也就是说：增，删，改，查。查最复杂，其是1:n的关系，而前3个绝大多数是1:1的关系。

C.1 Data Access Layer API 设计

1. 封装为级一简明的接口以供应用逻辑调用，就像操作单数据库一样，透明。
2. DAL API initialize时，会启动独立的线程监听在指定端口，并向配置中心注册自已，请求本应用需要的分库分表配置信息。

D. 必需要解决的问题

数据库操作：增，删，改，查 [这4个操作：库名，表名一定是明确的，但是：分库分表“主键”就不一定提供了，此种情况理解为: 1:n关系，意为向所有分库分表下发sql请求]

1. 对于增操作，不能再依赖于数据库原来的自增主键了，要建立独立的全局系统负责分配全局唯一的ID.

2. 对于删，改操作，皆是操作已有数据记录，故按其ID分库分表没问题。

3. 对于查操作就麻烦了：如果其提供明确主键则一切Ok , 如果其是select * from table 或是其它查询条件但是条件中就是不包括主键，

此时意味着，DAL要向所有的分库及分表依次分发执行sql语句，并负责拼装缓存丛不同分库，分表返回来的数据，可能还要排序，可能还要分页；
第3点正是麻烦之处！！！需要下大功夫，但方向明确！！！

内存是有限的，分页的大小要设定适当，如果分库分表比较多，则DAL 不可能同时向所有分库分表下发sql ，因为返回的结果很可能撑爆内存，
所以要分批次，对于已收集回来的结果很可能还要排序，然后返回给应用一页， next时再返回下一页，当收集的缓存池中内容不多时，
再下发sql 给其它集群，并收集返回页，至到所有集群已分发完毕；但这其实就会有一个问题：不能将所有的数据页收集，而只对部分数据进进行排序，
那这个排序其实从全局来讲是乱序的！，但是为了内存空间，及速度，必需要有所舍弃！所以比较好的做法：不要在数据库中做复杂的大数据量的查询，
这事要交给搜索引擎去做！数据库就只做写和简单主键读取！

E. 配置中心

1. 监控每一个DBAtom是否Live。
2. 设定分库分表配置时，要及时下发给应用.
3. 如果是修改之前的分库分表配置：增加机器，减少机器，则配置中心不会当时下发配置，因为数据需要迁移，
比如slave增加，不需要迁移，只要下发配置就好，前提是监控其主从同步是否完成。如果是增加DBGroup数,及分库数
注意：两者必需一致，则需迁移数据，完成后才可以下发新配置.

迁移公式：原分库分表： db: 10 , table: 6 , divide_key: user_id; 分库: user_id % 10 == x, 分表: user_id % 6 == y
新分库分表: db: 20, table: 20, divide_key: user_id ; 分库: user_id % 20 == x1, 分表: user_id %20 == y1

将x, y标定的row 转存入 x1, y1标定的新row位置, 循环进行，走到完成, 最后下发新配置.

注意： 1.为了简单和性能，只支持单node事务。
2. 分析和改写sql , 可以采用第3方库，但可控性差，最好自己写一个sql ast解析.
其实只改写表名，因为分表了. 一个node 只允许一个库，所以库名唯一，不用改写。

3. 不允许join。 sql语句中不允许出现库名。
4. 只充许单机事务，不允许分布式事务.
5. 设计超简单：就4个接口：
(1) InitShard初始化【配置，etcd】
自动监视etcd, 如果配置变动则拉取配置, 解析DB shard配置信息为本地高效访问数据结构中，保存在内存中。

DBShardedInfo : {DBAtom, 表名，库名}

(2) shard2DB(库名，表名，shardKey, forcemaster, 读写) 返回：【DBShardedInfo】，error
(3) broadcast2DBs(库名，表名，shardKey, forcemaster, 读写) [[DBShardedInfo...], error]
由应用层，拼装排序结果集，当然首先也要由应用层根据当前内存，来决定同时向多个DB发出请求。
（4）complainDB(DBShardedInfo) 将此有问题db报告到etcd, 相关其它系统会关注并处理。

注意：增，删，改，此3种sql, 只允许shardKey相等性匹配，查，此sql允许范围匹配。
一切靠开发者自觉，也就是说开发者自己分析sql, 然后决定调用哪个接口，这很幼稚，但简单。

需要有一个系统专门用于监控每一个mysql的健康情况，同时可以接受下游系统的报告，主动去检查mysql的健康，向下游发出错误提示，更新配置于etcd
如果mysql出现健康问题，则启动异常处理逻辑，并向管理员发送通知。
slave出现问题，分库分表还可以支持读写，但是如果master出现问题，则系统不能再执行写操作。
对于出问题的DBAtom, enable=0, 正常工作的为1 。配置中心系统要及时下发变更配置。

在分库分表的基础之上，数据访问层，应该一并负责缓存管理，如果没有命中，则更新缓存，否则直接读取，
其实，数据有效期，一致性问题，都是与业务数据直接相关的，数据访问层应区别处理。

##对于数据迁移工具##

复制当前etcd中的分库分表集群配置，在etcd中开辟新的迁移节点保存当前分库分表集群配置，并在此基础上扩容/缩容，完成数据迁移后，将新的分库分表集群配置，
替换原来的，即可。

##无数据迁移方案##
one master : two slave ,

scale 1m to 2m

a. chose one slave.
b. break old link with old master, and change it to master , and add new two slave.
c. add one slave for old master , and sync data.
d. shardkey mod 2 = [0,1] , coz same data, so not move data.

new:0,1 => old 0

scale 2m to 4m

shardkey mod 4 = [0,1,2,3] new:0,1 => old 0; new:2,3 => old 1

scale 4m to 8m

shardkey mod 8 [0,1,2,3,4,5,6,7] new:0,1 => old 0; new:2,3 => old 1; new:4,5 => old 2; new:6,7 => old 3

基于相同的数据不需要迁移，到是可以删除重复数据，以节省空间。

##如何缓存数据##

1. 以何为key缓存数据

2. 缓存数据粒度

3. 缓存数据失效

4. 数据一致性