sharding-jdbc 分表 - user_id 分表
准备
有表, 订单分3个表, 订单明细 4 个表:
mysql> show tables;
+----------------------+
| Tables_in_shard_ds_0 |
+----------------------+
| t_order_0 |
| t_order_1 |
| t_order_2 |
| t_order_item_0 |
| t_order_item_1 |
| t_order_item_2 |
| t_order_item_3 |
| t_sequence |
+----------------------+
8 rows in set (0.00 sec)
表结构:
# t_order_0
CREATE TABLE `t_order_0` (
`id` bigint NOT NULL AUTO_INCREMENT,
`order_no` varchar(50) DEFAULT NULL,
`user_id` int DEFAULT NULL,
`amount` int DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=220458 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
# t_order_item_0
CREATE TABLE `t_order_item_0` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单明细id',
`order_id` bigint DEFAULT NULL COMMENT '订单id',
`item_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
`item_no` varchar(255) DEFAULT NULL COMMENT '商品编号',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12501 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
分表策略
- 根据 t_order 中的 user_id
- 根据 t_order_item 中的 order_id
注意,如果按照 order_id 分表,需要保证 order_id 全局唯一, 不然 join 查询时会有问题!!
项目依赖 - spring boot + mybatis plus
<properties>
<java.version>11</java.version>
<sharding-sphere.version>4.1.1</sharding-sphere.version>
</properties>
<dependencies>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.6</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<!-- 不要加,会和sharding-jdbc-spring-boot-starter 冲突!!!! -->
<!-- <dependency>-->
<!-- <groupId>com.alibaba</groupId>-->
<!-- <artifactId>druid-spring-boot-starter</artifactId>-->
<!-- <version>1.2.6</version>-->
<!-- </dependency>-->
<!-- for spring boot -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!-- for spring namespace -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</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>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.76</version>
</dependency>
</dependencies>
启动类
@SpringBootApplication
@MapperScan("com.x.z.shardjdbc.persist.mapper")
public class ShardingJdbcSampleApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingJdbcSampleApplication.class, args);
}
}
application.properties 配置
spring.shardingsphere.datasource.names=ds0
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/shard_ds_0
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=111111
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order_$->{0..2}
# 分片键
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=user_id
# 分片策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{user_id % 3}
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds0.t_order_item_$->{0..3}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 4}
# 输出 sharding jdbc 查询日志
spring.shardingsphere.props.sql.show=true
logging.level.com.x.z.shardjdbc.persist.mapper=debug
mybatis-plus.type-aliases-package=com.x.z.shardjdbc.persist.model
mapper 文件
public interface OrderModelMapper extends BaseMapper<OrderModel> {
//单表查询
@Select("<script>" +
"select * from t_order where create_time <![CDATA[>=]]>#{start} and create_time<![CDATA[<=]]>#{end}" +
"<if test=\"userId!=null\"> and user_id=#{userId}</if>" +
"</script>")
List<OrderModel> list(OrderPageQuery query);
// join 查询
List<OrderDto> listWithItem(OrderPageQuery query);
}
xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.x.z.shardjdbc.persist.mapper.OrderModelMapper">
<resultMap id="listWithItemMap" type="com.x.z.shardjdbc.dto.OrderDto">
<id property="id" column="id" jdbcType="BIGINT"/>
<result property="orderNo" column="order_no" jdbcType="VARCHAR"/>
<result property="userId" column="user_id" jdbcType="BIGINT"/>
<result property="amount" column="amount" jdbcType="INTEGER"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
<collection property="itemList" ofType="com.x.z.shardjdbc.dto.OrderItemDto" javaType="java.util.ArrayList">
<id property="id" column="item_id" jdbcType="BIGINT"/>
<result property="itemNo" column="item_no" jdbcType="VARCHAR"/>
<result property="itemName" column="item_name" jdbcType="INTEGER"/>
<result property="orderId" column="order_id" jdbcType="BIGINT"/>
<result property="createTime" column="item_create_time" jdbcType="TIMESTAMP"/>
<result property="updateTime" column="item_update_time" jdbcType="TIMESTAMP"/>
</collection>
</resultMap>
<select id="listWithItem" resultMap="listWithItemMap">
SELECT
tor.*,
toi.id as item_id,
toi.item_name,
toi.item_no,
toi.create_time AS item_create_time,
toi.update_time AS item_update_time,
toi.order_id
FROM
t_order tor
INNER JOIN t_order_item toi ON toi.order_id = tor.id
WHERE
tor.create_time <![CDATA[>=]]>#{start}
AND tor.create_time <![CDATA[<=]]>#{end}
<if test="userId!=null">
and tor.user_id=#{userId}
</if>
</select>
</mapper>
测试用例
/**
* 分页查询
*/
@Test
public void pageQueryTest() {
LocalDateTime now = LocalDateTime.now();
OrderPageQuery query = new OrderPageQuery();
query.setPageSize(10);
query.setPageIndex(2);
query.setStart(now.minusDays(30));
query.setEnd(now);
query.setUserId(1L);
PageInfo<OrderModel> pageInfo = orderModelService.pageList(query);
System.out.println(JSON.toJSON(pageInfo));
}
/**
* 分页查询 with join
*/
@Test
public void pageQueryWithJoinTest() {
LocalDateTime now = LocalDateTime.now();
OrderPageQuery query = new OrderPageQuery();
query.setPageSize(10);
query.setPageIndex(1);
query.setStart(now.minusDays(30));
query.setEnd(now);
query.setUserId(1L);
PageInfo<OrderDto> pageInfo = orderModelService.pageListWithJoin(query);
System.out.println(JSON.toJSON(pageInfo));
}
普通分页查询
不带路由键 user_id
@Test
public void pageQueryTest() {
LocalDateTime now = LocalDateTime.now();
OrderPageQuery query = new OrderPageQuery();
query.setPageSize(10);
query.setPageIndex(2);
query.setStart(now.minusDays(30));
query.setEnd(now);
// query.setUserId(1L);
PageInfo<OrderModel> pageInfo = orderModelService.pageList(query);
System.out.println(JSON.toJSON(pageInfo));
}
# 查询总数
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_0 WHERE create_time >= ? AND create_time <= ?
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_1 WHERE create_time >= ? AND create_time <= ?
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_2 WHERE create_time >= ? AND create_time <= ?
# 分页
Actual SQL: ds0 ::: select * from t_order_0 where create_time >= ? and create_time <= ? LIMIT ?, ? ::: [0, 20]
Actual SQL: ds0 ::: select * from t_order_1 where create_time >= ? and create_time <= ? LIMIT ?, ? ::: [0, 20]
Actual SQL: ds0 ::: select * from t_order_2 where create_time >= ? and create_time <= ?LIMIT ?, ? ::: [0, 20]
可以发现,分页查询总数时 sharding jdbc 对每个表都进行了计数查询,但是在分页时, 实际上取的分页大小比我们传入的值要大, 我设置的是10, 但是sharding jdbc 取的 limit 是 20 (发现规律: limit 大小等于 pageIndexpageSize = 210) 。 并且 ,不带路由键时, 会查询所有表
带路由键 user_id
@Test
public void pageQueryTest() {
LocalDateTime now = LocalDateTime.now();
OrderPageQuery query = new OrderPageQuery();
query.setPageSize(10);
query.setPageIndex(2);
query.setStart(now.minusDays(30));
query.setEnd(now);
// 打开注释!!
query.setUserId(1L);
PageInfo<OrderModel> pageInfo = orderModelService.pageList(query);
System.out.println(JSON.toJSON(pageInfo));
}
# 查询总数
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_1 WHERE create_time >= ? AND create_time <= ? AND user_id = ?
# 分页
select * from t_order_1 where create_time >= ? and create_time <= ? and user_id=? LIMIT ?, ? ::: [10, 10]
可以发现, 带路由键, 统计的表明显要少得多, 查询效率也比较快
分页查询 with join
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_0 tor INNER JOIN t_order_item_1 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_0 tor INNER JOIN t_order_item_0 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_0 tor INNER JOIN t_order_item_3 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_0 tor INNER JOIN t_order_item_2 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_1 tor INNER JOIN t_order_item_1 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_1 tor INNER JOIN t_order_item_0 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_1 tor INNER JOIN t_order_item_3 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_1 tor INNER JOIN t_order_item_2 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_2 tor INNER JOIN t_order_item_1 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_2 tor INNER JOIN t_order_item_0 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_2 tor INNER JOIN t_order_item_3 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_2 tor INNER JOIN t_order_item_2 toi ON toi.order_id = tor.id WHERE...
其实和上面一样, 关联查询时, 未带上路由键, 用的是迪卡尔积关联表查询总数 ,如果带上路由键, 关联查询的表就要少一点