This is the 20th day of my participation in the August More Text Challenge

The premise is introduced

ShardingSphere introduction

ShardingSphere is an ecosystem of open source distributed database middleware solutions, which is composed of three independent products, Sharding-JDBC, Sharding-Proxy and Sharding-Sidecar (planned). They all provide standardized data sharding, distributed transaction and database governance functions, which can be applied to diverse application scenarios such as Java isomorphism, heterogeneous languages, cloud native and so on.

ShardingJDBC scope used

  • Works with any JDBC-based ORM framework, such as JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC.
  • Support any third party database connection pool, such as DBCP, C3P0, BoneCP, Druid, HikariCP, etc.
  • Support for any database that implements the JDBC specification. Currently supports MySQL, Oracle, SQLServer, PostgreSQL and any database that complies with the SQL92 standard.

Detail introduction directly see website: shardingsphere.apache.org/document/cu…

SQL statement correlation

  • Logical table: A collective name for tables of the same logical and data structure in a horizontally split database (table). Example: The order data is split into two tables, t_ORDER_0 to T_ORDER_1, based on the manda of the primary key, and their logical table name is T_ORDER.

  • Real table: A physical table that actually exists in a sharded database. Example: T_order_0 to t_order_1 in the example

  • Data node: The smallest unit of a data fragment. It consists of a data source name and a data table, for example, ds_0.t_order_0. Ds_0. T_order_1;

  • Binding table: a primary table and a sub-table whose sharding rules are consistent. For example, if both t_ORDER and T_order_item are sharded according to order_ID, the two tables are bound to each other. Cartesian product association does not appear in multi-table associated query between bound tables, which greatly improves the efficiency of associated query.

  • Broadcast table: a table that exists in all shard data sources and has exactly the same structure and data in the table in each database. This method is recommended when the data volume is small and associated query is required with massive data tables.

Data sharding correlation

  • Sharding key: A database field used for sharding, which is the key field for splitting a database (table) horizontally. For example, if the mantissa of the order primary key in the order table is modulo sharded, the order primary key is a sharding field.

If there is no fragment field in SQL, full routing is performed, resulting in poor performance. In addition to the support for single sharding fields, ShardingSphere also supports sharding based on multiple fields.

  • Sharding algorithm: Data is sharded using the sharding algorithm. Data can be sharded by =, >=, <=, >, <, BETWEEN, and IN. Application developers need to implement the sharding algorithm by themselves, which has high flexibility.

Currently, four sharding algorithms are available

  • PreciseShardingAlgorithm: corresponds to the PreciseShardingAlgorithm, which is used to process the scenario where = and IN are sharded using a single key as the sharding key. Needs to be used in conjunction with the StandardShardingStrategy.
  • RangeShardingAlgorithm: corresponds to the RangeShardingAlgorithm, which is used to process the scenarios where BETWEEN AND, >, <, >= AND <= are sharded using a single key as the sharding key. Needs to be used in conjunction with the StandardShardingStrategy.
  • Composite subdivision algorithm: corresponding ComplexKeysShardingAlgorithm, used for processing using multiple key as a shard key shard, contains more than one shard key, the complexity of the logic to application developers to deal with the complexity. It needs to work with ComplexShardingStrategy.
  • HintSharding algorithm: Corresponds to the HintShardingAlgorithm, which is used to handle the scenarios where Hint row sharding is used. It needs to be used together with HintShardingStrategy.

Sharding strategy: contains the sharding key and the sharding algorithm. Because the sharding algorithm is independent, it is removed independently. The real sharding operation is sharding key + sharding algorithm, also known as sharding strategy.

Currently, five sharding strategies are available

  • StandardShardingStrategy: corresponding to StandardShardingStrategy, it supports sharding operations for SQL statements IN =, >, <, >=, <=, IN AND BETWEEN AND.

    • StandardShardingStrategy only supports single sharding key and provides PreciseShardingAlgorithm and RangeShardingAlgorithm.
      • PreciseShardingAlgorithm is mandatory for sharding = and IN.
      • RangeShardingAlgorithm is optional for BETWEEN AND, >, <, >=, <= sharding. RangeShardingAlgorithm is not configured. BETWEEN AND in SQL will be processed according to the full library routing.

  • Compound sharding strategy: corresponds to a ComplexShardingStrategy. Supports sharding of =, >, <, >=, <=, IN AND BETWEEN AND IN SQL statements.

    • The ComplexShardingStrategy supports multi-shard keys, which are not encapsulated because of their complex relationships.
    • Instead, sharding key-value combinations and sharding operators are directly passed through to the sharding algorithm, completely implemented by the application developer, providing maximum flexibility.

  • Row expression sharding strategy: corresponds to InlineShardingStrategy. Use Groovy expressions to provide support for sharding = and IN IN SQL statements. Only single sharding keys are supported.

  • Simple sharding algorithms can be configured simply to avoid tedious Java code development, for example:t_user_$->{u_id % 8} The t_user table is divided into 8 tables based on u_id mod 8. The table names are T_user_0 to t_user_7.

  • HintShardingStrategy: corresponds to HintShardingStrategy. The strategy for sharding by Hint specifying shard values rather than extracting them from SQL.

  • Non-sharding Strategy: corresponds to NoneShardingStrategy.

Configuration related

Sharding rule: The general entry for configuring sharding rules. The configuration includes data source configuration, table configuration, binding table configuration, and read/write separation configuration.

  • Data source configuration: List of real data sources.
  • Table configuration: Configuration of logical table names, data nodes, and sub-table rules
  • Data node configuration: Used to configure the mapping between logical tables and real tables.
  • Sharding policy configuration:
    • Data source sharding strategy: corresponds to the DatabaseShardingStrategy. Used to configure the target data source to which data is allocated.
    • TableShardingStrategy: corresponding to TableShardingStrategy. Used to configure the target table to which the data is allocated, which exists in the target data source associated with the data. Therefore, the table sharding strategy depends on the results of the data source sharding strategy.
  • Auto-add primary key generation policy: Generates auto-add primary keys on the client instead of native auto-add primary keys in the database, ensuring no duplication of distributed primary keys. (Snowflake algorithm)

Development steps

Development integration approach

Method 1: Configuration file-based integration is convenient but not flexible

	<! Application. Properties ();
	<dependency>
		<groupId>io.shardingsphere</groupId>
		<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
		<version>3.1.0. M1</version>
	</dependency>
	<dependency>
		<groupId>io.shardingsphere</groupId>
		<artifactId>sharding-jdbc-spring-namespace</artifactId>
		<version>3.1.0. M1</version>
	</dependency>
Copy the code

Method 2: Here we are mainly based on Java Config to integrate into SpringBoot, more suitable for learning and understanding

// Dependencies<dependency>
	<groupId>io.shardingsphere</groupId>
	<artifactId>sharding-jdbc-core</artifactId>
	<version>3.1.0</version>
</dependency>
<! --<dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-transaction-2pc-xa</artifactId> The < version > 3.1.0 < / version > < / dependency > -- >
<dependency>
	<groupId>io.shardingsphere</groupId>
	<artifactId>sharding-jdbc-orchestration</artifactId>
	<version>3.1.0</version>
</dependency>
<dependency>
	<groupId>io.shardingsphere</groupId>
	<artifactId>sharding-orchestration-reg-zookeeper-curator</artifactId>
	<version>3.1.0</version>
</dependency>
Copy the code

Define the related configuration class (DataSourceConfig => MybatisConfig => TransactionConfig)

ShardingSphereDataSourceConfig
import javax.sql.DataSource;
import java.lang.management.ManagementFactory;
import java.sql.SQLException;
import java.util.*;

/ * * *@Author zhangboqing
 * @Date2020/4/25 * /
@Configuration
@Slf4j
public class ShardingSphereDataSourceConfig {

    @Bean("shardingDataSource")
    DataSource getShardingDataSource(a) throws SQLException {
        // Initializes the information control mechanism for related sharding rules
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // Set the relevant data source
        shardingRuleConfig.setDefaultDataSourceName("ds0");
       // Set the rule information configuration mechanism for the related Order table
        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
       // Set the related rule information configuration mechanism for the related OrderItem table
        shardingRuleConfig.getTableRuleConfigs().add(getOrderItemTableRuleConfiguration());
        // Configure the binding table relationship
        shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");
        // Broadcast table operation mechanism
        shardingRuleConfig.getBroadcastTables().add("t_config");
        // Set the related sharding mechanism policy (data source sharding policy mechanism control)
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id"."ds${user_id % 2}"));
        // Set the related sharding policy mechanism, including inline mode (including both modes).
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(getShardingStrategyConfiguration());
        / / ShardingPropertiesConstant related configuration options
        Properties properties = new Properties();
        // Whether to print SQL parsing and rewriting logs
        properties.put("sql.show".true);
       // The number of worker threads used for SQL execution. Zero indicates unlimited
        propertie.setProperty("executor.size"."4");
       // The maximum number of connections allocated per query per physical database
        propertie.setProperty("max.connections.size.per.query"."1");
       // Whether to check the consistency of sub-table metadata at startup
        propertie.setProperty("check.table.metadata.enabled"."false");
		// User-defined attributes
        Map<String, Object> configMap = new HashMap<>();
        configMap.put("effect"."Database and table");
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties);
    }

    // Configure the sharding policy mechanism
    private ShardingStrategyConfiguration getShardingStrategyConfiguration(a){
        // Exact match
        PreciseShardingAlgorithm<Long> preciseShardingAlgorithm = new PreciseShardingAlgorithm<Long>() {
            @Override
            public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
                String prefix = shardingValue.getLogicTableName(); // Logical table name
                Long orderId = shardingValue.getValue(); // Order number
                long index = orderId % 2; // Order table (routing index)
                // t_order + "" + 0 = t_order0
                String tableName = prefix + "" +index;
                // Precise queries, updates, etc., can return nonexistent tables and throw exceptions and warnings to the front end.
                if (availableTargetNames.contains(tableName) == false) {
                    LogUtils.error(log,"PreciseSharding"."OrderId :{}, no corresponding database table {}!", orderId, tableName);
                    return availableTargetNames.iterator().next();
                }
                return tableName;
// return availableTargetNames.iterator().next();}};// Range matches
        RangeShardingAlgorithm<Long> rangeShardingAlgorithm = new RangeShardingAlgorithm<Long>() {
            @Override
            public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
                String prefix = shardingValue.getLogicTableName();
                Collection<String> resList = new ArrayList<>();
                // Get the relevant data value range
                Range<Long> valueRange = shardingValue.getValueRange();
                // If there is no upper limit or lower limit, return all tables directly
                if(! valueRange.hasLowerBound() || ! valueRange.hasUpperBound()) {return availableTargetNames;
                }
               // Get the lower data range
                long lower = shardingValue.getValueRange().lowerEndpoint();
                BoundType lowerBoundType = shardingValue.getValueRange().lowerBoundType();
               // Get the lower data range
                long upper = shardingValue.getValueRange().upperEndpoint();
                BoundType upperBoundType = shardingValue.getValueRange().upperBoundType();
               // Indicates the lower limit
                long startValue = lower;
                long endValue = upper;
               // Open interval (lower limit)
                if (lowerBoundType.equals(BoundType.OPEN)) {
                    startValue++; // Reduce the scope by 1
                }
               // Whether it belongs to the open range (upper limit)
                if (upperBoundType.equals(BoundType.OPEN)) {
                    endValue--; // Reduce the scope by 1
                }
               // The entity table is required to perform calculations
                for (long i = startValue; i <= endValue ; i++) {
                    long index = i % 2;
                    String res = prefix + "" +index;
                    // Precise queries, updates, etc., can return nonexistent tables and throw exceptions and warnings to the front end.
                    if (availableTargetNames.contains(res) == false) {
                        LogUtils.error(log,"RangeSharding"."OrderId :{}, no corresponding database table {}!", i, res);
                    }else{ resList.add(res); }}if (resList.size() == 0) {
                    LogUtils.error(log,"RangeSharding"."Could not get the corresponding table, so will query the full table! OrderId ranges from {} to {}",startValue,endValue);
                    return availableTargetNames;
                }
                returnresList; }};// Set the algorithm consolidation of the related whole
        ShardingStrategyConfiguration strategyConf = new StandardShardingStrategyConfiguration("order_id", preciseShardingAlgorithm, rangeShardingAlgorithm);
        return strategyConf;
    }

    // Get the relevant Order rule table configuration information control Configure the control mechanism
    TableRuleConfiguration getOrderTableRuleConfiguration(a) {
        // Logical table + actual node: Set the mapping mechanism between logical tables and data nodes (the smallest unit of data fragments)
        TableRuleConfiguration result = new TableRuleConfiguration("t_order"."ds${0.. 1}.t_order${0.. 1}");
        // Primary key generation configuration
        result.setKeyGeneratorConfig(getKeyGeneratorConfigurationForTOrder());
        return result;
    }
    // Generate strategy for primary key operations
    private KeyGeneratorConfiguration getKeyGeneratorConfigurationForTOrder(a) {
        Properties keyGeneratorProp = getKeyGeneratorProperties();
        return new KeyGeneratorConfiguration("SNOWFLAKE"."order_id", keyGeneratorProp);
    }

    // Get the relevant Order rule table configuration information control Configure the control mechanism
    TableRuleConfiguration getOrderItemTableRuleConfiguration(a) {
        TableRuleConfiguration result = new TableRuleConfiguration("t_order_item"."ds${0.. 1}.t_order_item${0.. 1}");
        result.setKeyGeneratorConfig(getKeyGeneratorConfigurationForTOrderItem());
        return result;
    }

    // Create the related keyOrderItem mechanism control operation
	private KeyGeneratorConfiguration getKeyGeneratorConfigurationForTOrderItem(a) {
        Properties keyGeneratorProp = getKeyGeneratorProperties();
        return new KeyGeneratorConfiguration("SNOWFLAKE"."id", keyGeneratorProp);
    }

  // Generate configuration information control for key-value dependent generators
    private Properties getKeyGeneratorProperties(a) {
        Properties keyGeneratorProp = new Properties();
        String distributeProcessIdentify = NetUtils.getLocalAddress() + ":" + getProcessId();
        String workId = String.valueOf(convertString2Long(distributeProcessIdentify));
        keyGeneratorProp.setProperty("worker.id", workId);
        LogUtils.info(log, "shardingsphere init"."shardingsphere work id raw string is {}, work id is {}", distributeProcessIdentify, workId);
        return keyGeneratorProp;
    }

    // Data source configuration mechanism
    Map<String, DataSource> createDataSourceMap(a) {
        Map<String, DataSource> result = new HashMap<>();
        result.put("ds0", DataSourceUtils.createDataSource("ds0"));
        result.put("ds1", DataSourceUtils.createDataSource("ds1"));
        return result;
    }

	// Common associated workerIDS and dataid correspond to associated process ids
    private String getProcessId(a){
        String name = ManagementFactory.getRuntimeMXBean().getName();
        String pid = name.split("@") [0];
        return pid;
    }

	// Convert the string to the associated long type
    private Long convertString2Long(String str){
        long hashCode = str.hashCode() + System.currentTimeMillis();
        if(hashCode < 0){
            hashCode = -hashCode;
        }
        return hashCode % (1L << 10); }}Copy the code
ShardingsphereMybatisConfig configuration mechanism
/ * * *@Author zhangboqing
 * @Date2020/4/23 * /
@Configuration
@MapperScan(basePackages = "com.zbq.springbootshardingjdbcjavaconfigdemo.dao",sqlSessionFactoryRef = "sqlSessionFactoryForShardingjdbc")
public class ShardingsphereMybatisConfig {
    @Autowired
    @Qualifier("shardingDataSource")
    private DataSource dataSource;
    @Bean("sqlSessionFactoryForShardingjdbc")
    public SqlSessionFactory sqlSessionFactoryForShardingjdbc(a) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
// sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().
// getResources("classpath*:**/*.xml"));
        sessionFactory.setTypeAliasesPackage("com.zbq.springbootshardingjdbcjavaconfigdemo.domain.entity");
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);
        sessionFactory.setConfiguration(configuration);
        returnsessionFactory.getObject(); }}Copy the code
ShardingsphereTransactionConfig configuration mechanism

The main custom configuration transaction operation can be air-to-future, extended XA for future queries


@Configuration
@EnableTransactionManagement
public class ShardingsphereTransactionConfig {
    @Bean
    @Autowired
    public PlatformTransactionManager shardingsphereTransactionManager(@Qualifier("shardingDataSource") DataSource dataSource) {
        return newDataSourceTransactionManager(dataSource); }}Copy the code