ShardingSphere初探（二）
广播表
广播表是指在分布式数据库系统中，每个数据节点上都拥有其完整副本的表。无论查询操作在哪个节点上执行，广播表的数据在所有节点上都是一致的。
演示
- 表创建,分别在库1和库2创建t_dict表
CREATE TABLE `t_dict` (
`dict_id` bigint(20) NOT NULL,
`ustatus` varchar(255) DEFAULT NULL,
`uvalue` varchar(255) DEFAULT NULL,
PRIMARY KEY (`dict_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- 创建DictMapper
public interface DictMapper extends BaseMapper<Dict> {
}
- 创建entity
@TableName("t_dict")
public class Dict {
@TableField("dict_id")
private Long dictId;
private String ustatus;
private String uvalue;
getter setter....
}
- 广播表配置，将t_dict配置成广播表，广播表配置后，不受任何分片影响
spring:
shardingsphere:
datasource:
names: m1,m2
m1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://47.109.94.124:3306/coursedb?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
m2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://47.109.188.99:3306/coursedb?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
sharding:
#配置广播表
broadcast-tables: t_dict
props:
sql:
#开启sp sql日志
show: true
- 测试
public void addDict(){
Dict dict = new Dict();
dict.setDictId(1L);
dict.setUstatus("1");
dict.setUvalue("正常");
dictMapper.insert(dict);
Dict dict2 = new Dict();
dict2.setDictId(2L);
dict2.setUstatus("2");
dict2.setUvalue("异常");
dictMapper.insert(dict2);
}
观察日志
2024-06-28 22:57:33.786 INFO 5628 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_dict ( dict_id,ustatus,uvalue ) VALUES ( ?,?,? )
2024-06-28 22:57:33.787 INFO 5628 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: INSERT INTO t_dict ( dict_id,ustatus,uvalue ) VALUES (?, ?, ?) ::: [1, 1, 正常]
2024-06-28 22:57:33.787 INFO 5628 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: INSERT INTO t_dict ( dict_id,ustatus,uvalue ) VALUES (?, ?, ?) ::: [1, 1, 正常]
2024-06-28 22:57:33.860 INFO 5628 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_dict ( dict_id,ustatus,uvalue ) VALUES ( ?,?,? )
2024-06-28 22:57:33.860 INFO 5628 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: INSERT INTO t_dict ( dict_id,ustatus,uvalue ) VALUES (?, ?, ?) ::: [2, 2, 异常]
2024-06-28 22:57:33.860 INFO 5628 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: INSERT INTO t_dict ( dict_id,ustatus,uvalue ) VALUES (?, ?, ?) ::: [2, 2, 异常]
通过实际执行sql可以看到，ShardingSphere在m1,m2库中均执行了插入数据的操作。
绑定表
绑定表是指多个逻辑上存在关联关系的表（如外键关联的表），它们通过相同的分片键和分片规则进行分片，以确保相关联的数据存储在相同的物理节点上。
演示
- 表创建,分别在库1和库2创建orders_ − > 1..2 和 o r d e r i t e m s _ ->{1..2}和order_items\_ −>1..2和orderitems_->{1…2}表
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
order_date DATE
);
CREATE TABLE order_items (
item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT
);
- 创建entity
@TableName("orders")
public class Orders {
@TableField("order_id")
private Integer orderId;
@TableField("user_id")
private Integer userId;
@TableField("order_date")
private Date orderDate;
...getter setter
}
@TableName("order_items")
public class OrderItems {
@TableField("item_id")
private Integer itemId;
@TableField("order_id")
private Integer orderId;
@TableField("product_id")
private Integer productId;
private Integer quantity;
...getter setter
}
- 创建对应的mapper
public interface OrderItemsMapper extends BaseMapper<OrderItems> {
}
public interface OrdersMapper extends BaseMapper<Orders> {
}
- yaml配置
spring:
shardingsphere:
datasource:
names: m1,m2
m1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://47.109.94.124:3306/coursedb?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
m2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://47.109.188.99:3306/coursedb?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
sharding:
tables:
orders:
actual-data-nodes: m$->{1..2}.orders_$->{1..2}
database-strategy:
inline:
sharding-column: order_id
algorithm-expression: m$->{order_id % 2 + 1}
table-strategy:
inline:
sharding-column: order_id
algorithm-expression: orders_$->{order_id % 2 + 1}
order_items:
actual-data-nodes: m$->{1..2}.order_items_$->{1..2}
database-strategy:
inline:
sharding-column: order_id
algorithm-expression: m$->{order_id % 2 + 1}
table-strategy:
inline:
sharding-column: order_id
algorithm-expression: order_items_$->{order_id % 2 + 1}
props:
sql:
#开启sp sql日志
show: true
- 插入数据
public void addOrders(){
for (int i = 0; i <10; i++) {
Orders orders = new Orders();
orders.setOrderDate(new Date());
orders.setUserId(i + 1);
orders.setOrderId(i + 1);
ordersMapper.insert(orders);
Integer orderId = orders.getOrderId();
OrderItems orderItems = new OrderItems();
orderItems.setItemId(i + 1);
orderItems.setOrderId(orderId);
orderItems.setQuantity(1);
orderItems.setProductId(i);
orderItemsMapper.insert(orderItems);
}
}
m1->orders_1的数据如下
| order_id | user_id | order_date |
|---|---|---|
| 2 | 2 | 2024-06-28 |
| 4 | 4 | 2024-06-28 |
| 6 | 6 | 2024-06-28 |
| 8 | 8 | 2024-06-28 |
| 10 | 10 | 2024-06-28 |
m1->orders_2为空
m1->order_items_1的数据如下
| item_id | order_id | product_id | quantity |
|---|---|---|---|
| 2 | 2 | 1 | 1 |
| 4 | 4 | 3 | 1 |
| 6 | 6 | 5 | 1 |
| 8 | 8 | 7 | 1 |
| 10 | 10 | 9 | 1 |
m1->order_items_2的数据为空
可以看到，由于是相同的分片键，而且分片算法一样，所以基于order_id的情况，分片数据落到同一种上去了，既orders_1的数据在 order_items_1中
同理，m2库中也一样，orders_2的数据在order_items_2中,这里不做数据展示
非绑定演示
- 执行以下sql会出现什么结果呢
SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders o JOIN order_items oi ON o.order_id = oi.order_id
2024-06-29 00:36:59.544 INFO 15972 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_2 o JOIN order_items_1 oi ON o.order_id = oi.order_id
2024-06-29 00:36:59.544 INFO 15972 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_2 o JOIN order_items_2 oi ON o.order_id = oi.order_id
2024-06-29 00:36:59.544 INFO 15972 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_1 o JOIN order_items_1 oi ON o.order_id = oi.order_id
2024-06-29 00:36:59.544 INFO 15972 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_1 o JOIN order_items_2 oi ON o.order_id = oi.order_id
2024-06-29 00:36:59.544 INFO 15972 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_2 o JOIN order_items_1 oi ON o.order_id = oi.order_id
2024-06-29 00:36:59.544 INFO 15972 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_2 o JOIN order_items_2 oi ON o.order_id = oi.order_id
2024-06-29 00:36:59.544 INFO 15972 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_1 o JOIN order_items_1 oi ON o.order_id = oi.order_id
2024-06-29 00:36:59.544 INFO 15972 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_1 o JOIN order_items_2 oi ON o.order_id = oi.order_id
通过日志可以看到，sharding在两个表未进行绑定的时候，会默认m1中orders_1和order_items2进行关联查询，最后再聚合。
可是由于我们的分片键是通过order_id进行同样分片算法的，我们知道。orders_1中的order_items数据一定在order_items_2中。
这样浪费了查询次数，也可以发生笛卡尔积现象
通过在yaml中增加绑定表配置
spring:
sharding:
binding-tables:
- orders,order_items
将orders和order_items进行绑定。
- 再次执行，观察具体sql执行情况
2024-06-29 00:48:38.665 INFO 5700 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_1 o JOIN order_items_1 oi ON o.order_id = oi.order_id
2024-06-29 00:48:38.665 INFO 5700 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_2 o JOIN order_items_2 oi ON o.order_id = oi.order_id
2024-06-29 00:48:38.665 INFO 5700 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_1 o JOIN order_items_1 oi ON o.order_id = oi.order_id
2024-06-29 00:48:38.665 INFO 5700 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_2 o JOIN order_items_2 oi ON o.order_id = oi.order_id
可以看到，orders_1只会关联order_items_1，orders_2只会关联order_items_2，这样避免了跨节点查询，优化了查询性能。
读写分离
真正的读写分离是mysql主从中的概念，Sharding在应用层面只能做到读在一个库，写在另一个库。数据同步是由mysql主从通过binlog同步。
如何配置
spring:
shardingsphere:
datasource:
names: m1,m2
m1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://47.109.94.124:3306/coursedb?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
m2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://47.109.188.99:3306/coursedb?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
sharding:
tables:
orders:
actual-data-nodes: ds0.orders_$->{1..2}
database-strategy:
inline:
sharding-column: order_id
algorithm-expression: m$->{order_id % 2 + 1}
table-strategy:
inline:
sharding-column: order_id
algorithm-expression: orders_$->{order_id % 2 + 1}
order_items:
actual-data-nodes: ds0.order_items_$->{1..2}
database-strategy:
inline:
sharding-column: order_id
algorithm-expression: m$->{order_id % 2 + 1}
table-strategy:
inline:
sharding-column: order_id
algorithm-expression: order_items_$->{order_id % 2 + 1}
binding-tables:
- orders,order_items
master-slave-rules:
#配置主从规则名字
ds0:
#配置读写分离,主库是m1,从库是m2
master-data-source-name: m1
#从库可以多个，所以是数组
slave-data-source-names:
- m2
props:
sql:
#开启sp sql日志
show: true
actual-data-nodes中就不会存在m1,m2数据源了，只会存在配置的主从源ds0
这里配置m1负责写，m2负责读
重新执行
SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders o JOIN order_items oi ON o.order_id = oi.order_id
2024-06-29 01:01:09.817 INFO 3664 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_1 o JOIN order_items_1 oi ON o.order_id = oi.order_id
2024-06-29 01:01:09.817 INFO 3664 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: SELECT o.order_id, o.user_id, oi.product_id, oi.quantity FROM orders_2 o JOIN order_items_2 oi ON o.order_id = oi.order_id
可以看到，实际执行sql，不会再去m1库中查询。
901
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
