Mysql进阶之Mycat中间件（四） ER表和全局表的实现

每日任务1：完成ER表多表联查

每日任务标准：
2个表商品表 product 和产品类别表 type

create table product(
    id int unsigned primary key auto_increment,
    name varchar(255),
    price decimal(10,2),
    type_id int     # 产品分类种类
)engine=innodb;

create table type(
id int unsigned primary key auto_increment,
type_name varchar(255)
)engine=innodb;

1.规定在product中type_id和type表格中的id同样的数据信息要放到同一个分块连接点中以防止跨系统分区关联查询。
2.应用3个分块连接点,3个分块连接点在一台服务器上。
3.应用牙模型优化算法分割。

———————-
A. 配备schema.xml

<pre data-cke-widget-keep-attr="0" data-widget="codeSnippet" class="cke_widget_element" data-cke-widget-data="{"classes":null,"lang":"","code":"\n

\n \n

\n\n\n\n\n\n\n\n\n select user()\n \n”}”><schema name="test" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1,dn2,dn3"> <table name="type" primaryKey="id" dataNode="dn4,dn5,dn6" rule="mod-long2"> <childTable name="product" primaryKey="id" joinKey="type_id" parentKey="id"></childTable> </table> </schema>


<!-- 这3个分块连接点用以检测ER表而创 -->

<dataNode name="dn4" dataHost="host2" database="db1"></dataNode>

<dataNode name="dn5" dataHost="host2" database="db2"></dataNode>

<dataNode name="dn6" dataHost="host2" database="db3"></dataNode>

<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM2" url="127.0.0.1:3306" user="root" password="xxx"></writeHost> </dataHost>

# 上边dataHost的配备中，设定balance=”0″ 不开展读写分离，是由于仅有一台服务器hostM2，因此读不读写分离不在乎。
switchType=”-1″ 不全自动转换，由于仅有一台服务器
writeType=”0″ 就是指往第一台服务器写，也是由于仅有一台服务器

B. 配备server.xml,建立客户实际操作schema

<user name="u1">

    <property name="password">xxxxx</property>

    <property name="schemas">test</property>

</user>

C. 配备rule.xml

<tableRule name="mod-long2">

    <rule>

        <columns>id</columns>           #以type表的id做为系统分区字段名

        <algorithm>mod-long2</algorithm>

    </rule>

</tableRule>

<function name="mod-long2" class="io.mycat.route.function.PartitionByMod"> <property name="count">3</property> # 表明仅有3个分区表，分割成3一部分 </function>

重新启动mycat

# 登陆hostM2的mysql服务项目，建立db1~3数据库查询：(请先保证已设定表名忽视英文大小写)

set character_set_server=utf8;
create database db1;
create database db2;
create database db3;

# 当地登陆mycat

mysql -h204.175.124.51 -uu1 -P8066 -pxxxxx

# 建立product和type表

use test

create table product(
    id int unsigned primary key auto_increment,
    name varchar(255),
    price decimal(10,2),
    type_id int     # 产品分类种类
)engine=innodb;

create table type(
    id int unsigned primary key auto_increment,
    type_name varchar(255)
)engine=innodb;

先往type插进数据信息

insert into type (id,type_name) values (1,"t1");    # 在db2
insert into type (id,type_name) values (2,"t2");    # 在db3
insert into type (id,type_name) values (3,"t3");    # 在db1
insert into type (id,type_name) values (4,"t4");    # 在db2
insert into type (id,type_name) values (5,"t5");    # 在db3

在往product插进数据信息

insert into product (id,name,price,type_id) values (1,"p1",50,1);   # 在db2
insert into product (id,name,price,type_id) values (2,"p2",50,2);   # 在db3
insert into product (id,name,price,type_id) values (3,"p3",50,3);   # 在db1
insert into product (id,name,price,type_id) values (4,"p4",50,4);   # 在db2
insert into product (id,name,price,type_id) values (5,"p5",50,4);   # 在db2

发觉type_id和type的id同样的product与type数据信息都是在同一个分块连接点中。
实际上对type的id设定应用牙模型优化算法是，会对product的type_id采用同样的牙模型优化算法，那样才确保了type_id和type的id同样的product与type数据信息在同一个分块连接点中。

=================================================

每日任务2：完成全局性表

每日任务标准：
還是上边2个表商品表 product 和产品类别表 type

1.product是大表；type是小表，仅有固定不动的6种归类；规定对上边二张表开展有效分割，防止跨系统分区关联查询。
2.应用3个分块连接点,3个分块连接点在一台服务器上。
3.应用分块枚举算法。

———————————-

因为type是小表，并且仅有固定不动的6中归类，即数据信息不常常转换，因此能够对type表开展全局性表设定。
以product表的type_id为系统分区字段名

以下：
配备schema.xml

<pre data-cke-widget-keep-attr="0" data-widget="codeSnippet" class="cke_widget_element" data-cke-widget-data="{"classes":null,"lang":"","code":"\n

\n\n\n\n\n\n\n\n select user()\n \n”}”><schema name="test" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1,dn2,dn3"> <table name="type" primaryKey="id" dataNode="dn4,dn5,dn6" type="global"></table> <table name="product" primaryKey="id" dataNode="dn4,dn5,dn6" rule="sharding-by-intfile2"></table> </schema>


<dataNode name="dn4" dataHost="host2" database="db1"></dataNode>

<dataNode name="dn5" dataHost="host2" database="db2"></dataNode>

<dataNode name="dn6" dataHost="host2" database="db3"></dataNode>

# 设定了type=”global”的<table>标识不用设定rule优化算法

配备rule.xml

<tableRule name="sharding-by-intfile2">

    <rule>

        <columns>type_id</columns>              # 以type_id为系统分区字段名

        <algorithm>hash-int2</algorithm>

    </rule>

</tableRule>

<function name="hash-int2"

          class="io.mycat.route.function.PartitionByFileMap">

    <property name="mapFile">partition-hash-int2.txt</property>

    <property name="defaultNode">0</property>       # 设定假如product中有超出范畴的type_id则该数据信息会默认设置插进第一个连接点。如果不设定则插进超出的type_id的数据信息会出错。

</function>

partition-hash-int2.txt 以下：

# type为1,4放进连接点1，type为2,5放进连接点2，type为3,6放进连接点3

1=0
4=0
2=1
5=1
3=2
6=2

重新启动mycat

建立db1~3数据库查询，并插进几个数据信息：

insert into type (id,type_name) values (1,"t1");
insert into type (id,type_name) values (2,"t2");
insert into type (id,type_name) values (3,"t3");
insert into type (id,type_name) values (4,"t4");
insert into type (id,type_name) values (5,"t5");
insert into type (id,type_name) values (6,"t6");

# 建立6个归类

insert into product (id,name,price,type_id) values (1,"p1",10,1);
insert into product (id,name,price,type_id) values (2,"p4",30,4);
insert into product (id,name,price,type_id) values (3,"p12",30,5);
insert into product (id,name,price,type_id) values (4,"p62",30,6);
insert into product (id,name,price,type_id) values (4,"p62",30,7);

# 超出的type_id的数据信息会插进到第一个连接点dn4

explain select * from product where type_id=7;
 ----------- --------------------------------------------------- 
| DATA_NODE | SQL                                               |
 ----------- --------------------------------------------------- 
| dn4       | SELECT * FROM product WHERE type_id = 7 LIMIT 100 |
 ----------- ---------------------------------------------------

# 开展多表联查

explain select * from type t join product p on p.type_id=t.id where type_id=4;
 ----------- ----------------------------------------------------------------------- 
| DATA_NODE | SQL                                                                   |
 ----------- ----------------------------------------------------------------------- 
| dn4       | select * from type t join product p on p.type_id=t.id where type_id=4 |
| dn5       | select * from type t join product p on p.type_id=t.id where type_id=4 |
| dn6       | select * from type t join product p on p.type_id=t.id where type_id=4 |
 ----------- -----------------------------------------------------------------------

尽管这儿表明的是要验dn4,5,6这三个分块，实际上仅用查dn4这一分块就可以了。
由于试着将 dn5,dn6 的type数据信息清除，实行select * from type t join product p on p.type_id=t.id where type_id=4;依然能够查到数据信息。

关键字：