java分表动态建表

转载

编程梦想实现家 2024-10-10 17:22:49

文章标签 java分表动态建表 lua 开发语言分表表名 文章分类 Java 后端开发

背景

在我的自动化测试平台中，有一个录制测试案例的功能，会把测试环境上外围渠道调用的接口出入参全部记录在一张表里，在平台刚投入使用时没什么问题，但是随着使用时间的增长，表里的数据越来越多。虽然是每个月系统会定时清理一次过期案例，但是案例仍然会有很多，严重影响系统性能。这个时候我考虑到了做分表来处理单表数据量超级大的问题。

思路

由于平台的持久层采用的Mybatis框架，所以制定分表策略时，我首先想到的是使用插件在执行sql时动态的替换掉表名，那么新表名怎么来的呢？参考公司自研框架的实现思路，采用Lua脚本来动态的计算出新表名，这种方式的好处就是分表逻辑完全脱离业务和框架，使用者可以自定义分表策略，使用起来更灵活。

实现

1、创建路由配置表

CREATE TABLE system_partition_rule(
    table_name VARCHAR(128) NOT NULL   COMMENT '表名' ,
    scheme_id INT    COMMENT '关联分表规则' ,
    created_time DATETIME    COMMENT '创建时间' ,
    type INT    COMMENT '状态' ,
    ext1 VARCHAR(32)    COMMENT 'ext1' ,
    PRIMARY KEY (table_name)
) COMMENT = '表规则总表 记录表名和对应的分表规则ID关系';

CREATE TABLE system_partition_const_param(
    id INT NOT NULL AUTO_INCREMENT  COMMENT '流水编号' ,
    base_name VARCHAR(128)    COMMENT '表名' ,
    param_name VARCHAR(128)    COMMENT '参数名称' ,
    param_value VARCHAR(128)    COMMENT '参数值' ,
    param_comment VARCHAR(512)    COMMENT '备注信息' ,
    ext1 VARCHAR(128)    COMMENT 'ext1' ,
    PRIMARY KEY (id)
) COMMENT = '分表规则常量参数表 记录的脚本中的参数和对应的常量(one param mapping one const)';

CREATE TABLE system_partition_scheme(
    scheme_id INT NOT NULL AUTO_INCREMENT  COMMENT '编号' ,
    lua_context TEXT    COMMENT 'lua脚本' ,
    scheme_comment VARCHAR(512)    COMMENT '备注' ,
    ext1 VARCHAR(32)    COMMENT 'ext1' ,
    ext2 VARCHAR(128)    COMMENT 'ext2' ,
    PRIMARY KEY (scheme_id)
) COMMENT = '分库规则脚本 记录lua脚本,主要体现在分库上';

CREATE TABLE system_partition_logic_param(
    id INT NOT NULL AUTO_INCREMENT  COMMENT '流水号' ,
    table_naem VARCHAR(128)    COMMENT '表名' ,
    param_name VARCHAR(128)    COMMENT '参数名' ,
    param_value TEXT    COMMENT '参数值' ,
    param_comment VARCHAR(512)    COMMENT '备注' ,
    ext1 VARCHAR(128)    COMMENT 'ext1' ,
    PRIMARY KEY (id)
) COMMENT = '分表规则逻辑参数表 存放的是逻辑，一个参数对应一段逻辑（one param mapping one logic）。';

CREATE TABLE system_partition_relation(
    id INT NOT NULL AUTO_INCREMENT  COMMENT '流水' ,
    table_name VARCHAR(128)    COMMENT '表名' ,
    param_name VARCHAR(128)    COMMENT '参数名称' ,
    op_type VARCHAR(32)    COMMENT '操作类型' ,
    relation VARCHAR(32)    COMMENT '关联关系' ,
    column_name VARCHAR(32)    COMMENT '字段名' ,
    ext1 VARCHAR(32)    COMMENT 'ext1' ,
    PRIMARY KEY (id)
) COMMENT = '分表字段关系表 具体分表字段，例如按照bill_month分表';

2、程序初始化时加载配置表

核心代码
这里省略Mybatis的mapper类和实体类，还有一些其他的非核心类。

package com.cz.utils;

import com.cz.mapper.*;
import com.cz.model.*;
import com.cz.spring.SpringContextUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.script.ScriptException;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.StringReader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * 初始化分表规则上下文
 * @program: PostGirl-panent
 * @description: PartitionContextBuilder
 * @author: Cheng Zhi
 * @create: 2022-04-20 20:43
 **/
public class PartitionContextBuilder {

    Logger logger = LoggerFactory.getLogger(PartitionContextBuilder.class);
    private static final String LUA_CALL_SCRIPT="return partition_rule(table_partition_params)";

    // 存放所有的schemas
    Map<Integer, SystemPartitionScheme> schemas = new HashMap<Integer,SystemPartitionScheme>();
    // 存放分表常量参数
    Map<String, SystemPartitionConstParam> consts = new HashMap<String,SystemPartitionConstParam>();
    // 存放分表逻辑
    Map<String, SystemPartitionLogicParam> logics = new HashMap<String,SystemPartitionLogicParam>();
    // 存放所有的路由参数
    Map<String,Map<String, SystemPartitionRelation>> partitionParams = new HashMap<String,Map<String,SystemPartitionRelation>>();

    public PartitionContext build() throws ScriptException, IOException {

        boolean success = loadSchemas();
        if (!success) {
            return null;
        }

        PartitionContext partitionContext = new PartitionContext();
        // 拼接成完整的lua 查询System_partition_rules
        List<SystemPartitionRule> systemPartitionRuleList = SpringContextUtil.getBean(SystemPartitionRuleMapper.class).findAll();
        for (SystemPartitionRule systemPartitionRule : systemPartitionRuleList) {
            String tableName = systemPartitionRule.getTableName();
            Integer schemeId = systemPartitionRule.getSchemeId();

            SystemPartitionScheme systemPartitionScheme = schemas.get(schemeId);
            // 解析lua伪代码
            Map<String, String> constParams = new TreeMap<>();

            String luaScript = analyze(systemPartitionScheme, tableName, constParams);

            logger.debug("加载脚本内容：" + luaScript);
            Map<String, SystemPartitionRelation> partitionRelationMap = partitionParams.get(tableName);
            partitionContext.addConfig(schemeId,tableName, luaScript, constParams, partitionRelationMap);

        }
        return partitionContext;

    }

    /**
     * 解析SystemPartitionScheme表中的lua,将lua中的占位字段替换为真实字段
     * @param schema      SystemPartitionScheme表
     * @param tableName   表名
     * @param constParams 变量集
     * @return
     */
    private String analyze(SystemPartitionScheme schema,String tableName, Map<String, String> constParams) throws IOException {

        String luaScript = schema.getLuaContext();
        // 按照行解析
        int n;
        while ((n=luaScript.indexOf("?{"))>-1) {

            int m = luaScript.indexOf("}",n);
            if (n > -1 && m > -1) {
                String paramName = luaScript.substring(n+2, m);

                // 根据paramName查找paramValue  find paramValue by paramName
                String paramValue = getParamValue(paramName, tableName, constParams);
                // 做替换 replace paramName use paramValue

                StringBuilder sb = new StringBuilder(luaScript.length() + 16);
                sb.append(luaScript.substring(0, n));
                sb.append(paramValue);
                sb.append(luaScript.substring(m+1));
                luaScript = sb.toString();
            }

        }

        BufferedReader reader = new BufferedReader(new StringReader(luaScript));
        StringBuilder sb = new StringBuilder(luaScript.length());
        try {
            String line;
            while ((line = reader.readLine()) != null) {
                // line不包含require 或者 包含require但不是开头，则认为是需要append的行
                if (line.indexOf("require ") == -1 || !line.trim().startsWith("require")) {
                    sb.append(line).append('\n');
                }
            }
            // 加上调用函数脚本，以便调用上下文和函数本身的上下文保持一致，从而避免多线程调用的相关问题
            sb.append(LUA_CALL_SCRIPT).append("\n");
            return sb.toString();
        } finally {
            reader.close();
        }

    }

    /**
     * 根据paramName获取paramValue
     * @param paramName     参数名
     * @param tableName     表名
     * @param constParams   保存paramName和paramValue的映射关系
     * @return
     */
    private String getParamValue(String paramName, String tableName, Map<String, String> constParams) {

        if("TABLE_NAME".equals(paramName)){
            return tableName;
        }

        String key = tableName + "|" + paramName;
        SystemPartitionConstParam systemPartitionConstParam = consts.get(key);  // 所有的表规则参数
        SystemPartitionLogicParam systemPartitionLogicParam = logics.get(key);  // 所有的库路由参数

        if (systemPartitionConstParam != null && systemPartitionLogicParam != null) {
            // 如果都不为空，说明库路由和表规则参数名重复
            throw new IllegalArgumentException("Invalid Configuration: there are duplicate const Param and logic param :" + paramName);
        } else if (systemPartitionConstParam != null) {
            String constParamValue = systemPartitionConstParam.getParamValue();
            constParams.put(paramName, constParamValue);
            return constParamValue;
        } else if (systemPartitionLogicParam != null) {
            // here is a piece of code 这里获取到的是一段代码
            String logicParamValue = systemPartitionLogicParam.getParamValue();
            return logicParamValue;
        } else {

            throw new IllegalArgumentException("the param " + paramName + " for " + tableName + "is not found !");
        }

    }

    /**
     * 加载路由表
     * @return
     */
    private boolean loadSchemas() {

        boolean success = true;

        try {
            // 查询schema
            List<SystemPartitionScheme> systemPartitionSchemeList = SpringContextUtil.getBean(SystemPartitionSchemeMapper.class).findAll();
            for (SystemPartitionScheme systemPartitionScheme : systemPartitionSchemeList) {
                schemas.put(systemPartitionScheme.getSchemeId(), systemPartitionScheme);
            }

            // 加载分表规则
            List<SystemPartitionConstParam> systemPartitionConstParamList = SpringContextUtil.getBean(SystemPartitionConstParamMapper.class).findAll();
            for (SystemPartitionConstParam systemPartitionConstParam : systemPartitionConstParamList) {
                consts.put(systemPartitionConstParam.getBaseName() + "|" + systemPartitionConstParam.getParamName(), systemPartitionConstParam);
            }

            // 加载logics
            List<SystemPartitionLogicParam> systemPartitionLogicParamList = SpringContextUtil.getBean(SystemPartitionLogicParamMapper.class).findAll();
            for (SystemPartitionLogicParam systemPartitionLogicParam : systemPartitionLogicParamList) {
                logics.put(systemPartitionLogicParam.getTableNaem() + "|" + systemPartitionLogicParam.getParamName(), systemPartitionLogicParam);
            }

            List<SystemPartitionRelation> systemPartitionRelationList = SpringContextUtil.getBean(SystemPartitionRelationMapper.class).findAll();
            for (SystemPartitionRelation systemPartitionRelation : systemPartitionRelationList) {
                String tableBaseName = systemPartitionRelation.getTableName();
                Map<String,SystemPartitionRelation> partitionParam=partitionParams.get(tableBaseName);
                if(partitionParam == null){
                    partitionParam=new HashMap<String,SystemPartitionRelation>();
                    partitionParams.put(tableBaseName, partitionParam);
                }
                //所有的参数都提供
                //参数名为Key(转为大写),Lua参数名为Value的map
                partitionParam.put(systemPartitionRelation.getParamName(), systemPartitionRelation);
            }
        } catch (Exception e) {
            success = false;
            throw new IllegalArgumentException("load partition rules fail..." + e);
        }

        return success;
    }

3、lua分表脚本示例

function partition_rule(table_partition_params)
    local table = table_partition_params -- 分表字段 分表日期
    -- local currentMonth = os.date("%Y%m%d");
    local currentMonth = os.date("%H%M%S");
    print(currentMonth)
    local tableName = "?{user_name}.".."?{TABLE_NAME}" -- ?{}为自定义占位符，程序在初始化加载脚本时会进行替换
    local mod_count = ?{mod_count}
    local field = table["SO_NBR"] -- 分表字段名
    local suffix = tonumber(currentMonth)%mod_count
    local result = tableName.."_"..suffix
    print(suffix)
    return result
end

4、mybatis插件

package com.cz.mybatis.shard;

import com.cz.obj.ObjectUtils;
import com.cz.session.ContextHolder;
import com.cz.spring.SpringContextUtil;
import com.cz.sql.SqlParserTool;
import com.cz.utils.PartitionUtil;
import net.sf.jsqlparser.statement.Statement;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ReflectionUtils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @program: PostGirl-panent
 * @description: ShardingPlugin
 * @author: Cheng Zhi
 * @create: 2021-12-28 16:19
 **/
@Intercepts(@Signature(
        type = StatementHandler.class,
        method = "prepare",
        args = {Connection.class,Integer.class}
))
public class ShardingPlugin implements Interceptor {

    Logger log = LoggerFactory.getLogger(ShardingPlugin.class);

    private long time;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        // 1、通过StatementHandler获取执行的sql
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        BoundSql boundSql = statementHandler.getBoundSql();
        ParameterHandler parameterHandler = statementHandler.getParameterHandler();
        Object parameterObject = parameterHandler.getParameterObject();
        Map<String, Object[]> fieldNameAndValues = new HashMap<>();
        if (parameterObject != null) {
            // 获取对象属性
            Map<String, Object> fieldNameAndValue = new HashMap<>();
            ObjectUtils.getFieldNameAndValue(parameterObject, fieldNameAndValue);

            for (String key : fieldNameAndValue.keySet()) {
                Object[] objs = new Object[] {fieldNameAndValue.get(key)};
                fieldNameAndValues.put(key, objs);
            }
        }

        String sqlBefore = boundSql.getSql();

        String tableName = SqlParserTool.getSingleTableName(sqlBefore);
        // 如果没有分表字段，可直接在线程里指定
        Object so_nbr = ContextHolder.getRequestContext().get("SO_NBR");
        if (so_nbr != null) {
            fieldNameAndValues.put("SO_NBR", new Object[] {so_nbr});
        }
        // 更新后的sql
        if (sqlBefore.contains(tableName) && fieldNameAndValues.size() > 0) {
            // 获取sql条件中的字段名和对应的字段值。
            PartitionUtil bean = SpringContextUtil.getBean(PartitionUtil.class);
            if (bean != null) {
                String newTableName = bean.getTableName(tableName, fieldNameAndValues, null);
                String sqlAfter = sqlBefore.replaceAll(tableName, newTableName);
                // 通过反射替换boundSql中的sql
                Field sqlField = boundSql.getClass().getDeclaredField("sql");
                sqlField.setAccessible(true);
                sqlField.set(boundSql, sqlAfter);
            }

        }
        Object proceed = invocation.proceed();

        return proceed;
    }
}

demo

按照当前时间取模分表

@Test
    public String test1() {

        Map<String, Object[]> map = new HashMap<String, Object[]>();
        Integer[] soNbr = new Integer[] {123456};
        map.put("SO_NBR", soNbr);
        //ContextHolder.getRequestContext().put("SO_NBR", 123456);
        TestPartition testPartition = new TestPartition();
        testPartition.setUpdatedTime(DateUtil.date());
        SpringContextUtil.getBean(TestPartitionMapper.class).insertSelective(testPartition);
        //ContextHolder.getRequestContext().clear();
        return "success";
    }

效果（在不改动业务逻辑的基础上实现了分表功能）：

java分表动态建表_lua