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

SpringBoot + ShardingSphere实现读写分离，分库分表_shardingsphere 分库分表和读写分离的配置

作者：小惠珠哦 | 2024-06-25 19:27:34

赞

踩

shardingsphere 分库分表和读写分离的配置

分布式项目在通过一定的服务拆分，项目运行一段时间以后，随着用户和使用资源的不断增大，数据库的性能也会面临瓶颈。单表数据量过大，超高并发的读写引发的锁问题，导致系统运行加载越来越慢，不时出现系统假死的现象，数据库的优化已将近极限，几乎没有空间，这时就需要考虑读写分离，分库分表（这里主要是水平的分库分表）的策略，来减少对同一数据库的访问的压力。

一.操作的工程目录

二.读写分离

application.yml

  1. ​
  2. spring:
  3.     shardingsphere:
  4.         datasource:
  5.             names: master,slave
  6.             #数据源配置
  7.             master:
  8.                 driver-class-name: com.mysql.jdbc.Driver
  9.                 type: com.alibaba.druid.pool.DruidDataSource
  10.                 url: jdbc:mysql://localhost:3306/master0?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
  11.                 username: root
  12.                 password: 123456
  13.             slave:
  14.                 driver-class-name: com.mysql.jdbc.Driver
  15.                 type: com.alibaba.druid.pool.DruidDataSource
  16.                 url: jdbc:mysql://localhost:3306/slave0?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
  17.                 username: root
  18.                 password: 123456
  19.  
  20.         #读写分离配置(主库写，从库读)
  21.         masterslave:
  22.             load-balance-algorithm-type: round_robin
  23.             name: ms
  24.             #写库
  25.             master-data-source-name: master
  26.             #读库
  27.             slave-data-source-names: slave
  28.     props:
  29.         sql:
  30.             show: true
  31.     main:
  32.         allow-bean-definition-overriding: true
  33.

2.1 代码生成的测试

  1. ​
  2. package com.aliyun.readWrite.test.controller;
  3.  
  4.  
  5. import com.aliyun.readWrite.test.entity.VideoRoom;
  6. import com.aliyun.readWrite.test.service.IVideoRoomService;
  7. import com.aliyun.util.Result;
  8. import com.aliyun.util.StatusCode;
  9. import lombok.extern.slf4j.Slf4j;
  10. import org.springframework.beans.factory.annotation.Autowired;
  11. import org.springframework.web.bind.annotation.*;
  12.  
  13. /**
  14. * <p>
  15.     * 房间表 前端控制器
  16.     * </p>
  17. *
  18. * @author haige
  19. * @since 2021-11-24
  20. */
  21.  
  22. @Slf4j
  23. @RestController
  24. @RequestMapping("/videoRoom")
  25. public class VideoRoomController {
  26.  
  27.     @Autowired
  28.     public IVideoRoomService videoRoomService;
  29.  
  30.     @PostMapping("/save")
  31.     public Result save(@RequestBody VideoRoom videoRoom){
  32.         videoRoomService.save(videoRoom);
  33.         return new Result(StatusCode.SUCCESS,"保存成功");
  34.     }
  35.  
  36.     @GetMapping("/get/{id}")
  37.     public Result get(@PathVariable("id") String id){
  38.         VideoRoom videoRoom = videoRoomService.getById(id);
  39.         return new Result(StatusCode.SUCCESS,"查询成功",videoRoom);
  40.     }
  41.  
  42. }
  43.  
  44. ​

实现了接口调用时master库写，slave库读的效果

三.分库分表

application.yml

  1. spring:
  2.     shardingsphere:
  3.         props:
  4.             sql:
  5.                 show: true
  6.         main:
  7.             allow-bean-definition-overriding: true
  8.         datasource:
  9.             names: master0,master1
  10.             #数据源配置
  11.             master0:
  12.                 driver-class-name: com.mysql.jdbc.Driver
  13.                 type: com.alibaba.druid.pool.DruidDataSource
  14.                 url: jdbc:mysql://localhost:3306/master0?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
  15.                 username: root
  16.                 password: 123456
  17.             master1:
  18.                 driver-class-name: com.mysql.jdbc.Driver
  19.                 type: com.alibaba.druid.pool.DruidDataSource
  20.                 url: jdbc:mysql://localhost:3306/master1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
  21.                 username: root
  22.                 password: 123456
  23.  
  24.         #分库分表配置
  25.         sharding:
  26.             #分库
  27.             default-database-strategy:
  28.                 inline:
  29.                     sharding-column:  age
  30.                     algorithm-expression: master$->{age % 2}      #根据年龄分库
  31.             #分表
  32.             tables:
  33.                 tab_user:
  34.                     actual-data-nodes: master$->{0..1}.tab_user$->{0..1}
  35.                     table-strategy:
  36.                         inline:
  37.                             sharding-column: id
  38.                             algorithm-expression: tab_user$->{id % 2}

3.1 分库分表数据源及策略配置的注入类

  1. package com.aliyun.config;
  2.  
  3. import org.apache.ibatis.session.SqlSessionFactory;
  4. import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
  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.mybatis.spring.SqlSessionFactoryBean;
  11. import org.mybatis.spring.annotation.MapperScan;
  12. import org.springframework.context.annotation.Bean;
  13. import org.springframework.context.annotation.ComponentScan;
  14. import org.springframework.context.annotation.Configuration;
  15. import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
  16.  
  17. import javax.sql.DataSource;
  18. import java.sql.SQLException;
  19. import java.util.HashMap;
  20. import java.util.Map;
  21. import java.util.Properties;
  22.  
  23. @Configuration
  24. @ComponentScan(basePackageClasses = DataSourceConfig.class)
  25. @MapperScan(basePackages = "com.aliyun.shard.mapper")
  26. public class DataSourceConfig {
  27.  
  28.     /**
  29.      * SqlSessionFactory注入
  30.      */
  31.     @Bean
  32.     public SqlSessionFactory sqlSessionFactory() throws Exception {
  33.         PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
  34.         SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
  35.         sessionFactory.setDataSource(dataSource());
  36.         sessionFactory.setFailFast(true);
  37.         sessionFactory.setMapperLocations(resolver.getResources("classpath:/mapper/system/*Mapper.xml"));
  38.         return sessionFactory.getObject();
  39.     }
  40.  
  41.     @Bean
  42.     public DataSource dataSource() throws SQLException {
  43.         ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
  44.         shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
  45.         shardingRuleConfig.getBindingTableGroups().add("tab_user");
  46. //        shardingRuleConfig.getBroadcastTables().add("t_config");
  47.         //TODO 根据年龄分库 一共分为2个库
  48.         shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("age", "master${age % 2}"));
  49.         //TODO 根据ID分表  一共分为2张表
  50.         shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("id", new PreciseModuloShardingTableAlgorithm()));
  51.         return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, new Properties());
  52.     }
  53.  
  54.     private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
  55.         KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE", "id");
  56.         return result;
  57.     }
  58.  
  59.     TableRuleConfiguration getOrderTableRuleConfiguration() {
  60.         TableRuleConfiguration result = new TableRuleConfiguration("tab_user", "master${0..1}.tab_user${0..1}");
  61.         result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
  62.         return result;
  63.     }
  64.  
  65.  
  66.     Map<String, DataSource> createDataSourceMap() {
  67.         Map<String, DataSource> result = new HashMap<>();
  68.         result.put("master0", DataSourceUtil.createDataSource("master0"));
  69.         result.put("master1", DataSourceUtil.createDataSource("master1"));
  70.         return result;
  71.     }
  72. }
  1. package com.aliyun.config;
  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. /**
  9.  * 表分片规则
  10.  */
  11. public final class PreciseModuloShardingTableAlgorithm implements PreciseShardingAlgorithm<Long> {
  12.  
  13.     @Override
  14.     public String doSharding(final Collection<String> tableNames, final PreciseShardingValue<Long> shardingValue) {
  15.         for (String each : tableNames) {
  16.             if (each.endsWith(shardingValue.getValue() % 2 + "")) {
  17.                 return each;
  18.             }
  19.         }
  20.         throw new UnsupportedOperationException();
  21.     }
  22. }
  1. package com.aliyun.config;
  2.  
  3. import com.alibaba.druid.pool.DruidDataSource;
  4.  
  5. import javax.sql.DataSource;
  6.  
  7. /**
  8.  * @Description: 数据源配置
  9.  *
  10.  * @author xub
  11.  * @date 2019/10/16 下午7:47
  12.  */
  13. public final class DataSourceUtil {
  14.  
  15.     private static final String HOST = "localhost";
  16.  
  17.     private static final int PORT = 3306;
  18.  
  19.     private static final String USER_NAME = "root";
  20.  
  21.     private static final String PASSWORD = "123456";
  22.  
  23.     public static DataSource createDataSource(final String dataSourceName) {
  24.         DruidDataSource result = new DruidDataSource();
  25.         result.setDriverClassName(com.mysql.jdbc.Driver.class.getName());
  26.         result.setUrl(String.format("jdbc:mysql://%s:%s/%s?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8", HOST, PORT, dataSourceName));
  27.         result.setUsername(USER_NAME);
  28.         result.setPassword(PASSWORD);
  29.         return result;
  30.     }
  31. }

3.2 代码生成的测试

  1. package com.aliyun.shard.controller;
  2.  
  3. import com.aliyun.shard.entity.TabUser;
  4. import com.aliyun.shard.service.ITabUserService;
  5. import com.aliyun.util.Result;
  6. import com.aliyun.util.StatusCode;
  7. import com.google.common.collect.Lists;
  8. import lombok.extern.slf4j.Slf4j;
  9. import org.springframework.beans.factory.annotation.Autowired;
  10. import org.springframework.web.bind.annotation.*;
  11. import org.springframework.web.bind.annotation.RestController;
  12.  
  13. import javax.annotation.PostConstruct;
  14. import java.util.List;
  15.  
  16. /**
  17. * <p>
  18.     *  前端控制器
  19.     * </p>
  20. *
  21. * @author haige
  22. * @since 2021-11-26
  23. */
  24.  
  25. @Slf4j
  26. @RestController
  27. @RequestMapping("/tabUser")
  28. public class TabUserController {
  29.  
  30.     @Autowired
  31.     public ITabUserService tabUserService;
  32.  
  33.     /**
  34.      * 模拟插入数据
  35.      */
  36.     List<TabUser> userList = Lists.newArrayList();
  37.  
  38.     /**
  39.      * 初始化插入数据
  40.      */
  41.     @PostConstruct
  42.     private void getData() {
  43.         userList.add(new TabUser(1L,"小小", "女", 3));
  44.         userList.add(new TabUser(2L,"爸爸", "男", 30));
  45.         userList.add(new TabUser(3L,"妈妈", "女", 28));
  46.         userList.add(new TabUser(4L,"爷爷", "男", 64));
  47.         userList.add(new TabUser(5L,"奶奶", "女", 62));
  48.     }
  49.  
  50.     @PostMapping("batchSaveUser")
  51.     public Result batchSaveUser() {
  52.         return new Result(StatusCode.SUCCESS,tabUserService.insertForEach(userList));
  53.     }
  54.  
  55.     @GetMapping("/findAll")
  56.     public Result findAll(){
  57.         List<TabUser> tabUserList = tabUserService.findAll();
  58.         return new Result(StatusCode.SUCCESS,"查询成功",tabUserList);
  59.     }
  60.  
  61. }

实现了5条数据分别添加到了tab_user0，tab_user1，tab_user2三张表，在查询findAll数据时又能返回三张表的所有数据。

四.工程源码

感兴趣的猿友可以了解--> 下载源码

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

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

  

闽ICP备14008679号