JDBC-水平分片

水平分片的配置是最为复杂的，还包含了多表关联、绑定表与广播表的知识。

1. 准备服务器

服务器规划：使用docker方式创建如下容器

1.1 创建server-order0容器

docker run -d \
-p 3310:3306 \
-v /xk857/server/order0/conf:/etc/mysql/conf.d \
-v /xk857/server/order0/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-order0 \
mysql:8.0.29

创建数据库，需要注意的是，水平分片的id需要在业务层实现，不能依赖数据库的主键自增

CREATE DATABASE db_order;
USE db_order;
CREATE TABLE t_order0 (
    id BIGINT,
    order_no VARCHAR(30),
    user_id BIGINT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id) 
);
CREATE TABLE t_order1 (
    id BIGINT,
    order_no VARCHAR(30),
    user_id BIGINT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id) 
);

1.2 创建server-order1容器

docker run -d \
-p 3311:3306 \
-v /xk857/server/order1/conf:/etc/mysql/conf.d \
-v /xk857/server/order1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-order1 \
mysql:8.0.29

创建数据库，和server-order0相同

CREATE DATABASE db_order;
USE db_order;
CREATE TABLE t_order0 (
    id BIGINT,
    order_no VARCHAR(30),
    user_id BIGINT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id) 
);
CREATE TABLE t_order1 (
    id BIGINT,
    order_no VARCHAR(30),
    user_id BIGINT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id) 
);

2. 基本水平分片

2.1 基本配置

# 应用名称
spring.application.name=distributed-sharding
# 开发环境设置
spring.profiles.active=dev
# 内存模式
spring.shardingsphere.mode.type=Memory
# 打印SQl
spring.shardingsphere.props.sql-show=true

2.2 数据源配置

# 配置真实数据源
spring.shardingsphere.datasource.names=server-user,server-order0,server-order1

# 配置第 1 个数据源
spring.shardingsphere.datasource.server-user.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-user.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-user.jdbc-url=jdbc:mysql://192.168.31.115:3301/db_user?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true
spring.shardingsphere.datasource.server-user.username=root
spring.shardingsphere.datasource.server-user.password=123456
# 配置第 2 个数据源
spring.shardingsphere.datasource.server-order0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-order0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-order0.jdbc-url=jdbc:mysql://192.168.31.115:3310/db_order?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true
spring.shardingsphere.datasource.server-order0.username=root
spring.shardingsphere.datasource.server-order0.password=123456
# 配置第 3 个数据源
spring.shardingsphere.datasource.server-order1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-order1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-order1.jdbc-url=jdbc:mysql://192.168.31.115:3311/db_order?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true
spring.shardingsphere.datasource.server-order1.username=root
spring.shardingsphere.datasource.server-order1.password=123456

2.3 标椎分片表配置

spring.shardingsphere.rules.sharding.tables.t_user.actual-data-nodes=server-user.t_user
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=server-order0.t_order0,server-order0.t_order1,server-order1.t_order0,server-order1.t_order1

修改Order实体类的主键策略：

//@TableId(type = IdType.AUTO)//依赖数据库的主键自增策略
@TableId(type = IdType.ASSIGN_ID)//分布式id

2.4 行表达式

在繁琐的数据分片规则配置中，随着数据节点的增多，大量的重复配置使得配置本身不易被维护，通过行表达式可以有效地简化数据节点配置工作量。行表达式官方文档 (opens new window)

例如，以下行表达式：$->{['online', 'offline']}_table${1..3}最终会解析为：

online_table1, online_table2, online_table3, offline_table1, offline_table2, offline_table3

所以我们的配置可以优化为：

# spring.shardingsphere.rules.sharding.tables.t_user.actual-data-nodes=server-user.t_user
# spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=server-order0.t_order0,server-order0.t_order1,server-order1.t_order0,server-order1.t_order1
spring.shardingsphere.rules.sharding.tables.t_user.actual-data-nodes=server-user.t_user
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=server-order$->{0..1}.t_order$->{0..1}

2.5 分片算法配置

官方文档：分片算法 (opens new window)、数据分片 (opens new window)

水平分库分片规则：order表中user_id为偶数时，数据插入server-order0服务器，user_id为奇数时，数据插入server-order1服务器。这样分片的好处是，同一个用户的订单数据，一定会被插入到同一台服务器上，查询一个用户的订单时效率较高。

#------------------------分库策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-column=user_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-name=alg_inline_userid

#------------------------分片算法配置
# 行表达式分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.type=INLINE
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.props.algorithm-expression=server-order$->{user_id % 2}

# 取模分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.type=MOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.props.sharding-count=2

水平分表分片规则：order表中order_no的哈希值为偶数时，数据插入对应服务器的t_order0表，order_no的哈希值为奇数时，数据插入对应服务器的t_order1表。因为order_no是字符串形式，因此不能直接取模。

#------------------------分表策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=order_no
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=alg_hash_mod


#------------------------分片算法配置
# 哈希取模分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.type=HASH_MOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.props.sharding-count=2

• 分库：user_id为偶数时数据插入server-order0数据库，为奇数时数据插入server-order1数据库；
• 分表：order_no的哈希值为偶数时插入对应服务器的t_order0表，为奇数时插入对应服务器的t_order1表

/** 水平分片：分表插入数据测试 */
@Test
public void testInsertOrderTableStrategy(){
    for (long i = 1; i < 5; i++) {
        Order order = new Order();
        order.setOrderNo("ATGUIGU" + i);
        order.setUserId(1L);
        order.setAmount(new BigDecimal(100));
        orderMapper.insert(order);
    }

    for (long i = 5; i < 9; i++) {
        Order order = new Order();
        order.setOrderNo("ATGUIGU" + i);
        order.setUserId(2L);
        order.setAmount(new BigDecimal(100));
        orderMapper.insert(order);
    }
}

/** 测试哈希取模 */
@Test
public void testHash(){
    //注意hash取模的结果是整个字符串hash后再取模，和数值后缀是奇数还是偶数无关
    System.out.println("ATGUIGU001".hashCode() % 2);
    System.out.println("ATGUIGU0011".hashCode() % 2);
}

查询测试：

/**
  * 水平分片：查询所有记录
  * 查询了两个数据源，每个数据源中使用UNION ALL连接两个表
  */
@Test
public void testShardingSelectAll(){

    List<Order> orders = orderMapper.selectList(null);
    orders.forEach(System.out::println);
}

/**
  * 水平分片：根据user_id查询记录
  * 查询了一个数据源，每个数据源中使用UNION ALL连接两个表
  */
@Test
public void testShardingSelectByUserId(){

    QueryWrapper<Order> orderQueryWrapper = new QueryWrapper<>();
    orderQueryWrapper.eq("user_id", 1L);
    List<Order> orders = orderMapper.selectList(orderQueryWrapper);
    orders.forEach(System.out::println);
}

2.6 分布式序列算法

传统数据库软件开发中，主键自动生成技术是基本需求。而各个数据库对于该需求也提供了相应的支持，比如 MySQL 的自增键，Oracle 的自增序列等。 数据分片后，不同数据节点生成全局唯一主键是非常棘手的问题。同一个逻辑表内的不同实际表之间的自增键由于无法互相感知而产生重复主键。 虽然可通过约束自增主键初始值和步长的方式避免碰撞，但需引入额外的运维规则，使解决方案缺乏完整性和可扩展性。

目前有许多第三方解决方案可以完美解决这个问题，如 UUID 等依靠特定算法自生成不重复键，或者通过引入主键生成服务等。为了方便用户使用、满足不同用户不同使用场景的需求， Apache ShardingSphere 不仅提供了内置的分布式主键生成器，例如 UUID、SNOWFLAKE，还抽离出分布式主键生成器的接口，方便用户自行实现自定义的自增主键生成器。分布式主键-官方文档 (opens new window)

水平分片需要关注全局序列，因为不能简单的使用基于数据库的主键自增。这里有两种方案：一种是基于MyBatisPlus的id策略；一种是ShardingSphere-JDBC的全局序列配置。基于MyBatisPlus的id策略：将Order类的id设置成如下形式

@TableId(type = IdType.ASSIGN_ID)
private Long id;

为什么雪花算法更好？UUID是完全无序的，无序的数据充当MySQL主键影响数据库的性能，因此使用雪花算法生成有序的数据能在一定程度上提升数据库的性能。

基于ShardingSphere-JDBC的全局序列配置：和前面的MyBatisPlus的策略二选一

#------------------------分布式序列策略配置
# 分布式序列列名称
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.column=id
# 分布式序列算法名称
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-name=alg_snowflake

# 分布式序列算法配置
# 分布式序列算法类型
spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.type=SNOWFLAKE
# 分布式序列算法属性配置
#spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.props.xxx=

此时，需要将实体类中的id策略修改成以下形式：

//当配置了shardingsphere-jdbc的分布式序列时，自动使用shardingsphere-jdbc的分布式序列
//当没有配置shardingsphere-jdbc的分布式序列时，自动依赖数据库的主键自增策略
@TableId(type = IdType.AUTO)

3. 多表关联

3.1 创建关联表

在server-order0、server-order1服务器中分别创建两张订单详情表t_order_item0、t_order_item1，我们希望同一个用户的订单表和订单详情表中的数据都在同一个数据源中，避免跨库关联，因此这两张表我们使用相同的分片策略。那么在t_order_item中我们也需要创建order_no和user_id这两个分片键

CREATE TABLE t_order_item0(
    id BIGINT,
    order_no VARCHAR(30),
    user_id BIGINT,
    price DECIMAL(10,2),
    `count` INT,
    PRIMARY KEY(id)
);

CREATE TABLE t_order_item1(
    id BIGINT,
    order_no VARCHAR(30),
    user_id BIGINT,
    price DECIMAL(10,2),
    `count` INT,
    PRIMARY KEY(id)
);

3.2 创建实体类

@TableName("t_order_item")
@Data
public class OrderItem {
    //当配置了shardingsphere-jdbc的分布式序列时，自动使用shardingsphere-jdbc的分布式序列
    @TableId(type = IdType.AUTO)
    private Long id;
    private String orderNo;
    private Long userId;
    private BigDecimal price;
    private Integer count;
}

3.3 创建Mapper

@Mapper
public interface OrderItemMapper extends BaseMapper<OrderItem> {
}

3.4 配置关联表

t_order_item的分片表、分片策略、分布式序列策略和t_order一致

#------------------------标准分片表配置（数据节点配置）
spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodes=server-order$->{0..1}.t_order_item$->{0..1}

#------------------------分库策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-column=user_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-algorithm-name=alg_mod

#------------------------分表策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-column=order_no
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-algorithm-name=alg_hash_mod

#------------------------分布式序列策略配置
# 分布式序列列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.column=id
# 分布式序列算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.key-generator-name=alg_snowflake

3.5 测试插入数据

同一个用户的订单表和订单详情表中的数据都在同一个数据源中，避免跨库关联

@Test
public void testInsertOrderAndOrderItem(){
    for (long i = 1; i < 3; i++) {
        Order order = new Order();
        order.setOrderNo("ATGUIGU" + i);
        order.setUserId(1L);
        orderMapper.insert(order);
        for (long j = 1; j < 3; j++) {
            OrderItem orderItem = new OrderItem();
            orderItem.setOrderNo("ATGUIGU" + i);
            orderItem.setUserId(1L);
            orderItem.setPrice(new BigDecimal(10));
            orderItem.setCount(2);
            orderItemMapper.insert(orderItem);
        }
    }

    for (long i = 5; i < 7; i++) {
        Order order = new Order();
        order.setOrderNo("ATGUIGU" + i);
        order.setUserId(2L);
        orderMapper.insert(order);
        for (long j = 1; j < 3; j++) {
            OrderItem orderItem = new OrderItem();
            orderItem.setOrderNo("ATGUIGU" + i);
            orderItem.setUserId(2L);
            orderItem.setPrice(new BigDecimal(1));
            orderItem.setCount(3);
            orderItemMapper.insert(orderItem);
        }
    }
}

4. 绑定表

**需求：**查询每个订单的订单号和总订单金额

4.1 创建VO对象

package com.atguigu.shardingjdbcdemo.entity;

@Data
public class OrderVo {
    private String orderNo;
    private BigDecimal amount;
}

4.2 添加Mapper方法

package com.atguigu.shardingjdbcdemo.mapper;

@Mapper
public interface OrderMapper extends BaseMapper<Order> {

    @Select({"SELECT o.order_no, SUM(i.price * i.count) AS amount",
            "FROM t_order o JOIN t_order_item i ON o.order_no = i.order_no",
            "GROUP BY o.order_no"})
    List<OrderVo> getOrderAmount();

}

4.3 测试关联查询

@Test
public void testGetOrderAmount(){
    List<OrderVo> orderAmountList = orderMapper.getOrderAmount();
    orderAmountList.forEach(System.out::println);
}

4.4 配置绑定表

在原来水平分片配置的基础上添加如下配置：

#------------------------绑定表
spring.shardingsphere.rules.sharding.binding-tables[0]=t_order,t_order_item

配置完绑定表后再次进行关联查询的测试：

• **如果不配置绑定表：测试的结果为8个SQL。**多表关联查询会出现笛卡尔积关联。
• 如果配置绑定表：测试的结果为4个SQL。 多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。

绑定表：指分片规则一致的一组分片表。 使用绑定表进行多表关联查询时，必须使用分片键进行关联，否则会出现笛卡尔积关联或跨库关联，从而影响查询效率。

5. 广播表

4.1 什么是广播表

指所有的分片数据源中都存在的表，表结构及其数据在每个数据库中均完全一致。 适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。广播具有以下特性：

1. 插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性
2. 查询操作，只从一个节点获取
3. 可以跟任何一个表进行 JOIN 操作

4.2 创建广播表

在server-order0、server-order1和server-user服务器中分别创建t_dict表

CREATE TABLE t_dict(
    id BIGINT,
    dict_type VARCHAR(200),
    PRIMARY KEY(id)
);

4.3 程序实现

4.3.1 创建实体类

@TableName("t_dict")
@Data
public class Dict {
    //可以使用MyBatisPlus的雪花算法
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
    private String dictType;
}

4.3.2、创建Mapper

@Mapper
public interface DictMapper extends BaseMapper<Dict> {
}

4.3.3、配置广播表

#数据节点可不配置，默认情况下，向所有数据源广播
spring.shardingsphere.rules.sharding.tables.t_dict.actual-data-nodes=server-user.t_dict,server-order$->{0..1}.t_dict

# 广播表
spring.shardingsphere.rules.sharding.broadcast-tables[0]=t_dict

4.4 测试广播表

@Autowired
private DictMapper dictMapper;

/** 广播表：每个服务器中的t_dict同时添加了新数据 */
@Test
public void testBroadcast() {
    Dict dict = new Dict();
    dict.setDictType("type1");
    dictMapper.insert(dict);
}

/** 查询操作，只从一个节点获取数据,随机负载均衡规则 */
@Test
public void testSelectBroadcast() {

    List<Dict> dicts = dictMapper.selectList(null);
    dicts.forEach(System.out::println);
}