原因

當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");
	}

作者：倉儲大叔，張占嶺，
榮譽：微軟MVP
QQ：853066980

支付寶掃一掃，為大叔打賞!