myacat分片及全局表定义

myacat分片及全局表定义

1.schema.xml配置文件说明

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="orders1" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
	   <table name="customer" dataNode="dn2" ></table>
	   <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
	   <!--orders_detail与orders表根据order_id关联-->
			<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
	   </table>
	   <!--定义全局表-->
	   <table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>
	</schema>
	<dataNode name="dn1" dataHost="host1" database="orders1" />
	<dataNode name="dn2" dataHost="host2" database="orders1" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.2.128:3306" user="root"
				   password="kht123">
    </writeHost>
	</dataHost>
	
	<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="192.168.2.129:3306" user="root"
				   password="kht123">
    </writeHost>
	</dataHost>
</mycat:schema>

2.在另外一台mysql上执行建表语句

#订单详细表 rows:600万
CREATE TABLE orders_detail(
id INT AUTO_INCREMENT,
detail VARCHAR(2000),
order_id INT,
PRIMARY KEY(id)
);
#订单状态字典表,全局表 rows:20
CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT,
order_type VARCHAR(200),
PRIMARY KEY(id)
);

3.重启 Mycat,并在mycat上执行插入语句,并查看效果

#Mycat向orders_detail 表插入数据
INSERT INTO orders_detail(id,detail,order_id) values(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);
#在mycat、dn1、dn2中运行两个表join语句
Select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id;
#Mycat向dict_order_type 表插入数据
INSERT INTO dict_order_type(id,order_type) VALUES(101,'type1');
INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');

mycat1.6.7.6版本

<schema name="orders1" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1" dataNode="dn1">
	 <table name="customer" dataNode="dn2" ></table>
<!--autoIncrement="true" 开启主键自增策略-->
<!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
	 <table name="orders" dataNode="dn1,dn2" rule="mod_rule" autoIncrement="true" fetchStoreNodeByJdbc="true">
		<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"></childTable>
	 </table>
	 <table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>
	</schema>
	<dataNode name="dn1" dataHost="host1" database="orders1" />
	<dataNode name="dn2" dataHost="host2" database="orders1" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="jdbc:mysql://192.168.2.128:3306" user="root"
				   password="kht123">
		</writeHost>
	</dataHost>
	
	<dataHost name="host2" maxCon="1000" minCon="10" balance="2"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM2" url="jdbc:mysql://192.168.2.130:3306" user="root"
				   password="kht123">
		</writeHost>
	</dataHost>