ShardingSphere5.0.0-alpha如何實(shí)現(xiàn)mysql分庫(kù)分表

這篇文章主要講解了“ShardingSphere5.0.0-alpha如何實(shí)現(xiàn)mysql分庫(kù)分表”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“ShardingSphere5.0.0-alpha如何實(shí)現(xiàn)mysql分庫(kù)分表”吧！

聲明

• 本文會(huì)基于 Springboot + mybatis + shardingsphere + mysql5.6 + druid 進(jìn)行實(shí)戰(zhàn)講解

• 本文在上一篇文章[數(shù)據(jù)分表]的基礎(chǔ)上增加了 分庫(kù) 的功能

• 本文不會(huì)介紹 shardingsphere 以及分庫(kù)分表的相關(guān)概念

• 本文采用的 shardingsphere 版本是 5.0.0-alpha, 具體見(jiàn) pom 文件

• 本文涉及的源碼請(qǐng)參考 分庫(kù)

• 如果看官方文檔時(shí), 請(qǐng)選擇對(duì)應(yīng)的版本 !!!

• 文中涉及的源碼可能會(huì)有誤, 請(qǐng)以上傳到 gitee 的源碼為準(zhǔn).

正文

需求

我們有兩個(gè)數(shù)據(jù)庫(kù) miaosha2 和 miaosha3, 每個(gè)數(shù)據(jù)庫(kù)中都有 2 張被拆分過(guò)的用戶表 user_info0 和 user_info1

當(dāng)我們往用戶表插數(shù)據(jù)時(shí), 會(huì)按照一定的規(guī)則(根據(jù)自增id取模), 落到某個(gè) miaosha 庫(kù)中的某張 user_info 表中.

準(zhǔn)備工作

1. 數(shù)據(jù)庫(kù)表

create database miaosha2;

DROP TABLE IF EXISTS `miaosha2`.`user_info0`;
CREATE TABLE `miaosha2`.`user_info0`
(
    `id`         bigint(20)                    NOT NULL AUTO_INCREMENT,
    `user_label` varchar(32) COLLATE utf8_bin           DEFAULT NULL,
    `username`   varchar(64) COLLATE utf8_bin           DEFAULT NULL,
    `email`      varchar(64) COLLATE utf8_bin           DEFAULT NULL,
    `phone`      varchar(64) COLLATE utf8_bin           DEFAULT NULL,
    `password`   varchar(128) COLLATE utf8_bin NOT NULL,
    `active`     tinyint(4)                    NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 7
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin;


DROP TABLE IF EXISTS `miaosha2`.`user_info1`;
CREATE TABLE `miaosha2`.`user_info1`
(
    `id`         bigint(20)                    NOT NULL AUTO_INCREMENT,
    `user_label` varchar(32) COLLATE utf8_bin           DEFAULT NULL,
    `username`   varchar(64) COLLATE utf8_bin           DEFAULT NULL,
    `email`      varchar(64) COLLATE utf8_bin           DEFAULT NULL,
    `phone`      varchar(64) COLLATE utf8_bin           DEFAULT NULL,
    `password`   varchar(128) COLLATE utf8_bin NOT NULL,
    `active`     tinyint(4)                    NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 6
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin;



create database miaosha3;

DROP TABLE IF EXISTS `miaosha3`.`user_info0`;
CREATE TABLE `miaosha3`.`user_info0`
(
    `id`         bigint(20)                    NOT NULL AUTO_INCREMENT,
    `user_label` varchar(32) COLLATE utf8_bin           DEFAULT NULL,
    `username`   varchar(64) COLLATE utf8_bin           DEFAULT NULL,
    `email`      varchar(64) COLLATE utf8_bin           DEFAULT NULL,
    `phone`      varchar(64) COLLATE utf8_bin           DEFAULT NULL,
    `password`   varchar(128) COLLATE utf8_bin NOT NULL,
    `active`     tinyint(4)                    NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 7
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin;


DROP TABLE IF EXISTS `miaosha3`.`user_info1`;
CREATE TABLE `miaosha3`.`user_info1`
(
    `id`         bigint(20)                    NOT NULL AUTO_INCREMENT,
    `user_label` varchar(32) COLLATE utf8_bin           DEFAULT NULL,
    `username`   varchar(64) COLLATE utf8_bin           DEFAULT NULL,
    `email`      varchar(64) COLLATE utf8_bin           DEFAULT NULL,
    `phone`      varchar(64) COLLATE utf8_bin           DEFAULT NULL,
    `password`   varchar(128) COLLATE utf8_bin NOT NULL,
    `active`     tinyint(4)                    NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 6
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin;

2. pom 依賴

<?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.3.2.RELEASE</version>
        <relativePath/>
    </parent>
    <groupId>com.nimo</groupId>
    <artifactId>shardingsphere-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>shardingsphere-demo</name>

    <properties>
        <java.version>1.8</java.version>
    </properties>
  
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
      
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
      
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
      
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

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

        <!-- 阿里數(shù)據(jù)源 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.3</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

3. application.yml

再次強(qiáng)調(diào)下, 本文采用的 shardingsphere 版本是 5.0.0-alpha. 不同版本配置會(huì)有差異.

本文在上一篇文章的基礎(chǔ)上增加, 并修改了幾個(gè)配置, 下面的源碼中有標(biāo)記出來(lái)

• 添加了一個(gè)數(shù)據(jù)源配置

• 添加了一個(gè)分庫(kù)策略

• 添加了一個(gè)分庫(kù)算法

server:
  port: 8777

spring:
  shardingsphere:
    # 展示修改以后的sql語(yǔ)句
    props:
      sql-show: true
    datasource:
      # (這里增加了一個(gè) ds1 的數(shù)據(jù)源)
      names: ds0,ds1
      common:
        type: com.alibaba.druid.pool.DruidDataSource
      ds0:
        url: jdbc:mysql://127.0.0.1:3306/miaosha2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: '123456'
        driver-class-name: com.mysql.cj.jdbc.Driver
      # (新增的配置)
      ds1:
        url: jdbc:mysql://127.0.0.1:3306/miaosha3?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: '123456'
        driver-class-name: com.mysql.cj.jdbc.Driver
    rules:
      sharding:
        # 分布式序列算法配置
        key-generators:
          # 此處必須要配置，否則會(huì)導(dǎo)致報(bào)錯(cuò)
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 123

        # 配置 user_info 表
        tables:
          user_info:
            # 分庫(kù)策略 (新增的配置)
            database-strategy:
              standard:
                  sharding-column: id
                  sharding-algorithm-name: database-inline

            # 配置user_info的分庫(kù)分表的規(guī)則 (增加了數(shù)據(jù)源的配置)
            actual-data-nodes: ds$->{0..1}.user_info$->{0..1}

            # 單分片鍵的標(biāo)準(zhǔn)分片
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: table-inline

            # 主鍵id生成策略（雪花算法）
            key-generate-strategy:
              key-generator-name: snowflake
              column: id

        # 配置分片算法
        sharding-algorithms:
          # 通過(guò) id 取模的方式確定數(shù)據(jù)落到哪個(gè)庫(kù) (新增的配置)
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{id % 2}

          # 通過(guò) id 取模的方式確定數(shù)據(jù)落到哪個(gè)表
          table-inline:
            type: INLINE
            props:
              algorithm-expression: user_info$->{id % 2}
    enabled: true

mybatis:
  typeAliasesPackage: com.nimo.shardingdatabasedemo.entity
  mapperLocations: classpath:mapper/*.xml

4. 主要代碼

// sql 
<insert id="addUser" parameterType="com.nimo.shardingdemo.entity.UserInfo">
   insert into user_info(id, username, password) values (#{id}, #{username}, #{password})
</insert>
 
 // 新增一個(gè)用戶信息
@PostMapping("userinfo")
public Object addUserInfo(@RequestBody UserInfo userInfo) {
   return userInfoMapper.addUser(userInfo);
}

5. 測(cè)試命令

curl -X POST --location "http://localhost:8777/userinfo" \
    -H "Content-Type: application/json" \
    -d "{
          \"username\": \"wangbadan\",
          \"password\": \"123456\"
        }"

感謝各位的閱讀，以上就是“ShardingSphere5.0.0-alpha如何實(shí)現(xiàn)mysql分庫(kù)分表”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)ShardingSphere5.0.0-alpha如何實(shí)現(xiàn)mysql分庫(kù)分表這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！