数据分片的实践主要有根据业务来分库，对大数量表进行分表，根据实际需要可以分库分表，也可以只分表不分库。
分库除了在拆分业务时各自使用独立的库，现在很多中小型的 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_0 和 order_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
日志：会根据传入的分表字段的值，执行范围查询(between 与 in)，查询满足分表规则的多个表。
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);
}
当分片运算符是等于号时，路由结果将落入单库（表），当分片运算符是BETWEEN或IN时，则路由结果不一定落入唯一的库（表），因此一条逻辑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 |
|---|--------------|--------|
相关参考
更多内容请访问：IT源点
注意：本文归作者所有，未经作者允许，不得转载
