需求说明
本章节使用Sharding-JDBC完成对订单表的水平分表,通过快速入门程序的开发,快速体验Sharding-JDBC的使用 方法。
人工创建两张表,t_order_1和t_order_2,这两张表是订单表拆分后的表,通过Sharding-Jdbc向订单表插入数据, 按照一定的分片规则,主键为偶数的进入t_order_1,另一部分数据进入t_order_2,通过Sharding-Jdbc 查询数 据,根据 SQL语句的内容从t_order_1或t_order_2查询数据。
环境搭建
环境说明:
- 数据库:mysql-5.7
- jdk:1.8
- springboot:2.1.3.RELEASE
- sharding-jdbc-spring-boot-starter:4.0.0-RC1
- mybatis-spring-boot-starter:2.0.0
- druid-spring-boot-starter:1.1.16
- mysql-connector-java:5.1.47
数据库:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
version: '2'
services:
standalone:
image: mysql:5.7
container_name: standalone
volumes:
- ./standalone/log:/var/log/mysql
- ./standalone/data:/var/lib/mysql
- ./standalone/conf:/etc/mysql/mysql.conf.d
restart: always
environment:
- MYSQL_ROOT_PASSWORD=333
- MYSQL_ROOT_HOST=%
ports:
- 3306:3306
|
数据准备:
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
|
CREATE DATABASE `order_db` CHARACTER
SET 'utf8' COLLATE 'utf8_general_ci';
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
`order_id` BIGINT ( 20 ) NOT NULL COMMENT '订单id',
`price` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单价格',
`user_id` BIGINT ( 20 ) NOT NULL COMMENT '下单用户id',
`status` VARCHAR ( 50 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY ( `order_id` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
`order_id` BIGINT ( 20 ) NOT NULL COMMENT '订单id',
`price` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单价格',
`user_id` BIGINT ( 20 ) NOT NULL COMMENT '下单用户id',
`status` VARCHAR ( 50 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY ( `order_id` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
show tables;
|
编写程序
pom
引入 sharding-jdbc和SpringBoot整合的Jar包:
1
2
3
4
5
6
|
<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
|
完整pom配置
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
|
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>sharding-jdbc-test</artifactId>
<groupId>com.eh</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>sharding-jdbc-simple</artifactId>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--数据库-->
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.0</version>
</dependency>
<!--数据源druid-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.16</version>
</dependency>
<!--mysql-connector-java-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
</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>
</project>
|
yml
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
|
debug=true
server.port=8001
spring.application.name=sharding-jdbc-simple
# 使用bean定义覆盖,sharding-jdbc的datasource覆盖druid定义的datasource,如果不覆盖会报
# Invalid bean definition with name 'dataSource' defined in class path resource
spring.main.allow-bean-definition-overriding = true
# 以下是分片规则配置
# 定义数据源
# 数据源名称
spring.shardingsphere.datasource.names=m1
# 给m1数据源设置连接属性
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/order_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=333
# 指定t_order表的数据分布情况,配置数据节点
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m1.t_order_$->{1..2}
# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 指定t_order表的分片策略,分片策略包括分片键和分片算法
# 分片键
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
# 分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=\
t_order_$->{order_id % 2 + 1}
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
|
主启动
1
2
3
4
5
6
7
8
9
10
11
12
13
|
package com.eh.shardingjdbc.order;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@MapperScan("com.eh.shardingjdbc.order.dao")
@SpringBootApplication
public class OrderMainApplication {
public static void main(String[] args) {
SpringApplication.run(OrderMainApplication.class, args);
}
}
|
业务类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
package com.eh.shardingjdbc.order.dao;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import java.math.BigDecimal;
public interface OrderMapper {
/**
* 新增订单 * @param price 订单价格 * @param userId 用户id * @param status 订单状态 * @return
*/
@Insert("insert into t_order(price,user_id,status) value(#{price},#{userId},#{status})")
int insertOrder(@Param("price") BigDecimal price, @Param("userId") Long userId, @Param("status") String status);
}
|
测试类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
package com.eh.shardingjdbc.order.dao;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.math.BigDecimal;
@RunWith(SpringRunner.class)
@SpringBootTest
public class OrderMapperTest {
@Autowired
private OrderMapper orderMapper;
@Test
public void insertOrder() {
for (int i = 1; i < 20; i++) {
orderMapper.insertOrder(new BigDecimal(i), 1L, "SUCCESS");
}
}
}
|
往数据库插入了19条数据,按照分表规则,应该是偶数id进t_order_1表,奇数id进t_order_2表,查看数据库,确实如此

再来一个查询测试
OrderMapper
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
/**
* 根据id列表查询订单
*
* @param orderIds
* @return
*/
@Select("<script>" +
"select" +
" * " +
" from t_order t " +
" where t.order_id in " +
" <foreach collection='orderIds' open='(' separator=',' close=')' item='id'>" +
" #{id} " +
" </foreach>" +
"</script>")
List<Map> selectOrderByIds(@Param("orderIds") List<Long> orderIds);
|
测试类:
1
2
3
4
5
6
7
8
9
10
11
12
|
/**
* 查询两个表中的id
*/
@Test
public void testSelectOrderByIds() {
List<Long> ids = new ArrayList<>();
ids.add(537337422749368320L);
ids.add(537337421923090433L);
List<Map> maps = orderMapper.selectOrderByIds(ids);
System.out.println(maps);
}
|
执行日志:

流程分析
通过日志分析,Sharding-JDBC在拿到用户要执行的sql之后干了哪些事儿:
- 解析sql,获取片键值,在本例中是order_id
- Sharding-JDBC通过规则配置
t_order_$->{order_id % 2 + 1}
,知道了当order_id为偶数时,应该往 t_order_1表插数据,为奇数时,往t_order_2插数据。
- 于是Sharding-JDBC根据order_id的值改写sql语句,改写后的SQL语句是真实所要执行的SQL语句。
- 执行改写后的真实sql语句
- 将所有真正执行sql的结果进行汇总合并,返回。
其他配置方式
Sharding-JDBC可以与spring boot良好集成,它还支持其他配置方式,共支持以下四种集成方式。
-
yml(配置复杂了之后看起来杂乱无章)
-
properties(推荐)
-
Java配置类
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
|
package com.eh.shardingjdbc.order.dao;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class ShardingJdbcConfig {
// 定义数据源
Map<String, DataSource> createDataSourceMap() {
DruidDataSource dataSource1 = new DruidDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://localhost:3306/order_db?useUnicode=true");
dataSource1.setUsername("root");
dataSource1.setPassword("root");
Map<String, DataSource> result = new HashMap<>();
result.put("m1", dataSource1);
return result;
}
// 定义主键生成策略
private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE", "order_id");
return result;
}
// 定义t_order表的分片策略
TableRuleConfiguration getOrderTableRuleConfiguration() {
TableRuleConfiguration result = new TableRuleConfiguration("t_order", "m1.t_order_$‐> {1..2}");
result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_$‐>{order_id % 2 + 1}"));
result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
return result;
}
// 定义sharding‐Jdbc数据源
@Bean
DataSource getShardingDataSource() throws SQLException {
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
Properties properties = new Properties();
//spring.shardingsphere.props.sql.show = true
properties.put("sql.show", "true");
return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties);
}
}
|
-
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
|
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd http://shardingsphere.apache.org/schema/shardingsphere/sharding
http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<context:annotation-config/>
<!--定义多个数据源-->
<bean id="m1" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/order_db_1?useUnicode=true"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</bean>
<!--定义分库策略-->
<sharding:inline-strategy id="tableShardingStrategy" sharding-column="order_id"
algorithm-expression="t_order_$->{order_id % 2 + 1}"/>
<!--定义主键生成策略-->
<sharding:key-generator id="orderKeyGenerator" type="SNOWFLAKE" column="order_id"/>
<!--定义sharding-Jdbc数据源-->
<sharding:data-source id="shardingDataSource">
<sharding:sharding-rule data-source-names="m1">
<sharding:table-rules>
<sharding:table-rule logic-table="t_order" table-strategy-ref="tableShardingStrategy"
key-generator-ref="orderKeyGenerator"/>
</sharding:table-rules>
</sharding:sharding-rule>
</sharding:data-source>
</beans>
|