springboot整合sharding-jdbc实现按年分库按月分表

最新推荐文章于 2024-05-21 08:17:41 发布

秋大帅

最新推荐文章于 2024-05-21 08:17:41 发布

阅读量9.6k

点赞数 23

分类专栏： springboot整合sharding-jdbc 分库分表按年分库按月分表文章标签： java mysql spring boot sharding 数据库

本文链接：https://blog.csdn.net/weixin_47077809/article/details/113694604

版权

springboot整合sharding-jdbc实现按年分库按月分表（实现、简析篇）

1.前言：

此方案为适用对时间依赖度较高的数据进行水平切分，如果你正好符合，那么你可以看看此篇，不符合也可以看看，毕竟sharding-jdbc对分库分表的方案实现度很高，可根据自身实际情况进行数据切分，如果你对以下概念已经有共了解，可直接跳到代码环节

2.分库分表概念

在做实现之前，我们需要了解到何为分库分表，为何要这样做，这样做有何利弊？

由于关系型数据库的先天特性，在单表数据量达到一定体量的时候，就会出现性能瓶颈，至于具体是多少，此篇不做研究，说法众多，后期有时间的话可以进行测试，为了解决性能瓶颈问题，行业内的先驱们则提出了对数据进行分而治之的思想，对数据以贴合实际情况进行切分，达到对数据库性能的优化，主要的切分方式有行业先驱总结了一下几种，也可参考https://www.cnblogs.com/buddy-yuan/p/12089178.html：

1.垂直拆分

1）垂直分库：垂直分库其实是一种简单逻辑分割，把单一数据库按照业务进行划分，达到一个专库专表的效果，例如可将系统拆分为配置库、历史数据库、用户库…

2）垂直分表：比较适用于那种字段比较多的表，且有些字段并非所有的需求都要使用到，操作数据库中的某张表，将表中一部分字段信息存储至一张新表中，原表中不在存储被拆分出去的字段信息，例如机组信息表可拆分为机组基础信息表、机组详细信息表

垂直拆分有以下优点：

跟随业务进行分割，和最近流行的微服务概念相似，方便解耦之后的管理及扩展。
高并发的场景下，垂直拆分使用多台服务器的CPU、I/O、内存能提升性能，同时对单机数据库连接数、一些资源限制也得到了提升。
能实现冷热数据的分离。

垂直拆分的缺点：

部分业务表无法join，应用层需要很大的改造，只能通过聚合的方式来实现。增加了开发的难度。
当单库中的表数据量增大的时候依然没有得到有效的解决。
分布式事务也是一个难题。

2.水平拆分

当某张表数据量达到一定的程度的时候，例如MySQL单表出现2000万以上数据就会出现性能上的分水岭。此时发现没有办法根据业务规则再进行拆分了，就会导致单库上的读写性能出现瓶颈。此时就只能进行水平拆分了。

1）库内分表：假设当我们的c_vib_point表达到了5000万行记录的时候，非常影响数据库的读写效率，怎么办呢？我们可以考虑按照订单编号的id进行rang分区,就是把订单编号在1-1000万的放在order1表中，将编号在1000万-2000万的放在order2中，以此类推，每个表中存放1000万数据，或者根据插入时间进行分表，一年一个表，也可控制单表数量，但是表的数据仍然存放在一个库内，使用的是该主机的CPU、IO、内存。单库的连接数也有限制。并不能完全的降低系统的压力

2）分库分表：分库分表在库内分表的基础上，将分的表挪动到不同的主机和数据库上。可以充分的使用其他主机的CPU、内存和IO资源。并且分库之后，单库的连接数限制也不在成为瓶颈。但是“成也萧何败也萧何”，如果你执行一个扫描不带分片键，则需要在每个库上查一遍。刚刚我们按照id分成了5个库，但是我们查询是name='AAA’的条件并且不带id字段时，它并不知道在哪个分片上查，则会创建5个连接，然后每个库都检索一遍。这种广播查询则会造成连接数增多。因为它需要在每个库上都创立连接。如果是高并发的系统，执行这种广播查询，系统的thread很快就会告警，所以对于开发者的数据库操作会更加严格。

水平拆分的优点：

水平扩展能无线扩展。不存在某个库某个表过大的情况。
能够较好的应对高并发，同时可以将热点数据打散。
应用侧的改动较小，不需要根据业务来拆分。

水平拆分的缺点：

路由是个问题，需要增加一层路由的计算，而且像前面说的一样，不带分片键查询会产生广播SQL。
跨库join的性能比较差。
需要处理分布式事务的一致性问题。

个人觉得，分库分表有利有弊，在使用分库分表之前，应优先考虑缓存技术、读写分离、优化SQL、使用索引等方式，分库分表作为最后的方案，且最好是在项目初期就应该做好前瞻性考虑，如果你确定项目在未来的一定时间内，数据量确确实实会达到一个非常影响性能的体量，那么就可以考虑做分库分表，当然，你可以暂时先不做分库分表，等数据量上来了再做

3.sharding-jdbc概念

一个技术的出现必然是被需求所驱动，sharding-jdbc就是分库分表的产物，当然它并非唯一实现，只是此篇以sharding-jdbc作为技术支持来实现分库分表，下面可了解一下其相关概念，最好做到知其然知其所以然

1.简介

sharding-jdbc定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

2.SQL

1）逻辑表

水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例如根据时间将order一年表拆分为12个表，分别是order_01到order_12，则他们的逻辑表名为order

2）真实表

在分片的数据库中真实存在的物理表。即上个示例中order_01到order_12

3）数据节点

数据分片的最小单元。由数据源名称和数据表组成，例：dataSource.order_01

4）绑定表

指分片规则一致的主表和子表。例如：t_order表和t_order_item表，均按照order_id分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。举例说明，如果SQL为：

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

在不配置绑定表关系时，假设分片键order_id将数值10路由至第0片，将数值11路由至第1片，那么路由后的SQL应该为4条，它们呈现为笛卡尔积：

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

在配置绑定表关系后，路由的SQL应该为2条：

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

其中t_order在FROM的最左侧，ShardingSphere将会以它作为整个绑定表的主表。所有路由计算将会只使用主表的策略，那么t_order_item表的分片计算将会使用t_order的条件。故绑定表之间的分区键要完全相同。

5）广播表

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

2.分片

1）分片键

用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将订单表中的主键的尾数取模分片，则订单表主键为分片字段。 SQL中如果无分片字段，将执行全路由，性能较差。除了对单分片字段的支持，ShardingSphere也支持根据多个字段进行分片。

2）分片算法

通过分片算法将数据分片，支持通过=、>=、<=、>、<、BETWEEN和IN分片。分片算法需要应用方开发者自行实现，可实现的灵活度非常高。

目前提供4种分片算法。由于分片算法和业务实现紧密相关，因此并未提供内置分片算法，而是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法。

精确分片算法

对应PreciseShardingAlgorithm，用于处理使用单一键作为分片键的=与IN进行分片的场景。需要配合StandardShardingStrategy使用。
范围分片算法

对应RangeShardingAlgorithm，用于处理使用单一键作为分片键的BETWEEN AND、>、<、>=、<=进行分片的场景。需要配合StandardShardingStrategy使用。
复合分片算法

对应ComplexKeysShardingAlgorithm，用于处理使用多键作为分片键进行分片的场景，包含多个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度。需要配合ComplexShardingStrategy使用。
Hint分片算法

对应HintShardingAlgorithm，用于处理使用Hint行分片的场景。需要配合HintShardingStrategy使用。

3）分片策略

包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。目前提供5种分片策略。
- 标准分片策略
  
  对应StandardShardingStrategy。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的，用于处理=和IN的分片。RangeShardingAlgorithm是可选的，用于处理BETWEEN AND, >, <, >=, <=分片，如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理。
- 复合分片策略
  
  对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，最大的灵活度。
- 行表达式分片策略
  
  对应InlineShardingStrategy。使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8，而分成8张表，表名称为t_user_0到t_user_7。
- Hint分片策略
  
  对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略。
- 不分片策略
  
  对应NoneShardingStrategy。不分片的策略。

4.上代码，开干

此案例为springboot整合mybatis+sharding-jdbc+druid，项目中可根据实际情况替换，sharding都支持

1）引入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.dashuai</groupId>
    <artifactId>sharding-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath/>
    </parent>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- 单元测试 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--  JDBC起步依赖  -->
        <!--<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <exclusions>
                &lt;!&ndash; 排除掉HikariCP连接池 &ndash;&gt;
                <exclusion>
                    <groupId>com.zaxxer</groupId>
                    <artifactId>HikariCP</artifactId>
                </exclusion>
            </exclusions>
        </dependency>-->
        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
            <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-api</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- sharding-jdbc -->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0.M1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.1.0</version>
        </dependency>
    </dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2）配置文件

server:
  port: 9090
  servlet:
    context-path: /sharding
spring:
  application:
    name: sharding-demo
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.dashuai.pojo
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

3）启动类

package com.dashuai;

import io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;


@SpringBootApplication(exclude = {
   SpringBootConfiguration.class})
@MapperScan("com.dashuai.mapper")
public class ShardingApplication {
   
    public static void main(String[] args) {
   
        SpringApplication.run(ShardingApplication.class, args);
    }
}

此处有坑：如果你使用java进行sharding配置，那么这里需要排除sharding的自动配置，注意引包为io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration，否则会报数据源为空或者sqlSessionFactory为空，原因主要是 springboot与shardingjdbc整合的时候，默认会使用SpringbootConfiguration这个类(在sharding-jdbc包下)自动的从配置文件中读取配置，如果读取不到，那么数据源就配置不成功，因为这里我们使用配置类进行配置，不使用配置文件，所以他就读不到配置，那么就会报错，使用java配置有一定优点，请往下看

4）配置类

package com.dashuai.config;

import com.dashuai.utils.datasource.DataSourceUtil;
import com.dashuai.utils.shardingarithmetic.PreciseDatabaseShardingAlgorithm;
import com.dashuai.utils.shardingarithmetic.PreciseTableShardingAlgorithm;
import com.dashuai.utils.shardingarithmetic.RangeDatabaseShardingAlgorithm;
import com.dashuai.utils.shardingarithmetic.RangeTableShardingAlgorithm;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.context.annotation.Bean;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;


@SpringBootConfiguration
public class ShardingConfig {
   


    /**
     * 逻辑表名称
     */
    private final String LOGICAL_TABLE = "t_order";

    /**
     * 分片键
     */
    private final String DATABASE_SHARDING_COLUMN = "save_time_com";


    @Bean
    DataSource getShardingDataSource() throws SQLException {
   

        // 分片规则配置对象
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // 规则配置
        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
        // 数据库分片算法（精确、范围），按年分库
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration(DATABASE_SHARDING_COLUMN, new PreciseDatabaseShardingAlgorithm(), new RangeDatabaseShardingAlgorithm()));
        // 表分片算法（精确、范围），按月分表
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration(DATABASE_SHARDING_COLUMN, new PreciseTableShardingAlgorithm(), new RangeTableShardingAlgorithm()));
        // 默认库，如果存在广播表和绑定表也可在此配置
        shardingRuleConfig.setDefaultDataSourceName("default_dataSource");
        // 开启日志打印
        final Properties properties = new Properties();
        properties.setProperty("sql.show", "true");

        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties);
    }


    TableRuleConfiguration getOrderTableRuleConfiguration() {
   
        
        // 暂定为两年，关于此表达式，可查看官方文档 https://shardingsphere.apache.org/document/legacy/4.x/document/cn/features/sharding/other-features/inline-expression/
        String inLineExpressionStr = "dataSource_2020." + LOGICAL_TABLE + "_20200${1..9}" + "," + "dataSource_2021." + LOGICAL_TABLE + "_20210${1..9}" + "," +
                "dataSource_2020." + LOGICAL_TABLE + "_20201${0..2}" + "," + "dataSource_2021." + LOGICAL_TABLE + "_20211${0..2}";
        final TableRuleConfiguration ruleConfiguration = new TableRuleConfiguration("t_order", inLineExpressionStr);
        // 设置主键生成策略
        ruleConfiguration.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
        return ruleConfiguration;
    }

    private KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
   

        return new KeyGeneratorConfiguration("SNOWFLAKE", "id");
    }


    private Map<String, DataSource> createDataSourceMap() {
   

        // key为数据源名称，后面分片算法取得就是这个，value为具体的数据源
        final HashMap<String, DataSource> shardingDataSourceMap = new HashMap<>();
        shardingDataSourceMap.put("default_dataSource", DataSourceUtil.createDataSource("com.mysql.cj.jdbc.Driver",

最低0.47元/天解锁文章

秋大帅

关注

23
点赞
踩
100

收藏

觉得还不错? 一键收藏
25
评论
springboot整合sharding-jdbc实现按年分库按月分表

springboot整合sharding-jdbc实现按年分库按月分表1.前言：此方案为适用对时间依赖度较高的数据进行水平切分，如果你正好符合，那么你可以看看此篇，不符合也可以看看，毕竟sharding-jdbc对分库分表的方案实现度很高，可根据自身实际情况进行数据切分2.分库分表概念在做实现之前，我们需要了解到何为分库分表，为何要这样做，这样做有何利弊？由于关系型数据库的先天特性，在单表数据量达到一定体量的时候，就会出现性能瓶颈，至于具体是多少，此篇不做研究，说法众多，后期有时
复制链接

扫一扫