mycat实战-ER分片

1.配置mycat

1.1修改schema.xml

 <schema name="test" checkSQLschema="false">
        <!-- auto sharding by id (long) -->
        <table name="group_test"  dataNode="dn3,dn4" rule="ruleGroupTestMessage" >
                <childTable name="account_group_rel_test" primaryKey="id" joinKey="group_id" parentKey="id" />
        </table>
     </schema>

<dataNode name="dn3" dataHost="imHost1" database="test" />
<dataNode name="dn4" dataHost="imHost2" database="test" />
<dataHost name="imHost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
      <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
                 <writeHost host="hostM1" url="192.168.14.78:3306" user="root" password="123456">
                </writeHost>
      </dataHost>

     <dataHost name="imHost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
                 <writeHost host="hostM1" url="192.168.14.79:3306" user="root" password="123456">
                </writeHost>
      </dataHost>

1.2配置rule.xml

采用一致性hash算法

 	<tableRule name="ruleGroupTestMessage">
            <rule>
               <columns>id</columns>
               <algorithm>murmur</algorithm>
            </rule>
        </tableRule>
 <function name="murmur"
                class="io.mycat.route.function.PartitionByMurmurHash">
                <property name="seed">0</property><!-- 默认是0 -->
                <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
                <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
                <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须>是正整数，否则以1代替 -->
                <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
                        用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 -->
        </function>

2.测试

2.1 创建数据库

在两个分片上创建数据库

create database test;

2.2 创建table

mysql -uroot -p123456 -P 8066 -h 192.168.14.78
use test;

create table group_test (id int,name varchar(20));
create table account_group_rel_test(id int,group_id int,name varchar(20));

2.3插入数据

insert into group_test(id ,name) values(1,"aa");
insert into group_test(id ,name) values(11,"aaa");
insert into group_test(id ,name) values(111,"aaa");
insert into group_test(id ,name) values(1111,"aaaa");

insert into account_group_rel_test(id ,group_id,name) values(1,1,"acount_a");
insert into account_group_rel_test(id ,group_id,name) values(2,11,"acount_aa");
insert into account_group_rel_test(id ,group_id,name) values(3,111,"acount_aaa");
insert into account_group_rel_test(id ,group_id,name) values(4,1111,"acount_aaaa");

2.4 验证

select a.name,b.id,b.name from group_test a 
inner join account_group_rel_test b 
on a.id = b.group_id;