ShardingSphere-JDBC

2023-10-19

ShardingSphere

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

三大组件对比

ShardingSphere-Sidecar(规划中，简单知道就行)

定位为 Kubernetes 的云原生数据库代理，以 Sidecar 的形式代理所有对数据库的访问
通过无中心、零侵入的方案提供与数据库交互的啮合层，即Database Mesh，又可称数据库网格

ShardingSphere-JDBC

它使用客户端直连数据库，以 jar 包形式提供服务
无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架
适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis,或直接使用 JDBC
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, HikariCP 等；
支持任意实现 JDBC 规范的数据库，目前支持 MySQL，PostgreSQL，Oracle，SQLServer 以及任何可使用 JDBC 访问的数据库
采用无中心化架构，与应用程序共享资源，适用于 Java 开发的高性能的轻量级 OLTP 应用

ShardingSphere-Proxy

数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持
向应用程序完全透明，可直接当做 MySQL/PostgreSQL
它可以使用任何兼容 MySQL/PostgreSQL 协议的访问客户端（如：MySQL Command Client, MySQL Workbench, Navicat 等）操作数据

数据库的优化思路

在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案
如果数据量极大，且业务持续增长快，再考虑分库分表方案

Mysql 最大连接数

Mysql连接数：连接数过多时，就会出现‘too many connections’的错误，访问量太大或者数据库设置的最大连接数太小的原因。
Mysql默认的最大连接数为100，而mysql服务允许的最大连接数为16384

分表的意义

数据库分表可以解决单表海量数据的查询性能问题

分库的意义

数据库分库可以解决单台数据库的并发访问压力问题

分库分表之后的六大问题

问题一：跨节点数据库Join关联查询
数据库切分前，多表关联查询，可以通过sql join进行实现
分库分表后，数据可能分布在不同的节点上，sql join带来的问题就比较麻烦

问题二：分库操作带来的分布式事务问题
操作内容同时分布在不同库中，不可避免会带来跨库事务问题，即分布式事务

问题三：执行的SQL排序、翻页、函数计算问题
分库后，数据分布再不同的节点上，跨节点多库进行查询时，会出现limit分页、order by排序等问题
分片字段时，更加复杂了，要在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序（也会带来更多的CPU/IO资源损耗）

问题四：数据库全局主键重复问题
常规表的id是使用自增id进行实现，分库分表后，由于表中数据同时存在不同数据库中，如果用自增id，则会出现冲突问题

问题五：容量规划,分库分表后二次扩容问题
业务发展快，初次分库分表后，满足不了数据存储，导致需要多次扩容

问题六：分库分表技术选型问题
市场分库分表中间件相对较多，框架各有各的优势与短板，应该如何选择

垂直分表

也就是“大表拆小表”，基于列字段进行拆分。

垂直分表解决的问题

避免IO时锁表的次数
分离热点字段和非热点字段
避免大字段IO导致性能下降

垂直拆分原则

将不常用的字段单独放在一张表
将text，blob等大字段拆分出来放在附表中
业务经常组合查询的列放在一张表中

案例：商品表字段太多，每个字段访问频次不一样，浪费了IO资源，需要进行优化

//拆分前
CREATE TABLE `product` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(524) DEFAULT NULL COMMENT '视频标题',
  `cover_img` varchar(524) DEFAULT NULL COMMENT '封面图',
  `price` int(11) DEFAULT NULL COMMENT '价格,分',
  `total` int(10) DEFAULT '0' COMMENT '总库存',
  `left_num` int(10) DEFAULT '0' COMMENT '剩余',
  
  `learn_base` text COMMENT '课前须知，学习基础',
  `learn_result` text COMMENT '达到水平',
  `summary` varchar(1026) DEFAULT NULL COMMENT '概述',  
  `detail` text COMMENT '视频商品详情',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


//拆分后
CREATE TABLE `product` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(524) DEFAULT NULL COMMENT '视频标题',
  `cover_img` varchar(524) DEFAULT NULL COMMENT '封面图',
  `price` int(11) DEFAULT NULL COMMENT '价格,分',
  `total` int(10) DEFAULT '0' COMMENT '总库存',
  `left_num` int(10) DEFAULT '0' COMMENT '剩余',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `product_detail` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL COMMENT '产品主键',
  `learn_base` text COMMENT '课前须知，学习基础',
  `learn_result` text COMMENT '达到水平',
  `summary` varchar(1026) DEFAULT NULL COMMENT '概述',  
  `detail` text COMMENT '视频商品详情',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

垂直分库

一般从单体项目升级改造为微服务项目，就是垂直分库。一个mysql实例中多个库不叫物理分库

垂直分库解决的问题

单机处理能力有限：避免不同库竞争同一个物理机的CPU、内存、网络IO、磁盘IO，所以在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈
业务层面的解耦合：更好解决业务层面的耦合，业务清晰，且方便管理和维护

垂直分库不能解决的问题

垂直分库分表可以提高并发，但是依然没有解决单表数据量过大的问题

案例：C端项目里面，单个数据库的CPU、内存长期处于90%+的利用率，数据库连接经常不够，需要进行优化

水平分表

针对数据量巨大的单张表，按照某种规则（RANGE,HASH取模等）切分到N个小表中，每个表的结构是一样的，数据不一样，全部表的数据合起来就是全部数据

水平分表解决的问题

查询速度慢：单表数据量过大（当一张表的数据达到几千万时，查询一次所花的时间长）
减少锁表时间：没分表前，如果是DDL(create/alter/add等)语句，当需要添加一列的时候mysql会锁表，期间所有的读写操作只能等待

水平分表不能解决的问题

这些表还是在同一个库中，所以单数据库操作还是有IO瓶颈，主要是解决单表数据量过大的问题

案例：当一张表的数据达到几千万时，查询一次所花的时间长，需要进行优化，缩短查询时间

水平分库

同个表的数据按照一定规则分到不同的数据库中，数据库在不同的服务器上，多个数据库，降低了系统的IO和CPU压力。需要多个服务器，成本更大。建议先分表，分表之后还是不行再考虑分库。

水平分库的原理

水平分库是把不同表拆到不同数据库中，它是对数据行的拆分，不影响表结构
每个库的结构都一样,但每个库的数据都不一样，没有交集，所有库的并集就是全量数据
水平分库的粒度，比水平分表更大
每个库的表库跟随库表（尽量，这样看起来更加直观）

水平分库的原则

选择合适的分片键和分片策略，和业务场景配合
避免数据热点和访问不均衡、避免二次扩容难度大

案例：高并发的项目中，水平分表后依旧在单个库上面，1个数据库资源瓶颈 CPU/内存/带宽等限制导致响应慢，需要进行优化

水平分库分表常见策略

range

自增id，根据ID范围进行分表（左闭右开）

规则案例

1~1,000,000 是 table_1
1,000,000 ~2,000,000 是 table_2
2,000,000~3,000,000 是 table_3
...更多

优点

id是自增长，可以无限增长
扩容不用迁移数据，容易理解和维护

缺点

大部分读和写都访会问新的数据库，有IO瓶颈，整体资源利用率低
数据倾斜严重，热点数据过于集中，部分节点有瓶颈

自增id，根据ID范围进行分表延伸解决方案

更多适合是水平分表，分库会导致数据倾斜严重，热点数据过于集中，部分节点有瓶颈

数字
自增id范围
时间
年、月、日范围
比如按照月份生成库或表 pay_log_2022_01、pay_log_2022_02
空间
地理位置：省份、区域（华东、华北、华南）
比如按照省份生成库或表

基于Range范围分库分表业务场景

微博发送记录、微信消息记录、日志记录，id增长/时间分区都行
水平分表为主，水平分库则容易造成资源的浪费
网站签到等活动流水数据时间分区最好
水平分表为主，水平分库则容易造成资源的浪费
大区划分（一二线城市和五六线城市活跃度不一样，如果能避免热点问题，即可选择）
saas业务水平分库（华东、华南、华北等）

Hash取模

Hash分库分表是最普遍的方案

为啥不直接取模？

如果取模的字段不是整数型要先hash，统一规则就行

案例需求

1、用户ID是整数型的，要分2库，每个库表数量4表，一共8张表

2、用户ID取模后，值是0到7的要平均分配到每张表

分表规则

库ID = userId % 库数量(2) 
表ID = userId / 库数量(2) % 表数量(4) 

db0: tb0、tb1、tb2、tb3
db1: tb0、tb1、tb2、tb3

userid	库ID = userId % 库数量(2)	表ID = userId / 库数量(2) % 表数量(4)	库名_表名
1	1	0	db1_tb0
2	0	1	db0_tb1
3	1	1	db1_tb1
4	0	2	db0_tb2
5	1	2	db1_tb2
6	0	3	db0_tb3
7	1	3	db1_tb3
8	0	0	db0_tb0
9（新循环）	1	0	db1_tb0
10	0	1	db0_tb1
11	1	1	db1_tb1
12	1	1	db1_tb1

ShardingSphere-JDBC的常见术语

数据节点Node：数据分片的最小单元，由数据源名称和数据表组成
比如：ds_0.product_order_0
真实表：在分片的数据库中真实存在的物理表
比如：product_order_0、product_order_1、product_order_2
逻辑表：水平拆分的数据库（表）的相同逻辑和数据结构表的总称
比如：逻辑表就是product_order 包含真实表：product_order_0、product_order_1、product_order_2
绑定表：指分片规则一致的主表和子表
比如：product_order表和product_order_item表，均按照order_id分片，则此两张表互为绑定表关系
绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升
广播表：指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致
适用于数据量不大且需要与海量数据的表进行关联查询的场景, 例如：字典表、配置表

分库分表和Sharding-Jdbc常见分片算法

数据库表分片（水平库、表）包含：

分片键
分片策略

分片键

用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。
out_trade_no做哈希取模，则out_trade_no是分片键
除了对单分片字段的支持，ShardingSphere也支持根据多个字段进行分片

分片策略

分片策略	分片键支持	特点	补充说明
InlineShardingStrategy（行表达式分片策略）	单分片键	提供对SQL语句中的 =和IN 的分片操作支持，以通过简单的配置使用，无需自定义分片算法，从而避免繁琐的Java代码开发	`prouduct_order_$->{user_id % 8}`表示订单表根据user_id模8，而分成8张表，表名称为prouduct_order_0到prouduct_order_7
StandardShardingStrategy（标准分片策略）	单分片键	PreciseShardingAlgorithm 精准分片是必选的，用于处理=和IN的分片，RangeShardingAlgorithm 范围分配是可选的，用于处理BETWEEN AND分片，如果不配置RangeShardingAlgorithm	如果SQL中用了BETWEEN AND语法，则将按照全库路由处理，性能下降
ComplexShardingStrategy（复合分片策略）	单、多分片键	支持【多分片键】，多分片键之间的关系复杂，由开发者自己实现，提供最大的灵活度提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持
HintShardingStrategy（Hint分片策略）	无需配置分片健	这种分片策略无需配置分片健，分片健值也不再从 SQL中解析，外部手动指定分片健或分片库，让 SQL在指定的分库、分表中执行，用于处理使用Hint行分片的场景，通过Hint而非SQL解析的方式分片的策略	Hint策略会绕过SQL解析的，对于这些比较复杂的需要分片的查询，Hint分片策略性能可能会更好
NoneShardingStrategy(不分片策略)		不分片的策略

SpringBoot2.5+MybatisPlus+Sharding-Jdbc

分库分表需求

DB:xdclass_shop_order_0
	product_order_0
	product_order_1
DB:xdclass_shop_order_1
	product_order_0
	product_order_1

SQL脚本

字符集：utf8mb4

排序规则：utf8mb4_bin

手动创建两个库：xdclass_shop_order_0、xdclass_shop_order_1

CREATE TABLE `product_order_0` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `out_trade_no` varchar(64) DEFAULT NULL COMMENT '订单唯一标识',
  `state` varchar(11) DEFAULT NULL COMMENT 'NEW 未支付订单,PAY已经支付订单,CANCEL超时取消订单',
  `create_time` datetime DEFAULT NULL COMMENT '订单生成时间',
  `pay_amount` decimal(16,2) DEFAULT NULL COMMENT '订单实际支付价格',
  `nickname` varchar(64) DEFAULT NULL COMMENT '昵称',
  `user_id` bigint DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

创建mysql容器

docker run \
    -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=123456 \
    --name mysql \
    --restart=always \
    -d mysql:8.0

导入依赖

<properties>
        <!--JDK版本，如果是jdk8则这里是 1.8-->
        <java.version>11</java.version>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
        <spring.boot.version>2.5.5</spring.boot.version>
        <mybatisplus.boot.starter.version>3.4.0</mybatisplus.boot.starter.version>
        <lombok.version>1.18.16</lombok.version>
        <sharding-jdbc.version>4.1.1</sharding-jdbc.version>
        <junit.version>4.12</junit.version>
        <druid.version>1.1.16</druid.version>
        <!--跳过单元测试-->
        <skipTests>true</skipTests>
    </properties>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${spring.boot.version}</version>
        </dependency>

        <!--<dependency>-->
        <!--<groupId>org.springframework.boot</groupId>-->
        <!--<artifactId>spring-boot-test</artifactId>-->
        <!--</dependency>-->

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${spring.boot.version}</version>
            <scope>test</scope>
        </dependency>


        <!--mybatis plus和springboot整合-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisplus.boot.starter.version}</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <!--<scope>provided</scope>-->
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-jdbc.version}</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
        </dependency>
    </dependencies>

src/main/java/net/xdclass/DemoApplication.java

package net.xdclass;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@MapperScan("net.xdclass.mapper")
@EnableTransactionManagement
@SpringBootApplication
public class DemoApplication {

    public static void main(String [] args){

        SpringApplication.run(DemoApplication.class,args);
    }
}

application.properties

单库下分库分表

自增ID导致主键重复

server.port=8080
spring.application.name=xdclass-jdbc

logging.level.root=INFO
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true

# 数据源 db0,ds1,ds2
spring.shardingsphere.datasource.names=ds0
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.10.21:3306/shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=xdclass.net168


# 指定product_order表的数据分布情况，配置数据节点,行表达式标识符使用 ${...} 或 $->{...}，但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds0.product_order_$->{0..1}
# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{user_id % 2}

SQL路由

product_order_0 和 product_order_0 主键存在冲突

Snowflake算法生成id主键

application.properties

# 配置本节点的workId (分布式ID生成器不同节点需要配置不同的workId)
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1

方式一：model类配置

订单id使用MybatisPlus的配置,ProductOrder类配置

// 默认实现类为DefaultIdentifierGenerator雪花算法
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private Long id;

方式二：application.properties中添加配置

订单id使用MybatisPlus的配置,ProductOrder类配置

#id生成策略
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

主键id生成策略

方案一：设置不同的自增步长

利用自增id, 设置不同的自增步长，auto_increment_offset、auto-increment-increment

DB1: 单数
//从1开始、每次加2

DB2: 偶数
//从2开始，每次加2

缺点

依靠数据库系统的功能实现，但是未来扩容麻烦
主从切换时的不一致可能会导致重复发号
性能瓶颈存在单台sql上

方案二：UUID

性能非常高，没有网络消耗

缺点

无序的字符串，不具备趋势自增特性
UUID太长，不易于存储，浪费存储空间，很多场景不适用

方案三：Redis发号器

利用Redis的INCR和INCRBY来实现，原子操作，线程安全，性能比Mysql强劲

缺点

需要占用网络资源，增加系统复杂度

方案四：Snowflake雪花算法

twitter 开源的分布式 ID 生成算法，代码实现简单、不占用宽带、数据迁移不受影响，生成的 id 中包含有时间戳，所以生成的 id 按照时间递增，部署了多台服务器，需要保证系统时间一样，机器编号不一样

缺点

依赖系统时钟（多台服务器时间一定要一样）

雪花算法Snowflake

twitter用scala语言编写的高效生成唯一ID的算法

雪花算法生成的数字，long类，所以就是8个byte，64bit

表示的值 -9223372036854775808（-2的63次方） ~ 9223372036854775807（2的63次方-1）
生成的唯一值用于数据库主键，不能是负数，所以值为0~9223372036854775807（2的63次方-1）

优点

生成的ID不重复
算法性能高
基于时间戳，基本保证有序递增

分布式ID生成器Snowflake的限制条件

不同节点的workId不能重复
分布式部署就需要分配不同的workId, 如果workId相同，可能会导致生成的id相同
分布式情况下，需要保证各个系统时间一致
如果服务器的时钟回拨，就会导致生成的 id 重复

Model层（相等于逻辑表）

net/xdclass/model/ProductOrderDO.java

package net.xdclass.model;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;
import java.util.Date;


@Data
@TableName("product_order")
@EqualsAndHashCode(callSuper = false)
public class ProductOrderDO {

    @TableId(value = "id",type = IdType.AUTO)
    private Long id;

    private String outTradeNo;

    private String state;

    private Date createTime;

    private Double payAmount;

    private String nickname;

    private Long userId;

}

Mapper层

net/xdclass/mapper/ProductOrderMapper.java

package net.xdclass.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import net.xdclass.model.ProductOrderDO;

public interface ProductOrderMapper extends BaseMapper<ProductOrderDO> {
}

测试类

src/test/java/net/xdclass/db/DBTest.java

package net.xdclass.db;

import lombok.extern.slf4j.Slf4j;
import net.xdclass.DemoApplication;
import net.xdclass.mapper.ProductOrderMapper;
import net.xdclass.model.ProductOrderDO;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.Date;
import java.util.UUID;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
@Slf4j
public class DBTest {
    @Autowired
    private ProductOrderMapper productOrderMapper;

    @Test
    public void testSaveProductOrder(){
        for (int i = 0; i < 10; i++) {
            ProductOrderDO productOrderDO = new ProductOrderDO();
            productOrderDO.setCreateTime(new Date());
            productOrderDO.setNickname("soulboyi=" + i);
            productOrderDO.setOutTradeNo(UUID.randomUUID().toString().substring(0, 32));
            productOrderDO.setPayAmount(100.00);
            productOrderDO.setState("PAY");
            productOrderDO.setUserId(Long.valueOf(i+""));
            productOrderMapper.insert(productOrderDO);
        }
    }
}

广播表

指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致，适用于数据量不大且需要与海量数据的表进行关联查询的场景, 例如：字典表、配置表。

注意点：

分库分表中间件，对应的数据库字段，不能是sql的关键字，否则容易出问题，且报错不明显

sql(两个库中分别建立)

CREATE TABLE `ad_config` (
  `id` bigint unsigned NOT NULL COMMENT '主键id',
  `config_key` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置key',
  `config_value` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置value',
  `type` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

POJO类

package net.xdclass.model;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("ad_config")
public class AdConfigDO {
    private Long id;
    private String configKey;
    private String configValue;
    private String type;
}

mapper类

package net.xdclass.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import net.xdclass.model.AdConfigDO;

public interface AdConfigMapper extends BaseMapper<AdConfigDO> {
}

application.properties 配置文件

server.port=8080
spring.application.name=xdclass-jdbc

logging.level.root=INFO
# 打印sql语句
spring.shardingsphere.props.sql.show=true

# 数据源 db0,ds1,ds2
spring.shardingsphere.datasource.names=ds0,ds1
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.10.21:3307/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=abc1024.pub

# 第二个数据库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.10.21:3307/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=abc1024.pub
# workId
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1

### 配置广播表!!!
spring.shardingsphere.sharding.broadcast-tables=ad_config
spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE

# product_order的id字段使用snowflake
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

# 指定product_order表的数据分布情况，配置数据节点,行表达式标识符使用 ${...} 或 $->{...}，但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds0.product_order_$->{0..1}
# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{user_id % 2}

测试

    @Test
    public void testSaveAdOrder(){
            AdConfigDO adConfigDO = new AdConfigDO();
            adConfigDO.setConfigKey("banner");
            adConfigDO.setConfigValue("soulboy.com");
            adConfigDO.setType("ad");
            adConfigMapper.insert(adConfigDO);

    }

多库水平分库分表

2个数据库、每个库2张表

分库规则：根据 user_id 进行分库
分表规则：根据 product_order_id 订单号进行分表 （雪花算法）

数据库和表的下标如果不想从0开始，则hash取模后+1

{user_id % 2+1}

application.properties 配置文件

server.port=8080
spring.application.name=xdclass-jdbc

logging.level.root=INFO
# 打印sql语句
spring.shardingsphere.props.sql.show=true

# 数据源 db0,ds1,ds2
spring.shardingsphere.datasource.names=ds0,ds1
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.10.21:3307/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=abc1024.pub

# 第二个数据库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.10.21:3307/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=abc1024.pub
# workId
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1

# 配置广播表
spring.shardingsphere.sharding.broadcast-tables=ad_config
spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE

#配置分库规则
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}

# product_order的id字段使用snowflake
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

# 指定product_order 配置数据节点,行表达式标识符使用 ${...} 或 $->{...}，但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}
# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id % 2}

测试

    @Test
    public void testSaveProductOrder(){
        Random random = new Random();
        for (int i = 0; i < 20; i++) {
            ProductOrderDO productOrderDO = new ProductOrderDO();
            productOrderDO.setCreateTime(new Date());
            productOrderDO.setNickname("soulboyi=" + i);
            productOrderDO.setOutTradeNo(UUID.randomUUID().toString().substring(0, 32));
            productOrderDO.setPayAmount(100.00);
            productOrderDO.setState("PAY");
            productOrderDO.setUserId(Long.valueOf(random.nextInt(50)));
            productOrderMapper.insert(productOrderDO);
        }
    }

绑定表

指分片规则一致的主表和子表

比如product_order表和product_order_item表，均按照order_id分片，则此两张表互为绑定表关系

绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升

application.properties 配置文件

server.port=8080
spring.application.name=xdclass-jdbc

logging.level.root=INFO
# 打印sql语句
spring.shardingsphere.props.sql.show=true

# 数据源 db0,ds1,ds2
spring.shardingsphere.datasource.names=ds0,ds1
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.10.21:3307/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=abc1024.pub

# 第二个数据库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.10.21:3307/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=abc1024.pub

# workId
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1

# 配置广播表
spring.shardingsphere.sharding.broadcast-tables=ad_config
spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE

# 默认分库策略 (针对所有表的分库策略)
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{user_id % 2}

#配置分库规则
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}

# product_order的id字段使用snowflake
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

# 配置数据节点 product_order ,行表达式标识符使用 ${...} 或 $->{...}，但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}

# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id % 2}

# 指定product_order_item表的数据分布情况
spring.shardingsphere.sharding.tables.product_order_item.table-strategy.inline.sharding-column=product_order_id
spring.shardingsphere.sharding.tables.product_order_item.table-strategy.inline.algorithm-expression=product_order_item_$->{product_order_id % 2}

# 绑定表(在没有设置绑定表之前，会进行笛卡尔积查询)
spring.shardingsphere.sharding.binding‐tables[0] = product_order,product_order_item

sql (每个库中建立两张表：product_order_item_0、product_order_item_1)

CREATE TABLE `product_order_item_0` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `product_order_id` bigint DEFAULT NULL COMMENT '订单号',
  `product_id` bigint DEFAULT NULL COMMENT '产品id',
  `product_name` varchar(128) DEFAULT NULL COMMENT '商品名称',
  `buy_num` int DEFAULT NULL COMMENT '购买数量',
  `user_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

model

package net.xdclass.model;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@TableName("product_order_item")
@EqualsAndHashCode(callSuper = false)
public class ProductOrderItemDO {
    private Long id;
    //分表字段
    private Long productOrderId;
    private Long productId;
    private String productName;
    private Integer buyNum;
    //分库字段
    private Long userId;
}

mapper

ProductOrderItemMapper

package net.xdclass.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import net.xdclass.model.ProductOrderItemDO;

public interface ProductOrderItemMapper extends BaseMapper<ProductOrderItemDO> {
  
}

ProductOrderMapper

package net.xdclass.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import net.sf.jsqlparser.expression.operators.relational.OldOracleJoinBinaryExpression;
import net.xdclass.model.ProductOrderDO;
import org.apache.ibatis.annotations.Select;

import java.util.List;


public interface ProductOrderMapper extends BaseMapper<ProductOrderDO> {
    @Select("select * from product_order o left join product_order_item i on  o.id=i.product_order_id")
    List<Object> listProductOrderDetail();
}

测试

    @Test
    public void testBangDing(){
        List<Object> list = productOrderMapper.listProductOrderDetail();
        System.out.println(list);
    }

发生笛卡尔积查询(配置绑定表之前)

没有发生笛卡尔积查询(配置绑定表之后)

查询和删除操作性能分析

查询操作

有分片键（标准路由）=、in
无分片键（全库表路由）=、in

删除操作

有分片键（标准路由）=、in
无分片键（全库表路由）=、in

ShardingSphere-JDBC 执行流程

ShardingSphere-JDBC 执行流程

长：SQL解析 -> SQL优化 -> SQL路由 -> SQL改写 -> SQL执行 -> 结果归并 ->返回结果
短：解析->路由->改写->执行->结果归并

跨节点数据库Join关联和多维度查询

问题：跨节点数据库Join关联查询和多维度查询

数据库切分前，多表关联查询，可以通过sql join进行实现
分库分表后，数据可能分布在不同的节点上，sql join带来的问题就比较麻烦
不同维度查看数据，利用的partitionKey是不一样的

解决方案

冗余字段
广播表
NOSQL汇总

案例一：订单需要用户的基本信息，但是分布在不同库上

进行字段冗余，订单表冗余用户昵称、头像

案例二：订单表的partionKey是user_id，用户查看自己的订单列表方便，但商家查看自己店铺的订单列表就麻烦（商家不是根据user_id进行分库分表，会触发全库表路由），分布在不同数据节点。

订单冗余存储在es上一份

常见分布式事务解决方案

Seata

支持 AT、TCC、SAGA 和 XA 多种模式

RocketMq：自带事务消息解决分布式事务

分库分表后二次扩容问题

Range范围

时间：不用考虑扩容迁移
区域：调整分片粒度，需要全量迁移

Hash取模(成倍扩容策略)

业务最多的是hash取模分片，因扩分库分表涉及到rehash过程，分片数量建议可以成倍扩容策略，只需要【迁移部分数据】即可。

记录	扩容前(product_id % 2)	扩容后(product_id % 4)
3	1	3
7	1	3

更多解决方式

利用一致性Hash思想，增加虚拟节点，减少迁移数据量
专门的数据库表，记录数据存储位置，进行路由
...

二次扩容实施方案具体流程

方案一

新增两个数据库 A2、A3 作为从库，设置主从同步关系为：A0=>A2、A1=>A3,
开启主从数据同步，早期数据手工同步过去
发布新程序，某个时间点开始，利用MQ存储CUD操作
关闭数据库实例的主从同步关系
校验数据，消费原先MQ存储CUD操作,配置新分片规则和生效
数据校验和修复
- 依赖gmt_modified字段，所以常规数据表都需要加这个字段
- 由数据库自己维护值，根据业务场景，进行修复对应的数据
- 校验步骤
  - 开始迁移时间假如是2022-01-01 00:00:00
  - 查找 gmt_modified数据校验修复大于开始时间点，就是修改过的数据
各个节点的冗余数据进行删除

缺点
- 同步的很多数据到最后都需要被删除
- 一定要提前做，越晚做成本越高，因为扩容期间需要存储的数据更多
- 基本都离不开代码侵入，加锁等操作
优点
- 利用mysql自带的主从同步能力
- 方案简单，代码量相对少

方案二

对外发布公告，停机迁移
严格一致性要求：比如证券、银行部分数据等

优点

最方便、且安全

缺点

会造成服务不可用，影响业务
根据停机的时间段，数据校验人员有压力

作者：Soulboy