Mycat中如何配置schmea.xml

这篇文章主要为大家展示了“Mycat中如何配置schmea.xml”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“Mycat中如何配置schmea.xml”这篇文章吧。
 
1.基本环境
dn1 localhost1 192.168.6.121:3306—writehost
192.168.6.121:3307—readhost
192.168.6.121:3308
dn2 locahost2 192.168.6.120:3306—writehost
192.168.6.120:3307—readhost
192.168.6.121:3308
dn3 localhost3 192.168.6.119:3306—writehost
192.168.6.119:3307—readhost
192.168.6.119:3308
 
 
2.schemal配置
 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">     —默认数据节点，若新建一张表company2未在配置文件中配置，则默认建立在dn1数据节点上（私有表）
                <!– auto sharding by id (long) –>
                <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />   —分片表，根据对应的分片规则 分片到各个物理节点上
                <!– global table is auto cloned to all defined data nodes ,so can join
                        with any table whose sharding node is in the same data node –>
                <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />    —全局表,每个节点上都有的表
                                  —等同于company
                                            —-等同于company2
                <table name="goods" primaryKey="ID" type="global" dataNode="dn1，dn2,dn3" />
                <!– random sharding using mod sharind rule –>
                <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
                           rule="mod-long" />
                <!– <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
                        needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
                        rule="mod-long" /> –>
                <table name="employee" primaryKey="ID" dataNode="dn1,dn2"
                           rule="sharding-by-intfile" />
                <table name="customer" primaryKey="ID" dataNode="dn1,dn2"
                           rule="sharding-by-intfile">
                        <childTable name="orders" primaryKey="ID" joinKey="customer_id"
                                                parentKey="id">
                                <childTable name="order_items" joinKey="order_id"
                                                        parentKey="id" />
                        </childTable>
                        <childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
                                                parentKey="id" />
                </table>
                <!– <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
                        /> –>
        </schema>
        <!– <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                /> –>
        <dataNode name="dn1" dataHost="localhost1" database="examdb" />             —-数据节点对应的localhost以及真实的数据库
        <dataNode name="dn2" dataHost="localhost2" database="examdb" />
        <dataNode name="dn3" dataHost="localhost3" database="examdb" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"            ——localhosts对应的连接配置信息
                          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.6.121:3306" user="root"
                                   password="ESBecs00">
                        <!– can have multi read hosts –>
                </writeHost>
        </dataHost>
        
        <dataHost name="localhost2" maxCon="1000" minCon="10" balance="2"
                          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.6.120:3306" user="root"
                                   password="ESBecs00">
                        <!– can have multi read hosts –>
                </writeHost>
        </dataHost>
        <dataHost name="localhost3" maxCon="1000" minCon="10" balance="2"
                          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.6.119:3306" user="root"
                                   password="ESBecs00">
                        <!– can have multi read hosts –>
                </writeHost>
        </dataHost>
</mycat:schema>
……未完待续
 
全局表的查询有负载均衡的作用
 
mysql> select * from company;   —-本应该是相同的数据，改为不同的数据是为了展示负载均衡的效果
+—-+————-+
| id | name |
+—-+————-+
| 1 | this is 119 |
+—-+————-+
1 row in set (0.01 sec)
mysql> select * from company;
+—-+————-+
| id | name |
+—-+————-+
| 1 | this is 120 |
+—-+————-+
1 row in set (0.01 sec)
mysql> select * from company;
+—-+————-+
| id | name |
+—-+————-+
| 1 | this is 121 |
+—-+————-+
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;  –联合查，查不到了，因为跨节点了！
举例：
 
mysql> select * from order2;   —分片表
+—-+———-+———+———————+
| ID | PROVINCE | SN | CREATE_TIME |
+—-+———-+———+———————+
| 2 | shanghai | 2BJ0001 | 2017-05-09 15:01:33 |    —节点2上
| 1 | beijing | 2BJ0001 | 2017-04-23 21:48:08 |     —节点1上
| 3 | tianjin | 2BJ0001 | 2017-05-09 15:01:45 |
+—-+———-+———+———————+
3 rows in set (0.00 sec)
mysql> select * from tt;    —私有表 节点1上
+——+————+
| id | name |
+——+————+
| 2 | zhangsanli |
| 1 | 12314 |
+——+————+
2 rows in set (0.00 sec)
mysql> select a.*,b.* from tt a,order2 b where a.id=b.ID and b.id=2;   —–2和私有表不在一个节点上，查不出来
Empty set (0.00 sec)
mysql> select a.*,b.* from tt a,order2 b where a.id=b.ID and b.id=1;    —–1和私有表在一个节点上，所以查的出来
+——+——-+—-+———-+———+———————+
| id | name | ID | PROVINCE | SN | CREATE_TIME |
+——+——-+—-+———-+———+———————+
| 1 | 12314 | 1 | beijing | 2BJ0001 | 2017-04-23 21:48:08 |
+——+——-+—-+———-+———+———————+
1 row in set (0.00 sec)
 
同理：
 
分片表和分片表条件数据如果不在一个节点上就联合查不到了！
mysql> select a.* ,b.* from order2 a,order3 b where a.id=b.id and a.id=2;    –单独都是有数据的，但是id相等的分在不同的节点上，还是不能跨节点
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)
 
解决办法：注解，详细用法见文档
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;
+———————+—-+———+———————+
| CREATE_TIME | id | sn | CREATE_TIME |
+———————+—-+———+———————+
| 2017-04-23 21:48:08 | 1 | 2BJ0001 | 2017-05-15 14:56:35 |
| 2017-05-09 15:01:33 | 2 | 2BJ0001 | 2017-05-15 14:56:27 |
| 2017-05-15 14:52:17 | 3 | 2BJ0001 | 2017-05-15 14:56:17 |
+———————+—-+———+———————+
以上是“Mycat中如何配置schmea.xml”这篇文章的所有内容，感谢各位的阅读！