One 、 Sub database and sub table

Demand analysis ：

• Create database sharding_db1 and sharding_db1
• Create two tables in the database course_1 and course_2
• Database rules ： If the course id It's even , Then add sharding_db1 library , If it is an odd number, add it to sharding_db2 library .
• Data table rules ： If the course id It's even , Then add course_1 surface , If it is an odd number, add it to course_2 surface .

1、application.properties The configuration file

In the project application.properties Configuration in the configuration file .

#  Configure real data sources 
spring.shardingsphere.datasource.names=db1,db2
#  Configure the 1 Data sources 
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding_db1?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=123456
#  Configure the 2 Data sources 
spring.shardingsphere.datasource.db2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://localhost:3306/sharding_db2?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=123456

#  Specify the distribution of Library tables ： sub-treasury , table 
spring.shardingsphere.sharding.tables.course.actual-data-nodes=db$->{
   1..2}.course_$->{
   1..2}
#  Specifies the primary key generation policy for the table , Snowflake algorithm 
spring.shardingsphere.sharding.tables.course.key-generator.column=id
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1

# Specify the fragmentation policy 
#1. inline Fragmentation strategy 
#1.1  Cooper's strategy 
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=db$->{id%2+1}
#1.2  Table strategy 
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id%2+1}

#  open shardingsphere Of sql Log output .
spring.shardingsphere.props.sql.show=true

#  An entity class corresponds to two tables , Cover 
spring.main.allow-bean-definition-overriding=true

2、 Test code

1） preservation

    @Test
    public void testSave() throws InterruptedException {
    
        for (int i = 0; i < 20; i++) {
    
            Course course = new Course();
            course.setGmtCreate(new Date());
            course.setName("java");
            course.setUserId(1001L + i);
            course.setStatus("1");

            TimeUnit.MILLISECONDS.sleep(50);
            int count = courseMapper.insert(course);
            System.out.println("id ->" + course.getId());
        }

    }

The data will be stored according to our database and table rules ok.

Two 、 Fragmentation strategy

At present 5 Seed fragmentation strategy ：

• Line expression fragmentation strategy , Corresponding InlineShardingStrategy.
• Standard fragmentation strategy , Corresponding StandardShardingStrategy.
• Composite fragmentation strategy , Corresponding ComplexShardingStrategy.
• Hint Fragmentation strategy , Corresponding HintShardingStrategy.
• No fragmentation strategy , Corresponding NoneShardingStrategy.

1、 Line expression fragmentation strategy

Corresponding InlineShardingStrategy. Use Groovy The expression of , Provide right SQL Statement = and IN Fragment operation support for , Only single partition key is supported . For a simple sharding algorithm , It can be used through simple configuration .

1） stay application.properties Configuration in the configuration file .

#1. inline Fragmentation strategy 
#1.1  Cooper's strategy 
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=db$->{id%2+1}
#1.2  Table strategy 
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id%2+1}

2、 Standard fragmentation strategy

Corresponding StandardShardingStrategy. Provide right SQL Statement =, >, <, >=, <=, IN and BETWEEN AND Fragment operation support for .StandardShardingStrategy Only single partition key is supported ,StandardShardingStrategy Only single partition key is supported , Provide PreciseShardingAlgorithm and RangeShardingAlgorithm Two sharding algorithms .

1） stay application.properties Configuration in the configuration file

#2. standard Standard fragmentation strategy ： in eq between
#2.1  Cooper's strategy 
spring.shardingsphere.sharding.tables.course.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.standard.precise-algorithm-class-name=com.charge.learn.shardingsphere.jdbc.study.sharding.strategy.standard.MyPreciseTableShardingAlgorithm
spring.shardingsphere.sharding.tables.course.table-strategy.standard.range-algorithm-class-name=com.charge.learn.shardingsphere.jdbc.study.sharding.strategy.standard.MyRangeTableShardingAlgorithm
##2.2  Cooper's strategy 
spring.shardingsphere.sharding.tables.course.database-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.course.database-strategy.standard.precise-algorithm-class-name=com.charge.learn.shardingsphere.jdbc.study.sharding.strategy.standard.MyPreciseDSShardingAlgorithm
spring.shardingsphere.sharding.tables.course.database-strategy.standard.range-algorithm-class-name=com.charge.learn.shardingsphere.jdbc.study.sharding.strategy.standard.MyRangeDSShardingAlgorithm

The partition strategy of Library and table needs to be customized .

2） Library policy algorithm

/** *  User defined extended precise segmentation algorithm  */
public class MyPreciseDSShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    

    /** * @param availableTargetNames  The name of a valid data source or table . Corresponding to the data source information configured in the configuration file  * @param shardingValue  contain   Logical table name 、 Values of fragment column and fragment column . * @return  Return the target result  */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
    
        // Realization db$->{id%2+1}  Sub library strategy 
        BigInteger shardingValueB = BigInteger.valueOf(shardingValue.getValue());
        BigInteger resB = (shardingValueB.mod(new BigInteger("2"))).add(new BigInteger("1"));
        String key =  "db"+resB ;
        if(availableTargetNames.contains(key)){
    
            return key;
        }
        throw new UnsupportedOperationException(" route "+key+" is not supported. please check your config");
    }
}

/** *  Custom extended range slicing algorithm . */
public class MyRangeDSShardingAlgorithm implements RangeShardingAlgorithm<Long> {
    

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
    
        return availableTargetNames;
    }
}

3） Table policy algorithm

public class MyPreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
    
        // Implement according to  =  or  IN  Perform accurate segmentation .
        // for example  select * from course where id = 1 or id in (1,3,5)
        // Realization course_$->{cid%2+1}  Tabulation strategy 
        BigInteger shardingValueB = BigInteger.valueOf(shardingValue.getValue());
        BigInteger resB = shardingValueB.mod(new BigInteger("2"))
                .add(new BigInteger("1"));
        String key =  shardingValue.getLogicTableName()+"_"+resB ;
        if(availableTargetNames.contains(key)){
    
            return key;
        }
        throw new UnsupportedOperationException(" route "+key+" is not supported. please check your config");
    }
}

public class MyRangeTableShardingAlgorithm implements RangeShardingAlgorithm<Long> {
    

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
    
        // Implement according to  Between  Perform range slicing .
        // for example  select * from course where id between 2000 and 3000;
        Long lowerEndpoint = shardingValue.getValueRange().lowerEndpoint();//2000
        Long upperEndpoint = shardingValue.getValueRange().upperEndpoint();//3000
        // Realization course_$->{(3000 -2000 )%2+1}  Fragmentation strategy 
// return Arrays.asList(shardingValue.getLogicTableName()+"_"+shardingValue.getLogicTableName() + ((upperEndpoint - lowerEndpoint) % 2 + 1));
        // For our scenario of parity separation , Most range queries require both tables to be queried .
        return Arrays.asList(shardingValue.getLogicTableName()+"_1",shardingValue.getLogicTableName()+"_2");
    }
}

4） Test the query

3、 Composite fragmentation strategy

Corresponding ComplexShardingStrategy. Composite fragmentation strategy . Provide right SQL Statement =, >, <, >=, <=, IN and BETWEEN AND Fragment operation support for .ComplexShardingStrategy Support multi partition key , Because of the complexity of the relationship between multi partition keys , So there's not much encapsulation , But directly pass the key value combination and fragment operator to the fragment algorithm , It's fully implemented by application developers , Provide maximum flexibility .

1） stay application.properties Configuration in the configuration file

#3. complex Complex fragmentation strategy ： Complex queries 
##3.1  Cooper's strategy 
spring.shardingsphere.sharding.tables.course.table-strategy.complex.sharding-columns= cid, user_id
spring.shardingsphere.sharding.tables.course.table-strategy.complex.algorithm-class-name=com.charge.learn.shardingsphere.jdbc.study.sharding.strategy.complex.MyComplexTableShardingAlgorithm
##3.2  Cooper's strategy 
spring.shardingsphere.sharding.tables.course.database-strategy.complex.sharding-columns=cid, user_id
spring.shardingsphere.sharding.tables.course.database-strategy.complex.algorithm-class-name=com.charge.learn.shardingsphere.jdbc.study.sharding.strategy.complex.MyComplexDSShardingAlgorithm

2） Policy algorithm

public class MyComplexDSShardingAlgorithm  implements ComplexKeysShardingAlgorithm<Long> {
    

    /** * * @param availableTargetNames  Target data source   perhaps   surface   Value . * @param shardingValue logicTableName Logical table name  columnNameAndShardingValuesMap  The exact set of values for a partitioned column . columnNameAndRangeValuesMap  The range value set of the sliced column  * @return */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
    
        return availableTargetNames;
    }
}

public class MyComplexTableShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
    
    
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
    
        // Implement according to  Between  Perform range slicing .
        // for example  select * from course where id in (1,3,5) and user_id Between 200 and 300;

        Collection<Long> cidCol = shardingValue.getColumnNameAndShardingValuesMap().get("id");
        Range<Long> uageRange = shardingValue.getColumnNameAndRangeValuesMap().get("user_id");

        List<String> result = new ArrayList<>();

        Long lowerEndpoint = uageRange.lowerEndpoint();//200
        Long upperEndpoint = uageRange.upperEndpoint();//300
        // Implement custom sharding logic   For example, you can implement it yourself  course_$->{cid%2+1}
        for(Long id : cidCol){
    
            BigInteger idI = BigInteger.valueOf(id);
            BigInteger target = (idI.mod(BigInteger.valueOf(2L))).add(new BigInteger("1"));
            result.add("course_"+target);
        }

        return result;
    }
}

4、Hint Fragmentation strategy

Corresponding HintShardingStrategy. adopt Hint Specifies the value of the slice instead of from SQL The method of extracting the partition value in the partition strategy .

Note that there are many limitations when using it .

1） stay application.properties Configuration in the configuration file

#4. hint Enforce routing policy ： And sql irrelevant 
##4.1  Cooper's strategy 
spring.shardingsphere.sharding.tables.course.table-strategy.hint.algorithm-class-name=com.charge.learn.shardingsphere.jdbc.study.sharding.strategy.hint.MyHintTableShardingAlgorithm
##4.2  Cooper's strategy 
spring.shardingsphere.sharding.tables.course.database-strategy.hint.algorithm-class-name=com.charge.learn.shardingsphere.jdbc.study.sharding.strategy.hint.MyHintTableShardingAlgorithm

2） Policy algorithm

public class MyHintDSShardingAlgorithm implements HintShardingAlgorithm<Integer> {
    
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Integer> shardingValue) {
    
        return Arrays.asList("db1","db2");
    }
}
public class MyHintTableShardingAlgorithm implements HintShardingAlgorithm<Integer> {
    
    /** * * @param availableTargetNames  Optional   data source   and   surface   The name of  * @param shardingValue * @return */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Integer> shardingValue) {
    
        //  Yes SQL Zero intrusion fragmentation scheme .shardingValue It's through HintManager.
        //  For example, we want to achieve  select * from t_user where user_id in {1,2,3,4,5,.....};  according to in First value of , Route all to course_1 In the table . 
        String key = "course_"+shardingValue.getValues().toArray()[0];
        if(availableTargetNames.contains(key)){
    
            return Arrays.asList(key);
        }
// return Arrays.asList("course_1");
        throw new UnsupportedOperationException(" route "+key+" is not supported. please check your config");
    }
}

Fragmentation strategy , Sometimes for sql custom , Therefore, there are certain restrictions in the use process .
See the official website ： https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/sql/

Reference article ：

• ShardingSphere Official website ：https://shardingsphere.apache.org/document/current/cn/overview/
• ShardingSphere Basic introduction and core concepts ：https://blog.csdn.net/Kiven_ch/article/details/119087048

– Stay Hungry , stay foolish .