原因

當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

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

標籤：Java

上一篇：Maven的概述

下一篇：返回列表