devbox
Li_阴宅
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Sharding Sphere分库分表-java配置_org.apache.shardingsphere.api.config.sharding.shar

作者：Li_阴宅 | 2024-06-25 16:07:44

赞

踩

org.apache.shardingsphere.api.config.sharding.shardingruleconfiguration设置s

注意：分库分表要注意id不能再用数据库自增方式。可以采用雪花算法（新增的表），自定义分片算法（旧表）

分片策略配置

对于分片策略存有数据源分片策略和表分片策略两种维度

  • 数据源分片策略：

    对应于DatabaseShardingStrategy。用于配置数据被分配的目标数据源

  • 表分片策略
    对应于TableShardingStrategy。用于配置数据被分配的目标表，该目标表存在与该数据的目标数据源内。故表分片策略是依赖与数据源分片策略的结果的

表：

  1. CREATE TABLE `t_user0` (
  2.   `id` bigint(20) NOT NULL,
  3.   `name` varchar(64) DEFAULT NULL COMMENT '名称',
  4.   `city_id` int(12) DEFAULT NULL COMMENT '城市',
  5.   `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  6.   `phone` varchar(32) DEFAULT NULL COMMENT '电话',
  7.   `email` varchar(32) DEFAULT NULL COMMENT '邮箱',
  8.   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  9.   `password` varchar(32) DEFAULT NULL COMMENT '密码',
  10.   PRIMARY KEY (`id`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  12.  
  13. CREATE TABLE `t_user1` (
  14.   `id` bigint(20) NOT NULL,
  15.   `name` varchar(64) DEFAULT NULL COMMENT '名称',
  16.   `city_id` int(12) DEFAULT NULL COMMENT '城市',
  17.   `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  18.   `phone` varchar(32) DEFAULT NULL COMMENT '电话',
  19.   `email` varchar(32) DEFAULT NULL COMMENT '邮箱',
  20.   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  21.   `password` varchar(32) DEFAULT NULL COMMENT '密码',
  22.   PRIMARY KEY (`id`)
  23. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

库：

   test0,test1

pom:

  1.         <dependency>
  2.             <groupId>org.apache.shardingsphere</groupId>
  3.             <artifactId>sharding-jdbc-core</artifactId>
  4.             <version>4.1.1</version>
  5.         </dependency>

properties:

  1. spring.application.name=shardingjdbc
  2. server.port=8080
  3.  
  4. datasource0.url=jdbc:mysql://localhost:3306/test0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
  5. datasource0.driver-class-name=com.mysql.cj.jdbc.Driver
  6. datasource0.type=com.alibaba.druid.pool.DruidDataSource
  7. datasource0.username=root
  8. datasource0.password=123456
  9.  
  10. datasource1.url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
  11. datasource1.driver-class-name=com.mysql.cj.jdbc.Driver
  12. datasource1.type=com.alibaba.druid.pool.DruidDataSource
  13. datasource1.username=root
  14. datasource1.password=123456
  15.  
  16. mybatis.mapper-locations=classpath:com/example/shardingjdbc/mapper/*.xml
  17. mybatis.type-aliases-package=com.example.shardingjdbc.**.entity
  19. #打印sql
  20. spring.shardingsphere.props.sql.show=true
  22. #
  23. spring.datasource.druid.filters=stat,wall,log4j2
  25. spring.datasource.druid.web-stat-filter.enabled=true
  26. spring.datasource.druid.web-stat-filter.url-pattern=/*
  27. spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
  28. spring.datasource.druid.web-stat-filter.session-stat-enable=true
  29. spring.datasource.druid.web-stat-filter.session-stat-max-count=1000
  30. #spring.datasource.druid.web-stat-filter.principal-session-name=
  31. #spring.datasource.druid.web-stat-filter.principal-cookie-name=
  32. #spring.datasource.druid.web-stat-filter.profile-enable=
  34. spring.datasource.druid.stat-view-servlet.enabled=true
  35. spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
  36. spring.datasource.druid.stat-view-servlet.reset-enable=false
  37. spring.datasource.druid.stat-view-servlet.login-username=admin
  38. spring.datasource.druid.stat-view-servlet.login-password=123
  39. spring.datasource.druid.stat-view-servlet.allow=127.0.0.1
  40. spring.datasource.druid.stat-view-servlet.deny=

 

主键策略：

  1. package com.example.shardingjdbc.config;
  2.  
  3. import org.apache.shardingsphere.core.strategy.keygen.SnowflakeShardingKeyGenerator;
  4. import org.springframework.context.annotation.Bean;
  5. import org.springframework.context.annotation.Configuration;
  6.  
  7. @Configuration
  8. public class KeyIdConfig {
  9.  
  10.     @Bean("userKeyGenerator")
  11.     public SnowflakeShardingKeyGenerator userKeyGenerator() {
  12.         return new SnowflakeShardingKeyGenerator();
  13.     }
  14.  
  15.     @Bean("orderKeyGenerator")
  16.     public SnowflakeShardingKeyGenerator orderKeyGenerator() {
  17.         return new SnowflakeShardingKeyGenerator();
  18.     }
  19. }

 分表分库策略：

  1. package com.example.shardingjdbc.sharding;
  2.  
  3. import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
  4. import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
  5.  
  6. import java.util.Collection;
  7.  
  8. public class UserShardingAlgorithm {
  9.  
  10.     public static final DatabaseShardingAlgorithm databaseShardingAlgorithm = new DatabaseShardingAlgorithm();
  11.  
  12.     public static final TableShardingAlgorithm tableShardingAlgorithm = new TableShardingAlgorithm();
  13.  
  14.     static class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
  15.         @Override
  16.         public String doSharding(Collection<String> databaseNames, PreciseShardingValue<Long> shardingValue) {
  17.             for (String database : databaseNames) {
  18.                 if (database.endsWith(String.valueOf(shardingValue.getValue() % 2))) {
  19.                     return database;
  20.                 }
  21.             }
  22.  
  23.             return "";
  24.         }
  25.     }
  26.  
  27.     static class TableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
  28.         @Override
  29.         public String doSharding(Collection<String> tableNames, PreciseShardingValue<Long> shardingValue) {
  30.             for (String table : tableNames) {
  31.                 if (table.endsWith(String.valueOf(shardingValue.getValue() % 2))) {
  32.                     return table;
  33.                 }
  34.             }
  35.  
  36.             return "";
  37.         }
  38.     }
  39. }

配置： 

1.分表不分库

 DataSourceConfig:

  1. package com.example.shardingjdbc.config;
  2.  
  3. import com.alibaba.druid.pool.DruidDataSource;
  4. import com.example.shardingjdbc.sharding.UserShardingAlgorithm;
  5. import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
  6. import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
  7. import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
  8. import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
  9. import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
  10. import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
  11. import org.springframework.beans.factory.annotation.Value;
  12. import org.springframework.context.annotation.Bean;
  13. import org.springframework.context.annotation.Configuration;
  14.  
  15. import javax.sql.DataSource;
  16. import java.util.HashMap;
  17. import java.util.Map;
  18. import java.util.Properties;
  19.  
  20. @Configuration
  21. public class DataSourceConfig {
  22.  
  23.     @Value("${datasource0.url}")
  24.     private String url0;
  25.     @Value("${datasource0.username}")
  26.     private String username0;
  27.     @Value("${datasource0.password}")
  28.     private String password0;
  29.     @Value("${datasource0.driver-class-name}")
  30.     private String driverClassName0;
  31.  
  32.     @Value(("${spring.datasource.druid.filters}"))
  33.     private String filters;
  34.  
  35.     @Bean("dataSource")
  36.     public DataSource dataSource() {
  37.         try {
  38.             DruidDataSource dataSource0 = new DruidDataSource();
  39.             dataSource0.setDriverClassName(this.driverClassName0);
  40.             dataSource0.setUrl(this.url0);
  41.             dataSource0.setUsername(this.username0);
  42.             dataSource0.setPassword(this.password0);
  43.             dataSource0.setFilters(this.filters);
  44.             //分库设置
  45.             Map<String, DataSource> dataSourceMap = new HashMap<>(2);
  46.             //添加数据库database0
  47.             dataSourceMap.put("ds0", dataSource0);
  48.             // 配置 t_user 表规则
  49.             TableRuleConfiguration userRuleConfiguration = new TableRuleConfiguration("t_user", "ds0.t_user${0..1}");
  50.             //此处可以自定义
  51.             //userRuleConfiguration.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "t_user${id % 2}"));
  52.             StandardShardingStrategyConfiguration standardShardingStrategyConfiguration =
  53.                     new StandardShardingStrategyConfiguration("id", UserShardingAlgorithm.tableShardingAlgorithm);
  54.             userRuleConfiguration.setTableShardingStrategyConfig(standardShardingStrategyConfiguration);
  55.             //主键
  56.             userRuleConfiguration.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "id", getProperties()));
  57.             // Sharding全局配置
  58.             ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
  59.             shardingRuleConfiguration.getTableRuleConfigs().add(userRuleConfiguration);
  60.             // 创建数据源
  61.             DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfiguration, new Properties());
  62.             return dataSource;
  63.         } catch (Exception ex) {
  64.             ex.printStackTrace();
  65.             return null;
  66.         }
  67.     }
  68.     private static Properties getProperties() {
  69.         Properties result = new Properties();
  70.         result.setProperty("worker.id", "123");
  71.         return result;
  72.     }
  73. }

2.分库不分表

  1. package com.example.shardingjdbc.config;
  2.  
  3. import com.alibaba.druid.pool.DruidDataSource;
  4. import com.example.shardingjdbc.sharding.UserShardingAlgorithm;
  5. import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
  6. import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
  7. import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
  8. import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
  9. import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
  10. import org.springframework.beans.factory.annotation.Value;
  11. import org.springframework.context.annotation.Bean;
  12. import org.springframework.context.annotation.Configuration;
  13.  
  14. import javax.sql.DataSource;
  15. import java.util.*;
  16.  
  17. @Configuration
  18. public class DataSourceConfig {
  19.  
  20.     @Value("${datasource0.url}")
  21.     private String url0;
  22.     @Value("${datasource0.username}")
  23.     private String username0;
  24.     @Value("${datasource0.password}")
  25.     private String password0;
  26.     @Value("${datasource0.driver-class-name}")
  27.     private String driverClassName0;
  28.  
  29.     @Value("${datasource1.url}")
  30.     private String url1;
  31.     @Value("${datasource1.username}")
  32.     private String username1;
  33.     @Value("${datasource1.password}")
  34.     private String password1;
  35.     @Value("${datasource1.driver-class-name}")
  36.     private String driverClassName1;
  37.  
  38.     @Value(("${spring.datasource.druid.filters}"))
  39.     private String filters;
  40.  
  41.     @Bean("dataSource")
  42.     public DataSource dataSource() {
  43.         try {
  44.             DruidDataSource dataSource0 = new DruidDataSource();
  45.             dataSource0.setDriverClassName(this.driverClassName0);
  46.             dataSource0.setUrl(this.url0);
  47.             dataSource0.setUsername(this.username0);
  48.             dataSource0.setPassword(this.password0);
  49.             dataSource0.setFilters(this.filters);
  50.             DruidDataSource dataSource1 = new DruidDataSource();
  51.             dataSource1.setDriverClassName(this.driverClassName1);
  52.             dataSource1.setUrl(this.url1);
  53.             dataSource1.setUsername(this.username1);
  54.             dataSource1.setPassword(this.password1);
  55.             dataSource1.setFilters(this.filters);
  56.             //分库设置
  57.             Map<String, DataSource> dataSourceMap = new HashMap<>(2);
  58.             //添加两个数据库database0和database1
  59.             dataSourceMap.put("ds0", dataSource0);
  60.             dataSourceMap.put("ds1", dataSource1);
  61.             // 配置 t_user 表规则
  62.             TableRuleConfiguration userRuleConfiguration = new TableRuleConfiguration("t_user", "ds${0..1}.t_user0");
  63.             //id 是 偶数的都插入到了 test0 库的 t_user0 表中， 奇数的都插入到了 test1 库中的 t_user1 表中
  64.             /**
  65.              * #表策略 行表达式分表规则
  66.              * sharding.jdbc.config.sharding.tables.user.actual-data-nodes=ds_$->{0..1}.user_$->{0..4}
  67.              * sharding.jdbc.config.sharding.tables.user.table-strategy.inline.sharding-column=id
  68.              * sharding.jdbc.config.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 5}
  69.              * sharding.jdbc.config.sharding.tables.user.key-generator-column-name=id
  70.              */
  71.             // 行表达式分库规则
  72.             /**
  73.              * #库策略
  74.              * sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id
  75.              * harding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds_$->{id % 2}
  76.              */
  77.             StandardShardingStrategyConfiguration standardShardingStrategyConfiguration =
  78.                     new StandardShardingStrategyConfiguration("id", UserShardingAlgorithm.databaseShardingAlgorithm);
  79.             userRuleConfiguration.setDatabaseShardingStrategyConfig(standardShardingStrategyConfiguration);
  80.             // Sharding全局配置
  81.             ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
  82.             shardingRuleConfiguration.getTableRuleConfigs().add(userRuleConfiguration);
  83.             // 创建数据源
  84.             DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfiguration, new Properties());
  85.             return dataSource;
  86.         } catch (Exception ex) {
  87.             ex.printStackTrace();
  88.             return null;
  89.         }
  90.     }
  91. }

3.分库分表

  1. package com.example.shardingjdbc.config;
  2.  
  3. import com.alibaba.druid.pool.DruidDataSource;
  4. import com.example.shardingjdbc.sharding.UserShardingAlgorithm;
  5. import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
  6. import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
  7. import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
  8. import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
  9. import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
  10. import org.springframework.beans.factory.annotation.Value;
  11. import org.springframework.context.annotation.Bean;
  12. import org.springframework.context.annotation.Configuration;
  13.  
  14. import javax.sql.DataSource;
  15. import java.util.*;
  16.  
  17. @Configuration
  18. public class DataSourceConfig {
  19.  
  20.     @Value("${datasource0.url}")
  21.     private String url0;
  22.     @Value("${datasource0.username}")
  23.     private String username0;
  24.     @Value("${datasource0.password}")
  25.     private String password0;
  26.     @Value("${datasource0.driver-class-name}")
  27.     private String driverClassName0;
  28.  
  29.     @Value("${datasource1.url}")
  30.     private String url1;
  31.     @Value("${datasource1.username}")
  32.     private String username1;
  33.     @Value("${datasource1.password}")
  34.     private String password1;
  35.     @Value("${datasource1.driver-class-name}")
  36.     private String driverClassName1;
  37.  
  38.     @Value(("${spring.datasource.druid.filters}"))
  39.     private String filters;
  40.  
  41.     @Bean("dataSource")
  42.     public DataSource dataSource() {
  43.         try {
  44.             DruidDataSource dataSource0 = new DruidDataSource();
  45.             dataSource0.setDriverClassName(this.driverClassName0);
  46.             dataSource0.setUrl(this.url0);
  47.             dataSource0.setUsername(this.username0);
  48.             dataSource0.setPassword(this.password0);
  49.             dataSource0.setFilters(this.filters);
  50.  
  51.             DruidDataSource dataSource1 = new DruidDataSource();
  52.             dataSource1.setDriverClassName(this.driverClassName1);
  53.             dataSource1.setUrl(this.url1);
  54.             dataSource1.setUsername(this.username1);
  55.             dataSource1.setPassword(this.password1);
  56.             dataSource1.setFilters(this.filters);
  57.             //分库设置
  58.             Map<String, DataSource> dataSourceMap = new HashMap<>(2);
  59.             //添加两个数据库database0和database1
  60.             dataSourceMap.put("ds0", dataSource0);
  61.             dataSourceMap.put("ds1", dataSource1);
  62.             /**
  63.              * 除了基本的分库分表规则以外，还有一些其他的配置，比如绑定表。这里先不一一举例了，参照官方文档配即可。
  64.              * 　　举个例子：现在有 order, order_detail两张表，1:1的关系。
  65.              * 　　在配置的时候，应该将相同 order_id 的 order 记录 和 order_detail 记录 映射到相同尾号的表中。这样方便连接查询。比如都插入到  order0, order_detail0中。
  66.              * 　　如果配置了绑定关系，那么只会产生一条查询 select * from order0 as o join order_detail0 as d  on o.order_id = d.order_id。
  67.              * 　　否则会产生笛卡儿积查询，
  68.              * 　　　　select * from order0 as o join order_detail0 as d  on o.order_id = d.order_id。
  69.              * 　　　　select * from order0 as o join order_detail1 as d  on o.order_id = d.order_id。
  70.              * 　　　　select * from order1 as o join order_detail0 as d  on o.order_id = d.order_id。
  71.              * 　　　　select * from order1 as o join order_detail1 as d  on o.order_id = d.order_id。
  72.              */
  73.             // 配置 t_user 表规则 库：test0 test1 表 t_user1 t_user0
  74.             TableRuleConfiguration userRuleConfiguration = new TableRuleConfiguration("t_user", "ds${0..1}.t_user${0..1}");
  75.             //id 是 偶数的都插入到了 test0 库的 t_user0 表中， 奇数的都插入到了 test1 库中的 t_user1 表中
  76.             /**
  77.              * #表策略 行表达式分表规则
  78.              * sharding.jdbc.config.sharding.tables.user.actual-data-nodes=ds_$->{0..1}.user_$->{0..1}
  79.              * sharding.jdbc.config.sharding.tables.user.table-strategy.inline.sharding-column=id
  80.              * sharding.jdbc.config.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 5}
  81.              * sharding.jdbc.config.sharding.tables.user.key-generator-column-name=id
  82.              */
  83.             //sharding.jdbc.config.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name: com.forezp.sharedingjdbcmasterslavetables.MyPreciseShardingAlgorithm
  84.             //此处可以自定义
  85.             userRuleConfiguration.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "t_user${id % 2}"));
  86.             // 行表达式分库规则
  87.             /**
  88.              * #库策略
  89.              * sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id
  90.              * harding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds_$->{id % 2}
  91.              */
  92.             //根据城市分库
  93.             userRuleConfiguration.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("city_id", "ds${city_id % 2}"));
  94.             // Sharding全局配置
  95.             ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
  96.             shardingRuleConfiguration.getTableRuleConfigs().add(userRuleConfiguration);
  97.             // 创建数据源
  98.             DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfiguration, new Properties());
  99.             return dataSource;
  100.         } catch (Exception ex) {
  101.             ex.printStackTrace();
  102.             return null;
  103.         }
  104.     }
  105. }

注意：

  1.事务需要手动配置。

  2.手动开启sql打印

  3.跨节点连接查询问题（分页、排序）

  4.多数据源管理问题 

     分库分表根据业务划分：要注意各表之间的依赖关系，其有个体表，和全局表，全局表可以在每一个库中存放一份，以便提高查询的性能。如：用户表和用户地址表这两张表是相互依赖的，订单明细表和订单表也是相互依赖的，可以存访到一个库中中，减少网路请求I/O。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Li_阴宅/article/detail/756727
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号