SpringBoot + ShardingSphere实现读写分离，分库分表

SpringBoot + ShardingSphere实现读写分离，分库分表

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

一. *** 作的工程目录

二.读写分离

application.yml

​
spring:
    shardingsphere:
        datasource:
            names: master,slave
            #数据源配置
            master:
                driver-class-name: com.mysql.jdbc.Driver
                type: com.alibaba.druid.pool.DruidDataSource
                url: jdbc:mysql://localhost:3306/master0?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
                username: root
                password: 123456
            slave:
                driver-class-name: com.mysql.jdbc.Driver
                type: com.alibaba.druid.pool.DruidDataSource
                url: jdbc:mysql://localhost:3306/slave0?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
                username: root
                password: 123456

        #读写分离配置(主库写，从库读)
        masterslave:
            load-balance-algorithm-type: round_robin
            name: ms
            #写库
            master-data-source-name: master
            #读库
            slave-data-source-names: slave
    props:
        sql:
            show: true
    main:
        allow-bean-definition-overriding: true

2.1 代码生成的测试

​
package com.aliyun.readWrite.test.controller;


import com.aliyun.readWrite.test.entity.VideoRoom;
import com.aliyun.readWrite.test.service.IVideoRoomService;
import com.aliyun.util.Result;
import com.aliyun.util.StatusCode;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;



@Slf4j
@RestController
@RequestMapping("/videoRoom")
public class VideoRoomController {

    @Autowired
    public IVideoRoomService videoRoomService;

    @PostMapping("/save")
    public Result save(@RequestBody VideoRoom videoRoom){
        videoRoomService.save(videoRoom);
        return new Result(StatusCode.SUCCESS,"保存成功");
    }

    @GetMapping("/get/{id}")
    public Result get(@PathVariable("id") String id){
        VideoRoom videoRoom = videoRoomService.getById(id);
        return new Result(StatusCode.SUCCESS,"查询成功",videoRoom);
    }

}

​

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

三.分库分表

application.yml

spring:
    shardingsphere:
        props:
            sql:
                show: true
        main:
            allow-bean-definition-overriding: true
        datasource:
            names: master0,master1
            #数据源配置
            master0:
                driver-class-name: com.mysql.jdbc.Driver
                type: com.alibaba.druid.pool.DruidDataSource
                url: jdbc:mysql://localhost:3306/master0?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
                username: root
                password: 123456
            master1:
                driver-class-name: com.mysql.jdbc.Driver
                type: com.alibaba.druid.pool.DruidDataSource
                url: jdbc:mysql://localhost:3306/master1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
                username: root
                password: 123456

        #分库分表配置
        sharding:
            #分库
            default-database-strategy:
                inline:
                    sharding-column:  age
                    algorithm-expression: master$->{age % 2}      #根据年龄分库
            #分表
            tables:
                tab_user:
                    actual-data-nodes: master$->{0..1}.tab_user$->{0..1}
                    table-strategy:
                        inline:
                            sharding-column: id
                            algorithm-expression: tab_user$->{id % 2}

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

package com.aliyun.config;

import org.apache.ibatis.session.SqlSessionFactory;
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.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

@Configuration
@ComponentScan(basePackageClasses = DataSourceConfig.class)
@MapperScan(basePackages = "com.aliyun.shard.mapper")
public class DataSourceConfig {

    
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource());
        sessionFactory.setFailFast(true);
        sessionFactory.setMapperLocations(resolver.getResources("classpath:/mapper/system
public final class PreciseModuloShardingTableAlgorithm implements PreciseShardingAlgorithm {

    @Override
    public String doSharding(final Collection tableNames, final PreciseShardingValue shardingValue) {
        for (String each : tableNames) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }
}

package com.aliyun.config;

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;


public final class DataSourceUtil {

    private static final String HOST = "localhost";

    private static final int PORT = 3306;

    private static final String USER_NAME = "root";

    private static final String PASSWORD = "123456";

    public static DataSource createDataSource(final String dataSourceName) {
        DruidDataSource result = new DruidDataSource();
        result.setDriverClassName(com.mysql.jdbc.Driver.class.getName());
        result.setUrl(String.format("jdbc:mysql://%s:%s/%s?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8", HOST, PORT, dataSourceName));
        result.setUsername(USER_NAME);
        result.setPassword(PASSWORD);
        return result;
    }
}

3.2 代码生成的测试

package com.aliyun.shard.controller;

import com.aliyun.shard.entity.TabUser;
import com.aliyun.shard.service.ITabUserService;
import com.aliyun.util.Result;
import com.aliyun.util.StatusCode;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.PostConstruct;
import java.util.List;



@Slf4j
@RestController
@RequestMapping("/tabUser")
public class TabUserController {

    @Autowired
    public ITabUserService tabUserService;

    
    List userList = Lists.newArrayList();

    
    @PostConstruct
    private void getData() {
        userList.add(new TabUser(1L,"小小", "女", 3));
        userList.add(new TabUser(2L,"爸爸", "男", 30));
        userList.add(new TabUser(3L,"妈妈", "女", 28));
        userList.add(new TabUser(4L,"爷爷", "男", 64));
        userList.add(new TabUser(5L,"奶奶", "女", 62));
    }

    @PostMapping("batchSaveUser")
    public Result batchSaveUser() {
        return new Result(StatusCode.SUCCESS,tabUserService.insertForEach(userList));
    }

    @GetMapping("/findAll")
    public Result findAll(){
        List tabUserList = tabUserService.findAll();
        return new Result(StatusCode.SUCCESS,"查询成功",tabUserList);
    }

}

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

四.工程源码

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