【踩坑记录】Sharding-JDBC（4.0.0）之单库分表

目录

背景

实践

依赖

配置

workerId处理

总结

参考

硬广

---

背景

之前由于有分表的需求，使用了sharding-jdbc，版本是3.0.0。前几天有一波高并发的调用，发现sharding-jdbc内部有报错（具体信息如下），也没有查出来具体的原因，盲猜使用升级大法，升到了4.0.0-RC1。变化还是有一些的，来记录一下4.0.0版本的配置。

org.springframework.transaction.TransactionSystemException: Could not roll back JDBC transaction; nested exception is java.sql.SQLException
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:331)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:857)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:834)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:536)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:286)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:671)
	at org.apache.skywalking.apm.agent.core.plugin.interceptor.enhance.InstMethodsInter.intercept(InstMethodsInter.java:93)
	at sun.reflect.GeneratedMethodAccessor270.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest$original$YfzLOBX2(InvocableHandlerMethod.java:133)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest$original$YfzLOBX2$accessor$8ieyR0dz(InvocableHandlerMethod.java)
	at org.springframework.web.method.support.InvocableHandlerMethod$auxiliary$v7TBb9VT.call(Unknown Source)
	at org.apache.skywalking.apm.agent.core.plugin.interceptor.enhance.InstMethodsInter.intercept(InstMethodsInter.java:93)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:854)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:765)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.web.filter.ApplicationContextHeaderFilter.doFilterInternal(ApplicationContextHeaderFilter.java:55)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:111)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:103)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493)
	at org.apache.catalina.core.StandardHostValve.invoke$original$1S3u7wFD(StandardHostValve.java:137)
	at org.apache.catalina.core.StandardHostValve.invoke$original$1S3u7wFD$accessor$7s3H5QK2(StandardHostValve.java)
	at org.apache.catalina.core.StandardHostValve$auxiliary$I2u3g8BO.call(Unknown Source)
	at org.apache.skywalking.apm.agent.core.plugin.interceptor.enhance.InstMethodsInter.intercept(InstMethodsInter.java:93)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:660)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:798)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: null
	at io.shardingsphere.shardingjdbc.jdbc.adapter.executor.ForceExecuteTemplate.throwSQLExceptionIfNecessary(ForceExecuteTemplate.java:56)
	at io.shardingsphere.shardingjdbc.jdbc.adapter.executor.ForceExecuteTemplate.execute(ForceExecuteTemplate.java:49)
	at io.shardingsphere.shardingjdbc.jdbc.adapter.AbstractConnectionAdapter.rollback(AbstractConnectionAdapter.java:197)
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:328)
	... 88 common frames omitted

实践

依赖

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

配置

因为4.0.0上了apache，变化还是有很多的，更多的配置可以参考官网文档

spring:
  shardingsphere:
    datasource:
      names: # 数据源名称（多个使用,分隔）
      ds01: # 数据源
        url: jdbc:mysql://****:3306/***?useSSL=false&autoReconnect=true&autoReconnectForPools=true&characterEncoding=UTF-8&allowMultiQueries=true&failOverReadOnly=false
        username: 
        password: 
        type: org.apache.commons.dbcp.BasicDataSource 
        driver-class-name: com.mysql.jdbc.Driver
        initial-size: 5
        min-idle: 5
        max-active: 100
        max-wait: 10000
        validation-query: SELECT 1 FROM DUAL
        test-on-borrow: true
        test-on-return: false
        test-while-idle: true
        # 检测连接是否存活时间间隔
        time-between-eviction-runs-millis: 60000
        # 连接最小存活时间
        min-evictable-idle-time-millis: 300000
    sharding:
      tables: # 分表配置
        table01:
          # 真实表名
          actual-data-nodes: ds01.table01_$->{2019..2022}_0$->{0..7}
          # 分片键（分表字段）
          table-strategy.complex.sharding-columns: year,hash_code
          # 多分片键要自定义分表逻辑，单分片键可使用inline表达式
          table-strategy.complex.algorithm-class-name: com.yx.config.TableComplexKeysShardingAlgorithmImpl
          # 自增字段
          key-generator.column: id
          # 自增字段使用算法
          key-generator.type: SNOWFLAKE
          # 分布式部署下，使用SNOWFLAKE需要配置worker.id，用来区分不同的工作进程，即不同的机器
          key-generator.props.worker.id: ${workerIdValue}
      # 默认数据源
      default-data-source-name: ds01
    props:
      sql:
        # 打印SQL
        show: true

package com.yx.config;

import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

/**
 * 表分片策略实现
 *
 * @yx8102 2020/5/27
 */
public class TableComplexKeysShardingAlgorithmImpl implements ComplexKeysShardingAlgorithm {

    /**
     * 自定义分片策略
     * @param actualTableNames 实际表名集合
     * @param complexKeysShardingValue 分片键集合
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection actualTableNames, ComplexKeysShardingValue complexKeysShardingValue) {
        // 返回真实表名集合
        List<String> tableNameList = new ArrayList<>();

        // 逻辑表名
        String logicTableName = complexKeysShardingValue.getLogicTableName();

        // 分片键的值
        Collection<Integer> yearValues = (Collection<Integer>) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("year");
        Collection<Integer> hashCodeValues = (Collection<Integer>) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("hash_code");

        // 获取真实表名
        for (Integer year : yearValues) {
            for (Integer hashCode : hashCodeValues) {
                String tableSuffix = year + "_0" + Math.abs(hashCode) % 8;

                for (String tableName : (Collection<String>) actualTableNames) {
                    if (tableName.endsWith(tableSuffix)) {
                        tableNameList.add(tableName);
                    }
                }
            }
        }

        return tableNameList;
    }
}

workerId处理

设置workId的原因，可以参考之前的文章：【踩坑记录】Sharding-JDBC（3.0.0）之分布式主键冲突

因为4.0.0是在配置文件里指定workerId的值，又要不同机器，生成不一样的，所以使用了System.setProperty();通过设置JRE的全局变量，动态的指定配置文件中属性的值。

package com.yst.config;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.context.annotation.Configuration;

/**
 * sharding-jdbc 使用雪花算法生成主键时，workId配置
 *
 * @yx8102 2020/5/27
 */
@Configuration
@Slf4j
public class WorkIdConfig {

    /**
     * 设置workerIdValue的值
     */
    static {

        int workId = 0;
        try {
            workId = getWorkId();
        } catch (Exception e) {
            log.error("生成workId发生异常.", e);
        }

        System.setProperty("workerIdValue", String.valueOf(workId));
    }


    /**
     * 根据机器名称生成workId
     * @return
     */
    private static int getWorkId() {
        String hostAddress = System.getenv("HOSTNAME");
        log.info("============= hostAddress: {} =============", hostAddress);
        int[] ints = StringUtils.toCodePoints(hostAddress);
        int sum = 0;

        for (int b : ints) {
            sum += b;
        }

        int workId = (sum % 32);
        log.info("============== workId：{} =============", workId);
        return workId;
    }
}

总结

终于理解为什么大公司即使是造轮子、抄代码，也要所有中间件都用自己的。配置workerId的时候，发现了一个bug，如果你配置的值是数值类型的，比如123，debug就会发现并没有生效，使用的依然是默认值0，看了源码，要是String类型才行，所以要配置成"123"，我觉得很魔幻。

参考

官方文档

【踩坑记录】Sharding-JDBC（3.0.0）之单库分表

【踩坑记录】Sharding-JDBC（3.0.0）之分布式主键冲突

在springboot中使用${}占位符配置参数

硬广

受到大佬的鼓舞和激励，阿雪也有公众号啦！会对博客中的内容进行整合和优化后发表在公众号上，规划的内容有后端技术、其他有趣的技术（比如PS）、生活相关（比如做饭）、答疑解惑（其实是互相帮助）。

总之，关注我吧，每周都会更新哒，一个不太萌的小萌新程序媛（企图卖萌🐶）orz