原创

Sharding-JDBC系列(六):Sharding-JDBC 单库分表实践

数据分片的实践主要有根据业务来分库,对大数量表进行分表,根据实际需要可以分库分表,也可以只分表不分库

分库除了在拆分业务时各自使用独立的库,现在很多中小型的 SASS 系统,在租户数据隔离这块也有采用分库分表的方式实现。

此系列文章都是基于 Sharding-JDBC 4.x版本, 在写此文章时,正式发布的是 4.1.0版本,点此 4.x 官方文档

分表配置

ShardingSphere 分表配置支持 Java Api 配置,Yaml 配置,Spring Boot 配置,Spring 命名空间配置(XML配置)。

官方配置手册:Sharding-JDBC > 配置手册,提供了数据分片、读写分离、数据脱敏、数据分表 + 读写分离,数据分片 + 数据脱敏,治里 的配置示例。

分片配置示例和属性说明可参考 Sharding-JDBC系列(三):Sharding-JDBC分片配置示例与说明

单库分表实践

不分库只分表的业务场景也是非常多的,例如某块业务会产生大量数据,其对应的表数据量可能变得很大,只时就可以采用分表,在原有系统上改动可控,改造的风险也有限。常见的如订单表,订单详情表,支付表,支付详情表,退款表,退款详情表等可以采用分表。

下面基于 Spring Boot 框架实现分表。支持在 application.properties 文件中配置分表策略。

准备分表

在数据库里创建两张表:order_01,order_02

CREATE TABLE `order_info_0` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `order_no` varchar(32) NOT NULL COMMENT '订单编号',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4

CREATE TABLE `order_info_1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `order_no` varchar(32) NOT NULL COMMENT '订单编号',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4

添加依赖

创建 Spring Boot 项目,集成 Mybatis 和 Mybatis-Plus 操作数据库。

本示例演示:对订单表进行分表,根据用户ID 列(user_id % 2)分为 order_info_0order_info_1 两个表。

pom.xml 文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.gxitsky</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-jdbc-table</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions>
                <!--排除logback,后面配置log4j2 -->
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!--log4j2 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.67</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.10</version>
        </dependency>
        <dependency>
            <groupId>commons-beanutils</groupId>
            <artifactId>commons-beanutils</artifactId>
            <version>1.9.4</version>
        </dependency>
        <!--start:jdbc-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>
        <dependency>
            <groupId>org.inurl.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.bgee.log4jdbc-log4j2</groupId>
            <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
            <version>1.16</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1.tmp</version>
        </dependency>
        <!--end-->

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Mybatis配置

@Configuration
@MapperScan(basePackages = "com.gxitsky.shardingjdbc.table.mapper")
public class MybatisConfig {

    /**
     * Mybatis Plus 分页插件
     *
     * @return
     */
    @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;
    }
}

配置文件设置

主配置文件

application.properties

spring.profiles.active=sharding_table
spring.application.name=sharding-jdbc
#server.servlet.context-path=/sharding/table

#============Spring MVC=================
##======throw ex for 404 code, close static resource mapping=======
spring.mvc.throw-exception-if-no-handler-found=true
spring.resources.add-mappings=false

#============MyBatis ===================
mybatis-plus.mapper-locations=classpath*:/mapper/**/*.xml
mybatis-plus.type-aliases-package=com.gxitsky.shardingjdbc.table.entity
mybatis-plus.configuration.map-underscore-to-camel-case=true

#mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

#===========Jackson======================
spring.jackson.time-zone=GMT+8
spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
spring.jackson.default-property-inclusion=non_empty

分表配置文件

特别注意:配置数据源的属性是跟数据源类中的定义走的,不同的数据源的属性并不都一致,但大部分是一致的。

  • Spring Boot 默认提供的数据源 com.zaxxer.hikari.HikariDataSource 的连接属性是:jdbc-url
  • 使用 com.alibaba.druid.pool.DruidDataSource 数据源的连接属性是:url

application-sharding_table.properties

#=============ShardingSphere Datasource=================

#------------System Config-----------------------------
#spring.shardingsphere.props.sql.show=true
spring.shardingsphere.props.sql.simple=true

#-------------Data Source Config------------------------
spring.shardingsphere.datasource.names=ds0
#spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
#spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:log4jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.shardingsphere.datasource.ds0.url=jdbc:log4jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

#-------------Sharding Tables----------------------------
spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds0.order_info_$->{0..1}
spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_info_$->{user_id % 2}

#bind tables
spring.shardingsphere.sharding.binding-tables=order_info

上面示例的分片配置是基于行表达式,数据用户ID(user_id % 2)取模(奇数/偶数)分 2 个表。

解决数据源冲突

如果数据源使用的是 druid-spring-boot-starter 包,Druid 自动配置创建的数据源不能被 Sharding-JDBC 使用,需要在入口类配置排除 Druid 数据源自动配置,启用 spring.shardingsphere.datasource 数据源。

// 集成Sharding-JDBC时,需要关注Druid数据源自动配置
@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
public class ShardingJdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcApplication.class, args);
    }
}

业务代码示例

Entity

OrderInfo 订单信息实体类

/**
 * @desc 订单表
 */
@Data
@Accessors(chain = true)
@TableName(value = "order_info")
public class OrderInfo implements Serializable {
    private static final long serialVersionUID = 7228334740131864851L;

    /**
     * 主键ID
     */
    @TableId(type = IdType.AUTO)
    private Long id;
    /**
     * 订单号
     */
    private String orderNo;
    /**
     * 用户ID
     */
    private Long userId;

    public OrderInfo() {
    }

    public OrderInfo(String orderNo, Long userId) {
        this.orderNo = orderNo;
        this.userId = userId;
    }
}

Controller

OrderController

/**
 * @desc 订单
 */
@RestController
@RequestMapping("/order")
public class OrderController {
    private static final Logger logger = LogManager.getLogger(OrderController.class);

    @Autowired
    private OrderService orderService;

    /**
     * @desc: 订单列表
     * @param: [userId]
     * @author: gxing
     * @date: 2020/9/27
     */
    @GetMapping("/userId")
    public List<OrderInfo> orderList(Long userId) {
        return orderService.orderList(userId);
    }

    /**
     * @desc: 添加订单
     * @param: [userId]
     */
    @PostMapping("/add")
    public int addOrder(Long userId) {
        return orderService.addOrder(userId);
    }

    /**
     * @desc: IN 查询
     * @param: [idList]
     */
    @PostMapping("/userIdList")
    public List<OrderInfo> userIdList(@RequestParam List<Long> idList) {
        return orderService.userIdList(idList);
    }

    /**
     * @desc: 大于等于
     * @param: [userId]
     */
    @PostMapping("/ge")
    public List<OrderInfo> ge(Long userId) {
        return orderService.ge(userId);
    }

    /**
     * @desc: between
     * @param: [start, end]
     */
    @PostMapping("/between")
    public List<OrderInfo> between(Long start, Long end) {
        return orderService.between(start, end);
    }
}

Service

OrderService 接口

/**
 * @desc 订单接口
 */
public interface OrderService {

    /**
     * @desc: 订单列表
     * @param: [userId]
     */
    List<OrderInfo> orderList(Long userId);

    /**
     * @desc: 添加订单
     * @param: [userId]
     */
    int addOrder(Long userId);

    /**
     * @desc: 多个用户订单
     * @param: [userIdList]
     */
    List<OrderInfo> userIdList(List<Long> userIdList);

    /**
     * @desc: 大于等于
     * @param: [userId]
     */
    List<OrderInfo> ge(Long userId);

    /**
     * @desc: 范围查询
     * @param: [start, end]
     */
    List<OrderInfo> between(Long start, Long end);
}

OrderServiceImpl 接口实现类

/**
 * @desc 订单服务
 */
@Service
public class OrderServiceImpl implements OrderService {

    @Autowired
    private OrderMapper orderMapper;

    /**
     * @desc: 订单列表
     * @param: []
     */
    @Override
    public List<OrderInfo> orderList(Long userId) {
        LambdaQueryWrapper<OrderInfo> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(OrderInfo::getUserId, userId);
        return orderMapper.selectList(queryWrapper);
    }

    /**
     * @desc: 添加订单
     * @param: [userId]
     */
    @Override
    public int addOrder(Long userId) {
        long millis = System.currentTimeMillis();
        return orderMapper.insert(new OrderInfo(String.valueOf(millis), userId));
    }


    /**
     * @desc: 多个用户的数据 in 查询
     * @param: [userIdList]
     */
    @Override
    public List<OrderInfo> userIdList(List<Long> userIdList) {
        LambdaQueryWrapper<OrderInfo> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.in(OrderInfo::getUserId, userIdList);
        return orderMapper.selectList(queryWrapper);
    }

    /**
     * @desc: 大于等于
     * @param: [userId]
     */
    @Override
    public List<OrderInfo> ge(Long userId) {
        LambdaQueryWrapper<OrderInfo> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.ge(OrderInfo::getUserId, userId);
        return orderMapper.selectList(queryWrapper);
    }

    /**
     * @desc: between 查询
     * @param: [end,start]
     */
    @Override
    public List<OrderInfo> between(Long start, Long end) {
        LambdaQueryWrapper<OrderInfo> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.between(OrderInfo::getUserId, start, end);
        return orderMapper.selectList(queryWrapper);
    }
}

Mapper

OrderMapper 接口

/**
 * @desc 订单Mapper
 */
@Repository
public interface OrderMapper extends BaseMapper<OrderInfo> {
}

分片日志分析

启动日志

启动项目,查看启动的日志打印:

# 打印分片规则配置信息
2020-09-27 23:07:20,381 INFO [LogId:] [o.a.s.c.l.ConfigurationLogger->log:104] [restartedMain] ShardingRuleConfiguration:
bindingTables:
- order_info
tables:
  order_info:
    actualDataNodes: ds0.order_info_$->{0..1}
    logicTable: order_info
    tableStrategy:
      inline:
        algorithmExpression: order_info_$->{user_id % 2}
        shardingColumn: user_id

# 分片的系统配置(打印简单SQL)
2020-09-27 23:07:20,381 INFO [LogId:] [o.a.s.c.l.ConfigurationLogger->log:104] [restartedMain] Properties:
sql.simple: 'true'

# 加载数据库表结构元数据(加载了 1 个逻辑表元数据)
2020-09-27 23:14:42,175 INFO [LogId:] [o.a.s.c.m.ShardingMetaDataLoader->loadShardingSchemaMetaData:131] [restartedMain] Loading 1 logic tables' meta data.
2020-09-27 23:14:42,189 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [restartedMain] SELECT * FROM `order_info_0` WHERE 1 != 1; 

2020-09-27 23:14:42,195 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [restartedMain] 
|---|---------|--------|
|id |order_no |user_id |
|---|---------|--------|
|---|---------|--------|

# 加载了 4 个表的元数据
2020-09-27 23:14:42,203 INFO [LogId:] [o.a.s.s.p.b.m.s.SchemaMetaDataLoader->load:70] [restartedMain] Loading 4 tables' meta data.
2020-09-27 23:14:42,209 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [restartedMain] SELECT * FROM `order_info` WHERE 1 != 1; 

2020-09-27 23:14:42,209 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [restartedMain] 
|---|---------|--------|
|id |order_no |user_id |
|---|---------|--------|
|---|---------|--------|

2020-09-27 23:14:42,214 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [restartedMain] SELECT * FROM `order_info_0` WHERE 1 != 1; 

2020-09-27 23:14:42,216 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [restartedMain] 
|---|---------|--------|
|id |order_no |user_id |
|---|---------|--------|
|---|---------|--------|

2020-09-27 23:14:42,221 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [restartedMain] SELECT * FROM `order_info_1` WHERE 1 != 1; 

2020-09-27 23:14:42,221 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [restartedMain] 
|---|---------|--------|
|id |order_no |user_id |
|---|---------|--------|
|---|---------|--------|

2020-09-27 23:14:42,226 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [restartedMain] SELECT * FROM `order_item` WHERE 1 != 1; 

2020-09-27 23:14:42,227 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [restartedMain] 
|---|--------------|---------|----------|
|id |order_info_id |order_no |good_name |
|---|--------------|---------|----------|
|---|--------------|---------|----------|

精准新增

Postman 或浏览器请求新增的接口,参数 userId 分别为 1001,1002,1003,1004

url: localhost:8080/order/add?userId=1008

查看日志:

2020-09-27 23:19:58,644 INFO [LogId:] [o.a.j.l.DirectJDKLog->log:173] [http-nio-8080-exec-1] Initializing Spring DispatcherServlet 'dispatcherServlet'
2020-09-27 23:19:58,645 INFO [LogId:] [o.s.w.s.FrameworkServlet->initServletBean:525] [http-nio-8080-exec-1] Initializing Servlet 'dispatcherServlet'
2020-09-27 23:19:58,651 INFO [LogId:] [o.s.w.s.FrameworkServlet->initServletBean:547] [http-nio-8080-exec-1] Completed initialization in 6 ms
# 执行的SQL, userId % 2 = 0,路由到 order_info_0 表上
2020-09-27 23:19:59,174 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-1] INSERT INTO order_info_0 ( order_no, user_id ) VALUES ('1601219998675', 1008) 

2020-09-27 23:19:59,246 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-1] 
|--------------|
|generated_key |
|--------------|
|20            |
|--------------|

# 执行的SQL, userId % 2 = 1,路由到 order_info_1 表上
2020-09-27 23:22:35,349 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-5] INSERT INTO order_info_1 ( order_no, user_id ) VALUES ('1601220155347', 1009) 

2020-09-27 23:22:35,406 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-5] 
|--------------|
|generated_key |
|--------------|
|22            |
|--------------|

从插入数据上看分表已经实现了。

精准查询

Postman 或浏览器请求查询的接口,参数 userId 分别为 1001,1002,1003,1004

url:llocalhost:8080/order/userId?userId=1003

日志:精确路由到具体的物理分表。

2020-09-27 23:24:17,780 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-8] SELECT id,order_no,user_id FROM order_info_1 WHERE (user_id = 1003) 
# 执行的SQL, user_id % 2 = 1, 查询的是 order_info_1 表 
2020-09-27 23:24:17,793 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-8] 
|---|--------------|--------|
|id |order_no      |user_id |
|---|--------------|--------|
|14 |1601218184873 |1003    |
|15 |1601218186473 |1003    |
|16 |1601218187248 |1003    |
|---|--------------|--------|

# 执行的SQL, user_id % 2 = 0, 查询的是 order_info_0 表
2020-09-27 23:25:51,600 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-2] SELECT id,order_no,user_id FROM order_info_0 WHERE (user_id = 1004) 

2020-09-27 23:25:51,602 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-2] 
|---|--------------|--------|
|id |order_no      |user_id |
|---|--------------|--------|
|8  |1601218192586 |1004    |
|9  |1601218193627 |1004    |
|10 |1601218194203 |1004    |
|11 |1601218194945 |1004    |
|12 |1601218195693 |1004    |
|13 |1601218196411 |1004    |
|14 |1601218197192 |1004    |
|---|--------------|--------|

分表联查

localhost:8080/order/userIdList?idList=1002&idList=1003

日志:会根据传入的分表字段的值,执行范围查询(betweenin),查询满足分表规则的多个表。

2020-09-27 23:57:37,499 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-4] SELECT id,order_no,user_id FROM order_info_0 WHERE (user_id IN (1001,1002)) 

2020-09-27 23:57:37,501 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-4] SELECT id,order_no,user_id FROM order_info_1 WHERE (user_id IN (1001,1002)) 

2020-09-27 23:57:37,503 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-4] 
|---|--------------|--------|
|id |order_no      |user_id |
|---|--------------|--------|
|15 |1601218216439 |1002    |
|---|--------------|--------|

2020-09-27 23:57:37,504 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-4] 
|---|--------------|--------|
|id |order_no      |user_id |
|---|--------------|--------|
|17 |1601218213989 |1001    |
|---|--------------|--------|

自定义分片算法

上面的分片配置都是基于 行表达式 在配置文件中设置,此方式方便,但不灵活,行表达式不支持范围查询(Between...and, >, >=, <, <=)。

自定义分片算法使用最多的是标准分片策略,Sharding-JDBC 提供了标准分片策略配置类:StandardShardingStrategyConfiguration

标准分片策略配置类

StandardShardingStrategyConfiguration:该配置类中有三个属性:分别是 shardingColumn ,用于分片的列名;PreciseShardingAlgorithm 用于 =in 的精确分片算法;RangeShardingAlgorithm 用于 between and, >, >=, <, <=的范围分片算法。

/**
 * Standard strategy configuration.
 */
@Getter
public final class StandardShardingStrategyConfiguration implements ShardingStrategyConfiguration {

    private final String shardingColumn;
    // 精准分片算法,用于 = 和 in
    private final PreciseShardingAlgorithm preciseShardingAlgorithm;
    // 范围分片算法,用于 between and, >, >=, <, <= 
    private final RangeShardingAlgorithm rangeShardingAlgorithm;

    public StandardShardingStrategyConfiguration(final String shardingColumn, final PreciseShardingAlgorithm preciseShardingAlgorithm) {
        this(shardingColumn, preciseShardingAlgorithm, null);
    }

    public StandardShardingStrategyConfiguration(final String shardingColumn, final PreciseShardingAlgorithm preciseShardingAlgorithm, final RangeShardingAlgorithm rangeShardingAlgorithm) {
        Preconditions.checkArgument(!Strings.isNullOrEmpty(shardingColumn), "ShardingColumns is required.");
        Preconditions.checkNotNull(preciseShardingAlgorithm, "PreciseShardingAlgorithm is required.");
        this.shardingColumn = shardingColumn;
        this.preciseShardingAlgorithm = preciseShardingAlgorithm;
        this.rangeShardingAlgorithm = rangeShardingAlgorithm;
    }
}

精准分片算法接口

PreciseShardingAlgorithm:用于 =in 的精确分片算法。

/**
 * Precise sharding algorithm.
 * 
 * @param <T> class type of sharding value
 */
public interface PreciseShardingAlgorithm<T extends Comparable<?>> extends ShardingAlgorithm {

    /**
     * Sharding.
     * 
     * @param availableTargetNames available data sources or tables's names(可用的数据源名或表名列表)
     * @param shardingValue sharding value(参于分片的值,通常取分片值和逻辑表名)
     * @return sharding result for data source or table's name(返回数据源名或物理表名)
     */
    String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<T> shardingValue);
}

范围分片算法接口

RangeShardingAlgorithm:用于 between and, >, >=, <, <=的范围分片

/**
 * Range sharding algorithm.
 * 
 * @param <T> class type of sharding value
 */
public interface RangeShardingAlgorithm<T extends Comparable<?>> extends ShardingAlgorithm {

    /**
     * Sharding.
     * 
     * @param availableTargetNames available data sources or tables's names(可用的数据源名或表名列表)
     * @param shardingValue sharding value(参于分片的值,通常取分片值和逻辑表名)
     * @return sharding results for data sources or tables's names(返回数据源名或物理表名列表)
     */
    Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<T> shardingValue);
}

当分片运算符是等于号时,路由结果将落入单库(表),当分片运算符是BETWEENIN时,则路由结果不一定落入唯一的库(表),因此一条逻辑SQL最终可能被拆分为多条用于执行的真实SQL。详细参考 路由引擎 描述

自定义标准分片算法

在上面行表达式的示例中,order_info 表改为 4 个分表:order_info_0, order_info_1, order_info_2, order_info_3

自定义分片算法使用最多的是 精准分片算法范围分片算法,精准分片算法用于 =in SQL 操作,范围分片算法用于 between and, >, >=, <, <=的SQL操作。

  • 精准分片算法,需要实现 PreciseShardingAlgorithm 接口,重写里面的 doSharding 方法;
  • 范围分片算法,需要实现 RangeShardingAlgorithm 接口,重写里面的 doSharding 方法。
/**
 * @desc 自定义userId分片算法 hash(userId) % 4
 * PreciseShardingAlgorithm(精准分片), RangeShardingAlgorithm(范围分片)
 */
@Component
public class UserIdShardingAlgorithm implements PreciseShardingAlgorithm<Long>, RangeShardingAlgorithm<Long> {
    private static final Logger logger = LogManager.getLogger(UserIdShardingAlgorithm.class);
    // 分 4 个表
    private static final int TABLES = 4;
    // 表后缀索引
    private static List<String> SUFFIX_LIST = new ArrayList<>();

    static {
        for (int i = 0; i < TABLES; i++) {
            SUFFIX_LIST.add(String.valueOf(i));
        }
    }

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> preciseShardingValue) {
        logger.info("----->availableTargetNames:{}", JSON.toJSONString(availableTargetNames));
        logger.info("----->preciseShardingValue:{}", JSON.toJSONString(preciseShardingValue));

        String logicTableName = preciseShardingValue.getLogicTableName();
        Long userId = preciseShardingValue.getValue();
        String index = hashCodeIndex(userId);
        return logicTableName + "_" + index;
    }

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> rangeShardingValue) {
        logger.info("----->availableTargetNames:{}", JSON.toJSONString(availableTargetNames));
        logger.info("----->rangeShardingValue:{}", JSON.toJSONString(rangeShardingValue));

        String logicTableName = rangeShardingValue.getLogicTableName();
        Range<Long> range = rangeShardingValue.getValueRange();
        logger.info("----->range lowerEndpoint:{}", range.hasLowerBound() ? range.lowerEndpoint() : null);
        logger.info("----->range upperEndpoint:{}", range.hasUpperBound() ? range.upperEndpoint() : null);

        if (range.hasLowerBound() && range.hasUpperBound()) {
            // 如果范围是闭区间,就精确计算所有可能的分表名
            List<String> indexList = new ArrayList<>();
            for (Long userId = range.lowerEndpoint(); userId <= range.upperEndpoint(); userId++) {
                String index = this.hashCodeIndex(userId);
                indexList.add(index);
            }
            return indexList.stream().map(index -> logicTableName + "_" + index).collect(Collectors.toList());
        }
        // 开区间就扫描所有的分表名
        return SUFFIX_LIST.stream().map(index -> logicTableName + "_" + index).collect(Collectors.toList());
    }

    /**
     * @desc: 哈希取模
     * @param: [userId]
     */
    private String hashCodeIndex(Long userId) {
        int index = (userId.hashCode()) % TABLES;
        return String.valueOf(index);
    }
}

Spring Boot 配置示例

上面示例工程,根配置文件设置启用此自定义分片算法的配置文件

application-sharding_table_cust.properties

#=============ShardingSphere Datasource=================

#------------System Config-----------------------------
#spring.shardingsphere.props.sql.show=true
spring.shardingsphere.props.sql.simple=true

#-------------Data Source Config------------------------
spring.shardingsphere.datasource.names=ds0
#spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
#spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:log4jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.shardingsphere.datasource.ds0.url=jdbc:log4jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

#-------------Sharding Tables----------------------------
spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds0.order_info_$->{0..3}
spring.shardingsphere.sharding.tables.order_info.table-strategy.standard.sharding-column=user_id
spring.shardingsphere.sharding.tables.order_info.table-strategy.standard.preciseAlgorithmClassName=com.gxitsky.shardingjdbc.table.common.config.UserIdShardingAlgorithm
spring.shardingsphere.sharding.tables.order_info.table-strategy.standard.rangeAlgorithmClassName=com.gxitsky.shardingjdbc.table.common.config.UserIdShardingAlgorithm

#bind tables
spring.shardingsphere.sharding.binding-tables=order_info

精准查询

url: localhost:8080/order/userId?userId=1001

日志:精准路由到具体的物理表

2020-09-28 15:37:07,080 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:39] [http-nio-8080-exec-6] ----->availableTargetNames:["order_info_0","order_info_1","order_info_2","order_info_3"]
2020-09-28 15:37:07,090 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:40] [http-nio-8080-exec-6] ----->preciseShardingValue:{"columnName":"user_id","logicTableName":"order_info","value":1001}
2020-09-28 15:37:07,101 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-6] SELECT id,order_no,user_id FROM order_info_1 WHERE (user_id = 1001) 

2020-09-28 15:37:07,111 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-6] 
|---|--------------|--------|
|id |order_no      |user_id |
|---|--------------|--------|
|1  |1601276568851 |1001    |
|2  |1601276570891 |1001    |
|---|--------------|--------|

精准新增

url: localhost:8080/order/add?userId=1007

日志:精准路由到具体的物理表

2020-09-28 15:38:18,224 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:39] [http-nio-8080-exec-9] ----->availableTargetNames:["order_info_0","order_info_1","order_info_2","order_info_3"]
2020-09-28 15:38:18,225 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:40] [http-nio-8080-exec-9] ----->preciseShardingValue:{"columnName":"user_id","logicTableName":"order_info","value":1007}
2020-09-28 15:38:18,228 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-9] INSERT INTO order_info_3 ( order_no, user_id ) VALUES ('1601278698185', 1007) 

2020-09-28 15:38:18,299 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-9] 
|--------------|
|generated_key |
|--------------|
|5             |
|--------------|

IN 条件查询

url: localhost:8080/order/userIdList?idList=1002&idList=1003

日志:IN 条件查询是精准查询,会精确路由到具体的物理分表。

:从下面日志可以看出,会按分表执行多条SQL,但 SQL 的 where 条件是一致的,where 条件不会精确到每一个分表。

2020-09-28 15:51:14,003 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:39] [http-nio-8080-exec-9] ----->availableTargetNames:["order_info_0","order_info_1","order_info_2","order_info_3"]
2020-09-28 15:51:14,004 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:40] [http-nio-8080-exec-9] ----->preciseShardingValue:{"columnName":"user_id","logicTableName":"order_info","value":1002}
2020-09-28 15:51:14,004 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:39] [http-nio-8080-exec-9] ----->availableTargetNames:["order_info_0","order_info_1","order_info_2","order_info_3"]
2020-09-28 15:51:14,005 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:40] [http-nio-8080-exec-9] ----->preciseShardingValue:{"columnName":"user_id","logicTableName":"order_info","value":1003}
2020-09-28 15:51:14,007 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-9] SELECT id,order_no,user_id FROM order_info_2 WHERE (user_id IN (1002,1003)) 

2020-09-28 15:51:14,008 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-9] SELECT id,order_no,user_id FROM order_info_3 WHERE (user_id IN (1002,1003)) 

2020-09-28 15:51:14,010 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-9] 
|---|--------------|--------|
|id |order_no      |user_id |
|---|--------------|--------|
|1  |1601276582200 |1002    |
|2  |1601276583184 |1002    |
|---|--------------|--------|

2020-09-28 15:51:14,011 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-9] 
|---|--------------|--------|
|id |order_no      |user_id |
|---|--------------|--------|
|1  |1601276586103 |1003    |
|2  |1601276587038 |1003    |
|---|--------------|--------|

Between查询

url: localhost:8080/order/between?start=1006&end=1008

日志:范围有是闭区间,有上限值和下限值,计算出的所有分表有:order_info_0,order_info_2,order_info_3

注:可以看到 SQL 的 where 条件 是不会按分表来拆分的,查询所有分表的 where 条件 是一致的。

2020-09-28 15:40:08,238 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:50] [http-nio-8080-exec-1] ----->availableTargetNames:["order_info_0","order_info_1","order_info_2","order_info_3"]
2020-09-28 15:40:08,238 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:51] [http-nio-8080-exec-1] ----->rangeShardingValue:{"columnName":"user_id","logicTableName":"order_info","valueRange":{"empty":false}}
2020-09-28 15:40:08,239 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:55] [http-nio-8080-exec-1] ----->range lowerEndpoint:1006
2020-09-28 15:40:08,239 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:56] [http-nio-8080-exec-1] ----->range upperEndpoint:1008
2020-09-28 15:40:08,241 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-1] SELECT id,order_no,user_id FROM order_info_0 WHERE (user_id BETWEEN 1006 AND 1008) 

2020-09-28 15:40:08,243 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-1] SELECT id,order_no,user_id FROM order_info_2 WHERE (user_id BETWEEN 1006 AND 1008) 

2020-09-28 15:40:08,244 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-1] SELECT id,order_no,user_id FROM order_info_3 WHERE (user_id BETWEEN 1006 AND 1008) 

2020-09-28 15:40:08,246 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-1] 
|---|---------|--------|
|id |order_no |user_id |
|---|---------|--------|
|---|---------|--------|

2020-09-28 15:40:08,247 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-1] 
|---|--------------|--------|
|id |order_no      |user_id |
|---|--------------|--------|
|3  |1601276599588 |1006    |
|4  |1601276600374 |1006    |
|---|--------------|--------|

2020-09-28 15:40:08,249 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-1] 
|---|--------------|--------|
|id |order_no      |user_id |
|---|--------------|--------|
|3  |1601276603232 |1007    |
|4  |1601276603975 |1007    |
|5  |1601278698185 |1007    |
|---|--------------|--------|

大于等于查询

url: localhost:8080/order/ge?userId=1006

日志:查询条件是开区间,查询所有分表。

2020-09-28 15:49:00,023 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:50] [http-nio-8080-exec-5] ----->availableTargetNames:["order_info_0","order_info_1","order_info_2","order_info_3"]
2020-09-28 15:49:00,023 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:51] [http-nio-8080-exec-5] ----->rangeShardingValue:{"columnName":"user_id","logicTableName":"order_info","valueRange":{"empty":false}}
2020-09-28 15:49:00,024 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:55] [http-nio-8080-exec-5] ----->range lowerEndpoint:1006
2020-09-28 15:49:00,024 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:56] [http-nio-8080-exec-5] ----->range upperEndpoint:null
2020-09-28 15:49:00,026 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-5] SELECT id,order_no,user_id FROM order_info_0 WHERE (user_id >= 1006) 

2020-09-28 15:49:00,027 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-5] SELECT id,order_no,user_id FROM order_info_1 WHERE (user_id >= 1006) 

2020-09-28 15:49:00,028 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-5] SELECT id,order_no,user_id FROM order_info_2 WHERE (user_id >= 1006) 

2020-09-28 15:49:00,029 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-5] SELECT id,order_no,user_id FROM order_info_3 WHERE (user_id >= 1006) 

2020-09-28 15:49:00,030 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-5] 
|---|---------|--------|
|id |order_no |user_id |
|---|---------|--------|
|---|---------|--------|

2020-09-28 15:49:00,031 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-5] 
|---|---------|--------|
|id |order_no |user_id |
|---|---------|--------|
|---|---------|--------|

2020-09-28 15:49:00,032 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-5] 
|---|--------------|--------|
|id |order_no      |user_id |
|---|--------------|--------|
|3  |1601276599588 |1006    |
|4  |1601276600374 |1006    |
|---|--------------|--------|

2020-09-28 15:49:00,033 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-5] 
|---|--------------|--------|
|id |order_no      |user_id |
|---|--------------|--------|
|3  |1601276603232 |1007    |
|4  |1601276603975 |1007    |
|5  |1601278698185 |1007    |
|---|--------------|--------|

相关参考

  1. 文章中示例代码-demo
  2. 使用Sharding-JDBC 分库分表
正文到此结束
本文目录