一、水平分割
1、水平分库
11)、概念： 2以字段为依据，按照一定策略，将一个库中的数据拆分到多个库中。 32)、结果 4每个库的结构都一样；数据都不一样； 5所有库的并集是全量数据；
2、水平分表
11)、概念 2以字段为依据，按照一定策略，将一个表中的数据拆分到多个表中。 32)、结果 4每个表的结构都一样；数据都不一样； 5所有表的并集是全量数据；
二、Shard-jdbc 中间件
1、架构图
2、特点
11)、Sharding-JDBC直接封装JDBC API，旧代码迁移成本几乎为零。 22)、适用于任何基于Java的ORM框架，如Hibernate、Mybatis等 。 33)、可基于任何第三方的数据库连接池，如DBCP、C3P0、 BoneCP、Druid等。 44)、以jar包形式提供服务，无proxy代理层，无需额外部署，无其他依赖。 55)、分片策略灵活，可支持等号、between、in等多维度分片，也可支持多分片键。 66)、SQL解析功能完善，支持聚合、分组、排序、limit、or等查询。
三、项目演示
1、项目结构
1springboot 2.0 版本 2druid 1.1.13 版本 3sharding-jdbc 3.1 版本
2、数据库配置
1一台基础库映射（shard_one） 2两台库做分库分表（shard_two，shard_three）。 3表使用：table_one，table_two
3、核心代码块
数据源配置文件
1spring: 2 datasource: 3 # 数据源：shard_one 4 dataOne: 5 type: com.alibaba.druid.pool.DruidDataSource 6 druid: 7 driverClassName: com.mysql.jdbc.Driver 8 url: jdbc:mysql://localhost:3306/shard_one?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false 9 username: root 10 password: 123 11 initial-size: 10 12 max-active: 100 13 min-idle: 10 14 max-wait: 60000 15 pool-prepared-statements: true 16 max-pool-prepared-statement-per-connection-size: 20 17 time-between-eviction-runs-millis: 60000 18 min-evictable-idle-time-millis: 300000 19 max-evictable-idle-time-millis: 60000 20 validation-query: SELECT 1 FROM DUAL 21 # validation-query-timeout: 5000 22 test-on-borrow: false 23 test-on-return: false 24 test-while-idle: true 25 connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 26 # 数据源：shard_two 27 dataTwo: 28 type: com.alibaba.druid.pool.DruidDataSource 29 druid: 30 driverClassName: com.mysql.jdbc.Driver 31 url: jdbc:mysql://localhost:3306/shard_two?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false 32 username: root 33 password: 123 34 initial-size: 10 35 max-active: 100 36 min-idle: 10 37 max-wait: 60000 38 pool-prepared-statements: true 39 max-pool-prepared-statement-per-connection-size: 20 40 time-between-eviction-runs-millis: 60000 41 min-evictable-idle-time-millis: 300000 42 max-evictable-idle-time-millis: 60000 43 validation-query: SELECT 1 FROM DUAL 44 # validation-query-timeout: 5000 45 test-on-borrow: false 46 test-on-return: false 47 test-while-idle: true 48 connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 49 # 数据源：shard_three 50 dataThree: 51 type: com.alibaba.druid.pool.DruidDataSource 52 druid: 53 driverClassName: com.mysql.jdbc.Driver 54 url: jdbc:mysql://localhost:3306/shard_three?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false 55 username: root 56 password: 123 57 initial-size: 10 58 max-active: 100 59 min-idle: 10 60 max-wait: 60000 61 pool-prepared-statements: true 62 max-pool-prepared-statement-per-connection-size: 20 63 time-between-eviction-runs-millis: 60000 64 min-evictable-idle-time-millis: 300000 65 max-evictable-idle-time-millis: 60000 66 validation-query: SELECT 1 FROM DUAL 67 # validation-query-timeout: 5000 68 test-on-borrow: false 69 test-on-return: false 70 test-while-idle: true 71 connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
数据库分库策略
1/** 2 * 数据库映射计算 3 */ 4public class DataSourceAlg implements PreciseShardingAlgorithm<string> { 5 6 private static Logger LOG = LoggerFactory.getLogger(DataSourceAlg.class); 7 @Override 8 public String doSharding(Collection<string> names, PreciseShardingValue<string> value) { 9 LOG.debug("分库算法参数 {},{}",names,value); 10 int hash = HashUtil.rsHash(String.valueOf(value.getValue())); 11 return "ds_" + ((hash % 2) + 2) ; 12 } 13}
数据表1分表策略
1/** 2 * 分表算法 3 */ 4public class TableOneAlg implements PreciseShardingAlgorithm<string> { 5 private static Logger LOG = LoggerFactory.getLogger(TableOneAlg.class); 6 /** 7 * 该表每个库分5张表 8 */ 9 @Override 10 public String doSharding(Collection<string> names, PreciseShardingValue<string> value) { 11 LOG.debug("分表算法参数 {},{}",names,value); 12 int hash = HashUtil.rsHash(String.valueOf(value.getValue())); 13 return "table_one_" + (hash % 5+1); 14 } 15}
数据表2分表策略
1/** 2 * 分表算法 3 */ 4public class TableTwoAlg implements PreciseShardingAlgorithm<string> { 5 private static Logger LOG = LoggerFactory.getLogger(TableTwoAlg.class); 6 /** 7 * 该表每个库分5张表 8 */ 9 @Override 10 public String doSharding(Collection<string> names, PreciseShardingValue<string> value) { 11 LOG.debug("分表算法参数 {},{}",names,value); 12 int hash = HashUtil.rsHash(String.valueOf(value.getValue())); 13 return "table_two_" + (hash % 5+1); 14 } 15}
数据源集成配置
1/** 2 * 数据库分库分表配置 3 */ 4@Configuration 5public class ShardJdbcConfig { 6 // 省略了 druid 配置，源码中有 7 /** 8 * Shard-JDBC 分库配置 9 */ 10 @Bean 11 public DataSource dataSource (@Autowired DruidDataSource dataOneSource, 12 @Autowired DruidDataSource dataTwoSource, 13 @Autowired DruidDataSource dataThreeSource) throws Exception { 14 ShardingRuleConfiguration shardJdbcConfig = new ShardingRuleConfiguration(); 15 shardJdbcConfig.getTableRuleConfigs().add(getTableRule01()); 16 shardJdbcConfig.getTableRuleConfigs().add(getTableRule02()); 17 shardJdbcConfig.setDefaultDataSourceName("ds_0"); 18 Map<string,datasource> dataMap = new LinkedHashMap<>() ; 19 dataMap.put("ds_0",dataOneSource) ; 20 dataMap.put("ds_2",dataTwoSource) ; 21 dataMap.put("ds_3",dataThreeSource) ; 22 Properties prop = new Properties(); 23 return ShardingDataSourceFactory.createDataSource(dataMap, shardJdbcConfig, new HashMap<>(), prop); 24 } 25 26 /** 27 * Shard-JDBC 分表配置 28 */ 29 private static TableRuleConfiguration getTableRule01() { 30 TableRuleConfiguration result = new TableRuleConfiguration(); 31 result.setLogicTable("table_one"); 32 result.setActualDataNodes("ds_${2..3}.table_one_${1..5}"); 33 result.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new DataSourceAlg())); 34 result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new TableOneAlg())); 35 return result; 36 } 37 private static TableRuleConfiguration getTableRule02() { 38 TableRuleConfiguration result = new TableRuleConfiguration(); 39 result.setLogicTable("table_two"); 40 result.setActualDataNodes("ds_${2..3}.table_two_${1..5}"); 41 result.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new DataSourceAlg())); 42 result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new TableTwoAlg())); 43 return result; 44 } 45}
测试代码执行流程
1@RestController 2public class ShardController { 3 @Resource 4 private ShardService shardService ; 5 /** 6 * 1、建表流程 7 */ 8 @RequestMapping("/createTable") 9 public String createTable (){ 10 shardService.createTable(); 11 return "success" ; 12 } 13 /** 14 * 2、生成表 table_one 数据 15 */ 16 @RequestMapping("/insertOne") 17 public String insertOne (){ 18 shardService.insertOne(); 19 return "SUCCESS" ; 20 } 21 /** 22 * 3、生成表 table_two 数据 23 */ 24 @RequestMapping("/insertTwo") 25 public String insertTwo (){ 26 shardService.insertTwo(); 27 return "SUCCESS" ; 28 } 29 /** 30 * 4、查询表 table_one 数据 31 */ 32 @RequestMapping("/selectOneByPhone/{phone}") 33 public TableOne selectOneByPhone (@PathVariable("phone") String phone){ 34 return shardService.selectOneByPhone(phone); 35 } 36 /** 37 * 5、查询表 table_one 数据 38 */ 39 @RequestMapping("/selectTwoByPhone/{phone}") 40 public TableTwo selectTwoByPhone (@PathVariable("phone") String phone){ 41 return shardService.selectTwoByPhone(phone); 42 } 43}
四、项目源码
1GitHub地址：知了一笑 2https://github.com/cicadasmile/middle-ware-parent 3码云地址：知了一笑 4https://gitee.com/cicadasmile/middle-ware-parent
