shardingsphere

When the company’s business volume increases and the single table cannot support it, the database and table is an unsolved topic. My younger brother recently joined a new company and found that the company uses ShardingSphere to conduct database and table. I have never been exposed to this aspect before, so I wrote a demo to learn. The following article will record how to use ShardingSphere to divide the database into tables! (ability limited, this chapter will not talk about the principle, just record how to divide the database into tables, principles behind I learn to write, now will use the first 0.0,)

1. Official website document address

This is the address of ShardingSphere official website document. If you need it, you can click on it to have a look.

2. Technology and environment

The database The technology used in the project
mysql5.6 Springboot, Mybatis, ShardingSphere

3. Build the database and build the table

Create two new databases:One user0, one user1. Select * from user0 where user0 = 1;One us_admin0 and one us_admin1, create the following statement:

CREATE TABLE `us_admin0` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID'.  `user_id` int(11) NOT NULL COMMENT 'user id'. `addr_id` int(11) NOT NULL COMMENT 'address id'.  `user_name` varchar(64) NOT NULL COMMENT 'User number'. PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Background User table';   CREATE TABLE `us_admin1` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID'. `user_id` int(11) NOT NULL COMMENT 'user id'. `addr_id` int(11) NOT NULL COMMENT 'address id'. `user_name` varchar(64) NOT NULL COMMENT 'User number'. PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Background User table'; Copy the code

Select * from user1 where user1 = us_admin0 and us_admin1;

CREATE TABLE `us_admin0` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID'.  `user_id` int(11) NOT NULL COMMENT 'user id'. `addr_id` int(11) NOT NULL COMMENT 'address id'.  `user_name` varchar(64) NOT NULL COMMENT 'User number'. PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Background User table';   CREATE TABLE `us_admin1` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID'. `user_id` int(11) NOT NULL COMMENT 'user id'. `addr_id` int(11) NOT NULL COMMENT 'address id'. `user_name` varchar(64) NOT NULL COMMENT 'User number'. PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Background User table'; Copy the code

4. Set up springboot project

The POM file and configuration file contents are as follows:

Pom file:

<? 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.0.7.RELEASE</version> <relativePath/> <! -- lookup parent from repository --> </parent>  <groupId>com.sharding</groupId>  <artifactId>sphere</artifactId>  <version>1.0.0-SNAPSHOT</version>  <name>sphere</name>  <description>Demo project for Spring Boot</description>   <properties>  <java.version>1.8</java.version>  <spring-cloud.version>Hoxton.SR3</spring-cloud.version>  <redisson.version>3.8.2</redisson.version>  <mysql-connector.version>8.0.12</mysql-connector.version>  </properties>   <dependencies>  <! -- Unit tests --> <dependency>  <groupId>junit</groupId>  <artifactId>junit</artifactId>  <version>4.12</version>  <scope>test</scope>  </dependency>  <! -- web --> <dependency>  <groupId>org.springframework.boot</groupId>  <artifactId>spring-boot-starter-web-services</artifactId>  </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>  <! -- Jar package of ShardingSphere --> <dependency>  <groupId>org.apache.shardingsphere</groupId>  <artifactId>sharding-jdbc-spring-boot-starter</artifactId>  <version>4.0.0-RC1</version>  </dependency>  <! Ali database connection pool --> <dependency>  <groupId>com.alibaba</groupId>  <artifactId>druid-spring-boot-starter</artifactId>  <version>1.1.14</version>  </dependency>  <! Mysql --> <dependency>  <groupId>mysql</groupId>  <artifactId>mysql-connector-java</artifactId> <! -- <scope>runtime</scope>--> </dependency>  <! -- Health check --> <dependency>  <groupId>org.springframework.boot</groupId>  <artifactId>spring-boot-starter-actuator</artifactId>  </dependency>  <! -- mybatis --> <dependency>  <groupId>org.mybatis.spring.boot</groupId>  <artifactId>mybatis-spring-boot-starter</artifactId>  <version>2.1.2</version>  </dependency>   </dependencies>   <build>  <plugins>  <plugin>  <groupId>org.springframework.boot</groupId>  <artifactId>spring-boot-maven-plugin</artifactId>  </plugin>  </plugins>  </build>  </project> Copy the code

Application. Yml configuration:

# service portserver:
  port: 8888

# service namespring:  application:  name: test-user # Configure sharding JDBC sharding rules shardingsphere:  datasource: # connection name (we will use this name to differentiate libraries) names: ds0,ds1  ds0:  type: com.alibaba.druid.pool.DruidDataSource  driver-class-name: com.mysql.jdbc.Driver  url: jdbc:mysql://192.1681.19.:3306/user0? useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8  username: root  password: 123  initialSize: 5Initialize the size maxActive: 200A maximum # maxWait: 2000Set the timeout for obtaining connections in milliseconds ms timeBetweenEvictionRunsMillis: 60000Configure how often to check for idle connections that need to be closed ds1:  type: com.alibaba.druid.pool.DruidDataSource  driver-class-name: com.mysql.jdbc.Driver  url: jdbc:mysql://192.1681.19.:3306/user1? useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8  username: root  password: 123  initialSize: 5Initialize the size maxActive: 200A maximum # maxWait: 2000Set the timeout for obtaining connections in milliseconds ms timeBetweenEvictionRunsMillis: 60000Configure how often to check for idle connections that need to be closedConfigure the sharding rule sharding: # By table tables:  us_admin: Configure the data node actualDataNodes: ds${0.1.}.us_admin${0.1.} # Separate library strategy databaseStrategy:  inline: Rule # depots With the field to depots user_id There are two libraries and a ds0 (user0) and ds1 (user1) shardingColumn: user_id  algorithmExpression: ds${user_id % 2} # table splitting strategy tableStrategy:  inline:  shardingColumn: addr_id  algorithmExpression: us_admin${addr_id % 2}  keyGenerator:  column: id  type: SNOWFLAKE  bindingTables:  us_admin  broadcastTables:  t_config  defaultDataSourceName: ds0   props:  sql.show: true  Configure the scan path for XMLmybatis:  mapper-locations: classpath:mapper/*.xml  check-config-location: true  type-aliases-package: com.sharding.sphere.model  configuration:  cacheEnabled: true  mapUnderscoreToCamelCase: true Copy the code

5. Interface test

Write add, delete and modify interfaces for testing, slice the sub-database according to user_id, slice the sub-table according to addr_id, insert some data respectively, and the results are as follows:

@RestController
public class UserController {

    @Resource
    UserService userService;
  @RequestMapping("add")  public Integer add(@RequestBody UsAdmin usAdmin){  Integer add = userService.add(usAdmin);  return add;  }   @RequestMapping("select")  public List<UsAdmin> select(a){  List<UsAdmin> select = userService.select();  return select;  }   @RequestMapping("delect")  public Integer delect(Long id){  Integer delect = userService.delect(id);  return delect;  } } Copy the code

Query SQL statement:

<select id="selectAll" resultMap="BaseResultMap">
    select
      id, addr_id, user_id, user_name
    from us_admin
  </select>
Copy the code

New SQL statement:

<insert id="insertSelective" parameterType="com.sharding.sphere.model.UsAdmin">
    insert into us_admin
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id ! = null">
        id. </if>  <if test="userId ! = null">  user_id,  </if>  <if test="addrId ! = null">  addr_id,  </if>  <if test="userName ! = null">  user_name,  </if>  </trim>  <trim prefix="values (" suffix=")" suffixOverrides=",">  <if test="id ! = null">  #{id,jdbcType=BIGINT},  </if>  <if test="userId ! = null">  #{userId,jdbcType=INTEGER},  </if>  <if test="addrId ! = null">  #{addrId,jdbcType=INTEGER},  </if>  <if test="userName ! = null">  #{userName,jdbcType=VARCHAR},  </if>  </trim>  </insert> Copy the code

Delete SQL statement:

 delete from us_admin
    where id = #{id,jdbcType=BIGINT}
Copy the code

New data table and database results:

image

Query result:

image

Select * from us_admin0; select * from us_admin1; select * from us_admin1; select * from us_admin1; select * from us_admin1; select * from us_admin1; select * from us_admin1; However, it seems that some SQL statements are not supported by ShardingSphere, such as associating with oneself (us_admin left join us_admin), and some SQL statements are not supported. Please check the details on Baidu.

To be reasonable, ShardingSphere actually does not invade the code much, but there are some configurations, and the SQL written after the configuration is the same as the original, it may be that the pit has not been stepped on, and I will write an article for analysis after studying the principle at the weekend.

6. Public account

If you think my article is helpful to you, please pay attention to my wechat official account :” A happy and painful programmer “(no advertising, simply share original articles, pJ practical tools, a variety of Java learning resources, looking forward to common progress with you).