Java基于ShardingSphere实现mysql分库分表

举个蚕豆

已于 2023-07-14 11:32:41 修改

阅读量387

点赞数

文章标签： java 开发语言

于 2023-06-13 16:21:00 首次发布

本文链接：https://blog.csdn.net/weixin_61240867/article/details/131189481

版权

在开发过程中，难免会遇到数据量超大的情况，比如订单记录、系统操作记录等。如果放在一张表中，那操作时可谓是一言难尽。

今天和小伙伴们分享一下ShardingSphere来解决这一问题吧！！！

话不多说，上代码

1.注入依赖

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>4.1.0</version>
        </dependency>

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

2.建表，我就拿系统操作记录来举例子，单张表sql：

CREATE TABLE `sys_log` (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号',
  `type` int(1) DEFAULT '1' COMMENT '1操作日志，2错误日志',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `service_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remote_addr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_agent` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `request_uri` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `method` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `params` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `time` bigint(20) DEFAULT NULL COMMENT '操作时长',
  `del_flag` bit(1) DEFAULT b'0',
  `exception` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

但是问题来了，既然需要分表，表肯定是多张，所以要用某个特殊关键字来做区分，由于此表为操作记录，我可用日期作区分，在构建前要评估数据量的大小，如果有必要，可按天来区分，我这里是按月来区分的。

但是又有个问题，一张一张的去建也没毛病，就是有点麻烦，假如按天去用的话，那可真是太痛苦了，所以可以用存储过程来建一个函数。

CREATE DEFINER = `mysql的用户名` @`%` PROCEDURE `sub_sys_log` () BEGIN
	DECLARE
		x INT;
	DECLARE
		y INT;
	
	SET x = 2023;
	
	SET y = 1;
	WHILE
			x <= 2023 DO
		WHILE
				y <= 12 DO
				
				SET @sql_create_table_gpstrail = concat( 'CREATE TABLE IF NOT EXISTS sys_log_', lPAD( x, 4, '0' ), lPAD( y, 2, '0' ), "(
					`id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号',
					`type` int(1) NULL DEFAULT 1 COMMENT '1操作日志，2错误日志',
					`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
					`service_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
					`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
					`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
					`update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
					`remote_addr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
					`user_agent` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
					`request_uri` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
					`method` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
					`params` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
					`time` bigint(20) NULL DEFAULT NULL COMMENT '操作时长',
					`del_flag` bit(1) NULL DEFAULT b'0',
					`exception` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
					PRIMARY KEY (`id`) USING BTREE
				) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic" );
			PREPARE sql_create_table_gpstrail 
			FROM
				@sql_create_table_gpstrail;
			EXECUTE sql_create_table_gpstrail;
			
			SET y = y + 1;
			
		END WHILE;
		
		SET x = x + 1;
		
		SET y = 1;
		
	END WHILE;

END

其中需要注意的是DEFINER 是你的用户名，需要此用户有建函数的权限才可， PROCEDURE 就是你要建立的函数名。lPAD()里有三个参数，第一是上边定义的常量，第二是拼接长度，第三是需要补的字段，比如lPAD(y,2,'0'),当y小于10时，其长度是小于2的，所以会在左边补个0，比如01,02等。

存储过程写好后运行会得到一个函数，在运行函数便可得到你所需要的N张表啦！！！

3.创建实体类、service、mapper等，这里没有特殊之处，我就不一一列举了

4.配置文件

spring:
  #sharding-jdbc
  shardingsphere:
    datasource:
      names: master,history
      # 主库
      master:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://url:端口/其他库名?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        username: 账号
        password: 密码
      # 历史数据库
      history:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://url:端口/历史库名?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        username: 账号
        password: 密码
    props:
      sql:
        show: true
    # 分片配置
    sharding:
      # 分片读写分离配置
      master-slave-rules:
        # 默认主从
        ds_master:
          master-data-source-name: master
          #如果有从库，需要在datasource里面配置连接，我这没有建立从库，所以都是master
          slave-data-source-names: master
        # 分片数据源读写分离配置（历史数据源）
        ds_history:
          master-data-source-name: history
          slave-data-source-names: history
      # 未配置分片规则的表将通过默认数据源定位
      default-data-source-name: ds_master
      binding-tables: sys_log
      broadcast-tables: t_address
      tables:
        #表名
        sys_log:
          #花括号为拼接的关键字
          actual-data-nodes: ds_history.sys_log_$->{2023}$->{['01','02','03','04','05','06','07','08','09','10','11','12']}
          key-generator:
            column: id
            type: UUID
          table-strategy:
            standard:
              #精准分片规则
              precise-algorithm-class-name: com.jtg.common.config.MonthShardingAlgorithm
              #范围分片规则
              range-algorithm-class-name: com.jtg.common.config.MonthRangeShardingAlgorithm
              #监听某个字段来执行上述规则，这里我用的创建时间
              sharding-column: create_time

5.编写规则

/**
 * 月精确分片
 *
 * @author guowj
 * @date 2023-05-10 19:11
 */
public class MonthShardingAlgorithm implements PreciseShardingAlgorithm<String> {

    /**
     * 设置分片
     *
     * @param tableNames    数据表
     * @param shardingValue 分片列信息
     * @return
     */
    @SneakyThrows
    @Override
    public String doSharding(Collection<String> tableNames, PreciseShardingValue<String> shardingValue) {
        String tableName = shardingValue.getLogicTableName();
        String key = getDate(shardingValue.getValue(), 6);
        return tableName.concat("_").concat(key);
    }

    /**
     * 得到日期数字
     *
     * @param date 字符串日期
     * @param len  长度
     * @return 202008
     * @throws ParseException
     */
    public String getDate(String date, int len) throws ParseException {
        String number = date.replaceAll("\\D", "");
        return number.substring(0, len);
    }

/**
 * 月范围分片
 *
 * @author guowj
 * @date 2023-05-10 19:30
 */
public class MonthRangeShardingAlgorithm implements RangeShardingAlgorithm<String> {

    /**
     * 设置分片
     *
     * @param collection
     * @param rangeShardingValue
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        Collection<String> result = new LinkedHashSet<>();
        DateFormat sdf = new SimpleDateFormat("yyyyMM");
        //日期
        Range<String> ranges = rangeShardingValue.getValueRange();
        Date startTime = dateFormat(ranges.lowerEndpoint());
        Date endTime = dateFormat(ranges.upperEndpoint());

        Calendar cal = Calendar.getInstance();
        while (startTime.getTime() <= endTime.getTime()) {
            String value = sdf.format(startTime);
            for (String each : collection) {
                if (each.endsWith(value)) {
                    result.add(each);
                    break;
                }
            }
            cal.setTime(startTime);
            cal.add(Calendar.MONTH, 1);
            startTime = cal.getTime();
        }
        if (result.size() == 0) {
            result = collection;
        }
        return result;
    }

    /**
     * 日期转换
     *
     * @param date
     * @return
     */
    public Date dateFormat(String date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            return sdf.parse(date);
        } catch (ParseException e) {
            return null;
        }
    }
}

6.编写自动注入配置类

@EnableCaching
@ComponentScan("分片规则配目录")
@MapperScan("mapper目录")
@EnableAutoConfiguration(exclude = {DruidDataSourceAutoConfigure.class, JtaAutoConfiguration.class})
public class DataServerAutoConfiguration {
}

7.编写自定义启动注解

@Documented
@Inherited
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Import({DataServerAutoConfiguration.class})
public @interface EnableShardingJdbc {
}

8.在启动类上加上此注解就可以啦

最后需要注意的是，在操作数据库时，配置文件中的sharding-column所对应的字段为必填项，否则是无法执行分片规则的。

举个蚕豆

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Java基于ShardingSphere实现mysql分库分表

lPAD()里有三个参数，第一是上边定义的常量，第二是拼接长度，第三是需要补的字段，比如lPAD(y,2,'0'),当y小于10时，其长度是小于2的，所以会在左边补个0，比如01,02等。但是问题来了，既然需要分表，表肯定是多张，所以要用某个特殊关键字来做区分，由于此表为操作记录，我可用日期作区分，在构建前要评估数据量的大小，如果有必要，可按天来区分，我这里是按月来区分的。但是又有个问题，一张一张的去建也没毛病，就是有点麻烦，假如按天去用的话，那可真是太痛苦了，所以可以用存储过程来建一个函数。
复制链接

扫一扫