程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mycat高可用ER分片的2種場景詳細分析

mycat高可用ER分片的2種場景詳細分析

編輯:MySQL綜合教程

mycat高可用ER分片的2種場景詳細分析


1,ER分片關系簡介

有一類業務,例如訂單(ORDER)跟訂單明細表(ORDER_DETAIL),明細表會依賴二訂單,就是該會存在表的主從關系,這類似業務的切分可以抽象出合適的切分規則,比如根據用戶ID切分,其它相關的表都依賴於用戶ID,再或者根據訂單ID進行切分,總之部分業務總會可以抽象出父子關系的表。這類表適用於ER分片表,子表的記錄與所關聯的父表記錄存放在同一個數據分片上,避免數據Join跨庫操作,以order與order_detail例子為例,schema.xml中定義合適的分片配置,order,order_detail 根據order_id迕行數據切分,保證相同order_id的數據分到同一個分片上,在進行數據插入操作時,Mycat會獲取order所在的分片,然後將order_detail也插入到order所在的分片。


2, 父表按照主鍵ID分片,字表的分片字段與主表ID關聯,配置為ER分片

2.1 在schema.xml添加如下配置配置文件修改

 

在rule.xml裡面設定分片規則

    
                
                        id
                        mod-long
                
    

然後重啟mycat或者重新加載mycat

本blog源地址:http://blog.csdn.net/mchdba/article/details/50655304,不經過原作者mchdba(黃杉)允許,謝絕轉載


2.2 先建表, ORDER 和 ORDER_DETAIL 表,有主外鍵關系

    CREATE TABLE ORDER1(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME);

    CREATE TABLE ORDER_DETAIL(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID11 FOREIGN KEY (ORDER_ID) REFERENCES ORDER1 (ID));



    mysql> CREATE TABLE ORDER1(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME);
    Query OK, 0 rows affected (0.06 sec)

    mysql> 
    mysql> CREATE TABLE ORDER_DETAIL(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID FOREIGN KEY (ORDER_ID) REFERENCES ORDER1 (ID));
    Query OK, 0 rows affected (0.02 sec)

    mysql> 

3.3 錄入數據:

    mysql> 
    mysql> explain INSERT INTO ORDER1(SN,CREATE_TIME) VALUES('BJ0001',NOW());
    ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO ORDER1 (SN, CREATE_TIME)
    VALUES ('BJ0001', NOW())
    mysql> explain INSERT INTO ORDER1(SN,CREATE_TIME) VALUES('BJ0001',NOW());
    ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO ORDER1 (SN, CREATE_TIME)
    VALUES ('BJ0001', NOW())
    mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(1,'BJ0001',NOW());
    Query OK, 1 row affected (0.03 sec)

    mysql> 

錄入數據,一組組錄入,涉及到外鍵關系:
第一組北京的訂單

    mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(1,'BJ0001',NOW());
    Query OK, 1 row affected (0.03 sec)

    mysql> 

    mysql> INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,BJ001) ',NOW());
    Query OK, 1 row affected (0.00 sec)

    mysql> 

第二組上海的訂單:

    mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW());
    Query OK, 1 row affected (0.01 sec)

    mysql> INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,SHH001) ',NOW());

    mysql> 

第三組廣州的訂單:

    mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(4,'GZH004',NOW());
    Query OK, 1 row affected (0.01 sec)

    mysql> INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (4,4,'1','test data  of ORDER1(ID=4,GZH004) ',NOW());
    mysql> 

第四組 武漢的訂單,這裡故意將order_id設置成4,看看效果,是否隨id為4的廣州的那組分片:

    mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(5,'WUHAN005',NOW());
    ERROR 1064 (HY000): Index: 2, Size: 2
    mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(6,'WUHAN006',NOW());
    Query OK, 1 row affected (0.02 sec)

    mysql> INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,4,'1','test data  of ORDER1(ID=6,WUHAN005) ',NOW());

2.4 驗證子表是否隨父表分片,果然驗證如下,大家看到訂單詳細表ORDER_DETAIL的數據的分片都是隨ORDER_ID所在外鍵的父表的分片:

    [root@test_1_11logs]# /usr/local/mysql56s1/bin/mysql -uroot -p -P3317 --socket=/usr/local/mysql56s1/mysql.sock -e "select @@port;select * from db3.ORDER1; select * from db3.ORDER_DETAIL";
    Enter password: 
    +--------+
    | @@port |
    +--------+
    |   3327 |
    +--------+
    +----+--------+---------------------+
    | ID | SN     | CREATE_TIME         |
    +----+--------+---------------------+
    |  1 | BJ0001 | 2016-02-11 22:54:26 |
    |  4 | GZH004 | 2016-02-11 22:57:49 |
    +----+--------+---------------------+
    +----+----------+------------+--------------------------------------+---------------------+
    | ID | ORDER_ID | ORD_STATUS | ADDRESS                              | CREATE_TIME         |
    +----+----------+------------+--------------------------------------+---------------------+
    |  1 |        1 | 1          | test data  of ORDER1(ID=1,BJ001)     | 2016-02-11 23:07:05 |
    |  4 |        4 | 1          | test data  of ORDER1(ID=4,GZH004)    | 2016-02-11 23:09:48 |
    |  6 |        4 | 1          | test data  of ORDER1(ID=6,WUHAN005)  | 2016-02-11 23:10:18 |
    +----+----------+------------+--------------------------------------+---------------------+
    [root@test_1_11logs]# 
    [root@test_1_11logs]# 
    [root@test_1_11logs]# /usr/local/mysql56s1/bin/mysql -uroot -p -P3327 --socket=/usr/local/mysql56m1/mysql.sock -e "select @@port;select * from db3.ORDER1; select * from db3.ORDER_DETAIL";
    Enter password: 
    +--------+
    | @@port |
    +--------+
    |   3317 |
    +--------+
    +----+----------+---------------------+
    | ID | SN       | CREATE_TIME         |
    +----+----------+---------------------+
    |  3 | SHH001   | 2016-02-11 22:57:26 |
    |  6 | WUHAN006 | 2016-02-11 23:04:39 |
    +----+----------+---------------------+
    +----+----------+------------+------------------------------------+---------------------+
    | ID | ORDER_ID | ORD_STATUS | ADDRESS                            | CREATE_TIME         |
    +----+----------+------------+------------------------------------+---------------------+
    |  3 |        3 | 1          | test data  of ORDER1(ID=3,SHH001)  | 2016-02-11 23:09:02 |
    +----+----------+------------+------------------------------------+---------------------+
    [root@test_1_11logs]# 

2.5 有日志為列,看到SHH001這個訂單的數據錄入都在端口為3317的分片mysql實例上,如下驗證:

[root@test_1_11logs]# more mycat.log|grep SHH001
    02/11 22:56:58.477  DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW())
    02/11 22:56:58.478   WARN [$_NIOREACTOR-3-RW] (ServerConnection.java:209) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW()) err:java.lang.IndexOutOfBoundsException: Index: 2, Size: 2
    02/11 22:57:22.476  DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW())
    02/11 22:57:22.477   WARN [$_NIOREACTOR-3-RW] (ServerConnection.java:209) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW()) err:java.lang.IndexOutOfBoundsException: Index: 2, Size: 2
    02/11 22:57:26.988  DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())
    02/11 22:57:26.989  DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW()), route={
       1 -> dn21{INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())}
    02/11 22:57:26.999  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=4, lastTime=1455202646984, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=91, charset=latin1, txIsolation=3, autocommit=true, attachment=dn21{INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())}, packetId=0], host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
    02/11 23:09:02.690  DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,SHH001) ',NOW())
    VALUES (3, 3, '1', 'test data  of ORDER1(ID=3,SHH001) ', NOW())
    02/11 23:09:02.692  DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,SHH001) ',NOW()), route={
    VALUES (3, 3, '1', 'test data  of ORDER1(ID=3,SHH001) ', NOW())}
    VALUES (3, 3, '1', 'test data  of ORDER1(ID=3,SHH001) ', NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER_DETAIL (ID, ORDER_ID, ORD_STATUS, ADDRESS, CREATE_TIME)
    VALUES (3, 3, '1', 'test data  of ORDER1(ID=3,SHH001) ', NOW())}, packetId=0], host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
    [root@test_1_11logs]# 

【】看到直接22:57:26.988時間點處有數據錄入,22:57:26.989處有路由分配 route={dn21了,直接走父表的路由了**


2.6 查詢路由

mycat命令行裡面執行:

    mysql> explain select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID;
    +-----------+-----------------------------------------------------------------------------------------------------------+
    | DATA_NODE | SQL                                                                                                       |
    +-----------+-----------------------------------------------------------------------------------------------------------+
    | dn21      | select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID |
    | dn22      | select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID |
    +-----------+-----------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

    mysql>  select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID;
    +----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+
    | ID | SN     | CREATE_TIME         | ID | ORDER_ID | ORD_STATUS | ADDRESS                           | CREATE_TIME         |
    +----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+
    |  1 | BJ0001 | 2016-02-12 17:51:24 |  1 |        1 | 1          | test data  of ORDER1(ID=1,BJ001)  | 2016-02-12 17:51:30 |
    +----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+
    1 row in set (0.01 sec)

    mysql> 

【】mycat.log後台日志debug信息,看到走的route路由是dn21和dn22,所有的節點路由都走了:

    02/12 18:22:51.444  DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]explain select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID
    02/12 18:22:51.444  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:TESTDB select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID
    02/12 18:22:51.448  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDB select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID value:select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID, route={
       1 -> dn21{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}
       2 -> dn22{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}
    }
    02/12 18:23:01.214  DEBUG [Timer0] (ConnectionHeartBeatHandler.java:52) -do heartbeat for con MySQLConnection [id=18, lastTime=1455272581207, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=18, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
    02/12 18:23:01.215  DEBUG [Timer0] (ConnectionHeartBeatHandler.java:52) -do heartbeat for con MySQLConnection [id=22, lastTime=1455272581207, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=21, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 

這裡寫圖片描述
圖片1.jpg
<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4NCjxociAvPg0KPGg0IGlkPQ=="27-總結當子表與父表的關聯字段正好是父表的分片字段時子表直接根據父表規則進行分片在數據錄入的時候子表直接放在父表的分片上面在進行關聯查詢join的時候走的是">2.7 總結:當子表與父表的關聯字段正好是父表的分片字段時,子表直接根據父表規則進行分片,在數據錄入的時候子表直接放在父表的分片上面,在進行關聯查詢join的時候,走的是


3 .父表的分片字段為其他字段,以PROVINCE字段分片,字表的分片字段與主表ID關聯,配置為ER分片

3.1 各種配置

在schema.xml裡面添加表配置

      

在rule.xml裡面設定分片規則

        
                 
                         PROVINCE
                          province-str-split
                
             
        
                1
                partition-hash-str-pro.txt
                0
        

新添加partition-hash-str-pro.txt

    [root@test_1_11conf]# more partition-hash-str-pro.txt
    beijing=0
    shanghai=1
    tianjing=2
    [root@test_1_11conf]#       

然後重啟mycat或者重新加載mycat


3.2 建表

    CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);

    CREATE TABLE ORDER_DETAIL2(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID21 FOREIGN KEY (ORDER_ID) REFERENCES ORDER2 (ID));
    mysql> CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME);
    Query OK, 0 rows affected (0.05 sec)

    mysql> CREATE TABLE ORDER_DETAIL2(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID21 FOREIGN KEY (ORDER_ID) REFERENCES ORDER2 (ID));
    Query OK, 0 rows affected (0.02 sec)

    mysql> 

3.2 錄入數據,一組組錄入,涉及到外鍵關系:

    INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,'beijing','2BJ0001',NOW());
    INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW());
    mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,'beijing','2BJ0001',NOW());
    Query OK, 1 row affected (0.01 sec)

    mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW());
    Query OK, 1 row affected (0.21 sec)

    mysql>  
    INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW());
    INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,2SHH001) ',NOW());
    mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW());
    Query OK, 1 row affected (0.01 sec)

    mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,2SHH001) ',NOW());
    Query OK, 1 row affected (0.41 sec)

    mysql> 
    INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(4,'beijing','2GZH004',NOW());
    INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (4,4,'1','test data  of ORDER1(ID=4,2GZH004) ',NOW());
    mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(4,'beijing','2GZH004',NOW());
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (4,4,'1','test data  of ORDER1(ID=4,2GZH004) ',NOW());
    Query OK, 1 row affected (0.21 sec)

    mysql>
    INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(6,'shanghai','2WUHAN006',NOW());
    INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,4,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW());
    mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(6,'shanghai','2WUHAN006',NOW());
    Query OK, 1 row affected (0.02 sec)

    mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,4,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW());
    Query OK, 1 row affected (0.22 sec)

    mysql>  
    mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW());
    ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW())
    mysql> 

3.3 數據驗證

    [root@test_1_11~]# /usr/local/mysql56s1/bin/mysql -uroot -p --socket=/usr/local/mysql56s1/mysql.sock -e "select @@port;select * from db3.ORDER2; select * from db3.ORDER_DETAIL2";
    Enter password: 
    +--------+
    | @@port |
    +--------+
    |   3327 |
    +--------+
    +----+----------+-----------+---------------------+
    | ID | PROVINCE | SN        | CREATE_TIME         |
    +----+----------+-----------+---------------------+
    |  3 | shanghai | 2SHH001   | 2016-02-12 17:24:00 |
    |  6 | shanghai | 2WUHAN006 | 2016-02-12 17:24:14 |
    +----+----------+-----------+---------------------+
    +----+----------+------------+-------------------------------------+---------------------+
    | ID | ORDER_ID | ORD_STATUS | ADDRESS                             | CREATE_TIME         |
    +----+----------+------------+-------------------------------------+---------------------+
    |  3 |        3 | 1          | test data  of ORDER1(ID=3,2SHH001)  | 2016-02-12 17:24:04 |
    +----+----------+------------+-------------------------------------+---------------------+
    [root@test_1_11~]#     
    [root@test_1_11~]# /usr/local/mysql56s1/bin/mysql -uroot -p --socket=/usr/local/mysql56m1/mysql.sock -e "select @@port;select * from db3.ORDER2; select * from db3.ORDER_DETAIL2";
    Enter password: 
    +--------+
    | @@port |
    +--------+
    |   3317 |
    +--------+
    +----+----------+---------+---------------------+
    | ID | PROVINCE | SN      | CREATE_TIME         |
    +----+----------+---------+---------------------+
    |  1 | beijing  | 2BJ0001 | 2016-02-12 17:23:46 |
    |  4 | beijing  | 2GZH004 | 2016-02-12 17:24:07 |
    +----+----------+---------+---------------------+
    +----+----------+------------+---------------------------------------+---------------------+
    | ID | ORDER_ID | ORD_STATUS | ADDRESS                               | CREATE_TIME         |
    +----+----------+------------+---------------------------------------+---------------------+
    |  1 |        1 | 1          | test data  of ORDER1(ID=1,2BJ0001)    | 2016-02-12 17:23:50 |
    |  4 |        4 | 1          | test data  of ORDER1(ID=4,2GZH004)    | 2016-02-12 17:24:11 |
    |  6 |        4 | 1          | test data  of ORDER1(ID=6,2WUHAN006)  | 2016-02-12 17:24:17 |
    +----+----------+------------+---------------------------------------+---------------------+
    [root@test_1_11~]# 

3.4 mycat.log中debug日志顯示,通過關鍵字2BJ0001來檢索父子數據記錄的錄入情況

vim mycat.log

    02/12 17:23:50.530  DEBUG [$_NIOREACTOR-2-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where  order2.id=1
    02/12 17:23:50.531  DEBUG [BusinessExecutor3] (EnchachePool.java:76) -ER_SQL2PARENTID  miss cache ,key:TESTDB:select order2.id from order2 where  order2.id=1
    02/12 17:23:50.531  DEBUG [BusinessExecutor3] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where  order2.id=1
    02/12 17:23:50.531  DEBUG [BusinessExecutor3] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21
    02/12 17:23:50.531  DEBUG [BusinessExecutor3] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1
    02/12 17:23:50.532  DEBUG [$_NIOREACTOR-0-RW] (FetchStoreNodeOfChildTableHandler.java:154) -received rowResponse response,1 from  MySQLConnection [id=8, lastTime=1455269030515, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=15, charset=latin1, txIsolation=0, autocommit=true, attachment=dn21, respHandler=org.opencloudb.mysql.nio.handler.FetchStoreNodeOfChildTableHandler@dde0e41, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
    02/12 17:23:50.533  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1455269030515, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=15, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
    02/12 17:23:50.732  DEBUG [BusinessExecutor4] (RouterUtil.java:1213) -found partion node for child table to insert dn21 sql :INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())
    02/12 17:23:50.733  DEBUG [BusinessExecutor4] (NonBlockingSession.java:113) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW()), route={
       1 -> dn21{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())}
    } rrs
    02/12 17:23:50.735  DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=7, lastTime=1455269030715, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=dn21{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())}, packetId=0], host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
    02/12 17:23:50.735  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=7, lastTime=1455269030715, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

【】可以看到在17:23:50.532處,看到find root parent’s node sql select order2.id from order2 where order2.id=1這樣的日志記錄,就是通過外鍵鎖涉及的父表的關聯字段id去找所在分片記錄,然後在17:23:50.732看到已經路由出來了分片地址route={dn21。


3.5 如果錄入不存在的父表數據的時候路由情況

數據錄入:

    mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW());
    ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW())
    mysql> 

查看日志mycat.log情況:

    02/12 17:54:43.351  DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW())
    02/12 17:54:43.352  DEBUG [$_NIOREACTOR-2-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where  order2.id=9
    02/12 17:54:43.353  DEBUG [BusinessExecutor7] (EnchachePool.java:76) -ER_SQL2PARENTID  miss cache ,key:TESTDB:select order2.id from order2 where  order2.id=9
    02/12 17:54:43.353  DEBUG [BusinessExecutor7] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where  order2.id=9
    02/12 17:54:43.353  DEBUG [BusinessExecutor7] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21
    02/12 17:54:43.353  DEBUG [BusinessExecutor7] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1
    02/12 17:54:43.354  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=7, lastTime=1455270883346, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
    02/12 17:54:43.554  DEBUG [BusinessExecutor7] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn22
    02/12 17:54:43.554  DEBUG [BusinessExecutor7] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m2
    02/12 17:54:43.554  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=15, lastTime=1455270883547, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
    02/12 17:54:43.805   WARN [BusinessExecutor3] (RouterUtil.java:1206) -org.opencloudb.server.NonBlockingSession@634984c8INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW()) err:can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW())

看到,遍歷了所有節點dn21和dn22之後,select order2.id from order2 where order2.id=9執行後沒有找到記錄,就報錯了 can’t find (root) parent sharding node for sql:


3.6 join關聯查詢涉及到的路由,是全節點遍歷查詢

    mysql> explain select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6;
    +-----------+--------------------------------------------------------------------------------------+
    | DATA_NODE | SQL                                                                                  |
    +-----------+--------------------------------------------------------------------------------------+
    | dn21      | select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 |
    | dn22      | select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 |
    +-----------+--------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

    mysql> 
    mysql> select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6;
    +----+----------+---------+---------------------+----+----------+------------+---------------------------------------+---------------------+
    | ID | PROVINCE | SN      | CREATE_TIME         | ID | ORDER_ID | ORD_STATUS | ADDRESS                               | CREATE_TIME         |
    +----+----------+---------+---------------------+----+----------+------------+---------------------------------------+---------------------+
    |  1 | beijing  | 2BJ0001 | 2016-02-12 17:23:46 |  6 |        4 | 1          | test data  of ORDER1(ID=6,2WUHAN006)  | 2016-02-12 17:24:17 |
    |  4 | beijing  | 2GZH004 | 2016-02-12 17:24:07 |  6 |        4 | 1          | test data  of ORDER1(ID=6,2WUHAN006)  | 2016-02-12 17:24:17 |
    +----+----------+---------+---------------------+----+----------+------------+---------------------------------------+---------------------+
    2 rows in set (0.01 sec)

    mysql>

    02/12 18:02:23.370  DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6
    02/12 18:02:23.371  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:TESTDBselect t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6
    02/12 18:02:23.373  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDBselect t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 value:select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6, route={
       1 -> dn21{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
       2 -> dn22{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
    }
    02/12 18:02:23.373  DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6, route={
       1 -> dn21{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
       2 -> dn22{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
    } rrs       

3.7 【】總結:當子表與父表的關聯字段不是父表的分片字段時,必須通過查找對應的父表記錄來確認子表所在分片,如果找不到則會拋出錯誤,在join查詢的時候,路由走的是所有分片節點。


4,報錯處理總結

4.1 報錯1:

mysql>  INSERT INTO ORDER2(ID,SN,CREATE_TIME) VALUES(1,'2BJ0001',NOW());
    ERROR 1064 (HY000): For input string: "2BJ0001"
mysql> 

報錯是因為分片字段是str字符串,所以需要修改分片規則1中type從0改成1,0是數字型分片,1是字符串分片。

4.2 報錯2:

    mysql>  INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW());
    ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())
    mysql>  
解決方案1:此類ER表癿揑入操作丌能做為一個亊務迕行數捤提交,如果父子表在一個亊務中迕行提交,顯然在亊務沒有提交前子表是無法查刡父表癿數捤癿,因此就無法確定sharding node。如果是ER關系癿表在揑入數捤時丌能在同一個亊務中提交數捤,叧能分開提交。

4.3 報錯3

解決方案2:上面的方案2個事務搞不定,所以查看後台mycat.log,日志

    02/12 14:32:09.378  DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())
    02/12 14:32:09.506  DEBUG [$_NIOREACTOR-3-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where  order2.id=1
    02/12 14:32:09.513  DEBUG [BusinessExecutor5] (EnchachePool.java:76) -ER_SQL2PARENTID  miss cache ,key:TESTDB:select order2.id from order2 where  order2.id=1
    02/12 14:32:09.513  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where  order2.id=1
    02/12 14:32:09.514  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21
    02/12 14:32:09.514  DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1
    02/12 14:32:09.535   WARN [$_NIOREACTOR-3-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.order2' doesn't exist
    02/12 14:32:09.535  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=3, lastTime=1455258729497, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=290, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
    02/12 14:32:09.715  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn22
    02/12 14:32:09.715  DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m2
    02/12 14:32:09.716   WARN [$_NIOREACTOR-1-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.order2' doesn't exist

後台執行sql確實找不到

    mysql> select order2.id from order2 where  order2.id=1;
    ERROR 1105 (HY000): Table 'db3.order2' doesn't exist
    mysql> 

原因是2個datanode是互為主從的關系,dn21和dn22是mm結構,然後停止mm結構,2個都是單獨的mysql,然後重建ORDER2和ORDER_DETAIL2表,再執行insert操作,ok,問題解決了。

4.4 報錯4

解決方案3:表名字大小寫的問題導致

    mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW());
    ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())
    mysql> 

後台報錯:

    02/12 17:04:24.583  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=5, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())
    02/12 17:04:24.584  DEBUG [$_NIOREACTOR-0-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where  order2.id=1
    02/12 17:04:24.584  DEBUG [BusinessExecutor6] (EnchachePool.java:76) -ER_SQL2PARENTID  miss cache ,key:TESTDB:select order2.id from order2 where  order2.id=1
    02/12 17:04:24.585  DEBUG [BusinessExecutor6] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where  order2.id=1
    02/12 17:04:24.585  DEBUG [BusinessExecutor6] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21
    02/12 17:04:24.585  DEBUG [BusinessExecutor6] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1
    02/12 17:04:24.585   WARN [$_NIOREACTOR-3-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.order2' doesn't exist
    mysql> select * from db3.order2;
    ERROR 1146 (42S02): Table 'db3.order2' doesn't exist
    mysql> select * from db3.ORDER2;
    Empty set (0.01 sec)

    mysql> 
    mysql> 
    [mysqld]
    lower_case_table_names = 1

然後重啟mysql已經mycat服務,再次進行check。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved