-
资料
分库分表的概念及应用场景详解
分库分表带来的一些问题
sharding-jdbc水平垂直分库分表环境搭建
sharding-jdbc水平分库分表实战
sharding-jdbc垂直分库分表实战 -
水平分库分表目的
1.水平分表,缓解单表数据量大的性能问题.
2.水平分库,缓解单库高并发请求的性能问题 -
水平分表实现
实现方案:将一张表的数据,根据某种算法,分摊到多表中 public class springJdbcTest { public static void main(String[] args) throws SQLException { //获取数据源 DataSource dataSource = getDataSource(); //获取jdbctemplate JdbcTemplate jdbcTemplate = getJdbcTemplate(dataSource); //执行语句 for(int i=1;i<=10;i++) { jdbcTemplate.update("insert into t_user(user_name,user_age,user_type) values (?,?,?)",i,i,i); } } //创建JdbcTemplate public static JdbcTemplate getJdbcTemplate( DataSource dataSource){ JdbcTemplate jdbcTemplate = new JdbcTemplate(); jdbcTemplate.setDataSource(dataSource); return jdbcTemplate; } //创建数据源,这个数据源是sharding-jdbc级别的数据源 public static DataSource getDataSource() throws SQLException { // 配置真实数据源 Map<String, DataSource> dataSourceMap = new HashMap<>(); // 配置第 1 个数据源 DruidDataSource dataSource1 = new DruidDataSource(); dataSource1.setDriverClassName("com.mysql.jdbc.Driver"); dataSource1.setUrl("jdbc:mysql://localhost:3306/ds0?characterEncoding=utf-8&useSSL=false"); dataSource1.setUsername("root"); dataSource1.setPassword("123456"); dataSourceMap.put("ds0", dataSource1); // 配置表规则bean ShardingRuleConfiguration shardingRuleConfiguration=new ShardingRuleConfiguration(); //配置逻辑表和实际表分布情况,$是一个变量,取值范围是[1,2],表都在一个数据源ds0 TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration("t_user","ds0.t_user_$->{1..2}"); //实际表生成主键策略,分表用数据库自增会重复,必须用第三方生成的id KeyGeneratorConfiguration keyGeneratorConfiguration = new KeyGeneratorConfiguration("snowflake","user_id"); //配置选择表的路由配置,当user_id是偶数数据存t_user_1中 //配置选择表的路由配置,当user_id是奇数数据存t_user_2中 ShardingStrategyConfiguration shardingStrategyConfiguration = new InlineShardingStrategyConfiguration("user_id","t_user_$->{user_id%2+1}"); //配置选择库的路由配置,因为是分表,所以库是固定的 ShardingStrategyConfiguration shardingStrategyConfiguration1 = new InlineShardingStrategyConfiguration("user_id","ds0"); shardingRuleConfiguration.setTableRuleConfigs(Arrays.asList(tableRuleConfiguration)); shardingRuleConfiguration.setDefaultKeyGeneratorConfig(keyGeneratorConfiguration); shardingRuleConfiguration.setDefaultTableShardingStrategyConfig(shardingStrategyConfiguration); shardingRuleConfiguration.setDefaultDatabaseShardingStrategyConfig(shardingStrategyConfiguration1); return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfiguration, new Properties()); } }
-
水平分表效果
-
水平分库实现
public class springJdbcTest { public static void main(String[] args) throws SQLException { //获取数据源 DataSource dataSource = getDataSource(); //获取jdbctemplate JdbcTemplate jdbcTemplate = getJdbcTemplate(dataSource); //执行语句 for(int i=1;i<=10;i++) { jdbcTemplate.update("insert into t_user(user_name,user_age,user_type) values (?,?,?)",i,i,i); } } public static JdbcTemplate getJdbcTemplate( DataSource dataSource){ JdbcTemplate jdbcTemplate = new JdbcTemplate(); jdbcTemplate.setDataSource(dataSource); return jdbcTemplate; } public static DataSource getDataSource() throws SQLException { // 配置真实数据源 Map<String, DataSource> dataSourceMap = new HashMap<>(); // 配置第 1 个数据源 DruidDataSource dataSource1 = new DruidDataSource(); dataSource1.setDriverClassName("com.mysql.jdbc.Driver"); dataSource1.setUrl("jdbc:mysql://localhost:3306/ds0"); dataSource1.setUsername("root"); dataSource1.setPassword("123456"); dataSourceMap.put("ds0", dataSource1); // 配置第 2 个数据源 DruidDataSource dataSource2 = new DruidDataSource(); dataSource2.setDriverClassName("com.mysql.jdbc.Driver"); dataSource2.setUrl("jdbc:mysql://localhost:3307/ds0"); dataSource2.setUsername("root"); dataSource2.setPassword("123456"); dataSourceMap.put("ds1", dataSource2); // 配置表规则bean ShardingRuleConfiguration shardingRuleConfiguration=new ShardingRuleConfiguration(); //配置逻辑表和实际表分布情况,两个t_user表分布在不同的数据库中 TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration("t_user","ds$->{0..1}.t_user"); //实际表生成主键策略,分表用数据库自增会重复,必须用第三方生成的id KeyGeneratorConfiguration keyGeneratorConfiguration = new KeyGeneratorConfiguration("snowflake","user_id"); //配置选择表的路由配置,这里我们分库了,表名称一样了 ShardingStrategyConfiguration shardingStrategyConfiguration = new InlineShardingStrategyConfiguration("user_id","t_user"); //配置选择库的路由配置,当user_id是奇数,数据存在ds0数据库中 //配置选择库的路由配置,当user_id是偶数,数据存在ds1数据库中 ShardingStrategyConfiguration shardingStrategyConfiguration1 = new InlineShardingStrategyConfiguration("user_id","ds$->{user_id%2}"); shardingRuleConfiguration.setTableRuleConfigs(Arrays.asList(tableRuleConfiguration)); shardingRuleConfiguration.setDefaultKeyGeneratorConfig(keyGeneratorConfiguration); shardingRuleConfiguration.setDefaultTableShardingStrategyConfig(shardingStrategyConfiguration); shardingRuleConfiguration.setDefaultDatabaseShardingStrategyConfig(shardingStrategyConfiguration1); return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfiguration, new Properties()); } }
-
水平分库效果
sharding-jdbc水平分库分表实战
于 2020-10-17 22:24:13 首次发布