记一次shardingsphere4.x+sqlserver整合心酸史

前言

年前接手的项目上有个需求，就是单表数据量太大了，以前过早的数据都是需要手动清除的，也较麻烦。客户想着能够去做个分表，这样查 询起来也很快，也不用刻意手动去维护数据了。于是乎最先想到了shardingsphere，然后就去官方查看了下文档。感觉用起来挺简单的，想着年后很快就能做好，然后就大胆的请了一周假回家过年了【🐼：是不是有点奢侈了】。结果来了后准备大展手脚操作一下，谁承想，这一搭建各种问题，搞得人是一脸问号，小伙子，还是too young,too simple 啊！！某一时刻我都怀疑自己是不是适合干这行了🤔。

好了好了，说了这么多废话，你们不烦我都开始感觉烦了，让我们切入正题，走起 🤞

tips: 本来这次想用下比较新的版本的，像 5.1.2 、5.2.1、5.3.0 等，但是自己尝试着搭建时发现各种问题，所以作罢。后来在网上搜到别人有用过4.1.1来搭建，心想，别人能搭建完成，自己应该也可搭建成功吧。所以就用4.1.1来搭建了，果然没问题，虽然经过了一番折腾，总算是搭建好了。下图所示是鄙人搭建期间踩坑时测试用的各种版本及分支，有的测试是在之前测试失败的分支上修改的。哎，真不容易啊。

1.shardingsphere官方地址

• 官 网👉 : https://shardingsphere.apache.org
• GitHub👉 : https://github.com/apache/shardingsphere

如下图所示就是官网首页了，有需要的同学可以看看，毕竟官方出品，必属精品。官网文档写的不仅非常全面，而且清晰明了。这个项目原本是当当网内部开发出来的一个分库分表产品，做的也非常好。项目已于2020年4月16日成为 Apache 软件基金会的顶级项目，含金量自然也不用多说了吧。而且有庞大的社区开发人员做维护与升级，可靠性与稳定性自然也差不了。总之，你就放心大胆的用吧，保准没错。

以下是查看历史版本

2.开始整合（踩坑）

2.1 所需pom

		<!--shardingsphere依赖-->
		<dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <!--sqlserver  jdbc 依赖-->
		<dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.2</version>
            <scope>runtime</scope>
        </dependency>
        <!--pagehelper依赖-->
		<dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.0</version>
        </dependency>
        <!--tk.mybatis依赖-->
		<dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.1.5</version>
        </dependency>

2.2 yml配置

pagehelper:
  #指定pagehelper分页方言
  helper-dialect: sqlserver
  #开启分页合理化（比如分页大小小于0时设置为1，等等..）
  reasonable: true
  #是否支持接口参数来传递分页参数
  support-methods-arguments: true

  
spring:
  shardingsphere:
    props:
      # 配置显示sql，这项配置在测试的时候非常有用，建议打开，可以看到路由结果，生产环境可以关掉
      sql.show: true
    datasource:
      # 配置数据源列表，多个数据源使用逗号分割，下边的data1是自己随便起的名称，对应下下一行的data1
      names: data1
      # 如果需要配置多个数据源，就需要实现数据库的分库策略。
      data1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
        username: 用户名
        password: 密码
        jdbc-url: jdbc:sqlserver://数据库ip:端口;DatabaseName=数据库名
    sharding:
      tables:
        #逻辑表名(table_one是你以前数据量大的那个表名，也就是想要分表的那个表名)
        table_one:
          #此处配置实际表信息，查询数据时能够查询多少张表，取决于这里实际配置的表信息（以下这行配置的结果必须存在于你的数据库当中）
          #以下这行配置的结果就是shardingsphere解析时会解析成不同的表名
          #例如：table_one_202301 table_one_202302 ... table_one_201311 table_one_202312 总计是12张表
          actual-data-nodes: data1.table_one_20230$->{1..9},data1.table_one_2023$->{10..12}
          table-strategy:
            standard:
              #你要根据哪个数据字段来解析并分表
              sharding-column: detect_date
              #精准解析类的全路径名
              precise-algorithm-class-name: com.xxx.dashboard.shardingAlgorithm.DatePreciseShardingAlgorithm
              #范围解析类的全路径名
              range-algorithm-class-name: com.xxx.dashboard.shardingAlgorithm.DateRangeShardingAlgorithm

2.3 自定义分片规则

2.3.1 精确分片规则类

public class DatePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
        Date value = shardingValue.getValue();
        // 根据精确值获取路由表
        String actuallyTableName = shardingValue.getLogicTableName() + shardingSuffix(value);
        if (availableTargetNames.contains(actuallyTableName)) {
            return actuallyTableName;
        }
        return null;
    }

    /**
     * sharding 表后缀 _yyyyMM
     */
    private String shardingSuffix(Date shardingValue) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMM");
        return "_" + simpleDateFormat.format(shardingValue);
    }

}

2.3.2 范围分片规则类

public class DateRangeShardingAlgorithm implements RangeShardingAlgorithm<Date> {

    private final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMM");

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> shardingValue) {
        //返回可用实际表名
        List<String> returnActuralTableNames = new ArrayList<>();
        Date rangeLowerDate = shardingValue.getValueRange().lowerEndpoint();
        Date rangeUpperDate = shardingValue.getValueRange().upperEndpoint();
        //判断是否是同一个月的数据
        Integer lowerDateInt = Integer.parseInt(simpleDateFormat.format(rangeLowerDate));
        Integer upperDateInt = Integer.parseInt(simpleDateFormat.format(rangeUpperDate));
        //所属同一个月的数据
        if(rangeLowerDate.before(rangeUpperDate) && lowerDateInt.equals(upperDateInt)){
            returnActuralTableNames.add(shardingValue.getLogicTableName()+shardingSuffix(rangeLowerDate));
        }else{
            while(lowerDateInt <= upperDateInt){
                String actuallyTableName = shardingValue.getLogicTableName() + shardingSuffix(rangeLowerDate);
                if(availableTargetNames.contains(actuallyTableName)){
                    returnActuralTableNames.add(actuallyTableName);
                }
                rangeLowerDate = DateUtil.offsetMonth(rangeLowerDate, 1).toJdkDate();
                lowerDateInt = Integer.parseInt(simpleDateFormat.format(rangeLowerDate));
            }
        }
        return returnActuralTableNames;
    }

    private String shardingSuffix(Date shardingValue) {
        return "_" + simpleDateFormat.format(shardingValue);
    }

注意：你要分表的字段类型一定要和数据库字段类型要一致，例如：我这里的 detect_date字段对应的数据库类型是datetime，实体类字段类型是Date，所以此处两个分片规则类中泛型指定的类型就是Date，包括请求入口如controller层到server层再到mapper层，这个字段所对应的类型都应该是一致的，否则的话会报异常错误。

2.4 数据源使用说明

如果你用的是Java配置类方式使用数据源的话（一般是多个数据源配置），那么你可以采用以下这种方式配置shardingsphere的数据源

@Configuration
@MapperScan(basePackages ="com.xxx.yyy.zzz.dao", sqlSessionFactoryRef = "sudataSqlSessionFactory")
public class SqlServerSuDataShardConfig {

    @Autowired
    @Qualifier(value = "shardingDataSource")
    private DataSource dataSource;

    @Bean(name = "sudataSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setTypeAliasesPackage("com.xxx.yyy.zzz.pojo.entity");
        factoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mappers/shard/*.xml"));
        return factoryBean.getObject();
    }

    @Bean(name = "sudataTransactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "sudataSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("sudataSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

其他普通的数据源配置的话，大体是类似的，只不多创建DataSource的方式不同而已，使用如下方式来生成非分表的DataSource

	@Bean(name = "sulogDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.su-log")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

其中spring.datasource.su-log是你yml文件里面自定义数据源的前缀，类似下边这样

spring:
  datasource:
    su-log:
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      username: 用户名
      password: 密码
      jdbc-url: jdbc:sqlserver://数据库ip:端口;DatabaseName=数据库名

提醒:  如果只想数据库当中某个表进行分表，而其他表不分表，那么你可以将不分表的那些文件单独放到一个文件夹，然后多建个数据源，新建个java配置类进行扫描即可

3. 问题集锦

分页查询问题

先上报错信息，如下图所示

对应的server层接口

PageInfo<DataVo> searchData(SearchDto dto)

service层实现类，忽略某些无关代码

@Override
public PageInfo<DataVo> searchData(SearchDto dto) {
    //***
    //分页
    PageHelper.startPage(dto.getPageNum(), dto.getPageSize());
    List<DataInfo> golddetectDataInfos = dataMapper.searchData(dto);
    PageInfo<DataInfo> list = new PageInfo<>(golddetectDataInfos);
    PageInfo<DataVo> retrunListPage = new PageInfo<>();
    BeanUtils.copyProperties(list, retrunListPage);
    //****
    return retrunListPage;
}

对应的mapper层接口

List<DataInfo> searchData(SearchDto dto);

造成这个问题的原因就是pagehelper分页查询时会先去查一次总数量，默认查询总数的这个方法名称呢，其实就是按照我们的mapper定义的查询接口名称来拼接出来的，看上边的mapper定义的接口名称为searchData，那么此处框架自动生成的查询数量的方法名称其实就是searchData_COUNT，这个其实原本是没有问题的，关键在于我们使用了shardingsphere，由于pagehelper默认生成的sql语句是没有别名的，形如：SELECT count(0) FROM xxx WHERE 你的条件 而此时shardingsphere在解析时由于获取不到别名，就会报错啦。

解决办法 : 自定义查询总数sql语句

像下边这样，mapper层继续定义接口，然后对应的xml文件内也一样，该定义的定义，语句改成查询总数的，别忘了带上查询条件呦，定义好了后，就不用做任何处理了，框架会自动找到这个sql语句并执行的。

Integer searchData_COUNT(SearchDto dto);

<select id="searchData_COUNT" resultType="java.lang.Integer">
        select count(0) as numbers
        from xxxx gold
        where gold.status = 1
        <if test="startTime != null">
            AND gold.detect_date  >=  #{startTime}
        </if>
        <if test="endTime != null ">
            AND gold.detect_date  <![CDATA[ <= ]]> #{endTime}
        </if>
        <if test="productCode != null and productCode.size() > 0 ">
            and  gold.product_code in
            <foreach collection="productCode" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="normsValue != null and normsValue.size() > 0 ">
            and  gold.norms_value in
            <foreach collection="normsValue" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="deviceIds!=null and deviceIds.size > 0 ">
            AND gold.device_code IN
            <foreach collection="deviceIds" separator="," open="(" close=")" item="deviceId">
                #{deviceId}
            </foreach>
        </if>
    </select>

SQL方言问题

先上报错信息，如下图所示

由于客户方是外企，然后所使用的数据库是sqlserver，并且版本是2012版，而我们yml文件中配置的pagehelper的sql方言是sqlserver，乍一看好像没毛病呀，很正确呀。实则不然，具体的我们先看pagehelper官网文档有关的说明， pagehelper github 地址 具体的我贴下图片，如下图所示：

这也就是说当我们连接的数据库版本是2012版的sqlserver，我们需要指定pagehelper方言为sqlserver2012。

解决办法 : 修改pagehelper方言配置信息

pagehelper:
  #指定pagehelper分页方言
  helper-dialect: sqlserver2012
  #开启分页合理化（比如分页大小小于0时设置为1，等等..）
  reasonable: true
  #是否支持接口参数来传递分页参数
  support-methods-arguments: true

某些SQL不支持

类似的sql有 top 、 函数等等，具体可看官方文档说明（官方文档可能也不全，需要自行测试），链接👉shardingsphere 4.1.1版本 sql 说明

1.   SELECT top 1 * FROM xxx 
2.   SELECT max(column)  FROM xxx

友情提醒

1. 在你不知晓的情况下，分表时尽量避免复杂的sql书写。