数据库多库多表创建工具

好像在哪儿见过
361 阅读4分钟

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第14天，点击查看活动详情

概述

截止目前，外面平台订单系统单体项目从流量暴增导致查询变慢，经历SQL优化、索引创建、引入缓存、读写分离、分库分表。

分库分表后需要进行路由的设计、全集ID的设计、多数据源的管理。不再通过自己实现多数据源的管理，选择比较成熟的用于分库分表的数据库中间件ShardingSphere进行多数据源管理。

分库分表后方案确定后，需要进行库、表结构的创建，可以手动的进行数据库和表的创建，但是有些麻烦，这里实现了一个小工具快速创建多个数据库和表。

分片策略配置文件

配置文件中包含多数据源的配置信息、分库分表配置信息。数据源可以配置一个或者多个，数据库会平均在多个数据源中进行创建。比如：配置了一个数据源，订单数据库的数量是8个，那么在这个数据源中会创建8个订单数据库。如果是配置了两个数据源，那么每个数据源中会分别创建4个库。

下图示例是只配置了一个数据源，创建了所有的数据库：

image.png

每个库中会创建相同数量的表。

shardingstrategyConfig.properties

# 数据源的配置，多个数据源参考下边的配置
# 数据源1
shardingstrategy.datasources[0].hostName=xxx.xxx.xxx.xxx
shardingstrategy.datasources[0].port=3306
shardingstrategy.datasources[0].username=root
shardingstrategy.datasources[0].password=xxx

# 数据源2
#shardingstrategy.datasources[1].hostName=xxx.xxx.xx.xxx
#shardingstrategy.datasources[1].port=3306
#shardingstrategy.datasources[1].username=root
#shardingstrategy.datasources[1].password=123456

# 分库的配置
# 分库分表前的库名
shardingstrategy.originDbName=order_db
# 要分的逻辑库总数
shardingstrategy.dbNum=8
# 库名的后缀
shardingstrategy.dbSuffix=_

# 分表的配置
# 每个逻辑库中表的数量
shardingstrategy.tableNumPerDb=8
# 表名后缀，一种分表分库维度对应一种后缀，比如C端维度：_by_user_id_  商户端维度：_by_merchant_id_
shardingstrategy.tableSuffix=_sharded_by_user_id_,_sharded_by_merchant_id_

建表SQL脚本

表名称只需要按逻辑表名称配置即可(即按单库单表时的建表语句即可)。工具会sql建表脚本和配置生成实际的表名称。

每个库中会根据配置文件中shardingstrategy.tableNumPerDb的值创建相同数量的表。表的实际名称是脚本中的名称+表名称后缀+表数量索引。表的数量还和表配置文件中shardingstrategy.tableSuffix表名后缀的值有关。如果是多个表名后缀会以逗号隔开。

建表脚本中每张表的数量等shardingstrategy.tableNumPerDb的值与shardingstrategy.tableSuffix逗号切割后数量的乘积。

比如订单表，如果表的数量是8，表名后缀有两个，那么就会创建16张表。

实际结果如下图：

image-20220809143118590.png

数据源配置类

添加配置类，该配置类用于接收配置文件的数据源信息。

package com.sharding.order.sharding;

import lombok.Data;

@Data
public class DataSourceConfig {
    /**
     * 主机名
     */
    private String hostName;
    /**
     * 端口号
     */
    private int port;
    /**
     * 用户名
     */
    private String username;
    /**
     * 密码
     */
    private String password;
}

分片策略配置类

接收配置文件的相关配置信息。

package com.sharding.order.sharding;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Component;

import java.util.List;

@Data
@Component
@ConfigurationProperties(prefix = "shardingstrategy")
@PropertySource("classpath:shardingstrategyConfig.properties")
public class ShardingStrategyConfig {
    /**
     * 原始库名，即分库分表前的库名
     */
    private String originDbName;
    /**
     * 要分的逻辑库总数
     */
    private int dbNum;
    /**
     * 每个逻辑库中表的数量
     */
    private int tableNumPerDb;
    /**
     * 库名的后缀
     */
    private String dbSuffix;
    /**
     * 表名后缀，一种分表分库维度对应一种后缀，比如C端维度：_by_user_id_  商户端维度：_by_merchant_id_
     */
    private List<String> tableSuffix;
    /**
     * 数据源
     */
    private List<DataSourceConfig> datasources;
}

建库建表工具

程序启动的时候会加载分片策略配置文件中的配置信息、读取指定路径下的建表SQL脚本。根据配置文件中的数据源信息、分库分表前的库名、逻辑库总数、库名的后缀先在每个数据源中创建对应的数据库，在创建数据库的过程中同时创建对应的表。

比如是8个数据源、逻辑库总数也是8，那么会分别在每个数据源中创建一个数据库，但是不会等到每个数据源中数据库创建完成后再一次分别创建对应的表(当然这样也可以)。而是在第一个数据源创建完第一个数据库后会接着会解析SQL建表脚本，建表脚本中的每张表的建表语句是以分号进行分割的，所以解析的时候会按分号进行拆分，然后再解析每一条建表语句，从中提取逻辑表名称，再根据解析出的逻辑表名称、配置文件中表的数量和表名后缀分别得到实际的表名称，最终建表语句中的表名称会替换成拼接后的实际表名称，然后执行建表语句。

avatar
文章
阅读
粉丝