SpringBoot combines ShardingSphere for data slicing, read-write separation

ShardingSphere is an ecosystem of open source distributed database middleware solutions comprising three separate products, ShardingSphere-JDBC, ShardingSphere-Proxy and HardingSphere-Sidecar (planned).They all provide standardized data fragmentation, distributed transactions, and database governance capabilities that can be applied to a wide variety of application scenarios, such as Java isomorphism, heterogeneous languages, cloud native, and so on.

ShardingSphere is positioned as a relational database middleware, which aims to fully and reasonably utilize the computing and storage capabilities of relational databases in a distributed scenario, rather than to implement a completely new relational database.It has a coexisting rather than mutually exclusive relationship with NoSQL and NewSQL.

ShardingSphere-JDBC uses a centralized architecture for high-performance, lightweight OLTP applications developed in Java; ShardingSphere-Proxy provides static entry and heterogeneous language support for OLAP applications and scenarios for managing and maintaining fragmented databases.

Explanation of the concept of fragmentation

Logical table

General name of the same logical and data structure tables for a horizontally split database (table).Example: Order data is divided into 10 tables based on the end of the primary key, t_order_0 to t_order_9, whose logical table is named t_order.

True table

Physical tables that really exist in a fragmented database.That is t_in the last exampleOrder_0 to t_order_9.

Data Node

The smallest unit of data slicing.Consists of a data source name and a data table, for example, ds_0.t_order_0.

Binding table

Refers to the main and sub tables with consistent slicing rules.For example:t_order table and t_order_item table, all in order_If the ID is sliced, the two tables are bound to each other.Multiple table Association queries between bound tables will not have Cartesian product associations, and the efficiency of association queries will be greatly improved.For example, if SQL is:

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 

Assume the fragmentation key order_when you do not configure a bound table relationshipIf ID routes numeric 10 to slice 0 and numeric 11 to slice 1, then the routed SQL should be 4, which are shown as Cartesian products:

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 

After configuring the binding table relationship, the route should have two SQL s:

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 

Where t_order is on the far left side of FROM and ShardingSphere will use it as the main table for the entire bound table.All route calculations will only use the policy of the primary table, then t_order_The slicing calculation of the item table will use t_Conditions for order.Therefore, the partition keys between the bound tables should be identical.

Slicing algorithm

Data is fragmented by the fragmentation algorithm, which supports fragmentation by=, >=, <=, >, <, BETWEEN and IN.The slicing algorithm needs to be implemented by the developer of the application and has a very high degree of flexibility.

Four tiling algorithms are currently available.Because fragmentation algorithms are closely related to business implementations, built-in fragmentation algorithms are not provided. Instead, scenarios are refined through a fragmentation strategy to provide a higher level of abstraction and an interface for application developers to implement the fragmentation algorithm themselves.

  • Precise slicing algorithm

Corresponds to PreciseSharding Algorithm, which handles scenarios where = and IN are fragmented using a single key as the fragmentation key.Requires use with Standard ShardingStrategy.

  • Range slicing algorithm

Corresponds to RangeShardingAlgorithm, which handles scenarios where BETWEEN AND, >, <, >=, <=is used as the slicing key with a single key.Requires use with Standard ShardingStrategy.

  • Composite slicing algorithm

Corresponding to ComplexKeysShardingAlgorithm, which deals with scenarios where multiple keys are used as slicing keys. The logic that contains multiple slicing keys is complex and requires application developers to handle the complexity themselves.It needs to be used with ComplexShardingStrategy.

  • Hint slicing algorithm

Corresponds to HintShardingAlgorithm, which handles scenarios where Hint row slicing is used.Need to work with HintShardingStrategy.

Fragmentation strategy

Includes a slicing key and a slicing algorithm, which are separated independently because of its independence.What really works with fragmentation is the fragmentation key + fragmentation algorithm, which is the fragmentation strategy.There are currently five fragmentation strategies available.

  • Standard Fragmentation Policy

Corresponds to StandardShardingStrategy.Provides support for fragmentation of =, >, <, >=, <=, IN and BETWEEN AND in SQL statements.StandardShardingStrategy only supports single-slice keys and provides PreciseShardingAlgorithms and RangeShardingAlgorithms.PreciseShardingAlgorithm is required to handle = and IN slicing.RangeShardingAlgorithms are optional for BETWEEN AND, >, <, >=, <=fragmentation. If RangeShardingAlgorithm is not configured, BETWEEN AND in SQL will be processed as a library-wide routing.

  • Composite Fragmentation Strategy

Corresponds to ComplexShardingStrategy.Composite fragmentation strategy.Provides support for fragmentation of =, >, <, >=, <=, IN and BETWEEN AND in SQL statements.ComplexShardingStrategy supports multi-slice keys. Due to the complexity of the relationship between multi-slice keys, it does not encapsulate too much. Instead, it passes the key-value combination and the slicing operator directly to the slicing algorithm, which is fully implemented by the application developers and provides maximum flexibility.

  • Row expression slicing strategy

Corresponds to InlineShardingStrategy.Groovy's expression provides support for fragmentation of = and IN in SQL statements, with only single-fragment keys.For simple slicing algorithms, you can avoid tedious Java code development by using simple configurations, such as: t_user_$->{u_id% 8} denotes t_The user table is based on u_id module 8, divided into eight tables with the name t_user_0 to t_user_7.

  • Hint Fragmentation Policy

Corresponds to HintShardingStrategy.A strategy for slicing by Hint specifying a slicing value rather than extracting it from SQL.

 

 

actual combat

Introducing dependencies

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.xin</groupId>
    <artifactId>shardingsphere-jdbc-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>shardingsphere-jdbc-demo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <shardingsphere.version>4.0.0</shardingsphere.version>
        <shardingsphere.spi.impl.version>4.0.0</shardingsphere.spi.impl.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-core-api</artifactId>-->
<!--            <version>${shardingsphere.version}</version>-->
<!--        </dependency>-->
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-jdbc-core</artifactId>-->
<!--            <version>${shardingsphere.version}</version>-->
<!--        </dependency>-->
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-jdbc-orchestration</artifactId>-->
<!--            <version>${shardingsphere.version}</version>-->
<!--        </dependency>-->
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-transaction-core</artifactId>-->
<!--            <version>${shardingsphere.version}</version>-->
<!--        </dependency>-->
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-transaction-xa-core</artifactId>-->
<!--            <version>${shardingsphere.version}</version>-->
<!--        </dependency>-->
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-transaction-base-seata-at</artifactId>-->
<!--            <version>${shardingsphere.version}</version>-->
<!--        </dependency>-->
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>-->
<!--            <version>${shardingsphere.version}</version>-->
<!--        </dependency>-->
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-jdbc-orchestration-spring-boot-starter</artifactId>-->
<!--            <version>${shardingsphere.version}</version>-->
<!--        </dependency>-->
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-jdbc-spring-namespace</artifactId>-->
<!--            <version>${shardingsphere.version}</version>-->
<!--        </dependency>-->
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-jdbc-orchestration-spring-namespace</artifactId>-->
<!--            <version>${shardingsphere.version}</version>-->
<!--        </dependency>-->
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-orchestration-reg-zookeeper-curator</artifactId>-->
<!--            <version>${shardingsphere.version}</version>-->
<!--        </dependency>-->

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>
    <repositories>
        <repository>
            <id>aliyun-repos</id>
            <url>http://maven.aliyun.com/nexus/content/groups/public/</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </repository>
    </repositories>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Build a database

Build libraries db0, db1, db0 write-only, db1 read-only, as read-write separation

Both libraries have the following table:

-- ----------------------------
-- Table structure for `user01`
-- ----------------------------
DROP TABLE IF EXISTS `user01`;
CREATE TABLE `user01` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of user01
-- ----------------------------
INSERT INTO `user01` VALUES ('1', 'wangxin', '99');

Configuration

server.port=8086

#Specify mybatis information
mybatis.config-location=classpath:mybatis-config.xml

spring.shardingsphere.datasource.names=master,slave0

# Data Source Master Library
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3307/db0?characterEncoding=utf-8
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=mysql

# Data Source Slave Library
spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://local:3307/db1?characterEncoding=utf-8
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=mysql

# Read-Write Separation
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0

#Print sql
spring.shardingsphere.props.sql.show=true

Tags: Spring Apache JDBC SQL

Posted on Thu, 21 May 2020 22:20:15 -0400 by nikbone