跳转至

46. mycat全局表和ER表

1. 使用场景

#某个表经常被join联合查询,则可以将其设置为全局表
例:
select t1.name,t.x from t1 join t;
select t2.name,t.x from t2 join t;
此时可以将t设置为全局表

2. 修改schema.xml配置文件

vim schema.xml
#添加如下参数
<table name='t' primaryKey='id' type='global' dataNode='dn1,dn2' />
#拿之前range的配置文件改一改

<?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="sh1">
    <table name="city_new" dataNode="dn1,dn2" rule="auto-sharding-long" />
    <table name='country_new' type='global' dataNode='dn1,dn2' />
</schema>

    <dataNode name="dn1" dataHost="oldguo1" database="world" />
    <dataNode name="dn2" dataHost="oldguo2" database="world" />

    <dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.178.161:3307" user="root" password="123">
        <readHost host="db2" url="192.168.178.161:3309" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.178.162:3307" user="root" password="123">
        <readHost host="db4" url="192.168.178.162:3309" user="root" password="123" />
    </writeHost>
    </dataHost>

    <dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.178.161:3308" user="root" password="123">
        <readHost host="db2" url="192.168.178.161:3310" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.178.162:3308" user="root" password="123">
        <readHost host="db4" url="192.168.178.162:3310" user="root" password="123" />
    </writeHost>
    </dataHost>

</mycat:schema>

3. 修改range的txt文件

#因为city表有4079行数据
vim autopartition-long.txt

0-2000=0
2001-4079=1

4. 导出数据

#3307
mysqldump -S /data/3307/mysql.sock world city_new >/tmp/city_new.sql
#3308
mysqldump -S /data/3308/mysql.sock world country_new >/tmp/country_New.sql

5. 删除原来的表数据

#3307
truncate table city_new;
#3308
truncate table country_new;

6. 得到建表语句

#3307
show create table city_new;
#3308
show create table country_new;

7. 建表

#3307,根据上一步中得到的country_new建表语句
create table country_new...
#3308
create table city_new...

8. 重启mycat

mycat restart

9. 导出insert语句

#1.
grep INSERT /tmp/city_new.sql >/tmp/city_insert.sql
#2.
grep INSERT /tmp/country_new.sql >/tmp/country_insert.sql

10. 连接mycat客户端,导入数据

mysql -uroot -p123 -h 192.168.178.161 -P8066
source /tmp/city_insert.sql
source /tmp/country_insert.sql

11. 分别查看不同分片数据

#3307
select count(*) from city_new;
#3308
select count(*) from country_new;

ER表

1. E-R表的作用

#为了防止跨分片join,可以使用E-R模式
A join B on a.x=b.x

2. 与全局表的区别

#1.依然是1个分片存储一个表
#2.没有那么多冗余数据

3. 参数如下

<table name="A" dataNode="dn1,dn2" rule="mod-long">
    <childTable name="B" joinKey="id" parentKey="id" />
</table>

4. 查询语句

select ci.name,co.name,ci.countrycode,ci.population from city_new as ci join country_new as co on ci.countrycode=co.code where ci.population<100;

最后更新: 2022-02-20 08:44:07