ShardingJDBC分表以及自动化建表方案（避坑攻略）

前言最近死磕…应该是磕死在了分表上，因为做的学校项目，对学校每学期数据会达到千万级别的表需要做分表处理，刚开始分表工具方案的选取，有试过mysql自带的表分区功能，Mycat，因为项目需要根据学期来进行自动分表，而且数据表需要程序根据学期的更新来自动创建，就是说比如原表为A，当监听到学期发生改变时，若改变为2020-202102学年学期，则需要自动创建一张A202102的历史表，分表路径的表名也需

会飞天的蜗牛

7871人浏览 · 2021-07-27 15:00:02

会飞天的蜗牛 · 2021-07-27 15:00:02 发布

文章目录

前言

最近死磕…应该是磕死在了分表上，因为做的学校项目，对学校每学期数据会达到千万级别的表需要做分表处理，刚开始分表工具方案的选取，有试过mysql自带的表分区功能，Mycat，因为项目需要根据学期来进行自动分表，而且数据表需要程序根据学期的更新来自动创建，就是说比如原表为A，当监听到学期发生改变时，若改变为2020-202102学年学期，则需要自动创建一张A202102的历史表，分表路径的表名也需要根据学期自动化拼接。所以最后采用了ShardingJdbc，可以实现复杂情况下的路由键表名拼接，不需要对原项目做过多的修改，只需要新增分表策略；好了，开始正文把，里面的坑是数不尽的！

一、分库分表概述

分库分表主要是从业务和数据量角度上来考虑系统存储容量和性能的问题提供的一套解决方案，同一个库同一张表，数据量大了，系统查询效率，数据存储容量是两个大关卡；

根据项目业务需求，可以进行 垂直分表、水平分表、垂直分库以及水平分库，因为本篇文章主要是应对项目中实际需求应用时做出的一套处理方案以及避坑策略，这里不会对以上各个技术点做详细描述；

1、垂直分表

在项目中例如现在有user表，存在 id、name、age、addr、gender、interest；在项目中 name，age、addr会被频繁的调用，而gender、interest频率低，当数据量达到百万甚至千万级别时，为了提高查询效率，将常用的字段与非常用字段进行分离，这时候就可以考虑将name、age、addr与gender、interest分别用两张表A、B来存储，这样就将原来的user表根据业务需求垂直拆分为了A、B表，例如下图实际情况；总结一句话：

垂直分表就是将数据库中数据表按照业务拆分出字段组合成业务表

2、水平分表

水平分表一般是采用比较多的方式，因为在项目系统原理设计时，如果已经根据实际情况合理的设计了数据表，一般再对表进行拆分；水平分表其实能联想到，将数据库里面存放的一行行的记录，根据某个字段进行分组，将每一组数据重新存放到另一张表，而存放每组数据的表和原始表结构是一致的，之后当需要用到哪个数据时，再按照路由键找到制定表查询对应数据，这样一来，每张表的数据量减少了，系统执行效率可以提高，表数据存储容量也可以得到扩展；总结一句话：

水平分表就是数据表中数据按照表字段的拆分策略放到与原表相同结构的数据表存储

3、垂直分库

通过垂直分表性能得到了一定程度的提升，但是还没有达到要求，并且磁盘空间也快不够了，因为数据还是始终限制在一台服务器，库内垂直分表只解决了单一表数据量过大的问题，但没有将表分布到不同的服务器上，因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘。
例如：现在有A数据库，里面有a、b、c、d四个数据表，随着数据累积，很快数据库A的执行效率达到了瓶颈，这时候，可以按业务需求，创建另外数据库B存储c、d表；总结一句话：

垂直分库就是将一个数据库中的一些表，按照业务拆分到另一个数据库

4、水平分库

经过垂直分库后，数据库性能问题得到一定程度的解决，但是随着业务量的增长,上面A、B库单库存储数据也会达到瓶颈，所以需要对数据进行拆分存储，例如新建数据库C表e，将数据库A中b表数据放一些到数据库C表e中；总结一句话：

水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

水平分库

以上就是对分库分表的简单介绍，概念图和一些技术点参照了这位大牛的文章，文章中针对各个技术点以及优劣势做了详细的讲解，大家可以传送过去一起学习下；

二、需求实现

开始之前，我们再来理清一下需求和方案，各位可以根据自己的项目实际需求选择性学习避坑；

1、按学期进行水平分表（分库就自己去拓展了）；
2、学期历史表（分表）自动化监听触发创建；

方案：使用ShardingJDBC分表拆分策略（ShardingJDBC拆分策略传送门）

1、项目架构搭建

1.1、技术栈及项目架构

技术栈：springboot、mybatis-plus、mysql、shardingJDBC

项目结构如下：

数据库结构如下：

# 系统学年学期记录表
CREATE TABLE `school_term` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `term` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '学年学期',
  `term_start_date` datetime DEFAULT NULL COMMENT '学期开始日期',
  `term_end_date` datetime DEFAULT NULL COMMENT '学期结束日期',
  `term_count` int(11) DEFAULT NULL COMMENT '1：学年第一学期\n2：学年第二学期',
  `is_last` int(11) DEFAULT NULL COMMENT '最新日期 0：否 1：是',
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='学期';

# 学生活动表
CREATE TABLE `stu_active_infor` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `stu_class` int(11) NOT NULL,
  `active_time` datetime NOT NULL,
  `active_desc` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

1.2、项目整体思路

项目初始化，获取第三方最新学期，若与本系统学期一致，则无需分表，即无需创建学期历史表，反之，程序自动化创建上一学期历史表；分表字段采用活动时间和活动学生Id；
系统查询数据时，若查询条件带时间字段则查询数据库取出所有学期，判断该时间数据哪个学年学期，则用该学期信息拼接要查询的学生活动信息表名；若不带时间字段，则默认查询当前最新学年学期数据；

2、配置ShardingJDBC

配置文件：

server:
  port: 8088

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.hexu.sharedb.entity

spring:
  application:
    name: sharedb
  main:
    # 只使用注册一个bean 会有多个dataSource,不设置会抛出异常启动失败
    allow-bean-definition-overriding: true

sharding:
  jdbc:
    datasource:
      names: sharedb0
      #sharedb0
      sharedb0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sharedb0?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: LancooECP
    config:
      sharding:
        # 分表策略
        tables:
          stu_active_infor:
            actual-data-nodes: sharedb0.stu_active_infor
            table-strategy:
              complex:
                sharding-columns: sid,active_time  # 拆分键使用两个字段，真正使用的只有active_time，因为有时候查询条件中没有时间，但是一般都会有学号
                algorithm-class-name: com.hexu.sharedb.sharding.ShardingAlgorithm

      # 开启SQL打印
      props:
        sql.show: true
# 需要拆分的数据表
term:
  sharding:
    tables: stu_active_infor

配置类（ShardingAlgorithm.java）：

/**
 * @author gufusheng
 * @time 2021/7/19 19:46
 */
public class ShardingAlgorithm implements ComplexKeysShardingAlgorithm {

    @SneakyThrows
    @Override
    public Collection<String> doSharding(Collection<String> collection, Collection<ShardingValue> collection1) {
        SchoolTermService schoolTermService = BeanContext.getBean(SchoolTermService.class);
        List<String> result = new ArrayList<>();
        for (ShardingValue shardingValue : collection1) {
            Class clazz = shardingValue.getClass();

            Field[] fields = clazz.getDeclaredFields();
            Map<String, Object> map = new HashMap<>();
            for (Field field : fields) {
                //打开私有访问
                field.setAccessible(true);
                String name = field.getName();
                Object value = field.get(shardingValue);
                map.put(name, value);
            }

            if (StringUtils.equals((String) map.get("columnName"), "active_time")) {
                Timestamp time = null;
                if (map.containsKey("valueRange")) { // active_time可能为范围查询，此条件判断针对该情况
                    Range range = (Range) map.get("valueRange");
                    time = (Timestamp) range.lowerEndpoint();
                } else {
                    List list = (LinkedList) map.get("values");
                    time = (Timestamp) list.get(0);
                }
                List<SchoolTerm> list = schoolTermService.getList();
                StringBuilder tableName = new StringBuilder();
                tableName.append(map.get("logicTableName"));
                for (SchoolTerm term : list) {
                    if (DateUtil.isIn(time, term.getTermStartDate(), term.getTermEndDate())) {
                        String name;
                        if (term.getIsLast().equals(1)) {
                            // 当前学期时间，则拼接当前表
                            name = "";
                        } else {
                            name = term.getTerm().substring(2, 4) + term.getTerm().substring(7, 9) + term.getTerm().substring(9);
                        }
                        tableName.append(name);
                    }
                }
                result.clear();
                result.add(tableName.toString());
                break;
            } else {
                // 不带时间字段时，查询默认表，即最新学年历史轨迹表
                StringBuffer tableName = new StringBuffer();
                String name = "";
                tableName.append(map.get("logicTableName"))
                        .append(name);
                result.add(tableName.toString());
            }
        }
        System.out.println("进入分表设计");
        result.forEach(System.out::println);
        return result;
    }
}

3、自动化建表

相信很多项目都会有这个需求，但是ShardingJDBC不支持DDL语句，这里大牛们可以深度专研下，我就直接说下我遇到的问题以及解决方法了；

3.1、踩坑及解决

问题1： 使用shardingjdbc配置获取的连接不能不能执行DDL语句；
解决1： 配置原生JDBC连接工具；

问题2： Statement有一些DDL语句不支持，例如rename数据库名；
解决2： 使用预处理PreparedStatement正常执行；

问题3： mysql执行DDL语句默认是不能回滚的；
解决3： 一个方法是可以手动配置mysql使其支持回滚，这里我程序自动检测若存在异常则自动回滚；

问题4： 使用了shardingJDBC后，其底层有有一些SQL是不能执行的，例如Union/All，having等；
解决4： 这里我目前是将项目中不支持的sql全部改掉，因为自己项目中使用的SQL基本都支持，大家看看有啥更好的解决方法分享下；

带着以上四个问题做了以下数据库原生JDBC的工具封装；

3.2、DB工具类封装

类文件中有许多封装的工具，大家可以参考下：包括根据配置key手动读取yml配置值，使用properties很简便，但是使用yml稍复杂些；

/**
 * @author gufusheng
 * @time 2021/7/24 17:51
 */
@Component
public class DBUtils {

    private static final Logger logger = LoggerFactory.getLogger(DBUtils.class);

    private static String driverClass;
    private static String url;
    private static String user; 
    private static String password;  
    private static Connection connection;
    private static String [] shardingTableNames;
    private static String active;

    static {
        active = getYmlConfig("application.yml", "spring.profiles.active");
        Yaml yaml = new Yaml();
        InputStream inputStream;
        Resource resource;
        try {
            driverClass = getYmlConfig("sharding.jdbc.datasource.sharedb0.driver-class-name");
            url = getYmlConfig("sharding.jdbc.datasource.sharedb0.url");
            user = getYmlConfig("sharding.jdbc.datasource.sharedb0.username");
            password = getYmlConfig("sharding.jdbc.datasource.sharedb0.password");

            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        shardingTableNames = getYmlConfig("term.sharding.tables").split(",");
    }

    /**
     * @param ymlName 配置文件路径
     * @param key 查找的配置键
     * @return
     */
    public static String getYmlConfig(String ymlName, String key) {
        String[] split = key.split("\\.");
        Yaml yaml = new Yaml();
        InputStream inputStream;
        Resource resource;
        try {
            resource = new ClassPathResource(ymlName);
            inputStream = resource.getInputStream();
            Map<String, Object> load = yaml.loadAs(inputStream, Map.class);
            for (int i = 0; i < split.length; i++) {
                if (i == split.length-1) {
                    return (String) load.get(split[i]);
                } else {
                    load = (Map<String, Object>) load.get(split[i]);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 配置键查找profile内的配置值
     * @param key
     * @return
     */
    public static String getYmlConfig(String key) {
        String[] split = key.split("\\.");
        Yaml yaml = new Yaml();
        InputStream inputStream;
        Resource resource;
        try {
            resource = new ClassPathResource("application-" + active + ".yml");
            inputStream = resource.getInputStream();
            Map<String, Object> load = yaml.loadAs(inputStream, Map.class);
            for (int i = 0; i < split.length; i++) {
                if (i == split.length-1) {
                    return (String) load.get(split[i]);
                } else {
                    load = (Map<String, Object>) load.get(split[i]);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 执行sql
     * @param sql sql语句
     * @throws Exception
     */
    public void executeSql(String sql) throws Exception {
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.execute();

            logger.info("sql执行成功..");
        } catch (SQLException e) {
            throw new Exception("sql执行失败.." + '\n' + "SQL: "+ sql);
        }
    }

    /**
     * 获取数据库中所有数据表
     * @return
     */
    public List<String> tableNames() {
        StringBuffer sbTables = new StringBuffer();
        List<String> tables = new ArrayList<String>();
        try {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getTables(null, null, null,new String[] { "TABLE" });
            while (rs.next()) {// ///TABLE_TYPE/REMARKS
                //            sbTables.append("表名：" + rs.getString("TABLE_NAME") + "<br/>");
                //            sbTables.append("表类型：" + rs.getString("TABLE_TYPE") + "<br/>");
                //            sbTables.append("表所属数据库：" + rs.getString("TABLE_CAT") + "<br/>");
                //            sbTables.append("表所属用户名：" + rs.getString("TABLE_SCHEM")+ "<br/>");
                //            sbTables.append("表备注：" + rs.getString("REMARKS") + "<br/>");
                if (StringUtils.equals(rs.getString("TABLE_CAT"), "share0")) {
                    tables.add(rs.getString("TABLE_NAME"));
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return tables;
    }

    public void createHisTrackTable(String tableName, SchoolTerm schoolTerm) throws Exception {
        String newName = splitTableName(tableName, schoolTerm);
        Statement statement = null;
        PreparedStatement preparedStatement = null;
        String rename = "rename table " + tableName + " to " + newName + ";";
        String create = "create table " + tableName + " like "+ newName +";";
        try {
            statement = connection.createStatement();
            preparedStatement = connection.prepareStatement(rename);

            assert statement != null;

            preparedStatement.execute();

            preparedStatement = connection.prepareStatement(create);
            preparedStatement.execute();

            logger.info(tableName + ": 数据移植成功..");
        } catch (SQLException e) {
            throw new Exception(tableName + "数据移植失败..");
        }
    }

    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
    public void termListener() {
        ThirdService thirdService = BeanContext.getBean(ThirdService.class);
        SchoolTermService schoolTermService = BeanContext.getBean(SchoolTermService.class);
        SchoolTermMapper schoolTermMapper = BeanContext.getBean(SchoolTermMapper.class);
        // 获取智慧校园当前学期
        SchoolTerm currentTerm = thirdService.getCurrentTerm();
        // 获取系统最新学期
        SchoolTerm lastSchoolTerm = schoolTermService.lastTerm();
        if (lastSchoolTerm != null) {
            if (!StringUtils.equals(lastSchoolTerm.getTerm(), currentTerm.getTerm())) {
                // 系统当前学期和智慧校园当前学期不一致
                try {
                    for (String tableName : shardingTableNames) {
                        createHisTrackTable(tableName, lastSchoolTerm);
                    }
                    // 切换系统学期
                    lastSchoolTerm.setIsLast(0);
                    schoolTermMapper.updateById(lastSchoolTerm);
                    currentTerm.setIsLast(1);
                    schoolTermMapper.insert(currentTerm);
                    logger.info("学期切换成功..");
                } catch (Exception e) {
                    logger.info("学期切换失败..");
                    logger.error(e.getMessage());
                    logger.error(e.getCause().toString());
                    // 检测数据移植是否成功，失败则回滚
                    checkTransplant(lastSchoolTerm);
                }
            }
        } else {
            // 系统初始化
            currentTerm.setIsLast(1);
            schoolTermMapper.insert(currentTerm);
            logger.info("学期初始化成功..");
        }
    }

    /**
     * 检测数据移植是否成功,检测失败手动回滚
     */
    public void checkTransplant(SchoolTerm schoolTerm) {
        // 当前数据库存在的表
        List<String> tableNames = tableNames();
        // 按数据表移植成功后应存在的表
        List<String> subTableNames = new ArrayList<>();
        for (String shardingTableName : shardingTableNames) {
            subTableNames.add(shardingTableName);
            subTableNames.add(splitTableName(shardingTableName, schoolTerm));
        }
        // 进行数据移植的表
        List<String> tableNameList = Arrays.asList(shardingTableNames);
        for (String subTableName : subTableNames) {
            if (!tableNames.contains(subTableName)) {
                // 数据表移植检测失败
                if (tableNameList.contains(subTableName) && tableNames.contains(splitTableName(subTableName, schoolTerm))) {
                    // 重命名成功，但创建表失败
                    String sql = "rename table " + subTableName + " to " + subTableName.substring(0, subTableName.length()-6);
                    try {
                        executeSql(sql);
                    } catch (Exception e) {
                        logger.error("回滚重命名失败，请管理员处理!!");
                        logger.error(e.getMessage());
                        logger.error(e.getCause().toString());
                    }
                }
            }
        }
        logger.info("检测数据移植成功");
    }

    // 进行表名拼接
    public String splitTableName(String tableName, SchoolTerm schoolTerm) {
        return tableName + schoolTerm.getTerm().substring(2, 4) + schoolTerm.getTerm().substring(7, 9) + schoolTerm.getTerm().substring(9);
    }
}