Mycat中如何配置schmea.xml

這篇文章主要為大家展示了“Mycat中如何配置schmea.xml”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“Mycat中如何配置schmea.xml”這篇文章吧。

1.基本環境

2.schemal配置

1. <?xml version="1.0"?>
   

2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
   

3. <mycat:schema xmlns:mycat="http://io.mycat/">
   

4. 
   

5.         <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">     ---默認數據節點，若新建一張表company2未在配置文件中配置，則默認建立在dn1數據節點上（私有表）
   

6.                 <!-- auto sharding by id (long) -->
   

7.                 <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />   ---分片表，根據對應的分片規則 分片到各個物理節點上
   

8. 
   

9.                 <!-- global table is auto cloned to all defined data nodes ,so can join
   

10.                         with any table whose sharding node is in the same data node -->
    

11. 
    

12.                 <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />    ---全局表,每個節點上都有的表
    

13.                                   ---等同于company

                                                ----等同于company2

14.                 <table name="goods" primaryKey="ID" type="global" dataNode="dn1，dn2,dn3" />
    

15.                 <!-- random sharding using mod sharind rule -->
    

16.                 <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
    

17.                            rule="mod-long" />
    

18.                 <!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
    

19.                         needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
    

20.                         rule="mod-long" /> -->
    

21.                 <table name="employee" primaryKey="ID" dataNode="dn1,dn2"
    

22.                            rule="sharding-by-intfile" />
    

23.                 <table name="customer" primaryKey="ID" dataNode="dn1,dn2"
    

24.                            rule="sharding-by-intfile">
    

25.                         <childTable name="orders" primaryKey="ID" joinKey="customer_id"
    

26.                                                 parentKey="id">
    

27.                                 <childTable name="order_items" joinKey="order_id"
    

28.                                                         parentKey="id" />
    

29.                         </childTable>
    

30.                         <childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
    

31.                                                 parentKey="id" />
    

32.                 </table>
    

33.                 <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
    

34.                         /> -->
    

35.         </schema>
    

36.         <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
    

37.                 /> -->
    




1.         <dataNode name="dn1" dataHost="localhost1" database="examdb" />             ----數據節點對應的localhost以及真實的數據庫
   

2.         <dataNode name="dn2" dataHost="localhost2" database="examdb" />
   

3.         <dataNode name="dn3" dataHost="localhost3" database="examdb" />
   

4. 
   

5. 
   

6. 
   

7.         <dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"            ------localhosts對應的連接配置信息
   

8.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
   

9.                 <heartbeat>select user()</heartbeat>
   

10.                 <!-- can have multi write hosts -->
    

11.                 <writeHost host="hostM1" url="192.168.6.121:3306" user="root"
    

12.                                    password="ESBecs00">
    

13.                         <!-- can have multi read hosts -->
    

14.                 </writeHost>
    

15. 
    

16.         </dataHost>
    

17.         
    

18. 
    

19.         <dataHost name="localhost2" maxCon="1000" minCon="10" balance="2"
    

20.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    

21.                 <heartbeat>select user()</heartbeat>
    

22.                 <!-- can have multi write hosts -->
    

23.                 <writeHost host="hostM1" url="192.168.6.120:3306" user="root"
    

24.                                    password="ESBecs00">
    

25.                         <!-- can have multi read hosts -->
    

26.                 </writeHost>
    

27. 
    

28.         </dataHost>
    

29. 
    

30.         <dataHost name="localhost3" maxCon="1000" minCon="10" balance="2"
    

31.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    

32.                 <heartbeat>select user()</heartbeat>
    

33.                 <!-- can have multi write hosts -->
    

34.                 <writeHost host="hostM1" url="192.168.6.119:3306" user="root"
    

35.                                    password="ESBecs00">
    

36.                         <!-- can have multi read hosts -->
    

37.                 </writeHost>
    

38. 
    

39.         </dataHost>
    

40. </mycat:schema>

41. ......未完待續


全局表的查詢有負載均衡的作用


1. mysql> select * from company;   ----本應該是相同的數據，改為不同的數據是為了展示負載均衡的效果
   

2. +----+-------------+
   

3. | id | name |
   

4. +----+-------------+
   

5. | 1 | this is 119 |
   

6. +----+-------------+
   

7. 1 row in set (0.01 sec)
   

8. 
   

9. mysql> select * from company;
   

10. +----+-------------+
    

11. | id | name |
    

12. +----+-------------+
    

13. | 1 | this is 120 |
    

14. +----+-------------+
    

15. 1 row in set (0.01 sec)
    

16. 
    

17. mysql> select * from company;
    

18. +----+-------------+
    

19. | id | name |
    

20. +----+-------------+
    

21. | 1 | this is 121 |
    

22. +----+-------------+
    

23. 1 row in set (0.00 sec)





分片表和私有表如果不在一哥節點上就聯合查不到了！

uc分片表，4101在第二個節點上
act為私有表，4101在第一個節點上
select * from uc_coupon where COUPON_ID=4101 ;   --單獨查，是有的
select * from act_vote_info where id=4101;       ---單獨查，是有的
select a.*,b.* from uc_coupon a,act_vote_info b where a.COUPON_ID=b.id and b.id=4101 limit 1;  --聯合查，查不到了，因為跨節點了！
舉例：


1. mysql> select * from order2;   ---分片表
   

2. +----+----------+---------+---------------------+
   

3. | ID | PROVINCE | SN | CREATE_TIME |
   

4. +----+----------+---------+---------------------+
   

5. | 2 | shanghai | 2BJ0001 | 2017-05-09 15:01:33 |    ---節點2上
   

6. | 1 | beijing | 2BJ0001 | 2017-04-23 21:48:08 |     ---節點1上
   

7. | 3 | tianjin | 2BJ0001 | 2017-05-09 15:01:45 |
   

8. +----+----------+---------+---------------------+ 
   

9. 3 rows in set (0.00 sec)
   

10. 
    

11. mysql> select * from tt;    ---私有表 節點1上
    

12. +------+------------+
    

13. | id | name |
    

14. +------+------------+
    

15. | 2 | zhangsanli |
    

16. | 1 | 12314 |
    

17. +------+------------+
    

18. 2 rows in set (0.00 sec)
    

19. 
    

20. mysql> select a.*,b.* from tt a,order2 b where a.id=b.ID and b.id=2;   -----2和私有表不在一個節點上，查不出來
    

21. Empty set (0.00 sec)
    

22. 
    

23. mysql> select a.*,b.* from tt a,order2 b where a.id=b.ID and b.id=1;    -----1和私有表在一個節點上，所以查的出來
    

24. +------+-------+----+----------+---------+---------------------+
    

25. | id | name | ID | PROVINCE | SN | CREATE_TIME |
    

26. +------+-------+----+----------+---------+---------------------+
    

27. | 1 | 12314 | 1 | beijing | 2BJ0001 | 2017-04-23 21:48:08 |
    

28. +------+-------+----+----------+---------+---------------------+
    

29. 1 row in set (0.00 sec)


同理：

分片表和分片表條件數據如果不在一個節點上就聯合查不到了！


1. mysql> select a.* ,b.* from order2 a,order3 b where a.id=b.id and a.id=2;    --單獨都是有數據的，但是id相等的分在不同的節點上，還是不能跨節點
   

2. Empty set (0.00 sec)

mysql> select * from order2;
+----+----------+---------+---------------------+
| ID | PROVINCE | SN      | CREATE_TIME         |
+----+----------+---------+---------------------+
|  2 | shanghai | 2BJ0001 | 2017-05-09 15:01:33 |
|  1 | beijing  | 2BJ0001 | 2017-04-23 21:48:08 |
|  3 | tianjing | 2BJ0001 | 2017-05-15 14:52:17 |
+----+----------+---------+---------------------+
3 rows in set (0.00 sec)


mysql> select * from order3;
+----+----------+---------+---------------------+
| ID | PROVINCE | SN      | CREATE_TIME         |
+----+----------+---------+---------------------+
|  2 | beijing  | 2BJ0001 | 2017-05-15 14:56:27 |
|  1 | tianjing | 2BJ0001 | 2017-05-15 14:56:35 |
|  3 | shanghai | 2BJ0001 | 2017-05-15 14:56:17 |
+----+----------+---------+---------------------+
3 rows in set (0.00 sec)






解決辦法：注解，詳細用法見文檔

1. mysql> /*!mycat:catlet=demo.catlets.ShareJoin */ select b.sn,b.CREATE_TIME,a.CREATE_TIME from order2 a,order3 b where a.id=b.id;
   

2. +---------------------+----+---------+---------------------+
   

3. | CREATE_TIME | id | sn | CREATE_TIME |
   

4. +---------------------+----+---------+---------------------+
   

5. | 2017-04-23 21:48:08 | 1 | 2BJ0001 | 2017-05-15 14:56:35 |
   

6. | 2017-05-09 15:01:33 | 2 | 2BJ0001 | 2017-05-15 14:56:27 |
   

7. | 2017-05-15 14:52:17 | 3 | 2BJ0001 | 2017-05-15 14:56:17 |
   

8. +---------------------+----+---------+---------------------+

以上是“Mycat中如何配置schmea.xml”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！