sharding
按照业务拆分的方式称为垂直分片，又称为纵向拆分，它的核心理念是专库专用。
水平切分水平分片又称为横向拆分。 相对于垂直分片，它不再将数据根据业务逻辑分类，而是通过某个字段（或某几个字段），根据某种规则将数据分散至多个库或表中，每个分片仅包含数据的一部分。 例如：根据主键分片，偶数主键的记录放入0库（或表），奇数主键的记录放入1库（或表）
Sharding-JDBC简介定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。
适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL。Sharding-JDBC采用无中心化架构，适用于Java开发的高性能的轻量级OLTP应用；
功能列表 分库 & 分表读写分离分布式主键 引入依赖<dependency> <groupId>io.shardingjdbc</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>2.0.3</version> </dependency> 12345
选择2.0.3作为实践版本，是因为1.4.2之前版本需要依赖druid解析，1.5.2之后采用自研解析方式，并且版本对比来说1.5.2之后性能更加稳定。不采用最新版本3.1.0.M1的原因是由于新版本性能相对而言还不太稳定，不适合在生产环境使用。
规则配置Sharding-JDBC可以通过Java，YAML，Spring命名空间和Spring Boot Starter四种方式配置，开发者可根据场景选择适合的配置方式。详情请参见配置手册。
分片算法通过分片算法将数据分片，支持通过=、BETWEEN和IN分片。分片算法需要应用方开发者自行实现，可实现的灵活度非常高。
目前提供4种分片算法。由于分片算法和业务实现紧密相关，因此并未提供内置分片算法，而是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法。
精确分片算法
对应PreciseShardingAlgorithm，用于处理使用单一键作为分片键的=与IN进行分片的场景。需要配合StandardShardingStrategy使用。
范围分片算法
对应RangeShardingAlgorithm，用于处理使用单一键作为分片键的BETWEEN AND进行分片的场景。需要配合StandardShardingStrategy使用。
复合分片算法
对应ComplexKeysShardingAlgorithm，用于处理使用多键作为分片键进行分片的场景，包含多个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度。需要配合ComplexShardingStrategy使用。
Hint分片算法
对应HintShardingAlgorithm，用于处理使用Hint行分片的场景。需要配合HintShardingStrategy使用。
spring.jpa.database=mysql spring.jpa.show-sql=true spring.jpa.hibernate.ddl-auto=none 123 分表分库数据源
@Configuration public class DataSourceConfig { /** * 分表 * * @return 数据源 * @throws SQLException 数据库异常 */ @Bean public DataSource userShardingDataSource() throws SQLException { ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); // 单库分表 TableRuleConfiguration result = new TableRuleConfiguration(); result.setLogicTable("t_user"); result.setActualDataNodes("ds0.t_user${0..2}"); result.setKeyGeneratorColumnName("user_id"); result.setKeyGeneratorClass(MySqlKeyGenerator.class.getName()); result.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", ModuloShardingDatabaseAlgorithm.class.getName(), ModuloShardingDatabaseAlgorithm.class.getName())); result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", ModuloShardingTableAlgorithm.class.getName(), ModuloShardingTableAlgorithm.class.getName())); TableRuleConfiguration result2 = new TableRuleConfiguration(); result2.setLogicTable("t_order"); result2.setActualDataNodes("ds${0..1}.t_order${[0,1]}"); result2.setKeyGeneratorColumnName("order_id"); result2.setKeyGeneratorClass(RedisKeyGenerator.class.getName()); result2.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", ModuloShardingDatabaseAlgorithm.class.getName(), ModuloShardingDatabaseAlgorithm.class.getName())); result2.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", ModuloShardingTableAlgorithm.class.getName(), ModuloShardingTableAlgorithm.class.getName())); TableRuleConfiguration result3 = new TableRuleConfiguration(); result3.setLogicTable("t_order_item"); result3.setActualDataNodes("ds${0..1}.t_order_item${[0,1]}"); result3.setKeyGeneratorColumnName("order_item_id"); result3.setKeyGeneratorClass(RedisKeyGenerator.class.getName()); result3.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", ModuloShardingDatabaseAlgorithm.class.getName(), ModuloShardingDatabaseAlgorithm.class.getName())); result3.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", ModuloShardingTableAlgorithm.class.getName(), ModuloShardingTableAlgorithm.class.getName())); shardingRuleConfig.getTableRuleConfigs().add(result); shardingRuleConfig.getTableRuleConfigs().add(result2); shardingRuleConfig.getTableRuleConfigs().add(result3); // 配置表关联 shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item"); Map<String, DataSource> dbMap = new HashMap<>(2); dbMap.put("ds0", DataSourceUtil.dataSource("ds0")); dbMap.put("ds1", DataSourceUtil.dataSource("ds1")); Properties properties = new Properties(); properties.put("sql.show", true); return ShardingDataSourceFactory.createDataSource(dbMap, shardingRuleConfig, new HashMap<>(1), properties); } }
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970 选库算法@Slf4j public final class ModuloShardingDatabaseAlgorithm implements PreciseShardingAlgorithm<Long>, RangeShardingAlgorithm<Long> { /** * 选库 */ @Override public String doSharding(final Collection<String> databaseNames, final PreciseShardingValue<Long> shardingValue) { log.info("databaseNames:{}", JSON.toJSONString(databaseNames)); log.info("shardingValue:{}", JSON.toJSONString(shardingValue)); String databaseName = ""; // 只做单库分表 if ("t_user".equalsIgnoreCase(shardingValue.getLogicTableName())) { databaseName = (String) databaseNames.toArray()[0]; } // 分库分表 if ("t_order".equalsIgnoreCase(shardingValue.getLogicTableName()) || "t_order_item".equalsIgnoreCase(shardingValue.getLogicTableName())) { for (String each : databaseNames) { if (each.endsWith(shardingValue.getValue() % databaseNames.size() + "")) { databaseName = each; break; } } } log.info("databaseName:{}", databaseName); if (StringUtils.isNotEmpty(databaseName)) { return databaseName; } throw new UnsupportedOperationException(); } /** * 实现between and查询 */ @Override public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) { log.info("collection:{}", JSON.toJSONString(collection)); log.info("rangeShardingValue:{}", JSON.toJSONString(rangeShardingValue)); Collection<String> collect = new ArrayList<>(); Range<Long> valueRange = rangeShardingValue.getValueRange(); log.info("valueRange:{}", JSON.toJSONString(valueRange)); log.info("lowerEndpoint:{}", valueRange.lowerEndpoint()); log.info("upperEndpoint:{}", valueRange.upperEndpoint()); for (Long i = valueRange.lowerEndpoint(); i <= valueRange.upperEndpoint(); i++) { for (String each : collection) { if (each.endsWith(i % collection.size() + "")) { collect.add(each); } } } log.info("collect:{}", JSON.toJSONString(collect)); return collect; } }
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465 选表算法@Slf4j public final class ModuloShardingTableAlgorithm implements PreciseShardingAlgorithm<Long>, RangeShardingAlgorithm<Long> { /** * 选表 */ @Override public String doSharding(final Collection<String> tableNames, final PreciseShardingValue<Long> shardingValue) { log.info("tableNames:{}", JSON.toJSONString(tableNames)); log.info("shardingValue:{}", JSON.toJSONString(shardingValue)); String tableName = ""; if ("t_user".equalsIgnoreCase(shardingValue.getLogicTableName())) { for (String each : tableNames) { if (each.endsWith(shardingValue.getValue() % tableNames.size() + "")) { log.info("table:{}", each); tableName = each; break; } } } // 分库分表 if ("t_order".equalsIgnoreCase(shardingValue.getLogicTableName()) || "t_order_item".equalsIgnoreCase(shardingValue.getLogicTableName())) { for (String each : tableNames) { if (each.endsWith(shardingValue.getValue() % tableNames.size() + "")) { tableName = each; break; } } } log.info("tableName:{}", tableName); if (StringUtils.isNotEmpty(tableName)) { return tableName; } throw new UnsupportedOperationException(); } /** * 实现between and查询 */ @Override public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) { log.info("collection:{}", JSON.toJSONString(collection)); log.info("rangeShardingValue:{}", JSON.toJSONString(rangeShardingValue)); Collection<String> collect = new ArrayList<>(); Range<Long> valueRange = rangeShardingValue.getValueRange(); for (Long i = valueRange.lowerEndpoint(); i <= valueRange.upperEndpoint(); i++) { for (String each : collection) { if (each.endsWith(i % collection.size() + "")) { collect.add(each); } } } return collect; } }
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364 主键策略默认使用雪花算法（snowflake）生成64bit的长整型数据。如果在请求并发小的情况下会出现所生产的主键都为偶数。有时候我们需要自增主键，就需要自定义主键成策略。
自定义分布式redis主键public class RedisKeyGenerator implements KeyGenerator { private RedisClient client = RedisClient.create(RedisURI.Builder.redis("192.168.97.57",6379).withTimeout(Duration.ofMillis(6000)).withPassword("666666").withDatabase(1).build()); private GenericObjectPool<StatefulRedisConnection<String, String>> pool = ConnectionPoolSupport.createGenericObjectPool(() -> client.connect(), getGenericObjectPoolConfig()); public RedisKeyGenerator() { } private GenericObjectPoolConfig getGenericObjectPoolConfig() { GenericObjectPoolConfig config = new GenericObjectPoolConfig(); config.setMaxIdle(8); config.setMinIdle(0); config.setMaxTotal(8); config.setMaxWaitMillis(-1); return config; } @Override public synchronized Number generateKey() { try (StatefulRedisConnection<String, String> connection = pool.borrowObject()) { RedisAsyncCommands<String, String> commands = connection.async(); RedisFuture<Long> future = commands.incr("id"); return future.get(); } catch (Exception e) { e.printStackTrace(); return System.currentTimeMillis(); } } @Override protected void finalize() throws Throwable { if (!pool.isClosed()){ pool.close(); } super.finalize(); } }
1234567891011121314151617181920212223242526272829303132333435363738 自定义mysql自增主键建立表t_generate_key
CREATE TABLE IF NOT EXISTS ds0.t_generate_key (user_id BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (user_id) ); 12345
代码
public class MySqlKeyGenerator implements KeyGenerator { private DataSource dataSource; private String sql = "insert into t_generate_key()values();"; public MySqlKeyGenerator() { this.dataSource = DataSourceUtil.dataSource("ds0"); } @Override public synchronized Number generateKey() { try (Connection conn = dataSource.getConnection(); Statement statement = conn.createStatement()) { statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); ResultSet resultSet = statement.getGeneratedKeys(); if (resultSet.next()) { return resultSet.getLong(1); } } catch (Exception e) { return System.currentTimeMillis(); } return System.currentTimeMillis(); } }
123456789101112131415161718192021222324 源码下载spring-boot-jpa-sharding-jdbc
参考 性能测试报告源码示例参考网址: sharding https://m.mcbbbk.com/newsview1338207.html
| 上一篇: Linux编译安装nginx详细 |
下一篇: Apache安装apr和apr |
