背景:
数据库中的数据量不一定是可控的，在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作，增删改查的开销也会越来越大；另外，由于无法进行分布式式部署，而一台服务器的资源（CPU、磁盘、内存、IO等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。
mybatis分库分表,核心是使用Spring的AbstractRoutingDataSource进行数据源的动态切换、一般有两种方式:一个是基于Mybatis的拦截器插件，一个是基于Spring的AOP的拦截器。
以下基于aop方式小试牛刀：
public enum DatabaseEnum { order1,order2; }
ShardingContextHolder是通过线程局部变量保存数据源的key值
public class DatabaseContextHolder { public static final ThreadLocal<DatabaseEnum> contextHolder = new ThreadLocal<>(); public static void setDatabaseEnum(DatabaseEnum databaseEnum) { contextHolder.set(databaseEnum); } public static DatabaseEnum getDatabaseEnum() { return contextHolder.get(); } public static void clearDatabaseEnum() { contextHolder.remove(); } }
重写AbstractRoutingDataSource的determineCurrentLookupKey方法
public class DynamicDatasource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DatabaseContextHolder.getDatabaseEnum(); } }
@Configuration public class MybatisConfig { @Autowired private Environment environment; @Autowired DefaultListableBeanFactory beanFactory; @Bean public DataSource order1DataSource() { HikariConfig hikariConfig = new HikariConfig(); hikariConfig.setDriverClassName(environment.getProperty("order.sharding.datasource.order_1.driver-class-name")); hikariConfig.setJdbcUrl(environment.getProperty("order.sharding.datasource.order_1.jdbc-url")); hikariConfig.setUsername(environment.getProperty("order.sharding.datasource.order_1.user-name")); hikariConfig.setPassword(environment.getProperty("order.sharding.datasource.order_1.password")); hikariConfig.setMinimumIdle(environment.getProperty("order.sharding.datasource.order_1.minimum-idle", Integer.class)); // return new HikariDataSource(hikariConfig); } @Bean public DataSource order2DataSource() { HikariConfig hikariConfig = new HikariConfig(); hikariConfig.setDriverClassName(environment.getProperty("order.sharding.datasource.order_2.driver-class-name")); hikariConfig.setJdbcUrl(environment.getProperty("order.sharding.datasource.order_2.jdbc-url")); hikariConfig.setUsername(environment.getProperty("order.sharding.datasource.order_2.user-name")); hikariConfig.setPassword(environment.getProperty("order.sharding.datasource.order_2.password")); hikariConfig.setMinimumIdle(environment.getProperty("order.sharding.datasource.order_2.minimum-idle", Integer.class)); // return new HikariDataSource(hikariConfig); } @Bean @Primary public DynamicDatasource datasource(@Qualifier("order1DataSource") DataSource order1Datasource, @Qualifier("order2DataSource") DataSource order2Datasource) { Map<Object, Object> map = new HashMap<>(); map.put(DatabaseEnum.order1, order1Datasource); map.put(DatabaseEnum.order2, order2Datasource); DynamicDatasource dynamicDatasource = new DynamicDatasource(); dynamicDatasource.setTargetDataSources(map); return dynamicDatasource; } @Bean public SqlSessionFactory sqlSessionFactory(@Autowired DynamicDatasource ds) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(ds); sqlSessionFactoryBean.setTypeAliasesPackage(environment.getProperty("order.sharding.mybatis.typeAliasesPackage")); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(environment.getProperty("order.sharding.mybatis.mapperLocations"))); //set plugin TODO return sqlSessionFactoryBean.getObject(); } }
dao层编写
@Mapper public interface OrderMapper { public OrderEntity getByUserId(@Param("userId") int userId, @Param("shard") ShardingInfo shardingInfo); }
aop切面
@Aspect @Component public class DataSourceAspect { @Before("execution(* com.sq.order.data.service.myDao.*.*(..))") public void setDatasourcePgKey(JoinPoint point) { Object[] args = point.getArgs(); for (Object arg : args) { if (arg instanceof ShardingInfo) { ShardingInfo shardingInfo = (ShardingInfo)arg; if (shardingInfo.getShardKey() != null) { int i = Math.floorDiv((shardingInfo.getShardKey() % 512), 256); if (i == 0) { DatabaseContextHolder.setDatabaseEnum(DatabaseEnum.order1); System.out.println("使用数据源 ====== order1"); } else { DatabaseContextHolder.setDatabaseEnum(DatabaseEnum.order2); System.out.println("使用数据源 ======= order2"); } } } } } }
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.sq.order.data.service.myDao.OrderMapper"> <select id="getByUserId" parameterType="map" resultType="orderEntity"> select * FROM ${shard.dbName}.order where booking_user_id = #{userId} </select> </mapper>
