Use ShardingJDBC for horizontal database and table

background

In recent years, with the explosion of data volume, every product related to the Internet or other traditional industries will involve massive data. For the case that the single service data is too large, due to the limited capacity of the single database and the situation that the vertical branch database has been carried out, the data volume is still very large and continues to expand, not only the subsequent data cannot be stored, but also affect the suction performance. Currently popular programs generally include NoSQL and subtable. NoSQL is open source recently, but I don’t know much about it and haven’t used it in the project. Therefore, this time, I will talk about the way of subtable and subdatabase to meet the needs of our business expansion.

A brief introduction to ShardingSphere

As a member of ShardingSphere ecosystem, ShardingJDBC provides the function of database routing for applications as database middleware. To quote an official introduction from ShardingSphere:

Apache ShardingSphere is an ecosystem of open source distributed database middleware solutions. It is composed of JDBC, Proxy and Sidecar (under planning), which are independent but can be deployed and used together. They all provide standardized data sharding, distributed transactions, and database governance functions, and can be applied to diverse application scenarios such as Java isomorphism, heterogeneous languages, and cloud native.

Apache ShardingSphere is positioned as relational database middleware, aiming to fully and reasonably utilize the computing and storage capabilities of relational databases in distributed scenarios, rather than realizing a new relational database. It captures the essence of things by focusing on the immutable. Relational databases still occupy a huge market today, and are the cornerstone of each company’s core business, and will be difficult to shake in the future. At present, we are more focused on incremental, rather than subversive, on the original foundation.

Apache ShardingSphere 5.x is dedicated to pluggable architecture, and the functional components of the project can be flexibly expanded in a pluggable manner. At present, functions such as data sharding, read/write separation, data encryption, shadow library pressure measurement, as well as support for SQL and protocols such as MySQL, PostgreSQL, SQLServer and Oracle are woven into the project through plug-ins. Developers can customize their own unique systems like building blocks. Apache ShardingSphere currently offers dozens of SPIs as extension points to the system, and more are being added.

ShardingSphere became the Apache Software Foundation’s Top project on April 16, 2020.

In view of its latest open source, and relatively rich documentation and resources, we use ShardingJDBC as our middleware to achieve sub-database sub-table.

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.

Simple sub – library sub – table implementation

When referring to the demo implementation on the official website, I did not understand some concepts, so I had many problems when copying the demo. I was very upset because I could not run it. So sort out a sure can run up the demo for your reference.

The preparatory work

The MySQL database
Maven, the network environment is smooth, can download dependencies normally
Developing Java IDE (IDEA Ultimate Best)

Development steps

To create a Maven project, we used SpringBoot as our framework, so we can also use Spring Initializer for project initialization. This is not required. After the project is created, we write the POM file and add the corresponding dependencies:


      
<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.example</groupId>
  <artifactId>ShardingJdbcDemo</artifactId>
  <version>1.0 the SNAPSHOT</version>

  <properties>
    <maven.compiler.source>8</maven.compiler.source>
    <maven.compiler.target>8</maven.compiler.target>
  </properties>

  <parent>
    <artifactId>spring-boot-starter-parent</artifactId>
    <groupId>org.springframework.boot</groupId>
    <version>Against 2.4.1</version>
  </parent>
  
  <dependencies>
    <! Spring Boot Web dependency -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <! -- Spring Boot test dependency -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    <! -- Mybatis dependency -->
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>2.1.3</version>
    </dependency>
    <! -- Database driver -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <! Starter, ShardingJDBC, and SpringBoot will all be configured, which will cause the startup to fail.
    <dependency>
      <groupId>com.zaxxer</groupId>
      <artifactId>HikariCP</artifactId>
      <version>3.4.5</version>
    </dependency>
    <! -- ShardingJDBC dependency (4.1.1) -->
    <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
      <version>4.1.1</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>
  
  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <version>2.4.0</version>
      </plugin>
    </plugins>
  </build>
</project>
Copy the code

Create database tables. We are going to simulate horizontal sub-databases and sub-tables later, so we create one such data model, the order model. The order model has the order id and the userId that creates the order. We divide the table by the order id and the database by the userId. The repository requires two databases, named DB1 and DB2, and then creates two tables under DB1 and DB2, named T_ORDER_1 and T_ORDER_2, respectively. Mysql > select * from ‘mysql’;

SQL > create table db1drop database if exists db1;
create database db1;
use db1;
create table t_order_1
(
    order_id bigint       not null,
    `desc`   varchar(128) null,
    user_id  bigint       null.constraint t_order_1_order_id_uindex
        unique (order_id)
);
alter table t_order_1
    add primary key (order_id);
create table t_order_2
(
    order_id bigint       not null,
    `desc`   varchar(128) null,
    user_id  bigint       null.constraint t_order_2_order_id_uindex
        unique (order_id)
);
alter table t_order_2
    add primarykey (order_id); Create database DB2 and create corresponding tablesdrop database if exists db2;
create database db2;
use db2;
create table t_order_1
(
    order_id bigint       not null,
    `desc`   varchar(128) null,
    user_id  bigint       null.constraint t_order_1_order_id_uindex
        unique (order_id)
);
alter table t_order_1
    add primary key (order_id);
create table t_order_2
(
    order_id bigint       not null,
    `desc`   varchar(128) null,
    user_id  bigint       null.constraint t_order_2_order_id_uindex
        unique (order_id)
);
alter table t_order_2
    add primary key (order_id);
Copy the code

Create application.properties under Resources and do the following configuration. Although yamL is officially recommended, yamL indentation can be confusing in unfamiliar situations, so you can make your own decisions.

application.properties:

spring.application.name=sharding-demo
server.port=8080
The name of the data source is not required to be the same as the name of the database.
spring.shardingsphere.datasource.names=ds1,ds2
Configure the DS1 data source (mandatory). Where type is the type of the data source, Hikari is selected here, and other parameters are configured according to the requirements of the data source.
# jdbc-url is used by Hikari. Other data sources such as Druid are used by Hikari
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=JDBC: mysql: / / localhost: 3306 / db1? useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=Huawei12#$
Configure the DS2 data source (required)
spring.shardingsphere.datasource.ds2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.jdbc-url=JDBC: mysql: / / localhost: 3306 / db2? useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=Huawei12
Select * from user_id; select * from user_id; In Spring Boot, placeholders need to be represented by $->{}. The ${} in the official example is not available, note here
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2 + 1}
SQL > select * from database where table = 2; SQL > select * from database where table = 2;
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{1.. 2}.t_order_$->{1.. 2}
Configure the table table policy (mandatory). We divide the table by order_id. The odd-numbered order_id is routed to T_order_2 and the even-numbered order_1.
If ShardingJDBC is used, configure the policy for the table, even if the table is not divided.
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2 + 1}
(optional) configure the primary key generation policy for order_id, as order_id is the primary key of the table.
If order_ID is not passed, ShardingJDBC will be automatically generated using SNOWFLAKE and then inserted into SQL
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# configure to print the SQL actually executed (production shutdown)
spring.shardingsphere.props.sql.show=true
Copy the code

Create a boot class for SpringBoot

package org.example.sharding;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApp {
    public static void main(String[] args) { SpringApplication.run(DemoApp.class, args); }}Copy the code

Create the OrderMapper interface class

package org.example.sharding;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

@Mapper
@Repository
public interface OrderMapper {

    @Insert("insert into t_order (desc, user_id) values (#{desc}, #{userId})")
    int insert(@Param("desc") String desc, @Param("userId") long userId);
}
Copy the code

To create a test case, we simulate 5 users, each user creates 10 orders, to see the data distribution after insertion:

package org.example.sharding;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Locale;

@RunWith(SpringRunner.class)
@SpringBootTest
public class OrderMapperTest {

    @Autowired
    private OrderMapper orderMapper;

    @Test
    public void testInsert(a) {
        for (long user = 1L; user <= 5L; ++user) {
            for (long i = 1L; i <= 10L; ++i) {
                orderMapper.insert(String.format(Locale.CHINA, "%s%d"."Order", i), user); }}}}Copy the code

In the execution log, we can see the following log:Insert data into DS2 and ds1 according to user_id; By dividing the data into T_ORDER_2 and T_ORDER_1 based on order_ID, our repository and table have been successful. Interested can also be queried and other operations, according to the incoming parameters for parsing, scattered to different libraries and tables.

Write in the back

It took a little bit of time for this to run through, but this is just a preliminary completion of the branch and the branch. In terms of capacity expansion, master/slave synchronization, read/write separation and other aspects, we have not been introduced yet, so I and you need to continue to learn and experiment. There are also many errors encountered in the middle, such as: if you do not configure the database character set SQL cannot parse the error reported null pointer. If time permits, I will do a separate sync and read/write demo later, and I welcome your questions.

reference

ShardingSphere
ShardingJDBC sub-database sub-table

Use ShardingJDBC for horizontal database and table

background

A brief introduction to ShardingSphere

Simple sub – library sub – table implementation

Write in the back

reference

Related Posts

How do you implement a thread-safe singleton without using synchronized and Lock?

Do you know what CI/CD is? Don’t understand? Five minutes to sink in!

Note on microservice Refactoring for card games (Part 1) : Set a small goal