02 ER表 思想3 按主表的主键，按主表的区域(写不到实体库中已解决{autocommit要自动提交，否则无法到实体库})

改my.cnf

     

图解：备 库read_only=1 这个参数没改，存储过程参数本来就有，改了：lower_case_table_names=1 不区分大小写，和autocommit=1 自动提交

配置文件 添加 lower_case_table_names=1  ，不管导入的是大写的还是小写的数据库或者表，数据库和表全部小写，此时你不管用大写还是小写，都是一个库，不做区分

导表的话，不提交，重启后，数据全部丢失

 

lower_case_table_names=1

autocommit = 1

log_bin_trust_function_creators = 1

 

 

 

1. ER分片        -----  主表与子表有关联关系  ,说白了就是外键

 

！）父表按照主键ID分片，子表的分片字段与主表ID关联， 按照主表分片字段分页，子表数据会跟着主表走

 

替换掉 schema.xml    <schema>标签下

<table    name="order01" dataNode="dnitpuxdb$1-3,dnitpuxdb$13-15,dnitpuxdb$25-27" rule="mod-long">

    <childTable    name="orderdetail01"    primaryKey="orderdetail_id" joinKey="order_id" parentKey="order_id" />

</table>

 

rule.xml 配置    使用取模分片 

 

替换掉下面的

<t a bleRule n a me="mod-long">

<rule>

<columns>order_id</columns>

< a lgorithm>mod-long</ a lgorithm>

</rule>

</t a bleRule>

<function n a me="mod-long"  cl a ss="io.myc a t.route.function.P a rtitionByMod">

<!-- how m a ny d a t a nodes -->

<property n a me="count">9</property>            <!-- id % 9 取模，得余数， 得到数据存储的位置 【012345678】  比如id=9 , 则放在第一个库，15的话，余数为6，所以会放在第7个库-->

</function>

 

然后重启   重新加载

myc a t restart

myc a t myc a t console

 

 

mysql -uroot -proot -h192.168.0.71 -P8066 mycatdb

 

CREATE TABLE order01(

order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

number VARCHAR(64), createtime DATETIME);

 

CREATE TABLE orderdetail01(

orderdetail_id INT AUTO_INCREMENT PRIMARY KEY, order_id INT,

order_status CHAR(1), addresss VARCHAR(128), createtime DATETIME,

CONSTRAINT fk_iorder01   FOREIGN  KEY (order_id) REFERENCES order01 (order_id));

 

录数据

INSERT    INTO order01(order_id,number,createtime) VALUES(1,'itpuxsn101',NOW());

INSERT    INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)

VALUES (1,1,'1','fgjy itpux101(ID=1,itpuxsn101) ',NOW());

 

 

INSERT    INTO    order01(order_id,number,createtime) VALUES(2,'itpuxsn201',NOW());

INSERT    INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime

)VALUES (2,2,'1','fgjy itpux201(ID=2,itpuxsn201) ',NOW());

 

 

INSERT    INTO    order01(order_id,number,createtime) VALUES(3,'itpuxsn301',NOW());

INSERT    INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)

VALUES (3,3,'1','fgjy itpux301(ID=3,itpuxsn301) ',NOW());

 

 

INSERT    INTO    order01(order_id,number,createtime) VALUES(4,'itpuxsn401',NOW());

INSERT    INTOorderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)

VALUES (4,4,'1','fgjy itpux401(ID=4,itpuxsn401) ',NOW());

 

 

INSERT    INTO    order01(order_id,number,createtime) VALUES(5,'itpuxsn501',NOW());

INSERT    INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)

VALUES (5,5,'1','fgjy itpux501(ID=5,itpuxsn501) ',NOW());

 

 

INSERT    INTO    order01(order_id,number,createtime) VALUES(6,'itpuxsn601',NOW());

INSERT    INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)

VALUES (6,6,'1','fgjy itpux601(ID=6,itpuxsn601) ',NOW());

 

 

INSERT    INTO    order01(order_id,number,createtime) VALUES(7,'itpuxsn701',NOW());

INSERT    INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)

VALUES (7,7,'1','fgjy itpux701(ID=7,itpuxsn701) ',NOW());

 

 

INSERT    INTO    order01(order_id,number,createtime) VALUES(8,'itpuxsn801',NOW());

INSERT    INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)

VALUES (8,8,'1','fgjy itpux801(ID=8,itpuxsn801) ',NOW());

 

 

INSERT    INTO    order01(order_id,number,createtime) VALUES(9,'itpuxsn901',NOW());

INSERT    INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)

VALUES (9,9,'1','fgjy itpux901(ID=9,itpuxsn901) ',NOW());

 

 

commit;

 

图解：数据库51，52，53上看到的是两张关联关系的表

 

 

验证：下面每一步都不能少

 

INSERT    INTO    order01(order_id,number,createtime) VALUES(15,'itpuxsn901',NOW());

INSERT    INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)

VALUES (15,15,'1','fgjy itpuxzxh(ID=zxh,itpuxsnzxh) ',NOW());

 

 

 

mysql -uroot -proot -h192.168.0.51 -e "select @@hostname;select * from itpuxdb01.order01;

select * from itpuxdb01.orderdetail01";

 

mysql -uroot -proot -h192.168.0.52 -e "select @@hostname;select * from itpuxdb13.order01;

select * from itpuxdb13.orderdetail01";

 

mysql -uroot -proot -h192.168.0.53 -e "select @@hostname;select * from itpuxdb25.order01;

select * from itpuxdb25.orderdetail01";

 

 

 

mysql    -uroot    -proot    -h192.168.0.71    -P8066    -e    "use    mycatdb;select @@hostname;select * from order01;

select * from orderdetail01";

 

 

 

 

看日志，有分发到哪里

 

 

 more /logs/mycat.log |grep itpuxsn

图解：看到阴影部分是itpuxdb25,说明15这个id 分配到了25号数据库上

 

 

 

 

mysql -uroot -proot -h192.168.1.71 -P8066 -Dmycatdb

select * from order01 t1,orderdetail01 t2 where t2.order_status='1' and t2.orderdetail_id=2  and t1.order_id=t2.order_id;

 

 

explain select * from order01 t1,orderdetail01 t2 where t2.order_status='1' and t2.orderdetail_id=2  and t1.order_id=t2.order_id;

 

 

图解：看执行计划，会看到，他会在没他机器上都执行下，说明成功了

 

总结：

 

 

 

@)父表按照region区域分片，子表的分片字段与主表ID关联

 

先truncate 表， 再drop表

 

truncate table orderdetail01;

drop table orderdetail01;

truncate table order01;

drop  table order01;

 

01.    schema.xml 配置

<table name="ORDER02" dataNode="dnitpuxdb$1-35" rule="region-hash-str">

    <childTable name="ORDERDETAIL02" primaryKey="orderdetail_id" joinKey="order_id" parentKey="order_id" />

</table>

 

02.    rule.xml 配置

<tableRule name="region-hash-str">

    <rule>

        <columns>region</columns>

        <algorithm>region-hash-int</algorithm>

    </rule>

</tableRule>

 

<function name="region-hash-int" class="io.mycat.route.function.PartitionByFileMap">

    <property name="type">1</property>

    <property name="mapFile">partition-region-hash-int.txt</property>

    <property name="defaultNode">0</property>

</function>

 

vi partition-region-hash-int.txt

北京市=0

上海市=1

云南省=2

内蒙古=3

贵州省=4

重庆市=5

台湾省=6

吉林省=7

四川省=8

天津市=9

宁夏省=10

安徽省=11

山东省=12

山西省=13

广东省=14

广西省=15

新疆省=16

江苏省=17

江西省=18

河北省=19

河南省=20

浙江省=21

海南省=22

湖北省=23

湖南省=24

澳门=25

甘肃省=26

福建省=27

西藏=28

辽宁省=29

陕西省=30

青海省=31

香港=32

黑龙江省 =33

DEFAULT_NODE=34

 

mycat restart

mycat console

 

mysql -uzxh -pzxh -h192.168.0.71 -P8066 -Dmycatdb

 

CREATE TABLE ORDER02(

ORDER_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

REGION VARCHAR(20), NUMBER VARCHAR(64), CREATETIME DATETIME);

 

CREATE TABLE ORDERDETAIL02(

ORDERDETAIL_ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,

ORDER_STATUS CHAR(1), ADDRESSS VARCHAR(128), CREATETIME DATETIME,

CONSTRAINT FK_IORDER02 FOREIGN KEY (ORDER_ID) REFERENCES ORDER02 (ORDER_ID));

 

INSERT INTO ORDER02(order_id,region,number,createtime) values(1,' 北京市 ','itpuxsn101',NOW());

######################这里执行下面的语句开始报错，ERROR 1064 (HY000): can't find (root) parent sharding node for sql '   出现这种情况，是因为父表的数据有问题，或者没灭插进去，所有这个时候一定要一条一条插，    否则只能铲掉重来了              #######################

INSERT INTO ORDERDETAIL02(orderdetail_id,order_id,order_status,addresss,createtime) VALUES (1,1,'1','fgjy itpux101(ID=1,itpuxsn101) ',NOW());

INSERT INTO ORDER02(order_id,region,number,createtime) values(2,' 上海市 ','itpuxsn201',NOW());

INSERT INTO ORDERDETAIL02(orderdetail_id,order_id,order_status,addresss,createtime) values (2,2,'1','fgjy itpux201(ID=2,itpuxsn201) ',NOW());

INSERT INTO ORDER02(order_id,region,number,createtime) values(3,' 四川省 ','itpuxsn301',NOW());

INSERT INTO ORDERDETAIL02(orderdetail_id,order_id,order_status,addresss,createtime) values (3,3,'1','fgjy itpux301(ID=3,itpuxsn301) ',NOW());

INSERT INTO ORDER02(order_id,region,number,createtime) values(4,' 江西省 ','itpuxsn401',NOW());

INSERT INTO ORDERDETAIL02(orderdetail_id,order_id,order_status,addresss,createtime) values (4,4,'1','fgjy itpux401(ID=4,itpuxsn401) ',NOW());

INSERT INTO ORDER02(order_id,region,number,createtime) values(5,' 广东省 ','itpuxsn501',NOW());

INSERT INTO ORDERDETAIL02(orderdetail_id,order_id,order_status,addresss,createtime) values (5,5,'1','fgjy itpux501(ID=5,itpuxsn501) ',NOW());

INSERT INTO order02(order_id,region,number,createtime) values(6,' 江苏省 ','itpuxsn601',NOW());

INSERT INTO orderdetail02(orderdetail_id,order_id,order_status,addresss,createtime) values (6,6,'1','fgjy itpux601(ID=6,itpuxsn601) ',NOW());

INSERT INTO ORDER02(order_id,region,number,createtime) values(7,' 湖南省 ','itpuxsn701',NOW());

INSERT INTO ORDERDETAIL02(orderdetail_id,order_id,order_status,addresss,createtime) values (7,7,'1','fgjy itpux701(ID=7,itpuxsn701) ',NOW());

INSERT INTO ORDER02(order_id,region,number,createtime) values(8,' 湖北省 ','itpuxsn801',NOW());

INSERT INTO ORDERDETAIL02(orderdetail_id,order_id,order_status,addresss,createtime) values (8,8,'1','fgjy itpux801(ID=8,itpuxsn801) ',NOW());

INSERT INTO ORDER02(order_id,region,number,createtime) values(9,' 福建省 ','itpuxsn901',NOW());

INSERT INTO ORDERDETAIL02(orderdetail_id,order_id,order_status,addresss,createtime) values (9,9,'1','fgjy itpux901(ID=9,itpuxsn901) ',NOW());

commit;

 

tail -5000 ../logs/mycat.log |grep itpuxsn

 

INSERT    INTO ORDERDETAIL02(orderdetail_id,order_id,order_status,addresss,createtime) values (10,10,'1','fgjy itpux1001(ID=10,itpuxsn1001) ',NOW());

ERROR 1064 (HY000): can't find (root) parent sharding node for sql '

 

mysql -uroot -proot -h192.168.0.51 -e "select @@hostname;select * from itpuxdb01.ORDER02;select * from itpuxdb01.ORDERDETAIL02";

                              

mysql -uroot -proot -h192.168.0.51 -e "select @@hostname;select * from itpuxdb02.ORDER02;select * from itpuxdb02.ORDERDETAIL02";

                              

mysql -uroot -proot -h192.168.0.52 -e "select @@hostname;select * from itpuxdb15.ORDER02;select * from itpuxdb15.ORDERDETAIL02";

                              

mysql -uroot -proot -h192.168.0.53 -e "select @@hostname;select * from itpuxdb25.ORDER02;select * from itpuxdb25.ORDERDETAIL02";

 

mysql -uzxh -pzxh -h192.168.0.71 -P8066 -e    "use    mycatdb;select @@hostname;select * from ORDER02; select * from ORDERDETAIL02";

 

 

#####################数据插入，等下 就没有了，日志也会消失###################

验证：

mysql -uzxh -pzxh -h192.168.0.71 -P8066 -Dmycatdb

explain select * from ORDER02 t1,ORDERDETAIL02 t2 where t2.order_status='1' and t2.orderdetail_id=2 and t1.order_id=t2.order_id;

select * from ORDER02 t1,ORDERDETAIL02 t2 where t2.order_status='1' and t2.orderdetail_id=2 and t1.order_id=t2.order_id;