分库分表

sharding-jdbc 分库分表

参考:
https://github.com/yudiandemingzi/spring-boot-sharding-sphere

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <!--使用这个出错  Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required-->
    <!--<artifactId>druid-spring-boot-starter</artifactId>-->
    <artifactId>druid</artifactId>
    <version>1.2.6</version>
</dependency>

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` BIGINT(64) NOT NULL,
  `nickname` VARCHAR(255) DEFAULT NULL,
  `password` VARCHAR(255) DEFAULT NULL,
  `sex` INT(32) DEFAULT NULL,
  `birthday` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `user`(`id`,`nickname`,`password`,`sex`,`birthday`) VALUES 
(1,'张三','Admin@123',1,'2021-08-26 22:26:00'),
(1430902809406021633,'xinxin-1791788037','Admin@123',1,'2021-08-26 14:39:49');

@TableName(value = "user")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    @TableId // 主键
    private Long id;
    private String nickname;
    private String password;
    private Integer sex;
    private Date birthday;
}

读写分离

主从同步自己实现
前置: 做两个库, 库内表相同, 数据相同

server:
  port: 8080
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源取别名，下面的db1,db2任意取名字
      names: db1,db2
      # 给master-db1每个数据源配置数据库连接信息
      db1:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sharding_db01?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: 123456
      # 配置db2-slave
      db2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sharding_db02?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: 123456
    sharding:
      # 默认数据源，主要用于写，读写分离里一定要配置. 注意：如果不配置，那么就会把三个节点都当做从slave节点，新增，修改和删除会出错。
      default-data-source-name: db1
    # 配置数据源的读写分离，但是数据库一定要做主从复制
    masterslave:
      # 配置主从名称，可以任意取名字
      name: ms
      # 配置主库master，负责数据的写入
      master-data-source-name: db1
      # 配置从库slave节点, 负责数据的读取
      slave-data-source-names: db2
      # 配置slave节点的负载均衡均衡策略，采用轮询机制
      load-balance-algorithm-type: round_robin
      
mybatis-plus:
  mapper-locations: classpath*:/mapper/*.xml
  type-aliases-package: com.xmtrock.sharding.entity
  configuration:
    map-underscore-to-camel-case: true
    use-generated-keys: true

spring.shardingsphere.datasource.names=master,slave01

# 主库
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/sharding_db01?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# 从库
spring.shardingsphere.datasource.slave01.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave01.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave01.url=jdbc:mysql://localhost:3306/sharding_db02?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.slave01.username=root
spring.shardingsphere.datasource.slave01.password=123456

# 读写分离配置, 名字随意, 轮询方式, slave是可以有多个的
spring.shardingsphere.masterslave.name=read_write_split
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave01
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin

# 默认数据源，主要用于写，注意一定要配置读写分离 ,注意：如果不配置，那么就会把三个节点都当做从slave节点，新增，修改和删除会出错。
spring.shardingsphere.default-data-source-name=master

# 打印sql
spring.shardingsphere.props.sql.show=true

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping("/save")
    public String insert() {
        User user = new User();
        user.setNickname("Admin" + new Random().nextInt());
        user.setPassword("Admin@123");
        user.setSex(1);
        user.setBirthday(new Date());
        userService.save(user);
        return "新增成功";
    }

    @GetMapping("/getuser")
    public List<User> listuser() {
        return userService.list();
    }
}

此时即可实现, 读从db02读, 写从db01写.

单库分表

前置: 做三个表, 分别是user0,user1,user2, 表结构要一样

spring.shardingsphere.datasource.names=master

# 数据源 db01做主库
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/sharding_db01?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# 数据分表规则
# 逻辑表(认作单表)是user, 实际指定所需分的表 -> user0,user1,user2
spring.shardingsphere.sharding.tables.user.actual-data-nodes=master.user$->{0..2}
# 指定主键
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
# 分表规则为主键除以3取模
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user$->{id % 3}

# 打印sql
spring.shardingsphere.props.sql.show=true

此时对于插入, 分表插入到user0,1,2. 而查询则从三张表一起查出

分库分表

分两个库, 对性别拆. 且分表, 对id奇偶数分表

spring.shardingsphere.datasource.names=db0,db1

spring.shardingsphere.datasource.db0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db0.url=jdbc:mysql://localhost:3306/sharding_db01?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.db0.username=root
spring.shardingsphere.datasource.db0.password=123456

spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding_db02?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=123456

# 数据节点：数据源$->{0..N}.逻辑表名$->{0..N}
# 指定数据源节点规则，指定数据库数据表分片，下面指定为库为db0-db1两个 ，表指定user0-user1两张，这种方式在使用的时候首先要确保有对应的数据库以及表才能进行使用
spring.shardingsphere.sharding.tables.user.actual-data-nodes=db$->{0..1}.user$->{0..1}
# 拆分库策略，也就是什么样子的数据放入放到哪个数据库中。
# 指定拆分条件，性别%2 成功走的是db0库 否则走的是db1库
spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=sex
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=db$->{sex % 2}
# 拆分表策略，也就是什么样子的数据放入放到哪个数据表中。[分库不分表可以不要这个]
# 拆分表条件age%2 成功走的user0表 失败走的user1表
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user$->{id % 2}

查询从两个库四个表一起查出汇总输出, 而插入则先拆库, 后拆表插入

读写分离, 不分库分表

// TODO

读写分离, 分库分表

数据库设计

分库 ms单库分库分为 ms0库 和 ms1库。按性别
分表 user单表分为user0表 和 user1表。按id奇偶数
读写分离 数据写入ms0库 和 ms1库，数据读取 sl0库 和 sl1库。

#数据源 
spring.shardingsphere.datasource.names=master0,slave0,master1,slave1

spring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0.url=jdbc:mysql://localhost:3306/ms0?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=123456

spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://localhost:3306/sl0?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=123456

spring.shardingsphere.datasource.master1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.url=jdbc:mysql://localhost:3306/ms1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=123456

spring.shardingsphere.datasource.slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.url=jdbc:mysql://localhost:3306/sl1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456

#根据性别分库
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=sex
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=master$->{sex % 2}
#根据id分表
spring.shardingsphere.sharding.tables.user.actual-data-nodes=master$->{0..1}.user$->{0..1}
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user$->{id % 2}

#指定master0为主库，slave0为它的从库
spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave0
#指定master1为主库，slave1为它的从库
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave1

# 打印sql
spring.shardingsphere.props.sql.show=true