PostgreSql分库分表
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);
......
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', '我是没有子表的数据');
可以看到，前四条数据按触发器中的判断分别进入了四个子表，第五条因为没有对50000的数据处理，所以会执行成功，但是不会插入数据
查询主表可以看到所有的数据，这是逻辑数据，实际数据是存在子表中的
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
5788
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
