开言:原理和介绍就不说啦。看官方文档 http://shardingsphere.apache.org/
1: 配置:
sharding: jdbc: datasource: names: message0,message1,message2 # 数据源ds0 message0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://10.10.10.201:3306/yuba?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=true&serverTimezone=GMT%2B8 username: momcome password: momcome # 数据源ds1 时间如果相差八小时 加上此参数serverTimezone=GMT%2B8 message1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://10.10.10.201:3306/yuba-message0?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=true&serverTimezone=GMT%2B8 username: momcome password: momcome message2: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://10.10.10.201:3306/yuba-message1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=true&serverTimezone=GMT%2B8 username: momcome password: momcome hikari: # 最小空闲链接数 minimumIdle: 50 # 最大链接数 maximumPoolSize: 400 # 最大生命周期 maxLifetime: 180000 # 最长闲置时间 idleTimeout: 60000 # 等待连接池的最大毫秒数 connectionTimeout: 20000 config: sharding: props: sql.show: true #是否打印日志 tables: yun_xin_all_msg_t: #yun_xin_all_msg_t表 actual-data-nodes: message$->{0..2}.yun_xin_all_msg_t_$->{0..3} #数据节点,均匀分布 database-strategy: #分库策略 standard: precise-algorithm-class-name: com.yuba.message.service.sharding.MyPreciseDatabaseMessageShardingAlgorithm #按模运算分配 精确分片算法 sharding-column: created_date table-strategy: #分表策略 standard: precise-algorithm-class-name: com.yuba.message.service.sharding.MyPreciseShardingAlgorithm #按模运算分配 精确分片算法 sharding-column: created_date yun_xin_customer_msg_t: #yun_xin_customer_msg_t表 actual-data-nodes: message$->{0..2}.yun_xin_customer_msg_t_$->{0..3} #数据节点,均匀分布 database-strategy: #分库策略 standard: precise-algorithm-class-name: com.yuba.message.service.sharding.MyPreciseDatabaseMessageShardingAlgorithm #按模运算分配 精确分片算法 分库策略 sharding-column: created_date table-strategy: #分表策略 standard: precise-algorithm-class-name: com.yuba.message.service.sharding.MyPreciseShardingAlgorithm #按模运算分配 精确分片算法 分表策略 sharding-column: created_date yun_xin_msg_t: #yun_xin_all_msg_t表 #key-generator-column-name: id #主键 注释掉主键 就可以使用String作为主键类型 否则 强制必须使用Number类型 actual-data-nodes: message$->{0..2}.yun_xin_msg_t_$->{0..3} #数据节点,均匀分布 database-strategy: #分库策略 standard: precise-algorithm-class-name: com.yuba.message.service.sharding.MyPreciseDatabaseMessageShardingAlgorithm #按模运算分配 精确分片算法 sharding-column: created_date table-strategy: #分表策略 standard: precise-algorithm-class-name: com.yuba.message.service.sharding.MyPreciseShardingAlgorithm #按模运算分配 精确分片算法 sharding-column: created_date user_visitor_t: #user_visitor_t actual-data-nodes: message$->{0..2}.user_visitor_t_$->{0..7} #数据节点,均匀分布 5张物理表 database-strategy: #分库策略 standard: precise-algorithm-class-name: com.yuba.message.service.sharding.MyPreciseDatabaseUserVisitShardingAlgorithm #按模运算分配 精确分片算法 sharding-column: id table-strategy: #分表策略 standard: precise-algorithm-class-name: com.yuba.message.service.sharding.MyPreciseUserVisitShardingAlgorithm #按模运算分配 精确分片算法 sharding-column: id user_login_history: #user_visitor_t actual-data-nodes: message$->{0..2}.user_login_history_$->{0..6} #数据节点,均匀分布 7张张物理表 database-strategy: #分库策略 standard: precise-algorithm-class-name: com.yuba.message.service.sharding.MyPreciseDatabaseUserLoginShardingAlgorithm #按模运算分配 精确分片算法 sharding-column: uid table-strategy: #分表策略 standard: precise-algorithm-class-name: com.yuba.message.service.sharding.MyPreciseUserLoginShardingAlgorithm #按模运算分配 精确分片算法 sharding-column: uid
2: 对应的分片算法:
按时间分片 每个月对应一张表进行存储
/** * 自定义精确分片算法 消息记录的分库算法 * @author */ @Slf4j @Component public class MyPreciseDatabaseMessageShardingAlgorithm implements PreciseShardingAlgorithm<String> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) { String id= shardingValue.getValue(); Date date= TimeUtil.stringConvertDate(shardingValue.getValue()); int month = DateUtil.month(date) + 1; //根据时间来计算出表 for (String dabaseName : availableTargetNames) { String indexStr = dabaseName.substring(dabaseName.lastIndexOf("e") + 1); int index = Integer.parseInt(indexStr); if(1<=month&&month<=4){// 1 到 4 表示 message0库 if(index==0){ return dabaseName; } }else if(5<=month&&month<=8){// 5 到 8 表示 message1库 if(index==1){ return dabaseName; } }else if(9<=month&&month<=12){// 9 到 12 表示 message2库 if(index==2){ return dabaseName; } } } return "message0";//如果没有数据源 默认为message0 数据源 避免程序出错 } }
@Slf4j public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<String> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) { log.info("availableTargetNames : " + availableTargetNames); Date date= TimeUtil.stringConvertDate(shardingValue.getValue()); int month = DateUtil.month(date) + 1;// 月份 对号入座 1 到4 对应表 0 到3 依次类推 for (String tableName : availableTargetNames) { //4 张表记性取模计算 String indexStr = tableName.substring(tableName.lastIndexOf("_") + 1); int index = Integer.parseInt(indexStr); if(NumberConvert.convertDatabaseMessageMonth(month)==index){ return tableName; } } throw new IllegalArgumentException(); } }
按id进行分片
根据自己制定的规则 当mysql数据表中数据达到一定的量 自动切换数据源 如果表不够用 自动添加库和表
@Slf4j public class MyPreciseDatabaseUserVisitShardingAlgorithm implements PreciseShardingAlgorithm<String> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) { String id= shardingValue.getValue(); String prefix=(String)id.subSequence(0, 1); Integer flag= NumberConvert.getVisitFlagByPrefix(prefix); for (String dabaseName : availableTargetNames) { String indexStr = dabaseName.substring(dabaseName.lastIndexOf("e") + 1); int index = Integer.parseInt(indexStr); if(0<=flag&&flag<8){//0 到7 代表在message0数据源 if(index==0){ return dabaseName; } } else if(8<=flag&&flag<16){//8 到15 代表在message1数据源 if(index==1){ return dabaseName; } } else if(16<=flag&&flag<24){//8 到15 代表在message2数据源 暂时就定义这么几个数据表 if(index==2){ return dabaseName; } } } return "message0";//如果没有数据源 默认为message0 数据源 避免程序出错 } }
@Slf4j @Component public class MyPreciseUserVisitShardingAlgorithm implements PreciseShardingAlgorithm<String> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) { String id= shardingValue.getValue(); String prefix=(String)id.subSequence(0, 1); for (String tableName : availableTargetNames) { String indexStr = tableName.substring(tableName.lastIndexOf("_") + 1); int index = Integer.parseInt(indexStr); if(index== NumberConvert.getVisitTableByPrefix(prefix).intValue()){//切换到对应的数据表 return tableName; } } throw new IllegalArgumentException(); } }
按照查询条件最多的键作为分片键 特点 大大优化查询速度 但是存储表数据会随机,不方便清楚
@Slf4j public class MyPreciseDatabaseUserLoginShardingAlgorithm implements PreciseShardingAlgorithm<String> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) { String uid= shardingValue.getValue(); if(StringUtils.isNotEmpty(uid)) { String prefix = (String) uid.substring(uid.length() -2,uid.length()); int flag=Integer.parseInt(prefix)%3;//根据uid进行分库 分为3个库 每个库7张表 for (String dabaseName : availableTargetNames) { String indexStr = dabaseName.substring(dabaseName.lastIndexOf("e") + 1); int index = Integer.parseInt(indexStr); if(index==flag){ return dabaseName; } } } return "message0";//如果没有数据源 默认为message0 数据源 避免程序出错 } }
@Slf4j @Component public class MyPreciseUserLoginShardingAlgorithm implements PreciseShardingAlgorithm<String> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) { log.info("MyPreciseUserVisitShardingAlgorithm : " + availableTargetNames); String uid= shardingValue.getValue(); if(StringUtils.isNotEmpty(uid)) { String prefix = (String) uid.substring(uid.length() -2,uid.length()); int flag=Integer.parseInt(prefix)%7;//根据uid进行分库 分为3个库 每个库7张表 for (String tableName : availableTargetNames) { String indexStr = tableName.substring(tableName.lastIndexOf("_") + 1); int index = Integer.parseInt(indexStr); if(index==flag){ return tableName; } } } return "user_login_history_0";//如uid为空 则默认从第0数据源 0表 } }
涉及工具类:
/** * 用户数字对应转换 * **/ public class NumberConvert { public static Integer convertDatabaseMessageMonth(Integer flag){//直接月份转换成对应的表 switch(flag){ case 1:return 0; case 2:return 1; case 3:return 2; case 4:return 3; case 5:return 0; case 6:return 1; case 7:return 2; case 8:return 3; case 9:return 0; case 10:return 1; case 11:return 2; case 12:return 3; } return 0; } //废弃 public static Integer getUserVisit(Integer flag,BigInteger redisNumber){ if(redisNumber.compareTo(SystemConfig.DATABASE_NUMBER)>=0){//满足500万条数据啦。直接切换数据源 int returnFlag=flag+1; return returnFlag; } return flag;//不满足 则直接返回 } public static String getPrefixByFlag(Integer flag){//计时器 对应主键改造 返回主键前缀 switch(flag){ case 0:return ""; case 1:return "A"; case 2:return "B"; case 3:return "C"; case 4:return "D"; case 5:return "E"; case 6:return "F"; case 7:return "G"; case 8:return "H"; case 9:return "I"; case 10:return "J"; case 11:return "K"; case 12:return "L"; case 13:return "M"; case 14:return "N"; case 15:return "O"; case 16:return "P"; case 17:return "Q"; case 18:return "R"; case 19:return "S"; case 20:return "T"; case 21:return "U"; case 22:return "V"; case 23:return "W"; case 24:return "X"; case 25:return "Y"; case 26:return "Z"; case 27:return ""; case 28:return ""; case 29:return ""; case 30:return ""; case 31:return ""; } return ""; } /** * 对应说明 没有前缀 和 A 到G 对应message0 0 到7 的表 * H 到 O 对应 message1 0 到7的表 * P到W 对应 message2 0 到7的表 * **/ public static Integer getVisitTableByPrefix(String prefix){//如果后期有其他的库的话 继续往后面累加 switch(prefix){ case "A":return 1; case "B":return 2; case "C":return 3; case "D":return 4; case "E":return 5; case "F":return 6; case "G":return 7; case "H":return 0; case "I":return 1; case "J":return 2; case "K":return 3; case "L":return 4; case "M":return 5; case "N":return 6; case "O":return 7; case "P":return 0; case "Q":return 1; case "R":return 2; case "S":return 3; case "T":return 4; case "U":return 5; case "V":return 6; case "W":return 7; case "X":return 0; case "Y":return 1; case "Z":return 2; } return 0; } /** * 作为判断条件转换 A 到Z 分辨对应 1 到26 * 对应说明 没有前缀 和 A 到G 对应message0 0 到7 的表 * H 到 O 对应 message1 0 到7的表 * P到W 对应 message2 0 到7的表 后面不够的方便累加 * 切换成数字 方便判断逻辑 0 代表message0 中的 0表 * **/ public static Integer getVisitFlagByPrefix(String prefix){// switch(prefix){ case "A":return 1; case "B":return 2; case "C":return 3; case "D":return 4; case "E":return 5; case "F":return 6; case "G":return 7; case "H":return 8; case "I":return 9; case "J":return 10; case "K":return 11; case "L":return 12; case "M":return 13; case "N":return 14; case "O":return 15; case "P":return 16; case "Q":return 17; case "R":return 18; case "S":return 19; case "T":return 20; case "U":return 21; case "V":return 22; case "W":return 23; case "X":return 24; case "Y":return 25; case "Z":return 26; } return 0; } }