shardingsphere 分库分表实现

这里主要讲讲shardingsphere 的内容

 

首先要明白数据库的瓶颈

                      不管是IO瓶颈，还是CPU瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是，可用数据库连接少甚至无连接可用。接下来就可以想象了吧（并发量、吞吐量、崩溃）。

1.

都知道数据库连接资源有限的吧，想想一个数据库如何在打并发的时候支撑的下来  这也就是IO的概念  也就是IO瓶颈

2.

数据量也会造成瓶颈，数据量过大，或者sql关联复杂，导致查询扫描内容过大过于复杂，造成效率低下，也就是cpu瓶颈

                    那怎么去解决这些问题呢？

这也就是很多公司愿意去搭建微服务的架构了，一个服务提供者就搞一个库，就完美了，但是也有比较重的模块，例如订单模块，大型电商平台订单量可想而知，可能针对这单独的模块也要进行拆分，多库多表，读写分离等等等

 

-------------------------一个数据库资源不够，那就两个，两个不够，那就三个！！！

-------------------------数据量大，那我们把它拆分出来，一千条数据我们拆成十张表，每表一百条，按照一定的规则存储，一个i查询进来查询，直接匹配到对应的表去查询，从要扫描1000变扫描100去找了量就少了

 

分库分表的技术有很多个

         我这边就用shardingsphere来实现一个简单的例子，帮助那些还在摸索这一块的小白

 为方便看效果我还集成了swagger来调试看看效果，上图就是我做出来的最终效果

主要技术有:

springboot

shardingsphere

swagger

mybatis-plus

mysql

这是我非常简单的一个项目结构，好了直接贴代码

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.zkb</groupId>
    <artifactId>sharding</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.14.RELEASE</version>
        <relativePath/>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC2</version>
            <exclusions>
                <exclusion>
                    <groupId>com.google.guava</groupId>
                    <artifactId>guava</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>4.0.0-RC2</version>
            <exclusions>
                <exclusion>
                    <groupId>com.google.guava</groupId>
                    <artifactId>guava</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8.1</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.60</version>
        </dependency>

        <dependency>
            <groupId>javax.xml.bind</groupId>
            <artifactId>jaxb-api</artifactId>
            <version>2.3.1</version>
        </dependency>
        <dependency>
            <groupId>com.sun.xml.bind</groupId>
            <artifactId>jaxb-impl</artifactId>
            <version>2.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.glassfish.jaxb</groupId>
            <artifactId>jaxb-runtime</artifactId>
            <version>2.3.1</version>
        </dependency>


        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
            <exclusions>
                <exclusion>
                    <groupId>io.swagger</groupId>
                    <artifactId>swagger-annotations</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>io.swagger</groupId>
                    <artifactId>swagger-models</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-annotations</artifactId>
            <version>1.5.22</version>
        </dependency>

        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-models</artifactId>
            <version>1.5.22</version>
        </dependency>
        <!-- swagger-ui -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>

        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>swagger-bootstrap-ui</artifactId>
            <version>1.9.1</version>
        </dependency>


    </dependencies>

    <build>
        <finalName>${project.artifactId}</finalName>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <fork>true</fork>
                    <mainClass>com.zkb.ShardingApplication</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

server:
    port: 8080
spring:
    shardingsphere:
        datasource:
            ds0:
                driver-class-name: com.mysql.jdbc.Driver
                jdbc-url: jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&autoReconnect=true&failOverReadOnly=false&zeroDateTimeBehavior=convertToNull
                password: root
                type: com.zaxxer.hikari.HikariDataSource
                username: root
            ds1:
                driver-class-name: com.mysql.jdbc.Driver
                jdbc-url: jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&autoReconnect=true&failOverReadOnly=false&zeroDateTimeBehavior=convertToNull
                password: root
                type: com.zaxxer.hikari.HikariDataSource
                username: root
            names: ds0,ds1
        props:
            sql:
                show: true
        sharding:
            default-database-strategy:
                inline:
                    algorithm-expression: ds$->{id % 2}
                    sharding-column: id
            tables:
                t_user:
                    actual-data-nodes: ds$->{0..1}.t_user_$->{0..2}
                    key-generator:
                        column: id
                        type: SNOWFLAKE
                    table-strategy:
                        inline:
                            algorithm-expression: t_user_$->{id % 3}
                            sharding-column: id

package com.zkb;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.embedded.tomcat.TomcatConnectorCustomizer;
import org.springframework.boot.web.embedded.tomcat.TomcatServletWebServerFactory;
import org.springframework.boot.web.servlet.server.ConfigurableServletWebServerFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@EnableSwagger2
@SpringBootApplication
@EnableAsync
@EnableScheduling
@EnableTransactionManagement
public class ShardingApplication {

  public static void main(String[] args) {
    SpringApplication.run(ShardingApplication.class, args);
  }

  @Bean
  public ConfigurableServletWebServerFactory configurableServletWebServerFactory() {
    TomcatServletWebServerFactory factory = new TomcatServletWebServerFactory();
    factory.addConnectorCustomizers(
        (TomcatConnectorCustomizer)
            connector -> connector.setProperty("relaxedQueryChars", "|{}[]"));
    return factory;
  }
}

package com.zkb.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import java.util.Date;

import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import lombok.experimental.Accessors;


@Data
@ApiModel(value = "用户信息", description = "用户信息")
@TableName("t_user")
public class User {
  @ApiModelProperty(value = "主键")
  @JsonSerialize(using = ToStringSerializer.class)
  private Long id;

  @ApiModelProperty(value = "用户名")
  private String userName;

  @ApiModelProperty(value = "性别")
  private int sex;

  @ApiModelProperty(value = "创建时间")
  private Date createTime;
}

package com.zkb.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zkb.entity.User;


public interface UserMapper extends BaseMapper<User> {}

package com.zkb.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.zkb.entity.User;


public interface UserService extends IService<User> {}

package com.zkb.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zkb.entity.User;
import com.zkb.mapper.UserMapper;
import com.zkb.service.UserService;
import org.springframework.stereotype.Service;

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {}

package com.zkb.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.zkb.entity.User;
import com.zkb.service.UserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;
import java.util.Random;


@Controller
@RequestMapping("/user")
@Api(value = "user",tags = "user")
public class UserController {
  private static final Logger logger = LoggerFactory.getLogger(UserController.class);

  @Resource
  private UserService userService;

  //登录
  @PostMapping("/insert")
  @ApiOperation(value="插入",notes = "登录")
  @ResponseBody
  public String test() {
    // 测试新增100条数据  根据id策略分布到各库
    for (int i = 0; i < 100; i++) {
      User user = new User();
      user.setSex(new Random().nextInt(2));
      user.setUserName("测试" + i);
      user.setCreateTime(new Date());
      this.userService.save(user);
    }
    return "ok";
  }

  @PostMapping("/queryById")
  @ApiOperation(value="查询",notes = "查询")
  @ResponseBody
  public User queryById(@RequestParam("id") Long id) {
    // 根据id查询用户信息
    User user = this.userService.getById(id);
    logger.info("用户信息:{}", user);
    return user;
  }



  @PostMapping("/updateById")
  @ApiOperation(value="修改",notes = "修改")
  @ResponseBody
  public boolean updateById(@RequestParam("id") Long id) {
    // 根据id查询用户信息
    User user = this.userService.getById(id);
    user.setSex(1000);
    boolean b=this.userService.updateById(user);
    return b;
  }



  @PostMapping("/deleteById")
  @ApiOperation(value="删除",notes = "删除")
  @ResponseBody
  public String delete(@RequestParam("id") Long id) {
    // 根据id查询用户信息
    boolean result = this.userService.removeById(id);
    logger.info("删除结果:{}", result);
    return "ok";
  }

  @PostMapping("/list")
  @ApiOperation(value="获取列表",notes = "获取列表")
  @ResponseBody
  public JSONObject list() {
    // 查询列表
    List<User> lists = this.userService.list();
    JSONObject jsonObject = new JSONObject();
    jsonObject.put("total", lists != null ? lists.size() : 0);
    jsonObject.put("list", lists);
    return jsonObject;
  }


  @PostMapping("/count")
  @ApiOperation(value="获取总数",notes = "获取总数")
  @ResponseBody
  public JSONObject count() {
    // 查询列表
   int count = this.userService.count();
    JSONObject jsonObject = new JSONObject();
    jsonObject.put("total", count);
    return jsonObject;
  }
}

package com.zkb.configuration;

import com.baomidou.mybatisplus.core.parser.ISqlParser;
import com.baomidou.mybatisplus.extension.parsers.BlockAttackSqlParser;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor;
import java.util.ArrayList;
import java.util.List;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan("com.zkb.mapper")
public class MybatisPlusConfig {

  @Bean
  public PaginationInterceptor paginationInterceptor() {
    PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
    List<ISqlParser> sqlParserList = new ArrayList<>();
    sqlParserList.add(new BlockAttackSqlParser());
    paginationInterceptor.setSqlParserList(sqlParserList);
    return new PaginationInterceptor();
  }

  @Bean
  public PerformanceInterceptor performanceInterceptor() {
    return new PerformanceInterceptor();
  }
}

package com.zkb.configuration;

import io.swagger.annotations.ApiOperation;
import io.swagger.models.auth.In;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.ApiKey;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

import java.util.Arrays;
import java.util.List;


@Configuration
@EnableSwagger2
public class SwaggerApp {



    @Bean
    public Docket createRestApi1() {
        return new Docket(DocumentationType.SWAGGER_2).enable(true).apiInfo(apiInfo()).select()
                .apis(RequestHandlerSelectors.withMethodAnnotation(ApiOperation.class))
                .apis(RequestHandlerSelectors.basePackage("com.zkb.controller"))
                .paths(PathSelectors.any()).build().securitySchemes(apiKeyList()).groupName("接口中心");
    }

    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("API")
                .contact(new Contact("XXXXX", "http://XXXXXX.XXXX/", ""))
                .version("1.0")
                .description("API 描述")
                .build();
    }

    private List<ApiKey> apiKeyList() {
        return Arrays.asList(new ApiKey("登录token", "token", In.HEADER.name()),
                new ApiKey("设备类型(android,ios,pc)---必填", "deviceType", In.HEADER.name()));
    }
}

 

 

 

 

 可以看到我数据库是空的，那么我直接执行以下

 

 

 两个库里面的表都有数据了，100条数据分别分配到了两个库中的表里面去了对应

都存在了数据，打完 

查询出来也是100条数据，说明成功了 

 

demo自取 https://download.csdn.net/download/qq_14926283/13219377