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