ShardingJDBC

ShardingJDBC

1. 概述

Apache ShardingSphere 是一款开源分布式数据库生态项目，旨在碎片化的异构数据库上层构建生态，在最大限度的复用数据库原生存算能力的前提下，进一步提供面向全局的扩展和叠加计算能力。其核心采用可插拔架构，对上以数据库协议及 SQL 方式提供诸多增强功能，包括数据分片、访问路由、数据安全等。

是一款轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

适用于任何基于 JDBC 的 ORM 框架，如：JPA，Hibernate， Mybatis，Spring JDBC Template 或直接使用 JDBC。

支持任何第三方的数据库连接池，如：DBCP，C3P0，BoneCP，Druid，HikariCP 等。
支持任意实现 JDBC 规范的数据库，目前支持 MySQL，Oracle，SQLServer，PostgreSQL 以及任何遵循 SQL92 标准的数据库。

Sharding-JDBC

Sharding-Proxy

混合架构

Apache ShardingSphere的功能

• 功能列表
  • 数据分片
  • 分库 & 分表
  • 读写分离
  • 分片策略定制化
  • 无中心化分布式主键
• 分布式事务
  • 标准化事务接口
  • XA 强一致事务
  • 柔性事务
  • 数据库治理
• 分布式治理
  • 弹性伸缩
  • 可视化链路追踪
  • 数据加密

Apache ShardingSphere数据分片

SQL 解析

分为词法解析和语法解析。 先通过词法解析器将 SQL 拆分为一个个不可再分的单词。再使用语法解析器对 SQL 进行理解，并最终提炼出解析上下文。 解析上下文包括表、选择项、排序项、分组项、聚合函数、分页信息、查询条件以及可能需要修改的占位符的标记。

查询优化

合并和优化分片条件，如 OR 等。

SQL 路由

根据解析上下文匹配用户配置的分片策略，并生成路由路径。目前支持分片路由和广播路由。

SQL 改写

将 SQL 改写为在真实数据库中可以正确执行的语句。SQL 改写分为正确性改写和优化改写。

SQL 执行

通过多线程执行器异步执行。

结果归并

将多个执行结果集归并以便于通过统一的 JDBC 接口输出。结果归并包括流式归并、内存归并和使用装饰者模式的追加归并。

2. MySQL安装

http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/

rpm -ivh http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.yum

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
yum -y install mysql-community-server

systemctl start mysqld   # 启动MySQL
systemctl status mysqlld # 查看启动状态
systemctl stop mysqld    # 停止MySQL
systemctl enable mysqld  # 开机自启动


grep "password" /var/log/mysqld.log  # 使用此命令查看默认密码
[root@summer1245 ~]# mysql -uroot -pQ5oZnaK.Sdjj  登录数据库
# 数据库默认密码规则必须携带大小写字母、特殊符号，字符长度大于8否则会报错。
# 因此设定较为简单的密码时需要首先修改set global validate_password_policy和_length参数值。
set global validate_password_policy=0;
set global validate_password_length=1;
set password for root@localhost = password('root');

# 至此现在还不能用可视化的客户端进行连接，需要我们进行授权：
grant all on *.* to root@'%' identified by '数据库密码';
flush privileges;
# 现在就可以用Navicat连接数据库了   记得阿里云修改

3. 主从复制

主从复制，允许将来自一个MySQL数据库服务器（主）的数据复制到一个或者多个MySQL数据库服务器（从）。

复制过程是异步的，从服务器不需要永久连接主服务器的更新，可以配置复制的数据库，也可以是某个表。

实现主从复制需要保证一个前提：主服务器必须开启二进制日志

大体流程：

• 从服务器上面启动一个 I/O thread，通过配置好的用户名和密码，连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。
• 从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。每个从服务器都会收到主服务器二进制日志的全部内容的副本。
• 从服务器设备负责决定应该执行二进制日志中的哪些语句。
  除非指定，否则主二进制日志中的所有事件都在从上执行，如果需要，可以将从配置为仅处理一些特定数据库或表的事件。

# 在主服务器修改以下配置
vim /etc/my.cnf

## 同一局域网内注意要唯一
server-id=100  
## 开启二进制日志功能，可以随便取（关键）
log-bin=mysql-bin
## 复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed

# 修改后重启

# 在从服务器修改以下配置
> vim /etc/my.cnf
[mysqld]
## 设置server_id,注意要唯一
server-id=102
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin
##复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
## 如果需要同步函数或者存储过程
log_bin_trust_function_creators=true
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

在主服务器执行

# 授予slave服务器可以同步master服务
grant replication slave, replication client on *.* to 'root'@'slave服务的ip' identified by 'slave服务器的密码';
flush privileges;
# 查看MySQL现在有哪些用户及对应的IP权限(可以不执行，只是一个查看)
select user,host from mysql.user;

查询master服务的binlog文件名和位置

show master status;

slave进行关联master节点

change master to master_host='master服务器ip', master_user='root', master_password='master数据库密码', master_port=3306, master_log_file='mysql-bin.000001',master_log_pos=626;

slave查看主从同步状态

# 启动主从复制
start slave;
# 查看主从同步状态
show slave status\G;

# 停止复制
stop slave;

可能出现的错误问题：

1. Slave_IO_Running：Connecting

# 检查以下内容
# 网络不通
# 检查ip,端口
# 密码不对
# 检查是否创建用于同步的用户和用户密码是否正确
# pos不对
# Position不对

2. Slave_SQL_Running: No

造成这类问题的原因一般是在主从复制的时候，基于创建表，然后又去删除和操作了数据表或者表。

# 解决方案1
# 先stop slave，然后执行了一下提示的语句，再
stop slave;
set global sql_slave_skip_counter=1;
start slave; 
show slave status\G;

# 解决方案2
# master节点执行,获取日志文件和position
show master status;
# slave节点重新绑定
stop slave;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=626; 
start slave;

4. 代码具体实现

创建一个SpringBoot项目

<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.2.2</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.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-core-common</artifactId>
    <version>4.0.0-RC1</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.22</version>
</dependency>

配置文件

server:
  port: 8085
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源取别名，下面的ds1,ds2,ds3任意取名字
      names: ds1,ds2,ds3
      # 给master-ds1每个数据源配置数据库连接信息
      ds1:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds2-slave
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds3-slave
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
    # 配置默认数据源ds1
    sharding:
     # 默认数据源，主要用于写，注意一定要配置读写分离 ,注意：如果不配置，那么就会把三个节点都当做从slave节点，新增，修改和删除会出错。
      default-data-source-name: ds1
    # 配置数据源的读写分离，但是数据库一定要做主从复制
    masterslave:
      # 配置主从名称，可以任意取名字
      name: ms
      # 配置主库master，负责数据的写入
      master-data-source-name: ds1
      # 配置从库slave节点
      slave-data-source-names: ds2,ds3
      # 配置slave节点的负载均衡均衡策略，采用轮询机制  随机的话是random
      load-balance-algorithm-type: round_robin
# 整合mybatis的配置XXXXX
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.hua.pojo

如果不做上面的shardingjdbc配置，那么shardingjdbc会采用随机的方式进行选择数据源。如果不配置default-data-source-name，那么就会把三个节点都当做从slave节点，那么新增，修改和删除会出错。

dao

@Repository
public interface UserMapper {
    int addUser(User user);
    List<User> getUserList();
}

主函数记得扫描包

@SpringBootApplication
@MapperScan("com.hua.dao")
public class DemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hua.dao.UserMapper">
    <insert id="addUser" parameterType="user">
        insert into user(id,name,age)
        values(#{id},#{name},#{age})
    </insert>
    <select id="getUserList" resultType="user">
        select * from user
    </select>
</mapper>

userService

public interface UserService {

    int addUser(User user);

    List<User> getUserList();
}

UserServiceImpl

@Service
public class UserServiceImpl implements UserService{

    @Autowired
    UserMapper userMapper;
    @Override
    public int addUser(User user) {
        return userMapper.addUser(user);
    }

    @Override
    public List<User> getUserList() {
        return userMapper.getUserList();
    }
}

UserController

@RestController
public class UserController {
    @Autowired
    UserService userService;

    @RequestMapping("/add")
    public String addUser(){
        User user = new User();
        user.setId(1);
        user.setName("小明");
        user.setAge(15);
        int i = userService.addUser(user);
        if (i>0){
            return "添加成功";
        }else{
            return "添加失败";
        }
    }

    @RequestMapping("/getUserlist")
    public List<User> getUserList(){
        return userService.getUserList();
    }
}

SQL: insert into user(name,age) values(?,?) ::: DataSources: ds1
# 执行插入的时候是在主节点
# 执行查询的时候是在从节点  两个从节点轮流读

数据库中需要有user表

读写分离建立在主从复制的基础上

5. 分库分表

1. 为什么要分库分表？

1. 高并发的情况下，会造成IO读写频繁，自然就造成读写缓慢，甚至宕机。一般单库不要超过2k并发。
2. 数据量大的问题，由于MySQL的索引使用的B+Tree，数据量很大的话，会导致索引树特别大，造成查询缓慢。innodb的最大存储限制在64TB.

目的：解决高并发，数据量大的问题。将一个表拆分为N个表，让每个表的数据量控制在一定范围内，保证性能。一个表的数据量不要超过500W。

2. 拆分方式

• 水平拆分：统一将一个表的数据拆分到不同库不同表，可以根据时间、地区或某个业务维度。拆分后的表结构一致。
• 垂直拆分：将有很多字段的表拆分为多个表或者多个库，拆分后的表结构不一致，可以根据业务维度进行拆分，如订单表可以拆分为订单、订单支持、订单地址等；也可以根据冷热程度拆分，20%的热点字段拆到一个表，80%冷门字段拆到另一个表。

总之就是垂直拆分就是按照业务来拆，水平拆分就是对表进行拆分，表的垂直拆分就是表的字段过多，字段使用的频率不一，此时可以拆为两个一对一关系的表

3. 逻辑表

水平拆分数据库或者数据表的相同数据结构表的总称，例用户表拆分为两个user0和user1，逻辑表名就是user

spring:
  shardingsphere:
    sharding:
      tables:
        # user 逻辑表名
        user:

4. 数据节点

tables:
        # user 逻辑表名
        user:
          # 数据节点：多数据源$->{0..N}.逻辑表名$->{0..N} 相同表
          actual-data-nodes: ds$->{0..2}.user$->{0..1}
          # 数据节点：多数据源$->{0..N}.逻辑表名$->{0..N} 不同表
          actual-data-nodes: ds0.user$->{0..1},ds1.user$->{2..4}
          # 指定单数据源的配置方式
          actual-data-nodes: ds0.user$->{0..4}
          # 全部手动指定
          actual-data-nodes: ds0.user0,ds1.user0,ds0.user1,ds1.user1,

图片转载于：

https://www.kuangstudy.com/zl/sharding#1369532584442216449

5. 分库策略

数据源分片：

• 数据源分片（分片键、分片算法）
• 表分片（分片键、分片算法）

1. none

对应NoneShardingStragey 不分片策略 sql会发给所有的节点

2. inline（重点）

对应InlineShardingStragey 使用Groovy表达式。提供对sql语句中的=和in的支持，只支持单分片键，例如user$(userid%5) 表示user表通过字段userid模5进行分表，分为user0–user4

server:
  port: 8085
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: ds0,ds1
      ds0:
      # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds2-slave
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      default-data-source-name: ds0
      tables:
        user:
          actual-data-nodes: ds$->{0..1}.user$->{0..1}
          database-strategy:
            inline:
              sharding-column: age    # 分片字段（分片键）
              algorithm-expression: ds$->{age%2} # 分片算法表达式
            # 拆分表策略，也就是什么样子的数据放入放到哪个数据表中。
          table-strategy:
            inline:
              sharding-column: age    # 分片字段（分片键）
              algorithm-expression: user$->{age%2} # 分片算法表达式



mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.hua.pojo

@Autowired
UserService userService;
@Test
void contextLoads() {
    User user = new User();
    user.setName("小明");
    user.setAge(15);  // 15%2=1 所以此条数据就会放到 1库1表中
    userService.addUser(user);
}

server:
  port: 8085
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源取别名，下面的ds1,ds1任意取名字
      names: ds0,ds1
      # 给master-ds1每个数据源配置数据库连接信息
      ds0:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/ksd-sharding-db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds1-slave
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/ksd-sharding-db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
    # 配置默认数据源ds0
    sharding:
      default-data-source-name: ds0
      # 配置分表的规则
      tables:
        # user 逻辑表名
        user:
          # 数据节点：数据源$->{0..N}.逻辑表名$->{0..N}
          actual-data-nodes: ds$->{0..1}.user$->{0..1}
          # 拆分库策略，也就是什么样子的数据放入放到哪个数据库中。
          database-strategy:
            inline:
              sharding-column: sex    # 分片字段（分片键）
              algorithm-expression: ds$->{sex%2} # 分片算法表达式  性别为0的放在0库，性别为1的放在1库
          # 拆分表策略，也就是什么样子的数据放入放到哪个数据表中。
          table-strategy:
            inline:
              sharding-column: age    # 分片字段（分片键）
              algorithm-expression: user$->{age % 2} # 分片算法表达式 年龄为偶数的放在0表,奇数的放在1表
# 整合mybatis的配置XXXXX
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.hua.pojo

3. 根据日期时间 按照标准规则分库分表

标准分片 standard

对应StrandardShardingStrategy提供对SQL语句中的=，in和between and的分片操作支持。

StrandardShardingStrategy只支持单分片键。提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。

PreciseShardingAlgorithm是必选的，用于处理=和in的分片

RangeShardingAlgorithm是可选的，是用于处理between and分片，如果不配置和RangeShardingAlgorithm，SQL的between and将按照全库路由处理。

server:
  port: 8085

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.hua.pojo

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      default-data-source-name: ds0
      tables:
        user:
          actual-data-nodes: ds$->{0..1}.user$->{0..1}
          database-strategy:
            standard:
              shardingColumn: birthday
              preciseAlgorithmClassName: com.hua.algorithm.BirthdayAlgorithm
          table-strategy:
            inline:
              sharding-column: age 
              algorithm-expression: user$->{age%2} 

public class BirthdayAlgorithm implements PreciseShardingAlgorithm<Date> {

    List<Date> dateList = new ArrayList<>();
    {
        Calendar calendar1 = Calendar.getInstance();
        calendar1.set(2020,1,1,0,0,0);
        Calendar calendar2 = Calendar.getInstance();
        calendar2.set(2021,1,1,0,0,0);
        Calendar calendar3 = Calendar.getInstance();
        calendar3.set(2022,1,1,0,0,0);
        dateList.add(calendar1.getTime());
        dateList.add(calendar2.getTime());
        dateList.add(calendar3.getTime());
    }


    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        // 获取数据库的真实值
        Date date = preciseShardingValue.getValue();
        // 获取数据源的名称信息列表
        Iterator<String> iterator = collection.iterator();
        String target = null;
        for (Date date1 : dateList) {
            target = iterator.next();
            System.out.println(target);
            if(date.before(date1)){
                break;
            }
        }
        return target; // 最后返回的是ds0 ds1
    }
}

@Autowired
UserService userService;

@Test
void contextLoads() throws ParseException {
    User user = new User();
    user.setName("小红");
    user.setAge(16);
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    Date date = format.parse("2021-9-6 10:20:30");
    user.setBirthday(date);
    userService.addUser(user);
}

此时运行会报错：

原因是：

List<Date> dateList = new ArrayList<>();
{
    Calendar calendar1 = Calendar.getInstance();
    calendar1.set(2020,1,1,0,0,0);
    Calendar calendar2 = Calendar.getInstance();
    calendar2.set(2021,1,1,0,0,0);
    Calendar calendar3 = Calendar.getInstance();
    calendar3.set(2022,1,1,0,0,0);
    dateList.add(calendar1.getTime());
    dateList.add(calendar2.getTime());
    dateList.add(calendar3.getTime());
}


@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
    // 获取数据库真实值
    Date date = preciseShardingValue.getValue();
    // 获取数据源的名称信息列表
    Iterator<String> iterator = collection.iterator(); //此处迭代器遍历数据源,只有两个
    String target = null;
    for (Date date1 : dateList) { // 但是此处遍历定义的日期有三个,而且测试用例会匹配最后一个日期,然而迭代器此时就会溢出,所以此处减少一个日期
        target = iterator.next();
        System.out.println(target);
        if(date.before(date1)){
            break;
        }
    }
    return target; // 最后返回的是ds0 ds1
}


List<Date> dateList = new ArrayList<>();
{
    Calendar calendar1 = Calendar.getInstance();
    calendar1.set(2020,1,1,0,0,0);
    Calendar calendar2 = Calendar.getInstance();
    calendar2.set(2022,1,1,0,0,0);
    dateList.add(calendar1.getTime());
    dateList.add(calendar2.getTime());
}

此时如果进行查询的话，会将四张表的数据都会查询出来

ShardingSphere-SQL                       : Actual SQL: ds0 ::: select * from user0
ShardingSphere-SQL                       : Actual SQL: ds0 ::: select * from user1
ShardingSphere-SQL                       : Actual SQL: ds1 ::: select * from user0
ShardingSphere-SQL                       : Actual SQL: ds1 ::: select * from user1

[{"id":5,"name":"小明","age":16,"birthday":"1998-09-05T16:00:00.000+00:00"},
 {"id":6,"name":"小明","age":16,"birthday":"1998-09-05T16:00:00.000+00:00"},
 {"id":7,"name":"小绿","age":16,"birthday":"2019-09-05T16:00:00.000+00:00"},
 {"id":5,"name":"小明","age":16,"birthday":"1998-09-05T16:00:00.000+00:00"},
 {"id":6,"name":"小明","age":16,"birthday":"1998-09-05T16:00:00.000+00:00"},
 {"id":7,"name":"小红","age":16,"birthday":"2021-09-05T16:00:00.000+00:00"}]

这里把主键设置为了自增。实际的话不能设置自增，这样主键会冲突，这里要注意 因为主从库的读写分离代码已经删除了，但是从库复制主库的内容仍然有效，也就是说如果往主库里插入内容，从库依然会复制，但是往从库里插入内容，主库是没有的

按年月进行分库分表

inline方式

server:
  port: 8085

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.hua.pojo

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
    # 只分表
    sharding:
      default-data-source-name: ds0
      tables:
        user_order:
          actual-data-nodes: ds0.user_order_$->{2021..2022}${(1..3).collect{t ->t.toString().padLeft(2,'0')} }
          key-generator:
            column: orderid
            type: SNOWFLAKE
          table-strategy:
             inline:
               shardingColumn: yearmonth  # 根据该字段进行分表
               algorithmExpression: user_order_$->{yearmonth}
               
     # 分库分表
     sharding:
       default-data-source-name: ds0
       tables:
          user_order:
          	actual-data-nodes: ds$->{0..1}.user_order_$->{2021..2022}${(1..3).collect{t ->t.toString().padLeft(2,'0')} }
          	key-generator:
            	column: orderid
            	type: SNOWFLAKE
          	database-strategy:
          	 	inline:
          	    	shardingColumn: orderid
          	    	algorithmExpression: ds$->{orderid%2}
          	table-strategy:
             	inline:
               		shardingColumn: yearmonth  # 根据该字段进行分表
               		algorithmExpression: user_order_$->{yearmonth}

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Order {
    private Long orderid;
    private String ordernumber;
    private Long userid;
    private Date createtime;
    private String yearmonth;
}

dao

@Repository
public interface OrderMapper {
    void addOrder(Order order);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hua.dao.OrderMapper">
    <insert id="addOrder" parameterType="order">
        insert into user_order(userid,ordernumber,createtime,yearmonth)
        values(#{userid},#{ordernumber},#{createtime},#{yearmonth})
    </insert>
</mapper>

@Autowired
OrderMapper orderMapper;
@Test
void test(){
    Order order = new Order();
    order.setUserid(1L);
    order.setOrdernumber("133455678");
    order.setCreatetime(new Date());
    order.setYearmonth("202103");   // 之后就可以在这里设置往那个表里写入
    orderMapper.addOrder(order);
}

Logic SQL: insert into user_order(userid,ordernumber,createtime,yearmonth)  values(?,?,?,?)
Actual SQL: ds0 ::: insert into user_order_202103 (userid, ordernumber, createtime, yearmonth, orderid) VALUES (?, ?, ?, ?, ?) ::: [1, 133455678, 2022-07-08 16:14:28.298, 202103, 752200056693587969]

standard方式

server:
  port: 8085

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.hua.pojo

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      default-data-source-name: ds0
      tables:
        user_order:
          actual-data-nodes: ds0.user_order_$->{2021..2022}${(1..3).collect{t ->t.toString().padLeft(2,'0')} }
          key-generator:
            column: orderid
            type: SNOWFLAKE
          table-strategy:
             standard:
               shardingColumn: yearmonth
               preciseAlgorithmClassName: com.hua.algorithm.YearMonthShardingAlgorithm
             # inline:
              # shardingColumn: yearmonth  # 根据该字段进行分表
              # algorithmExpression: user_order_$->{yearmonth}

public class YearMonthShardingAlgorithm implements PreciseShardingAlgorithm<String> {
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> shardingValue) {
        String tbName = shardingValue.getLogicTableName() + "_" + shardingValue.getValue(); // 这里就是拼接
        System.out.println("Sharding input:" + shardingValue.getValue() + ", output:{}" + tbName);
        return tbName;
    }
}

4. 符合分片策略

对应：HintShardingStrategy。通过Hint而非SQL解析的方式分片的策略。

对于分片字段非SQL决定，而是由其他外置条件决定的场景，使用SQL hint灵活的注入分片字段。例如：按照用户登录的时间，主键等进行分库，而数据库中并无此字段。SQL hint支持通过Java API和SQL注解两种方式使用。让分库分表更加灵活。

5. hint分片策略

对应ComplexShardingStrategy。符合分片策略提供对SQL语句中的=，in和between and的分片操作支持。

ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键组合以及分片操作符透传至分片算法，完全由开发者自己实现，提供最大的灵活度。

6. 分布式主键配置

ShardingSphere提供灵活的配置分布式主键生成策略方式。在分片规则配置模块配置每个表的主键生成策略。默认使用雪花算法。（snowflake）生成64bit的长整型数据。支持两种方式配置

• SNOWFLAKE 这里数据库改成bigint 实体类改为BigInteger
• UUID 数据库要改成varchar 实体类改为String

这里切记：主键列不能自增长。数据类型是：bigint(20) 这里需要进行修改实体类和数据库 sql语句是不需要加 id字段的

spring:
  shardingsphere:
    sharding:
      tables:
        # user 逻辑表名
        user:
          key-generator:
              # 主键的列名
            column: id
            type: SNOWFLAKE  

7. 分页实现

dao

List<Order> getOrder(@Param("pageNo") int pageNo,@Param("pageSize") int pageSize);
int getOrderCount();

<select id="getOrder" resultType="order">
    select *
    from user_order
    limit #{pageNo},#{pageSize}
</select>
<select id="getOrderCount" resultType="_int">
    select count(1)
    from user_order
</select>

@Test
public void test1(){
    List<Order> orderList = orderMapper.getOrder(0, 5);
    for (Order order : orderList) {
        System.out.println(order);
    }
}
@Test
public void test2(){
    int orderCount = orderMapper.getOrderCount();
    System.out.println(orderCount);
}

: Logic SQL: select count(1) from user_order

: Actual SQL: ds0 ::: select count(1) from user_order_202101
: Actual SQL: ds0 ::: select count(1) from user_order_202102
: Actual SQL: ds0 ::: select count(1) from user_order_202103
: Actual SQL: ds0 ::: select count(1) from user_order_202201
: Actual SQL: ds0 ::: select count(1) from user_order_202202
: Actual SQL: ds0 ::: select count(1) from user_order_202203
: Actual SQL: ds1 ::: select count(1) from user_order_202101
: Actual SQL: ds1 ::: select count(1) from user_order_202102
: Actual SQL: ds1 ::: select count(1) from user_order_202103
: Actual SQL: ds1 ::: select count(1) from user_order_202201
: Actual SQL: ds1 ::: select count(1) from user_order_202202
: Actual SQL: ds1 ::: select count(1) from user_order_202203
7

8. 事务管理

在单一数据节点中，事务仅限于对单一数据库资源的访问控制，称之为本地事务。几乎所有的成熟的关系型数据库都提供了对本地事务的原生支持。 但是在基于微服务的分布式应用环境下，越来越多的应用场景要求对多个服务的访问及其相对应的多个数据库资源能纳入到同一个事务当中，分布式事务应运而生。

关系型数据库虽然对本地事务提供了完美的ACID原生支持。 但在分布式的场景下，它却成为系统性能的瓶颈。如何让数据库在分布式场景下满足ACID的特性或找寻相应的替代方案，是分布式事务的重点工作。

server:
  port: 8085

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.hua.pojo

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      default-data-source-name: ds0
      tables:
        user:
          key-generator:
            column: id
            type: SNOWFLAKE
          actual-data-nodes: ds$->{0..1}.user$->{0..1}
          database-strategy:
             inline:
                sharding-column: age    # 分片字段（分片键）
                algorithm-expression: ds$->{age%2} # 分片算法表达式
          table-strategy:
             inline:
                sharding-column: age    # 分片字段（分片键）
                algorithm-expression: user$->{age%2} # 分片算法表达式
        user_order:
          actual-data-nodes: ds$->{0..1}.user_order_$->{2021..2022}${(1..3).collect{t ->t.toString().padLeft(2,'0')} }
          key-generator:
            column: orderid
            type: SNOWFLAKE

          database-strategy:
             inline:
               shardingColumn: orderid
               algorithmExpression: ds$->{orderid%2}
          table-strategy:
            standard:
              shardingColumn: yearmonth
              preciseAlgorithmClassName: com.hua.algorithm.YearMonthShardingAlgorithm

// 没有用到事务  也有没任何异常

@Test
public void testTransaction(){
    User user = new User();
    user.setName("陈一");
    user.setAge(15);
    user.setBirthday(new Date());
    userService.addUser(user);   // 立即写入数据库

    Order order = new Order();
    order.setUserid(1L);
    order.setOrdernumber("133455678");
    order.setCreatetime(new Date());
    order.setYearmonth("202103");
    orderMapper.addOrder(order);
}
// Actual SQL: ds1 ::: insert into user1 (name, age, birthday, id) VALUES (?, ?, ?, ?) ::: [陈一, 15, 2022-07-08 19:09:54.607, 752244207174811649]
// Actual SQL: ds1 ::: insert into user_order_202103 (userid, ordernumber, createtime, yearmonth, orderid) VALUES (?, ?, ?, ?, ?) ::: [1, 133455678, 2022-07-08 19:09:55.168, 202103, 752244207841705985]

// 没有用到事务 模拟出现异常

@Test
public void testTransaction(){
    User user = new User();
    user.setName("刘二");
    user.setAge(16);
    user.setBirthday(new Date());
    userService.addUser(user);

    int a = 1/0;

    Order order = new Order();
    order.setUserid(1L);
    order.setOrdernumber("133455678");
    order.setCreatetime(new Date());
    order.setYearmonth("202104");
    orderMapper.addOrder(order);
}
// : Actual SQL: ds0 ::: insert into user0 (name, age, birthday, id) VALUES (?, ?, ?, ?) ::: [刘二, 16, 2022-07-08 19:16:06.769, 752245769007792129]
// 此时刘二已经写入数据库,但是order因为错误没有执行,所以就会出现数据不一致的问题

// 使用事务,模拟出现异常,是否进行回滚
@Transactional(rollbackFor = Exception.class)
public void testTransaction(){
    User user = new User();
    user.setName("刘二");
    user.setAge(16);
    user.setBirthday(new Date());
    userService.addUser(user);

    int a = 1/0;

    Order order = new Order();
    order.setUserid(1L);
    order.setOrdernumber("133455678");
    order.setCreatetime(new Date());
    order.setYearmonth("202104");
    orderMapper.addOrder(order);
}

此时使用事务回滚是有效的，但是一旦使用分布式服务，就得使用分布式事务

怎么做呢？

<!--导入依赖-->
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-transaction-spring-boot-starter</artifactId>
    <version>3.1.0</version>
</dependency>

// 添加  @ShardingTransactionType(TransactionType.XA)
@Transactional(rollbackFor = Exception.class)
@ShardingTransactionType(TransactionType.XA)
public void testTransaction(){
    User user = new User();
    user.setName("刘二");
    user.setAge(16);
    user.setBirthday(new Date());
    userService.addUser(user);

    int a = 1/0;

    Order order = new Order();
    order.setUserid(1L);
    order.setOrdernumber("133455678");
    order.setCreatetime(new Date());
    order.setYearmonth("202104");
    orderMapper.addOrder(order);
}

6. 规范

6.1 基础规范

• 表必须有主键，可以使用整型作为主键 字符串占用空间大
• 避免使用外键，表之间的关联性和完整性通过应用层来控制
• 表在设计之初，应该考虑到大致的数据级，若表记录小于1000W，尽量使用单表，不建议分表
• 建议将大字段，访问频率低，或者不需要作为筛选条件的字段拆分到拓展表中（做好表垂直拆分）
• 控制单实例表的总数，单个表分表数控制在1024以内。

6.2 列设计规范

• 正确区分tinyint、int、bigint的范围
• 使用varchar(20)存储手机号，不要使用整数
• 使用int存储ipv4 不要使用char(15)
• 涉及金额使用decimal/varchar，并指定精度
• 不要设计为null的字段，而是用空字符，因为null需要更多的空间，并且使得索引和统计变得更复杂。空列会造成索引失效

6.3 索引规范

• 唯一索引使用uniq_[字段名]来命名
• 非唯一索引使用idx_[字段名]来命名
• 不建议在频繁更新的字段上建立索引 索引会重建
• 非必要不要进行join，如果要进行join查询，被join的字段必须类型相同，并建立索引。
• 单张表的索引数量建议控制在5个以内，索引过多，不仅会导致插入更新性能下降，还可能导致MYSQL的索引出错和性能下降
• 组合索引字段数量不建议超过5个，理解组合索引的最左匹配原则（查询条件要有最左侧的索引），避免重复建设索引。比如建立了(x,y,z) 相当于建立了(x),(x,y),(x,y,z)

6.4 SQL规范

• 禁止使用selet * ，只获取必要字段，select*会增加cpu/i0/内存、带宽的消耗
• insert 必须指定字段，禁止使用insert into Table values()，指定字段插入，在表结果变更时，能保证对应应用程序无影响
• 隐式类型转换会使索引失效，导致全表扫描。（比如：手机号码搜索时未转换成字符串）
• 禁止在where后面的查询列使用内置函数或者表达式，导致不能命中索引，导致全表扫描
• 禁止负向查询（!=,not like ,no in等）以及%开头的模糊查询，造成不能命中索引，导致全表扫描
• 避免直接返回大结果集造成内存溢出，可采用分段和游标方式
• 返回结果集时尽量使用limit分页显示 如果查这样的数据 limit 10000，50 会很慢，这时可以采用子查询先将id<10000的id过滤掉，直接加上 where id > 10000
• 尽量在order by/group by的列上创建索引
• 扫描尽量放在镜像库上去做
• 禁止大表join查询和子查询
• 尽量避免数据库内置函数作为查询条件
• 应用程序尽量捕获SQL异常

6.5 表的垂直拆分

垂直拆分：业务模块拆分、商品库，用户库，订单库
水平拆分：对表进行水平拆分（也就是我们说的：分表）
表进行垂直拆分：表的字段过多，字段使用的频率不一。（可以拆分两个表建立1:1关系）

• 将一个属性过多的表，一行数据较大的表，将不同的属性分割到不同的数据库表中。以降低单库表的大小
  特点：
• 每个表的结构不一致
• 每个表的数量都是全量
• 表和表之间一定会有一列会进行关联，一般都是主键

原则：

• 将长度较短，访问频率较高的字段放在一个表中，主表
• 将长度较长、访问频率比较低的字段放一个表中，从表
• 将经常访问字段放一个表中
• 所有表的并集是全量数据

6.6 添加字段

场景：在开发时，有时需要给表加字段，在大数据量且分表的情况下，怎么样平滑添加
1：直接alter table add column，数据量大时不建议，（会产生写锁）

alter table user add column api_pay_no varchar(32) not null  comment '用户扩展订单号'
alter table user add column api_pay_no varchar(32) not null unique comment '用户扩展订单号'

2：提前预留字段（空间浪费，预留多少很难控制，拓展性差）（推荐）
3：新增一张表，（增加字段），迁移原表数据，在重新命名新表作为原表
4：放入extinfo（无法使用索引）
5：提前设计，使用key/value方法存储，新增字段时 ，直接加一个key就好（推荐）

参考：狂神-飞哥

https://www.bilibili.com/video/BV1ei4y1K7dn?p=15&vd_source=575dca31012fff65131e575b5bef42a1