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 等）操作数据

数据库的优化思路

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

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资源，需要进行优化

 1//拆分前
 2CREATE TABLE `product` (
 3  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 4  `title` varchar(524) DEFAULT NULL COMMENT '视频标题',
 5  `cover_img` varchar(524) DEFAULT NULL COMMENT '封面图',
 6  `price` int(11) DEFAULT NULL COMMENT '价格,分',
 7  `total` int(10) DEFAULT '0' COMMENT '总库存',
 8  `left_num` int(10) DEFAULT '0' COMMENT '剩余',
 9  
10  `learn_base` text COMMENT '课前须知，学习基础',
11  `learn_result` text COMMENT '达到水平',
12  `summary` varchar(1026) DEFAULT NULL COMMENT '概述',  
13  `detail` text COMMENT '视频商品详情',
14  PRIMARY KEY (`id`)
15) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
16
17
18//拆分后
19CREATE TABLE `product` (
20  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
21  `title` varchar(524) DEFAULT NULL COMMENT '视频标题',
22  `cover_img` varchar(524) DEFAULT NULL COMMENT '封面图',
23  `price` int(11) DEFAULT NULL COMMENT '价格,分',
24  `total` int(10) DEFAULT '0' COMMENT '总库存',
25  `left_num` int(10) DEFAULT '0' COMMENT '剩余',
26  PRIMARY KEY (`id`)
27) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
28
29CREATE TABLE `product_detail` (
30  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
31  `product_id` int(11) DEFAULT NULL COMMENT '产品主键',
32  `learn_base` text COMMENT '课前须知，学习基础',
33  `learn_result` text COMMENT '达到水平',
34  `summary` varchar(1026) DEFAULT NULL COMMENT '概述',  
35  `detail` text COMMENT '视频商品详情',
36  PRIMARY KEY (`id`)
37) 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的要平均分配到每张表

分表规则

1库ID = userId % 库数量(2) 
2表ID = userId / 库数量(2) % 表数量(4) 
3
4db0: tb0、tb1、tb2、tb3
5db1: tb0、tb1、tb2、tb3

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也支持根据多个字段进行分片

分片策略

SpringBoot2.5+MybatisPlus+Sharding-Jdbc

分库分表需求

1DB:xdclass_shop_order_0
2	product_order_0
3	product_order_1
4DB:xdclass_shop_order_1
5	product_order_0
6	product_order_1

SQL 脚本

字符集：utf8mb4

排序规则：utf8mb4_bin

手动创建两个库：xdclass_shop_order_0、xdclass_shop_order_1

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

创建 MySQL 容器

1docker run \
2    -p 3306:3306 \
3    -e MYSQL_ROOT_PASSWORD=123456 \
4    --name mysql \
5    --restart=always \
6    -d mysql:8.0

导入依赖

 1<properties>
 2        <!--JDK版本，如果是jdk8则这里是 1.8-->
 3        <java.version>11</java.version>
 4        <maven.compiler.source>11</maven.compiler.source>
 5        <maven.compiler.target>11</maven.compiler.target>
 6        <spring.boot.version>2.5.5</spring.boot.version>
 7        <mybatisplus.boot.starter.version>3.4.0</mybatisplus.boot.starter.version>
 8        <lombok.version>1.18.16</lombok.version>
 9        <sharding-jdbc.version>4.1.1</sharding-jdbc.version>
10        <junit.version>4.12</junit.version>
11        <druid.version>1.1.16</druid.version>
12        <!--跳过单元测试-->
13        <skipTests>true</skipTests>
14    </properties>
15
16    <dependencies>
17
18        <dependency>
19            <groupId>org.springframework.boot</groupId>
20            <artifactId>spring-boot-starter-web</artifactId>
21            <version>${spring.boot.version}</version>
22        </dependency>
23
24        <!--<dependency>-->
25        <!--<groupId>org.springframework.boot</groupId>-->
26        <!--<artifactId>spring-boot-test</artifactId>-->
27        <!--</dependency>-->
28
29        <dependency>
30            <groupId>org.springframework.boot</groupId>
31            <artifactId>spring-boot-starter-test</artifactId>
32            <version>${spring.boot.version}</version>
33            <scope>test</scope>
34        </dependency>
35
36
37        <!--mybatis plus和springboot整合-->
38        <dependency>
39            <groupId>com.baomidou</groupId>
40            <artifactId>mybatis-plus-boot-starter</artifactId>
41            <version>${mybatisplus.boot.starter.version}</version>
42        </dependency>
43
44        <dependency>
45            <groupId>mysql</groupId>
46            <artifactId>mysql-connector-java</artifactId>
47            <version>8.0.27</version>
48        </dependency>
49
50        <dependency>
51            <groupId>org.projectlombok</groupId>
52            <artifactId>lombok</artifactId>
53            <version>${lombok.version}</version>
54            <!--<scope>provided</scope>-->
55        </dependency>
56
57        <dependency>
58            <groupId>org.apache.shardingsphere</groupId>
59            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
60            <version>${sharding-jdbc.version}</version>
61        </dependency>
62
63        <dependency>
64            <groupId>junit</groupId>
65            <artifactId>junit</artifactId>
66            <version>${junit.version}</version>
67        </dependency>
68    </dependencies>

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

 1package net.xdclass;
 2
 3import org.mybatis.spring.annotation.MapperScan;
 4import org.springframework.boot.SpringApplication;
 5import org.springframework.boot.autoconfigure.SpringBootApplication;
 6import org.springframework.transaction.annotation.EnableTransactionManagement;
 7
 8@MapperScan("net.xdclass.mapper")
 9@EnableTransactionManagement
10@SpringBootApplication
11public class DemoApplication {
12
13    public static void main(String [] args){
14
15        SpringApplication.run(DemoApplication.class,args);
16    }
17}

application.properties

单库下分库分表

 自增 ID 导致主键重复

 1server.port=8080
 2spring.application.name=xdclass-jdbc
 3
 4logging.level.root=INFO
 5# 打印执行的数据库以及语句
 6spring.shardingsphere.props.sql.show=true
 7
 8# 数据源 db0,ds1,ds2
 9spring.shardingsphere.datasource.names=ds0
10# 第一个数据库
11spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
12spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
13spring.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
14spring.shardingsphere.datasource.ds0.username=root
15spring.shardingsphere.datasource.ds0.password=xdclass.net168
16
17
18# 指定product_order表的数据分布情况，配置数据节点,行表达式标识符使用 ${...} 或 $->{...}，但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
19spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds0.product_order_$->{0..1}
20# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
21spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=user_id
22spring.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

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

方式一：model 类配置

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

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

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

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

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

主键 id 生成策略

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

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

1DB1: 单数
2//从1开始、每次加2
3
4DB2: 偶数
5//从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

 1package net.xdclass.model;
 2
 3import com.baomidou.mybatisplus.annotation.IdType;
 4import com.baomidou.mybatisplus.annotation.TableId;
 5import com.baomidou.mybatisplus.annotation.TableName;
 6import lombok.Data;
 7import lombok.EqualsAndHashCode;
 8import java.util.Date;
 9
10
11@Data
12@TableName("product_order")
13@EqualsAndHashCode(callSuper = false)
14public class ProductOrderDO {
15
16    @TableId(value = "id",type = IdType.AUTO)
17    private Long id;
18
19    private String outTradeNo;
20
21    private String state;
22
23    private Date createTime;
24
25    private Double payAmount;
26
27    private String nickname;
28
29    private Long userId;
30
31}

Mapper 层

net/xdclass/mapper/ProductOrderMapper.java

1package net.xdclass.mapper;
2
3import com.baomidou.mybatisplus.core.mapper.BaseMapper;
4import net.xdclass.model.ProductOrderDO;
5
6public interface ProductOrderMapper extends BaseMapper<ProductOrderDO> {
7}

测试类

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

 1package net.xdclass.db;
 2
 3import lombok.extern.slf4j.Slf4j;
 4import net.xdclass.DemoApplication;
 5import net.xdclass.mapper.ProductOrderMapper;
 6import net.xdclass.model.ProductOrderDO;
 7import org.junit.Test;
 8import org.junit.runner.RunWith;
 9import org.springframework.beans.factory.annotation.Autowired;
10import org.springframework.boot.test.context.SpringBootTest;
11import org.springframework.test.context.junit4.SpringRunner;
12import java.util.Date;
13import java.util.UUID;
14
15@RunWith(SpringRunner.class)
16@SpringBootTest(classes = DemoApplication.class)
17@Slf4j
18public class DBTest {
19    @Autowired
20    private ProductOrderMapper productOrderMapper;
21
22    @Test
23    public void testSaveProductOrder(){
24        for (int i = 0; i < 10; i++) {
25            ProductOrderDO productOrderDO = new ProductOrderDO();
26            productOrderDO.setCreateTime(new Date());
27            productOrderDO.setNickname("soulboyi=" + i);
28            productOrderDO.setOutTradeNo(UUID.randomUUID().toString().substring(0, 32));
29            productOrderDO.setPayAmount(100.00);
30            productOrderDO.setState("PAY");
31            productOrderDO.setUserId(Long.valueOf(i+""));
32            productOrderMapper.insert(productOrderDO);
33        }
34    }
35}

广播表

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

注意点：

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

sql(两个库中分别建立)

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

POJO 类

 1package net.xdclass.model;
 2
 3import com.baomidou.mybatisplus.annotation.TableName;
 4import lombok.Data;
 5import lombok.EqualsAndHashCode;
 6
 7@Data
 8@EqualsAndHashCode(callSuper = false)
 9@TableName("ad_config")
10public class AdConfigDO {
11    private Long id;
12    private String configKey;
13    private String configValue;
14    private String type;
15}
16

mapper 类

1package net.xdclass.mapper;
2
3import com.baomidou.mybatisplus.core.mapper.BaseMapper;
4import net.xdclass.model.AdConfigDO;
5
6public interface AdConfigMapper extends BaseMapper<AdConfigDO> {
7}
8

application.properties 配置文件

 1server.port=8080
 2spring.application.name=xdclass-jdbc
 3
 4logging.level.root=INFO
 5# 打印sql语句
 6spring.shardingsphere.props.sql.show=true
 7
 8# 数据源 db0,ds1,ds2
 9spring.shardingsphere.datasource.names=ds0,ds1
10# 第一个数据库
11spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
12spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
13spring.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
14spring.shardingsphere.datasource.ds0.username=root
15spring.shardingsphere.datasource.ds0.password=abc1024.pub
16
17# 第二个数据库
18spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
19spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
20spring.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
21spring.shardingsphere.datasource.ds1.username=root
22spring.shardingsphere.datasource.ds1.password=abc1024.pub
23# workId
24spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1
25
26### 配置广播表!!!
27spring.shardingsphere.sharding.broadcast-tables=ad_config
28spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
29spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE
30
31# product_order的id字段使用snowflake
32spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
33spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE
34
35# 指定product_order表的数据分布情况，配置数据节点,行表达式标识符使用 ${...} 或 $->{...}，但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
36spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds0.product_order_$->{0..1}
37# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
38spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=user_id
39spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{user_id % 2}

测试

1    @Test
2    public void testSaveAdOrder(){
3            AdConfigDO adConfigDO = new AdConfigDO();
4            adConfigDO.setConfigKey("banner");
5            adConfigDO.setConfigValue("soulboy.com");
6            adConfigDO.setType("ad");
7            adConfigMapper.insert(adConfigDO);
8
9    }

多库水平分库分表

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

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

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

• {user_id % 2+1}

application.properties 配置文件

 1server.port=8080
 2spring.application.name=xdclass-jdbc
 3
 4logging.level.root=INFO
 5# 打印sql语句
 6spring.shardingsphere.props.sql.show=true
 7
 8# 数据源 db0,ds1,ds2
 9spring.shardingsphere.datasource.names=ds0,ds1
10# 第一个数据库
11spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
12spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
13spring.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
14spring.shardingsphere.datasource.ds0.username=root
15spring.shardingsphere.datasource.ds0.password=abc1024.pub
16
17# 第二个数据库
18spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
19spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
20spring.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
21spring.shardingsphere.datasource.ds1.username=root
22spring.shardingsphere.datasource.ds1.password=abc1024.pub
23# workId
24spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1
25
26# 配置广播表
27spring.shardingsphere.sharding.broadcast-tables=ad_config
28spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
29spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE
30
31#配置分库规则
32spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.sharding-column=user_id
33spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
34
35# product_order的id字段使用snowflake
36spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
37spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE
38
39# 指定product_order 配置数据节点,行表达式标识符使用 ${...} 或 $->{...}，但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
40spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}
41# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
42spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=id
43spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id % 2}

测试

 1    @Test
 2    public void testSaveProductOrder(){
 3        Random random = new Random();
 4        for (int i = 0; i < 20; i++) {
 5            ProductOrderDO productOrderDO = new ProductOrderDO();
 6            productOrderDO.setCreateTime(new Date());
 7            productOrderDO.setNickname("soulboyi=" + i);
 8            productOrderDO.setOutTradeNo(UUID.randomUUID().toString().substring(0, 32));
 9            productOrderDO.setPayAmount(100.00);
10            productOrderDO.setState("PAY");
11            productOrderDO.setUserId(Long.valueOf(random.nextInt(50)));
12            productOrderMapper.insert(productOrderDO);
13        }
14    }

绑定表

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

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

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

application.properties 配置文件

 1server.port=8080
 2spring.application.name=xdclass-jdbc
 3
 4logging.level.root=INFO
 5# 打印sql语句
 6spring.shardingsphere.props.sql.show=true
 7
 8# 数据源 db0,ds1,ds2
 9spring.shardingsphere.datasource.names=ds0,ds1
10# 第一个数据库
11spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
12spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
13spring.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
14spring.shardingsphere.datasource.ds0.username=root
15spring.shardingsphere.datasource.ds0.password=abc1024.pub
16
17# 第二个数据库
18spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
19spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
20spring.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
21spring.shardingsphere.datasource.ds1.username=root
22spring.shardingsphere.datasource.ds1.password=abc1024.pub
23
24# workId
25spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1
26
27# 配置广播表
28spring.shardingsphere.sharding.broadcast-tables=ad_config
29spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
30spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE
31
32# 默认分库策略 (针对所有表的分库策略)
33spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = user_id
34spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{user_id % 2}
35
36#配置分库规则
37spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.sharding-column=user_id
38spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
39
40# product_order的id字段使用snowflake
41spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
42spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE
43
44# 配置数据节点 product_order ,行表达式标识符使用 ${...} 或 $->{...}，但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
45spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}
46
47# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
48spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=id
49spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id % 2}
50
51# 指定product_order_item表的数据分布情况
52spring.shardingsphere.sharding.tables.product_order_item.table-strategy.inline.sharding-column=product_order_id
53spring.shardingsphere.sharding.tables.product_order_item.table-strategy.inline.algorithm-expression=product_order_item_$->{product_order_id % 2}
54
55# 绑定表(在没有设置绑定表之前，会进行笛卡尔积查询)
56spring.shardingsphere.sharding.binding‐tables[0] = product_order,product_order_item

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

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

model

 1package net.xdclass.model;
 2
 3import com.baomidou.mybatisplus.annotation.TableName;
 4import lombok.Data;
 5import lombok.EqualsAndHashCode;
 6
 7@Data
 8@TableName("product_order_item")
 9@EqualsAndHashCode(callSuper = false)
10public class ProductOrderItemDO {
11    private Long id;
12    //分表字段
13    private Long productOrderId;
14    private Long productId;
15    private String productName;
16    private Integer buyNum;
17    //分库字段
18    private Long userId;
19}
20

mapper

ProductOrderItemMapper

1package net.xdclass.mapper;
2
3import com.baomidou.mybatisplus.core.mapper.BaseMapper;
4import net.xdclass.model.ProductOrderItemDO;
5
6public interface ProductOrderItemMapper extends BaseMapper<ProductOrderItemDO> {
7  
8}
9

ProductOrderMapper

 1package net.xdclass.mapper;
 2
 3import com.baomidou.mybatisplus.core.mapper.BaseMapper;
 4import net.sf.jsqlparser.expression.operators.relational.OldOracleJoinBinaryExpression;
 5import net.xdclass.model.ProductOrderDO;
 6import org.apache.ibatis.annotations.Select;
 7
 8import java.util.List;
 9
10
11public interface ProductOrderMapper extends BaseMapper<ProductOrderDO> {
12    @Select("select * from product_order o left join product_order_item i on  o.id=i.product_order_id")
13    List<Object> listProductOrderDetail();
14}
15

测试

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

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

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

查询和删除操作性能分析

查询操作

• 有分片键（标准路由）=、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 多种模式

MQ

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

分库分表后二次扩容问题

Range 范围

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

Hash 取模(成倍扩容策略)

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

更多解决方式

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

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

方案一

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

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

方案二

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

优点

• 最方便、且安全

缺点

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