数据库之Sharding分库分表操作详解

1 分库分表

1 分库分表

1.1 简介

Sharding有两种：Sharding-Jdbc和ShardingSphere，最早是由当当网开源，捐给了Apache，成为了Apache顶级项目，后来又升级成了ShardingSphere
具体可以参考官网：https://shardingsphere.apache.org/index_zh.html

Apache ShardingSphere 是一款分布式 SQL 事务和查询引擎，可通过数据分片、弹性伸缩、加密等能力对任意数据库进行增强。

1.2 实操准备

1.2.1 Sharding与SpringBoot 公共依赖pom

springboot使用的是2.6.6 版本

 <parent>
   	  <artifactId>spring-boot-starter-parent</artifactId>
     <groupId>org.springframework.boot</groupId>
     <version>2.6.6</version>
 </parent>
 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.70</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.32</version>
        </dependency>

        <!-- druid连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
        <!--注意引入druid需要引入log4j，不然会报错-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.0</version>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.2</version>
        </dependency>
      
    </dependencies>

1.3 Sharding-Jdbc与SpringBoot

Sharding-Jdbc 4.1.1与SpringBoot整合比较简单，只用引个依赖，和写个配置文件即可

注意：如果表明是mysql 关键字，直接使用表明会报错，要么加反引号 ( ` ) ，要么就在表明后加一个下划线即可

注意：sharding-Jdbc 不支持的一些SQL列表：https://shardingsphere.apache.org/document/4.1.1/cn/features/sharding/use-norms/sql/

INSERT INTO tbl_name (col1, col2, …) VALUES(1+2, ?, …)                 
-- VALUES语句不支持运算表达式
INSERT INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = ?    
-- INSERT .. SELECT
SELECT COUNT(col1) as count_alias FROM tbl_name GROUP BY col1 HAVING count_alias > ?        
-- HAVING
SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2                                     
-- UNION
SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2                                 
-- UNION ALL
SELECT * FROM ds.tbl_name1                                                                 
--  包含schema
SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name                                         
-- 详见DISTINCT支持情况详细说明
SELECT * FROM tbl_name WHERE to_date(create_time, ‘yyyy-mm-dd’) = ?                         
-- 会导致全路由
SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name                                         
-- 同时使用普通聚合函数和DISTINCT聚合函数

1.3.1 pom.xml

 <!-- sharding-jdbc -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

1.3.2 配置文件

由于配置文件较多，所以分开写了两个

1.3.2.1 application.yml

spring:
  application:
    name: mybatis-plus-demo
  profiles:
    include: sharding_4


mybatis-plus:
  mapper-locations: 'classpath:mapper/*Mapper.xml'
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  # 此处配置是 指定 mybatis-plus的 json处理文件
  type-handlers-package: cn.util

1.3.2.2 application-sharding_4.yml

spring:
  shardingsphere:
    datasource:
      # 指定多个数据源 用逗号分开
      names: db0
      db0:
        url: jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: xxxx
        password: xxxx
    sharding:
      # 指定 分表 还可以指定分库
      tables:
        user:  # 此处表是逻辑表  对应数据库中 user0,user1
          actual-data-nodes: db0.user$->{0..1}
          table-strategy:
            inline:
              sharding-column: id #指定分库的 键
              # 指定分库计算方法 此处是groovy 语法
              algorithm-expression: user$->{id % 2} 
    props:
      sql:
        show: true   # 日志显示SQL

注意：此处数据库部分报红是正常，不用担心

1.3.3 自定义雪花算法

1.3.3.1 实现ShardingKeyGenerator接口

package cn.util;

import cn.hutool.core.lang.Snowflake;
import cn.hutool.core.util.IdUtil;
import org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator;

import java.util.Properties;

public class CustomSnowflakeKeyGenerator implements ShardingKeyGenerator {

    @Override
    public Comparable<?> generateKey() {
        Snowflake snowflake = IdUtil.createSnowflake(1, 1);
        Long id = snowflake.nextId();
        System.out.println("自定义的id" + id);
        return id;
    }

    @Override
    public String getType() {
        return "SNOWFLAKE_MINE";

    }

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

    @Override
    public void setProperties(Properties properties) {

    }
}

1.3.3.2 添加配置

最后还需要添加一个SPI配置（点击了解SPI机制），resource下面的META-INF/services生成一个文件org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator，配置算法类的全路径。

cn.util.CustomSnowflakeKeyGenerator

1.3.3.3 使用自定义类型

在配置文件application-sharding_jdbc.yml中把对应的key生成规则修改为自定义的SNOWFLAKE_MINE即可
在这里插入图片描述

1.4 ShardingSphere与SpringBoot

Sharding-Jdbc 5.2.0 与SpringBoot整合比较简单，只用引个依赖，和写个配置文件即可

1.4.1 pom.xml

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

1.4.2 配置文件

此处配置文件大部分是从官网摘取，找到对应版本号即可查阅，比如：5.2.0https://shardingsphere.apache.org/document/5.2.0/cn/user-manual/shardingsphere-jdbc/spring-boot-starter/rules/sharding/

1.4.2.1 application.yml

spring:
  application:
    name: mybatis-plus-demo
  profiles:
    include: sharding_jdbc


mybatis-plus:
  mapper-locations: 'classpath:mapper/*Mapper.xml'
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  # 此处配置是 指定 mybatis-plus的 json处理文件
  type-handlers-package: cn.util

1.4.2.2 application-sharding_jdbc.yml

spring:
  shardingsphere:
    datasource:
      names: db0
      db0:
        url: jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: xxxx
        password: xxxx
    rules:
      sharding:
        tables:
          user:
            # 数据节点配置，采用Groovy表达式
            actual-data-nodes: db0.user$->{0..1}
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: user-inline
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          user-inline:
            type: INLINE
            props:
              algorithm-expression: user$->{id%2}
        key-generators:
          snowflake:
            type: SNOWFLAKE
    props:
      # 日志显示具体的SQL
      sql.show: true

注意：此处数据库部分报红是正常，不用担心

1.4.2.3 java编码配置方式

可以使用yml配置和Java编码两种方式做了实现。要注意的是两种方式不要并存，不然启动会报错

@Configuration
public class ShardingConfiguration {
    @Bean
    public DataSource getShardingDataSource() throws SQLException {
    	//多数据源场景
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("db0", dataSource0());
        dataSourceMap.put("db1", dataSource1());

        // 分片rules规则配置
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // 分片算法
        shardingRuleConfig.setShardingAlgorithms(getShardingAlgorithms());
        // 配置 t_order 表分片规则
        ShardingTableRuleConfiguration orderTableRuleConfig = new ShardingTableRuleConfiguration("t_order", "db${0..1}.t_order_${1..1000}");
        orderTableRuleConfig.setTableShardingStrategy(new StandardShardingStrategyConfiguration("order_id", "t_order_table_algorithms"));
        orderTableRuleConfig.setDatabaseShardingStrategy(new StandardShardingStrategyConfiguration("order_id", "t_order_database_algorithms"));
        shardingRuleConfig.getTables().add(orderTableRuleConfig);
        // 是否在控制台输出解析改造后真实执行的 SQL
        Properties properties = new Properties();
        properties.setProperty("sql-show", "true");
        // 创建 ShardingSphere 数据源
        return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, Collections.singleton(shardingRuleConfig), properties);
    }

   
    public DataSource dataSource0() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/db0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        return dataSource;
    }

    public DataSource dataSource1() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        return dataSource;
    }


    private Map<String, AlgorithmConfiguration> getShardingAlgorithms() {
        Map<String, AlgorithmConfiguration> shardingAlgorithms = new LinkedHashMap<>();
        // 自定义分库算法
        Properties databaseAlgorithms = new Properties();
        databaseAlgorithms.setProperty("algorithm-expression", "db$->{order_id % 2}");
        shardingAlgorithms.put("t_order_database_algorithms", new AlgorithmConfiguration("INLINE", databaseAlgorithms));
        // 自定义分表算法
        Properties tableAlgorithms = new Properties();
        tableAlgorithms.setProperty("algorithm-expression", "db$->{order_id % 1000}");
        shardingAlgorithms.put("t_order_table_algorithms", new AlgorithmConfiguration("INLINE", tableAlgorithms));
        return shardingAlgorithms;
    }
}

1.4.2.4 自定义管理分片表配置

由于默认的分片管理还是有局限性的，我们在设计分片规则时往往会根据不同的业务维度来划分，例如按天、月、按季度生成分片表并分布到不同数据源中等。这样就需要一些自定义的规则来实现。
ShardingSphere 5.X版本后推出了一种新的管理分片配置方式：AutoTable。设置了AutoTable的逻辑表，将交由ShardingSphere自动管理分片，用户只需要指定分片数量和使用的数据库实例，无需再关心表的具体分布，配置格式如下：

spring:
  shardingsphere:
    # 数据源配置
    datasource:
      ......
    # 具体规则配置
    rules:
      sharding:
        # 逻辑表分片规则
        tables:
          # 逻辑表名称
          t_order:
            .....
        # 自动分片表规则配置
        auto-tables:
          t_order: # 逻辑表名称
            actual-data-sources: db$->{0..1}
            sharding-strategy: # 切分策略
              standard: # 用于单分片键的标准分片场景
                sharding-column: order_id # 分片列名称
                sharding-algorithm-name: t_order_mod # 自动分片算法名称

ShardingSphere-Jdbc中配置使用auto-tables主要两个参数：

actual-data-sources指定数据源分布，由于是管理分片表所以只需数据源信息即可
sharding-strategy指具体采用何种算法来进行分片

对逻辑表的DDL操作，系统会首先检查是否配置了AutoTable，如果已配置，则优先采用配置的规则；若未配置，则将使用默认的逻辑表分片规则。
AutoTable支持ShardingSphere内置的全部自动分片算法，所谓自动分片算法就是根据actualDataSources设置的数据源信息，使用对应内置算法自行解析处理：

MOD：取模分片算法
HASH_MOD：哈希取模分片算法
VOLUME_RANGE：基于分片容量的范围分片算法
BOUNDARY_RANGE：基于分片边界的范围分片算法
AUTO_INTERVAL：自动时间段分片算法

1.4.3 自定义雪花算法

1.4.3.1 实现KeyGenerateAlgorithm接口

需要实现 KeyGenerateAlgorithm 接口

package cn.util;

import cn.hutool.core.lang.Snowflake;
import cn.hutool.core.util.IdUtil;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.sharding.spi.KeyGenerateAlgorithm;

import java.util.Properties;


@Slf4j
public class SnowFlake implements KeyGenerateAlgorithm  {

    @Override
    public Comparable<?> generateKey() {
        Snowflake snowflake = IdUtil.createSnowflake(1, 1);
        Long id = snowflake.nextId();
        System.out.println("自定义的id" + id);
        return id;

    }
	// 返回 自定义 算法类型表示
    @Override
    public String getType() {
        return "SNOWFLAKE_MINE";
    }

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

    @Override
    public void init(Properties properties) {
        
    }
}

1.4.3.2 添加配置

最后还需要添加一个SPI配置（点击了解SPI机制），resource下面的META-INF/services生成一个文件org.apache.shardingsphere.sharding.spi.KeyGenerateAlgorithm，配置算法类的全路径。

cn.util.SnowFlake

1.4.3.3 使用自定义类型

在配置文件application-sharding_jdbc.yml中把对应的key生成规则修改为自定义的SNOWFLAKE_MINE即可
在这里插入图片描述

1.4.3.4 MybatisPlus插入失效

如果使用的是 MybatisPlus 插入时发现用的 key 还是系统自带的雪花算法，觉得没有生效，需要把MybatisPlus 对应的实体主键对应的注解 @TableId 注释掉即可

1.5 ShardingSphere与MybatisPlus多数据源

由于ShardingSphere切换表或者库是自动的，如果想手动指定一个数据源还得用 MybatisPlus 多数据源比较方便点
点击此处了解 springboot多数据源配置

1.5.1 pom.xml

<dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
     <version>3.5.0</version>
 </dependency>

1.5.2 配置文件

1.5.2.1 application.yml

spring:
  application:
    name: mybatis-plus-demo
  profiles:
    include: mbp_dynamic,sharding_jdbc


mybatis-plus:
  mapper-locations: 'classpath:mapper/*Mapper.xml'
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  type-handlers-package: cn.util

1.5.2.2 application-mbp_dynamic.yml

spring:
  datasource:
    dynamic:
      primary: master
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/test_2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
          type: com.alibaba.druid.pool.DruidDataSource
          username: xxxx
          password: xxxx

1.5.2.3 application-sharding_jdbc.yml

spring:
  shardingsphere:
    datasource:
      names: db0
      db0:
        url: jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: xxxx
        password: xxxx
    rules:
      sharding:
        tables:
          user:
            # 数据节点配置，采用Groovy表达式
            actual-data-nodes: db0.user$->{0..1}
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: user-inline
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          user-inline:
            type: INLINE
            props:
              algorithm-expression: user$->{id%2}
        key-generators:
          snowflake:
          # 此处采用自定义 雪花算法
            type: SNOWFLAKE_MINE
    props:
      # 日志显示具体的SQL
      sql.show: true

1.5.3 ShardingSphere与多数据源整合

package cn.util;

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.driver.jdbc.adapter.AbstractDataSourceAdapter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Primary;

import javax.annotation.Resource;
import javax.sql.DataSource;

import java.util.Map;

@Slf4j
@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class DataSourceConfiguration {

    // 分表数据源名称
    public static final String SHARDING_DATA_SOURCE_NAME = "sharding";

    //动态数据源配置项
    @Autowired
    private DynamicDataSourceProperties properties;

   
    @Lazy
    @Resource(name = "shardingSphereDataSource")
    private AbstractDataSourceAdapter shardingDataSource;

    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                // 将 shardingjdbc 管理的数据源也交给动态数据源管理
                dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
                return dataSourceMap;
            }
        };
    }

    /**
     * 将动态数据源设置为首选的
     * 当spring存在多个数据源时, 自动注入的是首选的对象
     * 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
     */
    @Bean
    @Primary
    public DataSource dataSource() {
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
        dataSource.setPrimary(properties.getPrimary());
        dataSource.setStrict(properties.getStrict());
        dataSource.setStrategy(properties.getStrategy());
        dataSource.setP6spy(properties.getP6spy());
        dataSource.setSeata(properties.getSeata());
        return dataSource;
    }
}

当在使用时在需要使用Sharding的地方使用 @DS("sharding")即可切换到Sharding

上面的 dynamicDataSourceProvider 方法也可以这样重构

@Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        //Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                // Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                Map<String, DataSource> dataSourceMap = new HashMap<>();
                // 将 shardingjdbc 管理的数据源也交给动态数据源管理
                dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
                return dataSourceMap;
            }
        };
    }

1.6 集成报错

1.6.1 报错：Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required

如果使用了druid，依赖必须使用druid，而不能是 druid-spring-boot-starter

<!-- druid -->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
        <version>1.2.8</version>
</dependency>

<!-- shardingsphere -->
<dependency>
	<groupId>org.apache.shardingsphere</groupId>
	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	<version>5.2.0</version>
</dependency>

posted @ 2023-07-22 23:03 上善若泪阅读(553) 评论(0) 编辑收藏举报

刷新页面返回顶部

上善若泪