前言

在项目中有时候数据量较大的情况又不得不使用关系型数据库，单表数据量过大肯定是会影响查询性能的，为此业界也有多解决方案，shardingsphere尤为突出，基本上很多都使用sharding，我自己下去也实践了一下。

引入依赖

springboot版本 2.1.5

compile 'org.apache.shardingsphere:shardingsphere-jdbc-core-spring-boot-starter:5.0.0-alpha'  
compile 'com.alibaba:druid:1.2.16'  ##这里不能使用druid的spring-starter版本

api group: 'com.baomidou', name: 'mybatis-plus-boot-starter', version: '3.3.2'  
api 'com.fasterxml.jackson.datatype:jackson-datatype-jsr310:2.8.11'  
api 'com.fasterxml.jackson.datatype:jackson-datatype-jdk8:2.8.11'  
api 'com.fasterxml.jackson.datatype:jackson-datatype-hibernate5:2.8.4'  
api 'mysql:mysql-connector-java'  
api 'com.alibaba:fastjson:1.2.46'  
api 'org.springframework.boot:spring-boot-starter-web'  
api ('com.github.pagehelper:pagehelper:5.1.10'){  
exclude group:'org.mybatis' , module : 'mybatis'  
exclude group:'org.mybatis' , module : 'mybatis-spring'  
}  
api 'com.github.jsqlparser:jsqlparser:1.4'
implementation 'org.springframework.boot:spring-boot-starter'

项目配置

#端口号  
server:  
    port: 8283  
#数据库连接  
spring:  
    redis:  
        host: ${redis.host}  
        port: ${redis.port}  
        password: ${redis.password}  
        namespace: ${redis.namespace:${spring.profiles.active}}  
    profiles:  
        active: local  
    main:  
        allow-bean-definition-overriding: true  
    shardingsphere:  
        datasource:  
            common:  
                type: com.alibaba.druid.pool.DruidDataSource  
                driver-class-name: com.mysql.cj.jdbc.Driver  
            names: db0  
                db0:  
                    url: jdbc:mysql://${db.host}:${db.port}/${db.name}autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL&allowMultiQueries=true  
                    username: ${db.user}  
                    password: ${db.password}  
        props:  
            sql-show: true  
        rules:  
            sharding:  
                key-generators:  
# 此处必须要配置，否则会导致报错，因为shardingsphere-jdbc-core-spring-boot-starter需要加载此项配置，官网的demo例子有错  
                    snowflake:  
                        type: SNOWFLAKE  
                        props:  
                            worker-id: 1  
                sharding-algorithms:  
                    table-inline:  
                        type: INLINE  
                        props:  
                            algorithm-expression: t_sharding_test$->{id % 4}  
                tables:  
                    t_sharding_test:  
                # 配置t_order的分表的规则  
                        actual-data-nodes: db0.t_sharding_test$->{0..10}  
                        table-strategy:  
                            standard:  
                                sharding-column: id  
                                sharding-algorithm-name: table-inline  
          enabled: true
          
          
mybatis-plus:  
    global-config:  
        db-config:  
        id-type: ASSIGN_ID  
        field-strategy: not_empty  
        table-underline: true  
        db-type: mysql  
        logic-delete-value: 1 # 逻辑已删除值(默认为 1)  
        logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)  
    mapper-locations: classpath:/mapper/*.xml  
        type-enums-package: net.guojutech.*.enums;com.guojutech.*.enums;com.guojutech.*.*.enums          

@Configuration  
@MapperScan(basePackages = {"net.guojutech.*.mapper","com.guojutech.*.*.dao","com.guojutech.*.mapper"})  
public class MybatisPlusConfig {  
  
  
@Bean  
public PaginationInterceptor paginationInterceptor() {  
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();  
// 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求 默认false  
// paginationInterceptor.setOverflow(false);  
// 设置最大单页限制数量，默认 500 条，-1 不受限制  
// paginationInterceptor.setLimit(500);  
// 开启 count 的 join 优化,只针对部分 left join  
paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));  
return paginationInterceptor;  
}  
@Bean  
public PageInterceptor pageInterceptor(){  
return new PageInterceptor();  
}  
  
}

表

t_sharding_test0 t_sharding_test1 t_sharding_test2 t_sharding_test3