针对单数据库的情况，根据数据会新增的狠厉害，所以数据的快速增长针对数据库的压力也是很有客观的，最直观的感觉就是查询数据库的操作，延时越来越慢，所以针对这个问题，我们开发也是做了一个接口在数据量增大到一定程度的时候会新建表分区，因为以前的数据会很少用到，所以针对这样，建立表分区是不错的方法进行解决！
1、调用接口可以使用Controller进行调用 @Override
public void autoCreateTablePartition(TablePartitionConfigVo tablePartitionConfigVo) throws ApiException {
if (StringUtils.isEmpty(tablePartitionConfigVo.getTableName())) {
throw new ApiException(ApiResultCode.FAILED, "表名不能为空！");
}
if (StringUtils.isEmpty(tablePartitionConfigVo.getPartitionColumnName())) {
throw new ApiException(ApiResultCode.FAILED, "分区字段不能为空！");
}
if (null == tablePartitionConfigVo.getPartitionType()) {
throw new ApiException(ApiResultCode.FAILED, "分区类型不能为空！");
}
autoCreateTablePartitionService.autoCreateTablePartition(tablePartitionConfigVo.getTableName(),
tablePartitionConfigVo.getPartitionColumnName(), tablePartitionConfigVo.getPartitionType());
}
针对的入参实体Vo：
@Data
public class TablePartitionConfigVo implements Vo {
private static final long serialVersionUID = -851570373410700849L;
private String tableName;
private String partitionColumnName;
private Byte partitionType;
}
具体是实现Service服务层：
@Service
public class AutoCreateTablePartitionServiceImpl implements AutoCreateTablePartitionService {
@Autowired
private TbmTablePartitionConfigDao tbmTablePartitionConfigDao;
@Autowired
private ColumnsDao columnsDao;
@Value("${table_schema}")
private String tableSchema;
private static final String PRI_KEY = "PRI";
@Override
public void autoCreateTablePartition(String tableName, String partitionColumnName, Byte partitionType)
throws ApiException {
TablePartitionType tablePartitionType = TablePartitionType.of(partitionType);
if (null == tablePartitionType) {
throw new ApiException(ApiResultCode.FAILED, "参数partitionType不合法！");
}
List tbmTablePartitionConfigs = tbmTablePartitionConfigDao.selectList();
if (CollectionUtils.isNotEmpty(tbmTablePartitionConfigs)) {
// 暂不考虑数据库表名是否大小写敏感，全部转为大写比较
Optional optional = tbmTablePartitionConfigs.stream()
.filter(x -> Objects.equals(tableName.toUpperCase(), x.getTableName().toUpperCase())).findFirst();
if (optional.isPresent()) {
throw new ApiException(ApiResultCode.FAILED, "表分区配置信息已存在！");
}
}
List columns = columnsDao.selectList(tableSchema, tableName, null);
if (CollectionUtils.isEmpty(columns)) {
throw new ApiException(ApiResultCode.FAILED, tableName + "表不存在！");
}
Optional columnsOptional = columns.stream()
.filter(x -> Objects.equals(partitionColumnName.toUpperCase(), x.getColumnName().toUpperCase()))
.findFirst();
if (!columnsOptional.isPresent()) {
throw new ApiException(ApiResultCode.FAILED, partitionColumnName + "列不存在！");
}
Columns column = columnsOptional.get();
PartitionColumnDataType columnDataType = PartitionColumnDataType.of(column.getDataType());
if (null == columnDataType) {
throw new ApiException(ApiResultCode.FAILED, partitionColumnName + "列的数据类型是必须是datetime或timestamp！");
}
// 分区字段必须包含在主键字段内
if (!PRI_KEY.equals(column.getColumnKey())) {
throw new ApiException(ApiResultCode.FAILED, "分区字段必须包含在主键字段内，请检查！");
}
TbmTablePartitionConfig tbmTablePartitionConfig = new TbmTablePartitionConfig(tableName, partitionColumnName,
columnDataType.getType(), partitionType);
tbmTablePartitionConfigDao.insert(tbmTablePartitionConfig);
}
}
3、完整的dao操作实现落库执行
@Repository
public class TbmTablePartitionConfigDaoImpl implements TbmTablePartitionConfigDao {
@Autowired
private TbmTablePartitionConfigMapper tbmTablePartitionConfigMapper;
@Override
public List selectList() {
return tbmTablePartitionConfigMapper.selectList(new QueryWrapper<>());
}
@Override
public void insert(TbmTablePartitionConfig tbmTablePartitionConfig){
tbmTablePartitionConfigMapper.insert(tbmTablePartitionConfig);
}
}
4、表分区按照时间来区分新建枚举类
public enum TablePartitionType {
DAY((byte)1),
MonTH((byte)2),
QUARTER((byte)3),
YEAR((byte)4);
private Byte type;
TablePartitionType(Byte type){
this.type = type;
}
public static TablePartitionType of(Byte value){
TablePartitionType result = null;
if(null == value){
return null;
}
for(TablePartitionType item:TablePartitionType.values()){
if(item.type.equals(value)){
result = item;
break;
}
}
return result;
}
public Byte getType() {
return type;
}
public void setType(Byte type) {
this.type = type;
}
}
5、落库的实体类对象：
@Data
public class TbmTablePartitionConfig {
@TableId(type = IdType.ID_WORKER)
private Long id;
private String tableName;
private String partitionColumnName;
private Byte partitionColumnDataType;
private Byte partitionType;
public TbmTablePartitionConfig(){
}
public TbmTablePartitionConfig(String tableName,String partitionColumnName,Byte partitionColumnDataTyp,Byte partitionType){
this.tableName = tableName;
this.partitionColumnName = partitionColumnName;
this.partitionColumnDataType = partitionColumnDataTyp;
this.partitionType = partitionType;
}
}
6、针对的Column实体类注明下是MYSQL里面的配置表：
@Data
@TableName(value = "information_schema.columns")
public class Columns {
private String tableSchema;
private String tableName;
private String columnName;
private String dataType;
private String columnKey;
}
7.针对Column的Dao的操作
@Repository
public class ColumnsDaoImpl implements ColumnsDao {
@Autowired
private ColumnsMapper columnsMapper;
@Override
public List selectList(String tableSchema, String tableName, String columnName) {
LambdaQueryWrapper lqw = new LambdaQueryWrapper<>();
if (StringUtils.isNotEmpty(tableSchema)) {
lqw.eq(Columns::getTableSchema, tableSchema);
}
if (StringUtils.isNotEmpty(tableName)) {
lqw.eq(Columns::getTableName, tableName);
}
if (StringUtils.isNotEmpty(columnName)) {
lqw.eq(Columns::getColumnName, columnName);
}
return columnsMapper.selectList(lqw);
}
}
