Spring Boot与Sharding-JDBC分表实战：快速入门与配置解析

目录

一、引入依赖

 二、配置application

三、解析yml中的sharding配置 

业务不复杂，亿级数据量，实际表数量并不是很多。

考虑写数据的情况比较少，所以引入sharding-jdbf只分表、不分库。

一、引入依赖

项目当中引入了swagger、eureka、mybatis、log4j、lombok

数据库试用的是mysql

/*
 * This file was generated by the Gradle 'init' task.
 *
 * This project uses @Incubating APIs which are subject to change.
 */
plugins {
    id 'java'
    id 'org.springframework.boot' version '2.7.8'
    id 'io.spring.dependency-management' version '1.0.11.RELEASE'
}

group = 'com.jushu'
version = '0.0.1-SNAPSHOT'
description = 'test'
java {
    sourceCompatibility = "11"
}
ext {
    springBootVersion = '2.7.8'
    springCloudVersion = '3.1.7'
    mybatisPlusVersion = '3.5.3'
    lombokVersion = '1.18.24'
    springfoxVersion = '3.0.0'
    mysqlConnectorVersion = '8.0.33'
    fastjsonVersion = '1.2.76'
    lang3Version = '3.8.1'
    shardingJdbc = '4.1.1'
    hikariCP = '4.0.3'
}

repositories {
    maven { url 'https://maven.aliyun.com/repository/public' }
    mavenCentral()
}
dependencies {
    implementation("org.springframework.boot:spring-boot-starter-web:$springBootVersion")
    testImplementation("org.springframework.boot:spring-boot-starter-test:$springBootVersion")
    implementation("org.springframework.boot:spring-boot-starter-log4j2:$springBootVersion")
    implementation("org.springframework.cloud:spring-cloud-starter-netflix-eureka-client:$springCloudVersion")

    implementation("com.baomidou:mybatis-plus-core:$mybatisPlusVersion")
    implementation("mysql:mysql-connector-java:$mysqlConnectorVersion")
    implementation("com.zaxxer:HikariCP:$hikariCP")
    implementation("com.baomidou:mybatis-plus-core:$mybatisPlusVersion")

    implementation("org.projectlombok:lombok:$lombokVersion")
    compileOnly("org.projectlombok:lombok:$lombokVersion")
    annotationProcessor("org.projectlombok:lombok:$lombokVersion")

    /*swagger*/
    implementation("io.springfox:springfox-boot-starter:$springfoxVersion")

    implementation("com.alibaba:fastjson:$fastjsonVersion")

    implementation("org.apache.commons:commons-lang3:$lang3Version")

    implementation("org.apache.shardingsphere:sharding-jdbc-spring-boot-starter:$shardingJdbc")

}

configurations {
    // 排除 spring-boot-starter-logging 依赖
    configureEach {
        exclude group: 'org.springframework.boot', module: 'spring-boot-starter-logging'
    }
}

bootJar {
    archiveBaseName = 'test'
    archiveVersion = '0.0.1'
}

tasks.withType(JavaCompile).configureEach {
    options.encoding = 'UTF-8'
}

 二、配置application

 application.yml

spring:
  application:
    name: test
  mvc:
    pathmatch:
      matching-strategy: ANT_PATH_MATCHER
  profiles:
    active: local
logging:
  config: classpath:log4j.xml

 application-local.yml 

server:
  port: 8091
  servlet:
    context-path: /data
eureka:
  client:
    #表示是否将自己注册进EurekaServer默认为true
    register-with-eureka: true
    #是否从EurekaServer抓取已有的注册信息，默认为true。单节点无所谓，集群必须设置为true才能配合ribbon使用负载均衡
    fetch-registry: true
    service-url:
      defaultZone: http://localhost:8078/eureka/
  instance:
    prefer-ip-address: true
    instance-id: ${spring.application.name}:${spring.application.instance_id:${server.port}}
    appname: ${spring.application.name}
customer:
  url: http://localhost:8088
spring:
  shardingsphere:
    datasource:
      names: jushu-data
      jushu-data:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
        username: root
        password: root
        type: com.zaxxer.hikari.HikariDataSource
    sharding:
      # 配置分表策略：根据split作为分片的依据（分片键）
      tables:
        t_sequence:
          # groovy脚本->{}
          actual-data-nodes: test.t_sequence_$->{0..1}
          table-strategy:
            inline:
              sharding-column: split_no
              algorithm-expression: t_sequence_$->{split_no%2}
    props:
      sql:
        show: true

三、解析yml中的sharding配置 

 这里数据库配置，不做赘述~ 

 spring:
  shardingsphere:
    datasource:
      names: jushu-data
      jushu-data:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
        username: root
        password: root
        type: com.zaxxer.hikari.HikariDataSource

下面是我的数据库表：

CREATE TABLE `test`.`t_sequence_0`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `split_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `version` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `sequence` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `test`.`t_sequence_1`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `split_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `version` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `sequence` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

      sharding:
      # 配置分表策略：根据字段split_no作为分片的依据（分片键）
      tables:
        t_sequence:
          # groovy脚本->{}
          actual-data-nodes: test.t_sequence_$->{0..1}
          table-strategy:
            inline:
              sharding-column: split_no
              algorithm-expression: t_sequence_$->{split_no%2}

这里通过split_no%2进行确认是t_sequence_0表，还是t_sequence_0表

分片策略使用的是inline，提供对SQL语句中的=和IN的分片操作支持。InlineShardingStrategy只支持单分片键，对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发。

使用groovy的方式，直接使用字段模每个表分表的个数 （id%每个表分表数量）。切记这种策略不支持范围查询！

通过mybatis进行开发的时候可以直接对应逻辑表t_sequence。

而这里的物理表就是实际数据库中的表t_sequence_0、t_sequence_0。

如果需要支持范围查询可以使用standard分配策略。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。

相关踩坑：

实战之shardingjdbc引入报错Cannot invoke “Object.toString()“ because the return value of “java.util.Map.get(_result of 'object.tostring()' is ignored-CSDN博客

服务启动时报错Communications link failure-CSDN博客