One 、 Broadcast table

Broadcast table ： It refers to the table that exists in all partitioned data sources , The table structure and the data in the table are exactly the same in each database .

It is suitable for scenarios where the amount of data is not large and needs to be associated with massive data tables , There's no need to slice these watches .

for example ： For example, dictionary table 、 Province information, etc , Because they don't have much data , Moreover, this kind of table may need to be associated with tables with massive data .

Be careful ： If the specified table is a broadcast table , The sub table of this policy will not take effect , That is, the broadcast table will not fragment the data .

CREATE TABLE `t_dict`  (
  `id` bigint(0) PRIMARY KEY NOT NULL,
  `status_code` varchar(100) NOT NULL,
  `status_value` varchar(100) NOT NULL,
	`enabled` tinyint(1) NOT NULL DEFAULT 1
);

1、application.properties The configuration file

stay application.properties This is done in the configuration file

#  Configure real data sources 
spring.shardingsphere.datasource.names=db1,db2
#  Configure the 1 Data sources 
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding_db1?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=123456
#  Configure the 2 Data sources 
spring.shardingsphere.datasource.db2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://localhost:3306/sharding_db2?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=123456


#  Specify the distribution of virtual and real tables ： sub-treasury , table 
##  The broadcast table does not fragment the data , So configure sub database , The split table rule will not take effect 
#spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=db$->{1..2}.t_dict_$->{1..2}
## Broadcast table configuration 
spring.shardingsphere.sharding.broadcast-tables=t_dict
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_dict.key-generator.props.worker.id=1

#  open shardingsphere Of sql Log output .
spring.shardingsphere.props.sql.show=true

#  An entity class corresponds to two tables , Cover 
spring.main.allow-bean-definition-overriding=true

2、 Save the test

 @Test
    public void testSave() throws InterruptedException {
    
        Dict dict = new Dict();
        dict.setEnabled(true);
        dict.setStatusCode("1");
        dict.setStatusValue(" normal ");
        dictMapper.insert(dict);

        Dict dict2 = new Dict();
        dict.setEnabled(true);
        dict2.setStatusCode("2");
        dict2.setStatusValue(" To be certified ");
        dictMapper.insert(dict2);
    }

Two 、 Binding table

Binding table ： It refers to the relationship table with consistent segmentation rules （ Main table 、 Sub table ）.

for example ：t_order Table and t_order_item surface , All in accordance with order_id Fragmentation , The two tables are bound to each other . There will be no Cartesian product association in multi table Association queries between bound tables , The efficiency of association query will be greatly improved .

CREATE TABLE t_order_1 (
	id BIGINT(20) PRIMARY KEY,
	gmt_create timestamp NULL DEFAULT NULL,
	name VARCHAR(50) NOT NULL,
	status varchar(10) NOT NULL
);
CREATE TABLE t_order_2 (
	id BIGINT(20) PRIMARY KEY,
	gmt_create timestamp NULL DEFAULT NULL,
	name VARCHAR(50) NOT NULL,
	status varchar(10) NOT NULL
);

CREATE TABLE t_order_item_1 (
	id BIGINT(20) PRIMARY KEY AUTO_INCREMENT,
	gmt_create timestamp NULL DEFAULT NULL,
	order_id BIGINT(20) NOT NULL,
	comment  varchar(100) NOT NULL
);
CREATE TABLE t_order_item_2 (
	id BIGINT(20) PRIMARY KEY AUTO_INCREMENT,
	gmt_create timestamp NULL DEFAULT NULL,
	order_id BIGINT(20) NOT NULL,
	comment  varchar(100) NOT NULL
);

1、application.properties The configuration file

stay application.properties This is done in the configuration file

##2. Binding table configuration ：t_order and t_order_item surface , All in accordance with order_id Fragmentation 
#  Specify the distribution of virtual and real tables ：t_order surface 
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=db$->{
   1..2}.t_order_$->{
   1..2}
#  Specifies the primary key generation policy for the table , Snowflake algorithm 
spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=1
## Specify the fragmentation policy 
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=db$->{id%2+1}
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+1}

#  Specify the distribution of virtual and real tables ：t_order_item surface 
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=db$->{
   1..2}.t_order_item_$->{
   1..2}
#  Specifies the primary key generation policy for the table , Snowflake algorithm 
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.worker.id=1

## Specify the fragmentation policy 
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.algorithm-expression=db$->{order_id%2+1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id%2+1}

# Binding table configuration 
spring.shardingsphere.sharding.binding-tables[0]=t_order,t_order_item

2、 test

1） preservation

@Test
	public void testSave() throws InterruptedException {
    
		for (int i = 0; i < 5; i++) {
    
			Order order = new Order();
			order.setGmtCreate(new Date());
			order.setName("java");
			order.setStatus("1");
			orderMapper.insert(order);
			System.out.println("OrderId ---->" + order.getId());

			OrderItem orderItem = new OrderItem();
			orderItem.setOrderId(order.getId());
			orderItem.setGmtCreate(new Date());
			orderItem.setComment("comment" + i);
			orderItemMapper.insert(orderItem);
		}

	}

2） Relational query

public interface OrderMapper extends BaseMapper<Order> {
    
    @Select("select o.id id, o.gmt_create gmt_create ,o.name name, o.status status, oi.comment comment\n" +
            "from t_order o left join t_order_item oi on o.id =oi.order_id")
    List<OrderDetailDTO> queryOrderDetail();

}

	@Test
	public void testGetById() {
    
		List<OrderDetailDTO> list = orderMapper.queryOrderDetail();
		System.out.println(list.size());
		 list.forEach(o -> System.out.println(o));
	} 

– Stay Hungry , stay foolish .