分库分表，Sharding-JDBC水平分表

概述

随着数据量的增大，就可能会达到性能瓶颈，比如千万数据，增删改都很慢，这时候就需要考虑分库分表了

了解一下水平分表，数据库、表不变，创建相同结构的数据库 称为水平分库 创建相同结构的表 称为水平分表

demo

项目结构：

pom

<properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

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

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.21</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!-- 不要引入druid的starter，直接引入druid 使用druid-spring-boot-starter测试时会报url错  -->
        <!--<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.11</version>
        </dependency>-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.11</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

数据表，两张结构一样，名称不同的表

application.yml，重要的是其中是shardingsphere的配置

server:
  port: 8021
#应用名称
spring:
  application:
    name: sharding-service

  shardingsphere:
    datasource:
      names: ds1
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/course_db?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: a2332303
    sharding:
      tables:
        course:
          actual-data-nodes: ds1.course_$->{1..2}
          key-generator:
            column: cid
            type: SNOWFLAKE
          table-strategy:
            inline:
              sharding-column: cid
              algorithm-expression: course_$->{cid % 2 + 1}
    props:
      sql:
        show: true

mybatis-plus:
  mapper-locations: classpath*:/mapper/**/*.xml
  global-config:
    db-config:
      id-type: assign_uuid

CourseMapper：

@Repository
public interface CourseMapper extends BaseMapper<Course> {



}

Test

@RunWith(SpringRunner.class)
@SpringBootTest
@ActiveProfiles("dev")
public class Test {

    @Autowired
    private CourseMapper courseMapper;

    @org.junit.Test
    public void test(){

        Course course = new Course();
        course.setCname("java");
        course.setUserId(100L);
        course.setCstatus("Normal");
        courseMapper.insert(course);

    }

    @org.junit.Test
    public void findCourse(){

        courseMapper.selectOne(new QueryWrapper<Course>().lambda().eq(Course::getCid,752176322356183040L));
    }


}

可以看到数据写进来了