ADADADADAD

Mycat中如何配置schmea.xml[ mysql数据库 ]

mysql数据库时间：2024-11-26 22:14:03

作者：文/会员上传

如何提高mysql查询效率

简介：

1.基本环境dn1localhost1192.168.6.121:3306---writehost192.168.6.121:3307---readhost192.168.6.121:3308dn2locahost2192.168.6.120:3306---writehost192.168.6.120:3307

以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。

1.基本环境dn1localhost1192.168.6.121:3306---writehost
192.168.6.121:3307---readhost
192.168.6.121:3308dn2locahost2192.168.6.120:3306---writehost
192.168.6.120:3307---readhost
192.168.6.121:3308dn3localhost3192.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数据节点上（私有表）

<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="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以及真实的数据库

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"------localhosts对应的连接配置信息

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

<heartbeat>select user()</heartbeat>

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

password="ESBecs00">

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

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

password="ESBecs00">

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

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

password="ESBecs00">

</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.docx

将本文的Word文档下载到电脑

下载

热门标签: mycatschmea.xml

精品

申请https需要什么条件

Mycat中如何配置schmea.xml[ mysql数据库 ]

Mycat中如何配置schmea.xml.docx

精品

热门推荐

大家都在看