PostgreSql分库分表

  • PostgreSql分库分表
  • 一、分库分表的判断依据
  • 二、分表
  • 2.1使用触发器，子表的方式分表
  • 2.1.1 创建父表
  • 2.1.2 创建子表
  • 2.1.3 创建子表的约束
  • 2.1.4 创建子表的索引
  • 2.1.4 创建分区函数
  • 2.1.5 创建父表触发器
  • 2.1.6 测试
  • 2.2 使用Mybatis分表
  • 2.3 使用中间件分表


PostgreSql分库分表

一、分库分表的判断依据

  • 如果单表数据量太大：分表–水平分表和垂直分表
  • 如果单数据库QPS达到上限：分库

二、分表

场景：一个公司有多个部门，需要记录人员的操作日志，但是数据量很大

分表：使用部门id分表

2.1使用触发器，子表的方式分表

2.1.1 创建父表
create table db_father (
	c_id char(32),		--主键
	c_dept_id varchar(30), --部门id
	c_yw_id char(32),	--业务id
	c_value varchar(300)--业务数据
);
2.1.2 创建子表
create table db_father_10000() inherits (db_father);
create table db_father_20000() inherits (db_father);
create table db_father_30000() inherits (db_father);
create table db_father_40000() inherits (db_father);
......

postgresql 分库插件 pgsql 分库分表_sql

2.1.3 创建子表的约束
alter table db_father_10000
add constraint db_father_10000_check_dept_key
check(c_dept_id = '10000');
alter table db_father_20000
add constraint db_father_20000_check_dept_key
check(c_dept_id = '20000');
alter table db_father_30000
add constraint db_father_30000_check_dept_key
check(c_dept_id = '30000');
alter table db_father_40000
add constraint db_father_40000_check_dept_key
check(c_dept_id = '40000');
2.1.4 创建子表的索引
create index db_father_10000_key
on db_father_10000(c_id, c_dept_id, c_yw_id);
create index db_father_20000_key
on db_father_20000(c_id, c_dept_id, c_yw_id);
create index db_father_30000_key
on db_father_30000(c_id, c_dept_id, c_yw_id);
create index db_father_40000_key
on db_father_40000(c_id, c_dept_id, c_yw_id);
2.1.4 创建分区函数
create or replace function db_father_partition_trigger()
	returns trigger as $$
begin
	if new.c_dept_id = '10000'
	then
		insert into db_father_10000 values (new.*);
	elseif new.c_dept_id = '20000'
	then
		insert into db_father_20000 values (new.*);
	elseif new.c_dept_id = '30000'
	then
		insert into db_father_30000 values (new.*);
	elseif new.c_dept_id = '40000'
	then
		insert into db_father_40000 values (new.*);
	end if;
	return null;
end;
$$
language plpgsql;
2.1.5 创建父表触发器
create trigger insert_db_father_trigger
before insert on db_father
for each row execute procedure db_father_partition_trigger();
2.1.6 测试
insert into db_father values('8778f843c5cec712be47430c8916d2ad', '10000', '9bb45e1deac9f925ce1358d1ddb7fdd7', '我是db_father10000子表');
insert into db_father values('929d8025664959b7f02774314ce69e6a', '20000', '19855c4edde6c6a1e25a63f0eadbe28f', '我是db_father20000子表');
insert into db_father values('c85a1130d53d87921287f5b8b8c8c6c8', '30000', '1256e6a48fbc0db393adc6cd1960ef44', '我是db_father30000子表');
insert into db_father values('9a8b01388f5fa3816e984f51cf62373f', '40000', '60ae95b6e2fbc62bddc48a3537a06a82', '我是db_father40000子表');
insert into db_father values('51fde34cb00be6cbb17061ada8d1fa52', '50000', '4411e6eaf94cd60443606cb44377ba10', '我是没有子表的数据');

postgresql 分库插件 pgsql 分库分表_数据库_02


postgresql 分库插件 pgsql 分库分表_数据库_03

可以看到，前四条数据按触发器中的判断分别进入了四个子表，第五条因为没有对50000的数据处理，所以会执行成功，但是不会插入数据

查询主表可以看到所有的数据，这是逻辑数据，实际数据是存在子表中的

postgresql 分库插件 pgsql 分库分表_sql_04

2.2 使用Mybatis分表

  • 实现Interceptor 接口，使用@Intercepts注解 实现一个mybatis拦截器
  • 拦截所有请求，从配置类，注解或者数据库中获取需要分表的表
  • 把需要分表的sql替换为分表后的sql，主要是根据分表策略替换表名
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.util.Properties;

/**
 * MybatisSubmeterInterceptor
 * @Description: 分表拦截器
 * @Author:
 * @Date 2022/7/29 9:43
 * @Version 1.0.0
 */
@ConditionalOnClass(value = org.apache.ibatis.plugin.Interceptor.class)
@Component("mybatisSubmeterInterceptor")
@Intercepts({
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
})
@Order(0)
@Slf4j
public class MybatisSubmeterInterceptor  implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        Executor executor = (Executor) invocation.getTarget();
        // 获取原始的sql语句
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        Object parameter = invocation.getArgs()[1];
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        String oldsql = boundSql.getSql();

        // 判断是否需要分表
        if (isReplaceTableName(oldsql)) {
            log.error("\n分表前的sql：{}", oldsql);

            // 查询单独处理
            if (mappedStatement.getSqlCommandType().compareTo(SqlCommandType.SELECT) == 0) {
                RowBounds rowBounds = (RowBounds) args[2];
                ResultHandler resultHandler = (ResultHandler) args[3];

                CacheKey cacheKey;
                BoundSql boundSqlnew;
                if (args.length == 4) {
                    boundSqlnew = mappedStatement.getBoundSql(parameter);
                    cacheKey = executor.createCacheKey(mappedStatement, parameter, rowBounds, boundSqlnew);
                } else {
                    boundSqlnew = (BoundSql) args[5];
                    cacheKey = (CacheKey) args[4];
                }

                String sql = boundSqlnew.getSql();
                String newsql = sql.replace("db_father", "db_father_" + "2400");
                String newsql2 = sql.replace("DB_FATHER", "DB_FATHER_" + "2400");
                newsql = StringUtils.equals(sql,newsql) ? newsql2 : newsql;
                //通过反射修改sql语句
                Field field = boundSqlnew.getClass().getDeclaredField("sql");
                field.setAccessible(true);
                field.set(boundSqlnew, newsql);

                log.error("\n分表后的sql：{}", boundSqlnew.getSql());
                // 直接query可能会应该其他拦截器
                return executor.query(mappedStatement, parameter, rowBounds, resultHandler, cacheKey, boundSqlnew);
            } else {
                // 分表策略，这个地方可以使用注解，配置类，配置数据库等方式
                String newsql = oldsql.replace("db_father", "db_father_" + "2400");
                String newsql2 = oldsql.replace("DB_FATHER", "DB_FATHER_" + "2400");
                newsql = StringUtils.equals(oldsql,newsql) ? newsql2 : newsql;

                //重新生成一个BoundSql对象
                BoundSql bs = new BoundSql(mappedStatement.getConfiguration(),newsql,boundSql.getParameterMappings(),parameter);

                //重新生成一个MappedStatement对象
                MappedStatement newMappedStatement = copyMappedStatement(mappedStatement, new BoundSqlSqlSource(bs));
                //赋回给实际执行方法所需的参数中
                args[0] = newMappedStatement;
            }
            log.error("\n分表后的sql：{}", ((MappedStatement)args[0]).getBoundSql(parameter).getSql());
        }
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object o) {
        return Plugin.wrap(o, this);
    }

    @Override
    public void setProperties(Properties properties) {
        log.info("setProperties " + properties.toString());
    }

    /**
     * MybatisSubmeterInterceptor.java
     *
     * @Description: 判断是否需要分表
     * @Author:
     * @Date 2022/7/29 11:53
     * @param sql sql
     * @return boolean
     * @Version 1.0.0
     **/
    private boolean isReplaceTableName(String sql) {
        // 测试数据，将ddb_father  重新定位到db_father_2400,
        // 这个地方可以使用注解，配置类，配置数据库等方式判断
        if (sql.contains("db_father") || sql.contains("DB_FATHER")) {
            return true;
        }
        return false;
    }

    /**
     * MybatisSubmeterInterceptor.java
     *
     * @Description: 生成一个新的mappedStatement
     * @Date 2022/7/29 13:49
     * @param ms
     * @param newSqlSource
     * @return org.apache.ibatis.mapping.MappedStatement
     * @Version 1.0.0
     **/
    private MappedStatement copyMappedStatement (MappedStatement ms, SqlSource newSqlSource) {
        MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());

        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.keyGenerator(ms.getKeyGenerator());
        if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
            builder.keyProperty(String.join(",",ms.getKeyProperties()));
        }
        builder.timeout(ms.getTimeout());
        builder.parameterMap(ms.getParameterMap());
        builder.resultMaps(ms.getResultMaps());
        builder.resultSetType(ms.getResultSetType());
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());
        return builder.build();
    }

    /***
     * MappedStatement构造器接受的是SqlSource
     * 实现SqlSource接口，将BoundSql封装进去
     */
    public static class BoundSqlSqlSource implements SqlSource {
        private BoundSql boundSql;
        public BoundSqlSqlSource(BoundSql boundSql) {
            this.boundSql = boundSql;
        }
        @Override
        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }
    }
}

2.3 使用中间件分表

  • ShardingSphere Sharding-JDBC