- 如何选择分库表
#客户表 rows:20万
CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(200),
PRIMARY KEY(id)
);
———————–
#订单表 rows:600万
CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id)
);
#订单详细表 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)
);
select o.*,od.detail,d.order_type
from orders o
inner join orders_detail od on o.id =od.order_id
inner join dict_order_type d on o.order_type=d.id
where o.customer_id=xxxx
- 修改配置文件schema.xml
<mycat:schema xmlns:mycat=”http://io.mycat/”>
<schema name=”TESTDB” checkSQLschema=”false” sqlMaxLimit=”100″ dataNode=”dn1″>
<table name=”customer” dataNode=”dn2″ ></table>
</schema>
<dataNode name=”dn1″ dataHost=”host1″ database=”atguigu_mc” />
<dataNode name=”dn2″ dataHost=”host2″ database=”atguigu_sm” />
<dataHost name=”host1″ 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.67.1:3306″ user=”root”
password=”123123″>
<readHost host=”hosts1″ url=”192.168.67.131:3306″ user=”root”
password=”123123″>
</readHost>
</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.67.1:3306″ user=”root”
password=”123123″>
</writeHost>
</dataHost>
</mycat:schema>
想要了解跟多关于Java培训课程内容欢迎关注尚硅谷Java培训，尚硅谷除了这些技术文章外还有免费的高质量Java培训课程视频供广大学员下载学习
上一篇: 前端培训面试他分析-浏览器执行时间线
下一篇: Java培训MySQL之MYCAT水平分表
