Mycat Study notes tenth. Er shards of data shards

1 Application Scenarios

In this paper, the Mycat of the ER-relational shards, the so-called ER-relational shards can be understood as data fragmentation between relational tables. Similar to the Shard storage rules between the Order Master table and the Order Detail table.

The ER shards mentioned in this article are divided into two types:

A. Data sharding according to the primary key, verify that the main table data is saved in the 1th Datanode, and the sub-table data is stored according to the Shard rules.

B. Shards are based on the Shard key field, verifying that the primary and child tables are stored according to the Shard rules and saved in the same shard.

Next, the actual configuration and data validation can be

2 Environmental Description

Refer to the sixth of the MYCAT study notes. Data shard by month data Shard http://www.cnblogs.com/kaye0110/p/5160826.html

3 parameter configuration

3.1 Server.xml Configuration

Ibid reference

3.2 Schema.xml Configuration

<schema name= "Rangedb" checksqlschema= "false" sqlmaxlimit= ">"
　　<!--Plan A. Shard with primary key, using Sharding-by-pid rule (Shard according to T_ER_CHILD_1.CID value/256)--

<table name= "t_er_parent_1" datanode= "dn$4-7" rule= "Sharding-by-pid" >
<childtable name= "t_er_child_1" primarykey= "CID" joinkey= "pid" parentkey= "pid"/>
</table>

<!--Plan B. Based on the primary table Shard field pagination, the child table data is followed by the main table.

<table name= "T_er_order" datanode= "dn$4-7" rule= "Sharding-by-long" >
<childtable name= "T_er_detail" primarykey= "detail_id" joinkey= "order_id" parentkey= "order_id"/>
</table>

</schema>

3.3 Rule.xml Configuration

<tablerule name= "Sharding-by-long" >
<rule>
<columns>sharding_long</columns>
<algorithm>func2</algorithm>
</rule>
</tableRule>

<!--the value multiplied by partitioncount and partitionlength needs to be equal to

<function name= "Func2" class= "Org.opencloudb.route.function.PartitionByLong" >
<property name= "Partitioncount" >4</property>
<property name= "Partitionlength" >256</property>
</function>

4 Data validation

4.1 Validation of scenario A

CREATE TABLE ' t_er_parent_1 ' (
' PID ' INT not NULL,
' PName ' VARCHAR NULL,
PRIMARY KEY (' pid '));

CREATE TABLE ' T_er_child_1 ' (
' CID ' INT not NULL,
' PID ' INT not NULL,
' CNAME ' VARCHAR NULL,
PRIMARY KEY (' CID '));

Insert into t_er_parent_1 (pid,pname) VALUES (1, ' parent 255 ');
Insert into T_er_child_1 (cid,pid,cname) VALUES (1,255, ' child 255 ');

Insert into t_er_parent_1 (Pid,pname) VALUES (2, ' parent 256 ');
Insert into T_er_child_1 (cid,pid,cname) VALUES (2,256, ' child 256 ');

Insert into t_er_parent_1 (Pid,pname) VALUES (3, ' parent 512 ');
Insert into T_er_child_1 (cid,pid,cname) VALUES (3,512, ' child 512 ');

Insert into t_er_parent_1 (pid,pname) VALUES (4, ' parent 768 ');
Insert into T_er_child_1 (cid,pid,cname) VALUES (4,768, ' child 768 ');

Mysql> select * from T_er_parent_1;
+-----+------------+
| PID | PName |
+-----+------------+
| 1 | Parent 255 |
| 2 | Parent 256 |
| 3 | Parent 512 |
| 4 | Parent 768 |
+-----+------------+
4 rows in Set (0.01 sec)

Mysql> select * from T_er_child_1;
+-----+-----+-----------+
| CID | PID | CNAME |
+-----+-----+-----------+
| 1 | 255 | Child 255 |
| 4 | 768 | Child 768 |
| 2 | 256 | Child 256 |
| 3 | 512 | Child 512 |
+-----+-----+-----------+
4 rows in Set (0.01 sec)

Look at the log of plan A, all the main table data into the 1th data node dn4, but the child table data into the DN7

01/28 21:44:25.559 DEBUG [$_NIOREACTOR-0-RW] (serverqueryhandler.java:56)-serverconnection [id=2, Schema=RANGEDB, Host=0:0:0:0:0:0:0:1, User=test,txisolation=3, Autocommit=true, Schema=rangedb]insert into t_er_parent_1 (pid,pname) VALUES (4, ' parent 768 ')
01/28 21:44:25.559 DEBUG [$_NIOREACTOR-0-RW] (nonblockingsession.java:113)-serverconnection [id=2, Schema=RANGEDB, Host=0:0:0:0:0:0:0:1, User=test,txisolation=3, Autocommit=true, Schema=rangedb]insert into t_er_parent_1 (pid,pname) VALUES (4, ' parent 768 '),route={
1-Dn4{insert into t_er_parent_1 (pid,pname) VALUES (4, ' parent 768 ')}
} RRs
01/28 21:44:25.562 DEBUG [$_NIOREACTOR-1-RW] (nonblockingsession.java:229)-release connection mysqlconnection [Id=15, lasttime=1453988665553, User=root, Schema=range_db_4, old Shema=range_db_4, Borrowed=true, FromSlaveDB=false, threadid=179, Charset=utf8, txisolation=3, Autocommit=true, Attachment=dn4{insert into t_er_parent_1 (pid,pname) VALUES (4, ' parent 768 ')}, Resphandler=singlenodehandler [Node=dn4{insert into T_er_parent_1 (pid,pname) VALUES (4, ' Parent 768 ')}, Packetid=0], Host=localhost, port=3306, Statussync=null, Writequeue=0, Modifiedsqlexecuted=true]
01/28 21:44:25.562 DEBUG [$_NIOREACTOR-1-RW] (physicaldatasource.java:403)-release channel mysqlconnection [Id=15, lasttime=1453988665553, User=root, Schema=range_db_4, old Shema=range_db_4, Borrowed=true, FromSlaveDB=false, threadid=179, Charset=utf8, txisolation=3, Autocommit=true, Attachment=null, Resphandler=null, Host=localhost, port= 3306, Statussync=null, Writequeue=0, Modifiedsqlexecuted=false]
01/28 21:44:25.564 DEBUG [$_NIOREACTOR-0-RW] (serverqueryhandler.java:56)-serverconnection [id=2, Schema=RANGEDB, Host=0:0:0:0:0:0:0:1, User=test,txisolation=3, Autocommit=true, Schema=rangedb]insert into T_er_child_1 (Cid,pid, CNAME) VALUES (4,768, ' child 768 ')
01/28 21:44:25.565 DEBUG [$_NIOREACTOR-0-RW] (routerutil.java:650)-found partion node(using parent partion rule directly)For-child table-to-insert dn7 Sql:insert into T_er_child_1 (CID, PID, CNAME)
VALUES (4, 768, ' child 768 ')
01/28 21:44:25.565 DEBUG [$_NIOREACTOR-0-RW] (nonblockingsession.java:113)-serverconnection [id=2, Schema=RANGEDB, Host=0:0:0:0:0:0:0:1, User=test,txisolation=3, Autocommit=true, Schema=rangedb]insert into T_er_child_1 (Cid,pid, CNAME) VALUES (4,768, ' child 768 '),route={
1-Dn7{insert into T_er_child_1 (CID, PID, CNAME)
VALUES (4, 768, ' Child 768 ')}
} RRs

Look at the data of the physical table range_db_4 corresponding to the DN4 node.

Mysql> select * from Range_db_4.t_er_parent_1;

SELECT * from Range_db_4.t_er_child_1;
+-----+------------+
| PID | PName |
+-----+------------+
| 1 | Parent 255 |
| 2 | Parent 256 |
| 3 | Parent 512 |
| 4 | Parent 768 |
+-----+------------+
4 rows in Set (0.00 sec)

+-----+-----+-----------+
| CID | PID | CNAME |
+-----+-----+-----------+
| 1 | 255 | Child 255 |
+-----+-----+-----------+
1 row in Set (0.00 sec)

4.2 Validation of Scenario B

CREATE TABLE ' T_er_order ' (
' order_id ' INT not NULL,
' Prod_info ' VARCHAR NULL,
' Sharding_long ' VARCHAR NULL,
PRIMARY KEY (' order_id '));

CREATE TABLE ' T_er_detail ' (
' detail_id ' INT not NULL,
' order_id ' INT not NULL,
' Detail_info ' INT not NULL,
PRIMARY KEY (' detail_id '));

Insert into T_er_order (Order_id,prod_info,sharding_long) VALUES (1, ' prod_1 ', 200);
Insert into T_er_order (Order_id,prod_info,sharding_long) VALUES (2, ' prod_256 ', 256);
Insert into T_er_order (Order_id,prod_info,sharding_long) VALUES (3, ' prod_512 ', 512);
Insert into T_er_order (Order_id,prod_info,sharding_long) VALUES (4, ' prod_1024 ', 1024);
Insert into T_er_order (Order_id,prod_info,sharding_long) VALUES (5, ' prod_1025 ', 1025);
Insert into T_er_order (Order_id,prod_info,sharding_long) VALUES (6, ' prod_795 ', 795);

Insert into T_er_detail (detail_id,order_id,detail_info) values (1,1,200);
Insert into T_er_detail (detail_id,order_id,detail_info) values (2,2,256);
Insert into T_er_detail (detail_id,order_id,detail_info) values (3,3,512);
Insert into T_er_detail (detail_id,order_id,detail_info) values (4,4,1024);
Insert into T_er_detail (detail_id,order_id,detail_info) values (5,5,1025);
Insert into T_er_detail (detail_id,order_id,detail_info) values (6,6,795);

Mysql> select * from T_er_order;
+----------+-----------+---------------+
| order_id | Prod_info | Sharding_long |
+----------+-----------+---------------+
| 1 | Prod_1 | 200 |
| 4 | prod_1024 | 1024 |
| 5 | prod_1025 | 1025 |
| 2 | prod_256 | 256 |
| 3 | prod_512 | 512 |
| 6 | prod_795 | 795 |
+----------+-----------+---------------+
6 rows in Set (0.08 sec)

Mysql> select * from T_er_detail;
+-----------+----------+-------------+
| detail_id | order_id | Detail_info |
+-----------+----------+-------------+
| 2 | 2 | 256 |
| 1 | 1 | 200 |
| 4 | 4 | 1024 |
| 5 | 5 | 1025 |
| 3 | 3 | 512 |
| 6 | 6 | 795 |
+-----------+----------+-------------+
6 rows in Set (0.01 sec)

Querying physical Tables

Mysql> SELECT * FROM Range_db_6.t_er_order;select * from Range_db_6.t_er_detail;

+----------+-----------+---------------+
| order_id | Prod_info | Sharding_long |
+----------+-----------+---------------+
| 3 | prod_512 | 512 |
+----------+-----------+---------------+
1 row in Set (0.00 sec)

+-----------+----------+-------------+
| detail_id | order_id | Detail_info |
+-----------+----------+-------------+
| 3 | 3 | 512 |
+-----------+----------+-------------+
1 row in Set (0.00 sec)

And look at the logs.

New Main Table data, according to the Shard rule data should enter the 2nd data node Dn5

01/28 20:50:10.274 DEBUG [$_NIOREACTOR-0-RW] (nonblockingsession.java:113)-serverconnection [Id=1, Schema=RANGEDB, Host=0:0:0:0:0:0:0:1, User=test,txisolation=3, Autocommit=true, Schema=rangedb]insert into T_er_order (Order_id,prod _info,sharding_long) VALUES (2, ' prod_256 ', route={),
1- Dn5{insert into T_er_order (Order_id,prod_info,sharding_long) VALUES (2, ' prod_256 ', ()}
} RRS

Primary key search acknowledgement in the database and enter the data cache

01/28 20:50:10.544 DEBUG [BusinessExecutor2] (enchachepool.java:76)-er_sql2parentid Miss Cache, Key:RANGEDB:select T_ er_order.order_id from T_er_order where t_er_order.order_id=2
01/28 20:50:10.544 DEBUG [BusinessExecutor2] (fetchstorenodeofchildtablehandler.java:73)-find child node with sql: Select t_er_order.order_id from T_er_order where t_er_order.order_id=2
01/28 20:50:10.545 DEBUG [BusinessExecutor2] (fetchstorenodeofchildtablehandler.java:81)-execute in Datanode dn4
01/28 20:50:10.545 DEBUG [BusinessExecutor2] (physicaldbpool.java:452)-select read source hostM3306 for Datahost: localhost3306
01/28 20:50:10.545 DEBUG [$_NIOREACTOR-0-RW] (physicaldatasource.java:403)-release channel mysqlconnection [Id=16, lasttime=1453985410540, User=root, Schema=range_db_4, old Shema=range_db_4, Borrowed=true, FromSlaveDB=false, threadid=175, Charset=utf8, txisolation=3, Autocommit=true, Attachment=null, Resphandler=null, Host=localhost, port= 3306, Statussync=null, Writequeue=0, Modifiedsqlexecuted=false]
01/28 20:50:10.745 DEBUG [BusinessExecutor2] (fetchstorenodeofchildtablehandler.java:81)-execute in Datanode dn5
01/28 20:50:10.745 DEBUG [BusinessExecutor2] (physicaldbpool.java:452)-select read source hostM3306 for Datahost: localhost3306
01/28 20:50:10.746 DEBUG [$_NIOREACTOR-1-RW] (fetchstorenodeofchildtablehandler.java:154)-received RowResponse response,2 from Mysqlconnection [id=19, lasttime=1453985410743, User=root, schema=range_db_5, old Shema=range_db_5, Borrowed=true, Fromslavedb=false, threadid=176, Charset=utf8, txisolation=3, Autocommit=true, Attachment=dn5, Resphandler=org[email protected]6be897cc, Host=localhost, port=3306, Statussync=null, WriteQueue=0, Modifiedsqlexecuted=false]
01/28 20:50:10.746 DEBUG [$_NIOREACTOR-1-RW] (physicaldatasource.java:403)-release channel mysqlconnection [id=19, lasttime=1453985410743, User=root, schema=range_db_5, old Shema=range_db_5, Borrowed=true, FromSlaveDB=false, threadid=176, Charset=utf8, txisolation=3, Autocommit=true, Attachment=null, Resphandler=null, Host=localhost, port= 3306, Statussync=null, Writequeue=0, Modifiedsqlexecuted=false]

explicitly inserting DN5 nodes.
01/28 20:50:10.945 DEBUG [BusinessExecutor0] (routerutil.java:1213)-found partion node for child table to insert Dn5 SQL : INSERT into T_er_detail (detail_id,order_id,detail_info) VALUES (2,2,256)

The end of this article.

Mycat Study notes tenth. Er shards of data shards