记录 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) {
    AtomicInteger totalCount = new AtomicInteger(0);
        CompletableFuture<?>[] futures = IntStream.range(0, 10)
                .mapToObj(index -> CompletableFuture.supplyAsync(() -> {
                    Integer count = oldDataMapper.selectOldDataListCount(param, "old_data_" + index);
                    return count != null ? count : 0;
                }).thenAccept(totalCount::addAndGet))
                .toArray(CompletableFuture[]::new);
        CompletableFuture.allOf(futures).join();
        return totalCount.get();
}

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的写法
优势：查询效率高
劣势：代码修改量大

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

问题6、数据统计时报错 java.lang.IllegalStateException: Can not find owner from table.

问题描述

在做数据统计时，有一些数量需要通过sql的count()、sum() 等函数查询，还包含联表查询、子查询、group by 、order by等，数据源是Sharding-JDBC时出现报错java.lang.IllegalStateException: Can not find owner from table.，但是在 Navicat 中单独执行sql不会有问题，在普通数据源中也不会有问题。

问题sql如下

select
	temp.*
from
	(select * from old_data where status > 0) temp
group by temp.type	
order by temp.m_time desc, temp.c_time desc

解决方法

将sql中的 temp.* 改为各个需要的字段名，且 group by 和 order by 后要使用字段别名。sql如下：

select
	temp.id as id,
    temp.name as name,
    temp.type as type,
    temp.m_time as mTime,
    temp.c_time as cTime
from
	(select * from old_data where status > 0) temp
group by type	
order by mTime desc, cTime desc

是因为Sharding-JDBC在执行过程中没有正确识别临时表的字段名导致的在数据库表中找不到所有者这个错误
具体原因和解析可以参考 can not find owner from table报错处理

---

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