MySQL運維6-Mycat分庫分表之垂直分庫

一、垂直分庫場景

　　場景：在業務系統中，涉及一下表結構，但是由於用戶與訂單每天都會產生大量的數據，單台伺服器的數據存儲以及處理能力是有限的，可以對資料庫表進行拆分，原有資料庫如下

　　說明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：這裡我們創建三個表作為代表，其他暫時用不到的表就先不創建了，每個數據節點上創建一個表，然後這些表就會自動的出現在配置好的數據節點上。

create table tb_goods_base(id int auto_increment primary key, goods_name varchar(20),  category varchar(20), price int);
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));
create table tb_user(id int auto_increment primary key, name varchar(20),  age int, gender varchar(1));

　　在往每個表中插入一些測試數據

insert into tb_user (name, age, gender) values ("張三", 21, "男");
insert into tb_user (name, age, gender) values ("李四", 22, "女");
insert into tb_user (name, age, gender) values ("王五", 23, "男");

insert into tb_goods_base (goods_name, category, price) values ("華為手機","家電", 5888);
insert into tb_goods_base (goods_name, category, price) values ("中國李寧","服裝", 499);
insert into tb_goods_base (goods_name, category, price) values ("雙匯火腿","食品", 15);

insert into tb_order_master (money, goods_id, receiver_province, receiver_city, receiver) values (5888, 1, "130000", "130200", "張三");
insert into tb_order_master (money, goods_id, receiver_province, receiver_city, receiver) values (499, 2, "130000", "130800", "李四");
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數據節點上的。

create table tb_areas_provinces (id int auto_increment primary key, provinceid varchar(6), province varchar(20));
create table tb_areas_city (id int auto_increment primary key, cityid varchar(6), city varchar(20), provinceid varchar(6));
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數據節點上。

　　　　添加一些測試數據

insert into tb_areas_provinces (provinceid, province) values ("110000", "北京市");
insert into tb_areas_provinces (provinceid, province) values ("120000", "天津市");
insert into tb_areas_provinces (provinceid, province) values ("130000", "河北省");

insert into tb_areas_city (cityid, city, provinceid) values ("110100", "市轄區", "110000");
insert into tb_areas_city (cityid, city, provinceid) values ("130200", "唐山市", "130000");
insert into tb_areas_city (cityid, city, provinceid) values ("130800", "承德市", "130000");


insert into tb_user_address (user_id, province_id, city_id, address) values (1, "110000", "110100", "清河毛紡廠");
insert into tb_user_address (user_id, province_id, city_id, address) values (2, "130000", "130200", "里二衚衕");
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查詢

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中可以正確查出結果。 

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兩個表

create table tb_areas_provinces (id int auto_increment primary key, provinceid varchar(6), province varchar(20));
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出現在了三個數據節點上

　　　　再次插入數據進行多表查詢測試：

insert into tb_areas_provinces (provinceid, province) values ("110000", "北京市");
insert into tb_areas_provinces (provinceid, province) values ("120000", "天津市");
insert into tb_areas_provinces (provinceid, province) values ("130000", "河北省");

insert into tb_areas_city (cityid, city, provinceid) values ("110100", "市轄區", "110000");
insert into tb_areas_city (cityid, city, provinceid) values ("130200", "唐山市", "130000");
insert into tb_areas_city (cityid, city, provinceid) values ("130800", "承德市", "130000");　

　　　　會發現插入的這些測試數據，會在dn1,dn2,dn3的每個數據節點的表中都添加成功。

　　　　現在就可以順利的進行多表查詢了。

　　　　說明6：當全局表中的數據發生改變的時候，每個數據節點下的表，也都會發生數據改變。