Apache ShardingSphere is an ecosystem of open source distributed database solutions. It is composed of JDBC, Proxy and Sidecar, which can be deployed independently and support mixed deployment. They all provide standardized data level expansion, distributed transactions, and distributed governance, and can be applied to diverse application scenarios such as Java isomorphism, heterogeneous languages, and cloud native.

ShardingSphere is a very active project, currently stable version 4.x, preview version 5.x and documentation have been released. The configuration of ShardingSphere before 3.x and after 4.x is not compatible, and the problem of resolving select for Update statements was fixed only from 4.x, and the schema is strictly verified and cross-library queries are not allowed.

Based on 4.x version, this chapter uses Sharding JDBC to realize database and table, and configureSharding Proxy and Sharding UI to realize aggregate query.

Sharding JDBC

Sharding JDBC is positioned as a lightweight Java framework that provides additional services in Java’s JDBC layer. It uses the client directly connected to the database, in the form of JAR package to provide services, without additional deployment and dependence, can be understood as an enhanced VERSION of THE JDBC driver, fully compatible with JDBC and various ORM frameworks.

  • Introduction of depend on

Sharding JDBC:

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

Spring JDBC and MySQL drivers:

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
</dependency>
Copy the code
  • configuration

Configuring a Data Source

spring:
  shardingsphere:
    props:
      show-sql: true Print SQL statements, debug can be enabled
    datasource:
      names: master1,slave1,slave2 Master master1, slave slave1, slave2
      master1:
        type: org.apache.commons.dbcp2.BasicDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://${MYSQL_HOST:localhost}:3307/engrz? useSSL=false&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/ Shanghai
        username: ${MYSQL_USER:engrz}
        password: ${MYSQL_PASSWORD:engrz2021}
      slave1:
        type: org.apache.commons.dbcp2.BasicDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://${MYSQL_HOST:localhost}:3308/engrz? useSSL=false&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/ Shanghai
        username: ${MYSQL_USER:engrz}
        password: ${MYSQL_PASSWORD:engrz2021}
      slave2:
        type: org.apache.commons.dbcp2.BasicDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://${MYSQL_HOST:localhost}:3309/engrz? useSSL=false&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/ Shanghai
        username: ${MYSQL_USER:engrz}
        password: ${MYSQL_PASSWORD:engrz2021}
Copy the code

Read and write separation and library and table

spring:
  shardingsphere:
    sharding:
      master-slave-rules:
        ds1: Read and write separate data sources, if there are multiple libraries can be configured multiple
          master-data-source-name: master1
          slave-data-source-names: slave1,slave2
      tables:
        t_user_info: # the name of the table
          actual-data-nodes: ds1.t_user_info_${0.. 9} # Rules, using Groovy syntax
          table-strategy:
            inline:
              sharding-column: user_id # fragment field
              algorithm-expression: t_user_info_${user_id % 10}
        t_user_log: # the name of the table
          actual-data-nodes: ds1.t_user_log_$->{2019.. 2021} # Rules, using Groovy syntax
          table-strategy:
            standard:
              sharding-column: log_date # fragment field
              precise-algorithm-class-name: com.engrz.commons.sharding.jdbc.algorithm.DatePreciseModuloShardingTableAlgorithm
              range-algorithm-class-name: com.engrz.commons.sharding.jdbc.algorithm.DateRangeModuloShardingTableAlgorithm
Copy the code

Sharding JDBC configuration simple separation separation and depots of the table, speaking, reading and writing, speaking, reading and writing configuration method is a little difference Use only read and write configuration properties of separation: spring. Shardingsphere. Masterslave. * depots in reading and writing table separation configuration properties: spring.shardingsphere.sharding.master-slave-rules.*

Sharding description in the configuration file:

T_user (user table)

There are 10 tables. The user_id field is the primary key of long type. Use the user_id value to model the calculation results into table names, such as T_user_info_0 and t_user_info_1

T_user_log (user log table)

Table by year, from 2019 to 2021, using custom sharding policy

DatePreciseModuloShardingTableAlgorithm:

/** * The date exactly matches */
public class DatePreciseModuloShardingTableAlgorithm implements PreciseShardingAlgorithm<Date> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {

        Date date = preciseShardingValue.getValue();
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        String year = String.valueOf(c.get(Calendar.YEAR));

        String tableName = null;
        for (String tmp : collection) {
            if (tmp.endsWith(year)) {
                // If the current year ends
                tableName = tmp;
                break; }}if (null == tableName) {
            String str = collection.iterator().next();
            tableName = str.substring(0, str.lastIndexOf("_"));
        }

        returntableName; }}Copy the code

DateRangeModuloShardingTableAlgorithm:

/** * Date range query */
public class DateRangeModuloShardingTableAlgorithm implements RangeShardingAlgorithm<Date> {

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Date> rangeShardingValue) {

        // The date field can be processed here to avoid unnecessary queries
        Range<Date> range = rangeShardingValue.getValueRange();

        Integer start = null;
        if (range.hasLowerBound()) {
            Date lowerEndpoint = range.lowerEndpoint();
            Calendar c = Calendar.getInstance();
            c.setTime(lowerEndpoint);
            start = Calendar.YEAR;
        }
        Integer end = null;
        if (range.hasUpperBound()) {
            Date upperEndpoint = range.upperEndpoint();
            Calendar c = Calendar.getInstance();
            c.setTime(upperEndpoint);
            end = Calendar.YEAR;
        }

        if (null == start && null == end) {
            return collection;
        }

        List<String> list = new ArrayList<>();
        for (String tableName : collection) {
            int suffix = Integer.parseInt(tableName.substring(tableName.lastIndexOf("_") + 1));
            if (null! = start && suffix < start) {continue;
            }
            if (null! = end && suffix > end) {continue;
            }
            list.add(tableName);
        }

        returnlist; }}Copy the code

If the log volume is large, you can also divide the table by quarter, as long as you modify the method slightly, return the corresponding table name.

For details about Sharding JDBC Sharding policies and Sharding algorithms, see the official documents.

  • Matters needing attention
  1. Note that the primary key is unique after using the sub-table. You can use the Snowflake algorithm to generate the primary key
  2. When using Sharding JDBC query, try to use sub-database and sub-table fields as conditions to avoid global scanning
  3. In the master-slave model, both read and write transactions use the master library
  4. Some SQL statements, such as distinct and Group by, do not support parsing
  • Sharding JDBC 4.x SQL parsing support

Sharding Proxy

Sharding Proxy is positioned as a transparent database Proxy and provides server version that encapsulates database binary protocol to support heterogeneous languages. MySQL/PostgreSQL can be accessed using any MySQL/PostgreSQL compliant client (e.g. MySQL Command Client, MySQL Workbench, Navicat, etc.) manipulate data and are more DBA-friendly.

The Sharding Proxy is used as a Proxy for t_user_0, t_user_1… Aggregate into a T_USER logical table.

For details about Sharding Proxy installation and configuration, see the official documents. If there is a custom Sharding algorithm, put the code into a JAR package and put it in the conf/lib directory after the Sharding Proxy is decompressed.

In practice, Sharding Proxy has version verification for connected clients. For example, I failed to connect to MySQL 5.7 database using MySQL Workbench 8.0. You can manually specify the driver corresponding to the server version using the DBeaver client.

Sharding UI

Sharding UI is a simple and useful Web management console for ShardingSphere. It is designed to make it easier for users to use the relevant functions of ShardingSphere and currently provides registry management, dynamic configuration management, database orchestration and other functions.

Sharding UI and Sharding Proxy facilitate database management. After understanding Sharding JDBC database table, configuration is very simple. Please refer to the relevant documents of Sharding Sphere 4.x

All are “Siege Lion · Zheng” unless noted. Link to this article: engr-z.com/177.html