Table splitting based on springboot+sharding jdbc +spring data jpa

background

The IOT PC rental project in the charge of the blogger did not consider this aspect in the code design because the product said it did not need to support the multi tenant mode in the design stage. Suddenly, some time ago, gou product said it needed to do this mode. So we have to carry out pre research.

Design ideas

The original idea was to use data table fields as data pseudo isolation (field conditions are added for all operations). In this way, code modification can be reduced. However, considering that the project architecture is relatively simple, only mysql and redis are used for data storage, and there is no time series database or mongodb to store business data. There are some large data tables in mysql, If you only use fields for isolation, it will cause query efficiency problems. so, I plan to use sharding JDBC to divide large tables (of course, you can also divide libraries, but I don't think it's necessary here). Next is the code file demonstration.

code

pom

The following pom leads the package.

    <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
      <version>5.0.0-beta</version>
    </dependency>

yml configuration (key)

The following is about the configuration of shardingsphere and jpa. It depends on their needs. Because it is pre research, this paragraph is not involved. jpa is configured to automatically generate tables and libraries.

spring:
  shardingsphere:
    # Data source configuration
    datasource:
      # Data source name. Multiple data sources are separated by commas
      names: zc #
      zc:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://localhost:3306/zc?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useLegacyDatetimeCode=false&serverTimezone=UTC
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: root
    # Rule configuration
    rules:
      sharding:
        tables:
          # Logical table name
          device_log:
            # The line expression identifier can use ${...} or $- > {...}, but the former conflicts with the placeholder of Spring's own property file. Therefore, it is recommended to use $- > {...} in the Spring environment
            actual-data-nodes: zc.device_log_$->{1..3}
    # Table splitting strategy
    table-strategy:
      standard:
        # Partition column name
        sharding-column: company_code
        # Partition algorithm name
        sharding-algorithm-name: table-inline
    # Table primary key algorithm
#    key-generate-strategy:
#      column: id
#      key-generator-name: snowflake
    # Partition algorithm configuration
    sharding-algorithms:
      table-inline:
        # Partition algorithm type
        type: INLINE
        props:
          # Row expression of slicing algorithm
          algorithm-expression: device_log_$->{company_code}
#    # Distributed sequence algorithm configuration
#    key-generators:
#      snowflake:
#        # Distributed sequence algorithm (SNOWFLAKE algorithm: SNOWFLAKE; UUID: UUID)
#        type: SNOWFLAKE
#        # Attribute configuration of distributed sequence algorithm
#        props:
#          # Unique identification of the working machine
#          worker-id: 123
  jpa:
    database: mysql
    database-platform: org.hibernate.dialect.MySQL57Dialect
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        jdbc:
          time_zone: UTC
  messages:
    encoding: UTF-8

Test code

The test code is shown below. When inserting, the table will be inserted according to the configured code, and findAll will query all tables, but the local test is a little slow. This operation should be avoided. Querying data according to code will also query the corresponding table.

        List<String> merchantList = Lists.newArrayList("1", "2", "3");
        // Insert 20 pieces of data
        for (int i = 0; i < 20; i++) {
            DeviceLog log = new DeviceLog();
            log.setSn("9999" + i);
            log.setCompanyCode(merchantList.get(new Random().nextInt(merchantList.size())));
            log.setCreateTime(DateUtils.format(new Date(), DateUtil.pattern_yMdHms));
            log.setTmxData("{}");
            log.setShanzuData("{}");
            deviceLogRepository.save(log);
        }
        // Query all data
        List<DeviceLog> deviceLogs = deviceLogRepository.findAll();
        // Query data according to code
        List<DeviceLog> deviceLogs1 = deviceLogRepository.queryDeviceLogByCode(merchantList.get(new Random().nextInt(merchantList.size())));

sql of querydevice logbycode:

    @Query(value = "select * from device_log c where  c.company_code = :companyCode",nativeQuery = true)
    List<DeviceLog> queryDeviceLogByCode(@Param("companyCode") String companyCode);

Pit encountered during pre research

1. At the beginning, the sharding version was too low. Both 3.x and 4.x encountered Unsupported Date type:class java.lang.String error. Upgrade to version 5.0.0-beta. As for what other articles said about turning off log sql printing, it was nonsense.
Specific issue:https://github.com/apache/shardingsphere/issues/12822
2. Due to different sharding version points, the configuration files will be different, and the configuration needs to be modified according to the version.