一、分库分表思路
1、垂直分表
拆分列。基于列进行。
根据业务耦合性，将关联度低的不同表存储在不同的数据库，与微服务类似，按照业务独立划分，每个微服务使用单独的一个数据库。也可将字段较多
的表拆分新建一张扩展表，将不常用或字段较大的字段拆分出去到扩展表中。
在字段很多的情况下（例如一个大表有100多个字段），通过"大表拆小表"，更便于开发与维护，也能避免跨页问题，MySQL底层是通过数据页存储的，
一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加
载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。
优点：业务解耦清晰；高并发下，提升一定程度的IO，数据库连接数、单机硬件资源瓶颈。
缺点：部分表无法join，只能通过接口聚合，提升了开发复杂度；分布式事务处理复杂。
2、水平分表
拆分行。基于行进行。
分为库内分表，分库分表，根据表内数据内在的逻辑关系，将同一个表按条件分散到多个数据库或多个表中，每张表中包含一部分数据，从而使单张表的数
据量变小。
库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同
一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。
优点：不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力；应用端改造较小，不需要拆分业务模块。
缺点：跨分片的事务一致性难以保证；跨库的join关联性能较差；数据不易维护
二、Spring动态数据源实现分库
在分库分表的情况下，在执行SQL时选择连接不同的数据源（库）的思路：配置多个数据源加到动态数据源对象中，根据实际的情况动态切换到相应的数据源中。
如存放订单信息的有10个库，每个库中有100张表，根据用户的ID做分库分表。可以选择ID的某一位的值作为选择的第几个库，某两位的值作为对应的表名。
实现：在service中使用AOP拦截具有某个注解的方法，根据方法中的参数的用户信息，按照上面的规则指定要连接的数据源。
环境2.1.0.RELEASE，MySQL
1、所有的数据源信息配置：application.properties
#dataSource sharding
tvbss.jdbc.username=${config.tvbss.jdbc.username}
tvbss.jdbc.password=${config.tvbss.jdbc.password}
tvbss.jdbc.driver=${config.tvbss.jdbc.driver}
tvbss.jdbc.server00=${config.tvbss.jdbc.server00}
tvbss.jdbc.server01=${config.tvbss.jdbc.server01}
tvbss.jdbc.server02=${config.tvbss.jdbc.server02}
tvbss.jdbc.server03=${config.tvbss.jdbc.server03}
tvbss.jdbc.server04=${config.tvbss.jdbc.server04}
tvbss.jdbc.server05=${config.tvbss.jdbc.server05}
tvbss.jdbc.server06=${config.tvbss.jdbc.server06}
tvbss.jdbc.server07=${config.tvbss.jdbc.server07}
tvbss.jdbc.server08=${config.tvbss.jdbc.server08}
tvbss.jdbc.server09=${config.tvbss.jdbc.server09}
2、读取配置信息到属性类中
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component; import javax.annotation.PostConstruct;
import java.util.HashMap;
import java.util.Map; /**
* 数据源属性配置
*
* @author yangyongjie
* @date 2019/9/27
* @desc
*/
@Component
@ConfigurationProperties(prefix = "tvbss.jdbc")
public class ShardingProperties {
private String username;
private String password;
private String driver;
private String server00;
private String server01;
private String server02;
private String server03;
private String server04;
private String server05;
private String server06;
private String server07;
private String server08;
private String server09; private Map<String, String> urlsMap = new HashMap<>(); @PostConstruct
public void initUrlList() {
urlsMap.put("server00", server00);
urlsMap.put("server01", server01);
urlsMap.put("server02", server02);
urlsMap.put("server03", server03);
urlsMap.put("server04", server04);
urlsMap.put("server05", server05);
urlsMap.put("server06", server06);
urlsMap.put("server07", server07);
urlsMap.put("server08", server08);
urlsMap.put("server09", server09);
} }
3、指定动态数据源的关键类
import com.xxx.common.bean.BaseShardedBean;
import com.xxx.config.datasource.prop.ShardingProperties;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.util.StringUtils; import java.util.HashMap;
import java.util.Map; /**
* 动态数据源切换
* 方式：配置多个数据源加到动态数据源对象中，根据实际的情况动态的切换到相应的数据源
*/
public class DynamicDataSource extends AbstractRoutingDataSource { private static Logger LOGGER = LoggerFactory.getLogger(DynamicDataSource.class); @Autowired
private ShardingProperties shardingProperties; /**
* 连接池分配的最大连接数
*/
private int maxActive = GenericObjectPool.DEFAULT_MAX_ACTIVE; /**
* 空闲时保留的最大连接数
*/
private int maxIdle = GenericObjectPool.DEFAULT_MAX_IDLE; /**
* 空闲时保留的最少连接数
*/
private int minIdle = GenericObjectPool.DEFAULT_MIN_IDLE; /**
* 当连接池没有空闲时等待的最大毫秒数，小于0表示无限期等待
*/
private long maxWait = GenericObjectPool.DEFAULT_MAX_WAIT;
/**
* 连接池的初始连接数
*/
private int initialSize = 0; /**
* 返回数据源dataSource的key值，实现数据源切换的关键方法
* 在determineTargetDataSource方法中根据key获取目标连接数据源
*
* @return
*/
@Override
protected Object determineCurrentLookupKey() {
Object value = CurentDataSourceHolder.getCurentDataSource();
if (StringUtils.isEmpty(value)) {
value = BaseShardedBean.defaultKey();
}
return value;
} /**
* 启动时初始化所有的目标数据源
*/
@Override
public void afterPropertiesSet() {
super.setTargetDataSources(buildDataSource());
super.afterPropertiesSet();
} /**
* 根据配置文件的配置构建数据源
*
* @return
*/
private Map<Object, Object> buildDataSource() {
Map<Object, Object> dataSources = new HashMap<>(16);
Map<String, String> urls = shardingProperties.getUrlsMap();
if (MapUtils.isEmpty(urls)) {
LOGGER.error("获取配置的数据库urls为空");
return dataSources;
}
String driver = shardingProperties.getDriver();
String username = shardingProperties.getUsername();
String password = shardingProperties.getPassword();
urls.forEach((key, value) -> {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(driver);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setUrl(value);
dataSource.setMaxWait(maxWait);
dataSource.setMaxIdle(maxIdle);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
dataSources.put(key, dataSource);
});
return dataSources;
} /**
* 数据源存放对象，用于动态切换用
*/
public static class CurentDataSourceHolder { private static ThreadLocal<Object> holder = new ThreadLocal<>(); /**
* 设置当前线程的数据源
*
* @param source
*/
public static void setCurentDataSource(Object source) {
holder.set(source);
} /**
* 获取当前线程的数据源
*
* @return
*/
public static Object getCurentDataSource() {
return holder.get();
} /**
* 清楚数据源
*/
public static void remove() {
holder.remove();
}
} public int getMaxActive() {
return maxActive;
} public void setMaxActive(int maxActive) {
this.maxActive = maxActive;
} public int getMaxIdle() {
return maxIdle;
} public void setMaxIdle(int maxIdle) {
this.maxIdle = maxIdle;
} public int getMinIdle() {
return minIdle;
} public void setMinIdle(int minIdle) {
this.minIdle = minIdle;
} public long getMaxWait() {
return maxWait;
} public void setMaxWait(long maxWait) {
this.maxWait = maxWait;
} public int getInitialSize() {
return initialSize;
} public void setInitialSize(int initialSize) {
this.initialSize = initialSize;
} }
afterPropertiesSet()方法初始化了所有的目标数据源
determineCurrentLookupKey()方法用来获取当前线程变量中指定的当前数据源的key，若没有，则使用默认数据源
AbstractRoutingDataSource ：
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
@Nullable
private Map<Object, Object> targetDataSources; // 多个目标数据源
@Nullable
private Object defaultTargetDataSource; // 默认的目标数据源
private boolean lenientFallback = true;
private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();
@Nullable
private Map<Object, DataSource> resolvedDataSources;
@Nullable
private DataSource resolvedDefaultDataSource;
/**
* Specify the map of target DataSources, with the lookup key as key.
* The mapped value can either be a corresponding {@link javax.sql.DataSource}
* instance or a data source name String (to be resolved via a
* {@link #setDataSourceLookup DataSourceLookup}).
* <p>The key can be of arbitrary type; this class implements the
* generic lookup process only. The concrete key representation will
* be handled by {@link #resolveSpecifiedLookupKey(Object)} and
* {@link #determineCurrentLookupKey()}.
*/
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
this.targetDataSources = targetDataSources;
}
/**
* Specify the default target DataSource, if any.
* <p>The mapped value can either be a corresponding {@link javax.sql.DataSource}
* instance or a data source name String (to be resolved via a
* {@link #setDataSourceLookup DataSourceLookup}).
* <p>This DataSource will be used as target if none of the keyed
* {@link #setTargetDataSources targetDataSources} match the
* {@link #determineCurrentLookupKey()} current lookup key.
*/
public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
this.defaultTargetDataSource = defaultTargetDataSource;
}
/**
* Specify whether to apply a lenient fallback to the default DataSource
* if no specific DataSource could be found for the current lookup key.
* <p>Default is "true", accepting lookup keys without a corresponding entry
* in the target DataSource map - simply falling back to the default DataSource
* in that case.
* <p>Switch this flag to "false" if you would prefer the fallback to only apply
* if the lookup key was {@code null}. Lookup keys without a DataSource
* entry will then lead to an IllegalStateException.
* @see #setTargetDataSources
* @see #setDefaultTargetDataSource
* @see #determineCurrentLookupKey()
*/
public void setLenientFallback(boolean lenientFallback) {
this.lenientFallback = lenientFallback;
}
/**
* Set the DataSourceLookup implementation to use for resolving data source
* name Strings in the {@link #setTargetDataSources targetDataSources} map.
* <p>Default is a {@link JndiDataSourceLookup}, allowing the JNDI names
* of application server DataSources to be specified directly.
*/
public void setDataSourceLookup(@Nullable DataSourceLookup dataSourceLookup) {
this.dataSourceLookup = (dataSourceLookup != null ? dataSourceLookup : new JndiDataSourceLookup());
}
@Override
public void afterPropertiesSet() {
if (this.targetDataSources == null) {
throw new IllegalArgumentException("Property 'targetDataSources' is required");
}
this.resolvedDataSources = new HashMap<>(this.targetDataSources.size());
this.targetDataSources.forEach((key, value) -> {
Object lookupKey = resolveSpecifiedLookupKey(key);
DataSource dataSource = resolveSpecifiedDataSource(value);
this.resolvedDataSources.put(lookupKey, dataSource);
});
if (this.defaultTargetDataSource != null) {
this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
}
}
/**
* Resolve the given lookup key object, as specified in the
* {@link #setTargetDataSources targetDataSources} map, into
* the actual lookup key to be used for matching with the
* {@link #determineCurrentLookupKey() current lookup key}.
* <p>The default implementation simply returns the given key as-is.
* @param lookupKey the lookup key object as specified by the user
* @return the lookup key as needed for matching
*/
protected Object resolveSpecifiedLookupKey(Object lookupKey) {
return lookupKey;
}
/**
* Resolve the specified data source object into a DataSource instance.
* <p>The default implementation handles DataSource instances and data source
* names (to be resolved via a {@link #setDataSourceLookup DataSourceLookup}).
* @param dataSource the data source value object as specified in the
* {@link #setTargetDataSources targetDataSources} map
* @return the resolved DataSource (never {@code null})
* @throws IllegalArgumentException in case of an unsupported value type
*/
protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException {
if (dataSource instanceof DataSource) {
return (DataSource) dataSource;
}
else if (dataSource instanceof String) {
return this.dataSourceLookup.getDataSource((String) dataSource);
}
else {
throw new IllegalArgumentException(
"Illegal data source value - only [javax.sql.DataSource] and String supported: " + dataSource);
}
}
@Override
public Connection getConnection() throws SQLException {
return determineTargetDataSource().getConnection();
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return determineTargetDataSource().getConnection(username, password);
}
@Override
@SuppressWarnings("unchecked")
public <T> T unwrap(Class<T> iface) throws SQLException {
if (iface.isInstance(this)) {
return (T) this;
}
return determineTargetDataSource().unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return (iface.isInstance(this) || determineTargetDataSource().isWrapperFor(iface));
}
/**
* Retrieve the current target DataSource. Determines the
* {@link #determineCurrentLookupKey() current lookup key}, performs
* a lookup in the {@link #setTargetDataSources targetDataSources} map,
* falls back to the specified
* {@link #setDefaultTargetDataSource default target DataSource} if necessary.
* @see #determineCurrentLookupKey()
*/
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
Object lookupKey = determineCurrentLookupKey();
DataSource dataSource = this.resolvedDataSources.get(lookupKey);
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource;
}
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
}
return dataSource;
}
/**
* Determine the current lookup key. This will typically be
* implemented to check a thread-bound transaction context.
* <p>Allows for arbitrary keys. The returned key needs
* to match the stored lookup key type, as resolved by the
* {@link #resolveSpecifiedLookupKey} method.
*/
@Nullable
protected abstract Object determineCurrentLookupKey();
}
4、Spring容器中数据源配置类
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager; /**
* 数据源配置文件
*
* @author yangyongjie
* @date 2019/9/25
* @desc
*/
@Configuration
public class ShardingDataSourceConfig { private static final Logger LOGGER = LoggerFactory.getLogger(ShardingDataSourceConfig.class); /**
* 数据源
*
* @return
*/
@Bean
public DynamicDataSource shardingDataSource() {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
dynamicDataSource.setMaxActive(8);
dynamicDataSource.setMaxIdle(2);
dynamicDataSource.setMinIdle(1);
dynamicDataSource.setMaxWait(60000);
return dynamicDataSource;
} @Bean
public SqlSessionFactory shardingSqlSessionFactory() {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
// 扫描相关mapper文件
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
SqlSessionFactory sqlSessionFactory = null;
try {
sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:/mapper/**/*Mapper.xml"));
sqlSessionFactoryBean.setDataSource(shardingDataSource());
// sqlSessionFactoryBean.setConfigLocation();
sqlSessionFactory = sqlSessionFactoryBean.getObject();
} catch (Exception e) {
LOGGER.error("创建SqlSessionFactory:"+e.getMessage(),e);
}
return sqlSessionFactory;
} @Bean
public SqlSessionTemplate shardingSqlSessionTemplate() {
return new SqlSessionTemplate(shardingSqlSessionFactory());
} /**
* sharding数据源事务管理器
*
* @return
*/
@Bean
public DataSourceTransactionManager shardingTransactionManager() {
return new DataSourceTransactionManager(shardingDataSource());
} }
5、指定了默认数据源，提供 generateShardingKey 方法，用来根据当前bean分配库名，并保存在线程本地变量中，以及生成表名，为bean的tableIndex属性赋值
public abstract class BaseShardedBean {
static String DEFAULT_SHARDING_SCHAME = "server";
static String DEFAULT_SHARDING_INDEX = "00";
public abstract Object dbKey();
public abstract String tableKey();
/**
* 子类必须指定分片的key
*/
public abstract Object genShardedKey();
/**
* 子类必须根据bean的映射选择表名
*/
public abstract String genTableName();
/**
* 子类必须制定业务数据库类型
*/
public abstract String genDbSchema();
/**
* 默认的库名,server00
*
* @return
*/
public static Object defaultKey() {
return DEFAULT_SHARDING_SCHAME + DEFAULT_SHARDING_INDEX;
}
/**
* 生成库名和表名
*/
public void generateShardingKey() {
Object key = dbKey();
key = StringUtils.isEmpty(key) ? defaultKey() : key;
DynamicDataSource.CurentDataSourceHolder.setCurentDataSource(key);
tableKey();
}
}
6、具体的库表映射实体，继承了BaseShardedBean ，用户实体需继承它，以及顶一个公共的分库分表规则，即所有的实体类对应的分库分表规则都相同，不同的是库名前缀和表名前缀
**
* 支持分片的Bean，只支持单表操作 required ${genShardedKey} 指定类型的分片key ${genTableName} 指定类型映射的表名
**/
public abstract class ShardedMapperBean extends BaseShardedBean { @JsonIgnore
protected String orderByClause;
@JsonIgnore
protected boolean distinct;
@JsonIgnore
protected String tableName;
@JsonIgnore
private String tableIndex;
@JsonIgnore
protected Object shardKey; public String getOrderByClause() {
return orderByClause;
} public void setOrderByClause(String orderByClause) {
this.orderByClause = orderByClause;
} public boolean isDistinct() {
return distinct;
} public void setDistinct(boolean distinct) {
this.distinct = distinct;
} public String getTableName() {
return tableName;
} public void setTableName(String tableName) {
this.tableName = tableName;
} public String getTableIndex() {
return tableIndex;
} public void setTableIndex(String tableIndex) {
this.tableIndex = tableIndex;
} @Override
public Object dbKey() {
String key = genShardedKey() == null ? null : String.valueOf(genShardedKey());
if (StringUtils.isEmpty(key)) {
return null;
}
return genDbSchema() + "0" + Integer.parseInt(getLastSub4to2(key)) / 10; // 库名为key倒数三四位除10取整，即倒数第四位的值，一共分10个库
} @Override
public String tableKey() {
this.setTableName(genTableName());
String key = genShardedKey() == null ? null : String.valueOf(genShardedKey());
if (StringUtils.isEmpty(key)) {
setTableIndex(genTableName());
return null;
} else {
String tableIndex = genTableName() + "_" + getLastSub2(key); // 表名为倒数一二位的值，一共分100张表
setTableIndex(tableIndex);
return tableIndex;
}
} private static String getLastSub4to2(String key) {
if (StringUtils.isEmpty(key) || key.length() < 4) {
return "";
}
int keyLen = key.length();
return key.substring(keyLen - 4, keyLen - 2);
} private static String getLastSub2(String key) {
if (StringUtils.isEmpty(key) || key.length() < 2) {
return "";
}
int keyLen = key.length();
return key.substring(keyLen - 2);
}
}
如具体的实体继承ShardedMapperBean 之后重写的方法，自定义库名前缀和表名前缀，以及用什么作为分库分表的key
@Override
public Object genShardedKey() {
return id;
} @Override
public String genTableName() {
return "order";
} @Override
public String genDbSchema() {
return "databaseNamePrefix";
}
7、最后使用AOP拦截方法的执行，给DynamicDataSource中的静态内部类CurentDataSourceHolder中的线程本地变量指定实际连接的数据源即可。
import com.xxx.common.bean.BaseShardedBean;
import com.xxx.config.datasource.DynamicDataSource;
import org.apache.commons.lang3.StringUtils;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional; import java.lang.reflect.Method; /**
* 事务注解的拦截器，为事务管理器为shardingTransactionManager的方法指定数据源
*
* @author yangyongjie
* @date 2019/9/30
* @desc
*/
@Aspect
@Component
public class TransactionAspect { private static final String SHARD_TRANSACTION_MANAGER = "shardingTransactionManager"; @Pointcut("@annotation(org.springframework.transaction.annotation.Transactional)")
private void transactionbPointCut() {
// donothing
} /**
* 方法执行前的拦截,指定数据源
* 因此如果需要动态指定数据源，则方法参数bean需要继承ShardedMapperBean并重写genShardedKey()、genTableName()和genDbSchema()
*
* @param joinPoint
*/
@Before(value = "transactionbPointCut()")
public void beforeTransaction(JoinPoint joinPoint) {
// 获取方法上的注解
MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
Method pointCutMethod = methodSignature.getMethod();
Transactional ann = pointCutMethod.getAnnotation(Transactional.class);
if (!StringUtils.equals(ann.value(), SHARD_TRANSACTION_MANAGER)) {
return;
}
// 获取方法参数
Object[] methodArgs = joinPoint.getArgs();
for (Object args : methodArgs) {
if (args instanceof BaseShardedBean) {
((BaseShardedBean) args).generateShardingKey();
break;
}
} } /**
* 方法执行后删除存放数据源key的线程本地变量
*/
@After(value = "transactionbPointCut()")
public void afterTransaction() {
DynamicDataSource.CurentDataSourceHolder.remove();
}
}
三、水平拆分数据分片规则
1、根据数值范围
按照时间区间或者ID区间来切分。例如：按日期将不同月甚至是日的数据分散到不同的库中；将userId为1~9999的记录分到第一个库，10000~20000的分到第二个库，以此类推。
2、根据数值取模
一般采用hash取模mod的切分方式，如将Order表根据userId字段切分到10个库中，余数为0的放到第一个库，余数为1的放到第二个库，依此类推。这样同一个用户的数据会分散到同一个库中。
end.