在分布式开发中,某些表数据量比较大时,通常会选择分库分表作为解决方案。常用的中间件有mycat, sharding jdbc等。由于sharding jdbc比较轻量,而且是在客户端做的分库分表,使用起来比较方便。所以我也用sharding jdbc 做了一个例子。后面有源码下载。
前期准备:
1. 两个数据库:shard_jdbc_1, shard_jdbc_2
1. 在两个数据库中都创建表 user_info, 另外为了测试不分表的情况,在shard_jdbc_1中创建 user_account 表, 语句分别如下:Create TABLE `user_info` (
`user_id` bigint(19) NOT NULL,
`user_name` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`account` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`password` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
Create TABLE `user_account` (
`flow_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`account` int(255) NULL DEFAULT NULL,
PRIMARY KEY (`flow_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2. 工程搭建,采用spring boot, maven 工程。比较简单:
3. 分库分表的核心代码,采用在java代码中配置,另外也可以采用配置文件方式。可以参考官网说明.package com.yihaomen.shardingjava.config;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
/**
* 分片数据源配置,返回 ShardingDataSource
*/
@Configuration
@MapperScan(basePackages = "com.yihaomen.shardingjava.mapper", sqlSessionFactoryRef = "sqlSessionFactory")
public class DataSourceConfig {
@Bean
@Primary
public DataSource shardingDataSource() throws SQLException {
ShardingRuleConfiguration src = new ShardingRuleConfiguration();
// 默认的分库策略
src.setDefaultDataSourceName("ds0");
//src.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new DBShardAlgo()));
// 默认的分表策略
//src.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new TblPreShardAlgo(), new TblRangeShardAlgo()));
// 为user_info表设置分库分表策略、算法
src.getTableRuleConfigs().add(getUserTableRuleConfiguration());
// 数据源名和数据源的映射表
return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), src, new Properties());
}
// 配置数据源
private MapcreateDataSourceMap() {
Mapresult = new HashMap<>();
result.put("ds0", DataSourceUtil.createDataSource("shard_jdbc_1"));
result.put("ds1", DataSourceUtil.createDataSource("shard_jdbc_2"));
return result;
}
// 事务管理器
@Bean
public DataSourceTransactionManager transactitonManager(DataSource shardingDataSource) {
return new DataSourceTransactionManager(shardingDataSource);
}
// 为user_info表设置分库分表策略、算法
public TableRuleConfiguration getUserTableRuleConfiguration() {
TableRuleConfiguration result = new TableRuleConfiguration("user_info", "ds${0..1}.user_info");
result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
// 分库策略
result.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new DBShardAlgo(), new DBRangeShardAlgo() ));
// 分表策略
result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new TblPreShardAlgo(), new TblRangeShardAlgo() ));
return result;
}
private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE", "user_id");
return result;
}
}
4. 测试方法有两种,一是通过单原测试类:UserShardingTest, 另一种方式,通过Controller 测试都行。
5. 结果对于user_info 成功实现分库分表, 对于user_account 采用默认库,不分库分表.
6. 源代码下载: