记录 RuoYi-Vue 项目集成 Sharding-JDBC 遇到的问题与解决办法

前提说明

环境

整体框架为 RuoYi-Vue
数据库 MySQL
Sharding-JDBC 依赖版本 4.1.1

<!-- sharding-jdbc分库分表 -->
<dependency>
	<groupId>org.apache.shardingsphere</groupId>
	<artifactId>sharding-jdbc-core</artifactId>
	<version>4.1.1</version>
</dependency>

具体集成过程及代码 请参考 RuoYi文档 集成sharding-jdbc实现分库分表
我这里仅记录遇到的问题与解决办法

需求背景

有一个维护了4年的项目，数据量有300万，且存储的是长字符串居多，查询速度缓慢，单表容量达到近30G，整个系统都需要围绕这个表开展业务；有多表联查，有数据统计；时间长久，当时开发项目的同事已跳槽，本着能不改结构就不改结构的原则，选择分库分表的方案

---

遇到的问题与解决办法

问题1、LocalDateTime转换报错

问题描述

java 实体类中的字段类型为LocalDateTime，mysql 表中字段类型为datetime，使用Sharding-JDBC之后报错java.time.LocalDateTime cannot be cast to java.sql.Timestamp

解决办法

请参考我的另外一篇文章 shardingsphere+mybatis LocalDateTime转换报错java.time.LocalDateTime cannot be cast to java.sql.Timestamp

问题2、初始化分表数据，数据量过大，造成内存溢出

问题描述

初始化分表数据时，从1张表分别存储到10张表中，数据量过大，容易造成内存溢出

解决方法

使用JDBC流式查询，不会一下子把所有数据获取到内存中，可以有效减少内存占用
将查询到的数据循环存入redis消息队列中
再使用多线程消费redis消息队列中的数据，插入到分表数据源中

代码如下：

/**
 * 初始化分表数据 使用redis mq 处理
 */
@Override
public void initShardingDataByRedisMQ(Boolean isSlave) {
    log.info("开始初始化Slave分表数据");
    // 切换到分表数据源
    DynamicDataSourceContextHolder.setDataSourceType(DataSourceType.SLAVE_SHARDING.name());
    long l = System.currentTimeMillis();
    CompletableFuture arr[] = new CompletableFuture[6];
    arr[0] = CompletableFuture.runAsync(() -> {
        //删除redis队列
        redisCache.deleteObject("sharding_data_old_mq"); 
        //获取旧数据库数据源
        DataSource sourceDataSource = (DataSource) SpringUtils.getBean("oldSlaveDataSource");
        try {
            @Cleanup Connection sourceConnection = sourceDataSource.getConnection();
            @Cleanup Statement statement = sourceConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            statement.setFetchSize(Integer.MIN_VALUE);
            @Cleanup ResultSet resultSet = statement.executeQuery("SELECT * FROM old_data ");
            while (resultSet.next()) {
                // 发送到redis队列
                redisCache.pushObject("sharding_data_old_mq", convertResultSetToObject(resultSet, OldData.class)); // convertResultSetToObject方法是将 resultSet 转成对应的实体类
            }
        } catch (IllegalAccessException e) {
            // 处理异常，例如记录日志或抛出自定义异常
            e.printStackTrace();
        } catch (InstantiationException e) {
            // 处理异常，例如记录日志或抛出自定义异常
            e.printStackTrace();
        } catch (SQLException e) {
            // 处理异常，例如记录日志或抛出自定义异常
            e.printStackTrace();
        }
    });
    for (int i = 1; i < 6; i++) {
        arr[i] = CompletableFuture.runAsync(() -> {
            DynamicDataSourceContextHolder.setDataSourceType(DataSourceType.SLAVE_SHARDING.name());
            // 消费redis 消息队列 如果没获取到数据就 等待10秒，如果还是获取不到就跳出循环，如果 获取到数据就插入到对应的表里
            while (true) {
                // 从redis消息队列获取一个数据
                OldData oldData = redisCache.popObject("sharding_data_old_mq", 10, TimeUnit.SECONDS);
                if (oldData == null) {
                    break;
                } else {
                    oldDataMapper.insertOldDataHaveId(oldData); // 插入数据 不自动生成id
                }
            }
            DynamicDataSourceContextHolder.clearDataSourceType();
        });
    }
    CompletableFuture.allOf(arr).join();
    // 切换回主数据源
    DynamicDataSourceContextHolder.clearDataSourceType();
    log.info("初始化Slave分表数据完成，耗时：{}", System.currentTimeMillis() - l);
}

public static <T> T convertResultSetToObject(ResultSet resultSet, Class<T> clazz) throws SQLException, IllegalAccessException, InstantiationException {
    T obj = clazz.newInstance();
    ResultSetMetaData metaData = resultSet.getMetaData();
    int columnCount = metaData.getColumnCount();

    // 将ResultSet对象的列名和值存到map中，再将map转换为json字符串，最后将json字符串转换为实体类对象
    Map<String, Object> rowData = new HashMap<>();
    for (int i = 1; i <= columnCount; i++) {
        rowData.put(StrUtil.toCamelCase(metaData.getColumnLabel(i)), resultSet.getObject(i));
    }
    String jsonStr = JSONObject.toJSONString(rowData);
    obj = JSONObject.parseObject(jsonStr, clazz);

    return obj;
}

redisCache中消息队列相关方法代码如下：

/**
 * 发送消息 基本的对象，Integer、String、实体类等
 *
 * @param key 消息的键值
 * @param value 消息的值
 */
public <T> void pushObject(final String key, final T value)
{
    redisTemplate.opsForList().leftPush(key, value);
}
/**
 * 获取消息,可以对消息进行监听，没有超过监听事件，则返回消息为null。
 * rightPop：1.key,2.超时时间，3.超时时间类型
 *
 * @param key 缓存键值
 * @return 缓存键值对应的数据
 */
public <T> T popObject(final String key, long timeout, TimeUnit unit)
{
    try {
        ListOperations<String, T> operation = redisTemplate.opsForList();
        return operation.rightPop(key, timeout, unit);
    } catch (RedisCommandTimeoutException e) {
    	// 超时可能是因为队列中被消费完了
        log.warn("redis popObject timeout,key:{}", key);
        return null;
    }
}

问题3、count()查询结果不正确

问题描述

我这边是使用Mybatis作为数据库操作持久化框架，需要分表的表是分成了10个表，在使用count()查询时获取到的结果不像是总数量，只是其中一个表的数量
sql是非常基础的查询条数的sql，例如查询总条数 select count(0) from old_data

解决方法

用多线程分别查询10个分表的条数再累加到一起
（如果有更好的方法请指教）

代码如下：

/**
 * 分表后获取条数
 *
 * @param param
 * @return 条数
 */
@Override
public Integer getOldDataListShardingCount(OldDataListPageParam param) {
    List<Integer> countList = new ArrayList<>();
    CompletableFuture arr[] = new CompletableFuture[10];
    for (int i = 0; i < 10; i++) {
        int index = i;
        arr[i] = CompletableFuture.supplyAsync(() -> {
            Integer count = oldDataMapper.selectOldDataListCount(param, "old_data_" + index);
            if (count == null) {
                count = 0;
            }
            countList.add(count);
            return count;
        });
    }
    CompletableFuture.allOf(arr).join();
    return countList.stream().mapToInt(Integer::intValue).sum();
}

oldDataMapper.selectOldDataListCount 代码如下：

Integer selectOldDataListCount(@Param("param") OldDataListPageParam param, @Param("tableName") String tableName);

oldDataMapper.selectOldDataListCount 对应的xml代码如下：

<select id="selectOldDataListCount" resultType="int">
	select count(0) from ${tableName}
	<where>
		···
	</where>
</select>

问题4、已分表的表需要和其他表联查，并且需要分页，会报错

问题描述

使用Mybatis Plus的IPage或者PageHelper处理分页查询时，都会先查询总条数，单表查询时没有问题，生成的sql例如 select count(*) from table1,但是联表查询时，生成的sql是将原始sql作为子查询然后获取条数，生成的sql例如 select count(*) from ( select t1.id, t2.value from table1 t1 left join table2 t2 on t1.id = t2.t1_id ) tb，在使用 Sharding-JDBC 分表查询时，不能识别子查询中的表，就会报错找不到表 （我的原始表不在分表数据源中，如果原始表和分表在同一个数据源中，那就会查询原始表，这样就起不到分表查询的这样了）

解决方法

用多线程分别查询10个分表的条数再累加到一起，这样就可以获取到正确的总条数，然后再使用查询到的总条数计算生成limit拼接到查询sql中，这样就避免了原始sql作为子查询的问题
（如果有更好的方法请指教）

代码如下：

/**
 * 查询分页列表
 *
 * @param param 查询参数
 * @return 分页列表
 */
@Override
public PageResponse<OldData> selectOldDataPageList(OldDataListPageParam param) {
    Integer total = getOldDataListShardingCount(param); // 此方法具体代码请看问题3
    if (total != null) {
        //重新封装数据返回给前台
        PageResponse pageResponse=new PageResponse<OldData>();
        pageResponse.setList(Lists.newArrayList());
        pageResponse.setTotal(0);
        return pageResponse;
    }
    List<OldData> list = oldDataMapper.selectOldDataListByLimit(param, generateLimitClause(param.getPageNum(), param.getPageSize(), total));
    //重新封装数据返回给前台
    PageResponse pageResponse=new PageResponse<OldData>();
    pageResponse.setList(list);
    pageResponse.setTotal(total);
    return pageResponse;
}

/**
 * 生成limit
 *
 * @param pageNum
 * @param pageSize
 * @param total
 * @return
 */
public String generateLimitClause(int pageNum, int pageSize, int total) {
    int offset = (pageNum - 1) * pageSize;
    int limit = Math.min(pageSize, total - offset);
    return String.format("LIMIT %d, %d", offset, limit);
}

oldDataMapper.selectOldDataListByLimit 代码如下：

List<OldData> selectOldDataListByLimit(@Param("param") OldDataListPageParam param, @Param("limit") String limit);

oldDataMapper.selectOldDataListByLimit 对应的xml代码如下：

<select id="selectOldDataListByLimit" resultType="int">
	select
        ···
    from old_data a left join old_data_info b on a.id = b.d_id
	<where>
		···
	</where>
	<if test="limit != null and limit != ''">
        ${limit}
    </if>
</select>

问题5、数据统计 sql中用到group by和子查询，会报错

问题描述

在做数据统计时，有一些数量需要通过sql的count()、sum() 等函数查询，更复杂的还会包含联表查询、子查询、group by 等，这样sql 使用 Sharding-JDBC 分表查询肯定是会出问题的，例如上面的问题3和问题4

解决方法

方法① 使用视图查询

将10个分表使用UNION连接select语句，针对不同的业务查询不同的字段，能少则少
优势：操作简单，代码改动量少
劣势：查询效率低

方法② 使用多线程分别查询，再合并结果

类似问题3的写法
优势：查询效率高
劣势：代码修改量大

（如果有更好的方法请指教）

---

持续更新中，有问题请评论~