shardingsphere按年月分库分表实战

已于 2023-03-02 15:45:45 修改
阅读量3.8k 收藏 9
点赞数 5
分类专栏： shardingsphere 文章标签： 数据库
于 2022-11-17 17:38:33 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ITwenhao/article/details/127908040
版权

 前言：

 

        由于业务订单增长，设计之初没有考虑到订单量会达到亿级以上，从而导致现在订单服务查询耗时太久，为解决该问题，并结合业务实际情况，通过调研最终采用shardingsphere分库分表架构对订单数据按年分库，按业务code和月分表，最终分布到每张表的数据量尽可能的减少到百万以内，优化查询效果。

关于shardingsphere的介绍和基本使用可参考ShardingSphere介绍与使用_还没秃的小菜鸡的博客-CSDN博客_shardingsphere

官网地址：  概览 :: ShardingSphere

目前shardingshpere支持5.0以上版本，为了避免高版本带来的一些坑，作者这里选用4.1.1 的springboot起步依赖包

pom依赖如下：

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

数据库表结构：

CREATE TABLE `insurance_order` (
 `subOrderId` varchar(128) NOT NULL COMMENT '子订单',
 `orderId` varchar(128) NOT NULL COMMENT '保险订单号',
 `otaOrderId` varchar(128) NOT NULL COMMENT 'ota的主订单',
 `merchantId` varchar(32) NOT NULL COMMENT '商户Id',
 `insurCompanyCode` varchar(32) NOT NULL COMMENT '保险公司id',
 `policyNo` varchar(128) NOT NULL DEFAULT '' COMMENT '保单号',
 `proposalNo` varchar(128) NOT NULL DEFAULT '' COMMENT '核保订单号',
 `insurType` varchar(32) NOT NULL DEFAULT '' COMMENT '保险类别',
 `productCode` varchar(32) NOT NULL DEFAULT '' COMMENT '保险产品代码',
 `premium` double(10,2) NOT NULL DEFAULT '0.00' COMMENT '总保费',
 `amount` double(20,2) NOT NULL DEFAULT '0.00' COMMENT '保额',
 `createTime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '订单创建时间'
。。。部分省略
 PRIMARY KEY (`subOrderId`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='保险订单';

核心代码：

@SpringBootConfiguration
public class InsuranceOrderShardingConfig {

    //逻辑表名称
    private final String LOGICAL_TABLE = "insurance_order";
   	//数据库分片键
    private final String DATABASE_SHARDING_COLUMN = "createTime";
    //表分片键
    private final String TABLE_SHARDING_COLUMN = "createTime,insurCompanyCode";

    @Bean
    DataSource getShardingDataSource() throws SQLException {
        // 分片规则配置对象
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // 规则配置
        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
        // 默认库，如果存在广播表和绑定表也可在此配置
        shardingRuleConfig.setDefaultDataSourceName("default_dataSource");
        final Properties properties = new Properties();
        // 开启日志打印
        properties.setProperty("sql.show", "true");
        // 数据源配置
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties);
    }

	 //定义物理表建表规则
     // 关于inline表达式，可查看官方文档 		    https://shardingsphere.apache.org/document/legacy/4.x/document/cn/features/sharding/other-features/inline-expression/
    private TableRuleConfiguration getOrderTableRuleConfiguration() {
        String expression = "2020." + LOGICAL_TABLE + "_${['pingan','taibao','yangguang','renbao','renshou']}" + "_2020_${1..9}," +
                "2020." + LOGICAL_TABLE + "_${['pingan','taibao','yangguang','renbao','renshou']}" + "_2020_${10..12}," +
                "2021." + LOGICAL_TABLE + "_${['pingan','taibao','yangguang','renbao','renshou']}" + "_2021_${1..9}," +
                "2021." + LOGICAL_TABLE + "_${['pingan','taibao','yangguang','renbao','renshou']}" + "_2021_${10..12}," +
                "2022." + LOGICAL_TABLE + "_${['pingan','taibao','yangguang','renbao','renshou']}" + "_2022_${1..9}," +
                "2022." + LOGICAL_TABLE + "_${['pingan','taibao','yangguang','renbao','renshou']}" + "_2022_${10..12}";

        TableRuleConfiguration ruleConfiguration = new TableRuleConfiguration("insurance_order", expression);

        ruleConfiguration.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration(DATABASE_SHARDING_COLUMN,new DatabaseByYearShardingAlgorithm(), new RangeDatabaseShardingAlgorithm()));
        ruleConfiguration.setTableShardingStrategyConfig(new ComplexShardingStrategyConfiguration(TABLE_SHARDING_COLUMN, new TableByCodeAndMonthShardingAlgorithm()));
        return ruleConfiguration;
    }

    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",
                "jdbc:mysql://localhost:3306/sharding?userUnicode=ture&characterEncoding=utf8&serverTimezone=GMT%2B8",
                "root",
                "123456"));
        shardingDataSourceMap.put("2020", DataSourceUtil.createDataSource("com.mysql.cj.jdbc.Driver",
                "jdbc:mysql://localhost:3306/2020_insurance_order?userUnicode=ture&characterEncoding=utf8&serverTimezone=GMT%2B8",
                "root",
                "123456"));
        shardingDataSourceMap.put("2021", DataSourceUtil.createDataSource("com.mysql.cj.jdbc.Driver",
                "jdbc:mysql://localhost:3306/2021_insurance_order?userUnicode=ture&characterEncoding=utf8&serverTimezone=GMT%2B8",
                "root",
                "123456"));
        shardingDataSourceMap.put("2022", DataSourceUtil.createDataSource("com.mysql.cj.jdbc.Driver",
                "jdbc:mysql://localhost:3306/2022_insurance_order?userUnicode=ture&characterEncoding=utf8&serverTimezone=GMT%2B8",
                "root",
                "123456"));
        return shardingDataSourceMap;
    }

}

DatabaseByYearShardingAlgorithm，RangeDatabaseShardingAlgorithm为按年分库的分片算法实现类，这里采用的是shardingsphere的StandardShardingStrategyConfiguration标准分片策略

PreciseShardingAlgorithm精确分片算法

**
 * @desc 按年分库精确分片算法
 */
public class DatabaseByYearShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        Date createTime = preciseShardingValue.getValue();
        int year = DateUtil.year(createTime);
        for (String database : collection) {
            if (Objects.equals(database,String.valueOf(year))) {
                return database;
            }
        }
        throw new UnsupportedOperationException();
    }
}

RangeShardingAlgorithm范围分片算法


/**
 * @Description: 数据库范围分片算法
 */
public class RangeDatabaseShardingAlgorithm implements RangeShardingAlgorithm<Date> {

    /**
     * 范围分片算法
     *
     * @param availableTargetNames 所有配置的库列表
     * @param rangeShardingValue   分片值，也就是createTime的值，范围分片算法必须提供开始时间和结束时间
     * @return 所匹配库的结果
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> rangeShardingValue) {
        final ArrayList<String> result = new ArrayList<>();
        Range<Date> range = rangeShardingValue.getValueRange();
        Date startTime = range.lowerEndpoint();
        Date endTime = range.upperEndpoint();

        // 起始年和结束年
        int startYear = DateUtil.year(startTime);
        int endYear = DateUtil.year(endTime);

        Collection<String> databaseList =  startYear == endYear ? theSameYear(String.valueOf(startYear), availableTargetNames, result) : differentYear(startYear, endYear, availableTargetNames, result);
        return databaseList;
    }

    // 起始年跟结束年在同一年，只需要查一个数据库
    private Collection<String> theSameYear(String startTime, Collection<String> availableTargetNames, ArrayList<String> result) {
        for (String availableTargetName : availableTargetNames) {
            if (availableTargetName.endsWith(startTime)) {
                result.add(availableTargetName);
            }
        }
        return result;
    }

    // 跨年，查多个数据库
    private Collection<String> differentYear(int startYear, int endYear, Collection<String> availableTargetNames, ArrayList<String> result) {
        for (String availableTargetName : availableTargetNames) {
            for (int i = startYear; i <= endYear; i++) {
                if (availableTargetName.endsWith(String.valueOf(i))) {
                    result.add(availableTargetName);
                }
            }
        }
        return result;
    }
}

TableByCodeAndMonthShardingAlgorithm是通过insurCompanyCode和createTime两个字段的复合分片键进行分表的分片算法实现类，这里采用的是shardingsphere的ComplexShardingStrategyConfiguration复合分片算法策略

ComplexKeysShardingAlgorithm复合分片算法


public class TableByCodeAndMonthShardingAlgorithm implements ComplexKeysShardingAlgorithm {

    @Override
    public Collection<String> doSharding(Collection availableTargetNames, ComplexKeysShardingValue complexKeysShardingValue) {
        Map<String, Collection> columnNameAndShardingValuesMap = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
        ArrayList<String> monthList = new ArrayList<>();
        ArrayList<String> codeList = new ArrayList<>();
        ArrayList<String> columnNameAndShardingList = new ArrayList<>();

        ArrayList<String> tables = new ArrayList<>();

        //精确分片
        if (CollectionUtil.isNotEmpty(columnNameAndShardingValuesMap)) {
            Collection<Date> dateCollection = (Collection) columnNameAndShardingValuesMap.get("createtime");
            if (CollectionUtil.isEmpty(dateCollection)) {
                dateCollection = (Collection) columnNameAndShardingValuesMap.get("createTime");
            }
            if (CollectionUtil.isNotEmpty(dateCollection)) {
                for (Date date : dateCollection) {
                    monthList.add(DateUtil.year(date) + "_" + (DateUtil.month(date) + 1));
                }
            }

            Collection<String> insurCompanyCodeList = (Collection) columnNameAndShardingValuesMap.get("insurcompanycode");
            if (CollectionUtil.isEmpty(insurCompanyCodeList)) {
                insurCompanyCodeList = (Collection) columnNameAndShardingValuesMap.get("insurCompanyCode");
            }
            //再通过业务code过滤
            if (CollectionUtil.isNotEmpty(insurCompanyCodeList)) {
                for (String code : insurCompanyCodeList) {
                    codeList.add(code);
                }
            }

            if (CollectionUtil.isNotEmpty(codeList) && CollectionUtil.isNotEmpty(monthList)) {
                for (String code : codeList) {
                    for (String month : monthList) {
                        columnNameAndShardingList.add(code + "_" + month);
                    }
                }
            } else if (CollectionUtil.isNotEmpty(codeList)) {

                for (String code : codeList) {
                    columnNameAndShardingList.add(code);
                }
            } else if (CollectionUtil.isNotEmpty(monthList)) {
                for (String month : monthList) {
                    columnNameAndShardingList.add(month);
                }
            } else {
                logger.error("未按分片键查询,不支持此类操作");
                throw new UnsupportedOperationException();
            }

            if (CollectionUtil.isNotEmpty(columnNameAndShardingList)) {
                for (Object tableName : availableTargetNames) {
                    if (tableName == null) {
                        continue;
                    }
                    for (String target : columnNameAndShardingList) {
                        String lowerCase = target.toLowerCase();
                        if (tableName.toString().contains(lowerCase)) {
                            tables.add(tableName.toString());
                            break;
                        }
                    }
                }
            }
        }

        Map<String, Range<Date>> columnNameAndRangeValuesMap = complexKeysShardingValue.getColumnNameAndRangeValuesMap();
        if (CollectionUtil.isNotEmpty(columnNameAndRangeValuesMap)) {
            Range<Date> dateRange = columnNameAndRangeValuesMap.get("createTime");
            Date start = dateRange.lowerEndpoint();
            Date end = dateRange.upperEndpoint();
            int startYear = DateUtil.year(start);
            int endYear = DateUtil.year(end);

            int startMonth = DateUtil.month(start) + 1;
            int endMonth = DateUtil.month(end) + 1;

            if (startYear > endYear) {
                throw new UnsupportedOperationException();
            }
            List<String> finalTables = null;
            //通过精确分片过滤的数据库表集合不为空
            if (CollectionUtil.isNotEmpty(tables)) {
                finalTables = getResults(tables, startYear, endYear, startMonth, endMonth);
            } else {
                for (Object targetName : availableTargetNames) {
                    if (targetName == null) {
                        continue;
                    }
                    tables.add(targetName.toString());
                }
                //无精确分片过滤的表集合
                finalTables = getResults(tables, startYear, endYear, startMonth, endMonth);
            }
            return finalTables;
        }
        return tables;
    }

    private List<String> getResults(ArrayList<String> tables, int startYear, int endYear, int startMonth, int endMonth) {
        List<String> finalTables = new ArrayList<>();
        if (startYear == endYear) {
            //同年
            for (String tableName : tables) {
                for (int i = startMonth; i <= endMonth; i++) {
                    if (tableName.endsWith("_" + i)) {
                        finalTables.add(tableName);
                        break;
                    }
                }
            }
            return finalTables;
        } else if ((endYear - startYear) == 1) {
            //跨一年
            String tableNameOne = tables.get(0);
            if (tableNameOne.contains(startYear + "")) {
                for (String tableName : tables) {
                    for (int i = startMonth; i <= 12; i++) {
                        if (tableName.endsWith("_" + i)) {
                            finalTables.add(tableName);
                            break;
                        }
                    }
                }
                return finalTables;
            } else if (tableNameOne.contains(endYear + "")) {
                for (String tableName : tables) {
                    for (int i = 1; i <= endMonth; i++) {
                        if (tableName.endsWith("_" + i)) {
                            finalTables.add(tableName);
                            break;
                        }
                    }
                }
                return finalTables;
            }
        } else {
            //跨两年及以上
            logger.error("跨两年及以上的多数据库查询，影响性能，暂不支持此操作");
            throw new UnsupportedOperationException();
        }
        return null;
    }
}

最终效果：

 

由于shardingsphere不能自动数据库和表，在使用前，必须先创建好数据库，然后写个测试类，写入建表脚本，这里就可以根据你的规则创建好相应的物理表了，然后新增一些测试数据，可以看到数据会按照你相应的规则插入到指定的物理表上啦，这样按照不通维度的分库分表，能减少落到每张表的数据大大减少，查询的时候一定要注意，查询条件一定要带上你分表的分片键，这样才能路由到指定的表，缩小查询范围，提高查询效率。

更多的使用技能请移步官网：概览 :: ShardingSphere

如有不妥，望各路大神批评指正。

ITwenhao
关注
  • 5
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
搞懂分库分表 12 种分片算法
wjianwei666的专栏
03-26 155
本文中我们讲解了ShardingSphere-jdbc所支持的12种分片算法，每种算法都具有独特的特点。在实际应用中，需要结合具体的业务场景来灵活选择和应用适合的分片算法。
shardingsphere按年月分表
qq_39543482的博客
08-12 2505
第一步：引入依赖文件 <!--当当网分库分表--> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.0.0-RC1&l
sharding sphere 4.0.0-RC1版本 按年分表实战
tzxylao的博客
01-07 2568
1. sharding sphere 4.0.0-RC1版本 按年分表实战 1.1. 需求 需要对日志表进行按时间划分表，由于用于后台系统，日志量预估不会太大，因此按年划分表 经过我不断的查阅sharding sphere资料和实践，我最后还是决定先建表，再把actual-data-nodes表结点给定下来，为什么这么说？ 我纠结的是到底要不要动态创建表，若想要不自己手动每隔几年维护表，我们当然希...
ShardingSphere5.2.0按月分表，自动建表
最新发布
竹林幽深
07-27 154
现在的缺点：如果查询时不指定时间段，默认是查询当前月份对应的表。继续研究，争取能找出不设置时间段时默认全部表联合查询。有知道的给指点一下。2024年06月02日 21:56。下面的代码是查询条件。
使用Shardingsphere根据时间完成分表
weixin_43714208的博客
05-07 608
只需要注意上面的precise-algorithm-class-name和range-algorithm-class-name两个策略，主要是对分表键的等值和范围查询。增加mybatisplus和shardingsphere配置。
【ShardingSphere】springboot整合shardingjdbc按照年月分库分表
xlecho的博客
06-10 3376
上文中我们实现了SpringBoot整合shardingjdbc+mybatis，其中我们分库分表的时候选择的是根据id取模来进行分库分表。这种分库分表的形式是比较经典的方式，但是也有很多其他的分表的方式，比如按照年月日分库分表。本文将详细讲述按照年月日分表 环境准备 在商业开发当中，有一些需求会将几年的数据放到一个库里面，但是当前库里面会建很多表，表明是按照年份和月份来建。我们这里为了演示技术，将会按照一个年份建一个库，一个年份对应的库里面按照日期建立365个表。 我这里建立了两个库 每个库对应的.
shardingsphere 分表 自用水平单库分表 按月分表
admin_zjy的博客
04-01 746
项目需求表数据太多，需要按月建表存数据，通过网络所以简单的做了个单库水平分表，要求不多一般情况下够用了。
sharding-jdbc-springboot简单样例
fangye1的博客
10-17 971
1，sharding-jdbc 基本概念 逻辑表 ：水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例：订单数据根据主键尾数拆分为 10 张表，分别是 t_order_0 到 t_order_9，他们的逻辑表名为 t_order。 真实表：数据库中真实存在的物理表。即上个示例中的 t_order_0 到 t_order_9。 数据节点：数据分片的最小单元。由数据源名称和数据表组成，例：ds_0.t_order_0。 绑定表：分片规则一致的主表和子表。例如：t_order 表和 t_order_item
Sharding-JDBC（六）5.1.0版本，实现按月分表、自动建表、自动刷新节点
热门推荐
ACGkaka的博客
01-27 1万+
5.1.0版本，实现按月分表、自动建表、自动刷新节点
springboot 使用shardingsphere-jdbc4.1.1按年月分表
W915120623的博客
12-07 1073
访问记录表使用yyyyMM做后缀分表，表会每月自动创建分表规则配置如下文档：https://shardingsphere.apache.org/document/4.1.1/cn/overview/
sharding-jdbc按月分表样例
10-25
sharind-jdbc样例，当中包含了按月动态分表，一个比较简单的demo，大家可以结合自己的需求进行修改，以及可以看下实现思路，仅供参考！
ShardingJDBC5.1.1按月分库分表、读写分离、自动创表完整demo
06-17
《使用ShardingJDBC5.1.1实现按月分库分表、读写分离与自动创表的全面实战》 在现代企业级应用中，数据量的快速增长使得数据库的性能优化成为至关重要的环节。ShardingJDBC作为一款轻量级的Java框架，能够有效地...
sharding5.0.0分库分表、按年份/年月/区域分支/复合/标准/广播分表
06-16
《深入理解ShardingSphere分库分表策略：5.0.0版本解析》 ShardingSphere，作为一款开源的分布式数据库中间件，以其强大的分库分表能力在IT行业中赢得了广泛的认可。在5.0.0这个版本中，ShardingSphere提供了更为...
分库分表之_只分表不分库
HealerJean梦想博客
04-01 1734
前言 Github：https://github.com/HealerJean 博客：http://blog.healerjean.com 1、开始Demo 1.1、hlj-04-sharding-table.sql drop database if exists ds_0; create database ds_0 character set 'utf8' collate 'utf8_gene...
SpringBoot整合ShardingSphere-JDBC实现分库分表（根据年分库，根据月分表）
m0_46487331的博客
11-11 3560
SpringBoot整合ShardingSphere-JDBC实现分库分表（根据年分库，根据月分表）
sharding sphere 4.0.0-RC1版本 按年分表（自动建表）
tzxylao的博客
01-15 903
1. sharding sphere 4.0.0-RC1版本 按年分表（自动建表） 1.1. 概述 上篇文章留了个坑，sharding sphere本身没有提供自动建表功能，但我想了想，我们可以绕过它本身的设定，它本身的数据分片是通过分片算法实现，如下继承一些接口PreciseShardingAlgorithm、RangeShardingAlgorithm等，在范围查询的时候，原本我们需...
shardingSphereJDBC+dynamic+mysql+sqlserver+mybatis-plus按月分库分表的最强解决方案
weixin_45529487的博客
01-11 2100
本文主要介绍了在进行SQL server进行分库分表的时候遇到的问题
mycat动态按年月分表
09-03
要在Mycat中实现动态按年月分表，你需要进行以下步骤： 1. 首先，下载Mycat并进行安装配置。你可以从官方网站http://www.mycat.org.cn/ 下载最新版本的Mycat安装包。 2. 在Mycat的配置文件中，你需要添加一个创建表的配置语句。通过修改tableNames参数，可以实现按年月动态创建分表。具体的配置语句如下所示： ``` mycat:createTable{ "schemaName":"mycatdb", "shardingTable":{ "createTableSQL":"CREATE TABLE `mycatdb`.`test` (`name` varchar(10),`create_time` varchar(30));", "function":{ "clazz":"io.mycat.router.mycat1xfunction.PartitionByMonth", "properties":{ "beginDate":"2021-01-01 00:00:00", "dateFormat":"yyyy-MM-dd hh:mm:ss", "endDate":"", "columnName":"create_time" }, "ranges":{} }, "partition":{ "schemaNames":"1cloud", "tableNames":"test_$202101-202104", "targetNames":"prototype" } }, "tableName":"test" } ``` 在上述配置中，你需要修改schemaName为你的数据库名称，createTableSQL为你要创建的表的SQL语句，tableNames为按年月动态生成的表名格式，targetNames为最终生成的表名前缀。 3. 配置完成后，启动Mycat服务器。你可以按照中的指导进行启动。 通过以上步骤，你就可以在Mycat中实现动态按年月分表了。每个月的数据将自动分配到对应的表中，方便数据管理和查询。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [MyCat2按月分表操作](https://blog.csdn.net/Swlymbcty/article/details/125048968)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *2* *3* [mycat单数据库按日分表,按月分表](https://blog.csdn.net/qq_42795685/article/details/106768430)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值