(资料图片仅供参考)

原因

当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

        org.apache.shardingsphere        sharding-jdbc-spring-boot-starter                com.baomidou        dynamic-datasource-spring-boot-starter                com.alibaba        druid-spring-boot-starter                com.baomidou        mybatis-plus-boot-starter                mysql        mysql-connector-java    

配置整理

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   # 日志显示SQLmybatis:  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有四种数据源，需要根据业务注入不同的数据源 * * 
 * 1. 未使用分片, 脱敏的名称(默认): shardingDataSource; * 
 * 2. 主从数据源: masterSlaveDataSource; * 
 * 3. 脱敏数据源：encryptDataSource; * 
 * 4. 影子数据源：shadowDataSource */@Lazy@Resource(name = "shardingDataSource")AbstractDataSourceAdapter shardingDataSource;// 动态数据源配置项@Autowiredprivate DynamicDataSourceProperties properties;@Beanpublic DynamicDataSourceProvider dynamicDataSourceProvider() {Map datasourceMap = properties.getDatasource();return new AbstractDataSourceProvider() {@Overridepublic Map loadDataSources() {Map dataSourceMap = createDataSourceMap(datasourceMap);// 将 shardingjdbc 管理的数据源也交给动态数据源管理dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);return dataSourceMap;}};}/** * 将动态数据源设置为首选的 当spring存在多个数据源时, 自动注入的是首选的对象 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了 */@Primary@Beanpublic 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;}@Mapperpublic interface OrderMapper extends BaseMapper {}

• 分表的测试

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