https://www.bilibili.com/video/BV1ei4y1K7dn

ShardingShpere数据库中间件

Apache ShardingSphere 是一套开源的分布式数据库解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（规划中）这 3 款既能够独立部署，又支持混合部署配合使用的产品组成。 它们均提供标准化的数据水平扩展、分布式事务和分布式治理等功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

一、ShardingJdbc

1.1、概述

官网：http://shardingsphere.apache.org/index_zh.html
下载地址：https://shardingsphere.apache.org/document/current/cn/downloads/
快速入门：https://shardingsphere.apache.org/document/current/cn/quick-start/shardingsphere-jdbc-quick-start/

以下来自官网的原话：

Apache ShardingSphere 是一套开源的分布式数据库解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（规划中）这 3 款既能够独立部署，又支持混合部署配合使用的产品组成。 它们均提供标准化的数据水平扩展、分布式事务和分布式治理等功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

Apache ShardingSphere 旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。 关系型数据库当今依然占有巨大市场份额，是企业核心系统的基石，未来也难于撼动，我们更加注重在原有基础上提供增量，而非颠覆。

Apache ShardingSphere 5.x 版本开始致力于可插拔架构，项目的功能组件能够灵活的以可插拔的方式进行扩展。 目前，数据分片、读写分离、数据加密、影子库压测等功能，以及 MySQL、PostgreSQL、SQLServer、Oracle 等 SQL 与协议的支持，均通过插件的方式织入项目。 开发者能够像使用积木一样定制属于自己的独特系统。Apache ShardingSphere 目前已提供数十个 SPI 作为系统的扩展点，仍在不断增加中。

ShardingSphere 已于2020年4月16日成为 Apache 软件基金会的顶级项目。

1.2、关于改名问题

在3.0以后就更改成了ShardingSphere。

1.3、认识shardingjdbc

定位为轻量级 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 标准的数据库。

1.4、认识shardingjdbc功能架构图

1.5、认识Sharding-Proxy

定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。 目前提供 MySQL 和 PostgreSQL 版本，它可以使用任何兼容 MySQL/PostgreSQL 协议的访问客户端(如：MySQL Command Client, MySQL Workbench, Navicat 等)操作数据，对 DBA 更加友好。

• 向应用程序完全透明，可直接当做 MySQL/PostgreSQL 使用。
• 适用于任何兼容 MySQL/PostgreSQL 协议的的客户端。

1.6、三个组件的比较

	Sharding-Jdbc	Sharding-Proxy	Sharding-Sidecar
数据库	任意	MYSQL	MYSQL
连接消耗数	高	低	低
异构语言	仅Java	任意	任意
性能	损耗低	损耗高	损耗低
中心化	是	否	是
静态入口	无	有	无

1.7、ShardingJdbc混合架构

ShardingSphere-JDBC 采用无中心化架构，适用于 Java 开发的高性能的轻量级 OLTP（连接事务处理） 应用；ShardingSphere-Proxy 提供静态入口以及异构语言的支持，适用于 OLAP（连接数据分析） 应用以及对分片数据库进行管理和运维的场景。

Apache ShardingSphere 是多接入端共同组成的生态圈。 通过混合使用 ShardingSphere-JDBC 和 ShardingSphere-Proxy，并采用同一注册中心统一配置分片策略，能够灵活的搭建适用于各种场景的应用系统，使得架构师更加自由地调整适合与当前业务的最佳系统架构。

1.8、ShardingShpere的功能清单

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

1.9、 ShardingSphere数据分片内核剖析

ShardingSphere 的 3 个产品的数据分片主要流程是完全一致的。 核心由 SQL 解析 => 执行器优化 => SQL 路由 => SQL 改写 => SQL 执行 => 结果归并的流程组成。

SQL 解析

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

执行器优化

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

SQL 路由

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

SQL 改写

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

SQL 执行

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

结果归并

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

二、ShardingJdbc准备

Linux安装MySQL5.7

2.1、yum安装mysql

1-1、下载mysql的rpm地址

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

1-2、配置Mysql扩展源

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

1-3、yum安装mysql

yum install mysql-community-server -y

1-4、启动Mysql，并加入开机自启

systemctl start mysqld
systemctl stop mysqld
systemctl enable mysqld

1-5、使用Mysq初始密码登录数据库

>grep "password" /var/log/mysqld.log
> mysql -uroot -pma1S8xjuEA/F

或者一步到位的做法如下
>mysql -uroot -p$(awk '/temporary password/{print $NF}' /var/log/mysqld.log)

1-6、修改数据库密码

数据库默认密码规则必须携带大小写字母、特殊符号，字符长度大于8否则会报错。

因此设定较为简单的密码时需要首先修改set global validate_password_policy和_length参数值。

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

1-7、修改密码

mysql> set password for root@localhost = password('mkxiaoer');
Query OK, 0 rows affected, 1 warning (0.00 sec)

或者
mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';

1-8、登录测试

[root@http-server ~]# mysql -uroot -pmkxiaoer
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit

1-9、可视化工具的登录授权：(如果授权不成功，请查看防火墙)

操作完成上面的，现在还不能用可视化的客户端进行连接，需要我们进行授权：

mysql > grant all on *.* to root@'%' identified by '数据库密码';
mysql > flush privileges;

操作完毕，接下来可以使用navicat或者sqlylog进行远程连接了.

sqlylog的下载：https://sqlyog.en.softonic.com/

三、MySql完成主从复制

ShardingJdbc准备 - MySql完成主从复制

概述

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

复制是异步的 从站不需要永久连接以接收来自主站的更新。

根据配置，您可以复制数据库中的所有数据库，所选数据库甚至选定的表。

3.1、MySQL中复制的优点包括：

• 横向扩展解决方案 - 在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。
• 数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。
• 分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。
• 远程数据分发 - 您可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。

3.2、Replication 的原理

前提是作为主服务器角色的数据库服务器必须开启二进制日志

主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。

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

3.3、具体配置如下

3.3.1、 Master节点配置/etc/my.cnf (master节点执行)

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

3.3.2、Slave节点配置/etc/my.cnf (slave节点执行)

> 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

03-03、在master服务器授权slave服务器可以同步权限(master节点执行)

注意：在master服务器上执行

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

3.3.4、查询master服务的binlog文件名和位置(master节点执行)

mysql > show master status;

• 日志文件名：mysql-bin.000002
• 复制的位置：2079

3.3.5、slave进行关联master节点(slave节点执行)

• 进入到slave节点：

  mysql > mysql -uroot -p你slave的密码
  

• 开始绑定

  mysql> change master to master_host='master服务器ip', master_user='root', master_password='master密码', master_port=3306, master_log_file='mysql-bin.000002',master_log_pos=2079;
  

这里注意一下 master_log_file 和 master_log_pos 都是通过 master服务器通过show master status获得。

3.3.6、在slave节点上查看主从同步状态(slave节点执行)

启动主从复制

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

再查看主从同步状态

mysql> show slave status\G;

其他命令 (slave节点执行)

# 停止复制mysql> stop slave;

3.3.7、主从复制测试

1：在master下创建数据库和表，或者修改和新增，删除记录都会进行同步(master节点执行)
2：点击查看slave节点信息(slave节点执行)

3.3.8、切记

在主从复制操作的时候，不要基于去创建数据库或者相关操作。然后又去删除。这样会造成主从复制的pos改变，而造成复制失败，如果出现此类问题，查看04-03的常见问题排查。

3.4、主从复制相关问题排查

3.4.1、主从复制Connecting问题

使用start slave开启主从复制过程后，如果SlaveIORunning一直是Connecting，则说明主从复制一直处于连接状态，这种情况一般是下面几种原因造成的，我们可以根据 Last_IO_Error提示予以排除。

1. 网络不通
   检查ip,端口
2. 密码不对
   检查是否创建用于同步的用户和用户密码是否正确
3. pos不对
   检查Master的 Position

3.4.2、MYSQL镜像服务器因错误停止的恢复 —Slave_SQL_Running: No

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

3.4.3、从MYSQL服务器Slave_IO_Running: No的解决2

• master节点执行，获取日志文件和post

mysql > show master status;

• slave节点进行重新绑定

mysql > stop slave;
mysql > CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=519086591;
mysql > start slave;

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

四、ShardingJdbc的配置及读写分离

4.1、内容大纲

• 新建一个springboot工程
• 引入相关sharding依赖、ssm依赖、数据库驱动
• 定义配置application.yml
• 定义entity、mapper、controller
• 访问测试查看效果
• 小结

4.2、具体实现步骤

4.2.1、 新建一个springboot工程

4.2.2、 引入相关sharding依赖、ssm依赖、数据库驱动

<properties>
    <java.version>1.8</java.version>
    <sharding-sphere.version>4.0.0-RC1</sharding-sphere.version>
</properties>
<dependencies>
    <!-- 依赖web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- 依赖mybatis和mysql驱动 -->
    <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>
    <!--依赖lombok-->
    <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>
    <!--依赖sharding-->
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>${sharding-sphere.version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-core-common</artifactId>
        <version>${sharding-sphere.version}</version>
    </dependency>
    <!--依赖数据源druid-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.22</version>
    </dependency>
</dependencies>

4.2.3、 定义配置application.yml

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://47.115.94.78:3306/ksd-sharding-db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: mkxiaoer1986.
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds2-slave
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://114.215.145.201:3306/ksd-sharding-db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: mkxiaoer1986.
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds3-slave
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://114.215.145.201:3306/ksd-sharding-db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: mkxiaoer1986.
        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节点的负载均衡均衡策略，采用轮询机制
      load-balance-algorithm-type: round_robin
# 整合mybatis的配置XXXXX
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.xuexiangban.shardingjdbc.entity

注意问题：

# 配置默认数据源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节点的负载均衡均衡策略，采用轮询机制
   load-balance-algorithm-type: round_robin

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

4.2.4、 定义mapper、controller,entity

entity

@Data
public class User {
    // 主键
    private Integer id;
    // 昵称
    private String nickname;
    // 密码
    private String password;
    // 性
    private Integer sex;
    // 性
    private String birthday;
}

mapper

public interface UserMapper {
    
    @Insert("insert into ksd_user(nickname,password,sex,birthday) values(#{nickname},#{password},#{sex},#{birthday})")
    void addUser(User user);

    @Select("select * from ksd_user")
    List<User> findUsers();
}

controller

@RestController
@RequestMapping("/user")
public class UserController {
    
    @Autowired
    private UserMapper userMapper;
    
    @GetMapping("/save")
    public String insert() {
        User user = new User();
        user.setNickname("zhangsan"+ new Random().nextInt());
        user.setPassword("1234567");
        user.setSex(1);
        user.setBirthday("1988-12-03");
        userMapper.addUser(user);
        return "success";
    }
    
    @GetMapping("/listuser")
    public List<User> listuser() {
        return userMapper.findUsers();
    }
}

4.2.5、 访问测试查看效果

1：访问 http://localhost:8085/user/save 一直进入到ds1主节点

2：访问 http://localhost:8085/user/listuser 一直进入到ds2、ds3节点，并且轮询进入。

4.2.6、 日志查看

4.2.7、 小结

回顾流程

ShardingSphere 的 3 个产品的数据分片主要流程是完全一致的。 核心由 SQL 解析 => 执行器优化 => SQL 路由 => SQL 改写 => SQL 执行 => 结果归并的流程组成。

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

执行器优化：合并和优化分片条件，如 OR 等。

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

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

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

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

3.3、Props的其他相关配置

acceptor.size: # accept连接的线程数量,默认为cpu核数2倍
executor.size: #工作线程数量最大，默认值: 无限制
max.connections.size.per.query: # 每个查询可以打开的最大连接数量,默认为1
check.table.metadata.enabled: #是否在启动时检查分表元数据一致性，默认值: false
proxy.frontend.flush.threshold: # proxy的服务时候,对于单个大查询,每多少个网络包返回一次
proxy.transaction.type: # 默认LOCAL,proxy的事务模型 允许LOCAL,XA,BASE三个值,LOCAL无分布式事务,XA则是采用atomikos实现的分布式事务 BASE目前尚未实现
proxy.opentracing.enabled: # 是否启用opentracing
proxy.backend.use.nio: # 是否采用netty的NIO机制连接后端数据库,默认False ,使用epoll机制
proxy.backend.max.connections: # 使用NIO而非epoll的话,proxy后台连接每个netty客户端允许的最大连接数量(注意不是数据库连接限制) 默认为8
proxy.backend.connection.timeout.seconds: #使用nio而非epoll的话,proxy后台连接的超时时间,默认60s

五、MySQL分库分表原理

5.1、为什么要分库分表

一般的机器（4核16G），单库的MySQL并发（QPS+TPS）超过了2k，系统基本就完蛋了。最好是并发量控制在1k左右。这里就引出一个问题，为什么要分库分表？

分库分表目的：解决高并发，和数据量大的问题。

1、高并发情况下，会造成IO读写频繁，自然就会造成读写缓慢，甚至是宕机。一般单库不要超过2k并发，NB的机器除外。

2、数据量大的问题。主要由于底层索引实现导致，MySQL的索引实现为B+TREE，数据量其他，会导致索引树十分庞大，造成查询缓慢。第二，innodb的最大存储限制64TB。

要解决上述问题。最常见做法，就是分库分表。
分库分表的目的，是将一个表拆成N个表，就是让每个表的数据量控制在一定范围内，保证SQL的性能。 一个表数据建议不要超过500W。

5.2、分库分表

又分为垂直拆分和水平拆分。

**水平拆分：**统一个表的数据拆到不同的库不同的表中。可以根据时间、地区、或某个业务键维度，也可以通过hash进行拆分，最后通过路由访问到具体的数据。拆分后的每个表结构保持一致。

**垂直拆分：**就是把一个有很多字段的表给拆分成多个表，或者是多个库上去。每个库表的结构都不一样，每个库表都包含部分字段。一般来说，可以根据业务维度进行拆分，如订单表可以拆分为订单、订单支持、订单地址、订单商品、订单扩展等表；也可以，根据数据冷热程度拆分，20%的热点字段拆到一个表，80%的冷字段拆到另外一个表。

5.3、不停机分库分表数据迁移

一般数据库的拆分也是有一个过程的，一开始是单表，后面慢慢拆成多表。那么我们就看下如何平滑的从MySQL单表过度到MySQL的分库分表架构。

1、利用mysql+canal做增量数据同步，利用分库分表中间件，将数据路由到对应的新表中。

2、利用分库分表中间件，全量数据导入到对应的新表中。

3、通过单表数据和分库分表数据两两比较，更新不匹配的数据到新表中。

4、数据稳定后，将单表的配置切换到分库分表配置上。

5.4、小结

垂直拆分：业务模块拆分、商品库，用户库，订单库

水平拆分：对表进行水平拆分（也就是我们说的：分表）

表进行垂直拆分：表的字段过多，字段使用的频率不一。（可以拆分两个表建立1:1关系）

六、ShardingJdbc的分库和分表

6.1、分库分表的方式

**水平拆分：**统一个表的数据拆到不同的库不同的表中。可以根据时间、地区、或某个业务键维度，也可以通过hash进行拆分，最后通过路由访问到具体的数据。拆分后的每个表结构保持一致。

**垂直拆分：**就是把一个有很多字段的表给拆分成多个表，或者是多个库上去。每个库表的结构都不一样，每个库表都包含部分字段。一般来说，可以根据业务维度进行拆分，如订单表可以拆分为订单、订单支持、订单地址、订单商品、订单扩展等表；也可以，根据数据冷热程度拆分，20%的热点字段拆到一个表，80%的冷字段拆到另外一个表。

6.2、逻辑表

逻辑表是指：水平拆分的数据库或者数据表的相同路基和数据结构表的总称。比如用户数据根据用户id%2拆分为2个表，分别是：ksd_user0和ksd_user1。他们的逻辑表名是：ksd_user。

在shardingjdbc中的定义方式如下：

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

6.3、分库分表数据节点 - actual-data-nodes

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

数据分片是最小单元。由数据源名称和数据表组成，比如：ds0.ksd_user0。

寻找规则如下：

6.4、分库分表5种分片策略

数据源分片分为两种：

• 数据源分片
• 表分片

这两个是不同维度的分片规则，但是它们额能用的分片策略和规则是一样的。它们由两部分构成：

• 分片键
• 分片算法

第一种：none

对应NoneShardingStragey,不分片策略，SQL会被发给所有节点去执行，这个规则没有子项目可以配置。

第二种：inline 行表达时分片策略(核心，必须要掌握)

对应InlineShardingStragey。使用Groovy的表达时，提供对SQL语句种的=和in的分片操作支持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开放，如：ksd_user${分片键（数据表字段）userid % 5} 表示ksd_user表根据某字段（userid）模 5.从而分为5张表，表名称为：ksd_user0到ksd_user4 。如果库也是如此。

server:
  port: 8085
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql:
        show: true
    sharding:
      # 默认数据源，主要用于写，注意一定要配置读写分离 ,注意：如果不配置，那么就会把三个节点都当做从slave节点，新增，修改和删除会出错。
      default-data-source-name: ds0
      # 配置分表的规则
      tables:
        # ksd_user 逻辑表名
        ksd_user:
          # 数据节点：数据源$->{0..N}.逻辑表名$->{0..N}
          actual-data-nodes: ds$->{0..1}.ksd_user$->{0..1}
          # 拆分库策略，也就是什么样子的数据放入放到哪个数据库中。
          database-strategy:
            inline:
              sharding-column: sex    # 分片字段（分片键）
              algorithm-expression: ds$->{sex % 2} # 分片算法表达式
          # 拆分表策略，也就是什么样子的数据放入放到哪个数据表中。
          table-strategy:
            inline:
              sharding-column: age    # 分片字段（分片键）
              algorithm-expression: ksd_user$->{age % 2} # 分片算法表达式

algorithm-expression行表达式：

• ${begin…end} 表示区间范围。
• ${[unit1,unit2,….,unitn]} 表示枚举值。
• 行表达式种如果出现连续多个$expresssion或$

完整案例和配置如下

• 准备两个数据库ksd_sharding-db。名字相同，两个数据源ds0和ds1
• 每个数据库下方ksd_user0和ksd_user1即可。
• 数据库规则，性别为偶数的放入ds0库，奇数的放入ds1库。
• 数据表规则：年龄为偶数的放入ksd_user0库，奇数的放入ksd_user1库。

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://47.115.94.78:3306/ksd-sharding-db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: mkxiaoer1986.
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds1-slave
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://114.215.145.201:3306/ksd-sharding-db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: mkxiaoer1986.
        maxPoolSize: 100
        minPoolSize: 5
    # 配置默认数据源ds0
    sharding:
      # 默认数据源，主要用于写，注意一定要配置读写分离 ,注意：如果不配置，那么就会把三个节点都当做从slave节点，新增，修改和删除会出错。
      default-data-source-name: ds0
      # 配置分表的规则
      tables:
        # ksd_user 逻辑表名
        ksd_user:
          # 数据节点：数据源$->{0..N}.逻辑表名$->{0..N}
          actual-data-nodes: ds$->{0..1}.ksd_user$->{0..1}
          # 拆分库策略，也就是什么样子的数据放入放到哪个数据库中。
          database-strategy:
            inline:
              sharding-column: sex    # 分片字段（分片键）
              algorithm-expression: ds$->{sex % 2} # 分片算法表达式
          # 拆分表策略，也就是什么样子的数据放入放到哪个数据表中。
          table-strategy:
            inline:
              sharding-column: age    # 分片字段（分片键）
              algorithm-expression: ksd_user$->{age % 2} # 分片算法表达式
# 整合mybatis的配置XXXXX
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.xuexiangban.shardingjdbc.entity

结果如下图：

第三种：根据实时间日期 - 按照标准规则分库分表

标准分片 - Standard(了解)

• 对应StrandardShardingStrategy.提供对SQL语句中的=，in和恶between and 的分片操作支持。
• StrandardShardingStrategy只支持但分片键。提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。
• PreciseShardingAlgorithm是必选的呃，用于处理=和IN的分片
• 和RangeShardingAlgorithm是可选的，是用于处理Betwwen and分片，如果不配置和RangeShardingAlgorithm,SQL的Between AND 将按照全库路由处理。

定义分片的日期规则配置

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://47.115.94.78:3306/ksd-sharding-db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: mkxiaoer1986.
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds1-slave
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://114.215.145.201:3306/ksd-sharding-db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: mkxiaoer1986.
        maxPoolSize: 100
        minPoolSize: 5
    # 配置默认数据源ds0
    sharding:
      # 默认数据源，主要用于写，注意一定要配置读写分离 ,注意：如果不配置，那么就会把三个节点都当做从slave节点，新增，修改和删除会出错。
      default-data-source-name: ds0
      # 配置分表的规则
      tables:
        # ksd_user 逻辑表名
        ksd_user:
          # 数据节点：数据源$->{0..N}.逻辑表名$->{0..N}
          actual-data-nodes: ds$->{0..1}.ksd_user$->{0..1}
          # 拆分库策略，也就是什么样子的数据放入放到哪个数据库中。
          database-strategy:
            standard:
              shardingColumn: birthday
              preciseAlgorithmClassName: com.xuexiangban.shardingjdbc.algorithm.BirthdayAlgorithm
          table-strategy:
            inline:
              sharding-column: age    # 分片字段（分片键）
              algorithm-expression: ksd_user$->{age % 2} # 分片算法表达式
# 整合mybatis的配置XXXXX
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.xuexiangban.shardingjdbc.entity

定义分片的日期规则

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 s : dateList) {
            target = iterator.next();
            // 如果数据晚于指定的日期直接返回
            if (date.before(s)) {
                break;
            }
        }
        return target;
    }
}

测试查看结果

http://localhost:8085/user/save?sex=3&age=3&birthday=2020-03-09 —- ds1
http://localhost:8085/user/save?sex=3&age=3&birthday=2021-03-09 —- ds0

第四种：ShardingSphere - 符合分片策略（了解）

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

第五种：ShardingSphere - hint分片策略（了解）

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

七、分布式主键配置

7.1、ShardingSphere - 分布式主键配置

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

• SNOWFLAKE
• UUID

这里切记：主键列不能自增长。数据类型是：bigint(20)

spring:
  shardingsphere:
    sharding:
      tables:
        # ksd_user 逻辑表名
        ksd_user:
          key-generator:
              # 主键的列明，
            column: userid
            type: SNOWFLAKE

执行

http://localhost:8085/user/save?sex=3&age=3&birthday=2020-03-09
可以查看到新增的语句多了一个userid为576906137413091329的唯一值。这个值是通过雪花算法计算出来的唯一值

2021-03-11 22:59:01.605  INFO 4900 --- [nio-8085-exec-1] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into ksd_user1 (nickname, password, sex, age, birthday, userid) VALUES (?, ?, ?, ?, ?, ?) ::: [zhangsan-70137485, 1234567, 3, 3, 2020-03-09 00:00:00.0, 576906137413091329]

八、分库分表 - 年月案例

实战完成按照年月分库分表。

策略类

public class YearMonthShardingAlgorithm implements PreciseShardingAlgorithm<String> {
    private static final String SPLITTER = "_";
    @Override
    public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {
        String tbName = shardingValue.getLogicTableName() + "_" + shardingValue.getValue();
        System.out.println("Sharding input:" + shardingValue.getValue() + ", output:{}" + tbName);
        return tbName;
    }
}

entity

@Data
public class Order {
    // 主键
    private Long orderid;
    // 订单编号
    private String ordernumber;
    // 用户ID
    private Long userid;
    // 产品id
    private Long productid;
    // 创建时间
    private Date createTime;
}

Mapper

@Mapper
@Repository
public interface UserOrderMapper {

    @Insert("insert into ksd_user_order(ordernumber,userid,create_time,yearmonth) values(#{ordernumber},#{userid},#{createTime},#{yearmonth})")
    @Options(useGeneratedKeys = true,keyColumn = "orderid",keyProperty = "orderid")
    void addUserOrder(UserOrder userOrder);
}

配置如下：

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://47.115.94.78:3306/ksd_order_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: mkxiaoer1986.
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds1-slave
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://114.215.145.201:3306/ksd_order_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: mkxiaoer
        maxPoolSize: 100
        minPoolSize: 5
    # 配置默认数据源ds0
    sharding:
      # 默认数据源，主要用于写，注意一定要配置读写分离 ,注意：如果不配置，那么就会把三个节点都当做从slave节点，新增，修改和删除会出错。
      default-data-source-name: ds0
      # 配置分表的规则
      tables:
        # ksd_user 逻辑表名
        ksd_user:
          key-generator:
            column: id
            type: SNOWFLAKE
          # 数据节点：数据源$->{0..N}.逻辑表名$->{0..N}
          actual-data-nodes: ds$->{0..1}.ksd_user$->{0..1}
          # 拆分库策略，也就是什么样子的数据放入放到哪个数据库中。
          database-strategy:
            standard:
              shardingColumn: birthday
              preciseAlgorithmClassName: com.xuexiangban.shardingjdbc.algorithm.BirthdayAlgorithm
          table-strategy:
            inline:
              sharding-column: age    # 分片字段（分片键）
              algorithm-expression: ksd_user$->{age % 2} # 分片算法表达式
        ksd_order:
          # 数据节点：数据源$->{0..N}.逻辑表名$->{0..N}
          actual-data-nodes: ds0.ksd_order$->{0..1}
          key-generator:
            column: orderid
            type: SNOWFLAKE
          # 拆分库策略，也就是什么样子的数据放入放到哪个数据库中。
          table-strategy:
            inline:
              sharding-column: orderid    # 分片字段（分片键）
              algorithm-expression: ksd_order$->{orderid % 2} # 分片算法表达式
        ksd_user_order:
          # 数据节点：数据源$->{0..N}.逻辑表名$->{0..N}
          actual-data-nodes: ds0.ksd_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: ksd_user_order_$->{yearmonth}
            standard:
              shardingColumn: yearmonth
              preciseAlgorithmClassName: com.xuexiangban.shardingjdbc.algorithm.YearMonthShardingAlgorithm
# 整合mybatis的配置XXXXX
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.xuexiangban.shardingjdbc.entity

test

@SpringBootTest
class ShardingJdbcApplicationTests {
    @Autowired
    private UserOrderService userOrderService;
    @Test
    void contextLoads() throws Exception {
        User user = new User();
        user.setNickname("zhangsan" + new Random().nextInt());
        user.setPassword("1234567");
        user.setSex(1);
        user.setAge(2);
        user.setBirthday(new Date());
        Order order = new Order();
        order.setCreateTime(new Date());
        order.setOrdernumber("133455678");
        order.setProductid(1234L);
        userOrderService.saveUserOrder(user, order);
    }
    @Autowired
    private UserOrderMapper userOrderMapper;
    @Test
    public void orderyearMaster() {
        UserOrder userOrder = new UserOrder();
        userOrder.setOrderid(10000L);
        userOrder.setCreateTime(new Date());
        userOrder.setOrdernumber("133455678");
        userOrder.setYearmonth("202103");
        userOrder.setUserid(1L);
        userOrderMapper.addUserOrder(userOrder);
    }
}

九、ShardingJdbc的事务管理

9.1、分布式式事务的应用和实践

官方地址：https://shardingsphere.apache.org/document/legacy/4.x/document/cn/features/transaction/function/base-transaction-seata/

https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/usage/transaction/

数据库事务需要满足ACID（原子性、一致性、隔离性、持久性）四个特性。

• 原子性（Atomicity）指事务作为整体来执行，要么全部执行，要么全不执行。
• 一致性（Consistency）指事务应确保数据从一个一致的状态转变为另一个一致的状态。
• 隔离性（Isolation）指多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性（Durability）指已提交的事务修改数据会被持久保存。

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

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

本地事务

在不开启任何分布式事务管理器的前提下，让每个数据节点各自管理自己的事务。 它们之间没有协调以及通信的能力，也并不互相知晓其他数据节点事务的成功与否。 本地事务在性能方面无任何损耗，但在强一致性以及最终一致性方面则力不从心。

两阶段提交

XA协议最早的分布式事务模型是由X/Open国际联盟提出的X/Open Distributed Transaction Processing（DTP）模型，简称XA协议。

基于XA协议实现的分布式事务对业务侵入很小。 它最大的优势就是对使用方透明，用户可以像使用本地事务一样使用基于XA协议的分布式事务。 XA协议能够严格保障事务ACID特性。

严格保障事务ACID特性是一把双刃剑。 事务执行在过程中需要将所需资源全部锁定，它更加适用于执行时间确定的短事务。 对于长事务来说，整个事务进行期间对数据的独占，将导致对热点数据依赖的业务系统并发性能衰退明显。 因此，在高并发的性能至上场景中，基于XA协议的分布式事务并不是最佳选择。

柔性事务

如果将实现了ACID的事务要素的事务称为刚性事务的话，那么基于BASE事务要素的事务则称为柔性事务。 BASE是基本可用、柔性状态和最终一致性这三个要素的缩写。

基本可用（Basically Available）保证分布式事务参与方不一定同时在线。
柔性状态（Soft state）则允许系统状态更新有一定的延时，这个延时对客户来说不一定能够察觉。
而最终一致性（Eventually consistent）通常是通过消息传递的方式保证系统的最终一致性。

在ACID事务中对隔离性的要求很高，在事务执行过程中，必须将所有的资源锁定。 柔性事务的理念则是通过业务逻辑将互斥锁操作从资源层面上移至业务层面。通过放宽对强一致性要求，来换取系统吞吐量的提升。

基于ACID的强一致性事务和基于BASE的最终一致性事务都不是银弹，只有在最适合的场景中才能发挥它们的最大长处。 可通过下表详细对比它们之间的区别，以帮助开发者进行技术选型。

1-1：导入分布式事务的依赖

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

1-2：事务的几种类型

本地事务

• 完全支持非跨库事务，例如：仅分表，或分库但是路由的结果在单库中。
• 完全支持因逻辑异常导致的跨库事务。例如：同一事务中，跨两个库更新。更新完毕后，抛出空指针，则两个库的内容都能回滚。
• 不支持因网络、硬件异常导致的跨库事务。例如：同一事务中，跨两个库更新，更新完毕后、未提交之前，第一个库宕机，则只有第二个库数据提交。

两阶段XA事务

• 支持数据分片后的跨库XA事务
• 两阶段提交保证操作的原子性和数据的强一致性
• 服务宕机重启后，提交/回滚中的事务可自动恢复
• SPI机制整合主流的XA事务管理器，默认Atomikos，可以选择使用Narayana和Bitronix
• 同时支持XA和非XA的连接池
• 提供spring-boot和namespace的接入端

不支持：

• 服务宕机后，在其它机器上恢复提交/回滚中的数据

Seata柔性事务

• 完全支持跨库分布式事务
• 支持RC隔离级别
• 通过undo快照进行事务回滚
• 支持服务宕机后的，自动恢复提交中的事务

依赖：

• 需要额外部署Seata-server服务进行分支事务的协调
  待优化项
• ShardingSphere和Seata会对SQL进行重复解析

1-3：导入分布式事务的依赖

@Service
public class UserOrderService {
    @Autowired
    private UserMapper userMapper;
    
    @Autowired
    private OrderMapper orderMapper;
    
    @ShardingTransactionType(TransactionType.XA)
    @Transactional(rollbackFor = Exception.class)
    public int saveUserOrder(User user, Order order) {
        userMapper.addUser(user);
        order.setUserid(user.getId());
        orderMapper.addOrder(order);
        //int a = 1/0; //测试回滚，统一提交的话，将这行注释掉就行
        return 1;
    }
}

测试

@SpringBootTest
class ShardingJdbcApplicationTests {
    @Autowired
    private UserOrderService userOrderService;
    @Test
    void contextLoads() throws Exception {
        User user = new User();
        user.setNickname("zhangsan" + new Random().nextInt());
        user.setPassword("1234567");
        user.setSex(1);
        user.setAge(2);
        user.setBirthday(new Date());
        Order order = new Order();
        order.setCreateTime(new Date());
        order.setOrdernumber("133455678");
        order.setProductid(1234L);
        userOrderService.saveUserOrder(user, order);
    }
}

十、总结

10.1、基础规范

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

10.2、列设计规范

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

10.3、索引规范

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

10.4、SQL规范

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

10.5、表的垂直拆分

垂直拆分：业务模块拆分、商品库，用户库，订单库

水平拆分：对表进行水平拆分（也就是我们说的：分表）

表进行垂直拆分：表的字段过多，字段使用的频率不一。（可以拆分两个表建立1:1关系）

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

原则：

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

10.6、如何平滑添加字段

场景：在开发时，有时需要给表加字段，在大数据量且分表的情况下，怎么样平滑添加。

1：直接alter table add column，数据量大时不建议，（会产生写锁）

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

2：提前预留字段（不优雅：造成空间浪费，预留多少很难控制，拓展性差）

3：新增一张表，（增加字段），迁移原表数据，在重新命名新表作为原表。

4：放入extinfo（无法使用索引）

5:提前设计，使用key/value方法存储，新增字段时 ，直接加一个key就好了（优雅）

十一、Springboot整合ShardingJdbc3.0和案例分析

目标

使用Sharding-JDBC 分库分表，掌握什么是Sharding-JDBC.

分析

1. 什么是Sharding-JDBC

Sharding-JDBC提供标准化的数据分片、分布式事务和数据库治理功能，定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。

基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。

支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL。

2. 为什么要分片？

• 传统的将数据集中存储至单一数据节点的解决方案，在性能、可用性和运维成本这三方面已经难于满足互联网的海量数据场景。
• 从性能方面来说，由于关系型数据库大多采用B+树类型的索引，在数据量超过阈值的情况下，索引深度的增加也将使得磁盘访问的IO次数增加，进而导致查询性能的下降；同时，高并发访问请求也使得集中式数据库成为系统的最大瓶颈。
• 从可用性的方面来讲，服务化的无状态型，能够达到较小成本的随意扩容，这必然导致系统的最终压力都落在数据库之上。而单一的数据节点，或者简单的主从架构，已经越来越难以承担。数据库的可用性，已成为整个系统的关键。
• 从运维成本方面考虑，当一个数据库实例中的数据达到阈值以上，对于DBA的运维压力就会增大。数据备份和恢复的时间成本都将随着数据量的大小而愈发不可控。一般来讲，单一数据库实例的数据的阈值在1TB之内，是比较合理的范围。
• 在传统的关系型数据库无法满足互联网场景需要的情况下，将数据存储至原生支持分布式的NoSQL的尝试越来越多。 但NoSQL对SQL的不兼容性以及生态圈的不完善，使得它们在与关系型数据库的博弈中始终无法完成致命一击，而关系型数据库的地位却依然不可撼动。
• 据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中以达到提升性能瓶颈以及可用性的效果。 数据分片的有效手段是对关系型数据库进行分库和分表。分库和分表均可以有效的避免由数据量超过可承受阈值而产生的查询瓶颈。 除此之外，分库还能够用于有效的分散对数据库单点的访问量；分表虽然无法缓解数据库压力，但却能够提供尽量将分布式事务转化为本地事务的可能，一旦涉及到跨库的更新操作，分布式事务往往会使问题变得复杂。 使用多主多从的分片方式，可以有效的避免数据单点，从而提升数据架构的可用性。
• 通过分库和分表进行数据的拆分来使得各个表的数据量保持在阈值以下，以及对流量进行疏导应对高访问量，是应对高并发和海量数据系统的有效手段。

3. 分片的方式

数据分片的拆分方式又分为垂直分片和水平分片。

垂直拆分是把不同的表拆到不同的数据库中，而水平拆分是把同一个表拆到不同的数据库中（或者是把一张表数据拆分成n多个小表）。相对于垂直拆分，水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是**按照数据行的切分**，就是将表中的某些行切分到一个数据库，而另外某些行又切分到其他的数据库中，主要有分表，分库两种模式 该方式提高了系统的稳定性跟负载能力，但是跨库join性能较差。

4. Sharding-JDBC的核心/原理

Sharding-JDBC数据分片主要流程是由SQL解析 →执行器优化 → SQL路由 →SQL改写 →SQL执行 →结果归并的流程组成。

SQL解析

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

SQL解析分为两步， 第一步为 词法解析， 词法解析的意思是就是将SQL进行拆分。

例：

select *from t_user where id = 1

词法解析：

[select] [*] [from] [t_user] [where] [id=1]

第二步语法解析,语法解析器将SQL转换为抽象语法树。

执行器优化

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

SQL路由

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

举例说明，如果按照order_id的奇数和偶数进行数据分片，一个单表查询的SQL如下：

SELECT *FROM t_order WHERE order_id IN (1, 2);

那么路由的结果应为：

SELECT* FROM t_order_0 WHERE order_id IN (1, 2);
SELECT * FROM t_order_1 WHERE order_id IN (1, 2);

SQL改写

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

 从一个最简单的例子开始，若逻辑SQL为：
 SELECT order_id FROM t_order WHERE order_id=1;
 假设该SQL配置分片键order_id，并且order_id=1的情况，将路由至分片表1。那么改写之后的SQL应该为：
 SELECT order_id FROM t_order_1 WHERE order_id=1;

SQL执行

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

结果归并

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

SharedingJdbc完成数据的读写分离

目标

使用sharedingjdbc完成数据库的分库分表业务

步骤

1：新建一个springboot工程

2：创建两个数据库order1，order2，分别创建t_address表如下：

DROP TABLE IF EXISTS `t_address`;
CREATE TABLE `t_address` (
  `id` bigint(20) NOT NULL,
  `code` varchar(64) DEFAULT NULL COMMENT '编码',
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `pid` varchar(64) NOT NULL DEFAULT '0' COMMENT '父id',
  `type` int(11) DEFAULT NULL COMMENT '1国家2省3市4县区',
  `lit` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

**3： 开始整合SpringBoot，**这种方式比较简单只要加入sharding-jdbc-spring-boot-starter依赖，在application.yml中配置数据源，分片策略即可使用，这种方式简单，方便。pom.xml

<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>3.0.0</version>
</dependency>
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>3.0.0</version>
</dependency>

appplication.yml

mybatis:
  configuration:
    mapUnderscoreToCamelCase: true
spring:
  main:
    allow-bean-definition-overriding: true
# shardingjdbc分库分表
sharding:
  jdbc:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/order1?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/order2?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
        username: root
        password: root
    config:
      sharding:
        props:
          sql.show: true
        tables:
          t_user: #t_user表【即分库，又分表】
            key-generator-column-name: id # 主键
            actual-data-nodes: ds${0..1}.t_user${0..1} #数据节点
            database-strategy: #分库策略
              inline:
                sharding-column: city_id
                algorithm-expression: ds${city_id % 2}
            table-strategy: #分表策略
              inline:
                shardingColumn: sex
                algorithm-expression: t_user${sex % 2}
          t_address: #t_address表【只分库】
            key-generator-column-name: id
            actual-data-nodes: ds${0..1}.t_address
            database-strategy:
              inline:
                shardingColumn: lit
                algorithm-expression: ds${lit % 2}

4：编写Vo

package com.itheima.springbootshardingpro.vo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class AddressVo {
    private Long id;
    private String code;
    private String name;
    private String pid;
    private Integer type;
    private Integer lit;
}

5：编写Dao

@Mapper
public interface IndexDao {
    @Options(useGeneratedKeys = true)
    @Insert("insert into t_address (code,name,pid,type,lit)values(#{code},#{name},#{pid},#{type},#{lit})")
    int insertAddress(AddressVo addressVo);
    @Select("select * from t_address order by lit")
    List<AddressVo> listAddress();
}

6： 编写controller

@RestController
public class IndexController {
    @Autowired
    private IndexDao indexDao;
    @PostMapping("/addAddress")
    public int addAddress(AddressVo addressVo){
        int row = indexDao.insertAddress(addressVo);
        return row;
    }
    @GetMapping("/listAddress")
    public PageInfo<AddressVo> listAddress(@RequestParam(required=false,defaultValue="1")Integer pageNum,
                                @RequestParam(required=false,defaultValue="5")Integer pageSize){
        PageHelper.startPage(pageNum,pageSize);
        List<AddressVo> list =  indexDao.listAddress();
        PageInfo<AddressVo> info = new PageInfo<>(list);
        return info;
    }
}

此时，启动项目，测试