首页 > 其他分享 >shardingsphere-jdbc 水平分表学习记录

shardingsphere-jdbc 水平分表学习记录

时间：2022-11-05 16:24:37浏览次数：78  
标签：jdbc rules shardingsphere sharding user spring 分表 order

放在自己博客里搬过来一份~

前司使用的是自己魔改的TDDL,在家时间比较多就尝试学一些业内比较常用的中间件.

这里记录一下学习中遇到的一些问题.

环境

设置的比较简单(太懒了就测试了几个表), 两个分库, 各有几张分表.
sharding-test_0

  • order_0 (order_id)
  • order_1
  • order_item_0 (order_id)
  • order_item_1
  • user_0 (user_id)
  • user_1
  • address (用来做broadcast表)
CREATE TABLE `order_0` (
  `order_id` int NOT NULL,
  `user_id` int NOT NULL,
  `address_id` int NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `order_item_0` (
  `order_item_id` bigint NOT NULL,
  `order_id` int NOT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `user_0` (
  `user_id` bigint NOT NULL,
  `user_name` varchar(45) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `address` (
  `address_id` int NOT NULL,
  `address_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

spring-boot-starter使用2.7.5
shardingsphere-jdbc-core-spring-boot-starter使用5.2.1

测试的时候最好直接跑,不要用单测,会被自动回滚掉.
可以定义多个ApplicationRunner来测试.

@Component
public class MyApplicationRunner implements ApplicationRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void run(final ApplicationArguments args) throws Exception {

用JdbcTemplate方便点也省去了依赖更多的东西.
返回自增key的代码样例:

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement("insert into user(`user_name`) values (?)",
                    Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, "cc2");
            return ps;
        }, keyHolder);
        System.out.println("key:" + keyHolder.getKey());

配置

本来使用yaml的配置,但看了一下有点太乱,先用properties的代替.
配的时候有比较多的问题,几个配置错误会导致没法启动或者测试时报错,但配完之后感觉整体逻辑还是比较清晰的.

spring.shardingsphere.mode.type=Standalone
spring.shardingsphere.props.sql-show=true

# logic datasource
spring.shardingsphere.datasource.names=shard00,shard01

# real datasource
spring.shardingsphere.datasource.shard00.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.shard00.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.shard00.jdbc-url=jdbc:mysql://localhost:3306/sharding_test_0
spring.shardingsphere.datasource.shard00.username=root
spring.shardingsphere.datasource.shard00.password=

spring.shardingsphere.datasource.shard01.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.shard01.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.shard01.jdbc-url=jdbc:mysql://localhost:3306/sharding_test_1
spring.shardingsphere.datasource.shard01.username=root
spring.shardingsphere.datasource.shard01.password=

spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=\
  shard0$->{0..1}.user_$->{0..1}
spring.shardingsphere.rules.sharding.tables.order_item.actual-data-nodes=\
  shard0$->{0..1}.order_item_$->{0..1}
spring.shardingsphere.rules.sharding.tables.order.actual-data-nodes=\
  shard0$->{0..1}.order_$->{0..1}
spring.shardingsphere.rules.sharding.tables.address.actual-data-nodes=\
  shard0$->{0..1}.address


# database strategy and table strategy
spring.shardingsphere.rules.sharding.tables.user.database-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.user.database-strategy.standard.sharding-algorithm-name=alg_db_user
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_user.type=MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_user.props.sharding-count=2

spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-algorithm-name=alg_table_user
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_user.type=HASH_MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_user.props.sharding-count=2

spring.shardingsphere.rules.sharding.tables.order.database-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.order.database-strategy.standard.sharding-algorithm-name=alg_db_order
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_order.type=MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_order.props.sharding-count=2

spring.shardingsphere.rules.sharding.tables.order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.order.table-strategy.standard.sharding-algorithm-name=alg_table_order
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_order.type=HASH_MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_order.props.sharding-count=2
# order_item and order use the same strategy
spring.shardingsphere.rules.sharding.tables.order_item.database-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.order_item.database-strategy.standard.sharding-algorithm-name=alg_db_order

spring.shardingsphere.rules.sharding.tables.order_item.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.order_item.table-strategy.standard.sharding-algorithm-name=alg_table_order

# key generator
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.column=user_id
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.key-generator-name=alg_snowflake

spring.shardingsphere.rules.sharding.tables.order.key-generate-strategy.column=order_id
spring.shardingsphere.rules.sharding.tables.order.key-generate-strategy.key-generator-name=alg_snowflake

spring.shardingsphere.rules.sharding.tables.order_item.key-generate-strategy.column=order_item_id
spring.shardingsphere.rules.sharding.tables.order_item.key-generate-strategy.key-generator-name=alg_snowflake

# key generator algorithm
spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.alg_uuid.type=UUID

# binding table and broadcast table
spring.shardingsphere.rules.sharding.binding-tables[0]=order,order_item
spring.shardingsphere.rules.sharding.broadcast-tables=address

也就分为逻辑datasource定义, 真实的datasource定义.
对于每个逻辑表,定义分库分表规则,如果需要生成分布式key,定义key的生成算法.
分别对应spring.shardingsphere.datasource.前缀和spring.shardingsphere.rules.sharding前缀.

对于SNOWFLAKE要注意数据库的字段类型要bigint,int不够放.

启动报错

***************************
APPLICATION FAILED TO START
***************************

Description:

An attempt was made to call a method that does not exist. The attempt was made from the following location:

    org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1.<init>(ShardingSphereYamlConstructor.java:44)

The following method did not exist:

    'void org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1.setCodePointLimit(int)'

The calling methods class, org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1, was loaded from the following location:

    jar:file:/.m2/repository/org/apache/shardingsphere/shardingsphere-infra-util/5.2.1/shardingsphere-infra-util-5.2.1.jar!/org/apache/shardingsphere/infra/util/yaml/constructor/ShardingSphereYamlConstructor$1.class

The called methods class, org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1, is available from the following locations:

    jar:file:/.m2/repository/org/apache/shardingsphere/shardingsphere-infra-util/5.2.1/shardingsphere-infra-util-5.2.1.jar!/org/apache/shardingsphere/infra/util/yaml/constructor/ShardingSphereYamlConstructor$1.class

The called methods class hierarchy was loaded from the following locations:

    null: file:/.m2/repository/org/apache/shardingsphere/shardingsphere-infra-util/5.2.1/shardingsphere-infra-util-5.2.1.jar
    org.yaml.snakeyaml.LoaderOptions: file:/.m2/repository/org/yaml/snakeyaml/1.30/snakeyaml-1.30.jar


Action:

Correct the classpath of your application so that it contains a single, compatible version of org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1

很明显的一个以来冲突问题, 主要是这行代码:

    public ShardingSphereYamlConstructor(final Class<?> rootClass) {
        super(rootClass, new LoaderOptions() {
            
            {
                setCodePointLimit(Integer.MAX_VALUE);
            }
        });
        ShardingSphereYamlConstructFactory.getInstances().forEach(each -> typeConstructs.put(each.getType(), each));
        ShardingSphereYamlShortcutsFactory.getAllYamlShortcuts().forEach((key, value) -> addTypeDescription(new TypeDescription(value, key)));
        this.rootClass = rootClass;
    }

snakeyaml的版本冲突,使用的版本中LoaderOptions没有setCodePointLimit这个方法.
使用的springboot的依赖的是1.30.0,显式依赖1.33.0即可.

        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>1.33</version>
        </dependency>

配置错误导致的报错

这类报错种类比较多
比如

  • DataNodesMissedWithShardingTableException
  • ShardingRuleNotFoundException
  • InconsistentShardingTableMetaDataException

等等, 启动就会失败, 因为是读取了配置解析异常.

这种就要看看对应的错误和配置.

不过有点奇怪的是一些错误没有打出详细的报错信息.比如:

Caused by: org.apache.shardingsphere.sharding.exception.metadata.DataNodesMissedWithShardingTableException: null
	at org.apache.shardingsphere.sharding.rule.TableRule.lambda$checkRule$4(TableRule.java:246) ~[shardingsphere-sharding-core-5.2.1.jar:5.2.1]
	at org.apache.shardingsphere.infra.util.exception.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41) ~[shardingsphere-infra-util-5.2.1.jar:5.2.1]
	at org.apache.shardingsphere.sharding.rule.TableRule.checkRule(TableRule.java:245) ~[shardingsphere-sharding-core-5.2.1.jar:5.2.1]

看了下是基类没调用super,导致message没有值.看了下这个已经在master分支修好了:

    public ShardingSphereSQLException(final SQLState sqlState, final int typeOffset, final int errorCode, final String reason, final Object... messageArguments) {
        this(sqlState.getValue(), typeOffset, errorCode, reason, messageArguments);
    }
    
    public ShardingSphereSQLException(final String sqlState, final int typeOffset, final int errorCode, final String reason, final Object... messageArguments) {
        this.sqlState = sqlState;
        vendorCode = typeOffset * 10000 + errorCode;
        this.reason = null == reason ? null : String.format(reason, messageArguments);
        // missing super(resaon) here
    }

数据库自动生成的key不能作为route key

但是分布式生成的key可以, 这个在FAQ里有, 有这个错误是刚开始配分布式key的时候配错了.

原文:

[分片] ShardingSphere 除了支持自带的分布式自增主键之外，还能否支持原生的自增主键？
回答：

是的，可以支持。但原生自增主键有使用限制，即不能将原生自增主键同时作为分片键使用。 由于 ShardingSphere 并不知晓数据库的表结构，而原生自增主键是不包含在原始 SQL 中内的，因此 ShardingSphere 无法将该字段解析为分片字段。如自增主键非分片键，则无需关注，可正常返回；若自增主键同时作为分片键使用，ShardingSphere 无法解析其分片值，导致 SQL 路由至多张表，从而影响应用的正确性。 而原生自增主键返回的前提条件是 INSERT SQL 必须最终路由至一张表，因此，面对返回多表的 INSERT SQL，自增主键则会返回零。

分表分库的规则思考

最开始的时候对于分库分表无脑两个都用了MOD, 但因为分区数和分表数是一样的(都是2).
所以mod 2数据的分布也是一样的,这就导致了sharding_test_0的user_1是没有数据的,sharding_test_1的user_0也是没有数据的.
分表了个寂寞.

不只是一样,其实只要分库和分表数最大公约数不为1如果无脑MOD都会有倾斜的问题.
可以代码验证下:

        int dbShard = 6;
        int tableShard = 32;

        Map<Tuple2<Integer, Integer>, Integer> count = new TreeMap<>();

        for (int i = 0; i < dbShard; i++) {
            for (int j = 0; j < tableShard; j++) {
                count.put(Tuple.tuple(i, j), 0);
            }
        }

        for (int i = 0; i < 100000; i++) {
            count.computeIfPresent(Tuple.tuple(i % dbShard, i % tableShard), (k, v) -> v + 1);
        }
        count.forEach((k,v) -> {
            System.out.println(k + ":" + v);
        });

因为前司我经手的项目用的都是分表,还没有到分库,没有意识到这个问题,也算是一点点小经验吧,要考虑下分库分表的规则组合会不会导致数据倾斜.

其他还有些实践中的问题,当时没有记录把配置整对之后也不知道怎么复现了.
不得不说shardingsphere-jdbc的易用性是非常高了,通俗易懂.

参考

shardingsphere官网: https://shardingsphere.apache.org
shardingsphere-jdbc配置: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/yaml-config/
shardingsphere FAQ: https://shardingsphere.apache.org/document/current/cn/faq/
How to get generated ID after I inserted into a new data record in database using Spring JDBCTemplate?

github page的博客原文:https://bingowith.me/2022/11/05/shardingsphere-jdbc-learn-note/

标签：jdbc,rules,shardingsphere,sharding,user,spring,分表,order
From： https://www.cnblogs.com/fairjm/p/16860466.html

相关文章

  • JDBC的对象说明
    DriverManager类讲解Jdbc程序中的DriverManager用于加载驱动，并创建与数据库的链接，这个API的常用方法：DriverManager.registerDriver(newDriver())DriverManager.getC......
  • JDBC介绍及第一个JDBC程序测试
    一、JDBC介绍SUN公司为了简化、统一对数据库的操作，定义了一套Java操作数据库的规范（接口），称之为JDBC。这套接口由数据库厂商去实现，这样，开发人员只需要学习jdbc接口，并通过jd......
  • 学习笔记——base标签、加密方式、JDBC、将java中添加的数据增加到数据库中
    2022-11-03一、base标签1、作用：用于添加web项目的前缀。2、放置位置：放置在head标签内部，一般放在首行。3、使用方式：<basehref="/项目名称/">，在html网页中的其他（例如：图......
  • 使用JdbcTemplate操作数据库（增删改查）。具体代码+讲解 【上篇】
    1、什么是JdbcTemplateSpring框架对JDBC进行封装，使用JdbcTemplate方便实现对数据库操作2、前提准备2.1在项目中引入对应的jar包官网下载jar包、然后加入的项目......
  • mysql10--MyCat实现分库分表
    1分库分表介绍#数据切分(sharding)的两种模式-垂直(纵向)拆分：垂直分库：按照不同的表（或者Schema）来切分到不同的数据库（主机）之上垂直分表：将一个表按照字段......
  • 11-SSM_Spring_整合JDBC
    目录​​四，Spring整合JDBC​​​​1，使用spring-jdbc操作数据库​​​​1.1简单测试JdbcTemplate​​​​1.2  通过spring配置的方式实现数据源配置​​​​1.3查询​​......
  • SqlServer 主从复制/读写分离及分表分库
    十年河东，十年河西，莫欺少年穷学无止境，精益求精参考：https://furion.baiqian.ltd/docs/dbcontext-read-writehttps://www.cnblogs.com/atomy/p/15356584.htmlhttps://www......
  • spring jdbcTemplate 事务，各种诡异，包你醍醐灌顶！
    前言项目框架主要是spring，持久层框架没有用mybtis，用的是spring的jdbc；业务需求：给应用添加领域(一个领域包含多个应用，一个应用可能属于多个领域，一般而言一个应用只属......
  • arrow flight-sql-jdbc-driver 提供了
    就是最近apachearrow提供了flightsqljdbcdriver，但是目前还没有发布，从功能上还是比较期待的，内部实现了是基于了calcite的子项目avatica开发的，代码量并不是很大，可以学......
  • day24 JDBC批处理(通用泛型查询方法 & 下划线转驼峰命名法)
    批处理publicstaticIntegeraddBatch(String[]sqls){ init(); try{ //设置关闭自动提交 conn.setAutoCommit(false); Statementstmt=conn.createState......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99