Learning to know before depots table concept: spiritmark.blog.csdn.net/article/det…

I. Introduction to ShardingSphere

In the design of database, vertical subdivision and vertical subdivision are considered. With the increase of database data volume, do not immediately consider to do horizontal segmentation, first consider the cache processing, read and write separation, make use of indexes and so on, if these methods can not fundamentally solve the problem, then consider to do horizontal database and horizontal table.

Problems caused by separate database and table:

Cross-node connection query problems (paging, sorting)
Multiple data source management issues

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 make full and reasonable use of the computing and storage capabilities of relational databases in the distributed field, 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.

Second, the Sharding – JDBC

Sharding-JDBCIt’s lightweightjavaFramework, it’s an enhanced versionJDBCDrive, simplify the data related operations after the sub-database sub-table.Create a new project and add dependencies:

<parent>
    <groupId>org.springframework.bootgroupId>
    <artifactId>spring-boot-parentartifactId>
    <version>2.2.1. RELEASEversion > parent ><dependencies>
    <dependency>
        <groupId>org.springframework.bootgroupId>
        <artifactId>spring-boot-starterartifactId>
    dependency>
    <dependency>
        <groupId>org.springframework.bootgroupId>
        <artifactId>spring-boot-starter-testartifactId>
    dependency>
    <dependency>
        <groupId>com.alibabagroupId>
        <artifactId>druid-spring-boot-starterartifactId>
        <version>1.1.20 version > dependency ><dependency>
        <groupId>mysqlgroupId>
        <artifactId>mysql-connector-javaartifactId>
    dependency>
    <dependency>
        <groupId>org.apache.shardingspheregroupId>
        <artifactId>sharding-jdbc-spring-boot-starterartifactId>
        <version>4.0.0 - RC1version > dependency ><dependency>
        <groupId>com.baomidougroupId>
        <artifactId>mybatis-plus-boot-starterartifactId>
        <version>3.0.5 version > dependency ><dependency>
        <groupId>org.projectlombokgroupId>
        <artifactId>lombokartifactId>
    dependency>
dependencies>
Copy the code

2.1 Sharding-JDBC implementation level table

① Create a database and a database table based on the horizontal partition table

Horizontal subtable rule: if addedcidIt’s an even number to add datacourse_1If it is odd, add tocourse_2

CREATE TABLE `course_1`  (
  `cid` bigint(16) NOT NULL,
  `cname` varchar(255) ,
  `userId` bigint(16),
  `cstatus` varchar(16),PRIMARY KEY (`cid`)
)
Copy the code

② Write entity andMapper 类

@Data
public class Course {
    private Long cid;
    private String cname;
    private Long userId;
    private String cstatus;
}
Copy the code

@Repository
public interface CourseMapper extends BaseMapper<Course> {}Copy the code

3 detailed configuration files


spring:
  main:

    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:

      names: m1
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: JDBC: mysql: / / 192.168.182.200:3306 / course_db? serverTimezone=GMT%2B8
        username: root
        password: 1234
    sharding:
      tables:
        course:

          actual-data-nodes: m1.course_$->{1.. 2}

          key-generator:
            column: cid
            type: SNOWFLAKE

          table-strategy:
            inline:
              shardingcolumn: cid
              algorithm-expression: course_$->{cid%2+1}
    props:
      sql:
        show: true

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: false
Copy the code

(4) test

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

    @Autowired
    CourseMapper courseMapper;

    @Test
    public void addCourse(a) {
        for (int i = 1; i  10; i++) {
            Course course = new Course();
            course.setCname("java" + i);
            course.setUserId(100L);
            course.setCstatus("Normal"+ i); courseMapper.insert(course); }}@Test
    public void queryCourse(a) {
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.eq("cid".493001315358605313L); Course course = courseMapper.selectOne(wrapper); System.out.println(course); }}Copy the code

2.2 Sharding-JDBC implementation of horizontal branch library

① Demand analysis ② Create a database and table

3 detailed configuration files


spring:
  main:

    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:

      names: m1,m2
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: JDBC: mysql: / / 192.168.182.200:3306 / course_db_2? serverTimezone=GMT%2B8
        username: root
        password: 1234
      m2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: JDBC: mysql: / / 192.168.182.200:3306 / course_db_3? serverTimezone=GMT%2B8
        username: root
        password: 1234
    sharding:

      tables:
        course:

          actual-data-nodes: m$->{1.. 2}.course_$->{1.. 2}

          key-generator:
            column: cid
            type: SNOWFLAKE

          database-strategy:
            inline:
              sharding-column: userId
              algorithm-expression: m$->{userId%2+1}
          table-strategy:
            inline:
              sharding-column: cid
              algorithm-expression: course_$->{cid%2+1}
    props:
      sql:
        show: true

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: false
Copy the code

④ Test code

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

    @Autowired
    CourseMapper courseMapper;

    @Test
    public void addCourse(a) {
        for (int i = 1; i  20; i++) {
            Course course = new Course();
            course.setCname("java" + i);
            int random = (int) (Math.random() * 10);
            course.setUserId(100L + random);
            course.setCstatus("Normal"+ i); courseMapper.insert(course); }}@Test
    public void queryCourse(a) {
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.eq("cid".493001315358605313L); Course course = courseMapper.selectOne(wrapper); System.out.println(course); }}Copy the code

Query the actual correspondingSQL:

2.3 Sharding-JDBC operation common table

Common table:

A table that stores fixed data that rarely changes and is frequently associated with queries
Create common tables with the same structure in each database

① Analysis of ideas ② Create a common table in the corresponding databaset_udict， 并 创 建 对 应 实 体 和 Mapper“

CREATE TABLE `t_udict`  (
  `dict_id` bigint(16) NOT NULL,
  `ustatus` varchar(16) ,
  `uvalue` varchar(255),
  PRIMARY KEY (`dict_id`)
)
Copy the code

3 detailed configuration files


spring:
  main:

    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:

      names: m1,m2
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: JDBC: mysql: / / 192.168.182.200:3306 / course_db_2? serverTimezone=GMT%2B8
        username: root
        password: 1234
      m2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: JDBC: mysql: / / 192.168.182.200:3306 / course_db_3? serverTimezone=GMT%2B8
        username: root
        password: 1234
    sharding:

      tables:
        course:

          actual-data-nodes: m$->{1.. 2}.course_$->{1.. 2}

          key-generator:
            column: cid
            type: SNOWFLAKE

          database-strategy:
            inline:
              sharding-column: userId
              algorithm-expression: m$->{userId%2+1}
          table-strategy:
            inline:
              sharding-column: cid
              algorithm-expression: course_$->{cid%2+1}
        t_udict:
          key-generator:
            column: dict_id
            type: SNOWFLAKE
      broadcast-tables: t_udict

    props:
      sql:
        show: true

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: false

Copy the code

④ Test

Tested: Data will be inserted in every table in every library, deleted will also delete all data.

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

    @Autowired
    UdictMapper udictMapper;

    @Test
    public void addUdict(a) {
        Udict udict = new Udict();
        udict.setUstatus("a");
        udict.setUvalue("Enabled");
        udictMapper.insert(udict);
    }

    @Test
    public void deleteUdict(a) {
        QueryWrapper<Udict> wrapper = new QueryWrapper<>();
        wrapper.eq("dict_id".493080009351626753L); udictMapper.delete(wrapper); }}Copy the code

2.4 Sharding-JDBC to achieve read and write separation

To ensure the stability of database products, many databases have dual-system hot backup. That is, the first database server is a production server that provides external add, delete and modify services. The second database server does mostly read operations.

Sharding-jdbc implements read/write separation through SQL statement semantic analysis without data synchronization, which is usually automatically synchronized between database clusters.

Detailed configuration file:


spring:
  main:

    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:

      names: m0,s0
      m0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: JDBC: mysql: / / 192.168.182.200:3306 / course_db? serverTimezone=GMT%2B8
        username: root
        password: 1234
      s0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: JDBC: mysql: / / 192.168.182.200:3307 / course_db? serverTimezone=GMT%2B8
        username: root
        password: 1234

    masterslave:
      master-data-source-name: m0
      slave-data-source-names: s0
    props:
      sql:
        show: true

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: false
Copy the code

After testing: add, delete and change operations will be through the master database, and the master database will synchronize data to the slave database; All query operations are performed through the slave database.

Third, Sharding – Proxy

Sharding-proxy is positioned as a transparent database Proxy and provides server versions that encapsulate database binary protocols to support heterogeneous languages. Currently, only MySQL and PostgreSQL are available.

Sharding-ProxyIt is an independent application. You need to install the service, configure the database, table, or read/write separation, and start the application.

Sharding-proxyFor reference:Basic use of Sharding-proxy. Wechat search: Full stack xiao Liu

ShardingSphere

I. Introduction to ShardingSphere

Second, the Sharding – JDBC

2.1 Sharding-JDBC implementation level table

2.2 Sharding-JDBC implementation of horizontal branch library

2.3 Sharding-JDBC operation common table

2.4 Sharding-JDBC to achieve read and write separation

Third, Sharding – Proxy

Related Posts

A Set of Java collections

SSIS Learning Use two: The most basic data flow tasks (processing flow)

Practice and optimization of Apache Kylin