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

1
2
3
4
5
6
7
8
9
10
11
12
13
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 文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
<?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配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#=============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 数据源。

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

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

业务代码示例

Entity

OrderInfo 订单信息实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
/**
* @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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
/**
* @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 接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
/**
* @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 接口实现类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
/**
* @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 接口

1
2
3
4
5
6
/**
* @desc 订单Mapper
*/
@Repository
public interface OrderMapper extends BaseMapper<OrderInfo> {
}

分片日志分析

启动日志

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# 打印分片规则配置信息
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

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

查看日志:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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 |
|---|--------------|--------|

分表联查

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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, >, >=, <, <= 的范围分片算法。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/**
* 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 的精确分片算法。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/**
* 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, >, >=, <, <= 的范围分片

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/**
* 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 方法。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
/**
* @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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#=============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

精准查询

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

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

1
2
3
4
5
6
7
8
9
10
11
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 |
|---|--------------|--------|

精准新增

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

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

1
2
3
4
5
6
7
8
9
10
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 条件查询

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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查询

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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 |
|---|--------------|--------|

大于等于查询

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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 分库分表

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

http://blog.gxitsky.com/2020/09/29/sharding-jdbc-6-single-db-sharding-table/

作者

光星

发布于

2020-09-29

更新于

2021-02-05

许可协议

评论