Verify MyCAT ER sharding

Verify MyCAT ER sharding

Here, we have constructed two tables. All the children's shoes familiar with Oracle know that dept (Department table) and emp (employee table), where deptno in dept is the foreign key of dept_no In the emp table.

The statements for creating two tables are as follows:

Create table dept (deptno int, dname varchar (10), datanode varchar (10 ));

Create table emp (empno int, dept_no int, datanode varchar (10 ));

Note: here, the last column is datanode. By inserting the database () function, you can intuitively obtain the inserted node name to verify the effect of the partition.

Test the following two cases:

1. The parent table is sharded based on the primary key ID. The sharding field of the sub-table is associated with the primary table ID and configured as ER sharding.
2. The partition field of the parent table is another field. The partition field of the sub-table is associated with the ID of the master table and configured as the ER partition.

First, test the first case. Here, the dept table is used as the parent table, the primary key is deptno, The empno table is a sub table, the associated field is dept_no, And the partition field is deptno.

The configuration in schema. xml is as follows:

 <table name="dept" primaryKey="deptno" dataNode="dn1,dn2,dn3"                rule="sharding-by-intfile">                <childTable name="emp" primaryKey="empno" joinKey="dept_no"                                parentKey="deptno">                        </childTable>  </table>

The configuration in rule. xml is as follows:

 <tableRule name="sharding-by-intfile">                <rule>                        <columns>deptno</columns>                        <algorithm>hash-int</algorithm>                </rule>        </tableRule>

The modification is simple, that is, to change the original id to deptno, representing the shard Field

The partition-hash-int.txt value is changed:

10 = 0

20 = 1

 

The verification is as follows:

Insert the value of the parent table,

Datanodeis db1,it is consistent with the configuration in partition-hash-int.txt

mysql> insert into dept(deptno,dname,datanode) values(10,'ACCOUNTING',database());Query OK, 1 row affected (0.05 sec)mysql> select * from dept;+--------+------------+----------+| deptno | dname      | datanode |+--------+------------+----------+|     10 | ACCOUNTING | db1      |+--------+------------+----------+1 row in set (0.12 sec)

The log output information is as follows:

Insert the value of the sub-table

mysql> insert into emp(empno,dept_no,datanode) values(7788,10,database());Query OK, 1 row affected (0.01 sec)

The log output information is as follows:

The key point is "using parent partion rule directly ". This means that the parent sharding rule is used directly. If the table does not specify a foreign key constraint.

Even if the parent does not have the corresponding primary key value, the sub-table can still be inserted, as long as the Partition Rules are defined.

As follows:

mysql> select * from emp;+-------+---------+----------+| empno | dept_no | datanode |+-------+---------+----------+|  7788 |      10 | db1      |+-------+---------+----------+1 row in set (0.52 sec)mysql> select * from dept;+--------+------------+----------+| deptno | dname      | datanode |+--------+------------+----------+|     10 | ACCOUNTING | db1      |+--------+------------+----------+1 row in set (0.13 sec)mysql> insert into emp(empno,dept_no,datanode) values(1234,20,database());Query OK, 1 row affected (0.09 sec)

Although dept does not contain rows whose deptno is 20.

However, emp can still insert a value of 20 for dept_no.

The log output information is as follows:

Therefore, in the first case, the parent table is sharded according to the primary key, and the sharding field of the Word Table is associated with the master table. The conclusion is that when the child table performs the insert operation, it does not check whether the parent table has a related primary key (for the foreign key of the child table), but directly judges based on the sharding rules.

If the sharding rule is not defined, the following error is reported:

mysql> insert into emp(empno,dept_no,datanode) values(1234,30,database());ERROR 1064 (HY000): can't find datanode for sharding column:DEPTNO val:30

 

2. The partition field of the parent table is another field. The partition field of the sub-table is associated with the ID of the master table and configured as the ER partition.

Here, we use dnameas the part field. In this case, we only need to modify route.xmland partition-hash-int.txt.

First, modify rule. xml

<tableRule name="sharding-by-intfile">                <rule>                        <columns>dname</columns>                        <algorithm>hash-int</algorithm>                </rule>        </tableRule>

<function name="hash-int"                class="org.opencloudb.route.function.PartitionByFileMap">                <property name="mapFile">partition-hash-int.txt</property>                <property name="type">1</property>        </function>

Because dname is a character type, set type to 1.

Next, modify the value of the algorithm configuration file.

[root@mysql-server1 conf]# cat partition-hash-int.txt accounting,research=0sales=1operations=2

Log on to the mycat Management port and reload the configuration file.

[Root @ mysql-server1 conf] # mysql-h192.168.244.145-utest-ptest-P9066

mysql> reload @@config;Query OK, 1 row affected (0.14 sec)Reload config success

Test now:

mysql> insert into dept(deptno,dname,datanode) values(10,'accounting',database());Query OK, 1 row affected (0.14 sec)mysql> insert into emp(empno,dept_no,datanode) values(1234,10,database());Query OK, 1 row affected (0.29 sec)

The log output information is as follows:

Insert the value in the second partition

mysql> insert into dept(deptno,dname,datanode) values(20,'sales',database());Query OK, 1 row affected (0.01 sec)mysql> insert into emp(empno,dept_no,datanode) values(1234,20,database());Query OK, 1 row affected (0.41 sec)

The log output information is as follows:

This time we are searching for the partition nodes of the sub-table in two partitions.

Insert the value in the third partition

mysql>  insert into dept(deptno,dname,datanode) values(30,'operations',database());Query OK, 1 row affected (0.00 sec)mysql> insert into emp(empno,dept_no,datanode) values(4567,30,database());Query OK, 1 row affected (0.62 sec)

The log output information is as follows:

Search for the partition nodes of the sub-table in the three partitions.

Summary: When the partition field of the parent table is another field and the partition field of the child table is associated with the primary table ID, the partition nodes of the child table are sequentially searched, in this example, search for dn1, dn2, and dn3 in sequence. If dn1 exists, it is directly inserted into dn1. Otherwise, it continues to search for dn2 nodes. If it still does not exist, in the dn3 node.

MySQL read/write splitting using MyCAT

MySQL read/write splitting using MyCAT

This article permanently updates the link address: