导航：首页 > 数据库 >

Mycat中如何配置schmea.xml

发表于：2026-11-02 作者：千家信息网编辑

千家信息网最后更新 2026年11月02日，这篇文章主要为大家展示了"Mycat中如何配置schmea.xml"，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下"Mycat中如何配置schmea.xml"

千家信息网最后更新 2026年11月02日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配置

dataNode="dn1"> ---默认数据节点，若新建一张表company2未在配置文件中配置，则默认建立在dn1数据节点上（私有表）
---分片表，根据对应的分片规则分片到各个物理节点上

---全局表,每个节点上都有的表
---等同于company
----等同于company2
rule="mod-long" />

rule="sharding-by-intfile" />
rule="sharding-by-intfile">
parentKey="id">
parentKey="id" />

parentKey="id" />

----数据节点对应的localhost以及真实的数据库
balance="2" ------localhosts对应的连接配置信息
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
select user()
password="ESBecs00">
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
select user()
password="ESBecs00">
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
select user()
password="ESBecs00">
......未完待续

全局表的查询有负载均衡的作用

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 |
+---------------------+----+---------+---------------------+