Sharding - JDBC branch table 4 sharding strategies, quite simple

The basic concepts of Sharding-JDBC and a simple case of data Sharding are introduced in the “Quick Start Database and Table Middleware Sharding-JDBC (Compulsory Course)” above, but the actual development scenario is far more complex than this. We will choose the corresponding data sharding strategy according to the different operators >, <, between and, in, etc.

Before we move on, let’s take a question. Two days ago, a friend asked a question in private and said:

What if PART of my table is detabulated and part of my table is not detabulated? How can I access it normally?

This is a typical problem. As we know, sub-database sub-table is aimed at some tables whose data volume continues to grow greatly, such as user table and order table, rather than making sharding for all tables. So there are two general solutions to how to divide non-sharded tables and sharded tables.

Function libraries are strictly divided, sharded libraries are separated from non-sharded libraries, and data source access is switched as needed in business codes
Set the default data source toSharding-JDBCFor example, if we don’t set sharding rules for unsharded tables, they won’t be executed because we can’t find routing rules. In this case, we set a default data source and access the default library all the time if we can’t find rules.

Configure data source DS0
spring.shardingsphere.datasource.ds- 0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds- 0.driverClassName=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds- 0.url=jdbc:mysql://47.946.. 5:3306/ds0? useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.shardingsphere.datasource.ds- 0.username=root
spring.shardingsphere.datasource.ds- 0.password=Root # default data source, the fragmentation of table the default execution library spring. Shardingsphere. Sharding. Default-data-source-name=ds0
Copy the code

In this article, we will practice the use of the four sharding strategies for specific SQL usage scenarios, and do some preparatory work before starting.

Standard Sharding strategy
Compound sharding strategy
Row expression sharding policy
Hint Sharding strategy

The preparatory work

First, create two databases DS-0 and DS-1, and build six tables T_ORDER_0, T_ORDER_1, T_ORDER_2, T_ORDER_ITEM_0, T_ORDER_ITEM_1 and T_ORDER_ITEM_2 in the two databases respectively. Let’s see how to apply the four sharding strategies of Sharding – JDBC in different scenarios.

T_order_n table structure:

CREATE TABLE `t_order_0` (
  `order_id` bigint(200) NOT NULL,
  `order_no` varchar(100) DEFAULT NULL,
  `user_id` bigint(200) NOT NULL,
  `create_name` varchar(50) DEFAULT NULL,
  `price` decimal(10.2) DEFAULT NULL.PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Copy the code

T_order_item_n table structure:

CREATE TABLE `t_order_item_0` (
  `item_id` bigint(100) NOT NULL,
  `order_id` bigint(200) NOT NULL,
  `order_no` varchar(200) NOT NULL,
  `item_name` varchar(50) DEFAULT NULL,
  `price` decimal(10.2) DEFAULT NULL.PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Copy the code

Sharding strategy is divided into shard strategy and shard strategy, and they implement sharding algorithm in basically the same way, the difference is that there is a library DS-0, DS-1, and a table T_ORDER_0 ··· T_ORDER_N to process.

Standard Sharding strategy

Usage scenario: The SHARding policy can be applied to SQL statements that have the >, >=, <=, <, =, IN AND BETWEEN AND operators.

StandardShardingStrategy, which only supports shard entries based on a single shard, The PreciseShardingAlgorithm and RangeShardingAlgorithm are also provided.

IN the use of standard sharding strategy, accurate sharding algorithm is the algorithm must be implemented, used for SQL with = and IN sharding processing; The range sharding algorithm is optional AND is used to process sharding containing BETWEEN AND.

Once range sharding algorithm is not configured AND BETWEEN AND or like is used in SQL, then SQL will be executed one by one in the way of full library AND table routing, AND the query performance will be poor, requiring special attention.

Next, define the implementation of precise sharding algorithm and range sharding algorithm.

1. Accurate sharding algorithm

1.1 Accurate library sorting algorithm

The implementation of custom precise sub-database, sub-table algorithm is roughly the same, all to achieve PreciseShardingAlgorithm interface, rewrite doSharding() method, but the configuration is slightly different, and it is only an empty method, we have to deal with sub-database, sub-table logic. The same is true for other sharding strategies.

SELECT * FROM t_order where  order_id = 1 or order_id in （1.2.3);Copy the code

In the following section, we implement the precise branch strategy. By taking the mode of the shard key order_id, we calculate the SQL path to which library, and the calculated shard library information will be stored in the shard context, which is convenient for the subsequent use of the branch table.

/ * * *@author [Xiaofu public id] *@description Customize standard repository strategy *@date 2020/10/30 glorifying the * /
public class MyDBPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    @Override
    public String doSharding(Collection<String> databaseNames, PreciseShardingValue<Long> shardingValue) {

        /** * databaseNames shardingValue is the sharding attribute, where logicTableName is the logical table, columnName is the sharding key, and value is the sharding key value resolved from SQL */
        for (String databaseName : databaseNames) {
            String value = shardingValue.getValue() % databaseNames.size() + "";
            if (databaseName.endsWith(value)) {
                returndatabaseName; }}throw newIllegalArgumentException(); }}Copy the code

The Collection

parameter is used consistently in several sharding policies. The value of the parameter is databaseNames of all the sharding libraries. PreciseShardingValue is the sharding attribute, where logicTableName is the logical table, columnName is the sharding key (field), and value is the value of the sharding key resolved from the SQL.

In the application.properties configuration file, you only need to change database-strategy to standard mode. Standard. Precise -algorithm-class-name indicates the class path of the precise database segmentation algorithm.

# # # # depots strategy depots shard health spring. Shardingsphere. Sharding. Name t_order. Database-strategy.standard.sharding-column=Order_id # depots subdivision algorithm spring. Shardingsphere. Sharding. Name t_order. Database-strategy.standard.precise-algorithm-class-name=com.xiaofu.sharding.algorithm.dbAlgorithm.MyDBPreciseShardingAlgorithm
Copy the code

1.2 Accurate sub-table algorithm

PreciseShardingAlgorithm interface is also implemented for precision table algorithm, and doSharding() method is rewritten.

/ * * *@author [Xiaofu public id] *@description Customize standard sub-table policy *@date 2020/10/30 glorifying the * /
public class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    @Override
    public String doSharding(Collection<String> tableNames, PreciseShardingValue<Long> shardingValue) {

        /** * shardingValue specifies the sharding attribute, where logicTableName specifies the logical table, columnName specifies the sharding key, Value is the value of the sharding key parsed from SQL */
        for (String tableName : tableNames) {
            /** ** module algorithm, fragment health % table number */
            String value = shardingValue.getValue() % tableNames.size() + "";
            if (tableName.endsWith(value)) {
                returntableName; }}throw newIllegalArgumentException(); }}Copy the code

When dividing tables, the Collection

parameter is the sharded database calculated above. TablesNames of all corresponding sharded tables. PreciseShardingValue is the sharding attribute, where logicTableName is the logical table, columnName is the sharding key (field), and value is the value of the sharding key resolved from the SQL.

The application. Properties configuration file also needs to change the name of database-strategy to the standard mode. Standard. Precise -algorithm-class-name indicates the class path of the precise table algorithm.

# # table strategy table fragmentation health spring. Shardingsphere. Sharding. Name t_order. Table-strategy.standard.sharding-column=Order_id # table algorithm spring. Shardingsphere. Sharding. Name t_order. Table-strategy.standard.precise-algorithm-class-name=com.xiaofu.sharding.algorithm.tableAlgorithm.MyTablePreciseShardingAlgorithm

Copy the code

See this is not difficult to find that the implementation of custom sub-library and sub-table algorithm is basically the same, so we will only demonstrate sub-library

2. Range sharding algorithm

Usage scenario: This algorithm is used when the BETWEEN AND operator is used in the SHard key field in SQL. It processes the shard AND table logic according to the shard key range values given in SQL.

SELECT * FROM t_order where  order_id BETWEEN 1 AND 100;
Copy the code

The custom RangeShardingAlgorithm needs to implement the interface of RangeShardingAlgorithm and rewrite the doSharding() method. Below, I calculate the logic of each sub-library and sub-table by traversing the sharding key value interval.

/ * * *@author xinzhifu
 * @description Range sorting algorithm *@date 2020/11/2 12:06 * /
public class MyDBRangeShardingAlgorithm implements RangeShardingAlgorithm<Integer> {

    @Override
    public Collection<String> doSharding(Collection<String> databaseNames, RangeShardingValue<Integer> rangeShardingValue) {

        Set<String> result = new LinkedHashSet<>();
        // Start value of between and
        int lower = rangeShardingValue.getValueRange().lowerEndpoint();
        int upper = rangeShardingValue.getValueRange().upperEndpoint();
        // loop scope to calculate branch logic
        for (int i = lower; i <= upper; i++) {
            for (String databaseName : databaseNames) {
                if (databaseName.endsWith(i % databaseNames.size() + "")) { result.add(databaseName); }}}returnresult; }}Copy the code

RangeShardingValue RangeShardingValue indicates the starting value lowerEndpoint indicates the end value. RangeShardingValue indicates the starting value lowerEndpoint indicates the end value.

In terms of configuration, range sharding algorithm and precision sharding algorithm are both used under standard sharding policy. Therefore, you only need to add range-algorithm-class-name to user-defined range sharding algorithm classpath.

# precise subdivision algorithm spring. Shardingsphere. Sharding. Name t_order. Database-strategy.standard.precise-algorithm-class-name=Com. Xiaofu. Sharding. Algorithm. DbAlgorithm. MyDBPreciseShardingAlgorithm # range subdivision algorithm spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.range-algorithm-class-name=com.xiaofu.sharding.algorithm.dbAlgorithm.MyDBRangeShardingAlgorithm
Copy the code

Compound sharding strategy

Usage scenario: SQL statements contain operators such as >, >=, <=, <, =, IN, AND BETWEEN AND. The compound sharding policy supports multiple sharding health operations.

Below, we implement a custom compound sharding strategy using both order_id and user_id as sharding keys.

 SELECT * FROM t_order where  user_id =0  and order_id = 1;
Copy the code

Sharding -columns complex. Sharding -columns complex. Sharding -columns complex. Sharding -columns complex. Complex. algorithm-class-name was replaced by our self-defined compound sharding algorithm.

# # # # order_id depots strategy, user_id health spring. At the same time as the depots divided shardingsphere. Sharding. Name t_order. Database-strategy.complex.sharding-column=Order_id, user_id # composite subdivision algorithm spring shardingsphere. Sharding. Name t_order. Database-strategy.complex.algorithm-class-name=com.xiaofu.sharding.algorithm.dbAlgorithm.MyDBComplexKeysShardingAlgorithm

Copy the code

Custom composite subdivision strategy to achieve ComplexKeysShardingAlgorithm interface, doSharding again () method.

/ * * *@author [Xiaofu public id] *@description Custom compound repository strategy *@date 2020/10/30 glorifying the * /
public class MyDBComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Integer> {


    @Override
    public Collection<String> doSharding(Collection<String> databaseNames, ComplexKeysShardingValue<Integer> complexKeysShardingValue) {

        // Get the value of each shard key
        Collection<Integer> orderIdValues = this.getShardingValue(complexKeysShardingValue, "order_id");
        Collection<Integer> userIdValues = this.getShardingValue(complexKeysShardingValue, "user_id");

        List<String> shardingSuffix = new ArrayList<>();
        // Divide the library into two fragment keys
        for (Integer userId : userIdValues) {
            for (Integer orderId : orderIdValues) {
                String suffix = userId % 2 + "_" + orderId % 2;
                for (String databaseName : databaseNames) {
                    if(databaseName.endsWith(suffix)) { shardingSuffix.add(databaseName); }}}}return shardingSuffix;
    }

    private Collection<Integer> getShardingValue(ComplexKeysShardingValue<Integer> shardingValues, final String key) {
        Collection<Integer> valueSet = new ArrayList<>();
        Map<String, Collection<Integer>> columnNameAndShardingValuesMap = shardingValues.getColumnNameAndShardingValuesMap();
        if (columnNameAndShardingValuesMap.containsKey(key)) {
            valueSet.addAll(columnNameAndShardingValuesMap.get(key));
        }
        returnvalueSet; }}Copy the code

Collection

is a ComplexKeysShardingValue, which supports multiple shard keys. A map with a shard key as its key and a shard key as its value stores shard key attributes.

Row expression sharding policy

The Line expression sharding Strategy, which uses Groovy expressions IN the configuration to provide support for sharding = and IN IN SQL statements, supports only single sharding keys.

The line expression sharding strategy is suitable for simple sharding algorithm. It does not need to define the sharding algorithm and saves the tedious code development. It is the simplest of several sharding strategies.

Its configuration is fairly compact, and this sharding policy uses inline-algorithm-expression to write expressions.

Ds -$->{order_id % 2}; ds-$->{order_id % 2}; ds-$->{order_id % 2}

Line # expression shard key sharding. JDBC. Config. Sharding. Name t_order. Database-strategy.inline.sharding-column=Order_id # expression algorithm sharding. JDBC. Config. Sharding. Name t_order. Database-strategy.inline.algorithm-expression=ds-$->{order_id % 2}
Copy the code

Hint Sharding strategy

HintShardingStrategy Is slightly different from the preceding sharding strategies. This sharding strategy does not require the configuration of sharding keys, and the sharding keys are not parsed from SQL. Instead, the SHARding information is externally specified, and SQL is executed in the specified shard database and shard table. The ShardingSphere implements the specified operation through the Hint API, which in effect changes the sharding rules Tablerule and Databaserule from centralized configuration to personalized configuration.

For example, if we want the order table T_ORDER to be shard key with user_id, but the table t_ORDER does not have user_id, we can manually specify shard keys or shard libraries externally using the Hint API.

Let’s see how we can specify a shard key to route to the specified library table.

SELECT * FROM t_order;
Copy the code

Hint sharding strategy also requires customization. Implement the HintShardingAlgorithm interface and override the doSharding() method.

/ * * * @ author xinzhifu * * @ @ description hit table algorithm date 2020/11/2 12:06 * / public class MyTableHintShardingAlgorithm implements HintShardingAlgorithm<String> { @Override public Collection<String> doSharding(Collection<String> tableNames,  HintShardingValue<String> hintShardingValue) { Collection<String> result = new ArrayList<>(); for (String tableName : tableNames) { for (String shardingValue : hintShardingValue.getValues()) { if (tableName.endsWith(String.valueOf(Long.valueOf(shardingValue) % tableNames.size()))) { result.add(tableName); } } } return result; }}Copy the code

The custom algorithm is only partially implemented, but also needs to specify the sub-database and sub-table information through HintManager before calling SQL. Since each rule is added to ThreadLocal, clear() must be executed to remove the previous rule, otherwise an error will be reported. AddDatabaseShardingValue Sets the sharding key value of a branch table. AddTableShardingValue Sets the sharding key value of a branch table. SetMasterRouteOnly Forcibly reads the master library for read/write separation to avoid delay caused by master/slave replication.

// Clear the previous rule, otherwise an error will be reported
HintManager.clear();
// HintManager API utility class instance
HintManager hintManager = HintManager.getInstance();
// Specify the database directly
hintManager.addDatabaseShardingValue("ds".0);
// Set the sharding key for the table
hintManager.addTableShardingValue("t_order" , 0);
hintManager.addTableShardingValue("t_order" , 1);
hintManager.addTableShardingValue("t_order" , 2);

// In a read-write separation database, Hint can force reads from the primary library
hintManager.setMasterRouteOnly();

Copy the code

Table t_order (HintShardingValue);

Properties file, you do not need to specify the sharding key. You only need to specify the Hint sharding algorithm classpath.

# Hint subdivision algorithm spring. Shardingsphere. Sharding. Name t_order. Table-strategy.hint.algorithm-class-name=com.xiaofu.sharding.algorithm.tableAlgorithm.MyTableHintShardingAlgorithm
Copy the code

Next, we will implement sharding-JDBC functions one by one, such as distributed transactions, service management, etc. In the next article, we will look at how to customize distributed and Increment primary key IDS for sub-tables.

GitHub address: github.com/chengxy-nds…

I sorted out hundreds of technical e-books and gave them to my friends. Pay attention to the public number reply [666] to get yourself. I set up a technology exchange group with some friends to discuss technology and share technical information, aiming to learn and progress together. If you are interested, please join us!

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Sharding – JDBC branch table 4 sharding strategies, quite simple

The preparatory work

Standard Sharding strategy

1. Accurate sharding algorithm

1.1 Accurate library sorting algorithm

1.2 Accurate sub-table algorithm

2. Range sharding algorithm

Compound sharding strategy

Row expression sharding policy

Hint Sharding strategy

Sharding – JDBC branch table 4 sharding strategies, quite simple

The preparatory work

Standard Sharding strategy

1. Accurate sharding algorithm

1.1 Accurate library sorting algorithm

1.2 Accurate sub-table algorithm

2. Range sharding algorithm

Compound sharding strategy

Row expression sharding policy

Hint Sharding strategy

Related Posts

Do you really know the final keyword

Summary of Performance Optimization for a Large Number of Microservices -3. Improve the asynchronous log waiting strategy for the x86 cloud environment

Six examples for beginners to master the free command