分库分表和绑定表
原创大约 5 分钟
分库分表
之前的分表实践只是将数据分到了不同的表中，现在需要更进一步。
先根据
userid将数据划分到不同的数据库中。再根据订单
id将数据划分到不同的数据表中。
先清空itechthink_order_0、itechthink_order_1和itechthink_order_2这三个数据库中所有t_order表的数据。
> USE itechthink_order_0;
> TRUNCATE TABLE t_order_0;
> TRUNCATE TABLE t_order_1;
> USE itechthink_order_1;
> TRUNCATE TABLE t_order_0;
> TRUNCATE TABLE t_order_1;
> USE itechthink_order_2;
> TRUNCATE TABLE t_order_0;
> TRUNCATE TABLE t_order_1;接着修改配置文件application.properties。
......
# 配置分库规则
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=userid
# 因为有三个数据库，所以要对3取模
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{userid % 3}
# 指定 t_order 表的数据节点
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order_$->{0..1},ds1.t_order_$->{0..1},ds2.t_order_$->{0..1}
# 指定 t_order 表的分片键和分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{id % 2}ShardingJDBCTest测试类不用修改，直接执行测试。
执行后发现，itechthink_order_0、itechthink_order_1和itechthink_order_2这三个数据库中的数据分布如下。
> USE itechthink_order_0;
> SELECT * FROM t_order_0;
+---------------------+--------+------------------------------+-------+--------+---------------------+
| id | userid | tradeno | state | money | createtime |
+---------------------+--------+------------------------------+-------+--------+---------------------+
| 1822949717554790402 | 3 | 190b3247195c4e15a8e9d3a0d209 | 1 | 168.00 | 2023-06-09 23:54:16 |
| 1822949717668036610 | 6 | b7eb8ec92f1d4f30b1aaad89cdec | 1 | 168.00 | 2023-06-09 23:54:16 |
+---------------------+--------+------------------------------+-------+--------+---------------------+
2 rows in set (0.00 sec)
> SELECT * FROM t_order_1;
+---------------------+--------+------------------------------+-------+--------+---------------------+
| id | userid | tradeno | state | money | createtime |
+---------------------+--------+------------------------------+-------+--------+---------------------+
| 1822949714530697217 | 0 | dc585b6f03b44b6ba803adb3a787 | 1 | 168.00 | 2023-06-09 23:54:15 |
| 1822949717772894209 | 9 | 7293509c4caf44fc9b6775ddb41f | 1 | 168.00 | 2023-06-09 23:54:16 |
+---------------------+--------+------------------------------+-------+--------+---------------------+
2 rows in set (0.00 sec)
> USE itechthink_order_1;
> SELECT * FROM t_order_0;
+---------------------+--------+------------------------------+-------+--------+---------------------+
| id | userid | tradeno | state | money | createtime |
+---------------------+--------+------------------------------+-------+--------+---------------------+
| 1822949717466710018 | 1 | 8fb09e4a874c4255bdf40aba430d | 1 | 168.00 | 2023-06-09 23:54:16 |
| 1822949717588344834 | 4 | 08c2b60a818c4b3daafed513d283 | 1 | 168.00 | 2023-06-09 23:54:16 |
+---------------------+--------+------------------------------+-------+--------+---------------------+
2 rows in set (0.00 sec)
> SELECT * FROM t_order_1;
+---------------------+--------+------------------------------+-------+--------+---------------------+
| id | userid | tradeno | state | money | createtime |
+---------------------+--------+------------------------------+-------+--------+---------------------+
| 1822949717705785345 | 7 | 1f917792d23143c1b5c3e1ab9580 | 1 | 168.00 | 2023-06-09 23:54:16 |
+---------------------+--------+------------------------------+-------+--------+---------------------+
1 row in set (0.00 sec)
> USE itechthink_order_2;
> SELECT * FROM t_order_0;
+---------------------+--------+------------------------------+-------+--------+---------------------+
| id | userid | tradeno | state | money | createtime |
+---------------------+--------+------------------------------+-------+--------+---------------------+
| 1822949717743534082 | 8 | a0972b6ab8d54c6ca39e534b5ad1 | 1 | 168.00 | 2023-06-09 23:54:16 |
+---------------------+--------+------------------------------+-------+--------+---------------------+
1 row in set (0.00 sec)
> SELECT * FROM t_order_1;
+---------------------+--------+------------------------------+-------+--------+---------------------+
| id | userid | tradeno | state | money | createtime |
+---------------------+--------+------------------------------+-------+--------+---------------------+
| 1822949717508653057 | 2 | 22237c07202f452389e4af38b1cd | 1 | 168.00 | 2023-06-09 23:54:16 |
| 1822949717634482177 | 5 | 18a0550e30e44cee9b6c3be587aa | 1 | 168.00 | 2023-06-09 23:54:16 |
+---------------------+--------+------------------------------+-------+--------+---------------------+
2 rows in set (0.00 sec)所有的数据加起来，刚好是10条。
绑定表
绑定表就是所谓的子表，这在实际项目中非常普遍。
为t_order表创建子表t_order_item，同样在每个数据库中创建两张子表。
-- 依次在`itechthink_order_0`、`itechthink_order_1`和`itechthink_order_2`中执行以下建表语句
> DROP TABLE IF EXISTS t_order_item_0;
> CREATE TABLE t_order_item_0 (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '订单详情编码',
orderid BIGINT(20) NOT NULL COMMENT '订单编码',
productid BIGINT(20) DEFAULT NULL COMMENT '产品编码',
productname VARCHAR(256) DEFAULT NULL COMMENT '产品名称',
buynum INT(11) DEFAULT NULL COMMENT '购买数量',
userid BIGINT(20) DEFAULT NULL COMMENT '用户编码',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '订单详情表0';
> DROP TABLE IF EXISTS t_order_item_1;
> CREATE TABLE t_order_item_1 (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '订单详情编码',
orderid BIGINT(20) NOT NULL COMMENT '订单编码',
productid BIGINT(20) DEFAULT NULL COMMENT '产品编码',
productname VARCHAR(256) DEFAULT NULL COMMENT '产品名称',
buynum INT(11) DEFAULT NULL COMMENT '购买数量',
userid BIGINT(20) DEFAULT NULL COMMENT '用户编码',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '订单详情表1';然后依旧是为子表t_order_item增加实体类。
package com.itechthink.model;
import com.baomidou.mybatisplus.annotation.TableName;
import java.util.Objects;
@TableName("t_order_item")
public class OrderItem {
private Long id;
private Long orderid;
private Long productid;
private String productname;
private Integer buynum;
private Long userid;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getOrderid() {
return orderid;
}
public void setOrderid(Long orderid) {
this.orderid = orderid;
}
public Long getProductid() {
return productid;
}
public void setProductid(Long productid) {
this.productid = productid;
}
public String getProductname() {
return productname;
}
public void setProductname(String productname) {
this.productname = productname;
}
public Integer getBuynum() {
return buynum;
}
public void setBuynum(Integer buynum) {
this.buynum = buynum;
}
public Long getUserid() {
return userid;
}
public void setUserid(Long userid) {
this.userid = userid;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
OrderItem orderItem = (OrderItem) o;
return Objects.equals(id, orderItem.id) && Objects.equals(orderid, orderItem.orderid) && Objects.equals(productid, orderItem.productid) && Objects.equals(productname, orderItem.productname) && Objects.equals(buynum, orderItem.buynum) && Objects.equals(userid, orderItem.userid);
}
@Override
public int hashCode() {
return Objects.hash(id, orderid, productid, productname, buynum, userid);
}
@Override
public String toString() {
return "OrderItem{" +
"id=" + id +
", orderid=" + orderid +
", productid=" + productid +
", productname='" + productname + '\'' +
", buynum=" + buynum +
", userid=" + userid +
'}';
}
}以及相应的mapper映射。
package com.itechthink.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itechthink.model.OrderItem;
public interface OrderItemMapper extends BaseMapper<OrderItem> {
}修改t_order表的mapper映射，增加一个查询方法。
package com.itechthink.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itechthink.model.Order;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface OrderMapper extends BaseMapper<Order> {
@Select("SELECT * FROM t_order o, t_order_item i WHERE o.id = i.orderid")
List<Object> listOrderItem();
}修改配置文件application.properties。
......
# 将之前的 t_order 表分库策略改为全局默认的分库策略，也就是所有的表都使用的分库策略
# 如果使用默认的分库策略，那么分片键就要事先统一设定好
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = userid
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{userid % 3}
# 指定 t_order_item 表的数据节点
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds0.t_order_item_$->{0..1},ds1.t_order_item_$->{0..1},ds2.t_order_item_$->{0..1}
# 指定 t_order_item 表的分片键和分片算法
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=orderid
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{orderid % 2}
# 配置绑定表
# 这个配置可以避免产生交叉查询，也就是 t_order_0 只和 t_order_item_0 关联，而 t_order_1 只和 t_order_item_1 关联，不会产生 t_order_0 和 t_order_item_1 关联，或者 t_order_1 和 t_order_item_0 关联的情况
spring.shardingsphere.sharding.binding‐tables[0] = t_order,t_order_item最后修改ShardingJDBCTest测试类，加入如下测试方法。
@Test
public void testBind(){
List<Object> list = orderMapper.listOrderItem();
System.out.println(Arrays.toString(list.toArray()));
}执行测试，可以发现，如果不加绑定表的配置项，也就是spring.shardingsphere.sharding.binding‐tables[0] = t_order,t_order_item，那么查询结果输出如下（t_order_item表中有没有数据都不影响，主要是为了验证会不会产生交叉查询）。
SQLStatement: SelectStatementContext......
Actual SQL: ds0 ::: select * from t_order_0 o left join t_order_item_1 i on o.id = i.orderid -- t_order_0 和 t_order_item_1 产生了交叉查询
Actual SQL: ds0 ::: select * from t_order_0 o left join t_order_item_0 i on o.id = i.orderid
Actual SQL: ds0 ::: select * from t_order_1 o left join t_order_item_1 i on o.id = i.orderid
Actual SQL: ds0 ::: select * from t_order_1 o left join t_order_item_0 i on o.id = i.orderid -- t_order_1 和 t_order_item_0 产生了交叉查询
Actual SQL: ds2 ::: select * from t_order_0 o left join t_order_item_1 i on o.id = i.orderid
Actual SQL: ds2 ::: select * from t_order_0 o left join t_order_item_0 i on o.id = i.orderid
Actual SQL: ds2 ::: select * from t_order_1 o left join t_order_item_1 i on o.id = i.orderid
Actual SQL: ds2 ::: select * from t_order_1 o left join t_order_item_0 i on o.id = i.orderid
Actual SQL: ds1 ::: select * from t_order_0 o left join t_order_item_1 i on o.id = i.orderid
Actual SQL: ds1 ::: select * from t_order_0 o left join t_order_item_0 i on o.id = i.orderid
Actual SQL: ds1 ::: select * from t_order_1 o left join t_order_item_1 i on o.id = i.orderid
Actual SQL: ds1 ::: select * from t_order_1 o left join t_order_item_0 i on o.id = i.orderid
[1823268069208842242, 1823268072451039234, 1823268072543313922, 1823268072627200002, 1823268069208842242, 1823268072451039234, 1823268072543313922, 1823268072627200002, 1823268072597839874, 1823268072597839874, ......]而加上了这条配置项后，查询结果输出如下。
SQLStatement: SelectStatementContext......
Actual SQL: ds0 ::: select * from t_order_0 o left join t_order_item_0 i on o.id = i.orderid
Actual SQL: ds0 ::: select * from t_order_1 o left join t_order_item_1 i on o.id = i.orderid
Actual SQL: ds1 ::: select * from t_order_0 o left join t_order_item_0 i on o.id = i.orderid
Actual SQL: ds1 ::: select * from t_order_1 o left join t_order_item_1 i on o.id = i.orderid
Actual SQL: ds2 ::: select * from t_order_0 o left join t_order_item_0 i on o.id = i.orderid
Actual SQL: ds2 ::: select * from t_order_1 o left join t_order_item_1 i on o.id = i.orderid
[1823268069208842242, 1823268072451039234, 1823268072543313922, 1823268072627200002, 1823268072484593666, 1823268072358764545, 1823268072568479745, 1823268072597839874, 1823268072396513281, 1823268072522342401]明显没有交叉查询了，查询次数少了一半。
感谢支持
更多内容，请移步《超级个体》。