springboot~sharding-jdbc实现分库分表

原因

当mysql数据库单表大于1千万以后，查询的性能就不能保证了，我们必须考虑分库，分表的方案了，还好，sharding-jdbc可以很优雅的与springboot对接，完成对mysql的分库和分表。

依赖整理

为了不影响其它小容量的表，所有添加了动态数据源，只对需要分库分表的进行配置即可

• com.baomidou:dynamic-datasource-spring-boot-starter:3.3.1
• org.apache.shardingsphere:sharding-jdbc-spring-boot-starter:4.1.1
• com.baomidou:dynamic-datasource-spring-boot-starter:3.3.1
• com.baomidou:mybatis-plus-boot-starter:3.4.1

    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

配置整理

spring:
  application.name: sharding-jdbc
  datasource:
    dynamic:
      primary: master0
      datasource:
        master0:
          url: jdbc:mysql://192.168.4.xx:3306/sharding0?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
          username: root
          password: xxx
          driver-class-name: com.mysql.jdbc.Driver
        master1:
          url: jdbc:mysql://192.168.4.xx:3306/sharding1?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
          username: root
          password: xxx
          driver-class-name: com.mysql.jdbc.Driver

  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.4.xx:3306/sharding0?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
        username: root
        password: xxx
        type: com.zaxxer.hikari.HikariDataSource
      ds1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.4.xx:3306/sharding1?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
        username: root
        password: xxx
        type: com.zaxxer.hikari.HikariDataSource #必须个type，否则报错
    sharding:
      tables:
        t_order:
          #key-generator:
          #  column: id
          #  type: SNOWFLAKE
          actual-data-nodes: ds$->{0..1}.t_order_$->{0..1} #需要开发人员手动按规则建立数据表
          database-strategy:
            inline:
              sharding-column: id
              algorithm‐expression: ds$->{id % 2}
          table-strategy:
            inline:
              sharding-column: id
              algorithm‐expression: t_order_$->{id % 2}
    props:
      sql:
        show: true   # 日志显示SQL

mybatis:
  mapperLocations: classpath:mapper/*.xml
  typeAliasesPackage: com.lind.shardingjdbc.entity
  configuration:
    mapUnderscoreToCamelCase: true

提前建立表分库和分表

测试代码整理

• 配置类

@Configuration
@AutoConfigureBefore({ DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class })
public class DataSourceConfiguration {

	// 分表数据源名称
	private static final String SHARDING_DATA_SOURCE_NAME = "sharding";
	/**
	 * shardingjdbc有四种数据源，需要根据业务注入不同的数据源
	 *
	 * <p>
	 * 1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
	 * <p>
	 * 2. 主从数据源: masterSlaveDataSource;
	 * <p>
	 * 3. 脱敏数据源：encryptDataSource;
	 * <p>
	 * 4. 影子数据源：shadowDataSource
	 */
	@Lazy
	@Resource(name = "shardingDataSource")
	AbstractDataSourceAdapter shardingDataSource;
	// 动态数据源配置项
	@Autowired
	private DynamicDataSourceProperties properties;

	@Bean
	public DynamicDataSourceProvider dynamicDataSourceProvider() {
		Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
		return new AbstractDataSourceProvider() {
			@Override
			public Map<String, DataSource> loadDataSources() {
				Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
				// 将 shardingjdbc 管理的数据源也交给动态数据源管理
				dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
				return dataSourceMap;
			}
		};
	}

	/**
	 * 将动态数据源设置为首选的 当spring存在多个数据源时, 自动注入的是首选的对象 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
	 */
	@Primary
	@Bean
	public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
		DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
		dataSource.setPrimary(properties.getPrimary());
		dataSource.setStrict(properties.getStrict());
		dataSource.setStrategy(properties.getStrategy());
		dataSource.setProvider(dynamicDataSourceProvider);
		dataSource.setP6spy(properties.getP6spy());
		dataSource.setSeata(properties.getSeata());
		return dataSource;
	}

}

• 实体类和mapper类

@Data
@TableName("t_order")
public class Order {

	@TableId(type = IdType.ASSIGN_ID)
	Long orderId;

	double amount;

	Integer userId;

}
@Mapper
public interface OrderMapper extends BaseMapper<Order> {

}

• 分表的测试

    @GetMapping("insert")
	@DS("sharding")
	public ResponseEntity test() {
		Order order = new Order();
		order.setAmount(100);
		order.setUserId(1);
		orderMapper.insert(order);
		return ResponseEntity.ok("success");
	}

• 不进行分表的测试

    @GetMapping("insert-not-sharding")
	public ResponseEntity testNotSharding() {
		Order order = new Order();
		order.setAmount(101);
		order.setUserId(2);
		orderMapper.insert(order);
		return ResponseEntity.ok("success");
	}