翼度科技»论坛 编程开发 mysql 查看内容

MySQL运维6-Mycat垂直分库

6

主题

6

帖子

18

积分

新手上路

Rank: 1

积分
18
一、垂直分库场景

  场景:在业务系统中,涉及一下表结构,但是由于用户与订单每天都会产生大量的数据,单台服务器的数据存储以及处理能力是有限的,可以对数据库表进行拆分,原有数据库如下
  

  说明1:整个业务系统中的表,大致分为四个,商品信息类的表,订单相关的表,用户相关表及省市区相关的表,这里暂时将省市区的表和用户相关的表放在一个数据节点上。
  说明2:因为商品,订单和用户相关的数据,每天都会产生海量的数据,所以我们采取的分库策略是将不同业务类型数据,放在不同数据库中,即垂直分库。
 

二、准备工作

  在192.168.3.90,192.168.3.91,192.168.3.92三台服务器上创建shopping数据库
  

三、配置schema.xml

  

  说明1:在schema标签里面的table标签不需要rule属性的,只有在分表时才需要rule,我们现在是分库操作,不需要rule属性
  说明2:在table标签中,商品相关的表都放在dn1数据节点上,和订单相关的表都放在dn2数据节点上,和用户和地址相关的都放在dn3数据节点上
四、配置server.xml

  

  说明1:修改schemas标签中的数据库名称为shopping  
五、Mycat分库测试

  首先因为修改Mycat的配置文件,所以需要重启一下Mycat,保证新的配置起作用。
  

  重启之后,在192.168.3.91服务器上连接Mycat
  

  查看逻辑库和逻辑表
  

  说明1:目前这些表都还只是逻辑表,在mycat中存在,但是在MySQL的数据库中都没不存在,所以还需要把这些表创建出来。
  说明2:这里我们创建三个表作为代表,其他暂时用不到的表就先不创建了,每个数据节点上创建一个表,然后这些表就会自动的出现在配置好的数据节点上。
  1. create table tb_goods_base(id int auto_increment primary key, goods_name varchar(20),  category varchar(20), price int);
  2. create table tb_order_master(order_id int auto_increment primary key, money int, goods_id int, receiver_province varchar(6), receiver_city varchar(6), receiver varchar(20));
  3. create table tb_user(id int auto_increment primary key, name varchar(20),  age int, gender varchar(1));
复制代码
  

 
  在往每个表中插入一些测试数据
  1. insert into tb_user (name, age, gender) values ("张三", 21, "男");
  2. insert into tb_user (name, age, gender) values ("李四", 22, "女");
  3. insert into tb_user (name, age, gender) values ("王五", 23, "男");<br>
  4. insert into tb_goods_base (goods_name, category, price) values ("华为手机","家电", 5888);
  5. insert into tb_goods_base (goods_name, category, price) values ("中国李宁","服装", 499);
  6. insert into tb_goods_base (goods_name, category, price) values ("双汇火腿","食品", 15);
  7. insert into tb_order_master (money, goods_id, receiver_province, receiver_city, receiver) values (5888, 1, "130000", "130200", "张三");
  8. insert into tb_order_master (money, goods_id, receiver_province, receiver_city, receiver) values (499, 2, "130000", "130800", "李四");
  9. insert into tb_order_master (money, goods_id, receiver_province, receiver_city, receiver) values (15, 3, "110000", 110100, "王五");
复制代码
  

  以上是对Mycat数据垂直分库的创建表,插入数据和查询数据的测试。
六、Mycat多表查询测试

  情况一:同一数据节点上的多表查询
    首先创建一个三个的表用于测试,同一数据节点内的多表查询,tb_areas_provinces, tb_areas_city, tb_user_address 三个表都是在dn3数据节点上的。
  1. create table tb_areas_provinces (id int auto_increment primary key, provinceid varchar(6), province varchar(20));
  2. create table tb_areas_city (id int auto_increment primary key, cityid varchar(6), city varchar(20), provinceid varchar(6));
  3. create table tb_user_address (id int auto_increment primary key, user_id int, province_id varchar(6), city_id varchar(6), address varchar(20));
复制代码
    说明1:根据分库策略,创建的这三个测试表,都是属于用户和地址相关的数据,都在dn3数据节点上。
    

    添加一些测试数据
  1. insert into tb_areas_provinces (provinceid, province) values ("110000", "北京市");
  2. insert into tb_areas_provinces (provinceid, province) values ("120000", "天津市");
  3. insert into tb_areas_provinces (provinceid, province) values ("130000", "河北省");
  4. insert into tb_areas_city (cityid, city, provinceid) values ("110100", "市辖区", "110000");
  5. insert into tb_areas_city (cityid, city, provinceid) values ("130200", "唐山市", "130000");
  6. insert into tb_areas_city (cityid, city, provinceid) values ("130800", "承德市", "130000");
  7. insert into tb_user_address (user_id, province_id, city_id, address) values (1, "110000", "110100", "清河毛纺厂");
  8. insert into tb_user_address (user_id, province_id, city_id, address) values (2, "130000", "130200", "里二胡同");
  9. insert into tb_user_address (user_id, province_id, city_id, address) values (3, "130000", "130800", "避暑山庄");
复制代码
    

    现多表查询需求是:根据tb_user, tb_user_address表,tb_areas_provinces表和tb_areas_city表查出用户的名字已经所在的省,市,已经详细的地址:使用Mycat查询
  1. select u.name, p.province, c.city, ua.address from tb_user as u, tb_user_address as ua, tb_areas_provinces as p, tb_areas_city as c where u.id = ua.user_id and ua.province_id = p.provinceid and ua.city_id = c.cityid;
复制代码
    

    说明:同一数据节点内的多表联合查询在mycat中可以正确查出结果。 
  情况二:不在同一数据节点上的多表查询    需求:查询每一笔订单的收件地址信息(包含省、市信息),其中要用到tb_order_master在dn2数据节点上,tb_areas_provinces和tb_areas_city在dn3数据节点上
  1. select o.order_id, o.receiver,p.province, c.city from tb_order_master as o, tb_areas_provinces as p, tb_areas_city as c where o.receive_province=p.provinceid and o.receiver_city=c.cityid;
复制代码
    

    这个时候就报错了, 报错的原因是:夸数据节点的多表查询,在执行sql的时候,Mycat并不知道,将这条sql给哪一个数据节点处理。
    解决方式:将一些数据量少,并且一旦确定了就很少改变的表,设置为全局表,全局表可以在每个数据节点上都能访问。而本案例中的省/市表就符合这个特性,中国每个城市的编码一旦确定,几乎就不会变化,这样的数据表,我们就可以设置为全局表。全局表会存在每一个数据节点上。
    如果要设置全局表,只需要在schema.xml设置逻辑表的时候加上 type="global"参数即可
    

    说明1: 因为省市相关的数据表需要在dn1,dn2,dn3 三个数据节点上,所以dataNode这里要设置dn1,dn2,dn3三个节点
    说明2:在table标签内添加type="global"属性
    说明3:因为之前的areas的表,都要变成全局表,所以需要数据清空在重新添加测试数据
    

    说明4:因为修改了Mycat配置,所以需要重新启动Mycat
    

    重新创建tb_areas_provinces和tb_areas_city两个表
  1. create table tb_areas_provinces (id int auto_increment primary key, provinceid varchar(6), province varchar(20));
  2. create table tb_areas_city (id int auto_increment primary key, cityid varchar(6), city varchar(20), provinceid varchar(6));  
复制代码
    

    

    说明5:这时候就会发现tb_areas_procinces和tb_areas_city出现在了三个数据节点上
    再次插入数据进行多表查询测试:
  1. insert into tb_areas_provinces (provinceid, province) values ("110000", "北京市");
  2. insert into tb_areas_provinces (provinceid, province) values ("120000", "天津市");
  3. insert into tb_areas_provinces (provinceid, province) values ("130000", "河北省");
  4. insert into tb_areas_city (cityid, city, provinceid) values ("110100", "市辖区", "110000");
  5. insert into tb_areas_city (cityid, city, provinceid) values ("130200", "唐山市", "130000");
  6. insert into tb_areas_city (cityid, city, provinceid) values ("130800", "承德市", "130000"); 
复制代码
    会发现插入的这些测试数据,会在dn1,dn2,dn3的每个数据节点的表中都添加成功。
    现在就可以顺利的进行多表查询了。
    

    说明6:当全局表中的数据发生改变的时候,每个数据节点下的表,也都会发生数据改变。 

来源:https://www.cnblogs.com/Se7eN-HOU/p/17908887.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具