原因

當(dāng)mysql數(shù)據(jù)庫(kù)單表大于1千萬(wàn)以后，查詢的性能就不能保證了，我們必須考慮分庫(kù)，分表的方案了，還好，sharding-jdbc可以很優(yōu)雅的與springboot對(duì)接，完成對(duì)mysql的分庫(kù)和分表。

依賴(lài)整理

為了不影響其它小容量的表，所有添加了動(dòng)態(tài)數(shù)據(jù)源，只對(duì)需要分庫(kù)分表的進(jìn)行配置即可

• 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 #必須個(gè)type，否則報(bào)錯(cuò)
    sharding:
      tables:
        t_order:
          #key-generator:
          #  column: id
          #  type: SNOWFLAKE
          actual-data-nodes: ds$->{0..1}.t_order_$->{0..1} #需要開(kāi)發(fā)人員手動(dòng)按規(guī)則建立數(shù)據(jù)表
          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

提前建立表分庫(kù)和分表

文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-457178.html

測(cè)試代碼整理

• 配置類(lèi)

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

	// 分表數(shù)據(jù)源名稱(chēng)
	private static final String SHARDING_DATA_SOURCE_NAME = "sharding";
	/**
	 * shardingjdbc有四種數(shù)據(jù)源，需要根據(jù)業(yè)務(wù)注入不同的數(shù)據(jù)源
	 *
	 * <p>
	 * 1. 未使用分片, 脫敏的名稱(chēng)(默認(rèn)): shardingDataSource;
	 * <p>
	 * 2. 主從數(shù)據(jù)源: masterSlaveDataSource;
	 * <p>
	 * 3. 脫敏數(shù)據(jù)源：encryptDataSource;
	 * <p>
	 * 4. 影子數(shù)據(jù)源：shadowDataSource
	 */
	@Lazy
	@Resource(name = "shardingDataSource")
	AbstractDataSourceAdapter shardingDataSource;
	// 動(dòng)態(tài)數(shù)據(jù)源配置項(xiàng)
	@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 管理的數(shù)據(jù)源也交給動(dòng)態(tài)數(shù)據(jù)源管理
				dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
				return dataSourceMap;
			}
		};
	}

	/**
	 * 將動(dòng)態(tài)數(shù)據(jù)源設(shè)置為首選的 當(dāng)spring存在多個(gè)數(shù)據(jù)源時(shí), 自動(dòng)注入的是首選的對(duì)象 設(shè)置為主要的數(shù)據(jù)源之后，就可以支持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;
	}

}

• 實(shí)體類(lèi)和mapper類(lèi)

@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> {

}

• 分表的測(cè)試

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

• 不進(jìn)行分表的測(cè)試

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

到了這里，關(guān)于springboot~對(duì)應(yīng)sharding-jdbc實(shí)現(xiàn)分庫(kù)分表的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！