ShardingSphere基础

1.类型

1.1 ShardingSphere-JDBC客户端

客户端直连数据库，jar包形式提供服务，适用于任何ORM框架

1.2 ShardingSphere-Proxy服务端

透明化的数据库代理端，实现数据库二进制协议，对异构语言提供支持，可直接当做Mysql/PostgreSQL使用

1.3 混合部署

2.基本使用

• 数据库
  delivery_system_1 有order_info_1
  delivery_system_2 有order_info_2
• 依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version>
</dependency>

• 配置

spring:
  application:
    name: shop
  shardingsphere:
    # 数据源
    datasource:
      names: delivery_system_1, delivery_system_2
      delivery_system_1:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://localhost:3306/delivery_system_1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        driverClassName: com.mysql.cj.jdbc.Driver
        username: root
        password: lwxMysql
      delivery_system_2:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://localhost:3306/delivery_system_2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        driverClassName: com.mysql.cj.jdbc.Driver
        username: root
        password: lwxMysql
    rules:
      sharding:
        # 雪花算法
        key-generators:
          alg_snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1
        # 指定表
        tables:
          order_info:
            # 主键生成策略
            key-generate-strategy:
              column: id
              key-generator-name: alg_snowflake
            # 分片节点 库.表
            actual-data-nodes: delivery_system_$->{1..2}.order_info_$->{1..2}
            # 分库策略
            database-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: course_db_alg
            # 分表策略
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: course_tbl_alg
        # 分片算法
        sharding-algorithms:
          # 分库策略
          course_db_alg:
            type: INLINE
            props:
              algorithm-expression: delivery_system_$->{Long.parseLong(id) % 2 + 1}
          # 分表策略
          course_tbl_alg:
            type: INLINE
            props:
              algorithm-expression: order_info_$->{Long.parseLong(id) % 2 + 1}

• 实体类等为MybatisPlus框架
• 测试代码

// 增
        for (int i = 0; i < 10; i++){
            OrderInfo orderInfo = new OrderInfo();
            orderInfo.setRemark("this " + i);
            mapper1.insert(orderInfo);
        }

        // 删除
        Wrapper<OrderInfo> wrapper = new QueryWrapper<OrderInfo>()
                .eq("remark", "this 0");
        int f = mapper1.delete(wrapper);
        System.out.println(f);

        // 改
        LambdaUpdateWrapper<OrderInfo> updateWrapper = new LambdaUpdateWrapper<OrderInfo>()
                .eq(OrderInfo::getRemark, "this 1")
                .set(OrderInfo::getRemark, "this 1 new");
        boolean res = service1.update(updateWrapper);
        System.out.println(res);

        // 查
        LambdaQueryWrapper<OrderInfo> queryWrapper = new LambdaQueryWrapper<OrderInfo>();
        List<OrderInfo> list = service1.list(queryWrapper);
        for (OrderInfo orderInfo : list){
            System.out.println(orderInfo);
        }

3.核心

3.1 概念

• 虚拟库：ShardingSphere提供一个具备分库分表的虚拟库，是一个ShardingSphereDatasource实例。未指定的情况下Mybatis使用的就是该数据源
• 逻辑表：应用程序直接操作
• 分片策略：分片键和分片算法组成。分片键是水平拆分关键字段。分片算法表示分片键如何找到真实库和真实

3.分片策略

3.1 INLINE

处理分片键的in = 简单操作，可以拿到分片键精确值
问题：

• 查询中如果不包含分片键只能到所有库和表中查询，即全分片路由
• 范围查询需要加allow-range-query-with-inline-sharding: true

3.2 standard

同时支持精确查询和范围查询

3.3 COMPLEX_LINE

多个属性组合参与分片算法

• 修改配置

            table-strategy:
              # 俩个键分片
              complex:
                sharding-column: id,uid
                sharding-algorithm-name: course_tbl_alg
        sharding-algorithms:
          course_db_alg:
            type: MOD
            props:
              sharding-count: 2
          # 俩个键分片
          course_tbl_alg:
            type: COMPLEX_INLINE
            props:
              algorithm-expression: course_$->{(id + uid + 1)%2+1}

3.4 CLASS_BASED

自定义分片来实现具体功能

• 配置

        sharding-algorithms:
          course_db_alg:
            type: MOD
            props:
              sharding-count: 2
          # 分表策略
            course_tbl_alg:
              type: CLASS_BASED
              props:
                # 指定策略
                strategy: COMPLEX
                # 类名
                algorithmClassName: org.example.shardingSphereTest.MyComplexAlgorithm

• 自定义类

/**
 * ComplexKeysShardingAlgorithm<> 泛型是分片键数据类型
 */
public class MyComplexAlgorithm implements ComplexKeysShardingAlgorithm<Long> {

    private static final String SHARING_COLUMNS_KEY = "sharding-columns";

    private Properties props;
    // 保留配置的分片键
    private Collection<String> shardingColumn;

    @Override
    public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<Long> complexKeysShardingValue) {
        Collection<Long> idCol = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("cid");
        Range<Long> uidRange = complexKeysShardingValue.getColumnNameAndRangeValuesMap().get("uid");
        // uid 范围
        Long lowerEndPoint = uidRange.lowerEndpoint();
        Long upperEndPoint = uidRange.upperEndpoint();
        // 下限 >= 上限
        if (lowerEndPoint >= upperEndPoint) {
            throw new UnsupportedShardingOperationException("empty", "course");
        } else if (upperEndPoint < 1001L || lowerEndPoint > 1001L) {
            throw new UnsupportedShardingOperationException("range", "course");
        } else {
            List<String> res = new ArrayList<>();
            // uid 包含1001之后 按id的奇偶分片
            String logicTableName = complexKeysShardingValue.getLogicTableName();
            for (Long idVal : idCol){
                String targetTable = logicTableName + "_" + (idVal % 2) + 1;
                if (collection.contains(targetTable)){
                    res.add(targetTable);
                }
            }
            return res;
        }
    }
    
    private Collection<String> getShardingColumn(final Properties props){
        String shardingColumns = props.getProperty(SHARING_COLUMNS_KEY, "");
        return shardingColumns.isEmpty() ? Collections.emptyList() : Arrays.asList(shardingColumns.split(","));
    }

    @Override
    public void init(Properties properties) {
        this.props = properties;
        this.shardingColumn = getShardingColumn(properties);
    }

    @Override
    public Properties getProps() {
        return null;
    }
}

3.5 HINT_INLINE

强制分片：查询id为奇数的信息，用一种SQL无关的方式进行分库分表

• 配置

 # 指定表
        tables:
          course:
            table-strategy:
              hint:
                sharding-algorithm-name: course_tbl_alg
- 查询
        # MOD分库策略
        sharding-algorithms:
          course_db_alg:
            type: MOD
            props:
              sharding-count: 2
          # 分表策略
              course_tbl_alg:
                type: HINT_INLINE
                props:
                  algorithm-expression: course_$->{value}

    public static void main(String[] args) {
        HintManager hintManager = HintManager.getInstance();
        //dataSourceBaseShardingValue 用于强制分库
        hintManager.addTableShardingValue("course", "1");
        List<Course> courses = courseMapper.selectList(null);
        // 关闭
        hintManager.close();
    }

4.数据加密

    rules:
      # 加密配置
      encrypt:
        tables:
          user:
            columns:
              password:
                # 存储密文的字段
                cipherColumn: password
                # 存储明文的字段
                plainColumn: password1
                # 加密器
                encryptorName: user_pass_encry
        encryptors:
          # SM3加密器
          user_pass_encry:
            type: SM3
            sm3-salt: 123456

5.读写分离

      # 读写分离
      readwrite-splitting:
        data-sources:
          tablename:
            static-strategy:
              # 主从库
              write-data-source-name: m0
              read-data-data-source-names0[0]: m1
              # 指定负载均衡器
              load-balancer-name: roundRobin
        # 配置负载均衡器
        load-balancers: 
          roundRobin:
            # 轮询
            type: ROUND_ROBIN
            # 事务轮询
            type: TRANSACTION_ROUND_ROBIN
            # 操作随机
            type: random
            # 事务随机
            type: TRANSACTION_random
            # 强制主库
            type: Fixed_PRIMARY

6.广播表

所有分片数据源都存在的表，结构和数据完全一样。适用于数据量不大且需要与海量数据表相关联查询的场景

    rules:
      # 广播表
      sharding:
        tables: 
          dict:
            actual-data-nodes: m$->{0..1}.dict
        broadcast-tables: dict, dict_type

7.绑定表

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

    rules:
      # 绑定表
      sharding:
        binding-tables[0]: tablename, tablename1

8.分片审计

对SQL进行审计。
DML_SHARDING_CONDITIONS: 对逻辑表查询时必须带分片号

      # 分片审计
      sharding:
        tables: 
          tablename:
            audit-strategy:
              auditor-names[0]: tabelname_auditor
              allow-hint-diable: true
        auditors:
          default-data-auditor:
            # 默认数据审计
            type: DML_SHARDING_CONDITIONS